Nonclustered Index in SQL Server

After finishing two articles on Index “Some basics about Index in SQL Server” and “Clustered Index in SQL Server”, this is now time to explore nonclustered index in SQL Server. There are only two major difference between Clustered and Nonclustered Index are as follows:

1.)    You can have only one Clustered index per table and 249 (till SQL Server 2005) and 999 (after SQL Server 2005) Nonclustered Index per table

2.)    Clustered Index stores actual row data in the leaf level and nonclustered index would stores only those columns which are included in nonclustered index and pointer to the actual row which may be in clustered index or in heap (know more about leaf, heap, RID etc. from here).

Like clustered index, you can have one or multiple columns defined in Nonclustered Index too. Order of the column defined in Nonclustered plays an important role to meet Index seek, as I told you in previous article too that Index seek is good than Index Scan, so one should try to meet up Index seek as long as possible.

We are going to use same database and “Orders” table defined in previous article “Clustered Index in SQL Server”.

--creating nonclustered index,

CREATE NONCLUSTERED INDEX idx_orderdate on Orders(orderdate,orderid)

--run following query with execution plan and see the results in execution plan

--you can see execution plan with the following steps

--first select below given query

--Press Ctrl+M

--press F5

SELECT OrderDate,orderid from orders where OrderDate = '2010-02-01 00:04:00.000'

You will see your nonClustered Index Seek in your execution plan.

 

There are few more indexes under the same category like INCLUDE columns, Filtered Index, Covering Index which we will be exploring very soon.

if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”