August 29, 2013 at 12:05 pm
I recently finished reading this article wrote by Mr Jeff Moden.
http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
We wants to add "Multiples of 3" in our table like 3, 6, 9, 12. So we made a table for this goal with this structure:
create TABLE temp
(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SomeInt INT, Expected INT)
INSERT INTO temp (SomeInt, Expected)
SELECT 0,3 UNION ALL SELECT 0,6 UNION ALL SELECT 0,9 UNION ALL SELECT 0,12
Now we insert 3, 6, 9, 12 into our table with this query without any problem:
DECLARE @N INT, @Anchor INT
SELECT @N = 0
UPDATE temp
SET @N = SomeInt = @N + 3, --Adds 3 to N and updates SomeInt with N
@Anchor = RowNum
FROM temp
OPTION (MAXDOP 1)
But this query return unexpected results:
DECLARE @N INT, @Anchor INT
SELECT @N = 0
UPDATE temp
SET @N = @N + 1, --Adds 1 to N
@N = SomeInt = @N + 2, --"Forgets" to do @N + 2 after first row
@Anchor = RowNum
FROM temp
OPTION (MAXDOP 1)
Jeff Moden mentioned "There's a very long winded explanation about this that I'll very gratiously step aside".
And also he mentioned "YOU MUST HAVE AN "ANCHOR" COLUMN" like @Anchor = RowNum. He also said "there are some unpredictable cases where an error can occur without anchor"
Now I want to know why? I want to know the exact reasons. I want to realize it logically.
What makes 3 part update sometimes don't return appropriate results?
Also why I have to use an anchor in my query while logically there is no need of it?
Warmly thank you for your time and help
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy