January 11, 2012 at 9:32 am
Hi,
I'm editing a large stored procedure (very few comments) which has 3 methods (so far) of getting or creating an @ID variable - which is then used immediately.
I'd like your opinions on which construct you would favour, and why.
CREATE TABLE myTable (pKey INT IDENTITY(1,1) PRIMARY KEY,
CODE VARCHAR(10));
DECLARE @MYCODE VARCHAR(10);
SET @MYCODE = 'MYCODE';
DECLARE @ID INT;
--- Option 1 Using ROWCOUNT
SELECT @ID = pKey FROM myTable (NOLOCK) WHERE CODE=@MYCODE;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO myTable (CODE)
VALUES (@MYCODE);
SET @ID=@@IDENTITY;
END
-- Option 2
SELECT @ID = pKey FROM myTable WHERE CODE=@MYCODE;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO myTable (CODE)
VALUES (@MYCODE);
SELECT @ID=SCOPE_IDENTITY();
END
--- Option 3 Using EXISTS
IF NOT EXISTS (SELECT * FROM myTable WHERE CODE=@MYCODE)
BEGIN
INSERT INTO myTable (CODE)
VALUES (@MYCODE);
END
SELECT @ID=pKey FROM myTable WHERE CODE=@MYCODE;
January 11, 2012 at 9:35 am
In that case, 2 (though I might check if the variable is null or not rather than @@RowCount (which might be affected by triggers), and use Output instead of the scope_identity function)
Not 1 because of possible triggers affecting @@Identity
Not 3 because it has you selecting from the table twice (why)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2012 at 9:36 am
none of those 🙂
I would use the 3rd one (EXISTS) at the beginning and get the ID with SCOPE_IDENTITY at the end !
January 11, 2012 at 9:40 am
How about option 4:
Declare @ids Table (id INT);
IF NOT EXISTS (SELECT * FROM myTable WHERE CODE=@MYCODE)
BEGIN
INSERT INTO myTable (CODE)
OUTPUT Inserted.id into @ids
VALUES (@MYCODE);
Select @id = id from @ids
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 11, 2012 at 9:40 am
azdzn (1/11/2012)
none of those 🙂I would use the 3rd one (EXISTS) at the beginning and get the ID with SCOPE_IDENTITY at the end !
In that case, how would you get the ID if it did exist (and hence didn't insert)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2012 at 9:41 am
Jack Corbett (1/11/2012)
How about option 4:
Declare @ids Table (id INT);IF NOT EXISTS (SELECT * FROM myTable WHERE CODE=@MYCODE)
BEGIN
INSERT INTO myTable (CODE)
OUTPUT Inserted.id into @ids
VALUES (@MYCODE);
Select @id = id from @ids
END
Again, how does that get you the value for ID for that code if it's already in the table and doesn't need inserting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2012 at 9:46 am
GilaMonster (1/11/2012)
azdzn (1/11/2012)
none of those 🙂I would use the 3rd one (EXISTS) at the beginning and get the ID with SCOPE_IDENTITY at the end !
In that case, how would you get the ID if it did exist (and hence didn't insert)?
Yes you're right Gail. Option 2 then !
January 11, 2012 at 9:49 am
GilaMonster (1/11/2012)
Jack Corbett (1/11/2012)
How about option 4:
Declare @ids Table (id INT);IF NOT EXISTS (SELECT * FROM myTable WHERE CODE=@MYCODE)
BEGIN
INSERT INTO myTable (CODE)
OUTPUT Inserted.id into @ids
VALUES (@MYCODE);
Select @id = id from @ids
END
Again, how does that get you the value for ID for that code if it's already in the table and doesn't need inserting?
Oops, you are right, I missed that part. Yes I'd probably do option 2 then.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 11, 2012 at 9:58 am
P.S. I'll offer the anti-pattern that I've been cleaning out of some procedures recently (comments mine)
DECLARE @count int, @id int
SET @Count = (SELECT count(*) from myTable WHERE Code = @MyCode) -- count all rows to see if there are any... Isn't there another function for that?
IF @Count <= 0
BEGIN
Insert into myTable (Code)
Values (@MyCode)
SET @id = MAX(id) FROM MyTable -- race condition, what race condition???
END
SET @id = (SELECT id FROM MyTable WHERE Code = @MyCode) -- let's hope there's a unique constraint. Oh wait, no constraints.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2012 at 10:35 am
None of them for the reasons already stated, plus option 2 also contains a race condition.
DECLARE @MYCODE int = NULL
DECLARE @ID int = NULL
DECLARE @OUTPUT TABLE (ID int)
MERGE dbo.myTable WITH (HOLDLOCK) AS t
USING (VALUES(@MYCODE)) AS u (MyCode)
ON EXISTS (SELECT t.CODE INTERSECT SELECT u.MyCode)
WHEN NOT MATCHED THEN INSERT (CODE) VALUES (@MYCODE)
WHEN MATCHED THEN UPDATE SET @ID = t.pKey
OUTPUT INSERTED.pKey INTO @OUTPUT (ID);
SELECT
CASE
WHEN @ID IS NOT NULL THEN @ID
ELSE (SELECT TOP(1) ID FROM @OUTPUT)
END
January 11, 2012 at 10:41 am
SQL Kiwi (1/11/2012)
None of them for the reasons already stated, plus option 2 also contains a race condition.
DECLARE @MYCODE int = NULL
DECLARE @ID int = NULL
DECLARE @OUTPUT TABLE (ID int)
MERGE dbo.myTable WITH (HOLDLOCK) AS t
USING (VALUES(@MYCODE)) AS u (MyCode)
ON EXISTS (SELECT t.CODE INTERSECT SELECT u.MyCode)
WHEN NOT MATCHED THEN INSERT (CODE) VALUES (@MYCODE)
WHEN MATCHED THEN UPDATE SET @ID = t.pKey
OUTPUT INSERTED.pKey INTO @OUTPUT (ID);
SELECT
CASE
WHEN @ID IS NOT NULL THEN @ID
ELSE (SELECT TOP(1) ID FROM @OUTPUT)
END
Oh! I knew there was a "simple" answer! 😛
-- Gianluca Sartori
January 11, 2012 at 10:44 am
Also none of the options allows you to insert a NULL code.
January 11, 2012 at 10:45 am
SQL Kiwi (1/11/2012)
None of them for the reasons already stated, plus option 2 also contains a race condition.
DECLARE @MYCODE int = NULL
DECLARE @ID int = NULL
DECLARE @OUTPUT TABLE (ID int)
MERGE dbo.myTable WITH (HOLDLOCK) AS t
USING (VALUES(@MYCODE)) AS u (MyCode)
ON EXISTS (SELECT t.CODE INTERSECT SELECT u.MyCode)
WHEN NOT MATCHED THEN INSERT (CODE) VALUES (@MYCODE)
WHEN MATCHED THEN UPDATE SET @ID = t.pKey
OUTPUT INSERTED.pKey INTO @OUTPUT (ID);
SELECT
CASE
WHEN @ID IS NOT NULL THEN @ID
ELSE (SELECT TOP(1) ID FROM @OUTPUT)
END
1. I haven't really used MERGE much so I don't think it of it, but it is probably the best solution in this case.
2. What's the race condition in 2?
3. I would use the select case to set @ID at the end because I'd use it as an output parameter instead of returning a result set.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 11, 2012 at 10:51 am
Jack Corbett (1/11/2012)
2. What's the race condition in 2?
A row could be inserted by someone else between the select and the insert running. Can also counter that with transaction & higher isolation level, or with a INSERT ... SELECT WHERE NOT EXISTS (and a second query of a the row if that does happen)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2012 at 10:54 am
Rewritten for mere mortals (like myself):
CREATE TABLE myTable (
pKey INT IDENTITY(1,1) PRIMARY KEY,
CODE VARCHAR(10)
);
DECLARE @MYCODE VARCHAR(10) = 'MYCODE';
DECLARE @ID int = NULL;
DECLARE @OUTPUT TABLE (ID int);
WITH TestedValue (MyCode) AS (
SELECT @MYCODE
)
MERGE dbo.myTable WITH (HOLDLOCK) AS t
USING TestedValue AS u
ON t.CODE = u.MyCode
WHEN NOT MATCHED THEN INSERT (CODE) VALUES (@MYCODE)
WHEN MATCHED THEN UPDATE SET @ID = t.pKey
OUTPUT INSERTED.pKey INTO @OUTPUT (ID);
SELECT
CASE
WHEN @ID IS NOT NULL THEN @ID
ELSE (SELECT TOP(1) ID FROM @OUTPUT)
END;
@paul-2: why HOLDLOCK? Is there a chance of intra-query non-repeatable reads in MERGE statements?
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply