October 10, 2008 at 9:59 am
I have a lot of rows in a table and bunch of fields as varchar and they are null. I was told to delete the fields that we don't use and we will have more space. Is this true that varchar takes a lot of space as a null value?
October 10, 2008 at 10:52 am
Yes, it does. I looked in SQL 2k5 and 2000 BOL and couldn't find the amount, for some reason I thought it might be 4 bytes but I can't remember exactly and I can't find anything to back that up. I'm sure someone who knows will post. Nulls can be pretty hotly debated at times.
To prove it takes space, I created a 1 column table that allowed nulls. I inserted 1 record and the reserved space was 16 kb. After a few more records the report hadn't changed much so I inserted a lot more records.
So I inserted some more, when I got to 907809 records, the reserved space was 10440 KBand data size was 10376 KB
Chris.
Chris.
October 10, 2008 at 11:10 am
Thank you
October 10, 2008 at 12:51 pm
Any nullable column does take space, but keep in mind that the disk space used by the database is in multiples of pages, which are 8kb each.
As far as nulls go, here's a test:
create table NoNulls (
Col1 varchar(100) not null);
go
create table Nulls (
Col1 varchar(100) null);
go
insert into dbo.NoNulls (Col1)
select 'a'
from dbo.Numbers;
insert into dbo.Nulls(Col1)
select null
from dbo.Numbers;
Numbers table has 10,001 rows in it, so each ended up with that many rows.
The first table ended up at 0.141 Meg, the second one at 0.109 Meg.
Tested again with a cross join, yielding a total of a little over 100-million rows per table. At that many rows, it was 1.32 Gig for just storing an "a", and 1.06 for storing null.
That might give you a better idea of how that works.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply