Adding column with conditional default value based on existing field in SQL Server

Data and Databases
1

I have an existing SQL Server table with data already in it. I need to modify a column so that its default value depends on another column’s value.

My current table structure:

CREATE TABLE [dbo].[EmployeeSettings]
(
   [SettingID] [VARCHAR](40) NOT NULL,
   [SettingName] [NVARCHAR](100) NOT NULL,
   [WorkingDaysCount] [INT] NOT NULL,
   [HasFixedSchedule] [BIT] NOT NULL,
   CONSTRAINT [PK_EmployeeSettings] PRIMARY KEY CLUSTERED (
   [SettingID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY]
GO

I want to change the HasFixedSchedule column so that it automatically gets set to 1 (true) when WorkingDaysCount is not equal to zero. Since there are already rows in the table, I need to use ALTER TABLE statement.

What’s the correct way to set up this kind of conditional default value? I tried a few approaches but couldn’t get the syntax right.

2

Interesting challenge! Why not update the existing data first, then use a trigger for new inserts? You could run an UPDATE to fix current rows, then create an INSERT trigger for future records. What errors did you hit when you tried this?

3

SQL Server doesn’t support dynamic default constraints that reference other columns in the same row. Default constraints can only use constants, functions like GETDATE(), or system values. But you can achieve this functionality using computed columns or triggers. To implement your requirement, consider converting HasFixedSchedule to a computed column:

ALTER TABLE [dbo].[EmployeeSettings]
DROP COLUMN [HasFixedSchedule];

ALTER TABLE [dbo].[EmployeeSettings]
ADD [HasFixedSchedule] AS CASE WHEN [WorkingDaysCount] <> 0 THEN 1 ELSE 0 END PERSISTED;

This approach calculates HasFixedSchedule based on WorkingDaysCount and stores it physically due to the PERSISTED keyword. The value updates automatically when WorkingDaysCount changes. Alternatively, if preserving the existing column structure is vital, you can implement an UPDATE trigger instead; however, computed columns are often more efficient for derived data.

4

yeah, sql server doesn’t allow dynamic defaults based on other columns. best bet is to go with a trigger or computed column to update HasFixedSchedule. but keep in mind, triggers slow down inserts if there’s a ton of data coming in.