Data storage

Does SQL SErver comprss the data it stores? or is a varchar(50) take up
that much space in the db?Jayme wrote:
> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
> that much space in the db?
>
A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
bytes, plus some overhead. A field defined as VARCHAR(50) containing
the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
in Books Online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Thu, 27 Jul 2006 15:17:05 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>Jayme wrote:
>> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
>> that much space in the db?
>A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
>bytes, plus some overhead. A field defined as VARCHAR(50) containing
>the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
>in Books Online.
CHAR has overhead? I thought only VARCHAR had overhead.
Roy|||Roy Harvey wrote:
> On Thu, 27 Jul 2006 15:17:05 -0500, Tracy McKibben
> <tracy@.realsqlguy.com> wrote:
>> Jayme wrote:
>> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
>> that much space in the db?
>> A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
>> bytes, plus some overhead. A field defined as VARCHAR(50) containing
>> the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
>> in Books Online.
> CHAR has overhead? I thought only VARCHAR had overhead.
> Roy
Unless I'm mistaken, EVERY datatype has a small amount of overhead just
due to the column's existence. Isn't it one byte per column?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Unless I'm mistaken, EVERY datatype has a small amount of overhead just
> due to the column's existence. Isn't it one byte per column?
Perhaps you are thinking about the null bitmap that SQL Server includes for
both fixed and variable length columns. The total row overhead of the
bitmap field is CEILING(#cols * 8).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:u%23jKUZesGHA.3556@.TK2MSFTNGP03.phx.gbl...
> Roy Harvey wrote:
>> On Thu, 27 Jul 2006 15:17:05 -0500, Tracy McKibben
>> <tracy@.realsqlguy.com> wrote:
>> Jayme wrote:
>> Does SQL SErver comprss the data it stores? or is a varchar(50) take up
>> that much space in the db?
>> A field defined as CHAR(50) containing the value 'BLUE' will occupy 50
>> bytes, plus some overhead. A field defined as VARCHAR(50) containing
>> the value 'BLUE' will occupy 4 bytes, plus some overhead. Details are
>> in Books Online.
>> CHAR has overhead? I thought only VARCHAR had overhead.
>> Roy
> Unless I'm mistaken, EVERY datatype has a small amount of overhead just
> due to the column's existence. Isn't it one byte per column?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Dan Guzman wrote:
>> Unless I'm mistaken, EVERY datatype has a small amount of overhead just
>> due to the column's existence. Isn't it one byte per column?
> Perhaps you are thinking about the null bitmap that SQL Server includes for
> both fixed and variable length columns. The total row overhead of the
> bitmap field is CEILING(#cols * 8).
>
No, I'm just not wording my thoughts properly. What I'm trying to say
is, the mere fact that a CHAR column exists is causing some overhead in
the table. The column definition has to be stored somewhere. I guess
my original response should have stated that for all practical purposes,
VARCHAR has overhead that CHAR does not.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Fri, 28 Jul 2006 07:52:42 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>The column definition has to be stored somewhere.
syscolumns, or whatever serves that purpose these days.