Worst Practices - Not Using Primary Keys and Clustered Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Worst Practices - Not Using Primary Keys and...

185 posts, Page 2 of 19

««1 2 3 4 5 »»»

Worst Practices - Not Using Primary Keys and Clustered Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

LBWH4LSHH

Posted Tuesday, October 23, 2001 7:54 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 05, 2003 12:00 AM
Points: 8, Visits: 1

I agree with you.

Post #22667

Steve Jones - SSC Editor

Posted Thursday, October 25, 2001 4:55 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

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

Andy Warren

Posted Thursday, October 25, 2001 7:38 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

You said it better than I tried to!

Andy

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22669

martinvg

Posted Monday, October 29, 2001 4:05 AM

Forum 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

Andy Warren

Posted Monday, October 29, 2001 5:44 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22671

baydata

Posted Tuesday, November 06, 2001 4:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, August 01, 2010 5:53 AM
Points: 2, Visits: 5

Ok, so you have to have a clustered index..... but why!!!

Post #22672

Andy Warren

Posted Tuesday, November 06, 2001 5:51 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22673

PeterLivesey

Posted Thursday, January 24, 2002 8:04 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, June 28, 2012 5:12 AM
Points: 34, Visits: 36

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

Andy Warren

Posted Thursday, January 24, 2002 1:29 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22675

Robert W Marda

Posted Thursday, January 24, 2002 3:40 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48

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 »

185 posts, Page 2 of 19

««1 2 3 4 5 »»»

Permissions