Sunday 3 February 2013

Beware SQL Developer/Enterprise feature add column on Standard edition

I always add new columns to SQL Server 2012 on large tables out of hours due to the extended table locks required. Factor in merge replication which I use heavily and is usually impossible to recover from schema failure (mitigated potentially by sp_markpendingschemachange) and I usually take a full backup of publisher and all subscribers (which are fixed servers, not mobile clients making it simple) before making schema changes.

A developer said they didn't understand the problem as schema changes on their 'dev' database only took a second. I had to explain that the Production database was on SQL Server 2012 Standard Edition and they were using SQL Server 2012 Developer Edition, and that Developer Edition is the same as Enterprise Edition, including the feature that allows Enterprise Edition to add columns almost instantly for most standard schema changes. here is an extract from SQL Server 2012 BOL.


Adding NOT NULL Columns as an Online Operation
In SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table.


A standard new column line syntax for a merge replicated table might be:
alter table mytable add mycolumn int constraint de_myconstraint default 0 not null
where the new column is defined in 1 DDL operation.

This could easily catch out unwitting users who try out a schema change on their 'dev' database, finds it completes subsecond and then try the same on Standard Edition and wait around for a potentially long time waiting for the schema locks to be dropped.
This Developer Edition vs Standard Edition problem has continued to get worse over the years and the feature difference is now very large.
If only MS would put a trace flag or other feature to allow Developer edition to behave as Standard Edition or other editions things would be much simpler all round.

No comments:

Post a Comment