Calculating space

Hi
Problem - I want to create a query that give me this results
Table, Column , Datatype, Rowcount, Size.
I have a question though, I've read in BOL about how to calculate space
based on variable and fixed lengths of datatypes. What I am not sure about i
s
how to sql allocate space.
Let's say I have a nvarchar(10) column, this is variable length and size
will be the amount of characters entered times two.
For the fixed lenght nchar(n) BOL specify the storage size will be two times
n bytes and one time when working with char.
However in nchar or char, does this mean the size is used even if the actual
char in the field is only 1 out of the 10 characters ?
Char doesn't specify "amount of characters entered" like varchar.
Can someone please help me out with info or a link.
Thanks in advanceFor character type columns the number of bytes they take up on disk is
calculated as follows:
2 bytes if it is variable length (to store the offset in the row)
+ (length for fixed length column or actual data length for variable length
column) * (2 if n(var)char or 1 if (var)char)
You can get the values for the actual number of bytes a column takes up in a
row with the DATALENGTH function. (The result doesn't include the 2 bytes to
store the variable length offset, as that is contributed to row overhead
rather than the column length).
Jacco Schalkwijk
SQL Server MVP
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:80A0E3A9-EA58-49A7-B802-A1FDEA70D092@.microsoft.com...
> Hi
> Problem - I want to create a query that give me this results
> Table, Column , Datatype, Rowcount, Size.
> I have a question though, I've read in BOL about how to calculate space
> based on variable and fixed lengths of datatypes. What I am not sure about
> is
> how to sql allocate space.
> Let's say I have a nvarchar(10) column, this is variable length and size
> will be the amount of characters entered times two.
> For the fixed lenght nchar(n) BOL specify the storage size will be two
> times
> n bytes and one time when working with char.
> However in nchar or char, does this mean the size is used even if the
> actual
> char in the field is only 1 out of the 10 characters ?
> Char doesn't specify "amount of characters entered" like varchar.
> Can someone please help me out with info or a link.
> Thanks in advance
>|||Always specify the size, never leave it to the default. With Char and NChar
datatypes the value stored in the database is always the full size that you
specified since it will padd with spaces. For instance if you have a
CHAR(10) and enter 'xyz' it will still be 10 bytes long and converts to 'xyz
'.
Andrew J. Kelly SQL MVP
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:80A0E3A9-EA58-49A7-B802-A1FDEA70D092@.microsoft.com...
> Hi
> Problem - I want to create a query that give me this results
> Table, Column , Datatype, Rowcount, Size.
> I have a question though, I've read in BOL about how to calculate space
> based on variable and fixed lengths of datatypes. What I am not sure about
> is
> how to sql allocate space.
> Let's say I have a nvarchar(10) column, this is variable length and size
> will be the amount of characters entered times two.
> For the fixed lenght nchar(n) BOL specify the storage size will be two
> times
> n bytes and one time when working with char.
> However in nchar or char, does this mean the size is used even if the
> actual
> char in the field is only 1 out of the 10 characters ?
> Char doesn't specify "amount of characters entered" like varchar.
> Can someone please help me out with info or a link.
> Thanks in advance
>