We have 2 exact same Database Environments. The second environment contains a copy of the production database and hosts approx 11M records in the Invoice table. The goal of this environment is used to see how long specific upgrade queries will take to know if there will be any downtime (as tables are locked during schema change)
upon executing the add statement in the second environment
alter table Invoice add IsVerified bit not null default(0)
The query exits immediately, which is strange as there are 11M records in it. I'd expect at least a little delay. Even a select count(*) takes longer. On the main production database however, it takes a lot longer, more than 30 seconds so we have to plan this query into a special maintenance window. While the query was being executed there was nothing blocking the SPID (checked using sp_who2)
What could be the reason, that the second copy of the database seemingly has no effort at all to add a column in a 11M record DB while the other maindb cannot finish in time (<30 seconds).Maybe some special setting that allows you to add a default value column without the need to be written in all records? Could it be because our test environment is Developer edition while the production environment is Standard edition? Maybe some special features in Developer edition that are not active in SQLStandard?
select count(*) from Invoice //result: 11701200 SQL Server Execution Times: CPU time = 2375 ms, elapsed time = 608 ms.
Script to add:
alter table Invoice add IsVerified bit not null default(0) SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 12 ms.