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.
| Author |
Topic |
|
ayamas
Aged Yak Warrior
552 Posts |
 Posted - 2008-04-10 : 06:15:04
|
Hi guys,Is it possible for a column in a table to have a clustered index & the same column to be a part of a non clustered covering index.Please try this one.I am confused  Create table test(id int primary key,name varchar(40),age int)CREATE NONCLUSTERED INDEX testindex ON test ( id, name, age)sp_help testAfter I do sp_help test it shows two indexes created, one on id & the other one on id,name& age as a part of covering index. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-10 : 06:38:27
|
| Yes, you can.The first one is created due to primary key constraint and it is unique clustered index by default. The second one you created explicitly, hence two indexes in sp_help.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-04-10 : 06:42:44
|
quote:
Originally posted by harsh_athalye
Yes, you can.The first one is created due to primary key constraint and it is unique clustered index by default. The second one you created explicitly, hence two indexes in sp_help.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
But then which index will take precedence first?If I use the id and name column in my query which index will be used? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-04-11 : 16:22:02
|
| Depends on the query and the statitics on the table. In your example of using teh ID and Name columns there is a good chance that the covering index would be used. |
|
|
|
ayamas
Aged Yak Warrior
552 Posts |
Posted - 2008-04-12 : 02:14:04
|
| Thanks for the feedback.I greatly appreciate it. |
|
|
|
|
|
|
Confusion about Indexes