T-SQL Puzzle for Update in SQL Server 2005 second method

I wrote one article for showing power of simple UPDATE statement in critical situation. You can refer that article at

http://www.sqlhub.com/2009/04/t-sql-puzzle-for-update-in-sql-server.html

After reading that article, one of my blog’s regular readers Rashmika sent me one script to do the same task. Herewith, I am giving the script by given Rashmika. I have used simple UPDATE statement and she has used new feature introduced from SQL Server 2005 CTE. I always like to publish comment, concern, query or solutions given by my readers.

Note: If you have not read my previous article on this topic than kindly read it to understand more.

Create Table SQLPuzzle

(

ChildID Int,

ParentID Int,

SortOrder Int

)

--data

INSERT INTO SQLPuzzle VALUES (1,1,5)

INSERT INTO SQLPuzzle VALUES (2,1,10)

INSERT INTO SQLPuzzle VALUES (3,2,25)

INSERT INTO SQLPuzzle VALUES (4,3,10)

INSERT INTO SQLPuzzle VALUES (5,2,40)

GO

with cte as

(

SELECT ROW_NUMBER() OVER( ORDER BY Parentid ) AS 'rownumber',

ROW_NUMBER() OVER(PARTITION BY Parentid ORDER BY ChildID ) AS 'Occurance'

FROM SQLPuzzle

)

Update SQLPuzzle Set SortOrder = cte.Occurance from

cte

inner join SQLPuzzle t on t.ChildID = cte.rownumber

This solution works perfectly fine. After running above query I thought to see performance difference between two script and here is the difference, my first solution is much faster but the script of Rashmika is really good example of CTE.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com