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.

script to Rebuild / Reorganize all index for all databases as per fragmentation ratio by serverku

AS a DBA activity, index maintenance is a main activity and here I am sharing a script to rebuild or reorganize indexes as periodically. The way is here to first collect index fragmentation statistics in one table and process further for rebuilding or reorganize. So follow the script below,

1. Create table to collect index fragmentation data

CREATE TABLE [dba].[IndexFragDetails](
 [SID] [int] IDENTITY(1,1) NOT NULL,
 [TableName] [varchar](500) NULL,
 [IndexName] [varchar](500) NULL,
 [IndexType] [varchar](100) NULL,
 [FragPercent] [int] NULL,
 [PageCount] [int] NULL,
 [CheckedDate] [datetime] NULL,
 [LastRebuiltDate] [datetime] NULL,
 [TimeTaken_Min] [int] NULL,
 [Result] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dba].[IndexFragDetails] ADD  DEFAULT (getdate()) FOR [CheckedDate]
ALTER TABLE [dba].[IndexFragDetails] ADD  DEFAULT ((0)) FOR [Result]
GO

2. Capture Index fragmentation statistics

DECLARE @CurrentDate datetime
SET @CurrentDate = GETDATE()

-- Fetching top 100 indexes
;With IndexFragReBuild
As
(
SELECT TOP 100
    [object_id] AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag, 
    page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED')
 WHERE avg_fragmentation_in_percent > 5.0  -- Allow limited fragmentation
 AND index_id > 0 -- Ignore heaps
 AND page_count > 1000 -- Ignore small tables
 ORDER BY avg_fragmentation_in_percent desc
)

-- Inserting in detail table
INSERT INTO dba.IndexFragDetails
(
TableName,
IndexName,
IndexType,
FragPercent,
[PageCount],
CheckedDate
)
SELECT 
 QUOTENAME(Schema_name(t.schema_id))+'.'+QUOTENAME(object_name(IFD.objectid)) as TableName,
 QUOTENAME(i.name) as IndexName, 
 I.Type_Desc,
 IFD.Frag, 
 IFD.Page_Count,
 @CurrentDate
FROM IndexFragReBuild IFD
INNER JOIN sys.indexes i
 ON i.object_id = IFD.objectid AND i.index_id = IFD.indexid
INNER JOIN  sys.tables  t
 ON t.object_id = IFD.objectid

-- Removing non-clustered indexes where clustered index exists for same table 
DELETE a 
FROM dba.IndexFragDetails a
 WHERE a.CheckedDate = @CurrentDate
 AND a.IndexType = 'NONCLUSTERED'
 AND EXISTS 
(
 SELECT 
  b.tablename 
 FROM dba.IndexFragDetails b
  WHERE b.CheckedDate = @CurrentDate
  AND a.tablename = b.tablename
  AND b.IndexType = 'CLUSTERED'
)
GO

3. Rebuilding or Reorganize indexes

SET QUOTED_IDENTIFIER ON

DECLARE @MaxCheckedDate datetime, 
  @ErrorMessage varchar(4000)

SET @MaxCheckedDate = (SELECT MAX(c.CheckedDate) FROM dba.IndexFragDetails c WITH (NOLOCK))

CREATE TABLE #IndexFragRebuild
(
 Seq int identity (1,1),
 TableName varchar(100),
 IndexName varchar(100),
 IndexType varchar(100),
 FragPercent int,
 PageCount bigint,
 LastRebuiltDate varchar(20) default 'NA'
)

INSERT INTO #IndexFragRebuild
(
 TableName,
 IndexName,
 IndexType,
 FragPercent,
 PageCount,
 LastRebuiltDate
)

-- Fetching indexes to be rebuild or reorganize from details table
SELECT 
 a.TableName,
 a.IndexName,
 a.IndexType,
 a.FragPercent,
 a.PageCount,
 ISNULL((select CONVERT(varchar(20),MAX(b.LastRebuiltDate),101) FROM dba.IndexFragDetails b  WITH (NOLOCK) 
   WHERE b.TableName=a.TableName AND b.IndexName=a.IndexName ),'NA')
FROM dba.IndexFragDetails a WITH (NOLOCK)
 WHERE 
 a.LastRebuiltDate IS NULL
 AND a.CheckedDate = @MaxCheckedDate
 AND a.Result = 0
 Order by a.FragPercent DESC

DECLARE @Subject VARCHAR (1000)  
DECLARE @Body VARCHAR(MAX) 
DECLARE @hpart VARCHAR(400)  
Declare @ini int,@cnt int, 
  @sql varchar(500), 
  @tableName varchar(500), 
  @indexName varchar(500),
  @Startdate datetime, 
  @EndDate datetime,
  @SDate datetime, 
  @EDate datetime,
  @FragPercent int


SET @Subject = 'List of the indexes to be Rebuild\Reorganize'  
SET @hpart='TableName

IndexName

IndexType

FragPercent

PageCount

LastRebuiltDate

'  

SET @Body =  
CAST ( ( SELECT  
  td = TableName, '',
  td = IndexName, '',  
  td = IndexType, '', 
  td = FragPercent, '',
  td = PageCount, '',
  td = LastRebuiltDate, ''
from #IndexFragRebuild
FOR XML PATH('tr'), TYPE  
) AS VARCHAR(MAX) ) +  
N''  

SET @Body = ''+@hpart+@Body+'
'  + '
'  

-- Sending an email of list of indexes to be rebuild\reorganize
EXEC msdb.dbo.sp_send_dbmail  
   @recipients = 'prajapatipareshm@gmail.com'
  , @subject = @subject  
  , @body = @Body  
  , @profile_name = 'ProfileName'  
  , @body_format = 'HTML' ;
 

 SET @ini = 1
 SET @cnt = (select MAX(seq) from #IndexFragRebuild)
 SET @SDate = GETDATE()
 
 WHILE(@ini <= @cnt)
 BEGIN
 BEGIN TRY

 SELECT @tableName = TableName, 
   @indexName = IndexName,
   @FragPercent = FragPercent
 FROM #IndexFragRebuild 
 WHERE seq = @ini
  
 IF (@FragPercent > 30)
 BEGIN
 SET @sql = 'ALTER INDEX '+@indexName+' on ' +@tableName+ ' REBUILD WITH (ONLINE = Off);'
 END
 ELSE BEGIN
 SET @sql = 'ALTER INDEX '+@indexName+' on ' +@tableName+ ' REORGANIZE ;'
 END
 
 EXEC (@sql)
  
 Update dba.IndexFragDetails
 SET LastRebuiltDate = GETDATE(),
  TimeTaken_Min = DATEDIFF(MINUTE,@Startdate,@Enddate),
  Result = 1 -- 1-Finish, 2-Failed, 0-Pending
 WHERE 
 LastRebuiltDate IS NULL
 AND TableName = @tableName 
 AND IndexName = @indexName
 AND CheckedDate = @MaxCheckedDate
 

 END TRY
 BEGIN CATCH
  SET @ErrorMessage = ERROR_MESSAGE()

  SET @Enddate = GETDATE()

  UPDATE dba.IndexFragDetails
  SET LastRebuiltDate = NULL,
   TimeTaken_Min = DATEDIFF(MINUTE,@Startdate,@Enddate),
   Result = 2  -- 1-Finish, 2-Failed, 0-Pending
  WHERE 
  LastRebuiltDate IS NULL
  AND TableName = @tableName 
  AND IndexName = @indexName
  AND CheckedDate = @MaxCheckedDate
 
  SET @subject = 'Rebuild\Reorganize error occured : ' + @indexName + ' index on ' + @tableName + ' table '
 
  -- Sending an email of failed operation
  EXEC msdb.dbo.sp_send_dbmail  
    @recipients = 'prajapatipareshm@gmail.com'
   , @subject = @subject  
   , @body = @ErrorMessage  
   , @profile_name = 'ProfileName'  
   , @body_format = 'HTML' ;

 END CATCH
 
 SET @ini = @ini  + 1
 WAITFOR DELAY '00:00:05'
 END
 SET @EDate = GETDATE()
 
 SET @subject = 'All Indexes Rebuilt\Reorganized in '+ cast(DATEDIFF(MINUTE,@Sdate,@EDate) as varchar) + ' Minutes'
 
 -- Sending an email of completion
 EXEC msdb.dbo.sp_send_dbmail  
   @recipients = 'prajapatipareshm@gmail.com'
  , @subject = @subject  
  , @body = @subject  
  , @profile_name = 'ProfileName'  
  , @body_format = 'HTML' ;
 
 DROP TABLE #IndexFragRebuild
 GO
 

This is just what i want to share. Please share your opinion.

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.

Script to get table size statistics - SQL Server by serverku

Recently we have seen the post for database size information and the script used for the same. Hope you liked that post. Sometime we need to have details like database size for growth and disk planning we viewed in an earlier post, the same way I am repeating this post in details like earlier size statistics for database and now it is for tables. You have seen in an earlier post we used sp_spaceused to get database size information, but what about if we pass the table name as parameter, I will return for particular table size statistics in one user database,

USE [AdventureWorksLT2008]
GO
EXEC sp_spaceused '[SalesLT].[Product]'; 

/* Output :

name     rows  reserved  data    index_size  unused
-------- ----- --------- ------- ----------- ------
Product  295   928 KB    808 KB  96 KB       24 KB

*/

The same information can be achieved for all tables using a loop (while loop or cursor ) or sp_msforeachtable system procedure,

USE [AdventureWorksLT2008]
GO
CREATE TABLE #tablesizestatistics 
  ( 
     Tablename  VARCHAR(100), 
     [rows]     VARCHAR(100), 
     reserved   VARCHAR(50), 
     data       VARCHAR(50), 
     index_size VARCHAR(50), 
     unused     VARCHAR(50) 
  ) 

EXEC Sp_msforeachtable 
  @command1='INSERT INTO #TableSizeStatistics exec sp_spaceused ''?''' 

SELECT * 
FROM   #tablesizestatistics 

/* Output :

Tablename                        rows   reserved  data    index_size  unused
-------------------------------- ------ --------- ------- ----------- -------
BuildVersion                     1      16 KB     8 KB    8 KB        0 KB
Address                          450    336 KB    72 KB   160 KB      104 KB
Customer                         847    512 KB    272 KB  136 KB      104 KB
CustomerAddress                  417    80 KB     32 KB   48 KB       0 KB
Product                          295    928 KB    808 KB  96 KB       24 KB
ProductCategory                  41     48 KB     8 KB    40 KB       0 KB
ProductDescription               762    248 KB    144 KB  56 KB       48 KB
ProductModel                     128    248 KB    56 KB   104 KB      88 KB
ProductModelProductDescription   762    112 KB    48 KB   64 KB       0 KB
SalesOrderDetail                 542    120 KB    40 KB   80 KB       0 KB
SalesOrderHeader                 32     64 KB     8 KB    56 KB       0 KB
ErrorLog                         0      0 KB      0 KB    0 KB        0 KB

*/

You may use a loop instead of above method and run sp_spaceused inside it to get for each table. But without a loop and sp_msforeachtable and sp_spaceused we have another script which help us to get each tables size statistic,

USE [AdventureWorksLT2008]
GO
SELECT Schema_name(t.schema_id) + '.' + t.name        AS TableName, 
       p.rows                                         AS Rows, 
       Sum(a.total_pages) * 8                         AS TotalSpace_KB, 
       Sum(a.used_pages) * 8                          AS UsedSpace_KB, 
       (Sum(a.total_pages) - Sum(a.used_pages)) * 8 AS UnusedSpace_KB 
FROM   sys.tables t 
       INNER JOIN sys.indexes i 
               ON t.object_id = i.object_id 
       INNER JOIN sys.partitions p 
               ON i.object_id = p.object_id 
                  AND i.index_id = p.index_id 
       INNER JOIN sys.allocation_units a 
               ON p.partition_id = a.container_id 
GROUP  BY Schema_name(t.schema_id) + '.' + t.name, 
          p.rows 
ORDER  BY totalspace_kb DESC

/* Output :

TableName                                Rows       TotalSpace_KB  UsedSpace_KB  UnusedSpace_KB
---------------------------------------- ---------- -------------- ------------- --------------
SalesLT.Product                          295        928            904           24
SalesLT.Customer                         847        512            408           104
SalesLT.Address                          450        336            232           104
SalesLT.ProductDescription               762        248            200           48
SalesLT.SalesOrderDetail                 542        120            120           0
SalesLT.ProductModelProductDescription   762        112            112           0
SalesLT.ProductModel                     128        112            104           8
SalesLT.CustomerAddress                  417        80             80            0
SalesLT.SalesOrderHeader                 32         64             64            0
SalesLT.ProductCategory                  41         48             48            0
dbo.BuildVersion                         1          16             16            0
dbo.ErrorLog                             0          0              0             0

*/

Hope you enjoyed this post and this may help you to get same table statistics. I would like you to share if we can have some other scripts or some additional information for tables.

Changing Columns to Rows Using UNPIVOT - SQL Server by serverku

I have already posted the article previously with my PIVOT. As PIVOT same, but working reversely and it is UNPIVOT. I do not want to miss the post using UNPIVOT. With PIVOT we can get aggregate data with Rows as Columns. With UNPIVOT operation, we can change Columns to Rows without aggregation.

Let me prepare small example and explain as how how can we use UNPIVOT and changing column as Rows. Before go ahead to run the script of UnPivot, we will create a database and table objects.

USE DEMO
GO

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


CREATE TABLE TblUnPivot
(
 ItemCode int,
 ItemName varchar(100),
 Red int,
 Green int,
 Blue int
)

GO

-- Inserting some sample records

INSERT INTO TblUnPivot
SELECT 1,'Samsung Mobile', 1, 1, 1
UNION ALL
SELECT 2,'Nokia Mobile', 1, 2, 3
UNION ALL
SELECT 3,'Motorola Mobile', 2, 3, 2

GO

Now we will check the original table data and also using UnPivot as well. So we will run both scripts for the same.

-- Getting table data
select 
 ItemCode,
 ItemName,
 Red,
 Green,
 Blue
from TblUnPivot

GO

-- Getting  Pivot and changing columns to rows
SELECT
 ItemCode,
 ItemName,
 ItemColour,
 ItemCount
FROM
 (
    SELECT 
      ItemCode,
      ItemName,
      Red,
      Green,
      Blue
    FROM TblUnPivot
 ) AS UP
UNPIVOT
(
  ItemCount FOR ItemColour IN (Red, Green, Blue)
) AS upv

GO

You can review here and see how the UNPIVOT is working. Let me share your experience with UNPIVOT.

Database size information - SQL Server by serverku

This is common information which DBA required on a daily basis for monitoring or reporting purposes. This is basic details which help us to get the database growth statistics on daily, weekly and monthly basis so we can have an idea for the disk space management. We can dump daily database statistics and make database size and growth reports. You all know about the script and you may use too, even I would like to share. System stored procedures help us to get the same database information, e.g sp_helpdb stored procedure give details,

EXEC sp_helpdb 'DEMO';

/* Output :

name       db_size   owner  dbid   created     status           compatibility_level
------------------------ ------ ------ ----------- ---------------- -------------------
DEMO       13.25 MB  sa     19     Apr 28 2015 Status=ONLINE,...   110

 
name       fileid filename                                                                      filegroup size     maxsize        growth  usage
-------   -------- ------------------------------------------------------------------------------ --------- ------- ------------   -------- -------
DEMO       1  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO.mdf     PRIMARY   9280 KB  Unlimited      1024 KB data only
DEMO_log   2  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO_log.ldf NULL      4288 KB  2147483648 KB  10%     log only

*/

This is all information for database which returned by sp_helpdb by passing the database name as a parameter. If you do not wish to pass the database name, then it will return all databases without physical file details. Same approach receive by sp_spaceused system stored procedure without any parameter passing.

USE DEMO
GO
EXEC sp_spaceused;

/* Output :

database_name  database_size      unallocated space
-------------- ------------------ ------------------
DEMO           13.25 MB           3.52 MB

reserved      data               index_size         unused
------------- ------------------ ------------------ ---------
5680 KB       3064 KB            2024 KB            592 KB


*/

Now I would like to get it with some other scripts which give details for each database and their physical files.

USE master 
GO 

SELECT db.[name]                  AS 'DBName', 
       af.name                    AS 'LogicalName', 
       af.[filename]              AS 'PhysicalName', 
       ( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 1024 ) 
              AS 
                NUMERIC(18, 2)) ) AS 'FileSize_MB' 
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 

/* Output :

DBName                LogicalName               PhysicalName                                                                                FileSize_MB
------------------- --------------------------- ---------------------------------------------------------------------------------------- ---------------
master                master                    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\master.mdf                 4.88
master                mastlog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\mastlog.ldf                1.75
tempdb                tempdev                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\tempdb.mdf                 8.00
tempdb                templog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\templog.ldf                0.50
model                 modeldev                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\model.mdf                  4.06
model                 modellog                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\modellog.ldf               1.00
msdb                  MSDBData                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\MSDBData.mdf               22.31
msdb                  MSDBLog                   C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\MSDBLog.ldf                19.63
test                  test                      C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test.mdf                   4.06
test                  test_log                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test_log.ldf               1.31
test1                 test1                     C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test1.mdf                  8.00
test1                 test1_log                 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\test1_log.ldf              19.63
Repl1                 Repl1                     C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Repl1.mdf                  23.00
Repl1                 Repl1_log                 C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Repl1_log.ldf              2.50
SBExternal            SBExternal                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternal.mdf             4.06
SBExternal            SBExternal_log            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternal_log.ldf         1.02
SBExternalReceiver    SBExternalReceiver        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternalReceiver.mdf     4.06
SBExternalReceiver    SBExternalReceiver_log    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SBExternalReceiver_log.ldf 1.02
importdb              importdb                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\importdb .mdf              4.06
importdb              importdb _log             C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\importdb _log.ldf          1.02
import                import                    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\import.mdf                 9.06
import                import_log                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\import_log.ldf             2.81
SchemaSync            SchemaSync                C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync.mdf             4.06
SchemaSync            SchemaSync_log            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_log.ldf         1.02
SchemaSync_New        SchemaSync_New            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_New.mdf         4.06
SchemaSync_New        SchemaSync_New_log        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SchemaSync_New_log.ldf     1.02
hMailServer           hMailServer               C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\hMailServer.mdf            4.06
hMailServer           hMailServer_log           C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\hMailServer_log.ldf        1.02
AdventureWorksLT2008  AdventureWorksLT2008_Data D:\Data\AdventureWorksLT2008_Data.mdf                                                       8.31
AdventureWorksLT2008  AdventureWorksLT2008_Log  D:\Data\AdventureWorksLT2008_Log.ldf                                                        18.00
powershelldb          powershelldb              C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\powershelldb.mdf           4.06
powershelldb          powershelldb_log          C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\powershelldb_log.ldf       1.02
SMOSimple_DB          SMOSimple_DB              C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SMOSimple_DB.mdf           4.06
SMOSimple_DB          SMOSimple_DB_log          C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\SMOSimple_DB_log.ldf       1.02
Merge_Repl_Sub        Merge_Repl_Sub            C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Merge_Repl_Sub.mdf         9.00
Merge_Repl_Sub        Merge_Repl_Sub_log        C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\Merge_Repl_Sub_log.ldf     3.75
DEMO                  DEMO                      C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO.mdf                   9.06
DEMO                  DEMO_log                  C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12\MSSQL\DATA\DEMO_log.ldf               4.19

*/

This is details returned by each physical file of all databases, but now the same script with minor changes return whole database size details without physical files detail.

USE master 
GO 

SELECT db.[name]                           AS 'DBName', 
       Sum(( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 
                    1024 ) AS 
                        NUMERIC(18, 2)) )) AS 'FileSize_MB' 
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 
GROUP  BY db.[name] 

/* Output :

DBName                FileSize_MB
--------------------- -------------
master                6.63
tempdb                8.50
model                 5.06
msdb                  41.94
test                  5.37
test1                 27.63
Repl1                 25.50
SBExternal            5.08
SBExternalReceiver    5.08
importdb              5.08
import                11.87
SchemaSync            5.08
SchemaSync_New        5.08
hMailServer           5.08
AdventureWorksLT2008  26.31
powershelldb          5.08
SMOSimple_DB          5.08
Merge_Repl_Sub        12.75
DEMO                  13.25

*/

These are the scripts which I want to share with you which may help you and I would like you to share your thoughts and scripts which you are using for database size and growth statistics. You may read my earlier post for Database Backup Statistics and History.

SQL Server Logical Reads - What's it? by serverku

Recently, when I was working on the tuning of stored procedures and I experienced with one performance issue, that was high Logical Reads.

Logical Reads?
"Number of pages read from the data cache" - It occurs every time when the database engine request a page from buffer cache, otherwise physical reads occurs if current page is not available in buffer cache. Let us go, through the sample demo and get experience for the logical reads. First, we need require objects, so we are creating database and tables inside it.

USE DEMO
GO

-- Creating a table
IF (OBJECT_ID('TblLogicalReads','U') > 0)
  DROP TABLE TblLogicalReads
GO

CREATE TABLE TblLogicalReads
(
 TranId INT,
 TrnData VARCHAR(100),
 TrnDate DATETIME
)

GO

After creating objects, 49999 records will be inserted by following scripts.

DECLARE @cnt BIGINT

SET @cnt  = 1

WHILE (@cnt < 50000)
BEGIN

INSERT INTO TblLogicalReads (TranId,TrnData,TrnDate)
VALUES (@cnt, 'Demo Records ' + CONVERT(VARCHAR(100),@cnt ), GETDATE() - @cnt)

SET @cnt = @cnt +1

END

GO

Now we are checking logical reads from the script which are going to be run.

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TranId = 5

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TrnDate = '2009-12-26 18:10:47.653'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF
GO

/* Output :

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 8 ms.

(1 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 278, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 59 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 278, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 4 ms.
   
*/

You can see here, logical reads are high.

How can we reduce it?
There are many factors to reduce it as it depends on But here, for example, we need to create some required indexes on columns which are used in the queries.

-- Creating indexes on tables 
CREATE CLUSTERED INDEX IX_TranId ON TblLogicalReads(TranId)
GO

CREATE NONCLUSTERED INDEX IX_TrnDate ON TblLogicalReads(TrnDate)
GO

Finally we are on the stage where we need to review logical reads after creating indexes on tables

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TranId = 5

SELECT 
  TranId,
  TrnData,
  TrnDate
FROM TblLogicalReads 
 WHERE TrnDate = '2009-12-26 18:10:47.653'

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

/* Output : 
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'TblLogicalReads'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TblLogicalReads'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
*/

Hope you understood the logical reads difference and performance impact from the examples given above. You can share your knowledge for the same.

Using force index hint - SQL Server by serverku

I wrote about my previous just learned tips about covering index and will publish my future post for the detail understanding it. We should generate index statistics and index usage report periodically, so we can have more idea of the index utilization. Sometime we require different indexes other than the query optimizer used for the execution for the best performance.

How can we use different indexes other that query optimizer used for execution? You can force the indexes to be used with Index Hint. Here is the small demonstration by example. Please look on below script and created objects for demo.

-- Creating objects
USE DEMO
GO

-- Creating table which will be used for demo

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

CREATE TABLE TblForceIndexHint
(
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in tables 

INSERT INTO TblForceIndexHint
SELECT 
object_id,
name,
create_date,
type_Desc  
FROM SYS.OBJECTS

GO

Now creating one clustered index, Normal non-clustered index and an additional non-clustered index with include means covering index.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_ObjectId ON TblForceIndexHint (ObjectId)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate on TblForceIndexHint (CreateDate)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblForceIndexHint (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType)
GO

To review how force Indexes hint works, We need to get the data with CreateDate filtered. So let us start with there.

-- Keep query optimizer to decides and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE CreateDate >= GETDATE() - 50

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE CreateDate >= GETDATE() - 50

Here you see the first query (by Query optimizer) and last query (forcing index hint) have used same index. Reviewing same index hints, but the query will get the data on ObjectId filter from where condition.

-- Keep query optimizer to decide and use the index
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint
WHERE ObjectId >= 1000000

-- Forcing query to not using any indexes.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (0))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_ObjectId indexes created on ObjectId
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint  with (index (IX_ObjectId))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate indexes created on CreateDate
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate))
WHERE ObjectId >= 1000000

-- Forcing query to using IX_CreateDate_Covering indexes created on CreateDate which is additional index.
SELECT 
ObjectId,
ObjectName,
ObjectType
FROM TblForceIndexHint with (index (IX_CreateDate_Covering))
WHERE ObjectId >= 1000000

Here you see the first query (by Query optimizer) and third query (forcing index hint) have used same index. Please share your experience if you ever used force indexes hint.