Retrieving Hierarchical Database table using Recursive CTE

Read Previous article: Hierarchical Data Storing in Database table

Problem Statement:

Suppose folder structure is der like folder n subfolder so suppose “Main” folder has sub folder “A” and “B” and “A”  has “a1” and B has “b1” so how will you maintain dis data in single table and how you will get all sub folders(“A”,”B”,”a1”,”b1”) of “Main” folder .

Level1      Level2      Level3

---------- ---------- ----------

mail       A          a1

Solution :

create table SnehalKaQuestion

(FolderId int,

FolderName varchar(10),

ParentFolder int null) 

insert into SnehalKaQuestion values(1,'mail',null),(2,'A',1),(3,'a1',2),(4,'B',1),(5,'b1',4)

select * from SnehalKaQuestion

FolderId    FolderName ParentFolder

----------- ---------- ------------

1           mail       NULL

2           A          1

3           a1         2

4           B          1

5           b1         4

(5 row(s) affected)

Now we want to find the level of each folder in the folder hierarchal structure, lets see how we will be achieving this use recursive CTE.

Here we are creating a stored procedure which will use CTE to derive the required result

create procedure proc_FindFolderLevel

@folderName varchar(10)

as

begin

;with cte(folderid,foldername,parentfolder)

as

(

select folderid,foldername,parentfolder from SnehalKaQuestion where foldername = @folderName

union all

select s.folderid,s.foldername,s.parentfolder from cte c,SnehalKaQuestion s

where c.parentfolder = s.folderid)

select 'Level' + cast (ROW_NUMBER() over (order by folderid) as varchar) as Level ,foldername  from cte

end  

This stored Procedure is taking one argument as folder_name for which we need to find the hierarchy.

Let’s execute the procedure to find the result

Test 1 : With ‘a1’, to see the folder hierarchy of a1

exec proc_FindFolderLevel 'a1'

Level                               foldername

----------------------------------- ----------

Level1                              mail

Level2                              A

Level3                              a1

(3 row(s) affected)

Test 2 : With ‘b1’, to see the folder hierarchy of b1

exec proc_FindFolderLevel 'b1'

Level                               foldername

----------------------------------- ----------

Level1                              mail

Level2                              B

Level3                              b1

(3 row(s) affected)

Test 3 : With ‘A’, to see the folder hierarchy of A

exec proc_FindFolderLevel 'A'

Level                               foldername

----------------------------------- ----------

Level1                              mail

Level2                              A

(2 row(s) affected)

Test 4 : With ‘B’, to see the folder hierarchy of B

exec proc_FindFolderLevel 'B'

Level                               foldername

----------------------------------- ----------

Level1                              mail

Level2                              B

(2 row(s) affected)

Test 5: With ‘mail’, to see the folder hierarchy of mail

exec proc_FindFolderLevel 'mail'

Level                               foldername

----------------------------------- ----------

Level1                              mail

(1 row(s) affected)

Cool to witness, we can pull off this with CTE.

Heart beat is just an evidence of programmer, if your code is burning well; heart beat is just an ash...

Post Reference: Vikram Aristocratic Elfin Share

A new column type with Sparse Column XML COLUMN_SET

Read Previous Post: Optimize your Storage while using NULL in your field with SPARSE Column.

There’s a new column type which is available to use with sparse columns – an XML COLUMN_SET. This is a column is only comes into picture when selected, and will return all the non-NULL sparse columns in a row as an XML BLOB.

It will also change the behavior of a SELECT * operation – removing all the sparse columns from the resultset, representing all the non-NULL sparse columns.

Let’s Check out with an example, here we are creating a table with sparse

create table SparseTest

(id int sparse,

name varchar(10) sparse,

addrss varchar(15) sparse,

sparseColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS)

Command(s) completed successfully.

Lets try to insert few null values in the SparseTest table few records in  table

declare @i int = 1

while  @i <= 3

begin

      insert into SparseTest(id,name,addrss) values(null,null,null)    

      set @i=@i+ 1     

end

Let’s insert few non null values in SparseTest table.

insert into SparseTest(id,name,addrss) values(1,'Gargi','Mumbai')

insert into SparseTest(id,name,addrss) values(1,'Bratiti','Banglore')

insert into SparseTest(id,name,addrss) values(1,'Lopamudra','Delhi')

insert into SparseTest(id,name,addrss) values(1,'Monalisa','Pune')

insert into SparseTest(id,name,addrss) values(1,'Pragayan',null)

Lets query the SparseTest to check out the result set.

select * from SparseTest

sparseColumns

----------------------------------------------------------

NULL

NULL

NULL

<id>1</id><name>Gargi</name><addrss>Mumbai</addrss>

<id>1</id><name>Bratiti</name><addrss>Banglore</addrss>

<id>1</id><name>Lopamudra</name><addrss>Delhi</addrss>

<id>1</id><name>Monalisa</name><addrss>Pune</addrss>

<id>1</id><name>Pragayan</name>

(8 row(s) affected)

Here we can see it changes the behavior of a SELECT * operation – removing all the sparse columns from the resultset, representing all the non-NULL sparse columns, you can define column name in select statement to retrieve the column values, like this

select id,name,sparseColumns from SparseTest

id          name       sparseColumns

----------- ---------- ----------------------------------------------------

NULL        NULL       NULL

NULL        NULL       NULL

NULL        NULL       NULL

1           Gargi      <id>1</id><name>Gargi</name><addrss>Mumbai</addrss>

1           Bratiti    <id>1</id><name>Bratiti</name><addrss>Banglore</addrss>

1           Lopamudra  <id>1</id><name>Lopamudra</name><addrss>Delhi</addrss>

1           Monalisa   <id>1</id><name>Monalisa</name><addrss>Pune</addrss>

1           Pragayan   <id>1</id><name>Pragayan</name>

(8 row(s) affected)

When code burns from heart, it shapes to rebellion   

Post Reference: Vikram Aristocratic Elfin Share

Optimize your Storage while using NULL in your field with SPARSE Column.

If a column is defined with Sparse column then if the column hold null value, sparse column will take much lesser space as compared to regular column.

Few facts on SPARSE column

•        A column can be declared sparse by specifying the ‘SPARSE’ after the data type in the table.
•        A table can contain sparse columns of up to 30000. Though a table contains 30000 sparse columns the maximum number of non sparse columns in a table is still 1024 only.
•        If the value of a column is NULL, it doesn’t consume space/ lesser space at all. It stores the data in a single xml column but for an external application it behaves like a normal column.

Let’s Check out with an example, here we are creating two table one with sparse and second without sparse.

create table SpareTest

(id int sparse,

name varchar(10) sparse,

addrss varchar(15) sparse)

Command(s) completed successfully.

create table withoutsparse

(id int,

name varchar(10),

addrss varchar(15))

Command(s) completed successfully.

Lets try to insert few records in both the table

declare @i int = 1

while  @i <= 100000

begin

      insert into SpareTest values(null,null,null)

      insert into withoutsparse values(null,null,null)

      set @i=@i+ 1     

end

Now let’s see how much space used in both the table to store the null values

sp_spaceused 'SpareTest'

go

name       rows    reserved  data     index_size unused

------------------ --------  -------  ---------- --------

SpareTest  100000  1224 KB   1176 KB  8 KB       40 KB

sp_spaceused 'withoutsparse'

go

name       rows    reserved  data     index_size unused

------------------ --------  -------  ---------- --------

SpareTest  100000  1480 KB   1416 KB  8 KB       56 KB

Conclusion: Here we can easily compare, how much space are being saved when we are using Spare for storing null values.

Code burn; lighten world

Post Reference: Vikram Aristocratic Elfin Share

MERGE WHEN NOT MATCHED

MERGE WHEN NOT MATCHED BY TARGET

Refer Previous Post before reading this: MERGE WHEN MATCHED

You should use this clause to insert new rows into the target table. The rows you insert into the table are those rows in the source table for which there are no matching rows in the target. For example, the BookOrder table contains a row for Gone with the Wind. However, the BookInventory table does not contain this book. The following example demonstrates how to include a WHEN NOT MATCHED clause in your MERGE statement that adds Gone with the Wind to your target table:

Let see first what is there in the target table

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   12

2           Pale Yellow wale Dimond Kanche           3

5           Green Suresh ke churaye kanche           10

6           Purple Kamal se jite kanche              0

8           Blue har game jitane wala lucky kanche   4

What is there in source table

select * from MarblePurchased_ChalKancheLene

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   12

2           Pale Yellow wale Dimond Kanche           3

5           Green Suresh ke churaye kanche           10

6           Purple Kamal se jite kanche              0

8           Blue har game jitane wala lucky kanche   4

Now if we apply MERGE when NOT MATCHED by TARGET then it should insert all the rows which are present in source (MarblePurchased_ChalKancheLene) but not there in TARGET (MarblesCounts_KancheKhelega)

merge MarblesCounts_KancheKhelega kc

using MarblePurchased_ChalKancheLene ckk

on kc.marble_id = ckk.marble_id

when MATCHED AND

  kc.marble_count + ckk.marble_count = 0 then

  delete

when MATCHED then

  update

  set kc.marble_count = kc.marble_count + ckk.marble_count

when NOT MATCHED by TARGET then

  insert (marble_id, marble_color, marble_count)

  values (ckk.marble_id, ckk.marble_color,ckk.marble_count);

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   15

2           Pale Yellow wale Dimond Kanche           3

3           Red Ramesh ke kanche                     0

4           Grey bade unlucky kanche                 4

5           Green Suresh ke churaye kanche           15

6           Purple Kamal se jite kanche              0

7           Silver bade expensive Kanche             8

8           Blue har game jitane wala lucky kanche   4

(8 row(s) affected)

So here we can see the apply of MERGE when NOT MATCHED by TARGET  insert all the rows which were present in source (MarblePurchased_ChalKancheLene) but not there in TARGET (MarblesCounts_KancheKhelega)  in target table(MarblesCounts_KancheKhelega)  .

MERGE WHEN NOT MATCHED BY SOURCE

suppose you want to delete a row from the target table that does not match a row in the source table. To delete a row that does not match a row in the source table, you must use the WHEN NOT MATCHED BY SOURCE clause.

Lets see what rows are there in source (MarblePurchased_ChalKancheLene) and target table(MarblesCounts_KancheKhelega).  

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   15

2           Pale Yellow wale Dimond Kanche           3

3           Red Ramesh ke kanche                     0

4           Grey bade unlucky kanche                 4

5           Green Suresh ke churaye kanche           15

6           Purple Kamal se jite kanche              0

7           Silver bade expensive Kanche             8

8           Blue har game jitane wala lucky kanche   4

select * from MarblePurchased_ChalKancheLene

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   3

3           Red Ramesh ke kanche                     0

4           Grey bade unlucky kanche                 4

5           Green Suresh ke churaye kanche           5

7           Silver bade expensive Kanche             8

The following example includes a WHEN NOT MATCHED BY SOURCE clause that specifies that any rows with a quantity of 0 that do not match the source should be deleted:

merge MarblesCounts_KancheKhelega kc

using MarblePurchased_ChalKancheLene ckk

on kc.marble_id = ckk.marble_id

when MATCHED AND

  kc.marble_count + ckk.marble_count = 0 then

  delete

when MATCHED then

  update

  set kc.marble_count = kc.marble_count + ckk.marble_count

when NOT MATCHED by TARGET then

  insert (marble_id, marble_color, marble_count)

  values (ckk.marble_id, ckk.marble_color,ckk.marble_count)

when NOT MATCHED by SOURCE

  AND kc.marble_count = 0 then

  delete;

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   18

2           Pale Yellow wale Dimond Kanche           3

4           Grey bade unlucky kanche                 8

5           Green Suresh ke churaye kanche           20

7           Silver bade expensive Kanche             16

8           Blue har game jitane wala lucky kanche   4

(6 row(s) affected)

A MERGE statement can simplify your code, improve performance, and reduce your development effort.

Fuel up you; start code for tomorrow, people are waiting  

Post Reference: Vikram Aristocratic Elfin Share