主页 / dba / 问题 / 294939
Accepted
Pawel
Asked: 2021-06-29 08:07:32 +0800 CST

SQL Server - 如何定义需要列名的默认值函数？

  • 772

声明默认列值时不允许使用列名。是否有解决方法来实现这样的目标？

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
)

要求是默认情况下最新任务获得最低优先级（最高 int 值）。在任务在表中之后，可以对其进行优先级/取消优先级，例如与表中的任何其他任务交换优先级值。

sql-server table

2 个回答

  1. Best Answer

    我会使用一个序列来实现它。

    该序列每次调用时都会生成一个唯一编号，它满足您在列UNIQUE上定义的约束。[Priority]我们将使用它作为列值的默认值。

    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];

    结果：

    任务 ID 任务名称 优先
    1 测试1 1
    2 测试2 2
    3 测试1 3

    虽然使用序列会引入间隙，但您可以向列提供任意值这一事实向我表明，您不需要在[Priority]列中严格单调递增的值。如果间隙对您来说是个问题，您可以使用NO CACHEpragma 重新定义序列，这将确保序列不会产生间隙。

    这种方法的一个警告是，如果您手动为尚未由序列生成的列分配值，则序列将导致错误。您可以使用触发器而不是序列来解决这个问题，但是使用基于触发器的解决方案的性能不会像使用序列那样线性。下面是一个会导致错误的代码示例：

    --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')

    错误：

    消息 2627，第 14 层，状态 1，第 36 行
    违反 UNIQUE KEY 约束“UQ__Tasks__534DF97B192F9781”。
无法在对象“dbo.Tasks”中插入重复键。重复键值为 (3)。
    • 6

  2. 如果你真的想要一个默认值“比迄今为止的最高值多一个”，你可以使用一个标量用户定义函数作为约束定义来做到这一点。

    您的函数如下所示：

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

    注意：如果表为空，此函数将失败，因为它会尝试插入NULL不可为空的列。您可以在函数中处理该初始情况，或者您必须保证在第一次插入表时提供初始值

    然后，您将向表中添加默认约束：

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

    请注意，该函数的查询将在每次插入此表时运行。如果表中会有很多插入，这可能是一个非常糟糕的主意。

    无论如何，您需要在列上创建一个索引，以便只需要读取一行即可获得默认值：

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

    另一个重要的性能注意事项是约束中的标量 UDF 将抑制该表的并行性。

    如果您担心与标量 UDF 相关的性能问题（每次插入时查询/缺乏并行性），请查看Hannah 的答案，它使用序列代替。

    • -1

相关问题