I’m sure you all make changes to your schema over time adding columns to tables and occassionally dropping them. But were you aware that even though you drop a column from a table the space in the row continues to be taken up by the ghost of the column that you deleted. So if you make lots of schema changes to a database over time the row becomes much larger than it needs to be.

Of course this has the interesting effect that in the syscolumns table the colid for your rows aren’t sequential so they can’t be used as an index.

Just a little thing to be aware of and one of those things that doesn’t seem to be mentioned in books on-line ( at least in the 5 minutes I spent looking for it this morning ).

CREATE TABLE ItsTooBig (col1 CHAR(100), col2 CHAR(100))
SELECT name, colid, xoffset
   FROM syscolumns
   WHERE OBJECT_ID(‘ItsTooBig’) = id

DECLARE @i INT
SET @i = 1
WHILE @i < 100
BEGIN
   INSERT INTO ItsTooBig VALUES (1,@i)
   DECLARE @command VARCHAR(50)
   SET @command =
     ‘alter table ItsTooBig drop column col’
     + CAST(@i AS VARCHAR(3))
   EXEC(@command)
   
   SET @i = @i + 1
   
   SET @command =
     ‘alter table ItsTooBig add col’
        + CAST((@i+1) AS VARCHAR(3))
        + ‘ char(100)’
   EXEC(@command)

   IF @i % 10 = 0
      SELECT @i, name, colid, xoffset
         FROM syscolumns
         WHERE OBJECT_ID(‘ItsTooBig’) = id
END
DROP TABLE ItsTooBig

Take care out there people – those columns are out to get you.