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

T SQL puzzle for Update in SQL Server 2005

Suppose we have one table with ID, ParentID and SortOrder and we want to stimulate sort order based on ParentID. How do we do that without looping and cursor? There may be quite a few ways to do so but I would like to go for simple T-SQL.

–table

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

–now I want data as output something like

–ChildID ParentID SortOrder

————- ———– ———–

–1 1 1

–2 1 2

–3 2 1

–5 2 2

–4 3 1

–I will create clustered index first to make data in proper order

CREATE CLUSTERED INDEX IDX_CLUST ON SQLPuzzle(ParentID asc, ChildID ASC)
GO

–after creating clustered index

–let us logically define two variable to

–check parent id and current sorting order

DECLARE @Sort int,@Parent int

SET @Sort=1

SET @Parent=-1

–asign @sort variable with current sort order

–first time @parent variable will be -1 so it will not match up with

–ParentID column so first row will get value 1

–next time when second record will come our @parent variable will have value 1

–so it will match up with ParentID so sortorder will be will be assigned by @sort+1

UPDATE SQLPuzzle

SET @Sort = SortOrder = CASE WHEN @Parent<>ParentID then 1 ELSE @Sort +1 END,

@Parent = ParentID

GO

–check whether above batch of update has worked

select * from sqlpuzzle

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