Hi All
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
I could have simplified the above as:
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
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.
Any ideas anyone on simplifying the original query, but utilising newsequentialid()
? Or, is the original query the most simplified solution in achieving this?