October 22, 2010 at 11:28 am
I continue on with the rewrite of stored procedures in on inherited SQL installation. I am starting to run into an interesting and recurring procedure that gets a Newid() and then checks to be sure it isn't being duplicated.
DECLARE @leadSetID UNIQUEIDENTIFIER
WHILE @leadSetID IS NULL
BEGIN
SET @leadSetID = Newid()
IF EXISTS(SELECT *
FROM leadsets l (NOLOCK)
WHERE l.leadsetID = @leadSetID)
BEGIN
SET @leadSetID = NULL
END
END
My understanding is that this is not necessary. However, I wanted to check with those of you who have more experience.
Is this really needed?
October 23, 2010 at 5:02 am
Personally, no I wouldn't bother... but, there is a possibility, remote, rare, but certainly there, that you can get duplicates on GUIDs. So... you might want to leave it in place since it's already there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2010 at 1:24 pm
That is pretty much what I concluded. With the transaction volume we have, I may leave it in for a while and then take it out to compare execution times. Simply out of curiousity.
Thansk!
October 25, 2010 at 2:26 pm
The probability of it being duplicated is tiny. You'll reduce the impact of the query by changing it so instead of checking before-hand, it captures any insert error and handles accordingly. That gets rid of the lookup until it's necessary. Try...Catch can deal with this more efficiently.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2010 at 6:52 am
Agreed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply