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 o
f
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 amoun
t
> 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/misu
se
> 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:
> 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
>

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
>

how many chars in varchar record

hello

How could I check how many chars are in record, defined as varchar(8000).
It's obvious that in such defined record could be 1 char to 8000 char. But
what query to SQL database should I post to give information about realy
lenght of this records ?

thanks from advance

AdamSee LEN() and "String Functions" in Books Online.

Simon|||Select len(field)
or
Select DataLength(field)

Madhivanan|||Be careful - the two functions are not the same. LEN() returns the
number of characters after removing trailing blanks, but DATALENGTH()
returns the number of bytes stored. So you can get different results if
trailing blanks are present, if you have varchar vs char, or if you use
Unicode columns - see the code below, and Books Online for more
details.

Simon

create table #t (v varchar(10), nv nvarchar(10))

insert into #t select 'X', 'X'
insert into #t select 'X ', 'X ' -- two trailing blanks

select len(v), datalength(v), len(nv), datalength(nv)
from #t

create table #t1 (c char(10), nc nchar(10))

insert into #t1 select 'X', 'X'
insert into #t1 select 'X ', 'X ' -- two trailing blanks

select len(c), datalength(c), len(nc), datalength(nc)
from #t1|||Thanks Simon

Madhivanan|||thanx for all for valuable information!

best wishes
Adam

How is 'change' defined ?

Can anyone tell me how a change is defined when replicating ? in particular, if an update is done to an existing row, but the value is actually the same as it was originally, does this count as a change and will the replication attempt to update/resolve
it or not ?
I am working in a scenario where it is quite important to minimise the time/quantity of replication so want to minimise any unnecessary replicating. Are there any techniques I can use to help with this ?
any change is replicated.
|||Bother!
But thanks
|||Sarah wrote:
> Can anyone tell me how a change is defined when replicating ? in particular, if an update is done to an existing row, but the value is actually the same as it was originally, does this count as a change and will the replication attempt to update/resolv
e it or not ?
> I am working in a scenario where it is quite important to minimise the time/quantity of replication so want to minimise any unnecessary replicating. Are there any techniques I can use to help with this ?
Basically the log is replicated. So any action that will make a commited
change in the log will be replicated.
Furthermore, if you are doing updated, there is a possiblity that
inserts/deletes are replicated instead.
Typically, if you have a table with a unique index and you make an
update which update this index, sql server will replace your update by
an insert/delete and this is what will be sent to subscribers
|||Hi Sarah,
If you have a lot of updates that are setting columns to the same value
repeatedly, you can minimize the the replication volume by adding additional
"where" criteria to your update statements. For example, if you have a
statement (psuedo-code)
set @.val1 = 'XXX'
set @.tblKey = '123'
update TABLEX set fieldA = @.val1 where tblKey= @.tblKey
and you execute this 100 times, it will generate 100 updates, and 100
replication transactions.
If you add a where clause like so
update TABLEX set fieldA = @.val1 where tblKey= @.tblKey and fieldA <> @.val1
ii will not update the row after the first time. This type of clause
generally doesn't add much overhead to the transaction.
Ed
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:A8B78B80-4AF0-43FE-B3A8-8678C072C3E2@.microsoft.com...
> Can anyone tell me how a change is defined when replicating ? in
particular, if an update is done to an existing row, but the value is
actually the same as it was originally, does this count as a change and
will the replication attempt to update/resolve it or not ?
> I am working in a scenario where it is quite important to minimise the
time/quantity of replication so want to minimise any unnecessary
replicating. Are there any techniques I can use to help with this ?