Space used by nulls in database

by

Storing a NULL in a column does not specifically cost or save space. For fixed-length data, the entire space is still reserved.

On the other hand, variable-length data requires only the length of the data plus overhead to store the actual length. For example, a VARCHAR(n) will use 2 bytes of data to indicate that actual length, so the storage space required is always n+2.

Additionally, it should be mentioned that if SET ANSI_PADDING ON, a char(n) with a NULL value will behave as a VARCHAR(n).

No matter what, you will not recognize space “savings” from storing NULLs when using SQL Server 2000 or SQL Server 2005. SQL Server 2008 introduces the concept of a sparse column, which can yield savings for columns that are primarily NULL.

More Related Contents:

  1. Not equal != operator on NULL
  2. Why does NULL = NULL evaluate to false in SQL server
  3. How can I schedule a job to run a SQL query daily?
  4. What’s the best practice for primary keys in tables? [closed]
  5. Reset identity seed after deleting records in SQL Server
  6. How to create multiple one to one’s
  7. Generate a resultset of incrementing dates in TSQL
  8. T-SQL Cast versus Convert
  9. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  10. How would you implement sequences in Microsoft SQL Server?
  11. What does =* mean?
  12. Correct use of transactions in SQL Server
  13. Splitting the string in sql server
  14. Need to list all triggers in SQL Server database with table name and table’s schema
  15. How can I do a BEFORE UPDATED trigger with sql server?
  16. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  17. Favourite performance tuning tricks [closed]
  18. Deferrable Constraints in SQL Server
  19. How to check which locks are held on a table
  20. How to sum up time field in SQL Server
  21. Can SQL Server Express LocalDB be connected to remotely?
  22. Using SQL Server as a DB queue with multiple clients
  23. Suggestions for implementing audit tables in SQL Server?
  24. SQL Server search using like while ignoring blank spaces
  25. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  26. How do I change db schema to dbo
  27. Functions vs Stored Procedures
  28. SQL Inner Join On Null Values
  29. Executing SQL query on multiple databases
  30. Single or multiple databases

Leave a Comment