July 7, 2008 at 10:02 am
I would like to know if there could be any possible advantage to having an identity primary key field set as non-clustered and having a separate clustered index on the same field? I really don't there believe there could be, but need to verify it. Also, would there be any disadvantages to maintaining this arrangement?
"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
July 7, 2008 at 10:24 am
No advantages I can see, and the disadvantage is that you are maintaining 2 indexes where you only really need to maintain 1.
Note, there are a few, very special, very rare cases where you may want this arrangement. They are not common. More likely, it's a mistake made by someone doing index tuning.
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
July 7, 2008 at 10:24 am
These two concepts are not necessarily related at all.
A PK defines uniqueness on your data rows. It allows you to find an individual row out of a group. Whether this is clustered or not, it's purpose is to provide a locator for that row.
A clustered index orders the data in index order, and is best used for range type queries. It has nothing to do with uniqueness or keys.
Clustered indexes can be affected by inserts/updates, causing page splits and internal fragmentation, so you want to be aware of this when choosing a clustered index.
I've often had some ID (identity/GUID) as the PK, but used a date for a clustered index since I often query on date ranges.
July 7, 2008 at 10:25 am
Steve Jones - Editor (7/7/2008)
I've often had some ID (identity/GUID) as the PK, but used a date for a clustered index since I often query on date ranges.
Likewise, but Ron (if I read properly) has a clustered and a nonclustered index on the same field.
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
July 9, 2008 at 9:27 am
Ron Kunce (7/7/2008)
I would like to know if there could be any possible advantage to having an identity primary key field set as non-clustered and having a separate clustered index on the same field? I really don't there believe there could be, but need to verify it. Also, would there be any disadvantages to maintaining this arrangement?
No advantage. Possible disadvantages (in terms of double-index writes, etc.).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 9:36 am
No advantage, disadvantage possibilites include an impact on performance due to the multiple indices.
July 9, 2008 at 9:37 am
Non-sequential inserts on a clustered key are a huge problem. It makes for page splits and some very slow inserts once a page split has occured. You need to size the FILL FACTOR correctly to keep page splits from happening and you need to maintain the indexes every night (or more) on tables have a large number of inserts in such a situation.
I know I'll get a huge amount of flak for this, especially if the likes of Celko show up on this thread, but I always have the clustered index on something that maintains the order of inserts so that the inserts go to the logical "end" of the table, thereby preventing page splits do to inserts. That means, many times, that the clustered index will be on some sort of "created" date column or an ID column. It also means that I may have to have a non-clustered unique index on another key for the table. It doesn't really violate what a lot of people have to say about trying to use natural keys (although everything has exceptions) because the Clustered IDENTITY or date column is not necessarily exposed to the user. It's usually just to keep page splits from happening. I'll frequently call the IDENTITY column "RowNum" just to keep the zealots off my back but it really is an "ID" column no matter how you slice it.
Ok... duck... here they come... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2008 at 10:15 am
PORK CHOPS!!!!! (lol) :w00t:
Couldn't resist (well, I could have, but didn't want too!)
😎
July 9, 2008 at 12:33 pm
Jeff, I do the same thing.
Relational Theory is a wonderful thing, but every now and again (not more than ten or fifteen times per nanosecond), it runs into this little detail called "physical universe reality".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 12:56 pm
GSquared (7/9/2008)
"physical universe reality".
Reality? What's that? 😉
Jeff Moden (7/9/2008)
I always have the clustered index on something that maintains the order of inserts so that the inserts go to the logical "end" of the table, thereby preventing page splits do to inserts.
Likewise, especially if it's a fast changing table.
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
July 9, 2008 at 1:10 pm
Reality - What a Concept (-- Robin Williams)
😎
July 9, 2008 at 3:01 pm
hope someone can help me get a bit more educated with sql server.
if i have a clustered index on an identity column and insert a row, what lock is taken? is it an exclusive lock on the last page? if this is the case and i do another insert, would the 2nd insert get blocked by the 1st one completing?
July 9, 2008 at 3:23 pm
The answer is that it can end up blocking, depending on what else is going on and what you're doing with uncommitted transactions. The only time I've seen it be a problem was someone who specifically was holding open a long-running transaction on the insert, then trying to insert again while it was still open. Haven't ever had that problem in any of my databases, but I did see a guy run into it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 4:43 pm
GSquared (7/9/2008)
Jeff, I do the same thing.Relational Theory is a wonderful thing, but every now and again (not more than ten or fifteen times per nanosecond), it runs into this little detail called "physical universe reality".
Cool... I deal with "physical universe reality" as heaped upon me by others less able 😉 Glad to see I'm not alone there... :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2008 at 4:45 pm
Lynn Pettis (7/9/2008)
PORK CHOPS!!!!! (lol) :w00t:Couldn't resist (well, I could have, but didn't want too!)
😎
Now THAT's entertainment! 😛 Haven't tried it yet, but I understand that Pork Chops go very well with "duck"... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply