Is it safe to increase the size of a field in a SQL table on MS SQL server?
The field is a text field, it has millions of records, but recently the entries have been too big to fit so we need to increase the size of the field.
Assuming there is enough space on the server, and that any associated DTS or code that is run on the data can also be changed - could there be any problems associated with this?
Shouldn’t be a problem at all. Might take a little while, but I wouldn’t worry too much about it.
Of course, the DB is backed up and all that… right?
Backed up every day onto tapes.
Thanks for the reply
Yeah, I’ve had to do this often. Never seems to be an issue. I don’t think it even requires recreating the table behind the scenes, just issuing an ALTER TABLE statement against it.
It depends on how much bigger. If the physical space is fully populated there will be no room to put the new data where it should go in the disk. You will end up with chaining(The location ends up holding a pointer to the real physical location) and you can get performance hits as another disk read somewhere else is required.
Best to rebuild the table after you Alter it.
Ooh, interesting - never thought of that before!
Is there any way to get SQL server to tell you how much chaining there is in a table? I don’t really want to rebuild all my tables if they don’t all need it. 