SQL Server Performance Forum – Threads Archive

How do NULL values affect table size?

Hi there. I am a newbie and have a very simple question for the pro. I have a table that has 100 decimal (yes it’s true) columns 13 bytes each. The table is very sparse. Only a few decimals are used in each row (and that is random). All columns are NOT NULLABLE and have a default value of (0.0). I am facing row size problems. I get about 5 rows per page and that hurts performance. Will i gain anything by making columns NULLABLE and setting NULL to all zero values? (any other suggestions will be appreciated) Thanx KKT

DECIMAL is a fixed length data type. That means, no matter what is stored therein, including NULL, it will always occupy the full storage space. So, you gain nothing by making it NULLABLE. What about normailising your table structure? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Frank, I don’t think it is quite true. Unfortunatelly I don’t have time to research at the moment (and unfortunatelly I don’t have Kalen’s book with me), but I think there is array of pointers to nullable column values in each data page, so if 13 bytes decimals are made nullable and most values are in fact null then he may be able to save a lot of space.

Here’s a quote from Kalen’s book

quote:
WARNING
——————————————————————————–
Fixed-length columns always use the full number of bytes defined for the table, even if the column holds a NULL value. If you’re just upgrading from version 6.5 of SQL Server, this might take you by surprise. I know of at least one large database at a manufacturing company that expanded to three or four times its size when it was upgraded from version 6.5. This is because they had fields in many tables that were defined as char(255) and allowed NULLs. Some tables had 10 or more such columns, and the vast majority of the data was actually NULL. In version 6.5, the only space that these NULL columns needed was the 2 bytes in the variable column offset array; the actual NULL data took no space at all. In SQL Server 2000, every one of these columns needed a full 255 bytes of storage and the database ballooned in size!

—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Oops, to fast… I think you mean the column offset array, right?
This is only present when there are variable-length column in the table. There is no array, when only fixed-length columns are present, because there is no need to. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

quote:Originally posted by FrankKalis Oops, to fast… I think you mean the column offset array, right?
This is only present when there are variable-length column in the table. There is no array, when only fixed-length columns are present, because there is no need to. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Yes, you’re right. [B)] I now even recmember the explanation you posted. I found it long time ago to explain myself problems we had.

No problem. You’ve caught me more than once, and you know, I always appreciate your comments! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

So NULL values will not do. And normalization in not an option (actually it is but will reduce my columns to 87). I suppose that the only alternative is to break the table to smaller tables. Thank you for helping By the way which book is ‘Kalen’s book’ that you mention?

"Inside SQL Server 2000" by SQL Server MVP Kalen Delaney. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

Btw, depending on your queries, you might complicate their logic when you make the column NULLABLE as you would then have to deal with the special NULL logic. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Yes. When using Sum function, you need to deal NULL by Instead of Select Sum(col) from yourTable you should use Select Sum(IsNull(col,0)) from yourTable Madhivanan Failing to plan is Planning to fail

I thought that aggregate operations eliminate NULL values. In fact you get a warning for that in the query analyzer window "Warning: Null value is eliminated by an aggregate or other SET operation." The sum is correct (isn’t it?) KKT

Umh? SUM() ignores NULLs, so both queries preoduce the same result. COUNT() is the only aggregate function that is NULL-aware. You definitely get different results when you issue a COUNT(*) compared to a COUNT(< column > ) when there are NUL markers in < colum >. But I meant something like WHERE blahblahblah OR … IS NULL. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

You are correct in this one. Count ignores NULLs and yields different results[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] KKT

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by kkt You are correct in this one. Count ignores NULLs and yields different results[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] KKT <hr height="1" noshade id="quote"></blockquote id="quote">True. <pre id="code">select count(<nullable column&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> from <table></pre id="code">is equivalent to <pre id="code">select count(*) from <table> where <nullable column> is not null</pre id="code">

]]>