question

Ravik_ITQC avatar image
Ravik_ITQC asked

SQL Server

Hi Team, What is the exact difference between Empty/Blank, NULL and Not Null database field. What is the memory allocation did for Empty/Blank , NULL and Not NULL Field. How Can I found what is default memory occupied by Empty and NULL field. Is there any Impact did on performance ?? Please Help me !! Thanks Ravik
sql memory consumption
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
The main impact on space would appear to be the actual datatype you're using to store these blanks / nulls. I've put together a script to compare char & varchar strings, both 1 & 1000 characters long, NULLable & non-NULLable, and storing NULL (where possible) and the empty string. The biggest of those was CHAR(1000) - the rest were much of a muchness in terms of data storage. (Note: this was an assortment of single-column tables, populated with half a million rows of data each, in SQL Server 2012, SQL Server 2008R2 and SQL Server 2005.)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
For every row, there is a bitmap stored for NULL columns that marks whether that column is NULL or not. A fixed length column, whether NULL or not, always has the same size. That means, even empty, as in passing an empty string ('' quotes with nothing in them) to a fixed length column, the length of data storage is the same, even when NULL. Variable length columns are different. If you have a variable length column and you store an empty string, no additional data is stored. But, if you store a NULL value, a two byte offset is stored to mark the values as NULL. NULL is a special case within SQL Server. The impact on performance for most of this is trivial in terms of storage and retrieval. That is, until you're dealing with very large amounts of NULL values and only a few actual values. Then, SQL Server 2008R2 and better has a construct called a [sparse column][1] which can help. But that's an edge case in most OLTP systems. It's primarily targeted at certain types of data warehousing structures. [1]: http://msdn.microsoft.com/en-us/library/ff427239.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.