PERSISTED Columns with HierarchyId Datatype - SQL Server 2008 by serverku

I think you have already read all the articles related to HierarchyId in the past And hope you understood the concept and usage of it. You also experienced with performance by using HierarchyId datatype as I have posted the performance review in earlier posts as well.

With earlier posts you got some basic methods or functions as how we get the hierarchy data levels, root node, string path. Also, some other methods like as how can we get up-line & down-line modes. Here I am going to present the same, but as Persisted column. So we do not need to write those functions in query level every time. Let us see the workaround for that.

How can ?
We need to create one table and define those columns as a function call as a PERSISTED. We will look the methods to get the hierarchical data without defining columns as Persisted columns and we will use those function calls at the query level. The scripts for the same are as follows.

-- Creating objects
IF (OBJECT_ID('TblHierarchyStructure','U') > 0)
DROP TABLE TblHierarchyStructure
GO

CREATE TABLE TblHierarchyStructure
  (
     ItemId        INT,
     ParentItemId  INT,
     ItemOrder     INT,
     ItemName      VARCHAR(100),
     HierarchyNode HIERARCHYID
  )

GO

-- Inseting records in tables for the demo
INSERT INTO TblHierarchyStructure
            (ItemId,
             ParentItemId,
             ItemOrder,
             ItemName,
             HierarchyNode)
SELECT 1,
       NULL,
       1,
       'RootItem',
       HierarchyId::Parse('/')
UNION ALL
SELECT 2,
       1,
       1,
       'FirstItem',
       HierarchyId::Parse('/1/')
UNION ALL
SELECT 3,
       1,
       2,
       'SecondItem',
       HierarchyId::Parse('/2/')
UNION ALL
SELECT 4,
       1,
       3,
       'ThirdItem',
       HierarchyId::Parse('/3/')
UNION ALL
SELECT 5,
       2,
       1,
       'FourthItem',
       HierarchyId::Parse('/1/1/')
UNION ALL
SELECT 6,
       4,
       1,
       'FifthItem',
       HierarchyId::Parse('/3/1/')
UNION ALL
SELECT 7,
       5,
       1,
       'SixthItem',
       HierarchyId::Parse('/1/1/1/')
UNION ALL
SELECT 8,
       5,
       2,
       'SeventhItem',
       HierarchyId::Parse('/1/1/2/')
UNION ALL
SELECT 9,
       5,
       3,
       'NinthItem',
       HierarchyId::Parse('/1/1/3/')
UNION ALL
SELECT 10,
       8,
       1,
       'TenthItem',
       HierarchyId::Parse('/1/1/2/1/')

GO

-- Usinf HierarchyId functions at query level and see output.
SELECT *,
       HierarchyNode.ToString()                AS ItemNodeString,
       HierarchyNode.GetLevel()                AS ItemNodeLevel,
       HierarchyNode.GetAncestor(1)            AS ParentNode,
       HierarchyNode.GetAncestor(1).ToString() AS ParentNodeString
FROM   TblHierarchyStructure
GO

Now we will look the methods to get the hierarchical data with defining columns as Persisted columns and we will use those functions call at the column level. The scripts for the same are as follows.

-- Creating objects
IF (OBJECT_ID('TblHierarchyStructure','U') > 0)
DROP TABLE TblHierarchyStructure
GO

CREATE TABLE TblHierarchyStructure
  (
     ItemId        INT,
     ParentItemId  INT,
     ItemOrder     INT,
     ItemName      VARCHAR(100),
     HierarchyNode HIERARCHYID NOT NULL PRIMARY KEY,
     ItemNodeString AS HierarchyNode.ToString() PERSISTED,
     ItemNodeLevel AS HierarchyNode.GetLevel() PERSISTED,
     ParentNode AS HierarchyNode.GetAncestor(1) PERSISTED,
     ParentNodeString AS HierarchyNode.GetAncestor(1).ToString()
  )

GO

-- Inserting sample records here 
INSERT INTO TblHierarchyStructure
            (ItemId,
             ParentItemId,
             ItemOrder,
             ItemName,
             HierarchyNode)
SELECT 1,
       NULL,
       1,
       'RootItem',
       HierarchyId::Parse('/')
UNION ALL
SELECT 2,
       1,
       1,
       'FirstItem',
       HierarchyId::Parse('/1/')
UNION ALL
SELECT 3,
       1,
       2,
       'SecondItem',
       HierarchyId::Parse('/2/')
UNION ALL
SELECT 4,
       1,
       3,
       'ThirdItem',
       HierarchyId::Parse('/3/')
UNION ALL
SELECT 5,
       2,
       1,
       'FourthItem',
       HierarchyId::Parse('/1/1/')
UNION ALL
SELECT 6,
       4,
       1,
       'FifthItem',
       HierarchyId::Parse('/3/1/')
UNION ALL
SELECT 7,
       5,
       1,
       'SixthItem',
       HierarchyId::Parse('/1/1/1/')
UNION ALL
SELECT 8,
       5,
       2,
       'SeventhItem',
       HierarchyId::Parse('/1/1/2/')
UNION ALL
SELECT 9,
       5,
       3,
       'NinthItem',
       HierarchyId::Parse('/1/1/3/')
UNION ALL
SELECT 10,
       8,
       1,
       'TenthItem',
       HierarchyId::Parse('/1/1/2/1/')

GO

-- We have not using HierarchyId functions at query level
-- and using them at columns level as Persisted
SELECT 
*
FROM   TblHierarchyStructure 

GO

I hope you liked this post about Persisted columns with HierarchyID new datatype. Share your experience if you know this type of the usage.

Move Node to other place with HierarchyId Data Type - SQL Server 2008 by serverku

After writing some of the posts related HierachyId data type, Finally moving in last topics of HierachyId functions, we will see here the movement of the hierarchy nodes. Let's start from the script to create data for the demo.

-- Create database and table
CREATE DATABASE HierarchyDB

GO

USE HierarchyDB

GO

IF ( Object_id('HierarchyTab') > 0 )
DROP TABLE HierarchyTab

GO

CREATE TABLE HierarchyTab
(
NodeId     INT IDENTITY(1, 1)
,NodeDepth VARCHAR(100) NOT NULL
,NodePath  HIERARCHYID NOT NULL
,NodeDesc  VARCHAR(100)
)

GO 

-- Creating constraint on hierarchy data type.
ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)

GO 
-- Inserting data in above creatd table.
INSERT INTO HierarchyTab(NodeDepth,NodePath,NodeDesc)
VALUES 
('1',HIERARCHYID::Parse('/'),'Node-1'),
('1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
('1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
('1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
('1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
('1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
('1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
('1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
('1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9'),
('1.2.2.1.2',HIERARCHYID::Parse('/2/2/1/2/'),'Node-10'),
('1.3',HIERARCHYID::Parse('/3/'),'Node-11'),
('1.3.1',HIERARCHYID::Parse('/3/1/'),'Node-12'),
('1.3.2',HIERARCHYID::Parse('/3/2/'),'Node-13'),
('1.4',HIERARCHYID::Parse('/4/'),'Node-14')

GO

The logical image of hierarchy data are as following,

(Click on image to enlarge)
Now we will move hierarchy nodes and it's down-line from one place to another place.

But how to move?


GetReparentedValue(OldNode, NewNode) : It will move all nodes, including itself and down-line as well to another place. Let's see  what should be new place of the hierarchy id "1" and it's down-line nodes after moving to another place.

-- GetReparentedValue()
SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeCurrentPath,
NodePath.GetReparentedValue(HIERARCHYID::Parse('/1/'), HIERARCHYID::Parse('/4/1/')).ToString()
 AS NewNodePath,
-- Above line will give new node path of id 1 and it's downline where it will be placed.
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab
WHERE NodePath.IsDescendantOf(HIERARCHYID::Parse('/1/')) = 1

GO

(Click on image to enlarge)


Now run the below query and move the id 1 and down-line of hierarchy id and then see a logical image from data

UPDATE HierarchyTab
SET NodePath  = NodePath.GetReparentedValue(HIERARCHYID::Parse('/1/'), HIERARCHYID::Parse('/4/1/')),
NodeDepth = '4.' + NodeDepth
WHERE NodePath.IsDescendantOf(HIERARCHYID::Parse('/1/')) = 1

GO

(Click on image to enlarge)

Hope you understood well and get used this feature for the hierarchical data. Please comments how you are using HierarchyId Data Type and their functions.

Is Child Node? - With HierarchyId Data Type in SQL Server 2008 by serverku

Various method I have introduced in my earlier posts, like How to get levels of hierarchy nodes, get up-line and down-line of nodes, get string paths of nodes and get next available nodes.

I hope you have read all of them and you liked too. In this post I am presenting how can we know the node is child of particular node or not? Before going ahead to run the script and see the output of hierarchy structure.

-- Create database and table
CREATE DATABASE HierarchyDB

GO

USE HierarchyDB

GO

IF ( Object_id('HierarchyTab') > 0 )
DROP TABLE HierarchyTab

GO

CREATE TABLE HierarchyTab
(
NodeId     INT IDENTITY(1, 1)
,NodeDepth VARCHAR(100) NOT NULL
,NodePath  HIERARCHYID NOT NULL
,NodeDesc  VARCHAR(100)
)

GO 

-- Creating constraint on hierarchy data type.
ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)

GO 
-- Inserting data in above creatd table.
INSERT INTO HierarchyTab(NodeDepth,NodePath,NodeDesc)
VALUES 
('1',HIERARCHYID::Parse('/'),'Node-1'),
('1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
('1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
('1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
('1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
('1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
('1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
('1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
('1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9'),
('1.2.2.1.2',HIERARCHYID::Parse('/2/2/1/2/'),'Node-10'),
('1.3',HIERARCHYID::Parse('/3/'),'Node-11'),
('1.3.1',HIERARCHYID::Parse('/3/1/'),'Node-12'),
('1.3.2',HIERARCHYID::Parse('/3/2/'),'Node-13'),
('1.4',HIERARCHYID::Parse('/4/'),'Node-14')

GO

Here is the logical image of the above data are as follows,

(Click on image to enlarge)

So our topic here, Is the node Child ?

IsDescendantOf() : This functions will return 1 if the node is child of given node, return 0 if the node is not child.

Let's run the script with example as who are child of hierarchy node "1".

SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodePath.GetAncestor(1).ToString() AS ParentNode,
NodePath.IsDescendantOf(HIERARCHYID::Parse('/1/')) IsParent,
-- Above line will return 1 or 0
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab

GO

You can see the above image and check parent node for appropriate child nodes. Hope you like this. Stay tuned for more.

Next Available Node with HierarchyId Data Type - SQL Server 2008 by serverku

I have posted for the some of the functions with examples and demonstrate them in earlier posts. I have written articles of HierarchyId data type overview, some basic functions, even demonstrate for the up-line & down-line of hierarchy nodes which you can read from below links.

Here I will present how can we get the next available node to be planed with HierarchyID data type function. Let's first create hierarchy data structure with the script.

-- Create a database and table
CREATE DATABASE HierarchyDB

GO

USE HierarchyDB

GO

IF ( Object_id('HierarchyTab') > 0 )
DROP TABLE HierarchyTab

GO

CREATE TABLE HierarchyTab
(
NodeId     INT IDENTITY(1, 1)
,NodeDepth VARCHAR(100) NOT NULL
,NodePath  HIERARCHYID NOT NULL
,NodeDesc  VARCHAR(100)
)

GO 

-- Creating constraint on hierarchy data type.
ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)

GO 
-- Inserting data in above creatd table.
INSERT INTO HierarchyTab(NodeDepth,NodePath,NodeDesc)
VALUES 
('1',HIERARCHYID::Parse('/'),'Node-1'),
('1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
('1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
('1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
('1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
('1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
('1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
('1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
('1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9'),
('1.2.2.1.2',HIERARCHYID::Parse('/2/2/1/2/'),'Node-10'),
('1.3',HIERARCHYID::Parse('/3/'),'Node-11'),
('1.3.1',HIERARCHYID::Parse('/3/1/'),'Node-12'),
('1.3.2',HIERARCHYID::Parse('/3/2/'),'Node-13'),
('1.4',HIERARCHYID::Parse('/4/'),'Node-14')

GO

Hierarchy data structure as imaged as below.

(Click on image to enlarge)

How to we find it?

GetDescendant() : This function will give next available node where we can place new node.

1. GetDescendant(NULL,NULL) : will return default next left node.
2. GetDescendant(LeftNode,NULL) : will return right node next to left node.
3. GetDescendant(NULL,RighNode) : will return left node previous to left node.

Run the following script and see the output.

-- GetDescendant()
SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodePath.GetDescendant(NULL,NULL).ToString() AS NextDefaultNode,
-- Above line will get default node.
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab

GO

Looking for one hierarchy node id 13,

SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodePath.GetDescendant(NULL,NULL).ToString() AS NextDefaultNode,
NodePath.GetDescendant(HIERARCHYID::Parse('/3/2/1/'),NULL).ToString() AS NextRightNode,
NodePath.GetDescendant(NULL,HIERARCHYID::Parse('/3/2/2/')).ToString() AS NextLeftNode,
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab
WHERE NodeId = 13

GO

(Click on image to enlarge)

This is what I want to share here and hope you like it.

Up-Line and Down-Line with HierarchyId Data type - SQL Server 2008 by serverku

I have already given HierarchyId datatype overview in my earlier post. Also explained some of HierarchyId functions in post with details as well. Please go through to the overview and some function details which I explained in my previous posts. In this article I am going to demonstrate following items.

1. How to get the up-line nodes?
2. How to get down-line nodes?

Hierarchies functions will give you the answer to all above questions. Let's demonstrate the answers with examples. Before going ahead, I would like to create a hierarchy data structure by following a script.

-- Create database and table
CREATE DATABASE HierarchyDB

GO

USE HierarchyDB

GO

IF ( Object_id('HierarchyTab') > 0 )
DROP TABLE HierarchyTab

GO

CREATE TABLE HierarchyTab
(
NodeId     INT IDENTITY(1, 1)
,NodeDepth VARCHAR(100) NOT NULL
,NodePath  HIERARCHYID NOT NULL
,NodeDesc  VARCHAR(100)
)

GO 

-- Creating constraint on hierarchy data type.
ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)

GO 
-- Inserting data in above creatd table.
INSERT INTO HierarchyTab(NodeDepth,NodePath,NodeDesc)
VALUES 
('1',HIERARCHYID::Parse('/'),'Node-1'),
('1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
('1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
('1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
('1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
('1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
('1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
('1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
('1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9'),
('1.2.2.1.2',HIERARCHYID::Parse('/2/2/1/2/'),'Node-10'),
('1.3',HIERARCHYID::Parse('/3/'),'Node-11'),
('1.3.1',HIERARCHYID::Parse('/3/1/'),'Node-12'),
('1.3.2',HIERARCHYID::Parse('/3/2/'),'Node-13'),
('1.4',HIERARCHYID::Parse('/4/'),'Node-14')

GO

The structure of HierarchyId data looks as follows,

(Clink on image to enlarge)

Now we have time to demonstrate the answers one by one here.

1. How to get the up-line nodes?

GetAncestor(n) : This function will help us to get up-line of the particular hierarchy node. Looking at the script, which will find first up-line node of all hierarchy nodes.

-- GetAncestor(n)
SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodePath.GetAncestor(1).ToString() AS ParentNode,
-- Here 1 is used to get 1st u-line node
-- You can use n to get nth up-line node
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab

GO

Please change value to 2 as GetAncestor(n) in place of 1 in script and get the output, You will get more idea.

2. How to get down-line nodes?

IsDescendantOf() : This function will give us the down-line noes of a particular node. Let us run the script and clear our answer. We are viewing a downline node of hierarchy node 1 by script.

-- IsDescendantOf()
SELECT  
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab
WHERE NodePath.IsDescendantOf(HIERARCHYID::Parse('/1/')) = 1
-- Here we have filtered condition to get down-line of node 1.
GO

I hope you are very clear now after demonstrate to how get up-line and down-line of hierarchy node.

Get Downline and Upline of hierarchical data and Performance review - CTE vs HierarchyId in SQL Server 2008 by serverku

I have already posted for the HierarchyId and CTE (Common Table Expression), also given the comparison review of them for the level and hierarchical order data. I am not saying that HierarchyId is better than CTE or CTE is better then HierarchyId. But it all depends on. You need to practically use them and review the performance of hierarchies and CTE. I am going to show one more demo to find the members with downline and upline.

-- creating database and objects
CREATE DATABASE HierarchyDB
GO

USE HierarchyDB
GO

IF ( Object_id('HierarchyTab') > 0 )
  DROP TABLE HierarchyTab
GO

CREATE TABLE HierarchyTab
  (
     NodeId     INT NOT NULL
     ,NodeParent int
     ,NodeDepth VARCHAR(100) NOT NULL
     ,NodePath  HIERARCHYID NULL
     ,NodeLevel as (NodePath.[GetLevel]())
     ,NodeStringPath as  (NodePath.ToString())
     ,NodeDesc  VARCHAR(100)
  )
GO 

ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)
GO 

INSERT INTO HierarchyTab(NodeId,NodeParent,NodeDepth,NodePath,NodeDesc)
VALUES 
(1,NULL,'1',HIERARCHYID::Parse('/'),'Node-1'),
(2,1,'1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
(3,2,'1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
(4,2,'1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
(5,1,'1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
(6,5,'1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
(7,5,'1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
(8,7,'1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
(9,8,'1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9')
GO

SELECT 
* 
FROM HierarchyTab
GO

1. Get down line data using CTE and HierarchyId and compare the execution plan.

-- Using CTE (Not Using NodePath and NodeLevel)
;WITH cteLevels
AS
(
SELECT
    NodeId as Node
    ,NodeParent as Perent 
    ,NodeDepth as Depth
    ,0        AS [Level]
    ,NodeDesc as [Desc]
FROM   HierarchyTab 
WHERE NodeId = 1

UNION ALL

SELECT
     NodeId 
    ,NodeParent 
    ,NodeDepth
    ,[Level] + 1 AS [Level]
    ,NodeDesc 
FROM   HierarchyTab
       INNER JOIN cteLevels
         ON ( NodeParent = Node ) 
WHERE [Level] + 1 < =3
)

SELECT 
* 
FROM cteLevels 
WHERE [Level] = 3
GO


-- With HierarchyId
DECLARE @DownlineNode HierarchyId = 
(SELECT NodePath FROM HierarchyTab WHERE NodeId = 1)

SELECT 
 NodePath.ToString() AS NodeStringPath,
 NodeId,
 NodeParent,
 NodeDepth,
 NodeLevel,
 NodeDesc
FROM HierarchyTab
WHERE NodePath.IsDescendantOf(@DownlineNode) = 1
AND NodeLevel = @DownlineNode.GetLevel() + 3
GO

When you run above script you have data and execution plans as follow,

2. Get up line data using CTE and HierarchyId and compare the execution plan.

-- Using CTE (Not Using NodePath and NodeLevel)
;WITH cteLevels
AS
(
SELECT
    NodeId as Node
    ,NodeStringPath as StringPath
    ,NodeParent as Parent 
    ,NodeDepth as Depth
    ,0        AS [Level]
    ,NodeDesc as [Desc]
FROM   HierarchyTab 
WHERE NodeId = 9


UNION ALL

SELECT
    NodeId 
    ,NodeStringPath as StringPath
    ,NodeParent 
    ,NodeDepth
    ,[Level] + 1 AS [Level]
    ,NodeDesc 
FROM   HierarchyTab
       INNER JOIN cteLevels
         ON ( NodeId = Parent ) 
WHERE [Level] + 1 < =3
)

SELECT 
* 
FROM cteLevels 
WHERE [Level] = 3
GO


-- With HierarchyId
SELECT 
 NodePath.ToString() AS NodeStringPath,
 NodeId,
 NodePath.GetAncestor(3).ToString() as ParentOn4thPos,
 NodeParent,
 NodeDepth,
 NodeLevel,
 NodeDesc
FROM HierarchyTab
WHERE NodeId = 9
GO

The result set and execution plan when you run above script

Hope you like this and share your experience as comments.

Breath First and Depth First Strategy with Hierarchical Data and Performance review - HierarchyId Data Type vs CTE in SQL Server 2008 by serverku

It is very important to check the performance if we use the new features of alternative methods of SQL Server. I have written earlier posts of the HierarchyId and CTE (Common Table expression). Now in this post you can view the usage of HierarchyId and CTE, comparisons between them. Go ahead with the object creation.

1. Introduction to HierarchyId data type - Amazing feature of SQL Server 2008
2. HierarchyId DataType in SQL Server 2008 - Get Level and String Path

-- Creating databse and table
CREATE DATABASE HierarchyDB
GO

USE HierarchyDB
GO

IF ( Object_id('HierarchyTab') > 0 )
DROP TABLE HierarchyTab
GO

CREATE TABLE HierarchyTab
(
  NodeId     INT NOT NULL
 ,NodeParent int
 ,NodeDepth VARCHAR(100) NOT NULL
 ,NodePath  HIERARCHYID NULL
 ,NodeLevel as (NodePath.[GetLevel]()) 
 ,NodeDesc  VARCHAR(100)
)
GO 

ALTER TABLE HierarchyTab 
ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)
GO 

Inserting demo records in table created above.

INSERT INTO HierarchyTab(NodeId,NodeParent,NodeDepth,NodePath,NodeDesc)
VALUES 
(1,NULL,'1',HIERARCHYID::Parse('/'),'Node-1'),
(2,1,'1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
(3,2,'1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
(4,2,'1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
(5,1,'1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
(6,5,'1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
(7,5,'1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
(8,7,'1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
(9,8,'1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9')
GO

Now we will check the usage  and execution plan of the HierarchyId and CTE.

1. First, we will run the scripts for the to get data level by level order. Let’s start with CTE and the query without using a hierarchy node,

-- Using CTE (Not Using NodePath and NodeLevel)
;WITH cteLevels
AS
(
SELECT
  NodeId as Node
 ,NodeParent as Perent 
 ,NodeDepth as Depth
 ,0        AS [Level]
 ,NodeDesc as [Desc]
FROM   HierarchyTab 
WHERE NodeId = 1


UNION ALL

SELECT
  NodeId 
 ,NodeParent 
 ,NodeDepth
 ,[Level] + 1 AS [Level]
 ,NodeDesc 
FROM   HierarchyTab
INNER JOIN cteLevels
ON ( NodeParent = Node ) 

)

select 
* 
from cteLevels 
Order by [Level]
GO

Now running the script to get hierarchy data level by level order using HierarchyId,

-- With HierarchyId
SELECT 
  NodePath.ToString() AS NodeStringPath,
  NodeId,
  NodeParent,
  NodeDepth,
  NodeLevel,
  NodeDesc
FROM HierarchyTab
Order by NodeLevel

GO

Before going ahead with next script, we are checking the execution plan of both above script,

2. Getting records of hierarchy in hierarchical order with CTE and HierarchyId.

-- Using CTE (Not Using NodePath and NodeLevel)
;WITH cteLevels
AS
(
SELECT
  NodeId as Node
 ,NodeParent as Perent 
 ,NodeDepth as Depth
 ,0        AS [Level]
 ,NodeDesc as [Desc]
 ,CAST(NodeId AS VARCHAR(MAX))  AS [Order]
FROM   HierarchyTab 
WHERE NodeId = 1


UNION ALL

SELECT
  NodeId 
 ,NodeParent 
 ,NodeDepth
 ,[Level] + 1 AS [Level]
 ,NodeDesc 
 ,[Order] + '.' + CAST(NodeId AS VARCHAR(MAX)) AS [Order]
FROM   HierarchyTab
INNER JOIN cteLevels
ON ( NodeParent = Node ) 

)

SELECT 
* 
FROM cteLevels 
ORDER BY [Order]

GO

View the result of hierarchical order data with running below query with HierarhyId,

-- With HierarchyId
SELECT 
 NodePath.ToString() AS NodeStringPath,
 NodeId,
 NodeParent,
 NodeDepth,
 NodeLevel,
 NodeDesc
FROM HierarchyTab
Order by NodePath

GO

Finally, we go through the performance and the execution plan review of both above scripts,

This is just the details of execution and performance review.

Example of SET XACT_ABORT ON in SQL Server by serverku

We have seen one error The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION and seen workaround too. There is related to transaction mismatch and now I am writing further same with different error and an issue.

In that post, if I remove one column of used tables in those SPs. What will happen? It will raise an error and keep the transaction open. Let us check.

-- Creating table which will be used in SPs.
CREATE TABLE tbl_Tran
  (
     TranId    INT NOT NULL PRIMARY KEY
     ,TranName VARCHAR(10)
  )
 
GO
 
-- Altering first stored procedure here
CREATE PROCEDURE Firttranproc
AS
  BEGIN
	SET NOCOUNT ON
    
   -- Here we have specified Tran1 as transaction name
  
   BEGIN TRY
          BEGIN TRANSACTION
 
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
    
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran1'
 
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Altering second stored procedure here
CREATE PROCEDURE Secondtranproc
AS
  BEGIN
      SET NOCOUNT ON
 
      BEGIN TRY
          BEGIN TRANSACTION
 
    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'
 
          -- Calling first stored procedure here
          EXEC Firttranproc
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran2'
 
     
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO

 -- Droping one column to raise an complite type error 
ALTER TABLE tbl_Tran
DROP COLUMN TranName
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO
Msg 207, Level 16, State 1, Procedure Secondtranproc, Line 13
Invalid column name 'TranName'.
Msg 266, Level 16, State 2, Procedure Secondtranproc, Line 13
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

This will raise a compiled type error and keep transaction open which you can get details using the following script,

USE master
GO

SELECT 
       est.session_id as [Session ID],
       est.transaction_id as [Transaction ID],
       tas.name as [Transaction Name],
       tds.database_id as [Database ID]
FROM sys.dm_tran_active_transactions tas 
INNER JOIN sys.dm_tran_database_transactions tds 
       ON (tas.transaction_id = tds.transaction_id )
INNER JOIN sys.dm_tran_session_transactions est 
       ON (est.transaction_id=tas.transaction_id)
       WHERE est.is_user_transaction = 1 -- user
       AND tas.transaction_state = 2 -- active
       AND tas.transaction_begin_time IS NOT NULL
GO    
/*
Output :

Session ID  Transaction ID   Transaction Name  Database ID
----------- ---------------- ----------------- -----------
54          176426           user_transaction  19

*/

You can see the open transaction details above. For a fox for such issue we should use SET XACT_ABORT ON in the beginning of the stored procedures. When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. So let's change stored procedures,

-- Altering first stored procedure here
ALTER PROCEDURE Firttranproc
AS
  BEGIN
	SET NOCOUNT ON
        SET XACT_ABORT ON
   -- Here we have specified Tran1 as transaction name
  
   BEGIN TRY
          BEGIN TRANSACTION
 
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
    
          SELECT
            1
            ,'Tran-1'
          UNION ALL
          SELECT
            1
            ,'Tran-1'
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran1'
 
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Altering second stored procedure here
ALTER PROCEDURE Secondtranproc
AS
  BEGIN
          SET NOCOUNT ON
	  SET XACT_ABORT ON
 
      BEGIN TRY
          BEGIN TRANSACTION
 
    -- Inserting records
          INSERT INTO tbl_Tran
                      (TranId
                       ,TranName)
          SELECT
            2
            ,'Tran-2'
 
          -- Calling first stored procedure here
          EXEC Firttranproc
 
          COMMIT TRANSACTION
      END TRY
 
      BEGIN CATCH
          PRINT 'Rollback Tran2'
 
     
   -- This statement first check open transaction for their session 
   -- If found then will rollback it.
         IF @@TRANCOUNT > 0
          ROLLBACK TRANSACTION
      END CATCH
  END
 
GO
 
-- Executing second stored procedure which will also call SP1 
EXEC Secondtranproc
GO

It will raise a run-time error, but does not keep the transaction open. Please execute stored procedures and check for open transaction using query provided above. This is just what I want to share with you. I would like to put your comments.

MERGE statement - a new tsql feature of SQL Server 2008 by serverku

It will a lengthy and complex coding if we need to perform insert, update and delete statement individually. Instead of writing separate statements for the insert, update and delete operation, we have one more option which can be very helpful in this matter.

Yes, that feature is "Merge" statement and supported in SQL server 2008 or later version. Merge is allow multiple DML operation to perform. That must be ended by semicolon. Let's see the example using Merge statement.

-- Creating Database

CREATE DATABASE MergeDatabase

GO

USE MergeDatabase

GO

-- Creating tables used for merged operation

IF ( Object_id('UsingTable') > 0 )
  DROP TABLE UsingTable

GO

CREATE TABLE UsingTable
  (
     RefId INT IDENTITY(1, 1),
     name  VARCHAR(100)
  )

GO

IF ( Object_id('TargetTable') > 0 )
  DROP TABLE TargetTable

GO

CREATE TABLE TargetTable
  (
     ChildId INT,
     val     INT
  )

GO

-- Inserting records in both tables

INSERT INTO UsingTable(name)
VALUES      ('Target-1'),
            ('Target-2'),
            ('Target-3'),
            ('Target-4'),
            ('Target-5')

GO

INSERT INTO TargetTable(ChildId,val)
VALUES      (1,1),
            (2,2),
            (3,3),
            (6,6) 

GO

Let us see how Merge statement works.

1. Merge statement with WHEN MATCHED clause and updating records,>

MERGE TargetTable 
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED THEN
UPDATE set val = val + 5 ;

2. Merge statement with WHEN MATCHED clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN MATCHED AND ChildId = 3 THEN
DELETE ;

3. Merge statement with WHEN NOT MATCHED BY TARGET clause and inserting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)
;

4. Merge statement with WHEN NOT MATCHED BY SOURCE clause and deleting records,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

5. All together at once,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Let's see the result set of TargetTable before and after the Merge statement used.

Before Merge statement ran,

After Merge statement ran,

6. Using OUTPUT with Merge statement,

MERGE TargetTable
USING UsingTable
ON (RefId = ChildId)


WHEN MATCHED AND ChildId = 3 THEN
DELETE 

WHEN MATCHED THEN
UPDATE set val = val + 5

WHEN NOT MATCHED BY TARGET THEN
INSERT(childId,val)
VALUES(4,4)

WHEN NOT MATCHED BY SOURCE
THEN DELETE

OUTPUT

$action,
INSERTED.ChildId,
INSERTED.Val,
DELETED.childId,
DELETED.val
;

Hope you have already started to use Merge statement.

Find active open transactions from a SQL Server Instance by serverku

I would like to share the script to find open active transactions and sessions at database level, here you can find the same which capture for active transactions initiated by users only. This will result for all the databases, but you can make a database filter as well. This result is most relatively as DBCC OPENTRAN check in a particular database.

USE master
GO

SELECT 
       est.session_id as [Session ID],
       est.transaction_id as [Transaction ID],
       tas.name as [Transaction Name],
       tds.database_id as [Database ID]
FROM sys.dm_tran_active_transactions tas 
INNER JOIN sys.dm_tran_database_transactions tds 
       ON (tas.transaction_id = tds.transaction_id )
INNER JOIN sys.dm_tran_session_transactions est 
       ON (est.transaction_id=tas.transaction_id)
       WHERE est.is_user_transaction = 1 -- user
       AND tas.transaction_state = 2 -- active
       AND tas.transaction_begin_time IS NOT NULL
GO    

You can see the conditions made in this script and the values for the same are as following,

is_user_transaction

• 1 = The transaction was initiated by a user request.
• 0 = System transaction

transaction_state

• 0 = The transaction has not been completely initialized yet.
• 1 = The transaction has been initialized but has not started.
• 2 = The transaction is active.
• 3 = The transaction has ended. This is used for read-only transactions.
• 4 = The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.
• 5 = The transaction is in a prepared state and waiting resolution.
• 6 = The transaction has been committed.
• 7 = The transaction is being rolled back.
• 8 = The transaction has been rolled back.is_user_transaction

Download multiple files through the FTP in SQL Server - SSIS by serverku

We have completed the reading of earlier methods to receive single file and receive file dynamically through the FTP in SSIS. Hope you like those posts. Previously we have downloaded a file which has specific extension with format of current date.

The same thing and criteria will be repeated again, but one thing is different and it's not downloading a single file and not the specific current date format. It's a download of multiple files which are having an extension with current year like you can see in below shot.

1. Here you can see the files from FTP, among them we will download files meet with the criteria. You can see target files.

2. Drag and drop FTP Task as shown on screen.

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. Create one variable for the Remote Path for the FTP and assign the value of the FTP directory from where the file will be received.

7. Here we haven't given static value of Remote Path and file name, but the qualified name will be created dynamically. So apply expression for the Remote Path option like following.

8. Finally, execute the created ssis package and you will see the files received in the local folder. You can see the target files received.

What is the next? Stay tuned for the next posts.

Download file having specific extension in SQL Server - SSIS by serverku

Earlier we have seen some of the methods to download single file from FTP using tsql scripts and SSIS. So it's the same with single file, but a little bit different as we will download the file which have specific extension. Like we will have to receive the files which have extension of specific format of current date.

1. Here you can see the files from FTP, among them we will download one file. You can see target file.

2. Drag and drop FTP Task as shown in screen.

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. Create one variable for the Remote Path for the FTP and assign the value of the FTP directory from where the file will be received.

7. Here we haven't given static value of Remote Path and file name, but the qualified name will be created dynamically. So apply expression for the Remote Path option like following.

8. Finally, execute the created ssis package and you will see the file received in the local folder. You can see the target file received in local directory.

Just it. Please share your ideas and comments here. Hope you enjoyed this post. Stay tuned for more posts.

Adding/Modifying files and filegroup in Database - SQL Server by serverku

You all know about the database file structure as whenever we created databases without specifying a file location and file groups, they will be created in default path and default filegroup. Here I have tried to create a new database without defining any options, look the file structure on the default location and the default group.

CREATE DATABASE FILEGROUPDB
GO

SP_HELPDB FileGroupDB
GO

(Click on image to enlarge)

We can change the default filegroup with following script.

ALTER DATABASE AdventureWorks2008R2 
MODIFY FILEGROUP [PRIMARY] DEFAULT; 
GO

As a better perspective for the database Architecture, some frequently updated or used tables should be on different filegroup and the database secondary and log files also be one different filegroup. TempDB data and log files should have alone disk and filegroup as well.

Let us we go ahead with some of activities as how can we can create a new filegroup, how can we add the new secondary files to that file group. We will also look for the modifying the database file and changing the location. First capture the above image of the database file structure before doing any activities with database files. Please go to details explained by the comments in the script below and then we will compare the database file structure with original one.

USE MASTER 
GO

-- Adding a new filegroup to database 
ALTER DATABASE FileGroupDB 
ADD FILEGROUP NewFileGroup; 
GO

--Adding a new seconday and log files to a database to above created filegroup 
ALTER DATABASE FileGroupDB 
ADD FILE 
( 
    -- New secondary files added here 
    NAME = FileGroupDB_Data_1, 
    FILENAME = 'C:\FileGroupDB_Data_1.ndf', 
    SIZE = 15 MB, 
    MAXSIZE = 100 MB, 
    FILEGROWTH = 5 MB 
), 
( 
    -- New log file added here 
    NAME = FileGroupDB_Log_1, 
    FILENAME = 'C:\FileGroupDB_Log_1.ldf', 
    SIZE = 5 MB, 
    MAXSIZE = 100 MB, 
    FILEGROWTH = 5 MB 
) 
TO FILEGROUP NewFileGroup; -- Defining filegroup name here 
GO

-- Modifying size of the file 
ALTER DATABASE FileGroupDB 
MODIFY FILE  
( 
-- Changing log file size here 
NAME = FileGroupDB_Log, 
SIZE = 20 MB 
)

-- Moving log file to another location 
ALTER DATABASE FileGroupDB 
MODIFY FILE 
( 
NAME = FILEGROUPDB_log, -- Moving first log file to new location here 
FILENAME = 'C:\FileGroupDB_Log.ldf' 
) 
GO

(Click on image to enlarge)

After running the last script you will notified by following message,
"The file "FILEGROUPDB_log" has been modified in the system catalogue. The new path will be used the next time the database is started.
It will use the new file location when SQL Service will be restarted. We can have another option is taking database offline and use the same scripts written above and again database needs to back online which does not need to restart the database service.

Could not find the Distributor or the distribution database for the local server - Error while posts a tracer token in Replication by serverku

A few days back, I spoke about the manual failover of mirroring and also explained one issue and workaround too. Continuing with the same failover, I want to express one more issue here. This issue is not very critical but it somehow to create an issue while collecting some information for report or any other purpose. Let me elaborate everything here, why and how this error raised.
You all know about system stored procedure sys.sp_posttracertoken which posts a tracer token into the transaction log at the Publisher and begins the process of tracking latency statistics, which we can schedule on some frequency to post tracer tokens. Tracer tokens can be inserted with Replication monitor also,

You can find the tsql code for same below which must be run against the publisher database,

USE publisherdb 
GO

DECLARE @out_tracer_token_id INT 

EXEC sys.Sp_posttracertoken 
  @publication = N'<Publication Name>',  -- Put Publication name here
  @tracer_token_id=@out_tracer_token_id out 

SELECT @out_tracer_token_id 

Error
But after a failover when I tried the same tsql code in the switched publisher database I received an error,

"Could not find the Distributor or the distribution database for the local server.
The Distributor may not be installed, or the local server may not be configured as a Publisher at the Distributor."

Solution
This script was running fine in the original publisher database before failover. During the investigation as per error message I found sp_helpdistributor was returning NULL values in publisher database. sp_helpdistpublisher also not showing publisher in Distributor server or a server where distribution database belongs to. That means we have to do two things,

1. Configure distributor at publisher.
2. Configure publisher at distributor.

So moving ahead towards the solution and apply below solution,
1. sp_adddistributor which creates an entry in linked server and executed at a publisher in the master database to configure remote distributor,

Use master
GO

EXEC sp_adddistributor  
           @distributor=  '<Distributor>' , -- Put your distributor server name here
           @password= 'testpwd' -- password of distributor_admin 

2. sp_adddistpublisher configures a publisher in distributor server, which executed at the distributor side in the master database,

USE master
GO

EXEC sp_adddistpublisher 
  @publisher= '<Publisher>'  -- Put publisher servername here
 ,@distribution_db= 'distribution'  -- Distribution database name
 ,@security_mode= 1 
 ,@login= 'sa' 
 ,@password= 'testpwd' 

After this workaround I was able to ran this script successfully at the publisher and scheduled for  every 5 minutes, so I can use it for replication latency alert and reports too.  Are you using sys.Sp_posttracertoken system stored procedure? Share your feedback here.

Purge old data from dbo.sysmail_mailitems system table in msdb database - SQL Server by serverku

As a DBA, disk space is an important factor for daily monitoring and I encountered one issue of same for disk space. I found it based on disk space statistics report from all servers. I investigated disk space usage of all databases and found msdb system database went beyond 38 GB around. You can find the script at database size information. After database, it is needed to know where actually it was eating, which tables? I found one system table ‘dbo. sysmail_mailitems’ consumed high disk space and created space issue. Please read the post to get table size statistics for a particular database.

To make a disk space I removed some old data from dbo. sysmail_mailitems system table using a script. We can also delete data directly from this system table, but I found system stored procedure to purge old data from this table from here. Let me share with you,

1. sysmail_delete_mailitems_sp :  Permanently deletes e-mail messages from the Database Mail internal tables.
2. sysmail_delete_log_sp : Deletes events from the Database Mail log. Deletes all events in the log or those events meeting a date or type criteria.

And the script using these system procedures is following,

USE msdb; 
GO

DECLARE @DeleteBeforeDate DATETIME 

-- Purge data older than 30 days
SELECT @DeleteBeforeDate = Dateadd(d, -30, Getdate()) 

EXEC sysmail_delete_mailitems_sp 
  @sent_before = @DeleteBeforeDate 

EXEC sysmail_delete_log_sp 
  @logged_before = @DeleteBeforeDate 

To purge data manually, we can make it atomize with scheduled job. I do not know about setting which removed data automatically from this system table. Do you know about the same? This issue I experienced first time and I want to know about your experience if you can share here. What will be your comment to this post?

Merge statement and identity insert - SQL Server by serverku

Today I asked by my friend for merge statement and identity insert, how to insert identity column data using merge statement? I posted for the merge statement without identity insert. Please read that post first before move ahead. So I would like to publish my friend’s question and answer too. It’s nothing but simple as identity insert for single table without merge. Let me generate objects required for the demo,

IF ( Object_id('EmpList1', 'U') > 0 ) 
  DROP TABLE emplist1 

IF ( Object_id('EmpList2', 'U') > 0 ) 
  DROP TABLE emplist2 

CREATE TABLE emplist1 
  ( 
     seq1     INT NOT NULL IDENTITY(1, 1), 
     empid1   INT NOT NULL PRIMARY KEY, 
     empname1 VARCHAR(50) 
  ) 

CREATE TABLE emplist2 
  ( 
     seq2     INT NOT NULL IDENTITY(1, 1), 
     empid2   INT NOT NULL PRIMARY KEY, 
     empname2 VARCHAR(50) 
  ) 

INSERT INTO emplist1 
VALUES      (1001, 
             'Emp1001') 

INSERT INTO emplist2 
VALUES      (1001, 
             'Emp2001') 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

DELETE FROM emplist2 
WHERE  seq2 = 2 

INSERT INTO emplist2 
VALUES      (1002, 
             'Emp2002') 

SELECT * 
FROM   emplist1 

SELECT * 
FROM   emplist2 

You can see in the script and the image where we have to update and insert record in table emplist1 from emplist2, where record with seq1 will be updated and record with seq3 will be inserted with an identity. So emplist1 will become a target and emplist2 will become a source for this operation. Let me put a script here for same,

SET IDENTITY_INSERT emplist1 ON 

MERGE emplist1 
USING emplist2 
ON ( empid1 = empid2 ) 
WHEN matched THEN 
  UPDATE SET empname1 = empname2 
WHEN NOT matched BY target THEN 
  INSERT(seq1, 
         empid1, 
         empname1) 
  VALUES(seq2, 
         empid2, 
         empname2) 
WHEN NOT matched BY source THEN 
  DELETE; 

SET IDENTITY_INSERT emplist1 OFF 

You can see I used IDENTITY_INSERT on top and an identity column in the code while inserting records. Now checking records after the end,

I know you know about this, but I shared this post because I never used merge statement and identity insert at once. Have you ever used?

How to manual failover mirroring without affecting replication - SQL Server by serverku

Before a couple of days we planned to manual failover of production servers and all live databases for that instance to mirror instance and did a failover too. It was a good experience for failover without fail anything like replication, scheduled jobs, linked servers, ssis packages, reports, windows tasks and whatever dependencies. Well perfect planning and team work was key for that succeed failover for us. This post is about to considering mirroring without witness server\automatic failover and transactional replication where the production database to act as a publisher and principal and the plan for same like following.

Planning

• Configured mirror all production databases to mirror instance without witness server.
• Created a dns alias for production server.
• Used that alias as a data source to connect production sql server instance in linked servers, reports, ssis packages in all servers which pointing production instance and in application too .
• Created all scheduled jobs with disable status in mirror instance.
• Created all linked servers of production instance in mirror instance.
• Created all logins of production instance in mirror instance.
• Created all database mail profiles of production instance in mirror instance.
• Created sql server agent operators of production instance in mirror instance.
• Created windows scheduled tasks with disable status in mirror server.

All of above steps applied in advance with recent changes just before to start failover and need to change dns alias to mirror server, enable scheduled jobs and windows tasks during failover.

Problem 
But had a little bit confusing for replication, How to manually failover of mirroring without affecting replication? That was an issue. I have applied the solution and made it succeed. After manual failover, transactional replication started to raise an error and stopped working. Because it was trying to connect publisher database but it became a mirror after failover,

The process could not execute 'sp_replcmds' on '<original Publisher Server>'.

Workaround
There is one more step apart from listed above.

• Add Failover Partner as a parameter (–PublisherFailoverPartner) in snapshot, log reader and queue reader agents.

How to add parameter?
I am sharing some screen shots which drive us for the explanation,
1. Go to Replication monitor and move to agent tab. Select agent from Agent types drop box, you will have list of agents, select it and click Agent Profiler from right click property.

2. Under Agent property, create a new user profile which will be created same as system profile, just need to add value <failover partner> of –PublisherFailoverPartner parameter.

3. Add –PublisherFailoverPartner parameter value for all agents like snapshot, log reader, queue reader agent and merge agent we have merge replication configured.

Note : After creating a new user agent profile check the box “Use for this agent”. I created a new agent profile because it does not allow to add –PublisherFailoverPartner parameter for system profile from the user interface. But we can add it with system procedures with tsql script.

USE distribution 
GO 

-- For Snapshot Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 1, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Log Reader Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 2, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Distribution Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 3, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- For Merge Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 4, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

-- Queue Reader Agent 
EXEC sp_add_agent_parameter 
  @profile_id = 9, 
  @parameter_name = N'-PublisherFailoverPartner', 
  @parameter_value = N'<Failover Partner>' 

It has allowed to add this parameter for system profiles, but change the profile_id whatever system or user profile used for an agent which you will get it from sp_help_agent_profile system procedure from distribution database.

How to confirm?
By following script we can confirm the parameter values for such agents. Run this script in msdb database from distributor server.

USE msdb 
GO

SELECT a.profile_id, 
       a.profile_name, 
       a.description, 
       a.def_profile, 
       b.parameter_name, 
       b.value 
FROM   msagent_profiles a 
       INNER JOIN msagent_parameters b 
               ON ( a.profile_id = b.profile_id ) 
WHERE  b.parameter_name = '-PublisherFailoverPartner' 

(Click on image to enlarge)
Hope you enjoyed this case and might help you a lot. Did you face this issue or what is your solution? Something missing in failover plan? Please share your ideas and opinion about it. Your comments are most welcome!