January 16, 2004 at 5:06 am
What circumstances would warrant creating non clustered primary keys? I have seen this in some of our OLTP tables and was curious if anyone else uses them and why.
January 16, 2004 at 5:21 am
It all depends on your queries and your data. Sometimes a person will autogen (IDENTITY) a value for the primary key to use in relationships but a real world key may exist such as phone number which the majority of your queries may actually use. Or in some circumstances you may just find yourself querying another column more often than you ever do the primary key and may opt for performance reasons to cluster on it instead of the PK.
January 16, 2004 at 6:21 am
I guess it is most common to use the identity property on some numeric column as primary key. No problem with it. But as you can have only one clustered index on a table, like Antares said, you will certainly use it for something better than placing it on the primary key. On the other hand, a clustered index on the primary key is better than not having a clustered index on your table at all.
I know this is a nasty behaviour of EM when creating tables with EM that the primary key will become also the clustered index when you do not take care of this.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 16, 2004 at 6:46 am
> I know this is a nasty behaviour of EM when creating tables with EM that the primary key will become also the clustered index when you do not take care of this.
It's not just EM; that's the default whenever a table is created, unfortunately. I've seen very large and popular OLTP applications where the developers apparently simply accepted this default when they created the tables. We've increased the speed by an order of magnitude by simply choosing the best clustered indexes, which is seldom the primary key.
--Jonathan
January 16, 2004 at 1:07 pm
I assume you guys are saying that a column with a property of IDENTITY is a bad choice for a clustered index becuase it has been beat in your/our heads that clustered indexes are so wonderful for range-type queries. At the recent PASS conference in Seattle, Kimberly Tripp argued this and stated there was virtually no difference in range type queries when the range query used a non-clustered index or a clustered index. Her along with the storage architecture guys from MS that I spoke to now reccomend keeping the clustered indexes on tables with IDENTITY type columns unless there are no inserts going on. With the clustered index on the IDENT column, you create a hot spot for entries as the new inserts fall to the bottom of the data pages thus eliminating unanticipated page splits. I have seen people put clustered indexes on range columns and in the process cause an exhorbitant amount of page splits or have to play with the fill-factor every night to avoid this.
Jonathan, I am very interested how you were able to guage the speed gain and if you gauged the inserts as well or monitored for page splits or messed with the fill factors.
------------
Ray Higdon MCSE, MCDBA, CCNA
January 16, 2004 at 2:22 pm
> Jonathan, I am very interested how you were able to guage the speed gain and if you gauged the inserts as well or monitored for page splits or messed with the fill factors.
None of these applications used the identity property on primary keys. We time-trialed the stored procedures, and improved the performance of all of them including ones inserting rows. Yes, we do monitor page splits and adjust both fill factors and index maintenance schedules.
--Jonathan
January 16, 2004 at 4:00 pm
Ah, OK, I read it wrong, I thought you meant specifically for clustered on idents versus non-idents, thanks for replying
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply