2

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.
Improve this question

1 Answer 1

Reset to default
5

Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 (11.x) 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 despite the number of rows in the table. Because, the existing rows in the table aren't updated during the operation. Instead, the default value is stored only in the metadata of the table and the value is looked up, as needed, in queries that access these rows. This behavior is automatic. No additional syntax is required to implement the online operation beyond the ADD COLUMN syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table despite its determinism. For example, the constant expression "My temporary data", or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() aren't runtime constants, because a unique value is produced for each row in the table. Adding a NOT NULL column with a default value that's not a runtime constant is always run offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.

Online schema change is still an Enterprise feature of SQL Server 2019 (15.x), so you perform an online operation on your Developer edition whereas on your Standard one you do it offline.

Improve this answer
3
  • True. The Developer Edition includes all the functionality of Enterprise edition. AFAIK, there is no option in the Developer Edition to not use Enterprise features. Commented Dec 2, 2021 at 8:09
  • 3
    Given the goal is to test schema upgrade performance where the edition matters, perhaps @MichaelD should look into a Visual Studio subscription so that SQL Server Standard Edition can used for development and testing (non-prod use). The subscription includes Azure credits too. Commented Dec 2, 2021 at 10:12
  • 1
    You can also economically use Standard Edition for tests with Azure VM pay-as-you-go. Commented Dec 2, 2021 at 20:11

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.