Space – NULLs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Space – NULLs

I have read somewhere on the net that null values for a varchar column occupy 1 byte per column. eg: tblAttribute
=============
AttributeId INT NOT NULL
AttributeName VARCHAR(100) NOT NULL
Value VARCHAR(100) NULL If I leave the value column in the above table as NULL, will it still consume some space. (I know if it is a CHAR then it does). Thanks
What would be count of records on this table?
For information refer to this topichttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=3289&SearchTerms=NULL If it is possible that null values may be stored in your data, it is a good idea to create queries and data-modification statements that either eliminate NULLs or transform NULLs into some other value (if you do not want null values appearing in your data). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks for your response Satya. The number of rows in this table could be in millions.
Well…. The following test shows that for 100,000 rows you save a 500kb, so it’s 5mb for a million That’s a savings by having null instead of 1 byte char default value in a varchar. USE Northwind
GO SET NOCOUNT ON
Go CREATE TABLE myTable99(Col1 int, Col2 varchar(100))
GO
CREATE TABLE myTable00(Col1 int, Col2 varchar(100) NOT NULL)
GO DECLARE @x int
SELECT @x = 1 WHILE @x < 100000
BEGIN
INSERT INTO myTable99(Col1) SELECT @x
INSERT INTO myTable00(Col1,Col2) SELECT @x, ‘x’
SELECT @x = @x + 1
END
GO sp_spaceused myTable99
GO
— name rows reserved data index_size unused
— ——————————————————————————————————————————– ———– —————— —————— —————— ——————
— myTable99 99626 1416 KB 1352 KB 8 KB 56 KB sp_spaceused myTable00
GO
— name rows reserved data index_size unused
— ——————————————————————————————————————————– ———– —————— —————— —————— ——————
— myTable00 99993 1928 KB 1872 KB 8 KB 48 KB SET NOCOUNT OFF
GO SELECT COUNT(*) AS myTable99_Rows FROM myTable99
GO
— myTable99_Rows
— ————–
— 99999 SELECT COUNT(*) AS myTable00_Rows FROM myTable00
GO
— myTable00_Rows
— ————–
— 99999
DROP TABLE myTable00
DROP TABLE myTable99
GO
Brett :cool:
On the talk of millions of rows and saving on few MB is not a factor, for this you should definetly going to maintain high specification of hardware and disks in order to support the database. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Do you really have a space problem? Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Generally it’s not a good idea to have NULL in the rows. Wherever possible, attempt should be made to replace the same with default values. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Hi all- Thanks for your responses. My initial question was will a NULL in a row occupy any space? Not that I have any storage issues, but I did not want to waste any space if I didn’t have to. My design depended on the answer to this question. I modified the script x002548 sent and I found that there is no space occupied by NULLs within a row. Thanks
cbarus
]]>