"Lock Escalation" is how SQL handles locking for large updates-- when SQL is going to change a lot of rows, it's more efficient to lock something larger (e.g. entire table) instead of many smaller things (e.g. row locks).
But this can be problematic when you have a huge table, because taking a lock on the entire table may lock out other queries for a long time. But there's a tradeoff: many small-granularity locks are slower than fewer (or one) coarse-grained locks, and having multiple queries locking different parts of a table creates the possibility for deadlock if one process is waiting on another.
There is a table-level option, LOCK_ESCALATION
, new in SQL 2008, which allows control of lock escalation. The default, "TABLE" allows locks to escalate all the way to the table level. DISABLE prevents lock escalation to the entire table in most cases. AUTO allows table locks except if the table is partitioned, in which case locks are only made up to the partition level. See this blog post for more info.
I suspect that the IDE adds this setting when re-creating a table because TABLE is the default in SQL 2008. Note that LOCK_ESCALATION isn't supported in SQL 2005, so you'll need to strip it if trying to run the script on a 2005 instance. Also, since TABLE is the default, you can safely remove that line when re-running your script.
Also note that, in SQL 2005 before this setting was present, all locks could escalate to table level-- in other words, "TABLE" was the only setting on SQL 2005.