Post reply

UniqueIdentifiers as Indexes

  • November 14, 2007 at 12:09 am

    #184205

    Hi all

    We have an existing database which contains uniqueidentifiers as primary keys for all the tables ( rowguidid = false, thou). I've noticed someone have added a lot of non-clustered indexes on certain tables, especially the big tables containing more that 18 000 000 records. Some have tables also have composite keys.

    I have wondered what would the best idea be to optimize indexes on uniqueidentifier columns. Also how would sql handle indexes on uniqueidentifiers?

    According to this article http://qa.sqlservercentral.com/articles/Data+Types/uniqueidentifierusageandlimitations/1406/

    using guid's was a very bad idea, since no replication happens.

  • November 14, 2007 at 1:29 am

    #750982

    My recomendation would be to find a more appropiate column for the clustered index. Which column it is will depend on how the tables are used.

    The problems with guids as clusterd indexes are two-fold. Firstly, since guids are random, inserts cause page splits, resulting in fragmentation of the index. I tave a table that used to have the cluster on a guid column and it could reach 99.9% fragmentation in 5 days.

    The second prolem is that guids are quite wide (16 bytes) and since the clustering key is present in all nonclustered indexes, this can make the nonclustered indexes larger than necessary.

    Also, since people don't often do range queries on guide, you're not taking advantage of one of the larger uses of the clustered index (range queries and sorting)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 14, 2007 at 5:52 am

    #751052

    For the ordering and fragmentation problem, SQL 2005 has a function NEWSEQUENTIALID() that can be used in a default to solve this issue.

    If you have a big, multi-server environment and you are sharing data and updating in multiple places, I can see GUIDS being a good way to go for primary keys. They are really nice for merge replication. For most situations, I tend to avoid them.

  • November 14, 2007 at 10:05 pm

    #751365

    Guess in all it sounded like the Guid options was a poor dissicion for the design teams side. Thanks for the replies thou.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply