简化使用NEWSEQUNETIALID（）列默认值的SQL插入

本文介绍了简化使用NEWSEQUNETIALID（）列默认值的SQL插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下插入存储过程：

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.