Space used by NULL value in different data type columns

Question

Space used by NULL value in different data type columns

only4mithunc

SSCrazy Eights

Points: 8434
More actions
November 17, 2010 at 9:43 am

#231713

Hello friends,
Recently I red some article about making a column as SPARSE, and the restrictions related to it. Now I would like to know something related to the storage of NULL values.
After reading the article I understood that if the column is specified as SPARSE it wont take any space for NULL values.
My doubt is if the column is non-sparse,will there be any change in space used if the value is NULL?
For example C1 is the column of type INT in a table T_TEST ,
CREATE TABLE T_TEST
(C1 INT)
INSERT INTO T_TEST
VALUES(10),(20),(NULL)
SELECT * FROM T_TEST
As you can see I have inserted values 10,20 and NULL to the column C1 of type INT, is there an difference in space used for these three rows? or each row will use 4 bytes each? I'm really confused about this
Like that can I know how it will be for varchar,char,nvarchar.
One more things is is there any way to find the space used by the rows of a table..? my intention is to check the difference in space usage before and after making the column as SPARSE.
It would be great help if some one can help me in this
Thanks in advance.
Thanks & Regards,
MC

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

each row will use 4 bytes. If it is a nullable column it will have a bit associated with it to indicate that it is null or non-null.

There is a way to determine the exact size (number of bytes) of each row. It depends on the data types, null-ability and (in the case of varbinary/char) the content of the column (use datalength()) All of this can be computed fairly accurately.

When you are talking about sparse columns and so forth all that changes.

The probability of survival is inversely proportional to the angle of arrival.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Thanks mate..

As you mentioned that there is a way to find the exact space used, can I know how to find it..? is there any script for that..?

And one more confirmation:

1) If the column is char(10), the max length of the string we can store is 10 bytes and each value (whether its length is < 10 or = 10 ) it will take 10 bytes.

2)If the column is varchar(10) again max length of the string we can store is 10 bytes but the space will be length(string) bytes

3)If the column is nvarchar(10), each character will take 2 bytes , so the max length of string we can store is 5 , and the space used will be length(string) * 2 bytes

I'm I correct about the above three points?

Thanks & Regards,
MC

sturner SSC-Insane Points: 22411 More actions · Answer 3

only4mithunc (11/17/2010)
And one more confirmation:
1) If the column is char(10), the max length of the string we can store is 10 bytes and each value (whether its length is < 10 or = 10 ) it will take 10 bytes.
2)If the column is varchar(10) again max length of the string we can store is 10 bytes but the space will be length(string) bytes
3)If the column is nvarchar(10), each character will take 2 bytes , so the max length of string we can store is 5 , and the space used will be length(string) * 2 bytes
I'm I correct about the above three points?

Correct. In the case of [n]varchar/varbinary there are two additional bytes that store the length. In the case of [n]varchar(MAX) there are a few additional bytes needed to support these (I am not certain of the exact number)

The probability of survival is inversely proportional to the angle of arrival.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 4

On point #3. If NVARCAHR(10) you can still store 10 characters (not just 5). But, yes they each take up twice the space of a non-unicode string.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 5

Thanks for the clarification.

And as I asked above, can I know how can we find the exact space used...? Is there any script for that..?

My aim is if there any script for that I can check the space used , before and after making the column as SPARSE...

Thanks & Regards,
MC

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 6

My understanding is that you want to find out if there's a difference if between a normal column and after you make it a sparse column. The easiest way would be to us sp_spaceused on the table before and after the change and simply calculate the difference between the two results.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 7

Thanks Jeff,

I have tried the same by using the below example. I have created a table with two columns then inserted 1000 records with NULL for one column, then checked using sp_spaceused, after that I have altered column( to which I have inserted NULL ) to SPARSE, but then I checked sp_spaceused it didn't show any difference.

CREATE TABLE TEST_SPARSE

(

C1 INT,

C2 INT

)

GO

DECLARE @v-2 INT = 0

WHILE (@V<1000)

BEGIN

INSERT INTO TEST_SPARSE

SELECT 10,NULL

SET @v-2 = @v-2 +1

END

ALTER TABLE TEST_SPARSE

ALTER COLUMN C2 INT SPARSE

But when I dropped the table and the recreated with the column as SPARSE , then after inserting the same records , sp_spaceused has showed some difference.

CREATE TABLE TEST_SPARSE

(

C1 INT,

C2 INT SPARSE

)

GO

DECLARE @v-2 INT = 0

WHILE (@V<1000)

BEGIN

INSERT INTO TEST_SPARSE

SELECT 10,NULL

SET @v-2 = @v-2 +1

END

Is this correct? then how SPARSE will be useful if we are altering an existing column AND making it SPARSE..?

Thanks & Regards,
MC

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 8

Friends,

Did you get a chance to look into my previous reply? 🙁

I have created a table with two columns then inserted 1000 records with NULL for one column, then checked using sp_spaceused, after that I have altered column( to which I have inserted NULL ) to SPARSE, but then I checked sp_spaceused it didn't show any difference.

Thanks & Regards,
MC

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 9

only4mithunc (11/21/2010)
Friends,
Did you get a chance to look into my previous reply? 🙁

Mostly yes. You forgot to do two things in your testing. You really do need a (usually PK) column with a clustered index on it. After you change the non-clustered column to SPARSE, you need to rebuild the already allocated rows in the table by rebuilding the indexes on the table. Then test using sp_space used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 10

Thanks a lot Jeff,

When I tried those two things, I'm able to see the difference... 🙂

Thanks & Regards,
MC

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 11

only4mithunc (11/21/2010)
Thanks a lot Jeff,
When I tried those two things, I'm able to see the difference... 🙂

And thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)