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.
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];
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')
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).
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 🙂