Lock Escalation - What's happening here?

Question

While altering a table (removing a column) in SQL Server 2008, I clicked the Generate Change Script button and I noticed that the change script it generated drops the column, says "go" and then runs an additional ALTER TABLE statement that appears to set the lock escapation for the table to "TABLE". Example:

ALTER TABLE dbo.Contract SET (LOCK_ESCALATION = TABLE)

I should also note that this is the last thing the change script is doing. What is it doing here and why is it setting the LOCK_ESCALATION to TABLE?

---

Blades Don't Need Reloading...

Answer 1

I can't tell you why the scripting engine chose to add that to the script but TABLE is the default for LOCK_ESCALATION anyway so its not changing anything, unless you explicitly changed the LOCK_ESCALATION setting already.  I know other scripts script options as a part of their logic, but why specifically, you'd need to check with Microsoft to find out.  The BOL reference that covers this is

http://msdn.microsoft.com/en-us/library/ms190273.aspx

Also see the responses on your StackOverflow posting:

http://stackoverflow.com/questions/1703597/lock-escalation-whats-happening-here

they say the same thing.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Thanks Jon, my assumption is that it's setting is "TABLE" by default and so it's simply setting that at the end in case the script generator makes more granular locks.

---

Blades Don't Need Reloading...