Sql Server Internal: Negative Side Effects of Altering Tables

What do you think, what happens when you alter your database table in Sql Server. Most of the time Sql Server just make changes in meta data and NO physical changes are made. It commonly happens when you:

o   Drop a column

o   Add a new column and assumes NULL as the new value for all rows

o   Increase length of  a variable-length column

o   Change a  non-nullable column to allow NULLs

Due to this behavior few major negative side effects can be observed.

1.       When a fixed length column (i.e. column with data type CHAR, NCHAR, INT, SMALL INT etc) is altered to increase its length. The old column is not actually replaced. Rather, a new column is added to the table, and DBCC PAGE shows you that the old data is still there.

2.       When you try to decrease length of fixed length column. It just make changes in meta data, that column values will be according to new mentioned length and NO PHYSICAL change occur. It means size is never deceased. For example if you want to change CHAR(15) column to CHAR(10). Sql Server will never decrease its length to 10. Column length will remain 15 physically and its only meta data which will bind you to use only 10 character length.

3.       One more drawback is, when you drop a column from your table. NO such action is performed (Column is not dropped physically) and only meta data is changed, so in future you can’t see or use it. No space is released and column still exists.

SOLUTION:

               Make your desired changes through ALTER TABLE and then reclaim table space by recreating table or just rebuild clustered index.