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.
elementary set theory - What's the difference between a null set and an empty set? - Mathematics Stack Exchange
Sizeof NULL value of uniqueidentifier
Understanding difference between 0 and NULL
SIZE OF NULL COLUMN – SQLServerCentral Forums
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).
In analysis and measure theory, the term null set is also used to denote a set which has "size" zero, but in that case, size means a different thing. For instance, on the real line, it is customary to use length (at least in naïve settings). So the interval 
has size 
and the set 
has size 
.
In this setting, examples of non-empty null-sets are:
- Single-element sets, like
. - In fact, any countable set, like
- The Cantor set
So whether there is a difference between the phrases "empty set" and "null set" depends entirely on the context.
This depends on the context.
In the context of set theory, the null set is the empty set. And that's the end of it.
In the context of measure theory, analysis, or probability, a null set is a set whose measure is 
. For example in the usual Borel measure, finite sets are null sets; countable sets are null sets; and even some uncountable sets (e.g. the Cantor set) are null sets. But they are certainly not empty.
In that context, a null set is a set which is completely uninteresting "for practical purposes" and we can ignore safely ignore it if we choose to. So this statement is more general than just "empty".
Note, however, that if you define an equivalence relation "
if and only if 
is a null set", then the null sets are exactly those equivalent to the empty set.
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.
0 being an int like other integers, sizeof(0) will yield 4 bytes.
sizeof(NULL) will yield 8 bytes. In binary system, it is 8x8=64 bits, all bits with 0.
Pointers have 8 bytes allocated against characters with 1 bytes and integers 4 bytes. Is 8 bytes the maximum bytes for any datatype? I believe so as NULL is set to 8 bytes apparently for that reason to take care NULL denotes 0 for all datatypes.
In your example myApple has the special value null (typically all zero bits), and so is referencing nothing. The object that it originally referred to is now lost on the heap. There is no way to retrieve its location. This is known as a memory leak on systems without garbage collection.
If you originally set 1000 references to null, then you have space for just 1000 references, typically 1000 * 4 bytes (on a 32-bit system, twice that on 64). If those 1000 references originally pointed to real objects, then you allocated 1000 times the size of each object, plus space for the 1000 references.
In some languages (like C and C++), pointers always point to something, even when "uninitialized". The issue is whether the address they hold is legal for your program to access. The special address zero (aka null) is deliberately not mapped into your address space, so a segmentation fault is generated by the memory management unit (MMU) when it is accessed and your program crashes. But since address zero is deliberately not mapped in, it becomes an ideal value to use to indicate that a pointer is not pointing to anything, hence its role as null. To complete the story, as you allocate memory with new or malloc(), the operating system configures the MMU to map pages of RAM into your address space and they become usable. There are still typically vast ranges of address space that are not mapped in, and so lead to segmentation faults, too.
The answer depends on the language you're using.
C/C++
In C and C++, the keyword was NULL, and what NULL really was was 0. It was decided that "0x0000" was never going to be a valid pointer to an object, and so that is the value which gets assigned to indicate that it is not a valid pointer. However, it's completely arbitrary. If you attempted to access it like a pointer, it would behave exactly like a pointer to an object which no longer exists in memory, causing a invalid pointer exception to be thrown. The pointer itself occupies memory, but no more than an integer object would. Hence, if you have 1000 null pointers, it is the equivalent of 1000 integers. If some of those pointers point to valid objects, then the usage of memory would be the equivalent of 1000 integers plus the memory contained in those valid pointers. Remember that in C or C++, if a pointer no longer points to its object, that does not imply memory has been released, so you must explicitly delete that object using dealloc (C) or delete (C++).
Java
Unlike in C and C++, in Java null is merely a keyword. Rather than managing null like a pointer to an object, it is managed internally and treated like a literal. This eliminated the need to tie in pointers as integer types and allows Java to abstract away pointers entirely. However even if Java hides it better, they are still pointers, meaning 1000 null pointers still consume the equivalent of 1000 integers. Obviously when they point to objects, much like C and C++, memory is consumed by those objects until no more pointers reference them, however unlike in C and C++, the garbage collector picks up on it on its next pass and frees up the memory, without requiring that you have to keep track of what objects are freed up and which objects are not, in most cases (unless you have reasons to weakly reference objects for example).