We want to minimize the downtime for our users during the upgrade process and want to ensure that our schema changes can all be performed in O(1) and are not O(n). Is there a list of documented schema changes that can be performed in O(1) with Microsoft SQL Server?
E.g. adding a nullable column is O(1) - at least our empiric tests show that.
This blog post by Russ Thomas – SQL Judo, as shared by one of the commenters here, has a list of several O(1) commands.
I'll do my best to create a more extensive list here.
Please note that while these commands are performed in O(1), some may require very high-level locks, and therefore may cause contention with other sessions in the database. Especially when potentially affecting dependent objects such as indexed views and foreign key constraints.
So, please don't take them lightly.
All of the following commands will be performed in O(1), not including waiting on locks on database objects:
CREATE commands, except CREATE INDEX (this does not include ALTER TABLE ... ADD ... commands).DROP commands, except the DROP of a CLUSTERED INDEX.ALTER TABLE ... SWITCH TO ... - this is commonly identified with partitioning, but you don't actually require partitioning on a table in order to use it. This is because technically, since SQL 2005, ALL tables are partitioned (having only partition 1 by default).CREATE NONCLUSTERED ... INDEX ... ON ... WITH STATISTICS_ONLY - this creates a hypothetical index in O(1).ALTER TABLE ... DROP ... commands, except the DROP of a CLUSTERED constraint (such as a UNIQUE or PRIMARY KEY constraint).ALTER TABLE ... ADD CONSTRAINT ... WITH NOCHECK (the NOCHECK option is crucial for avoiding retroactively checking all existing data in the table).ALTER TABLE ... ADD DEFAULT ... on an existing column should be performed in O(1).ALTER TABLE ... ALTER COLUMN ... when increasing the maxsize of variable length columns (varchar, nvarchar, varbinary) should be performed in O(1).If I recall additional commands, I'll edit this message and add them to the list.
