AngryPets Blog

WARNING: GUIDS aren't as big as you think..

If you've spent anytime looking at a 'size chart' for SQL Server Data Types, you know that the GUID datatype weighs in at a hefty 16 bytes. That's an awfully large column, compared to a smallint, which covers all integers between -32,768 and 32,767 -- and only costs 2 bytes.

However, don't forget that despite its seemingly large size, it's still a numeric datatype internally. In other words, don' t fall under the assumption that storing it as a char/varchar would be comparative in terms of size. Here's a quick test I threw together that shows what I mean:

IF OBJECT_ID('gt_guid') IS NOT NULL DROP TABLE gt_guid
IF OBJECT_ID('gt_vchr') IS NOT NULL DROP TABLE gt_vchr
IF OBJECT_ID('gt_char') IS NOT NULL DROP TABLE gt_char

SET NOCOUNT ON

CREATE TABLE dbo.gt_guid 
( gID int NOT NULL IDENTITY(1,1),
 guid uniqueidentifier NOT NULL )

CREATE TABLE dbo.gt_vchr
( gID int NOT NULL IDENTITY(1,1),
 guid varchar([n]) NOT NULL )

CREATE TABLE dbo.gt_char
( gID int NOT NULL IDENTITY(1,1),
 guid char([n]) NOT NULL )

DECLARE @loop int
SET @loop = 0
WHILE @loop < 5000 
   BEGIN
      INSERT INTO gt_guid VALUES(NEWID())
      SET @loop = @loop +1
   END

INSERT INTO gt_vchr
SELECT CAST(LEFT(g.guid,[n]) AS varchar([n])) FROM gt_guid g

INSERT INTO gt_char
SELECT CAST(LEFT(g.guid,[n]) AS char([n])) FROM gt_guid g

SET NOCOUNT OFF
EXEC sp_spaceused gt_guid
EXEC sp_spaceused gt_vchr
EXEC sp_spaceused gt_char

Where [n] is the size of the char/varchar datatype being targeted. The results below tell the whole story (which is that storing a GUID as a GUID isn't nearly as bad as you think... it's effectively only the size of a char/varchar (10) field).

Existing Comments:

Add your own comment: