This article is the part of series Mastering Index, in this particular article we will be exploring Clustered Index. Take a look at the content of this series -
One of the most interesting and talked about topic in database field is Index. I am writing this Series of article to cover all the different types of index available in SQL Server. I will be writing this series in two categories Index and Special Index. Please find the List of topics below, which I will covering –
One of the most interesting and talked about topic in database field is Index. I am writing this Series of article to cover all the different types of index available in SQL Server. I will be writing this series in two categories Index and Special Index. Please find the List of topics below, which I will covering –
Mastering Index
1. Clustered Index
2. Non Clustered Index
3. Unique Index
4. Index with included columns
5. Index on computed columns
6. Filtered Index
Special Index
1. XML Index
2. Full Text Index
3. Spatial Index
4. Columnstore Index
Clustered Index
As we know Indexes are used to speed up the data retrieval from table or view. Clustered index is a kind of Index which physically organizes the data as per clustering key. It means leaf nodes of clustered index contain the data rows. We can create only one clustered index on a table because records can be arrange physically only in one way. Primary key of the table is by default clustered index but it can be changed. When we create a clustered index without unique constraint, SQL Server creates the 4-byte integer with index to uniquely identify the record. Since non clustered index reference clustered index key to identify a records, so it should be unique. While creating a clustered index try to maintain three properties of a good index unique, narrow and static. How these properties are useful in creating efficient index is beyond this topic. If you wish to study them in details I would recommend you following book.
Recommended Book - Microsoft SQL Server 2012 Internals (Microsoft Press)
You may remember from the last
part of this series, indexes are implemented as B+ Tree in SQL server.
Let’s assume that we have a
very simple table Employee and it has two columns. First column is ID which is
an INT identity column and other column is Name varchar. If we create a
clustered index on ID column or we declare ID column as primary key, our
clustered index will be something like this.
Top most single Node in the tree is called root node and nodes at the bottom are called leaf node. Leaf nodes in a clustered index contain the actual records/data. Nodes in between Root and Leaf are called intermediate node/ non leaf nodes and used for traversing.
Creating Clustered Index
We can create index from management studio by using the GUI or script. Here is the syntax for that-
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex_Name] ON [dbo].[TableName]
(
[Column_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
If you like to work from graphical interface, then expand the components of your table then select and right click the Index folder. It will show the options to create index and select create clustered index.
Once you select clustered index, it pops up the window.
Change the name of Index and use some relevant name. Then Add columns you want to index and yes, you can select multiple columns.
Deleting/ Dropping Index
You can use script or GUI from management studio.
DROP INDEX [ClusteredIndex_Name] ON [dbo].[Table_Name] WITH ( ONLINE = OFF )
GO
Once you have created the index, it is important to monitor the usage and performance of the index. So you can make sure indexes are working towards the performance goals. I have a separate article for index utilization report.
That shows the list of indexes in your database and their performance metrics.
It is very common that over the time we creates Indexes and lose track of them. You can find duplicate index and delete them. It is important because Index consumes disk space and CPU during insert and update. One of my article shows a very simple way to find duplicate index.
Finding Duplicate Index
Important DMVs for clustered index
sys.indexes
sys.index_columns
sys.dm_db_index_physical_stats function
sys.dm_db_database_page_allocations function
sys.dm_db_missing_index_details
sys.dm_db_missing_index_groups
Interview Questions
Q. Can we create a clustered index with include column option?
Q. How many clustered index we can create on a table.
Q. Describe the properties of a good indexing key?
Q. is it possible to create a primary key with non clustered index?
Recommended Book for this Topic
