home
Alter table - good or bad?.
Author Nigel Rivett
Alter table - good or bad?
You have a production table and wish to add or change a column.
How do you do it?
One way is to create a new table with the new structure, copy all the data across, delete the old table, rename the new table, apply constraint, foreign keys etc.
A much simpler way is to use alter table to add or change the column but this has consecuences of which you should be aware.
Data is stored in pages. The position of this data is stored in syscolumns.
syscolumns
name name of column
colid id of column
colorder presentation order of column
xoffset offset from start of fixed length data (negative value for variable length)
length length of column
Create the table
create table x (s char(1) not null, t char(10) not null, u char(1) not null, y varchar(5), z varchar(5))
insert x select 'A', 'bbbbbbbbbb', 'C', 'yyy', 'zzz'
insert x select 'D', 'eeeeeeeeee', 'F', 'yyy', 'zzz'
The syscolumn entries are
select name = left(name,2), colid, xoffset, offset, type, prec, length from
syscolumns where id = object_id('x')
name colid xoffset offset type prec length
---- ------ ------- ------ ---- ------ ------
s 1 4 2 47 1 1
t 2 5 3 47 10 10
u 3 15 13 47 1 1
y 4 -1 -1 39 5 5
z 5 -2 -2 39 5 5
And the data is stored as
19CE2060: 30001000 41626262 62626262 62626243 0...AbbbbbbbbbbC
19CE2070: 05000002 001c001f 00797979 7a7a7a30 .........yyyzzz0
19CE2080: 00100044 65656565 65656565 65654605 ...DeeeeeeeeeeF.
19CE2090: 00000200 1c001f00 7979797a 7a7a0000 ........yyyzzz..
19CE20A0: 00000000 00000000 00000000 00000000 ................
19CE20B0: 00000000 00000000 00000000 00000000 ................
19CE20C0: 00000000 00000000 00000000 00000000 ................
Alter fixed length column
We alter the size of col t to be 1 byte larger
alter table x alter column t char(11)
select name = left(name,2), colid, colorder, xoffset, offset, type, prec, length from
syscolumns where id = object_id('x')
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 5 5
z 5 5 -2 -2 39 5 5
19CE2060: 30001000 41626262 62626262 62626243 0...AbbbbbbbbbbC
19CE2070: 05000002 001c001f 00797979 7a7a7a30 .........yyyzzz0
19CE2080: 00100044 65656565 65656565 65654605 ...DeeeeeeeeeeF.
19CE2090: 00000200 1c001f00 7979797a 7a7a3000 ........yyyzzz0.
19CE20A0: 1b004162 62626262 62626262 62436262 ..AbbbbbbbbbbCbb
19CE20B0: 62626262 62626262 20050000 02002700 bbbbbbbb .....'.
19CE20C0: 2a007979 797a7a7a 30001b00 44656565 *.yyyzzz0...Deee
19CE20D0: 65656565 65656546 65656565 65656565 eeeeeeeFeeeeeeee
19CE20E0: 65652005 00000200 27002a00 7979797a ee .....'.*.yyyz
19CE20F0: 7a7a0000 00000000 00000000 00000000 zz..............
What has happened here:
Looking at offset in syscolumns the old column t has been moved to after u and the space formally occupied has been left unused.
Looking at the data page the first record cannot be enlarged due to the following record so it is copied after it.
The second now has the same problem too and so is also copied.
This leaves wasted space where the old records were stored - but this can presumably be reclaimed by defragmenting.
The old column though will be wasted space in every record in the table.
Alter variable length column
We alter the size of col y to be 1 byte larger
alter table x alter column y varchar(6)
select name = left(name,2), colid, colorder, xoffset, offset, type, prec, length from
syscolumns where id = object_id('x')
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 6 6
z 5 5 -2 -2 39 5 5
19CE2060: 30001000 41626262 62626262 62626243 0...AbbbbbbbbbbC
19CE2070: 05000002 001c001f 00797979 7a7a7a30 .........yyyzzz0
19CE2080: 00100044 65656565 65656565 65654605 ...DeeeeeeeeeeF.
19CE2090: 00000200 1c001f00 7979797a 7a7a3000 ........yyyzzz0.
19CE20A0: 1b004162 62626262 62626262 62436262 ..AbbbbbbbbbbCbb
19CE20B0: 62626262 62626262 20050000 02002700 bbbbbbbb .....'.
19CE20C0: 2a007979 797a7a7a 30001b00 44656565 *.yyyzzz0...Deee
19CE20D0: 65656565 65656546 65656565 65656565 eeeeeeeFeeeeeeee
19CE20E0: 65652005 00000200 27002a00 7979797a ee .....'.*.yyyz
19CE20F0: 7a7a0000 00000000 00000000 00000000 zz..............
Unsurprisingly there is no change as there is no change to the data physically stored.
Add a fixed length column
We add a new 1 byte column
alter table x add a char(1) not null default 'P'
select name = left(name,2), colid, colorder, xoffset, offset, type, prec, length from
syscolumns where id = object_id('x')
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 6 6
z 5 5 -2 -2 39 5 5
a 6 6 27 2 47 1 1
19CE2060: 30001000 41626262 62626262 62626243 0...AbbbbbbbbbbC
19CE2070: 05000002 001c001f 00797979 7a7a7a30 .........yyyzzz0
19CE2080: 00100044 65656565 65656565 65654605 ...DeeeeeeeeeeF.
19CE2090: 00000200 1c001f00 7979797a 7a7a3000 ........yyyzzz0.
19CE20A0: 1b004162 62626262 62626262 62436262 ..AbbbbbbbbbbCbb
19CE20B0: 62626262 62626262 20050000 02002700 bbbbbbbb .....'.
19CE20C0: 2a007979 797a7a7a 30001b00 44656565 *.yyyzzz0...Deee
19CE20D0: 65656565 65656546 65656565 65656565 eeeeeeeFeeeeeeee
19CE20E0: 65652005 00000200 27002a00 7979797a ee .....'.*.yyyz
19CE20F0: 7a7a3000 1c004162 62626262 62626262 zz0...Abbbbbbbbb
19CE2100: 62436262 62626262 62626262 20500600 bCbbbbbbbbbb P..
19CE2110: 00020028 002b0079 79797a7a 7a30001c ...(.+.yyyzzz0..
19CE2120: 00446565 65656565 65656565 46656565 .DeeeeeeeeeeFeee
19CE2130: 65656565 65656520 50060000 02002800 eeeeeee P.....(.
19CE2140: 2b007979 797a7a7a 00000000 00000000 +.yyyzzz........
The data is again copied as the records have been enlarged.
Drop and add fixed length column
alter table x drop constraint DF__x__a__023D5A04
alter table x drop column a
alter table x add a char(1) not null default 'P'
select name = left(name,2), colid, colorder, xoffset, offset, type, prec, length from
syscolumns where id = object_id('x')
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 6 6
z 5 5 -2 -2 39 5 5
a 7 7 27 2 47 1 1
19CE2060: 30001000 41626262 62626262 62626243 0...AbbbbbbbbbbC
19CE2070: 05000002 001c001f 00797979 7a7a7a30 .........yyyzzz0
19CE2080: 00100044 65656565 65656565 65654605 ...DeeeeeeeeeeF.
19CE2090: 00000200 1c001f00 7979797a 7a7a3000 ........yyyzzz0.
19CE20A0: 1b004162 62626262 62626262 62436262 ..AbbbbbbbbbbCbb
19CE20B0: 62626262 62626262 20050000 02002700 bbbbbbbb .....'.
19CE20C0: 2a007979 797a7a7a 30001b00 44656565 *.yyyzzz0...Deee
19CE20D0: 65656565 65656546 65656565 65656565 eeeeeeeFeeeeeeee
19CE20E0: 65652005 00000200 27002a00 7979797a ee .....'.*.yyyz
19CE20F0: 7a7a3000 1c004162 62626262 62626262 zz0...Abbbbbbbbb
19CE2100: 62436262 62626262 62626262 20500700 bCbbbbbbbbbb P..
19CE2110: 00020028 002b0079 79797a7a 7a30001c ...(.+.yyyzzz0..
19CE2120: 00446565 65656565 65656565 46656565 .DeeeeeeeeeeFeee
19CE2130: 65656565 65656520 50070000 02002800 eeeeeee P.....(.
19CE2140: 2b007979 797a7a7a 00000000 00000000 +.yyyzzz........
Here there is no change to the data storage but the colid and colorder have increased.
Drop and add variable length column
alter table x drop column z
alter table x add z varchar(5)
select name = left(name,2), colid, colorder, xoffset, offset, type, prec, length from
syscolumns where id = object_id('x')
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 6 6
a 7 7 27 2 47 1 1
z 8 8 -2 -1 39 5 5
and again
alter table x drop column z
alter table x add z varchar(5)
select name = left(name,2), colid, colorder, xoffset, offset, type, prec, length from
syscolumns where id = object_id('x')
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 6 6
a 7 7 27 2 47 1 1
z 9 9 -2 -1 39 5 5
Notice that the colid and coloffset are increasing
What happens if we continue with this process
set nocount on
declare @i int
select @i = 0
while @i < 2000
begin
select @i = @i + 1
alter table x drop column z
alter table x add z varchar(5)
end
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 6 6
a 7 7 27 2 47 1 1
z 2009 2009 -2 -1 39 5 5
Similarly for a fixed length column
set nocount on
declare @i int
declare @c varchar(128)
declare @sql varchar(1000)
select @i = 0
while @i < 2000
begin
select @i = @i + 1
select @c = object_name(cn.constid) from sysconstraints cn, syscolumns c where c.id = cn.id and cn.colid = c.colid and c.name = 'a' and c.id = object_id('x')
select @sql = 'alter table x drop constraint ' + @c
exec (@sql)
alter table x drop column a
alter table x add a char(1) not null default 'P'
end
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 6 6
z 2009 2009 -2 -1 39 5 5
a 4009 4009 27 2 47 1 1
But now look at the data pages
19CE2060: 30001c00 41626262 62626262 62626243 0...AbbbbbbbbbbC
19CE2070: 62626262 62626262 62622050 a80f80ff bbbbbbbbbb P....
19CE2080: ffffffff ffffffff ffffffff ffffffff ................
19CE2090: ffffffff ffffffff ffffffff ffffffff ................
19CE20A0: ffffffff ffffffff ffffffff ffffffff ................
19CE20B0: ffffffff ffffffff ffffffff ffffffff ................
19CE20C0: ffffffff ffffffff ffffffff ffffffff ................
19CE20D0: ffffffff ffffffff ffffffff ffffffff ................
19CE20E0: ffffffff ffffffff ffffffff ffffffff ................
19CE20F0: ffffffff ffffffff ffffffff ffffffff ................
19CE2100: ffffffff ffffffff ffffffff ffffffff ................
19CE2110: ffffffff ffffffff ffffffff ffffffff ................
19CE2120: ffffffff ffffffff ffffffff ffffffff ................
19CE2130: ffffffff ffffffff ffffffff ffffffff ................
19CE2140: ffffffff ffffffff ffffffff ffffffff ................
19CE2150: ffffffff ffffffff ffffffff ffffffff ................
19CE2160: ffffffff ffffffff ffffffff ffffffff ................
19CE2170: ffffffff ffffffff ff010000 00000000 ................
19CE2180: 00000000 00000000 00000000 00000000 ................
19CE2190: 00000000 00000000 00000000 00000000 ................
19CE21A0: 00000000 00000000 00000000 00000000 ................
19CE21B0: 00000000 00000000 00000000 00000000 ................
19CE21C0: 00000000 00000000 00000000 00000000 ................
19CE21D0: 00000000 00000000 00000000 00000000 ................
19CE21E0: 00000000 00000000 00000000 00000000 ................
19CE21F0: 00000000 00000000 00000000 00000000 ................
19CE2200: 00000000 00000000 00000000 00000000 ................
19CE2210: 00000000 00000000 00000000 00000000 ................
19CE2220: 00000000 00000000 00000000 00000000 ................
19CE2230: 00000000 00000000 00000000 00000000 ................
19CE2240: 00000000 00000000 00000000 00000000 ................
19CE2250: 00000000 00000000 00000000 00000000 ................
19CE2260: 00000000 00000000 00000000 00000000 ................
19CE2270: 00000002 001c021f 02797979 7a7a7a30 .........yyyzzz0
19CE2280: 001c0044 65656565 65656565 65654665 ...DeeeeeeeeeeFe
19CE2290: 65656565 65656565 652050a8 0f80ffff eeeeeeeee P.....
19CE22A0: ffffffff ffffffff ffffffff ffffffff ................
19CE22B0: ffffffff ffffffff ffffffff ffffffff ................
If we interleave two fixed length columns drop and add
alter table x add b char(1) default 'Q'
set nocount on
declare @i int
declare @c varchar(128)
declare @sql varchar(1000)
select @i = 0
while @i < 2000
begin
select @i = @i + 1
select @c = object_name(cn.constid) from sysconstraints cn, syscolumns c where c.id = cn.id and cn.colid = c.colid and c.name = 'a' and c.id = object_id('x')
select @sql = 'alter table x drop constraint ' + @c
exec (@sql)
alter table x drop column a
alter table x add a char(1) not null default 'P'
select @c = object_name(cn.constid) from sysconstraints cn, syscolumns c where c.id = cn.id and cn.colid = c.colid and c.name = 'b' and c.id = object_id('x')
select @sql = 'alter table x drop constraint ' + @c
exec (@sql)
alter table x drop column b
alter table x add b char(1) not null default 'Q'
end
name colid colorder xoffset offset type prec length
---- ------ -------- ------- ------ ---- ------ ------
s 1 1 4 2 47 1 1
t 2 2 16 3 39 11 11
u 3 3 15 13 47 1 1
y 4 4 -1 -1 39 6 6
z 2009 2009 -2 -1 39 5 5
a 8009 8009 4027 2 47 1 1
b 8010 8010 4028 2 47 1 1
Note that although the total row data size is 26 we can see from xoffset it will take up 4029 bytes of storage (+ variable length data)
What happens if we add another 5000 byte column
alter table x add c char(5000) not null default 'R'
Creation of table 'x' failed because the row size would be 10047, including internal overhead. This exceeds the maximum allowable table row size, 8060.
It would need to make the storage size greater than the max 8060 and so we receive an error.
Summary
Alter table can be useful for quick changes but should be used with caution.
In particular
When altering a fixed length column it can cause records to be copied so wasting space.
It can cause space to be wasted in every row.
It can cause the size of the row to exceed the 8060 byte limit although the structure would appear much less than that.
home