SQL Server – how to define default value function that require column name?

All we need is an easy explanation of the problem, so here it is.

Column names are not permitted when declaring default column value. Is there a workaround to achieve something like this?

CREATE TABLE [dbo].[Tasks]
(
    [TaskId] INT IDENTITY(1,1) NOT NULL,
    [TaskName] NVARCHAR(255) NULL,
    [Priority] INT NOT NULL UNIQUE DEFAULT Max(Priority)+1, -- not allowed
)

The requirement is that by default newest tasks gets the lowest priority (highest int value). After the task is in table it then can be prioritised/deprioritised for example priority value swapped with any other task in the table.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’d implement this using a sequence.

The sequence generates a unique number each time it is called, which satisfies the UNIQUE constraint you have defined on the [Priority] column. We’ll use that as the default for the column value.

USE tempdb;

DROP TABLE IF EXISTS dbo.[Tasks];
DROP SEQUENCE IF EXISTS dbo.[tasks_priority_sequence]
GO

CREATE SEQUENCE dbo.[tasks_priority_sequence]
AS int
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE;
GO

CREATE TABLE dbo.[Tasks]
(
    [TaskId] int NOT NULL
        IDENTITY(1,1)
    , [TaskName] nvarchar(255) NULL
    , [Priority] int NULL 
        UNIQUE 
        DEFAULT (NEXT VALUE FOR dbo.[tasks_priority_sequence])
);
GO

INSERT INTO dbo.[Tasks] ([TaskName])
VALUES ('test1')
    , ('test2');

SELECT *
FROM dbo.[Tasks];

The results:

TaskId TaskName Priority
1 test1 1
2 test2 2
3 test1 3

While the use of a sequence can introduce gaps, the mere fact that you can supply any arbitrary value to the column indicates to me that you don’t require strict monotonically increasing values in the [Priority] column. If gaps are an issue for you, you can redefine the sequence using the NO CACHE pragma which will ensure no gaps are produced by the sequence.

The one caveat for this approach is the sequence will cause an error if you manually assign a value to the column that hasn’t yet been generated by the sequence. You could work around that using a trigger instead of a sequence, however the performance of using a trigger-based solution won’t be as linear as using a sequence. Here’s an example of the code that would cause an error:

--this works just fine
INSERT INTO dbo.[Tasks] (TaskName, [Priority])
VALUES ('test3', 3);

--this fails since the next value for the sequence already exists in the UNIQUE index.
INSERT INTO dbo.[Tasks] (TaskName)
VALUES ('test4')

The error:

Msg 2627, Level 14, State 1, Line 36
    Violation of UNIQUE KEY constraint 'UQ__Tasks__534DF97B192F9781'. 
Cannot insert duplicate key in object 'dbo.Tasks'. The duplicate key value is (3).

Method 2

If you really want a default value of "one more than the highest one so far," you can do that with a scalar user-defined function as the constraint definition.

Your function would look like this:

CREATE OR ALTER FUNCTION dbo.GetNextPriority()
RETURNS INT
AS
BEGIN;
    RETURN (SELECT MAX(t.[Priority] + 1) FROM dbo.Tasks t);
END;
GO

Note: this function will fail if the table is empty, because it will try to insert NULL into the non-nullable column. You can either handle that initial case in the function, or you have to guarantee an initial value is supplied in the first insert to the table

Then you would then add the default constraint to the table:

ALTER TABLE dbo.Tasks
ADD CONSTRAINT DF_Tasks_Priority
DEFAULT dbo.GetNextPriority() FOR [Priority];
GO

Note that the the query from that function will run on every insert into this table. This is probably a really bad idea if there will be a lot of inserts into the table.

Regardless, you’ll want to create an index on the column, so that only one row needs to be read in order to get the default value:

CREATE NONCLUSTERED INDEX IX_Priority 
ON dbo.Tasks ([Priority] DESC);
GO

Another important note to performance is that scalar UDFs in constraints will inhibit parallelism for that table.

If you’re concerned about the performance issues related to scalar UDFs (queries on every insert / lack of parallelism), take a look at Hannah’s answer, which uses Sequences instead.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply