SQL Server Performance Forum – Threads Archive

size NULL column

Hello, Please somebody tell me, what would be size of a column in SQL DB if it is declared bigint but stores NULL.
e.g., in the following table definition, if I have NULL in col1 to col6, what would be the size of a record. And if I values in all the columns, what would be the size of the record. CREATE TABLE [dbo].[individual_assertion] (
[assertion_id] [int] IDENTITY (1, 1) NOT NULL ,
[creation_date] [datetime] NOT NULL ,
[col1] [bigint] NULL ,
[col2] [bigint] NULL ,
[col3] [bigint] NULL ,
[col4] [bigint] NULL ,
[col5] [bigint] NULL ,
[col6] [bigint] NULL ,
) (moved from OLAP forum, please post in relevant forums only)

i am 99% sure a big int will take 8 bytes regardless of whether u store any data in it. Ben ‘I reject your reality and substitute my own’ – Adam Savage

Thanks for your response.
Does that confirm that if a NULL is stored in a column having datatype bigint, it will occupy 8 bytes and not 0 byte because of NULL. Also what about varchar[x] column having NULL value. I tried to find more help on this in the documentation but could not find much. Thanks again.

yes, you are right. I think a varchar, etc will take 0 bytes if null, and increase based on the size of the data stored in it. Ben ‘I reject your reality and substitute my own’ – Adam Savage

BIGINT is a fixed-length data type. No matter what you store in such a type it will occupy the full space. That’s what Ben already told you. Since your table only contains fixed-length column, the minlen value in the sysindexes table will equal the sum of the column length + 4 bytes for some internal information tracking, i.e. number of rows and the NULL bitmap. As for VARCHAR: Yes, the actual column will occupy 0 bytes. But keep in mind the NULL bitmap. And as the column is of variable length, SQL Server need to be able to track somewhere, where one column end and another one starts, so there will be some information about that stored in the column offset array. IIRC, another 2 bytes. You’ll find this and a whole lot more about internal storage structure in "Inside SQL Server 2000" by Kalen Delaney.
Probably these will also help you:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
http://msdn.microsoft.com/library/d…dnsql2k/html/thestorageengine.asp?frame=true]
http://support.microsoft.com/default.aspx?scid=kb;EN-US;230785
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks Frank, your response clarifies a lot.

]]>