SQL Interview: 10 System-defined default names

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: Development Level: Medium

Question:

When you define a column default with code like below:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Customers;
GO

CREATE TABLE dbo.Customers
(
    CustomerID int IDENTITY(1,1)
        CONSTRAINT PK_dbo_Customers PRIMARY KEY,
    TradingName nvarchar(100) NOT NULL,
    CreatedDate datetime2(3) NOT NULL DEFAULT (SYSDATETIME())
);
GO

the system will define the name of the default. Can you give examples of why specifying the name of the default instead of letting the system supply it would be a good practice?

Answer:

There are several reasons. Here are three:

If you ever need to change the default value, you will need to know the name of the default constraint to be able to remove it, before you add a new default. This is much easier if you already know the name of the default.

If you ever need to drop the column, in SQL Server, you must first drop any default on the column. Again, that is much easier if you already know the name of the default. (Note that other database engines like PostgreSQL do not allow you to name defaults, but they also automatically drop them when dropping columns).

If you have created the table in multiple databases and you are using database comparison tools to check for differences, having consistent names in the script avoids the potential detection of a difference. (Note that some comparison tools can ignore system names like these).

2021-04-15