Would null values reduce size of a table?

[vnad]

We have a large table and would like to reduce the amount of physical disk it is occupying. We cannot delete rows since we need the data. Would the size of the table go down if we changed all the fields we do not use in the table to NULL? We cannot cut the fields out of the table since it is a 3rd party app and we would loss support for future releases if we change table structure.

 

[GShen]

If you have char fields loaded with spaces or int fields loaded with zeros, using nulls would definitely reduce the size of the table.

 

[vnad]

Is there anyway I could calculate the amount that would be saved?

 

[GShen]

I am not sure how much room a null value takes (if any), since it is just that... NULL.
Simple math of # of records * field Length being set to null values should do it.

EG. 100 records in table
field1 char(50)
field2 int


depending on amount of zeros being stored in INT field and spaces being stored in the CHAR field, add the 2 amount together and mult by 100.

Hope that helps.

 

[royjimenez]

if u want to improve the performance in your table, u could use particioned views.

I try to avoid the null fields, I like using a default value for each field when it's possible 'cause I don't have to use isnull function ( or similars ) .

Regards.

The life is too short to cry and long enough to try it... God bless us.

 

[GShen]

royjimenez
I hear you. Good point. It gets cumbersome at times. Especially if you are coding vb and sql. The NULL syntax is not the same.

 

[vnad]

Doesn't updatable partition views take Enterprise Edition to acomplish?

 

[SemperFiDownUnda]

Note that a CHAR(50) will take up 50 bytes even if it is NULL, Same with INT.

Using VARCHAR, NVARCHAR, etc will reduce your table size in most cases. They wear a 2 byte overhead so it doesn't make sence in very small fields.

 

[fluteplr]

Do you have any text or image fields? Setting these to null rather than spaces can save a huge amount of space.

 

[SemperFiDownUnda]

Text and image fields are not stored with the main table data thus they don't slow down read performance, if they are not referenced. Blob type data is stored in datapages not with the table. The data associated with the blob data in the tables datapages is just a pointer. Blob data automatically shapes its size to the data being provided. But yes if you send a string of 10,000 spaces it will take 10k to store.....20k if you are using unicode.

 

[fluteplr]

He didn't say anything about performance he said space.
If you store a text or image with one character in it, it will take up the minimum allocation amount of a text field which if I remember correctly is 8k. As opposed to a null which will take no space at all except for the space in the main table to store a null pointer.

 

[SemperFiDownUnda]

Ah! Miss this "3rd party app and we would loss support for future releases" thanks Flutepir.

To that I'd test this. If the 3rd party program is not currrently putting in NULL then you have a good chance of breaking it if you do. Even if you didn't break it the only columns that would benifit from NULL is VARCHAR and if they are defined as VARCHAR then you they probably are already set to something like '' instead of SPACE(20).

How big is your database? Disk space is generally cheep concidering how much effort and risk you'll go through to cutting down the size of the database without breaking the program.

 

[vnad]

Our database is around 140gigs and one table consists of 120 of the 140 gigs. Disk space is relativly expensive since we are on a EMC SAN. Most of the fields that we would null out are chars. The app is putting in nulls for most of the fields but there are some that we could null.

 

[SemperFiDownUnda]

I'm sure you'll test it. Might take a bit of time to shrink the database tho. Good luck

 

[vnad]

Going back to the updatable views with Enterprise edition. What happens when you do a reindex of one of the sub tables that the view inserts into. For example, you have three tables A1, A2, A3 and a view that makes them 'look' like one table viewA. What happens when you reindex A1? Can inserts still be possible?

 

[johnherman]

see also thread183-806159

Sometimes the grass is greener on the other side because there is more manure there - original.