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.

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.

Copy nonclustered indexes to subscriber while generating snapshot - SQL Server Replication by serverku

Few days back, I was working to add new table in a publication in transaction replication. After adding an article in a publication and after reinitialized, I see non-clustered index was not copied at subscriber database. I tried reinitialized one more time and had a same issue. Let me generate scenario and look forward to the solution after that,

USE [PublisherDB]
GO

CREATE TABLE [dbo].[EmpList]
(
 [EmpId] [int] NOT NULL PRIMARY KEY,
 [EmpName] [varchar](100) NULL,
 [EmpCity] varchar(100) NULL,
 [EmpAddress] [varchar](200) NULL
)

CREATE NONCLUSTERED INDEX IX_EmpList_Name ON [dbo].[EmpList] ([EmpName]) 

I added above created a table in publication and reinitialized subscription with new snapshot and check indexes of that table at both publisher and subscriber side,

EXEC sp_helpindex [EmpList]

Non-clustered is not copied to subscriber after new snapshot applied. After some workaround I found a solution. Make true for the option of copy nonclustered indexes from article property,
Go to Publication property –>  Articles tab –> Article properties –> Set Properties of Highlighted Table Article or Set Properties of All Table Articles and apply changes as following,

After applying changes above and new snapshot generated after that, it copied non clustered indexes to the subscriber database. There are some other options you can find there,

All existing non-clustered indexes copied after new snapshot applied, but I created one more non-clustered index on that table and it's not copied subscriber database without generating a new snapshot. That new index copied after only new snapshot applied of that table. So I am leaving one discussion open here, why that newly created non-clustered index not copied without generating a new snapshot? Any alternative way or recommends solution?

Script to get running schedule jobs - SQL Server by serverku

A few days back, while I was working with Alerts for failed scheduled jobs and missing scheduled jobs, I was needed to exclude running jobs. So we can exactly know which jobs are actually failed excluding running jobs and sane case for missing jobs. I applied some additional in the existing script to fetch the records of failed scheduled jobs or missing jobs. Let me share a script here to get all running scheduled jobs,

USE msdb
GO

CREATE TABLE #RunningJobs 
( 
    job_id                UNIQUEIDENTIFIER NOT NULL,   
    last_run_date         INT              NOT NULL,   
    last_run_time         INT              NOT NULL,   
    next_run_date         INT              NOT NULL,   
    next_run_time         INT              NOT NULL,   
    next_run_schedule_id  INT              NOT NULL,   
    requested_to_run      INT              NOT NULL,   
    request_source        INT              NOT NULL,   
    request_source_id     sysname          COLLATE database_default NULL,   
    running               INT              NOT NULL,   
    current_step          INT              NOT NULL,   
    current_retry_attempt INT              NOT NULL,   
    job_state             INT              NOT NULL
)   
  
INSERT INTO  #RunningJobs  
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa'

SELECT sj.name, 
       rj.* 
FROM   #runningjobs rj 
       INNER JOIN dbo.sysjobs sj 
               ON ( sj.job_id = rj.job_id ) 
WHERE  rj.running = 1 

DROP TABLE #RunningJobs

Above script use dbo.xp_sqlagent_enum_jobs undocumented object from the master database. It is also useful to get all other state values of jobs like following which received from forums,

0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions

I am using this script in failed jobs and missing jobs list. You might be using it somewhere, Please share your comments. I will publish further posts to get failed jobs and missing jobs list.

How to increase the number of characters retrieved from the server for XML data - SQL Server by serverku

You might work with xml and you may aware of title mentioned in this post. Today when I was working to collect information from one table which having xml column, I was trying to open xml data result set from query analyzer with new query window. For some of the xml data result set it to raise an error below while opening with new window,

Unable to show XML. The following error happened:  There is an unclosed literal string. 

This is just an error I faced for some xml data which are lengthy in size. But error is showing solution too “One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options”. Let follow toward it and go ahead the step suggested, please see below snapshot which is showing the option where we can change the suggested setting,

Tools –> Options

Nothing to say other than the screen shot above. I know this is common post, but I shared it with you because I never faced this issue earlier and also did not aware about this setting. Let us share here if have this in your mind earlier.

Skip distributor agent error in sql server transactional replication by serverku

A while before few weeks, I discussed about an error of replication “The row was not found at the Subscriber when applying the replicated command”. We had a trick to get discrepancies for error table and resolved the issue. Let me put another method (Actually patch) to come out from same error which is very interesting. Before moving this method, Please read some my posts related to replication which you may like,

1. Replication components are not installed on this server-Error while adding subscriber in replication
2. Replicated transactions are waiting for next Log backup or for mirroring partner to catch up-Issue in SQL Server Replication

Now going to move ahead, here I am talking about the solution for the above highlighted error while is “Skip error in sql server transactional replication, How to? ”.

Raise a problem
We will consider the same objects created and same scenario for a transactional replication in this post. Let us check the data of ready made tables,

SELECT * 
FROM   test.dbo.sample1 (nolock) 

SELECT * 
FROM   test1.dbo.sample1 (nolock) 

We have the same records in both tables at the publisher and subscriber side. Now it is time to create discrepancies using following script,

-- Deleting one record from table in subscriber database 
DELETE FROM test1.dbo.sample1 
WHERE  id = 2 

-- Updating same record from table in publisher database 
UPDATE test.dbo.sample1 
SET    name = 'test5' 
WHERE  id = 2 

-- Inserting new record in table in publisher database 
INSERT test.dbo.sample1 
SELECT 4, 
       'test4' 

Monitoring replication after the above script ran,

And viewing table’s data again from the publisher and subscriber after an error,

You can see nothing happened at subscriber because of an error occurred for one missing row.

Solution
We are about to skip this error where all replication commands stuck, So sp_setsubscriptionxactseqno system stored procedure help us which is used to troubleshooting to specify the log sequence number (LSN) of the next transaction to be applied by the Distribution Agent at the Subscriber. So let us first get sequence number of an error which we have from the replication monitor. We can also use following script to get all transactional replication errors and executed in distributor server in a distributed database,

USE distribution 
GO

DECLARE @PublisherServer  VARCHAR(50), 
        @PublicationDB    VARCHAR(50), 
        @SubscriberServer VARCHAR(50), 
        @SubscriberDB     VARCHAR(50), 
        @PublicationName  VARCHAR(50) 

SET @PublisherServer = '<Publisher>' 
SET @PublicationDB = 'test' 
SET @SubscriberServer = '<Subscriber>' 
SET @SubscriberDB = 'test1' 
SET @PublicationName = 'testpub' 

EXEC Sp_helpsubscriptionerrors 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  @SubscriberServer, 
  @SubscriberDB 

GO 

(Click on image to enlarge)
Please get a top sequence number of an error from where all transaction commands stuck and run following script on the subscriber side,

USE test1 
GO

DECLARE @PublisherServer VARCHAR(50), 
        @PublicationDB   VARCHAR(50), 
        @PublicationName VARCHAR(50) 

SET @PublisherServer = '<Publisher>'
SET @PublicationDB = 'test' 
SET @PublicationName = 'testpub' 

EXEC Sp_setsubscriptionxactseqno 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  0x00000022000000C1000400000000 

GO

Finally running above script in the subscriber database, this error skipped and all remaining and pending commands were applied, which we can see the data of both tables from the publisher and subscriber database,

This is just my experience which I am sharing with you. It is recommended to find a route of this error and solve it. Did you receive such error and you skipped any? Please share your thoughts!