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 many characters has my field?

Does anyone know a function in SQL or how I can get the amount of characters of a field?

I have a column named NU_IPS wich contains data varchar type, that has a % symbol at the end, like 9.7% and so on... But in original table it can't be like this (it has to bem float type), I just want the number content, like this 9.7 For that I need in DTS put a query that convert it. That's why I need a function or something that can get the quantity of characters of each field.

So, It would be someting like this...

select substring(convert(varchar(getSizeField() - 1), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

It would cut always the last caracter, wich is '%'...

Any clues?
All posts are welcome, thanks.Look up the Len() - function ...|||Thanks Nephilim!

But why can't I put this function in the convert if it returns a int type?
I guess it can't process each field at time, 'cause we can admit there's a table in convert varchar paremeter, it's obvious.

Like:
select substring(convert(varchar(SELECT len(nu_ipi) - 1 FROM dbo.t_STAGEAREACHAIR), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

I've tried putting an IF statment...

Like:
if ((select len(nu_ipi) - 1 from dbo.t_STAGEAREACHAIR) = 1)
begin
select substring(convert(varchar(1), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR
end

But it returns more than 1 value though.

Have you an idea how I can do it?
Thanks|||First of all, there really is no need to put a subselect in the select you posted :

select substring(convert(varchar(SELECT len(nu_ipi) - 1 FROM dbo.t_STAGEAREACHAIR), nu_ipi), 1, 4) from dbo.t_STAGEAREACHAIR

I think you are looking for something like this

select substring(convert(Varchar(10), NU_IPS), 1, len(convert(Varchar(10), NU_IPS)) -1 from dbo.t_STAGEAREACHAIR

however I'm not exactly certain I've understood your problem fully...|||maybe i don't understand it fully either, but NU-IPS is already a varchar, so...
select left(NU_IPS,len(NU_IPS)-1) ...or alternatively
select replace(NU_IPS,'%','') ...|||r937 and Nephilim
I think You got it, that's what I wanted.
It works...
Really thanks!
Juliane

how i convert varchar sal field to numeric in query

how i convert varchar sal field to numeric in query

select sum(sal) from emp1

error:the sum or average aggregate operation cannot take a varchar data type as an argument.

Use Cast as in:

select sum(cast(sal as float)) from emp1