Does using varchar instead of char save space?
fromeo
I came across a table in our database where 2 columns had datatypes of
char(40) and char(20) respectively.
For example, aggregating on the width of the char(40) column gives the
following distribution:
Width Count
37 1062
34 15
33 591
31 7636
30 96912
29 16068
28 13108
27 40326
26 9541
25 14695
24 31267
23 21026
22 4234
21 985
20 1287
19 9538
18 4003
17 7270
16 3621
15 4603
14 7391
13 1244
12 1965
11 88
10 1908
9 284
I changed the column definitions to be varchar(40) and varchar(20) and
expected to see a space saving.
However, this doesn't seem to be the case.
Here's the output from the sp_spaceused system sp for before and
after:
rows reserved data index_size unused
Before 300668 28048 KB 27648 KB 288 KB 112 KB
After 300668 29912 KB 29696 KB 184 KB 32 KB
The reserved and data sizes appear to have increased. Although the
index size has gone down.
Is there something else that I need to do to reclaim the space used by
the table?
Or am I mistaken in expecting to make a space saving this way?
Thanks,
F.
Iain Sharp
It may be that the trailing spaces stored in the database when it is a
char are preserved during the alter table.
Varchar requires a couple (4?) of bytes to store the length of the
data in addition to the data itself, so the space used would indeed go
up.
You could try
update table
set column1=rtrim(column1),
column2=rtrim(column2)
where column1 <> rtrim(column1) or column2 <> rtrim(column2)
and see if it makes a difference.
Iain
Erland Sommarskog
> It may be that the trailing spaces stored in the database when it is a
> char are preserved during the alter table.
A quick test confirms this:
create table bludder(a char(40) not null)
insert bludder (a) values ('Nisse')
go
alter table bludder alter column a varchar(40) not null
go
select datalength(a), '<' + a + '>'
from bludder
go
drop table bludder
> Varchar requires a couple (4?) of bytes to store the length of the
> data in addition to the data itself, so the space used would indeed go
> up.
>
> You could try
>
> update table
> set column1=rtrim(column1),
> column2=rtrim(column2)
> where column1 <> rtrim(column1) or column2 <> rtrim(column2)
>
> and see if it makes a difference.
Fromeo may also need rebuild indexes after the update.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
fromeo
Thanks for the response.
I just tried what you suggested and it actually seems to have made
things worse!!!
The figures are now:
rows reserved data index_size unused
Before: 300668 28048 KB 27648 KB 288 KB 112 KB
After : 300668 29912 KB 29696 KB 184 KB 32 KB
Trimmed: 300668 29928 KB 29712 KB 184 KB 32 KB
> Varchar requires a couple (4?) of bytes to store the length of the
> data in addition to the data itself, so the space used would indeed go up.
Yes, I expected the change to VARCHAR to add a few bytes to each row,
however, overall I expected the size to come down as a lot of rows had
more bytes of padded space to compensate for the addition of the row
length information. For example there are over 96,000 rows with a
width of 30 characters in the 40 character column. That's 10 bytes of
extra space that I would expect to be able to reclaim, which even if
you subtract 2 bytes (to hold length information) from each row still
leaves 8 bytes * 96,000 rows = 768,000 bytes.
I'm obviously missing something, but I just can't see what.
Cheers,
F.
fromeo
Rebuilding the index seems to have done the trick.
After rebuilding the clustered index the size has now come down to the
following:
rows reserved data index_size unused
Before: 300668 28048 KB 27648 KB 288
KB 112 KB
After : 300668 29912 KB 29696 KB 184
KB 32 KB
Trimmed: 300668 29928 KB 29712 KB 184 KB
32 KB
Rebuild: 300668 24456 KB 24296 KB 136
KB 24 KB
Many thanks,
F.