CODEWITHSUNDEEP
sql-server-2005indexinginternalclustered-index
sqlchild
sqlchild

Reputation: 9064

indexes in sql server, internal working and structure of indexes

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 ?

Upvotes: 1

Views: 3322

Answers (2)

Flat Cat
Flat Cat

Reputation: 916

Indexing views will duplicate data.

"The downside to this is that the indexed view is a full copy of the data from the underlying tables[...]"

http://strangenut.com/blogs/dacrowlah/archive/2008/11/26/creating-an-indexed-view-in-sql-server-2005-and-2008.aspx

Upvotes: 0

marc_s
marc_s

Reputation: 754268

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

Microsoft SQL Server 2008 Internals

enter image description here

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:

Upvotes: 2

Related Questions