Composite Index vs Non Clustered Index

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 Programming

Composite Index vs Non Clustered Index

Author

Topic

sixsigma1978
Starting Member

25 Posts

Posted - 2012-02-02 : 16:05:14

Had a question about how would the database handle this scenario:

IF I created a non-clustered index like this (pseudo code):
create non clustered index (col1, col2)
vs
create non clustered index (col1)
create non clustered index (col2)

Would this be covering the query in both cases? what happens during query execution plan in both cases?
select col1, col2 from target_table

Any pointers would help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-02 : 16:12:53

Your query isn't a good example as you don't have a where clause or anything else that would utilize an index.

Provide a better example and we'll help you.

Just looking at your indexes though, you would just eliminate the one on col1 as it's a "duplicate" of your composite.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sixsigma1978
Starting Member

25 Posts

Posted - 2012-02-02 : 16:31:08

well - had simply using pseudos - but you can take this:

select col1, col2 from target_table where col1 > 12.5

I wanted to know whether both of these would qualify as "covered"?

create non clustered index (col1, col2)
vs
create non clustered index (col1)
create non clustered index (col2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-02 : 16:39:47

Both of THESE would be covering:

create non clustered index (col1, col2)
create non clustered index (col1) include (col2)

SQL will NOT use the index only on col2 for this query. If it uses the one with only col1, it will have to do a lookup on the clustered index to get the col2 info.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-02 : 17:33:21

Maybe take a look at this: http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

--
Gail Shaw
SQL Server MVP

sixsigma1978
Starting Member

25 Posts

Posted - 2012-02-02 : 21:17:11

quote:
Originally posted by tkizer

Both of THESE would be covering:

create non clustered index (col1, col2)
create non clustered index (col1) include (col2)

SQL will NOT use the index only on col2 for this query. If it uses the one with only col1, it will have to do a lookup on the clustered index to get the col2 info.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Thanks - so having two separate clustered index on columns won't cover the query i specified. It has to be a composite index! Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-02 : 21:50:22

A table can only have one clustered index. In your example query, only one index can be used.

It does not have to be a composite index. Maybe you should read Gail's link.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sixsigma1978
Starting Member

25 Posts

Posted - 2012-02-03 : 09:46:25

quote:
Originally posted by tkizer

A table can only have one clustered index. In your example query, only one index can be used.

It does not have to be a composite index. Maybe you should read Gail's link.

Tara Kizer

my bad - typo - I meant two non clustered indexes!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources