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 Saturday, October 20, 2001 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/worstpracticesnotusingprimarykeysandclusteredindex.asp>http://www.sqlservercentral.com/columnists/awarren/worstpracticesnotusingprimarykeysandclusteredindex.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #1364
Posted Sunday, October 21, 2001 11:29 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, July 31, 2007 8:20 AM
Points: 885, Visits: 1
Andy

Hi Andy, well Im not sure about this article and its validity in some areas.
My only real concern in the title of the article, I believe that is not "bad practice" NOT to use clustered indexes, in 6.5 days this may be the case re hot spots etc, but v7 onwards its not an issues. Worst still, I firmly believe its GOOD practice not to use clustered indexes, especially on identity column keys (which seem to be popular with sqlserver programmers rather than natural keys). As you can only create one clustered index over a table one should carefully plan the creation of the key to maximise performance. I beleive their is little performance gain on placing a clustered index over an identity column when a natural or other combo key will yield better performance and query optimiser hit rates. This is especially the case when the default pkey index is type clustered, which is bad practice on Microsoft's part!

What is bad practice, as you mentioned, is no p-keys.

Perhaps worth mentioning that f-keys should also be indexed. This is a common performance gain in oracle and holds true in most dbms's. Funny enough, many people forgot them and end up indexing later on some strange concatenation of columns that the optimizer cant used in joins.

Cheers

Chris




Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Post #22658
Posted Monday, October 22, 2001 12:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 24, 2001 12:00 AM
Points: 8, Visits: 1
you mention about overhead, i am working with SQL in the web development enviroment.
i use very small tables smallest 2 rows upto say 20 rows to allow dynamic updating of drop down/lists within the web site [this puts the power of the intranet content in the approprate users hands]. (i hope all unstander what i am doing). The use of indexing (i belive) is not so valid within this situation, due to the overhead one the system.

If i am incorrect in my assumption please can someone correct me (so i can improve my work)




Post #22659
Posted Monday, October 22, 2001 2:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 21, 2014 5:45 AM
Points: 12, Visits: 150
I would absolutely agree that every table should have a primary key.

I would strongly disagree with your recommendation that a "damn good starting point is to just add a identity column called rowid and make it the primary key and the clustered key". I would consider this a bad practice. Rather, we should examine what attributes we are storing to find the "real primary key" and alway use that. I would only suggest identity columns as a last resort, in cases where there are no unique attributes that are a candidate key - the typical example being a Customer table, where we would add an artificial CustomerID. I wouldn't go as far as saying never use identity columns, but using them as a matter of course is a bad idea. I doubt you will get any agreement on identity columns though - it seems to be one of those issues that always causes arguments.

Cheers,
Andy Mackie




Post #22660
Posted Monday, October 22, 2001 3:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 12, 2001 12:00 AM
Points: 3, Visits: 1
I found this article very useful, but I think that it goes a little too far. I have seen a few comments which I agree with regarding the default use of clustered indexes which of course can be a source of performance problems when used inappropriately, but I thoroughly agree with the gist of the article.

I would have thought (other than tables with a single lookup row, typically used for global system values) any table that cannot be keyed in some form is more a sign of bad design. How can you single out a particular row of a table (say for deletion?) if you cannot uniquely identify it?






Post #22661
Posted Monday, October 22, 2001 4:58 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934
Thanks to all for their comments so far! Let me address a couple of points:

Chris - I agree that placing the clustered index on the identity col itself won't render great gains. Remember, I didn't say that was a best practice, just a better than nothing practice. Having a clustered index anywhere in the table affects how other indexes are built/used, I believe better to have one than not. Sure it would be better used on a "perfect" key - but if they could do that, they wouldn't be in the WP category to start with! I'll have to think in your fkey argument - hard for me to disagree, but should it be a worst practice?

Dood - I addressed this to some extent in the article. Indexes arent always about speed, but data integrity (as constraints). Putting the index in place is also good planning for future growth. Aside from a primary key though, additional indexes should be used as needed only. If you KNOW it'll always be a 50 row state lookup table (or 51, or 52), then I'd agree no additional indexes required.

Andy - Hey, I hear you man - if you've got a good candidate key, use it! My goal is to get people thinking about the worst thing they can do. If all your developers build tables that always have a primarykey and always have a clustered index they've at least taken a step in the right direction. My hope would be that no table gets deployed without a DBA reviewing first - that's a great time to do some mentoring if they've created a fake pkey when a real one exists. One day we'll have to tackle the identity col issue just for fun!

Skelband - I agree a bad clustered index will cause you problems. Identity will never be one of those though. It's an "ok" plan at best, just better than nothing. For the second part, I agree - though in practice there is nothing that says you or your app can't be responsible for maintaining uniqueness rather than the db.

To everyone: The tough part about these articles is that identitying a worst practice gets you thinking about good, better, best practices. My thought was to present at least "ok" alternatives that a user could implement without a lot of work or knowledge. Is that going to do more harm than good? Leave that for the ensuing discussion? If you gave this to a new/junior programmer to read one week for professional development, would you/they be better off for having read it? Would they learn one bad habit to replace another?

Andy





Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22662
Posted Monday, October 22, 2001 9:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 10, 2009 4:18 PM
Points: 1,287, Visits: 11
Hello all,

Andy, I liked your article. I think it is important to have PKs on tables in order to maintain data integrity. I also agree with the individual who said that if a "natural key" existed, then you should use it instead of an identity field. Personally, I don't like identity fields. They get me into trouble more times than not.

I also remember a point about indexes that I heard at a conference. We as DBAs and developers try to achieve, or at least should, normalization in our databases. Every time you add an index, it denormalizes your data.

I think a worst practice that should be addressed is over use of indexes. I have a developer that insists on putting an index on every column of every table in her database. On a 3 GB database, that's a lot of extra space that could be used elsewhere!

My 2 cents,
Jason




Post #22663
Posted Monday, October 22, 2001 10:01 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934
Hey Jason,

I've heard that argument about "denormalizing" your data. It's certainly true, but I think misleading - the biggest point being that you don't have to worry about keeping the data in sync, SQL does it all for you very fast and efficiently.

I know what you mean about too many indexes, seems its always too many or too few, never just right! Do you think we expect too much? What if we told developers - index the primary key, index all foreign keys, index any column that MUST be unique - and leave the rest to us!

Andy






Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22664
Posted Monday, October 22, 2001 1:24 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 10, 2009 4:18 PM
Points: 1,287, Visits: 11
quote:

I know what you mean about too many indexes, seems its always too many or too few, never just right! Do you think we expect too much? What if we told developers - index the primary key, index all foreign keys, index any column that MUST be unique - and leave the rest to us!

Andy



Andy,

I think at a minimum you should index the primary keys and the foreign keys. Also, I would create an index on a unique field if it were going to be used in other relationships. Start with this and monitor the performance of the database. If you see that performance is slipping on a column that is not part of any index, then you may consider adding an index for that column. This is where SQL Profiler and the Index Tuning Wizard become a DBA's friend!

Jason




Post #22665
Posted Monday, October 22, 2001 1:46 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934
Sure, but these articles don't target DBA's as much as the people the DBA's support. I don't expect (or want) developers spending time with the tuning wizard or profiler. As I said earlier, any discussion of worst practices leads you into a discussion of "well, what should I do" - and its just not always that simple! But it does serve as a good way to start a discussion so that YOU can help them grow.

I'm not complaining about your comments - I appreciate them!

Andy



Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #22666
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse