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 |
|
JeffT
Posting Yak Master
111 Posts |
 Posted - 2006-03-31 : 07:05:18
|
| Hi,I have a real basic table index question. If I have a clustered index as follows:CREATE CLUSTERED INDEX [IX_HF1] ON [dbo].[Table1]([Column1],[Column2]) ON [PRIMARY]and I am querying just on Column2, would it be more efficient and faster, in terms of the query, for me to also have a nonclustered index with just Column2 (as in example below) ? CREATE INDEX [IX_HF2] ON [dbo].[Table1]([Column2]) ON [PRIMARY]Thanks,Jeff |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-31 : 07:26:33
|
| Given that the clustered index won't help in the query given then yes.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
JeffT
Posting Yak Master
111 Posts |
Posted - 2006-03-31 : 07:35:29
|
| Thanks very much nr !Jeff |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-31 : 08:44:44
|
quote:
Originally posted by nr
Given that the clustered index won't help in the query given then yes.
Even though, Jeff understood, I do not  Nr, Could you please explain whether that (2nd) indexing is beneficial or notSrinika |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-31 : 09:27:33
|
because if you have a clustered index on 2 columns they are sorted on 1st and then on 2nd.so if you do a where only on 2nd column the index will be an scan instead of seek because it has to go through all of it to find the correct page.indexes on more than one column are usefull only if you always join or do a where on all columns in the index.usually it's best to have a clustered index on one column and non clustered ones on others.will that be ok?Go with the flow & have fun! Else fight the flow  Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-03-31 : 10:51:32
|
| Thanks Spirit1Got it, NowSrinika |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-31 : 16:50:56
|
| >> indexes on more than one column are usefull only if you always join or do a where on all columns in the index.It's (probably) useful if the first column is referenced, you don't have to reference all the columns.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Basic Table Index question