Nonclustered Index in SQL Server

After finishing two articles on Index “Somebasics about Index in SQL Server” and “ClusteredIndex in SQL Server”, this is now time to explore nonclustered index in SQLServer. There are only two major difference between Clustered and NonclusteredIndex are as follows:
1.)    Youcan have only one Clustered index per table and 249 (till SQL Server 2005) and999 (after SQL Server 2005) Nonclustered Index per table
2.)    ClusteredIndex stores actual row data in the leaf level and nonclustered index wouldstores only those columns which are included in nonclustered index and pointerto the actual row which may be in clustered index or in heap (know more aboutleaf, heap, RID etc. from here).
Like clustered index, you can have one or multiple columnsdefined in Nonclustered Index too. Order of the column defined in Nonclusteredplays an important role to meet Index seek, as I told you in previous articletoo that Index seek is good than Index Scan, so one should try to meet up Indexseek as long as possible.
We are going to use same database and “Orders” table definedin previous article “ClusteredIndex in SQL Server”.
–creatingnonclustered index,
CREATE NONCLUSTERED INDEXidx_orderdate on Orders(orderdate,orderid)
–run followingquery with execution plan and see the results in execution plan
–you can seeexecution plan with the following steps
–first selectbelow 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 executionplan.
 
There are few more indexes under the same category likeINCLUDE columns, Filtered Index, Covering Index which we will be exploring verysoon.

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 defaultreference of all articles but examples and explanations prepared by RiteshShah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

2 Responses to “Nonclustered Index in SQL Server”

  1. Pravesh Singh Says:

    This is one of the best answer so far, I have read online.Just useful information. Very well presented. I had found another good collection of index in sql server over internet.please check out this link… Index in sql serverThanks

  2. Pravesh Singh Says:

    This is one of the best answer so far, I have read online.Just useful information. Very well presented. I had found another good collection of index in sql server over internet.please check out this link… Index in sql serverThanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: