Diving deep into SQL-Server 2005

You may also like...

3 Responses

  1. Dennis, this has nothing to do with Sql 2005.
    All the things you mention are valid for almost every database.

    Therefore there is nothing new about it…..

    You can see the SQL Server performance Website for very good explanations of the do’s and dont’s.

    However I am GLAD at least 1 developer is staying away from the GUID’s in the future…
    ( have a look at my blog http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx
    Read the comments and read who posted them…( you should know some of them)
    Then RE-Evaluate those persons with this new knowledge….)

    Patrick W.

  2. Patrick,
    I’ve read your blogpost about ‘guid is not always good’.
    I agree with you that you should not put a clustered index on a GUID column. However, this does not mean that you never should use a GUID as primary key. As long as your primary key GUID is not a clustered index, I think there’s nothing wrong with it.

    I also think that the statement that is in the BoL (haven’t verified it) should be ‘a primary key creates a clustered index by default’, instead of automatically. You can always override this clustered option.

    The biggest problem of using a GUID as a clustered primary key index, is, that a clustered index determines the fysical order of your records. Since a GUID has no logical incrementing sequence, this means that with every insert, your clustered index will have to be rebuilt, and since the clustered index determines the way the records are ordered within that table, those records will have to be ‘repositioned’ as well. That’s the primary reason why a GUID can detoriate the performance.
    So, I would say, if you use a GUID, be sure that you *never* put a clustered index on that GUID-column.

  3. Well if you have read the ENTIRE postings…
    somebody put a script there showing that guids WILL affect the search times as well ( i dont recall exactly but about 35 % faster if you use int), this is completely logical since a guid is 4 times as big as a int.

Click on a tab to select how you'd like to leave your comment

Leave a Reply

Your email address will not be published. Required fields are marked *