Clustered and non clustered index on same column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Clustered and non clustered index on same column

Author

Topic

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-12-13 : 07:33:31

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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-13 : 07:44:47

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-13 : 07:52:54

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

Madhao
Starting Member

1 Post

Posted - 2012-12-14 : 04:28:45

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

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-12-14 : 05:04:44

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 05:07:22

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/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-14 : 05:50:35

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.

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2012-12-20 : 00:51:59

Thanks nigelrivett for your reply,i got it need to put an index on column "type" by including column "id".

M.MURALI kRISHNA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources