February 18, 2008 at 4:05 pm
Hello!
If I do this:
ALTER TABLE tblOrder ADD testCol BIT DEFAULT(0)
Other than a sch-s lock, is there any other types of locks that could possibly be used? This is a big table and used intensively, both read and write. I had bad experience in the past with default. If there was no default, the query ran pretty fast, if there was, the query ran slow and seemed to be locked up or locking others. So I had to first create the column with no default, fill in the default value a few hundred lines at a time, then add the default constraint. I did not have a chance to check what was happening. Could I get some insight on it?
Thanks!
Kathleen
February 18, 2008 at 4:10 pm
If you are adding a column with a default value, SQL Server has to update the physical data pages with that value. In order to do that, it will get the necessary lock it needs to make this happen. As far as what kinds of locks it will use, your guess is as good as mine. SQL Server will determine which locking level is most appropriate and use it. If you are worried about blocking and contention, I would suggest going the route you did before where you add it w/o a default and run updates in batches and then apply the default once all of the rows have been updated.
February 19, 2008 at 9:07 am
Thanks for the reply.
I did a little experiment this morning. I thought I would just go forward with the default value because it is a lot of hassle running update in batches. I was ready to kill my own process if it did not finish running in a minute or so, or if it shows blocking tons of other processes. But guess what, it finished in one second. I guess thats because its early of the day and traffic has not started picking up yet, I was lucky to get the locks I needed for the change right away.
Thanks for the input!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply