April 4, 2012 at 1:35 pm
I'm trying to reduce the waste caused by bad schemas in this database. There is a table with 6-8 fields that are only storing null values. The table contains about 300m rows. I believe these fields were used previously before switching to lookup tables.
The fields are not marked as sparse. One of the fields is a varchar(2048). What consequences are we facing by keeping the columns as is? I'm not 100% sure if they can be removed so would setting them as sparse be a wise move?
April 4, 2012 at 1:48 pm
Remember that a NULL is not "stored" per se. It is the lack of a value. As far as I can tell, a column uses a bit to indicate nullable or not. A variable length column uses 2 bytes + size of value (in which case a NULL is 2 + 0 bytes). Fixed length columns are not affected by the value stored.
Jared
CE - Microsoft
April 4, 2012 at 5:45 pm
SQLKnowItAll (4/4/2012)
Remember that a NULL is not "stored" per se. It is the lack of a value. As far as I can tell, a column uses a bit to indicate nullable or not. A variable length column uses 2 bytes + size of value (in which case a NULL is 2 + 0 bytes). Fixed length columns are not affected by the value stored.
If the column is nullable then it uses a bit to indicate if the individual column in the particular row is null according to my reading. So null's are stored in my opinion. The metadata indicates if the column is nullable or not, this could also be a bit, but it has less consequences storagewise because metadata stores column info for the entire table.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply