CODEWITHSUNDEEP
sqlsql-server-2008alter-table
Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

How the Alter Table command is handled by SQLServer?

We are using SQL Server 2008. We have an Existing database and it was required to ADD a new COLUMN to one of the Table which has 2700 rows only but one of its column is of type VARCHAR(8000). When i try to add new column (CHAR(1) NULL) by using ALTER table command, it takes too much time!! it took 5 minutes and the command was still running to i stopped the command. Below is the command, i was trying to add new column:

ALTER TABLE myTable Add ColumnName CHAR(1) NULL
  1. Can someone help me to understand that How the SQL Server handles the ALTER Table command? what happens exactly?
  2. Why it takes so much time to Add new column

EDIT:

  1. What is the effect of Table size on ALTER Command?

Upvotes: 0

Views: 531

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

Well, one thing to bear in mind is that you were adding a new fixed length column to the table. The way that rows are structured in storage, all fixed length columns are placed before all of the variable length columns, for each row. So every row would have had to be updated in storage to make this change.

If, in turn, this caused the number of rows which could be stored on each page to change, a great many new allocations may have been required.

That being said, for the number of rows indicated, I wouldn't have though it should take 5 minutes - unless, as Andomar indicated, there was some lock contention also involved.

Upvotes: 1

Andomar
Andomar

Reputation: 238096

Altering a table requires a schema lock. Many other operations require the same lock too. After all, it wouldn't make sense to add a column halfway a select statement.

So a likely explanation is that a process had the table locked for 5 minutes. The ALTER then has to wait until it gets the lock itself.

You can see blocked processes, and the blocking process, from the Activity Monitor in SQL Server Management Studio.

Upvotes: 3

Related Questions