How much space on disk does a varchar use?

How much space on disk does a varchar use? Is it the defined length or the
actual length used? If the odd entry (1%) in a large table needs a comment of
say 40 characters, would an unused varchar field only take a minimum amount
of space, or should the comment be in a linked table (which of course also
has overheads as indexes are then needed), or would it be best to use/misuse
a text field?
--
Many thanks,
John AustinJohn Austin wrote:
> How much space on disk does a varchar use? Is it the defined length or the
> actual length used? If the odd entry (1%) in a large table needs a comment of
> say 40 characters, would an unused varchar field only take a minimum amount
> of space, or should the comment be in a linked table (which of course also
> has overheads as indexes are then needed), or would it be best to use/misuse
> a text field?
From Books Online:
char and varchar
Fixed-length (char) or variable-length (varchar) character data types.
char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must
be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
synonym for char is character.
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n
must be a value from 1 through 8,000. Storage size is the actual length
in bytes of the data entered, not n bytes. The data entered can be 0
characters in length. The SQL-92 synonyms for varchar are char varying
or character varying.
Remarks
When n is not specified in a data definition or variable declaration
statement, the default length is 1. When n is not specified with the
CAST function, the default length is 30.
Objects using char or varchar are assigned the default collation of the
database, unless a specific collation is assigned using the COLLATE
clause. The collation controls the code page used to store the character
data.
Sites supporting multiple languages should consider using the Unicode
nchar or nvarchar data types to minimize character conversion issues. If
you use char or varchar:
Use char when the data values in a column are expected to be
consistently close to the same size.
Use varchar when the data values in a column are expected to vary
considerably in size.
If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
a char column defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage
size is still n bytes. Depending on the character string, the storage
size of n bytes may be less than n characters.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy, just what I needed to know - and sorry, I should have searched
books online more thoroughly!
--
John Austin
"Tracy McKibben" wrote:
> John Austin wrote:
> > How much space on disk does a varchar use? Is it the defined length or the
> > actual length used? If the odd entry (1%) in a large table needs a comment of
> > say 40 characters, would an unused varchar field only take a minimum amount
> > of space, or should the comment be in a linked table (which of course also
> > has overheads as indexes are then needed), or would it be best to use/misuse
> > a text field?
> From Books Online:
> char and varchar
> Fixed-length (char) or variable-length (varchar) character data types.
> char[(n)]
> Fixed-length non-Unicode character data with length of n bytes. n must
> be a value from 1 through 8,000. Storage size is n bytes. The SQL-92
> synonym for char is character.
> varchar[(n)]
> Variable-length non-Unicode character data with length of n bytes. n
> must be a value from 1 through 8,000. Storage size is the actual length
> in bytes of the data entered, not n bytes. The data entered can be 0
> characters in length. The SQL-92 synonyms for varchar are char varying
> or character varying.
> Remarks
> When n is not specified in a data definition or variable declaration
> statement, the default length is 1. When n is not specified with the
> CAST function, the default length is 30.
> Objects using char or varchar are assigned the default collation of the
> database, unless a specific collation is assigned using the COLLATE
> clause. The collation controls the code page used to store the character
> data.
> Sites supporting multiple languages should consider using the Unicode
> nchar or nvarchar data types to minimize character conversion issues. If
> you use char or varchar:
> Use char when the data values in a column are expected to be
> consistently close to the same size.
>
> Use varchar when the data values in a column are expected to vary
> considerably in size.
> If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed,
> a char column defined as NULL is handled as varchar.
> When the collation code page uses double-byte characters, the storage
> size is still n bytes. Depending on the character string, the storage
> size of n bytes may be less than n characters.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>