General information
Forum:
Microsoft SQL Server
>What are the advantages/disadvantages of using Clustered Indexes?
>
>Thanks
Kevin,
A clustered index controls the physical sorting of the table on disk. If the fill factor of the clustered index (which is the fill factor for the table) is 100% and a row is inserted with clustered index key(s) that do not put the row at the end of the table, the table is reorganized to maintain the proper ordering. If the fill factor is less than 100 and there is room, the row is inserted without a reorg.
If a table has any indexes, one of them must be a clustered index. The clustered index is used by all other indexes as the pointer to the row in the table. Wide clustered indexes cause other indexes to consume more space.
By default, the first index created on a table is the clustered index. All other indexes are nonclustered by default. You cannot use the NONCLUSTERED clause in a CREATE INDEX statement if a clustered index does not already exist.
Internally, a clustered index has to be unique. SQL Server will automatically add a uniqueidentifier column (GUID) to the table and to the clustered index key list if the index is created with keys that are not unique. This is a terrible situation because the unique identifier is a 16 byte field.
-----
Queries and joins that can be satisfied from the key(s) of the clustered index go directly to the row without searching other indexes or scanning through all of the rows of the table.
INSERTs on a table with a properly designed clustered index will be placed at the end of the table rather than squeezed into the middle. Of course, a poorly designed clustered index makes the INSERTs horribly inefficient.
-----
The only real disadvantage of using a clustered index is that the key(s) must be chosen well and a proper fill factor specified. A clustered index on an int identity column is the way to go in most situations.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only