SQL Interview: 26 Whole of data operations when adding columns in SQL Server

This is a post in the SQL Interview series. These aren’t trick or gotcha questions, they’re just questions designed to scope out a candidate’s knowledge around SQL Server and Azure SQL Database.

Section: Administration Level: Medium

Question:

You need to add additional columns to a table. You are concerned that you do not want to lock the table for long periods. You plan to avoid any operation that would rewrite every row i.e., avoid whole of data operations.

Which of the following commands could be executed without the need to rewrite all table rows?

ALTER TABLE dbo.Cinemas 
ADD AdditionalContact nvarchar(50) NULL;

ALTER TABLE dbo.Cinemas 
ADD AdditionalContact nvarchar(50) NOT NULL DEFAULT(N'');

ALTER TABLE dbo.Cinemas 
ADD NextReviewDate date NULL;

ALTER TABLE dbo.Cinemas 
ADD NextReviewDate date NOT NULL DEFAULT (SYSDATETIME());

Answer:

None of the commands require whole of data operations.

In SQL Server, you can add a column without rewriting all rows. You can also add a column with a default without rewriting all rows, unless the default value is different for each row. None of these meet that criteria.

2025-03-15