Impact of Multi-Column Clustered Index

Clustered Indexes are really important and special. Special because there can only be 1 Clustered Index in a table and they decide the order in which data is stored in tables.

Clustered Index help the database uniquely identify individual rows in a table. As mentioned in my previous blog if the Clustered Index do not uniquely identify a row, SQL Server automatically adds a 4 byte uniqueidentifier column to make it unique.

However we should be really careful not to add too many columns in a Clustered Index in trying to make it unique, because all Non-Clustered Indexes store the columns from Clustered Index at the end to point to a row in a table.

Therefore, more the no. of columns in a Clustered Index will be, more the size of Non-Clustered Index will be.

Disable and Enable Index

Microsoft introduced the concept of disabling an Index in SQL Server 2005.

They syntax to disable the index is as follows:
ALTER INDEX indexname ON tablename DISABLE
e.g.
ALTER INDEX idx_TEST ON dbo.TEST DISABLE

However, contrary to popular belief syntax to enable the index is not
ALTER INDEX idx_TEST ON dbo.TEST ENABLE

The correct syntax to enable an Index is
ALTER INDEX idx_TEST ON dbo.TEST REBUILD

Remember the keyword REBUILD for it.

Create Clustered Index as Unique or Not?

Clustered Index is very important and determine the order in which data is stored in the table. A table without a Clustered Index is called a HEAP. (Try and picture a Heap in your mind and then you can visualize how data is stored in table without a Clustered Index.. Huh!!)

Clustered Index also helps the SQL Engine uniquely identify a row. Thus, if possible, always create a Clustered Index as UNIQUE. If you do not specify the UNIQUE clause while creating your Clustered Index, SQL Server automatically adds a 4 byte uniqueidentifier column to the table to make every row unique.

Thus, specifying a UNIQUE clause also helps save valueable database space.

More information can be found at the following MSDN Page.