ALTER TABLE - Adding Column - Column Order Impact

Most of us would have altered a table structre by adding a column to the table. When an ALTER table script is used to add the column to the table, the column is placed on the last of the table. For inserting a column, in the middle of the table one needs to use the SQL Server Management Studio ( SSMS ).ie., Right click on the table and pick design table and then proceed to add a column. This post will deal with the significance of column order while adding a column to the table.

Consider the table 'sample'. The table contains about 800 rows with a size of 6 MB. Relatively small by a normal database standards.



Let us add column in the middle using SSMS as shown below. Once we click on save button to save the column addition, the operation completes immediateley.


Let me add a few more rows into the table. Now the table contains over 200K rows and the size of the table is 1.6 GB.



Now let me add a column to the middle of the table using SSMS as done proviously. Now the operation takes much much longer. Just in case if you face timeout error refer here.

Now the operation takes few hours to complete and the new column will be inserted between two columns as shown below.




Let us add one more column at the last of the table ( not in between the columns as doen previously). Refer to picture below.

ALTER TABLE sample ADD col3_int CHAR(5) 



Now the operation takes in 0 seconds to complete. The points to note are provided below.

* When we add a column to the middle of the table using SSMS, when the number of rows are higher, it consumes a longer execution time and resource.

However, when the number of rows are lesser it doesnt consume much of time and CPU/IO resource. This aspect is to be handled carefully where a DBA can fall for the trap if overlooked.

Assume, DBA is planning to perform a small column addition on the production server. DBA has already tested in staging and it was over in few seconds. DBA assumes that the operation is going to take a few seconds in production and plans accordingly. If the number of rows are higher in production, the DBA can be taken for ride and it can be different ball game all together.

So lesson to be learnt is Dont underestimate any table modification prepations and make sure to check size/# of rows on production before deployment.

* When the column was added to the end of the table ( without caring about position of the column ) using T-SQL script ( ALTER TABLE coomand ),it completed immediately without consuming much of time and resource, though the number of rows were very high.

Lesson learnt is insertion of a column to the middle ( or rather between two other columns ) of the table should not be done, unless there is a strong reason to do so. If there is no strong reason, then always add the column to the end of the table using ALTER TABLE script as they take much much lesser time to execute.

* Use scripts instead of SSMS GUI especially while performing table strucutre modifications or DDL operations.

We will take a much closer look in the next post exploring why such a behavior is observed.

Altering Table structure - SSMS - Timeout Expired

Altering a table using SQL Server Management Studio ( SSMS ) can be done by right clicking on the table and by picking the design table as shown below.



While adding a column, especially for a huge table, then management studio prompts saying changing the data can consume lot of resources and time as shown below.



After clicking 'yes', if the alter table takes longer than 30 secs then the alter table fails with the error message 'Time out expired' as shown below.



The error can be avoided by changing the default setting in SSMS as shown below. Goto Tools->Options->Tables and Database Designer and set the option Transaction time out after to 1800 seconds from default 30 seconds . The default setting is shown below.

Database Owner - Error Message

Sometimes, a simple task that as a DBA you do day in and day out,
can throw wierd errors.

You right click on a database and click on Properties to check
the Database Size/ Database path. Suddenly, a error message as shown
below is thrown at you.



The error message says the problem is with the database owner. When the database owner is removed ( perhaps the login was dropped ) , such a error message is thrown.The problem can be rectified by using the command


EXEC MASTER.dbo.Sp_changedbowner 'sa'
GO 

The command sets database owner as dbo and solves the problem. Now we can comfortably view database property from SSMS as usual.

Please note that sp_changedbowner command is marked for depcreation and users are recommended to use ALTER AUTHORIZATRION command.syntax is provided below.

ALTER AUTHORIZATION ON DATABASE::dbname TO principal;

For more details on AUTHORIZATION refer here

I am back

I am back again!!!. It has been ages since I posted.An important event in my life kept me away from blogging. Now I am happily married to Sharadha. :) Till date, I havent posted a photograph of mine in this blog. Today is certainly a good day to that.


So, from now on the blog will be active as it was before and special thanks to all the readers for visiting even when it was not frequently updated.