Primary Key - string or integer
in [Server Configuration]
|
Prev: A Question about SQL Server 2008 Installation
Next: Long execution time for UPDATE tbl SET @var = col = {expression} under SQL2000
From: Alan T on 8 Mar 2010 18:49 What do you thing how slow will that be if string is used as primary key field type? I know most of the primary key defined as integer type (eg.auto-increment). We are using string as primary key field type: VARCHAR(36). We use the GUID type string.
From: Eric Isaacs on 8 Mar 2010 21:08 > We use the GUID type string. If you're using a GUID, use the built-in data type of uniqueidentifier. It's a 16 byte value whereas INT is a 4 byte value. You'll take a slight hit, but UNIQUEIDENTIFIER is basically a numeric value, as you're comparing bit for bit, whereas with character data you have upper and lowercase characters to contend with that you won't with a UNIQUEIDENTIFIER data type. http://msdn.microsoft.com/en-us/library/ms187942.aspx If you have the need for a GUID, UNIQUEIDENTIFIER is the data type you should be using. Do not use VARCHAR(36). -Eric Isaacs
From: Plamen Ratchev on 8 Mar 2010 21:13 There will be performance difference. You have to test to see how much, but INT will be faster. INT data type takes 4 bytes for storage and on the other side VARCHAR(36) can take up to 40 bytes (up to 36 bytes for data + 2 bytes to keep track of the length + 2 bytes that each row that has any variable length column takes). -- Plamen Ratchev http://www.SQLStudio.com
From: Ole Kristian Bangås on 9 Mar 2010 13:57 As stated earlier, varhcar(36) will take up to 40 bytes of storage, that is up to 10 times the size of an int. Furhtermore, please note that primary keys tend to create a unique clustered index. Generally speaking GUIDs are not good candidate for clustered keys, as their values are random by nature. If you should still want to use a clustered index, keep in mind that each nonclustered index in the same table also contian the clustering key, that's right, your 36 byte varchar. So, as you have figured out now, there are quite a few pitfalls using strings or GUIDs as primary keys. Ole Kristian Bang�s "Alan T" <alan_NO_SPAM_pltse(a)yahoo.com.au> wrote in message news:uYKIKoxvKHA.812(a)TK2MSFTNGP06.phx.gbl... > What do you thing how slow will that be if string is used as primary key > field type? > > I know most of the primary key defined as integer type > (eg.auto-increment). > > We are using string as primary key field type: VARCHAR(36). > We use the GUID type string. >
From: TheSQLGuru on 9 Mar 2010 18:30
GUIDs are awful for a bunch of reasons and you should spend the effort to engineer them out of your system if at all possible. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Alan T" <alan_NO_SPAM_pltse(a)yahoo.com.au> wrote in message news:uYKIKoxvKHA.812(a)TK2MSFTNGP06.phx.gbl... > What do you thing how slow will that be if string is used as primary key > field type? > > I know most of the primary key defined as integer type > (eg.auto-increment). > > We are using string as primary key field type: VARCHAR(36). > We use the GUID type string. > |