Alter table and size changes

27 Jan 2006 2:34 PM

Darin

Question that I hope someone can direct me.

I do alter table command to (mostly) add columns to an existing table,
but sometimes to change the size of an existing column (such as char(30)
changing to char(50)).

I remember reading that changing the sze will actually leave the 30
character area and add at the end the 50 character, so the table is
taking up 80 characters instead of 50 (if this is wrong let me know).

So I am thinking I need to do a command that compares the size of the
existing table with the size of the table if it had been created
correctly using the create table command. How can I do that or is it
necessary?

Thanks.

Darin

*** Sent via Developersdex http://www.developersdex.com ***

27 Jan 2006 6:37 PM

Razvan Socol

Hi, Darin

This happens only for fixed-length datatypes (for example char, but not
varchar). You can look at the xoffset column in sysobjects to see the
difference.

Try this, for example:

CREATE TABLE T1 (X char(1000), Y char(500))
ALTER TABLE T1 ALTER COLUMN X char(2000)

CREATE TABLE T2 (A char(2000), B char(500))

SELECT o.name as TableName, c.name as ColumnName, c.length, c.xoffset
FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.name IN ('T1','T2')

DROP TABLE T1, T2

You will get something like this:
TableName            ColumnName           length xoffset
-------------------- -------------------- ------ -------
T1                   X                    2000   1504
T1                   Y                    500    1004
T2                   A                    2000   4
T2                   B                    500    2004

This may indicate that the row size for T1 is about 3506 bytes, whereas
the row size for T2 is about 2506 bytes.

For more informations, see:
http://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html

However, I think you should worry about this problem only if are using
large columns with fixed-length data types. Usually, the large columns
are variable-length data types, i.e. varchar(50), not char(50), so this
is rarely a problem with a big impact.

Razvan