Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Worst Practices - Not Using Primary Keys and Clustered Indexes Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2001 7:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 5, 2003 12:00 AM
Points: 8, Visits: 1
I agree with you.




Post #22667
Posted Thursday, October 25, 2001 4:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:38 PM
Points: 31,018, Visits: 15,453
I know, I'm late. I've been meaning to read this all week, but I kept putting it off.
OK, here it goes.

I think Andy has done a great job pointing out some things. I totally agree that something is better than nothing and if you have no clue, then make an identity field and add a clustered index. No matter what. I thought I had a reference that shows where a clustered index is better than a heap in terms of performance, but I can't find it. If I do I'll post it.

Not having a primary key is a sin. Not having an intelligent PK is not as bad, but doesn't excuse the developer. Andy's point to this article (I think ), is that this is a Worst Practice, not a recommended practice. He is showing the lowest level of something that should be done.



Steve Jones
steve@dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #22668
Posted Thursday, October 25, 2001 7:38 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
You said it better than I tried to!

Andy



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22669
Posted Monday, October 29, 2001 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 28, 2003 12:00 AM
Points: 7, Visits: 1
I have to react to your opinion. I can go along with the part about every table having a UNIQUE index, however, I consider it bad practice to use IDENTITY columns to define these on. If each table is in 3rd normal form or Boyd-Codd Normal form (BCNF) the unique value can be constructed from one or more columns in the data itself. I NEVER use identity columns myself. As far as clustered indexes go, I have made it a rule never to use these either. Yhe reason for this is not a theological one, as in the previous example, but a practical one.
Consider an order-processing schema. Would you make the order#, the orderdate or the customer# the clustered index? In the first two cases you will have created your own contention problems, since you wil have forced INSERT statement on the table to take place at the end of this index. Now I know MSSQLServer 7.0+ is supposed to have INSERT-ROW-Level-locking capabilities, but not every other RDBMS may have tese qualities, and after all, we're not building platform-specific solutions, are we? Leaves us with the option of using some widely-distributed value (at INSERT-time) to put the index on, like customer#. I'd go along with doing so, however in my practice over the last 10 years or so, I have found out that the gains (in performance cost) do not outweigh the efforts.

Mvg, MvG.



Mvg, MvG.
Post #22670
Posted Monday, October 29, 2001 5:44 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
Use of identity columns (or any other contrived key) certainly has it's foes - and I'll agree that each side has it's proponents. What I tried to do..and evidently failed...was to target not users like yourself who have some experience in this arena, but to point out to users who DON'T know, understand, etc...that failing to use a primarykey is horrible. Should I explain the differing views on contrived keys? While maybe not doing so left a gap in the article, my thought was (and is) that for developers/DBA's making the worst mistakes don't try to change it all in a day, try to get them moving along the path to doing things better ways. If you're not seeing worst practices, you're working in a better environment than I do!

Clustered keys now, I'll have to disagree. Platform independence is the holy grail I think, and seldom reached. Clustered keys are a MAJOR part of SQL, to not use them....I just don't see that. In your example, I'd look for a better column to use first - often a date column, but in any case one where I'd want to return the results sorted. Failing to find that, I'd probably use the primary key as the clustered key. The only time I've had issues with contention on clustered indexes was when they were frequently being updated. I'd encourage you to revisit this issue, you may find performance gains.

Thanks for your feedback, I hope you'll continue to participate and offer your opinions - as you can see from this discussion, you're not the only one who disagrees with me!

Andy





Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22671
Posted Tuesday, November 6, 2001 4:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 1, 2010 5:53 AM
Points: 2, Visits: 5
Ok, so you have to have a clustered index..... but why!!!






Post #22672
Posted Tuesday, November 6, 2001 5:51 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
In over simplified terms, clustered indexes are both more effective for range searches and they increase the effective of your other indexes to some degree. Look for an article in the next few weeks.

Andy



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22673
Posted Thursday, January 24, 2002 8:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 8:15 AM
Points: 34, Visits: 43
I am not a DBA myself but a developer who has spent a lot of time studying, database
design.
Not all Companies have a DBA, my experience is that most SME's just allow developers to develop databases and if it works that is fine.
From adeveloper point of view I would totally agree with the points made about Natural keys as I am constantly tearing my hair out at the databases I come accross that have an identity as the key when a natural key exists. The worst practice I have found is placing an identity column on a many 2 many resolution table and making it the PK rather than having a compound key.

I have also found that the use of identity keys can cause no end of problems when you wish to perform a simple task such as importing data from another database of the same design. I have found myself running queries that add 200 to every key in the app having already removed all constraints and identity settings and then importing the data with identity override on.
I am very pleased to see articles tat are aimed at developers as well as DBA's
but would take issue with encouraging the use of identity fields.




Mr Peter Livesey
Post #22674
Posted Thursday, January 24, 2002 1:29 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Monday, September 15, 2014 9:16 AM
Points: 6,784, Visits: 1,895
Identity cols are often the subject of relentless debate. To me its like any other tool - use it properly and its great, abuse it and it hurts. I use idents a lot, though in recent months I've begun converting to uniqueidentifers in a lot of cases and have been pleased with the results - the biggest difference being I can generate a unique on the client - amazing how much simpler life becomes.



Andy


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22675
Posted Thursday, January 24, 2002 3:40 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
I like your article and believe that every table should have a primary key and that it won't hurt every table if it has a clustered index. Almost all our tables have primary keys (although some of that was forced upon us since you can't set up transactional replication on a table without a primary key.) Many of our tables have clustered indexes and we keep adding them to tables that don't have them. They help a lot to speed up slow running queries.

We use identity columns a lot as primary keys and they seem to work fine. One of the people I learned from said that joins are faster using int columns then with varchar and since we do a lot of joins on tables with a one to many relationship this seems to be fine.

Robert Marda




Robert W. Marda
SQL Programmer
Ipreo
Post #22676
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse