CODEWITHSUNDEEP
sqlsql-servert-sql
ahmd0
ahmd0

Reputation: 17293

T-SQL performance penalty of using UNIQUEIDENTIFIER vs. INT

If I needed to unqiuely identify rows in a T-SQL database table I would set them as:

[id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY

But if I need ID to be unique among several databases (on different computers) I go with:

[uid] UNIQUEIDENTIFIER NOT NULL

and then set an index on that column:

CREATE INDEX index_name ON [TableName] ([uid]);

So I was wondering how much of a performance hit would the database take by changing INT to UNIQUEIDENTIFIER?

Upvotes: 0

Views: 2740

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

GUID is not the only option for surrogate values to be unique across databases. Two options come to mind immediately:

  1. Create your own hashing. Use a BIGINT and set the identity seed on each database, starting with, say, 1000000000 on database A, 2000000000 on database B, etc. This supports many, many databases.
  2. Have a central sequence generator that just bumps up a central identity column and gives the new id back to be used in the insert. Scale and concurrency might be more of a factor here, but only at the high end I'd suspect.

In SQL Server 2012, you will be able to implement (2) much easier using SEQUENCE.

Upvotes: 5

Related Questions