SQL Server: Use of Recursive Query with example

One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them.

 

Recursive query is the efficient way to display hierarchy setup (Parent child relation ship in the same table). e.g. Grand Parent, Parent, Child. This is common requirement. To display the parent child relationship in efficient manner, we can use Recursive CTE. Let me explain it by example.

Create Temporary Table:

DECLARE @Images TABLE (
 [ResourceID] [int] NOT NULL,
 [ChildOf] [int] NOT NULL,
 [ResourceName] [varchar](250) NOT NULL
 )

Lets populate some data into this table:

INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (5, 0, N'Mammal')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (6, 5, N'Carnivore')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (14, 6, N'Cannine')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (15, 14, N'Dog')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (16, 15, N'Dog1')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (17, 14, N'Monkey')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (18, 17, N'Monkey1')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (19, 16, N'dog1.01')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (21, 18, N'monkey1.02')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (23, 17, N'test')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (25, 17, N'Monkey 2')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (31, 0, N'Fish')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (32, 31, N'cartilaginous fish')
INSERT @Images ([ResourceID], [ChildOf], [ResourceName]) VALUES (33, 32, N'dogfish')

That is how data is stored in the table:

 

 

This is not the way that we can understand the hierarchy. Recursive query will help us to display result like this:

This is much efficient way to list out, as we understand that how the hierarchy is being maintained. Recursive CTE helps us to generate this kind efficient output. Let me share the t-sql script which generates the output which we are looking for:

;WITH images
AS (
 SELECT *
  ,ROW_NUMBER() OVER (
   ORDER BY ResourceID
   ) AS RowID
 FROM @images
 )
 ,cte
AS (
 SELECT 1 AS [Level]
  ,[ResourceID]
  ,[ResourceName]
  ,ChildOf
  ,cast(cast(RowID AS VARBINARY(4)) AS VARBINARY(max)) AS sort
 FROM images
 WHERE ChildOf = 0
 --where [ResourceID] = 5
 
 UNION ALL
 
 SELECT p.[Level] + 1
  ,c.[ResourceID]
  ,c.[ResourceName]
  ,c.ChildOf
  ,cast(p.sort + cast(c.RowID AS VARBINARY(4)) AS VARBINARY(max))
 FROM images c
 INNER JOIN cte p ON p.[ResourceID] = c.ChildOf
  --WHERE c.ChildOF=5
 )
SELECT ResourceID
 ,ChildOf
 ,REPLICATE('.', ([Level] - 1) * 4) + [ResourceName] AS menu
FROM cte
ORDER BY sort

 

That's it. Here, I have used first CTE, to assign the Unique numbers. Then just iterate thru each record and find their related children records. Hope this helps you.

Reference : Tejas Shah http://www.SQLYoga.com

SQL Server, avoid encoding special character when using FOR XML PATH

From SQL 2005, we are using FOR XML PATH('') for the string concatenation. I have also mentioned the same in very old blog: Generate Comma Separated List with SELECT statement. 

Today, we face an issue when special character gets encoded and we hate to have special character in my string. 

e.g: Table has records like: 
1. Gynecology & Obstetrics 
2. Dermatology 

Expected output (after concatenating two rows): Gynecology & Obstetrics, Dermatology. 

When we used FOR XML PATH, we got the output as: 
Gynecology & Obstetrics,Dermatology ("&" character is encoded and it lists out "&")

This output was not the one which we were expected. To get the expected output, we need to find workaround and we finally get it as: 

SELECT STUFF(((
        SELECT ',' + RTRIM(CONVERT(VARCHAR(50), sm.Speciality))
        FROM Table1 T2
        INNER JOIN Table2 sm ON sm.ID = t2.Speciality_ID
        FOR XML PATH(''),TYPE).value('.[1]', 'varchar(max)')
    ), 1, 1, '')

After adding "TYPE", we are able to get the expected output: Gynecology & Obstetrics, Dermatology 

Reference : Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: Find Current Location of Log File(s) with File Size of All the Database

As we are mostly engaged in SQL Assignments, we come to the place where we found that there are lots of ldf files which occupies lots of the space on the DISK. To continue my work, I have to make a room on the same DISK. I found that there are lots of log files (LDF) on the disk which occupies more than 1 TB (1024 GB). This is the development server and I found and confirmed that we can have “Recovery Option” as “SIMPLE” too. The tricky part over here is, these databases are already created and Log file names are not same as database name. So, first of all I need to find out the database name to which I can make Recovery Option as Simple. Finally, I got following query which helps me to continue my assignment as follows:

SELECT    d.name As DatabaseName,
        m.name As FileName,
        m.physical_name As PhysicalFileName,
        (m.size * 8.0) / 1024 / 1024 As SizeInGB
FROM sys.master_files m
INNER JOIN sys.databases d ON d.database_id = m.database_id
WHERE m.Type = 1
It will give us the list as follows:

Reference: Tejas Shah (www.SQLYoga.com)