Clustered Indexes

Hello

anyone can explain whats clustered indexes, what's the diference between clustered and non clustered indexes and when to use it. thanks :)

Hi,

One table can have only one clustered index

One table can have many non-clustered indexes (though it's not a good practice to have many)

The clustered index is a b-tree structure that reorganises the actual data of the table (the sql server pages) and stores it as it's leafs.

The non-clustered index is also a b-tree structure that uses pointers (the leafs of the tree) to the actual data. It does not contain the actual data of the table, like clustered indexes do. (A clustered index is a special type of index that reorders the way records in the table are physically stored: http://www.mssqlcity.com/Articles/Tuning/IndexOptimTips.htm)

You use non-clustered indexes on certain columns of the table, which are heavily used by your queries.

Indexes become defragmented so you need to rebuild them from time to time.

Here is some more info too: http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1088651_tax301327,00.html?bucket=ETA&topic=301327

There are many nice articles on the net, just write: "clustered vs nonclustered indexes" and you'll get many results

I also found really interesting stuff about indexes in book "Inside SQL Server 2000"

Cheers

|||

Golden rules:

Clustered indexes

Use for columns that contain a large number of distinct values