SQL Server 2019 NULL default constraint for nullable column

Question

I have to take care of a SQL Server 2019 database. What I found is that a lot of the exisiting tables have nullable columns and even though an additional NULL default constraint.

Here an example:

CREATE TABLE [dbo].[Customer](

[ID] [INT] IDENTITY(1,1) NOT NULL,

...

**[Birthday] [DATETIME] NULL**,

...

)

GO

ALTER TABLE [dbo].[Customer] ADD DEFAULT (null) FOR [Birthday]

GO

For me this default constraint doesn't make sense. But what I want to know is if it is harming performance - means does SQL Server check it even tough it is some sort of obsolete?

Thanks for your help in advance

Best Regards

Johannes

Answer 1

Hi @Johannes Maly

For me this default constraint doesn't make sense.

Yes, it makes no difference. If a column is nullable and you do not provide a value for it, it will default to NULL.

But what I want to know is if it is harming performance - means does SQL Server check it even tough it is some sort of obsolete?

The default constraint itself will not harm performance. But the NULL values can affect performance.

For example, SQL queries must handle NULL values with special logic when doing comparisons.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

the column default value defines what value to use on an insert if the column is not specified in the insert statement. if not specified, the default value is null, so your schema is just formally defining the default value as null, instead of using the inferred value.

it is similar to specifying the default value for a variable to be null:

declare @v1 int
declare @v2 int = null

select @v1,@v2
> null, null

this should have no impact on performance.