How much size “Null” value takes in SQL Server

by

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.

More Related Contents:

  1. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  2. Check if table exists in SQL Server
  3. Sql Server string to date conversion
  4. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  5. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  6. Parameter Sniffing (or Spoofing) in SQL Server
  7. How to get Time from DateTime format in SQL?
  8. SQL Server String or binary data would be truncated
  9. Datatype for storing ip address in SQL Server
  10. How to change identity column values programmatically?
  11. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  12. Altering a column: null to not null
  13. best way to convert and validate a date string
  14. SQL Server ORDER BY date and nulls last
  15. How to remove accents and all chars a..z in sql-server?
  16. Find non-ASCII characters in varchar columns using SQL Server
  17. WHERE clause on SQL Server “Text” data type
  18. SQL Server error on update command – “A severe error occurred on the current command”
  19. Aggregate bitwise-OR in a subquery
  20. SQL server schema and default schema
  21. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  22. FOR XML PATH(”): Escaping “special” characters
  23. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  24. Does query plan optimizer works well with joined/filtered table-valued functions?
  25. What are row, page and table locks? And when they are acquired?
  26. @@ERROR and/or TRY – CATCH
  27. SQL Server – error when creating stored procedure
  28. Not equal != operator on NULL
  29. How to split string using delimiter char using T-SQL?
  30. SQL Server stored procedure return code oddity

Leave a Comment