I wrote one article for showing power of simple UPDATE statement in critical situation. You can refer that article at
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
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)
with cte as
SELECT ROW_NUMBER() OVER( ORDER BY Parentid ) AS ‘rownumber’,
ROW_NUMBER() OVER(PARTITION BY Parentid ORDER BY ChildID ) AS ‘Occurance’
Update SQLPuzzle Set SortOrder = cte.Occurance from
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