CODEWITHSUNDEEP
sql-server
user3568783
user3568783

Reputation:

Is there any advantage to storing a GUID as a UNIQUEIDENTIFIER?

My code has this as a default value for a column in a table:

 [QuestionUid]      UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,

Having it as a UNIQUEIDENTIFIER is I guess the correct way to do this but what if I store it as a VARCHAR(50). That would make it a bit simpler when working with my C# code as right now I am getting some errors about type mismatch.

Upvotes: 1

Views: 2743

Answers (3)

Ben Thul
Ben Thul

Reputation: 32687

How big do you plan on your table being? I ask because a UNIQUEIDENTIFIER takes only 16 bytes while your varchar(50) will take up a lot more. While we're talking about that, why varchar(50)? A guid will always be the same length, so no need for a variable length at all; char(40) would do just fine. Either way, you're taking up more than three times the space for using the wrong data type. That's all space that is wasted in the buffer pool, has to be maintained during index maintenance, wasted in every index that involves this column, etc. So can you do it? Sure. Would I do it? No.

Upvotes: 3

TcKs
TcKs

Reputation: 26632

The Guid (uniqueidentifier) is 128 bit number. It can be easily indexed like other number types (int/bigint/etc...).

Storing GUID as varchar(50) is bad decision like storing integers in varchar.

Upvotes: 4

russ
russ

Reputation: 624

The uniqueidentifier data type is used in merge replcation where the ROWGUIDCOL property is assigned to a column of this datatype. This eliminates row duplication when data is being merged between database servers.

It is ok to use the varchar(50)...char(36) datatype because, when it comes to merge replication, if there is no column with the uniqueidentifier datatype, SQL Server will add its own column with this datatype.

The only issue might be that the table would then have two columns of uniqueidentifiers! This could be averted if you change the datatype to uniqueidentifier at the point of merge replication. Then SQL Server could assign ROWGUIDCOL to this column.

This doesn't necessarily help very much with your C# project but it might be an incentive to try a bit harder to make the C# project work with uniqueidentifier. I don't know C# but I feel sure it must have the facility to deal with this.

Upvotes: 0

Related Questions