asked
why to create only one cluster index and many noncluster index.
i am really confused on index why we should create only one cluster index with primary key table and which is not freqently accessible.and can create many non cluster index on a table which is frequently updated ,what will happen if i create many cluster index.
SQL Server allows you to only create 1 clustered index with 249 (999 in 2008) NCI supported. A CI doesn't have to be the PK. If you use the default table designer within SSMS to create a PK then a CI is generated. There is nothing stopping you dropping the CI on the PK (assuming it not on a large production env with active users would possibly see a slow down in perf) and then creating it on a more suitable column.
The reason you can only create a single clustered index is that the clustered index becomes the table. When there is no clustered index present on a table, a situation referred to as a heap table, the data is stored on the disk in an unorganized fashion, using space on any pages where it can be found, with a set of lookup tables created to manage retrieving the data. When you create a clustered index, the data is stored at the leaf level of the balanced-tree of the clustered index. This means that lookups against the index also gets the data, making the cluster a much more efficient way to store and retrieve data (in most cases). Because the cluster stores the data, you can only ever have one.
9 People are following this question.
