Clustered Indexes

Question

Hi, Ques I want to know that how many clustered indexes can be created on a table ?

Satyapriya Nayak · Answer

Hi Arun, A table can have only one clustered index. Also Please refer Our recommended articles http://www.c-sharpcorner.com/uploadfile/6897bc/clustered-and-non-clustered-index-in-sql-2005/default.aspx http://www.c-sharpcorner.com/uploadfile/nipuntomar/clustered-index-and-non-clustered-index-in-sql-server/default.aspx Thanks

Arun Choudhary · Answer

Thanks

MuthuMari M · Answer

Hi, a clustered index physically arranges all data in a table sequential manner. So you can not have more than one physical arrangements of data in a table, you have one clustered index per table. thanks. If this post is useful then mark it as " Accepted Answer "

Jignesh Trivedi · Answer

hi, SQL Server 2005 1 Clustered Index + 249 Nonclustered Index = 250 Index http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx SQL Server 2008 1 Clustered Index + 999 Nonclustered Index = 1000 Index http://msdn.microsoft.com/en-us/library/ms143432.aspx hope this help.

Senthilkumar · Answer

Hi Arun, A table can have only one clustered index. If you have a primary or unique key for the column then it will create the clustered index by default. As you know, a table can have only one primary key. when you create the primary key it will create automatically. When ever you to DML operations on the table then it will do sorting automatically based on the key created on the column. PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint. Alternatively, a clustered index could be created on lname, fname (last name, first name), because employee records are often grouped and queried in this way rather than by employee ID. Before creating clustered indexes, understand how your data will be accessed. Consider using a clustered index for: Columns that contain a large number of distinct values. Queries that return a range of values using operators such as BETWEEN, >, >=, Columns that are accessed sequentially. Queries that return large result sets. Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because the rows are already sorted. This improves query performance. OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key. You can see the created index by using the following statement: sp_helpindex table_name SELECT * FROM sys.indexes For more info: http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx http://www.c-sharpcorner.com/Forums/Thread/167764/index-in-sql-server.aspx

Clustered Indexes

Answers (5)