June 26, 2009 at 8:52 am
shnex (6/26/2009)
I have a index seek only for id and fk3(the first column in the enumeration for non cl index). The result was the same for other examples too, the seek was only for the first column.
Yes it will be. SQL can only seek on an index if the columns that it's seeking are a left-based subset of the index keys.
Think of a phone book, it's sorted by surname then firstname or initial, then address. How would you find all the people named Matthew in London? Can't do a seek, because you don't have a surname.
I was thinking to group more fk in the non-cl index, and the clustered one on the pk but it seems that I don't get what I want.
If you put all the pk column into one index, then only when the first column in there is used will SQL be able to seek
For starters you can try cluster on the pk (it's not a guid, is it?) and a nc index for each foreign key. It's not necessarily the best config, but it's far from the worst.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2009 at 9:08 am
Thanks man
That's exactly the answer I needed.
Unfortunately the pk is a uniqueidentifier...but I asked you before about this and we agree that is better to put on the uniqueidentifier than not to put one at all.
Anyway you cleared the problem for me. 10q again
June 26, 2009 at 9:20 am
shnex (6/26/2009)
Unfortunately the pk is a uniqueidentifier...but I asked you before about this and we agree that is better to put on the uniqueidentifier than not to put one at all.
Just keep a close eye on the fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply