SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Worst Practices - Not Using Primary Keys and Clustered Indexes


Worst Practices - Not Using Primary Keys and Clustered Indexes

Author
Message
LBWH4LSHH
LBWH4LSHH
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1
I agree with you.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61641 Visits: 19099
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
My Blog: www.voiceofthedba.com
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11379 Visits: 2730
You said it better than I tried to!

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
martinvg
martinvg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11379 Visits: 2730
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
baydata
baydata
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 5
Ok, so you have to have a clustered index..... but why!!!



Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11379 Visits: 2730
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
PeterLivesey
PeterLivesey
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 49
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
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11379 Visits: 2730
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
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1392 Visits: 82
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search