简化使用NEWSEQUNETIALID（）列默认值的SQL插入 [英] Simplify SQL Insert which uses NEWSEQUNETIALID() column default

问题描述

我有以下插入存储过程：

I have the following insert stored procedure:

CREATE Procedure dbo.APPL_ServerEnvironmentInsert
(
    @ServerEnvironmentName varchar(50),
    @ServerEnvironmentDescription varchar(1000),
    @UserCreatedId uniqueidentifier,
    @ServerEnvironmentId uniqueidentifier OUTPUT
)
WITH RECOMPILE
AS
    -- Stores the ServerEnvironmentId.
    DECLARE @APPL_ServerEnvironment TABLE (ServerEnvironmentId uniqueidentifier)

    -- If @ServerEnvironmentId was not supplied.
    IF (@ServerEnvironmentId IS NULL)
    BEGIN
        -- Insert the data into the table.
        INSERT INTO APPL_ServerEnvironment WITH(TABLOCKX)
        (
                ServerEnvironmentName,
                ServerEnvironmentDescription,
                DateCreated,
                UserCreatedId
        )
        OUTPUT Inserted.ServerEnvironmentId INTO @APPL_ServerEnvironment
        VALUES
        (
                @ServerEnvironmentName,
                @ServerEnvironmentDescription,
                GETDATE(),
                @UserCreatedId
        )

        -- Get the ServerEnvironmentId.
        SELECT @ServerEnvironmentId = ServerEnvironmentId
        FROM @APPL_ServerEnvironment
    END
    ELSE
    BEGIN
        -- Insert the data into the table.
        INSERT INTO APPL_ServerEnvironment WITH(TABLOCKX)
        (
                ServerEnvironmentId,
                ServerEnvironmentName,
                ServerEnvironmentDescription,
                DateCreated,
                UserCreatedId
        )
        VALUES
        (
                @ServerEnvironmentId,
                @ServerEnvironmentName,
                @ServerEnvironmentDescription,
                GETDATE(),
                @UserCreatedId
        )
    END
GO

我可以简化为：

CREATE Procedure dbo.APPL_ServerEnvironmentInsert
(
    @ServerEnvironmentName varchar(50),
    @ServerEnvironmentDescription varchar(1000),
    @UserCreatedId uniqueidentifier,
    @ServerEnvironmentId uniqueidentifier OUTPUT
)
WITH RECOMPILE
AS
-- Ensure @ServerEnvironmentId IS NOT NULL
SELECT ISNULL(@ServerEnvironmentId, newid())

-- Insert the data into the table.
INSERT INTO APPL_ServerEnvironment WITH(TABLOCKX)
(
    ServerEnvironmentId,
    ServerEnvironmentName,
    ServerEnvironmentDescription,
    DateCreated,
    UserCreatedId
)
VALUES
(
    @ServerEnvironmentId,
    @ServerEnvironmentName,
    @ServerEnvironmentDescription,
    GETDATE(),
    @UserCreatedId
)
GO

但是这样做，我失去了<$ c $的性能改进超过 newid（）的c> newsequentialid（）。 newsequentialid（）不能设置在代码中，如 newid（）一样，它只能作为表列级别的默认值提供。

But by doing so, i loose the performance improvements of the newsequentialid() over newid(). newsequentialid() can not be set in code as newid(), it can only be supplied as a default value on a table column level.

关于简化原始查询，但利用 newsequentialid（）的任何想法吗？或者，原始查询是实现此目的最简化的解决方案吗？

Any ideas anyone on simplifying the original query, but utilising newsequentialid()? Or, is the original query the most simplified solution in achieving this?

推荐答案

我的原始想法是正确的。这是最简单，最易读的解决方案。

My original idea was correct. It is the simplest and most readable solution possible.

这篇关于简化使用NEWSEQUNETIALID（）列默认值的SQL插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！