Easy about Varchars

I've done some testing recently to find out exactly how much overhead
varchars have. I have found that it seems to vary. When compared to the
exact same char, it seems to have a varied difference. If I create a table
with char(1), and another with varchar(1), and populate them both with like
10,000 rows, the difference is a couple hundred bytes. If I create another
table with char(10), and one with varchar(10), and fill them with the same
10,000 rows, theyre the same. Part of this could be the inaccuracy of SEM.
SEM doesn't report the exact number of rows for heaps such as these, and in
some cases, even reported different counts for each table. The problem is
2-fold.
1. Is there a way to get the actual byte count used up by a table?
sp_spaceused is useless for this.
2. How exactly are varchars stored, and how much extra space is required to
hold the variable info?
thx.Hi
1)
It could be useless if did not run dbcc updateusage command
-- dbcc updateusage(0)
create table #tmp (
name sysname
, rows int
, reserved varchar(50)
, data varchar(50)
, index_size varchar(50)
, unused varchar(50)
)
exec sp_msforeachtable 'insert #tmp exec sp_spaceused ''?'''
select * from #tmp order by rows desc
drop table #tmp
2) Character (fixed length) --1 byte per charcter declared , even if
partially used
Character (variable length)-- 1 byte per charcter stored.Declared but
unused do not consume storage.
"crever" <crever@.discussions.microsoft.com> wrote in message
news:BEFFDE84-E9BF-40CF-A156-80C2100D775F@.microsoft.com...
> I've done some testing recently to find out exactly how much overhead
> varchars have. I have found that it seems to vary. When compared to the
> exact same char, it seems to have a varied difference. If I create a
table
> with char(1), and another with varchar(1), and populate them both with
like
> 10,000 rows, the difference is a couple hundred bytes. If I create
another
> table with char(10), and one with varchar(10), and fill them with the same
> 10,000 rows, theyre the same. Part of this could be the inaccuracy of
SEM.
> SEM doesn't report the exact number of rows for heaps such as these, and
in
> some cases, even reported different counts for each table. The problem is
> 2-fold.
> 1. Is there a way to get the actual byte count used up by a table?
> sp_spaceused is useless for this.
> 2. How exactly are varchars stored, and how much extra space is required
to
> hold the variable info?
> thx.|||On Wed, 2 Feb 2005 07:37:07 -0800, crever wrote:
(snip)
>2. How exactly are varchars stored, and how much extra space is required t
o
>hold the variable info?
Hi crever,
A varchar column needs one byte for each character, plus a fixed overhead
of two bytes that SQL Server uses to store the length.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)