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.
DROP TABLE IF EXISTS dbo.[Tasks];
DROP SEQUENCE IF EXISTS dbo.[tasks_priority_sequence]
CREATE SEQUENCE dbo.[tasks_priority_sequence]
START WITH 1
INCREMENT BY 1
CREATE TABLE dbo.[Tasks]
[TaskId] int NOT NULL
, [TaskName] nvarchar(255) NULL
, [Priority] int NULL
DEFAULT (NEXT VALUE FOR dbo.[tasks_priority_sequence])
INSERT INTO dbo.[Tasks] ([TaskName])
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)
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).