none
Using UniqueIdentifier as Primary key RRS feed

  • Question

  • Hello,

    is it a good idea to set an UniqueIdentifier field as primary key ?

    why i can't set an indentity specification ?

    How can i do ?

    Thanks a lot

    Wednesday, February 15, 2006 2:19 PM

Answers

  • You can only specify an identity attribute for fixed precision data types and always returns an integer based value (i.e. non-decimal).  For example int, bigint, numeric, etc.

    As for if it's a good idea to use a uniqueidentifier as the primary key, that's always up for debate, but you definately can do it.  It will be unique, so that's good, and not only is it unique in the table, but also globally unique.  Depending on how you are indexing the given column, you will find that the index key generated is wide (16 bytes for a uniqueidentifier), so that would be a larger index to seek across than an integer based index for example. 

    There are some pretty good tips located here for more information as well:

    http://www.sql-server-performance.com/datatypes.asp

    HTH

    Wednesday, February 15, 2006 6:28 PM

All replies

  • You can only specify an identity attribute for fixed precision data types and always returns an integer based value (i.e. non-decimal).  For example int, bigint, numeric, etc.

    As for if it's a good idea to use a uniqueidentifier as the primary key, that's always up for debate, but you definately can do it.  It will be unique, so that's good, and not only is it unique in the table, but also globally unique.  Depending on how you are indexing the given column, you will find that the index key generated is wide (16 bytes for a uniqueidentifier), so that would be a larger index to seek across than an integer based index for example. 

    There are some pretty good tips located here for more information as well:

    http://www.sql-server-performance.com/datatypes.asp

    HTH

    Wednesday, February 15, 2006 6:28 PM
  • thanks a lot !
    Thursday, February 16, 2006 8:23 AM
  • If you are using SQL Server 2005 you could use the new NewSequentialID default.
    Thursday, February 16, 2006 5:32 PM
  • Boyd,

    I appreciate that it is open for debate, but can you give me an executive summary to the two sides or point me to where I can read the debate to make my own decision?

    You say "... but you definately can do it." but that kinda implies to me that you think it may not be wise.

    MCH
    Wednesday, March 22, 2006 6:39 AM
  • Bottom line is that you want you PK to be as small as possible because you use it to join all your tables together.

    Which is quicker examing a 16 byte value or a 4byte one? the 4 byte one.

    I would only use uniqueidentifier if you have to have globally unique key and you can't manage that with different identity ranges.

    Wednesday, March 22, 2006 11:51 PM
  •  

    I always use tinyint, smallint, int or bigint with identity(1,1) for primary keys depending upon how many rows the table will be holding.  This keeps searching quick, users can also easily remember an identity value, remembering 1256 is easier than quoting '6F9619FF-8B86-D011-B42D-00C04FC964FF' to a helpdesk for example.  The numeric data types explained above are also small in data size tinyint 1byte, smallint 2bytes, int 4bytes and bigint 8bytes.  When putting your application on top of the database it is also easier to use these numeric values with objects/components within the application rather than storing '6F9619FF-8B86-D011-B42D-00C04FC964FF' here there and everywhere.

     

    There will always be arguments for both sides however you need to look at three key areas, ease of use, data size and performance then make the decision based on how many boxes you've ticked with each solution.  My view is you will mainly come to the conclusion that the numeric data types explained above coupled with clustered index and an identity will usually win hands down over any other solution, however please do inform me if you have discovered a better resolution out there.

     

    Kind Regards

    MCTS SQL Server 2005

    Friday, January 18, 2008 10:56 AM
  • Refer to my blog for a best practice on using SQL Server uniqueidentifier as a primary key.

    Jeff Fischer

    http://twitter.com/scoftwaredev

    http://twitter.com/scoftware

    Wednesday, September 9, 2009 2:58 AM