Sizeof NULL value of uniqueidentifier

Question

I am designing a table and thinking about sizeof null value of uniqueidentifier type. The table will have 10 such columns and they will be often NULL.
Will be the size 10*16 B, or (10 - count of NULL columns) * 16 B?

Maybe it would be better having rather columns of type varchar(32).

Answer 1

Hi @Jan Vávra
Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it's 0.
For variable size datatypes the acctual size is 0 bytes.
For fixed size datatype the acctual size is the default datatype size in bytes set to default value (0 for numbers, '' for chars).
In my opinion, uniqueidentifier is a fixed size datatype.
Check this sample:

declare @guid1 uniqueidentifier = null  
       ,@guid2 uniqueidentifier = '728B7419-6E97-4F54-B06B-536B31AE954E'  
	   ,@guid3 uniqueidentifier = '728B7419-6E97-4F54-B06B-536B31AE954E4578RT'  
select DATALENGTH(@guid1)  
      ,DATALENGTH(@guid2)  
	  ,DATALENGTH(@guid3)  

Best regards,
LiHong

Answer 2

As Li says, each value will take up 16 bytes, no matter if it's NULL or not. However, there is a twist: if you apply row compression, the NULL values will not take up any bytes beside the bit in the NULL bitmap.

If row compression is not an alternative to you for some reason, varbinary(16) may be a better alternative. I will have to admit that I cannot really decide what I think in this case. I would probably consider the complexity of programming. One thing to keep in mind is that if index these columns and you do:

DECLARE @v uniqueidentifier  
...  
SELECT ... FROM tbl WHERE indexedbincol = @v  

The rules of type precedence in SQL Server will convert the binary column to uniqueidentifier, which most likely will prevent an Index Seek. (I have not actually tested to verify.) So if you go for varbinary(16), you should so throughout your application.

Viorel is perfectly right in that varchar(32) is not an alternative.

Answer 3

Does this answer your question?

declare @guid1 uniqueidentifier = null, @guid2 uniqueidentifier = newid();  
select DATALENGTH(@guid1), DATALENGTH(@guid2)  

If a nullable column is null, then SQL Server stores this information in a table bitmask = 1 bit required for NULL values.

Answer 4

Let me ask the question, why are you using uniqueidentifier?

uniqueidentifier sounds good, but you need to understand it is really for cross server uniqueness. If you don't need that, you are much better off simply using a properly sized numeric and an IDENTITY field.

See:
https://www.mssqltips.com/sqlservertip/1600/auto-generated-sql-server-keys-with-the-uniqueidentifier-or-identity/