Guid vs Identity columns (Ints)

SQL Studies

I came across an interesting question on SE last week. Guid vs INT – Which is better as a primary key? In addition to the quite good accepted answer I thought I would throw in my own take.

  • Size
    • GUIDs are 16 bytes and hold more values you then could ever use.
    • With an identity column you can choose a data type dependent on your need.
      • tinyint 1 byte 0-255
      • smallint 2 bytes -2^15 (-32,768) to 2^15-1 (32,767)
      • int 4 bytes -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
      • bigint 8 bytes -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)

    Remember that the size of your column affects not just how much space the table takes up but how many pages (both index and data) need to be read to perform a given operation. Bigger the column the less you can fit in a page, the more pages need to be read, the slower your queries…

View original post 458 more words

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s