Month: August 2012

Below is the sample in how to reproduce a recursive parent-child relationship and how to find out which records are the troublesome one

CREATE TABLE RecursiveSample
(
REF INT NOT NULL,
PARENT_REF INT NOT NULL,
NAME VARCHAR(255)
)
GO
/*Inserting the parents*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(1, 0, ‘Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(2, 0, ‘Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(3, 0, ‘Parent 3’)
GO
/*Inserting the child*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(4, 1, ‘Child Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(5, 2, ‘Child Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(6, 3, ‘Child Parent 3’)
GO
/*Inserting the grand child*/
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(7, 4, ‘Grand Child Parent 1’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(8, 5, ‘Grand Child Parent 2’)
GO
INSERT INTO RecursiveSample(REF, PARENT_REF, NAME) VALUES(9, 6, ‘Grand Child Parent 3’)
GO
/*Let’s update the record to make it recursive*/
UPDATE RecursiveSample SET PARENT_REF = 7 WHERE REF = 4
GO
/*Run this query to find the Parent child structure*/
;WITH CTE
AS(
SELECT *,[PATH]=CAST(REF AS NVARCHAR(1000))FROM RecursiveSample WHERE PARENT_REF =0
UNION ALL
SELECT A.*,[PATH]=CAST(C.[PATH]+’/’+RTRIM(C.REF) AS NVARCHAR(1000))
FROM CTE C
INNER JOIN RecursiveSample A ON C.REF=A.PARENT_REF AND PATINDEX(‘/’+RTRIM(A.REF)+’/%’,’/’+C.[PATH]+’/’)=0
)
SELECT * FROM CTE
/*
REF PARENT_REF NAME PATH

How to simulate Deadlock in SQL Server, pretty simple

1. Create a table

CREATE TABLE MyDeadlockTable
(
REF INT PRIMARY KEY NOT NULL,
NAME VARCHAR(255)
)
GO

2. Populate a table with the record
INSERT INTO MyDeadlockTable(REF, NAME) VALUES(1, ‘Record 1’)
GO
INSERT INTO MyDeadlockTable(REF, NAME) VALUES(2, ‘Record 2’)
GO

3. On the first window let’s run this
BEGIN TRAN
UPDATE MyDeadlockTable set NAME=NAME WHERE REF = 1;
WAITFOR DELAY ’00:00:15′
UPDATE MyDeadlockTable set NAME=NAME WHERE REF = 2;
COMMIT

4. Open a 2nd window let’s run this
BEGIN TRAN
UPDATE MyDeadlockTable SET NAME=NAME WHERE REF = 2;
WAITFOR DELAY ’00:00:15′
UPDATE MyDeadlockTable SET NAME=NAME WHERE REF = 1;
COMMIT