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.

CTE within CTE - SQL Server by serverku

As per requirement in custom logic, we need to require CTE (Common Table Expression) something like for the hierarchy, to find duplicate and remove data or for some other stuff. Recently i have used CTE within CTE for one logic and maxrecursion option as well.  So we will look at how we can use CTE inside CTE or multiple CTEs. Let us create one requirement. The requirement is we need to find the first and second objects by object types in the database and it should be in ascending order. 

The sample data will be created from the script below. Let us create it first, then we will demonstrate for the logic that need to be created as per requirement.

USE DEMO
GO

-- Creating sample table
IF(OBJECT_ID('TblCTEwithCTE','U') > 0)
  DROP TABLE TblCTEwithCTE

CREATE TABLE TblCTEwithCTE
(
 ObjectNumber INT ,
 ObjectType VARCHAR(50),
 ObjectName VARCHAR(100),
 ObjectCreateDate DATETIME
)

GO

-- Inserting sample records created above
INSERT INTO TBLCTEWITHCTE
(
 ObjectNumber,
 ObjectType,
 ObjectName,
 ObjectCreateDate
)
SELECT 
 ROW_NUMBER() OVER(PARTITION BY TYPE_DESC ORDER BY TYPE_DESC,CREATE_DATE) as ObjectNumber,
 TYPE_DESC,
 NAME,
 CREATE_DATE 
FROM SYS.OBJECTS

Here we are creating first CTE to get only objects with creating first based or created date by object type categories.

-- Fetching first created objects
;WITH FirstCreatedObjectsCTE
AS
(
 SELECT 
  ObjectNumber as ObjectNumber ,
  ObjectType as ObjectType, 
  ObjectName as FirstCreatedObject
 FROM TBLCTEWITHCTE WHERE ObjectNumber = 1
) 

SELECT 
 * 
FROM FirstCreatedObjectsCTE

GO

Now we have completed work for the first created objects by object type categories. And it is time to have the second created objects using first CTE and another second CTE to finally come out with an output which having both first created and next created objects. This first created and next created object by object type categories will be shown as column as follows.

-- Original table data.
SELECT 
 ObjectNumber,
 ObjectType,
 ObjectName,
 ObjectCreateDate
FROM TBLCTEWITHCTE
GO

-- Fetching first created objects in first CTE and using in second CTE for the second created objects.
;WITH FirstCreatedObjectsCTE
AS
(
 SELECT 
  ObjectNumber as ObjectNumber ,
  ObjectType as ObjectType, 
  ObjectName as FirstCreatedObject
 FROM TBLCTEWITHCTE WHERE ObjectNumber = 1
) 
,

SecondCreatedObjectsCTE
AS
(
 SELECT 
  t.ObjectType as ObjectType,
  c.FirstCreatedObject as FirstCreatedObject,
  t.ObjectName as SecondCreatedObject
 FROM TBLCTEWITHCTE t 
  RIGHT OUTER JOIN 
 FirstCreatedObjectsCTE c
  ON (c.ObjectType = t.ObjectType and t.ObjectNumber = c.ObjectNumber + 1)
)

SELECT 
 * 
FROM SecondCreatedObjectsCTE

GO

Hope you like this, stay tuned from more.

Changing Rows to Columns Using PIVOT - SQL Server by serverku

During working with one logic, I got a chance to work with PIVOT operation. Sometime we need do require row data as a column in our custom logic, then we can use some temp table and then populate aggregate data in a temp table. But With PIVOT we can do it very easily. Let me prepare small example and explain as how how can we use PIVOT and get row data as a column.

Before going ahead to run the script of Pivot, we will create a database and table objects.

CREATE DATABASE DEMO
GO

USE DEMO
GO

-- Creating table for demo
IF (object_id('TblPivot','U') > 0)
DROP TABLE TblPivot

CREATE TABLE TblPivot
(
ItemCode int,
ItemName varchar(100),
ItemColour varchar(50)
)
GO

-- Inerting some sample records
INSERT INTO TblPivot
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 3,'Nokia Mobile','Green'
UNION ALL
SELECT 4,'Motorola Mobile','Red'
UNION ALL
SELECT 5,'Samsung Mobile','Green'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
UNION ALL
SELECT 1,'Samsung Mobile','Red'
UNION ALL
SELECT 2,'Nokia Mobile','Blue'
GO

Now we will check the original table data and aggregated data using Pivot. So we will run both scripts for the same.

-- Getting table data
SELECT 
ItemCode, 
ItemName, 
ItemColour
from TblPivot
GO

-- Getting agreegated data using Pivot and converted rows to column
SELECT
*
 FROM
 (
    SELECT 
 ItemCode, 
 ItemName, 
 ItemColour
    FROM TblPivot
 ) AS P
PIVOT
(
  Count(ItemName) FOR ItemColour IN (Red, Blue, Green)
) AS pv
GO

You can review here and see how The PIVOT is working. Let me share your experience with PIVOT operation.

Backup Statistics and History - SQL Server by serverku

Every DBA has a daily activity review or monitor database backups as these database backups used for the restoration at the other place and using for the database restore which used for reporting purposes or used in log shipping purpose. Because database backups are the most important factor and first option in case of disaster recovery even whatever types of them because in this case transaction logs can reduce the data loss.

You can read my earlier posts Database Backup Compression, Database Backup files Verification , Automated All Databases Backups Script and Split Database Full Backup to Multiple files.

I would like to share the script which helps us to show the database backup status, history of theirs when they are done based on schedule, at where are taking and when, backup types, backups, physical device and the size of the database backups and time to perform backup and all other related backup statistics. Here is the script to collect the database backup statistics and status information.

USE MSDB
GO
 
SELECT
 bs.server_name AS Server, -- Server name
 bs.database_name AS DatabseName , -- Database name
 CASE bs.compatibility_level 
   WHEN 80 THEN 'SQL Server 2000'
   WHEN 90 THEN 'SQL Server 2005 '
   WHEN 100 THEN 'SQL Server 2008'
   WHEN 110 THEN 'SQL Server 2012'
 END AS CompatibilityLevel , -- Return backup compatibility level
 recovery_model AS Recoverymodel , -- Database recovery model
 CASE bs.type 
   WHEN 'D' THEN 'Full'
   WHEN 'I' THEN 'Differential'
   WHEN 'L' THEN 'Log'
   WHEN 'F' THEN 'File or filegroup'
   WHEN 'G' THEN 'Differential file'
   WHEN 'P' THEN 'Partial'
   WHEN 'Q' THEN 'Differential partial'
 END AS BackupType, -- Type of database baclup
 bs.backup_start_date AS BackupstartDate, -- Backup start date
 bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
 bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
 CASE device_type
   WHEN 2 THEN 'Disk - Temporary'
   WHEN 102  THEN 'Disk - Permanent'
   WHEN 5  THEN 'Tape - Temporary'
   WHEN 105  THEN 'Tape - Temporary'
   ELSE 'Other Device'
 END AS DeviceType, -- Device type
 bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
 bs.compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily bmf WITH (NOLOCK)
 ON (bs.media_set_id=bmf.media_set_id)
ORDER BY bs.backup_start_date DESC
 
GO

(Click on image to enlarge)

Hope you liked this post.

NOLOCK Hint & READ UNCOMMITTED Isolation level on table and Query/Session level - SQL Server by serverku

When we created a new database, it will be created with default isolation level and that is "READ COMMITTED". If some update transactions are running in with table rows under READ COMMITTED isolation level, How can we get data from a table in another session while running update transaction?

How can ?
NOLOCK hint or READ UNCOMMITTED isolation level help for the same as there are operating same. We have some other options other than this. But I am going to present the NOLOCK hint and READ UNCOMMITTED isolation level here.

For NOLOCK, we need to put this hint on table level, so it is required to put for every table level which are used in update transaction. So it is very lengthy and time consuming to put it everywhere, tables refers in the query. For READ UNCOMMITTED, We do not need to put it every tables level, just put at session level or query level and can be written on top of the query or stored procedure. Let us look on small demo to elaborate it. First checking here database default isolation level,

USE DEMO
GO
DBCC USEROPTIONS

Starting with creating a database and table objects.

IF (OBJECT_ID('TrnTable','U') > 0)
DROP TABLE TrnTable

CREATE TABLE TrnTable
(
TrnId INT ,
TrnData VARCHAR(100),
TrnDate DATETIME
)

GO

-- Inserting some sample records in table

INSERT INTO TrnTable(TrnId,TrnData,TrnDate)
SELECT 1,'TrnData-1',GETDATE()
UNION ALL
SELECT 2,'TrnData-2',GETDATE()
UNION ALL
SELECT 3,'TrnData-3',GETDATE()
UNION ALL
SELECT 4,'TrnData-4',GETDATE()
UNION ALL
SELECT 5,'TrnData-5',GETDATE()

GO

Now for the demo we will run the below script with session 1,

-- Script in session 1
-- Running query with transaction named TRAN1
BEGIN TRANSACTION TRAN1

UPDATE TrnTable
SET TrnData = 'Changed TrnData'
WHERE TrnId = 3
-- Not Committed/Rollback this transaction

After that we will get the same rows which are updated in above session, which are not committed yet in another session. It will be going on waiting to release the lock held by session 1,

We are not closing this transaction here, and created a new session and run following scripts having a NOLOCK hint on table level and READ UNCOMMITTED isolation level on query level.

-- Script in session 3
-- With NOLOCK hint
SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable (NOLOCK)
WHERE TrnId = 3

GO

-- With READ UNCOMMITTED isolation level 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT 
TrnId,
TrnData,
TrnDate
FROM TrnTable
WHERE TrnId = 3

GO

Do not forget to commit or rollback transaction TRAN1,
Commit Transaction TRAN1

I hope you liked this post. Please let me know what you are using among them or else something?

"Can not add a shared registered server with the same name as configuration Server" - Central Management Servers register error in SQL Server 2008 by serverku

You may know how can we register SQL Server instances with Central Management Servers (CMS) and also how can we perform multi server query will all of the instances of registered SQL Server instances.

There you can see I have registered one, shared SQL Server 2011 instance, under CMS and named it Denali. Under Shared instance, I have registered SQL Server 2008 and same SQL Server instance of Denali which I already registered. Here SQL server 2008 successfully registered but SQL Server 2011 has encountered an error. But how I have registered it which I am going to explain here.

This is because the same SQL instance already registered as shared SQL server.

How can i register same SQL Server Denali or 2011 instance again?

I have changed the port and applied static port as follows from SQL Server TCP/IP properties which we will available on the SQL Server Configuration Monitor.

Then please see the screenshot below as I have registered SQL Server Denali instance with port.

Now both SQL Server registered successfully.

Did you get it earlier? How did you resolve?

Database Backup Compression, Amazing feature for DBA - SQL Server 2008 by serverku

Production Database servers may have databases which are heavily in size. For the maintenance of those database backups are very hard and lengthy as the backups of those heavily databases take more time to execute and very CPU, memory and IO consumptive. And the important thing is backups activity should be completed within down time or pick a time when more users are not connected with databases.

What is the solution?
We have alternative ways to use the some backup tool that can help to use in this matter. But SQL Server itself provides the best feature and supported SQL Server 2008 or newer version. That is "Backup Compression". You can read my earlier posts for Automated All Databases Backups, Database Backup files Verification and Details and Split Database Full Backup to Multiple files

Using this feature, we can take a database backup with compression option. And will really reduce the time required to backup it, reduce server IO and less CPU and memory consumption. It is very full features for the DBA. Let us look on below example, which will clear you the difference between the uncompressed and compressed backups. We will first perform non-compressed backups of the database which have 4 GB size.
Using Management Studio :

Using TSQL:

#1. Performing noncompressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_NonCompressedBackup.bak' 
WITH NAME = N'ReportServer-Full NonCompressed Database Backup',
NO_COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

#2. Performing compressed backup.

SET STATISTICS IO ON
SET STATISTICS TIME ON

BACKUP DATABASE ReportServer TO  
DISK = N'D:\DBBackups\Compressed\ReportServer_CompressedBackup.bak' 
WITH NAME = N'ReportServer-Full Compressed Database Backup',
COMPRESSION -- Specifying option here

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
GO

From the result oputput , you can view the time for the backup execution, CPU usage. Here you have screen for the both of the backups size.

You can use below query to get the backup statistics,

SELECT 
 bs.database_name AS DatabaseName , -- Database name
 backup_size/compressed_backup_size as CompressionRatio, 
 CASE bs.type 
  WHEN 'D' THEN 'Full' 
  WHEN 'I' THEN 'Differential' 
  WHEN 'L' THEN 'Log' 
  WHEN 'F' THEN 'File or filegroup' 
  WHEN 'G' THEN 'Differential file' 
  WHEN 'P' THEN 'P' 
  WHEN 'Q' THEN 'Differential partial' 
 END AS BackupType, -- Type of database baclup
 bs.backup_start_date AS BackupstartDate, -- Backup start date
 bs.backup_finish_date AS BackupFinishDate,  -- Backup finish date
 bmf.physical_device_name AS PhysicalDevice, -- baclup Physical localtion
 bs.backup_size AS  [BackupSize(In bytes)],  -- Normal backup size (In bytes)
 compressed_backup_size AS [ConmpressedBackupSize(In bytes)] -- Compressed backup size (In bytes)
FROM msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
 ON (bs.media_set_id=bmf.media_set_id)
 AND database_name = 'ReportServer'
ORDER BY bs.backup_start_date DESC

You can set the default backup setting to Compressed as following,

By TSQL :

USE MASTER
GO

EXEC SP_CONFIGURE 'backup compression default', 1
GO
RECONFIGURE WITH OVERRIDE;
GO

From UI :

I hope you like this feature..

"The database owner SID recorded in the master database differs from the database owner SID recorded in database" - SQL CLR DLL Register error in SQL Server by serverku

Recently, while working with SQL CLR functionality and created DLL for the SQL CLR. But while registering this DLL in the database I got one surprised error. Let's show you the script so you have more idea. We have a script to register the DLL as following,

SP_CONFIGURE 'clr enabled',1
GO
RECONFIGURE
GO

USE SQLCLRDb 
GO

CREATE ASSEMBLY [SQLCLR_ASSEMBLY] 
FROM 'C:\SQLCLR_ASSEMBLY.dll' WITH permission_set = UNSAFE 

GO 

The error is,

The database owner SID recorded in the master database differs from the database owner SID recorded in database.
You should correct this situation by resetting the owner of database using the ALTER AUTHORIZATION statement.

The solution for this issue is which we have the script below. This script will change dbowner of the running database and make it trustworthy on.

USE SQLCLRDb 
GO

ALTER DATABASE SQLCLRDb SET TRUSTWORTHY ON
go

EXEC SP_CHANGEDBOWNER 'UserName'
GO

After running above query, I come out of the issue and registered SQL CLR DLL successfully. I think you also suffered same or different issues with SQL CLR. Please comment your issues and the solution for the same.

Grouping Sets vs Native method of Group By - Performance review in SQL Server 2008 by serverku

Previously I have posted for the overview and usage of the Grouping Sets as how can we get the aggregate data of different group sets with GROPING SETS vs Native method of group by.

If you have not read my earlier post for the same, then please read it before go ahead with this demonstration. In this demo i am going to show the performance of Grouping Sets and native method of group by. Let us start the demo here.

-- Creatind database and table 
CREATE DATABASE GroupingDB

GO

USE GroupingDB

GO

IF (OBJECT_ID('GroupingTable') > 0)
DROP TABLE GroupingTable

CREATE TABLE GroupingTable
  (
     MainCategoryName VARCHAR(100)
     ,SubCategoryId   VARCHAR(100)
     ,VALUE           BIGINT
  )

GO 

Now we are going to insert so many records in table for the presentation of demo.

-- Insert some demo records for different group sets
INSERT INTO GroupingTable
SELECT 'Main-1','Sub-1',100
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-1','Sub-2',200
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-2','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-1',300
GO 500 

INSERT INTO GroupingTable
SELECT 'Main-3','Sub-2',400
GO 500 

As I said to check the performance of both of the script using them and check the execution plan.

-- Get aggregate data using native method of group by of different sets
SELECT NULL, NULL,
  SUM(VALUE) as Total
FROM   GroupingTable
Union all
SELECT  MainCategoryName
,NULL
,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY MainCategoryName 
Union all
SELECT  NULL,
  SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY SubCategoryId 
union all
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
  MainCategoryName
  ,SubCategoryId

-- Get aggregate data using Grouping Sets of different sets
SELECT
  MainCategoryName
  ,SubCategoryId
  ,SUM(VALUE) as Total
FROM   GroupingTable
GROUP  BY
GROUPING SETS
(
  (MainCategoryName ,SubCategoryId),
  (MainCategoryName),
  (SubCategoryId),
  ()

)
ORDER BY MainCategoryName,SubCategoryId

You can see the data rows of above both scripts are same and given same result set. Now look for execution plan.

(Click on image to enlarge)

This performance review is totally based on the data and depends on the your business requirement. Before implementing this new feature, please check the execution and decide you view.

Multi server Query with Central Management Servers - SQL Server 2008 by serverku

Before SQL Server 2008, when we need to gather all information and details related to a server or database level, we must run the script individually by connecting each SQL Server instance. But SQL Server 2008 came up and easy our work for that. It has introduced a new feature - Central Management Servers (CMS).

With Central Management Servers we can configure and register SQL Server instances with shared SQL Server instances. Then we run the query against all the SQL instance and get the details for all instances. Let's you demonstrate the same in details here.

1. How to open Central Management Servers?

Go to View --> Registered Servers 
or
press Ctrl + Alt + G.

2. How can register SQL Server instances in CMS?

Expand Database engine from Registered Servers. Right click on the CMS and click on Register SQL Server Management. A new screen will appear below,

In the above, I have registered SQL Server Denali instance, which will be shared SQL Server instance. Now I am creating a new SQL Server Group under CMS and then register SQL Server 2008 and SQL Server 2011 by right click on the group and then go to the link of registration and then go on the same way as I did for SQL Server Denali instance.

3. How can we perform multi server query against all SQL Server ?

Go on right click on Shared SQL Server instance under CMS and click on New Query.

Let's do here same and execute the query and see what will be the result?

This feature very help us to run the script against all the registered SQL Server instances. Hope you liked this post.