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