SQL Server Clustered Index

What Is an Index?

An index can be best described as a pointer to data in a table. An index in a database is very similar to an index in the back of a book or the phone book.  Indexes are created on the table or the view

Purpose of index

The purpose of an index is to retrieve data from a table efficiently and fast

Types of indexes

  • Clustered Indexes

Clustered indexes sort the data rows in the table or view based on their key values, as such there can be only one clustered index per table. When a table does not have an index, it is referred to as a heap.  When you create a clustered index, it does not require any additional disk space

  • Non-clustered Indexes

A Nonclustered index have a structure separate from the data rows, much like the index in back of a book; and as such does require disk space, as it’s a separate object. A nonclustered index contains a pointer to the data row that contains the key value. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

·   Composite Indexes

A composite index is an index on two or more columns of a table. A composite index can be created both on a clustered and non clustered index. Note, when creating a composite index the order of columns in the index is important for performance. Columns that will always mentioned in a query should be placed first.

 Other less used indexes (depends)
  • Covering Indexes
  • Full-text
  • Filtered Indexes
  • Column-based Indexes
  • Spatial
  • XML

Creating indexes using TSQL

CREATE INDEX index_name
ON table_name (column_name)

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE NONCLUSTERED INDEX [indexname] 
ON table_or_view_name ([columnname] ASC|DESC) 

DROP INDEX index_name ON table_name

--CREATE CLUSTERED INDEX index_name
--ON table_name (column_name)

EXAMPLE OF INDEXES AND CLUSTERED INDEX

--Insert 1000 records 
USE [SQL2]
GO

CREATE TABLE [dbo].[PhoneBook](
	[PhoneBookID] [int] NULL,
	[lname] [varchar](50) NULL,
	[fname] [varchar](50) NULL,
	[phone] [varchar](50) NULL
) ON [PRIMARY]
GO

--Insert 1000 records 
--View the 1000 records. Notice that the last name is not in any particular order
 
SELECT * FROM PHONEBOOK

--Insert record into phonebook table and notice the row is inserted after 1000 rows, as there is no index and the Lname is in no particular order

Insert into PHONEBOOK
values (1001,'Abba','Sara','555-1212')

SELECT * FROM PHONEBOOK

--repeat with another record

Insert into PHONEBOOK
values (1002,'Turner','Mike','805-555-1212')

SELECT * FROM PHONEBOOK

--Create a clustered index on table phonebook and column Lname so that the last name is alphabetized (its sorted by the clustered index created)

USE [SQL2]
GO

CREATE CLUSTERED INDEX [Idx_PhoneBook_Lname] --<< Index name with prefix, table name and column name (convention for clarity)
ON PhoneBook(Lname ASC)                      --<< Table and column name
GO

--View the 1000 records. This time notice that the last name IS in order by the last!
 
SELECT * FROM PHONEBOOK

--Now with the clusttered index in place, if we insert a record, it will automatically be inserted in the sorted order.

Insert into PHONEBOOK
values (1003,'Briggham','Johm','777-555-1212')

SELECT * FROM PHONEBOOK
USE [SQL2]
GO
Drop table PHONEBOOK

NON CLUSTERED INDEX SCRIPT

--View data with clustered index (lname column is still sorted)

SELECT*
FROM [SQL2].[dbo].[PhoneBook]

-- Create another clustered index causes an issue (on fname) because you can only have one clustered index

USE [SQL2]
GO

CREATE CLUSTERED INDEX [Idx_PhoneBook_phonebookid]
ON PhoneBook(phonebookid ASC)

Drop index [PhoneBook].[Idx_PhoneBook_lname]

--Can create an single clustered index that has multiple columns

CREATE CLUSTERED INDEX [Idx_PhoneBook_Fname_Lname]
ON [dbo].[PhoneBook](lname ASC,fname ASC) --<< multiple columns

SELECT*
FROM [SQL2].[dbo].[PhoneBook]

--Example of non clustered index

USE [SQL2]
GO

CREATE NONCLUSTERED INDEX [NC_Ind_PhoneBook_ fname]  --<< notice that after creation of a non clustered index, the data is not sorted
ON PhoneBook(fname ASC)

SELECT*
FROM [SQL2].[dbo].[PhoneBook]

--Can create multiple non clustered index (On lname)

USE [SQL2]
GO

CREATE NONCLUSTERED INDEX [NC_Ind_PhoneBook_phone] --<< second creation of an index and phone
ON PhoneBook(phone ASC)

SELECT*
FROM [SQL2].[dbo].[PhoneBook]