SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Clustered and non clustered index on same column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mmkrishna1919
Yak Posting Veteran

India
85 Posts

Posted - 12/13/2012 :  07:33:31  Show Profile  Reply with Quote
Hi All,

in my application one of the table has 12 columns and they defined clustered index on one column(ID),and they defined "non clustered nonunique index" on the few columns like(ID,TYPE,status,reasoncode)

My question is if we execute an sql statement like

select * from table_name where id=some_value

In this case optimiser will go to either
clustered index
or
nonclustered index and then lookup for data retrival

And please advise why they created non clustered index with ID as first column in the composite index? even we had clustered index already on the same column ID.

M.MURALI kRISHNA

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/13/2012 :  07:44:47  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
A covering non clustered index should be more efficient for a query than a clustered index.
In your example the clustered index would probably be used but
select ID,TYPE
from tbl
where ID = some_value
would probably use the non clustered index.
In this case TYPE should probably be included rather than a key column.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 12/13/2012 :  07:52:54  Show Profile  Reply with Quote
Read this article.. You can understand better (5th and 6th topics)
http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005

--
Chandu
Go to Top of Page

Madhao
Starting Member

India
1 Posts

Posted - 12/14/2012 :  04:28:45  Show Profile  Reply with Quote
select * from table_name where id=some_value

In this case a Clustered index should be used by the optimizer to filter the required result set.(This is a ideal scenario where we are considering the CL is not highly fragmented and the stats are updated )
I say this because in absence of any other index a Index Seek on the CL is the fastest way to get the required data.

For the second question :
The NCL has ID as the first column as it will help SQL server to use Index seek of CL to get the required data quicker rather than a index scan that will be used if there is no relation between the two indexes
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
85 Posts

Posted - 12/14/2012 :  05:04:44  Show Profile  Reply with Quote
Hi Madhao,
If we have clustered index on the table then that clustered index key will associate with all non clustered indexes.

In our case we have clustered index on column ID
Hence this column(ID) will associate in all non clustered indexes for additional data look up,Then why they are explicitly creating non clustered index with ID as first column.

Hi nigelrivett:
select * from table_name where id=some_value --clustered index seek [expected]
select ID,TYPE from tbl where ID = some_value --non clustered seek [expected]

if my select statement is
select id,type from table_name where type='some value'
in this case the optimiser is going for non clustered index scan.

How optimiser behaves if we use second column or third[other than first column] of composite index in predicate clause?


M.MURALI kRISHNA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/14/2012 :  05:07:22  Show Profile  Reply with Quote
it may be that its a covering index which was created specifically for the query which is executed frequently. Check that it includes all the columns contained in the query. Creating such an index will not require a futher bookmark lookup step as it gets all required info from index itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/14/2012 :  05:50:35  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Not sure what your question is.
The scan is because the first column in the index isn't the search column so it has to access every row. It is scanning the non-clustered index because all of the data in the query is included in that index and so it is the fastest way. The non-clustered index is a copy of the data included in the index so will take up less space and hence less io than the clustered index.

For this query you should probably have an index
(type) include (id)

id will be included anyway as it is the clustered index but it is as well to be explicit as it is needed for this query.
That should give you a seek.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
85 Posts

Posted - 12/20/2012 :  00:51:59  Show Profile  Reply with Quote
Thanks nigelrivett for your reply,i got it need to put an index on column "type" by including column "id".

M.MURALI kRISHNA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000