If the field is fixed width storing NULL takes the same space as any other value - the width of the field.
If the field is variable width the NULL value takes up no space.
In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.
The reason for the discrepancies that you have observed in information from other sources:
The start of the first article is a bit misleading. The article is not talking about the cost of storing a NULL value, but the cost of having the ability to store a NULL (i.e the cost of making a column nullable). It's true that it costs something in storage space to make a column nullable, but once you have done that it takes less space to store a NULL than it takes to store a value (for variable width columns).
The second link seems to be a question about Microsoft Access. I don't know the details of how Access stores NULLs but I wouldn't be surprised if it is different to SQL Server.
If the field is fixed width storing NULL takes the same space as any other value - the width of the field.
If the field is variable width the NULL value takes up no space.
In addition to the space required to store a null value there is also an overhead for having a nullable column. For each row one bit is used per nullable column to mark whether the value for that column is null or not. This is true whether the column is fixed or variable length.
The reason for the discrepancies that you have observed in information from other sources:
The start of the first article is a bit misleading. The article is not talking about the cost of storing a NULL value, but the cost of having the ability to store a NULL (i.e the cost of making a column nullable). It's true that it costs something in storage space to make a column nullable, but once you have done that it takes less space to store a NULL than it takes to store a value (for variable width columns).
The second link seems to be a question about Microsoft Access. I don't know the details of how Access stores NULLs but I wouldn't be surprised if it is different to SQL Server.
The following link claims that if the column is variable length, i.e. varchar then NULL takes 0 bytes (plus 1 byte is used to flag whether value is NULL or not):
- How does SQL Server really store NULL-s
The above link, as well as the below link, claim that for fixed length columns, i.e. char(10) or int, a value of NULL occupies the length of the column (plus 1 byte to flag whether it's NULL or not):
- Data Type Performance Tuning Tips for Microsoft SQL Server
Examples:
- If you set a
char(10)toNULL, it occupies 10 bytes (zeroed out) - An
inttakes 4 bytes (also zeroed out). - A
varchar(1 million)set toNULLtakes 0 bytes (+ 2 bytes)
Note: on a slight tangent, the storage size of varchar is the length of data entered + 2 bytes.
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
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.
A string literal is an array of characters* (with static storage), which contains all the characters in the literal along with a terminator. The size of an array is the size of the element multiplied by the number of elements in the array.
The literal "" is an array that consists of one char with the value 0. The type is char[1], and sizeof(char) is always one; thereforesizeof(char[1]) is always one.
In C, NULL is implementation-defined, and is often ((void*)0). The size of a void*, on your particular implementation, is 4. It may be a different number depending on the platform you run on. NULL may also expand to an integer of some type of the value 0, and you'd get the size of that instead.
*A literal is not a pointer, arrays are not pointers, pointers do not play a role in this part of the question.
The empty string "" has type char[1], or "array 1 of char". It is not a pointer, as most people believe. It can decay into a pointer, so any time a pointer to char is expected, you can use an array of char instead, and the array will decay into a pointer to its first element.
Since sizeof(char) is 1 (by definition), we therefore have sizeof("") is sizeof(char[1]), which is 1*1 = 1.
In C, NULL is an "implementation-defined null pointer constant" (C99 §7.17.3). A "null pointer constant" is defined to be an integer expression with the value 0, or such an expression cast to type void * (C99 §6.3.2.3.3). So the actual value of sizeof(NULL) is implementation-defined: you might get sizeof(int), or you might get sizeof(void*). On 64-bit systems, you often have sizeof(int) == 4 and sizeof(void*) == 8, which means you can't depend on what sizeof(NULL) is.
Also note that most C implementations define NULL as ((void*)0) (though this is not required by the standard), whereas most C++ implementations just define NULL as a plain 0. This means that the value of sizeof(NULL) can and will change depending on if code is compiled as C or as C++ (for example, code in header files shared between C and C++ source files). So do not depend on sizeof(NULL).