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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: