GUID: varchar(36) versus uniqueidentifier

Question

I'm working with a legacy database that stores GUID values as a varchar(36) data type:

CREATE TABLE T_Rows (
    RowID    VARCHAR(36) NOT NULL PRIMARY KEY,
    RowValue INT         NOT NULL
) 

INSERT T_Rows (RowID, RowValue) VALUES (NEWID(), 1)

I would assume that storing a GUID as a uniqueidentifier would be preferable since it's only 16 bytes in size as opposed to 36.

Are there any advantages to storing a GUID as a varchar?

Solution 1

Perhaps only the fact that you can 'read' them from a SELECT statement (although I don't think that's particularly useful as you can use a function in a select to make Uniqueidentifiers displayable).

If the table is large, saving 20 bytes per row is considerable.

Solution 2

I would go with uniqueidentifier for many reasons such as,

it will take less space; it's unique so it can not be duplicated. It's much better for comparisons and specially performance related issues as well as easy to get unique default value etc.

I would use uniqueidentifier unless I need to use varchar for very specific reason.

Solution 3

If your database is Oracle then the performance of indexes for raw data in older version of Oracle (9) was much, much poorer than indexing a varchar(36) field. Luckily this has changed in Oracle 10 and 11.

Solution 4

I believe UNIQUEIDENTIFIER was added in SQL Server 2000, so it's possible this application was originally written for SQL Server 7, which didn't support it. But that's just a guess, of course...

View more solutions

Comments

[dburges]

YOu'll gain more than that, this is the PK, so if there are any child tables they too will have cost savings. If there are any other indexes onthe table and this ithe clustered index, the size of all indexes (and possibly the speed but test to be sure) will be smaller. Of coursre and INT is better as a PK for many many reasons but that is a huge change in a legacy database.

[code5]

If I may add. It's also better when working with federated databases. I would go guids as stated.

[Zapnologica]

why does entity-framework use nvarchar(128) for its GUIDs then? Can I just swap that out for uniqueidentifier?