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<\/i> 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.<\/p>\n
Of course this has the interesting effect that in the syscolumns<\/i> table the colid<\/i> for your rows aren’t sequential so they can’t be used as an index.<\/p>\n
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 ).<\/p>\n
CREATE TABLE ItsTooBig (col1 CHAR(100), col2 CHAR(100))
SELECT name, colid, xoffset
FROM syscolumns
WHERE OBJECT_ID(‘ItsTooBig’) = id<\/p>\n
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)<\/p>\n
IF @i % 10 = 0
SELECT @i, name, colid, xoffset
FROM syscolumns
WHERE OBJECT_ID(‘ItsTooBig’) = id
END
DROP TABLE ItsTooBig<\/p>\n
Take care out there people – those columns are out to get you.<\/p>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":167198,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-2508","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2508","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/167198"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2508"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2508\/revisions"}],"predecessor-version":[{"id":41536,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2508\/revisions\/41536"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2508"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2508"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2508"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2508"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}