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

Question

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.

Answer 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).

Answer 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.