indexes in sql server, internal working and structure of indexes

Question

when we create a clustered index CIX_FirstNames on a column , say, FirstNames, then what actually happens internally in SQL Server?

i have read that clustered indexes create copy of the data.

so, does sql server creates a new index table, IndexTable , and copies all the FirstNames from the table into IndexTable, and when a firstname is searched, then it displays it from the index table?

is this the actual working of clustered indexes ?

Answer 1

This is way too big a topic to handle here in a quick answer - you need to get yourself:

Microsoft SQL Server 2008 Internals

which covers this all in great detail.

But just briefly: NO! creating a clustered index does NOT duplicate data! Where did you get that from??

What a clustered index does is reorder the data (according to the key), and the leaf-level nodes of the clustered index are the data pages - but those exist only once.

Some more resources of interest:

• Kimberly Tripp on Index Internals
• Kalen Delaney Workshop on Index Internals
• SQL Server Indexes: AN Introduction
• Introduction to Indexes
• Stairway to SQL Server Indexes: an Introduction