July 3, 2012 at 1:29 am
Hi,
I have inherited 90+ databases.
On the front end there are mostly VB6 applications.
The norm was apparently just to use char(x) datatypes for text.
In all the databases there are 7300+ char(20+) columns.
I am wanting to change all the char(20+) to an equal length varchar.
The goals being the following.
1) Smaller database files
2) Less system resources to return or manipulate data
3) Less network traffic
4) Faster backups
5) Faster archiving
6) Faster restore
The steps involved:
1) Alter table <tblname> Alter column <colname> varchar(x)
2) Update <tblname> set <colname> = ltrim(rtrim(<colname>))
3) Rebuild one or all indexes on the table if available or create a new col with an index and drop it.
4) Shrink the data files or use shrink database. (Not recommended, but think it's ok in this case)
What I don't understand is why my 22gb database ended up 5gb bigger.
Also why my log file grew to 14gb in simple recovery mode?
Any ideas and / or advice would be greatly appreciated.
Thanks.
July 3, 2012 at 2:34 am
Rebuilding indexes requires space, so if there is no space in the DB then it will add the space to the file to rebuild successfully which is probably why you saw the file increase.
VARCHAR(50) is actually 52 as it requires 2 extra bytes for the varying length marker (not sure if thats the right term), so if your column was CHAR(50) and had 50 chars in it, then you have changed it to effectivly VARCHAR(52) so extra storage is needed again adds to increased disk usage.
Transaction log growth, again each column change, column update, index modification is logged to the transaction log, so if it was all done in one big transaction expect 1 big transaction log.
My advise, would be to shrink the files then rebuild, as shrinking will cause your newly rebuilt indexes to become fragmented, so why rebuild again. Once shrunk and rebuilt, size the files according to your growth factors.
July 3, 2012 at 3:58 am
Thanks for your input.
I think I will have to rebuild, shrink then rebuild again.
Rebuilding the index releases any freespace from the table. (As shown in my test code below)
Shrinking will fragment the indexes
Rebuilding the index to defragment the indexes.
Maybe I should only be changing chars in either very large tables or that have a large value. Char(1000+).
DROP TABLE TestChar
go
CREATE TABLE TestChar
(
ID TinyInt IDENTITY NOT NULL,
TextVal Char(100) NOT NULL
CONSTRAINT PK_TestChar PRIMARY KEY (ID)
)
go
sp_spaceused 'TestChar'
go
--name rows reserved data index_size unused
--testchar 0 0 KB 0 KB 0 KB 0 KB
insert into TestChar (textval) values ('A')
insert into TestChar (textval) values ('B')
insert into TestChar (textval) values ('C')
insert into TestChar (textval) values ('D')
insert into TestChar (textval) values ('E')
insert into TestChar (textval) values ('F')
insert into TestChar (textval) values ('G')
insert into TestChar (textval) values ('H')
insert into TestChar (textval) values ('I')
insert into TestChar (textval) values ('J')
insert into TestChar (textval) values ('K')
insert into TestChar (textval) values ('L')
insert into TestChar (textval) values ('M')
insert into TestChar (textval) values ('N')
insert into TestChar (textval) values ('O')
insert into TestChar (textval) values ('P')
insert into TestChar (textval) values ('Q')
insert into TestChar (textval) values ('R')
insert into TestChar (textval) values ('S')
insert into TestChar (textval) values ('T')
insert into TestChar (textval) values ('U')
insert into TestChar (textval) values ('V')
insert into TestChar (textval) values ('W')
insert into TestChar (textval) values ('X')
insert into TestChar (textval) values ('Y')
insert into TestChar (textval) values ('Z')
GO
sp_spaceused 'TestChar'
GO
--name rows reserved data index_size unused
--testchar 26 264 KB 208 KB 8 KB 48 KB
ALTER TABLE TestChar
ALTER COLUMN TextVal Varchar(100) NOT NULL
GO
sp_spaceused 'TestChar'
GO
--name rows reserved data index_size unused
--testchar 26 528 KB 424 KB 8 KB 96 KB
UPDATE TestChar SET
TextVal = LTRIM(RTRIM(TextVal))
GO
sp_spaceused 'TestChar'
GO
--name rows reserved data index_size unused
--testchar 26 272 KB 216 KB 8 KB 48 KB
ALTER INDEX PK_TestChar ON TestChar REBUILD
GO
sp_spaceused 'TestChar'
GO
--name rows reserved data index_size unused
--TestChar 26 16 KB 8 KB 8 KB 0 KB
July 3, 2012 at 12:18 pm
Consider that while CHAR columns would occupy more space if your columns were not mostly full they help us avoid page splits when inserting and updating. If your tables receive a ton of these types of transactions consider the tradeoff between space and the page split.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply