SQL Server Script : Get Parent Child hierarchy or tree structure or recursive records in sql query

You can get parent child hierarchy records or category sub category records or recursive records from table in sql server and display as a tree structure.
We are using A common table expression (CTE)  to get result. In fact we are using Recursive CTE to get this recursive data.

Here is example for this.
In this example we take one table "category_master". This table contains recursive records. This table has "category_id" , "category_name" and "parent_category_id" columns. "parent_category_id" column contain reference of "category_id". We can insert "N - Level" of parent child relationship records. We retrieve those records using recursive "CTE" and we can also do Hierarchy level by applying spacing so it's look like tree structure.

SQL Server Scripts : Insert particular value in Identity column in table by using IDENTITY_INSERT property

You can insert value in identity column manually using INSERT statement using "IDENTITY_INSERT" property. You need to set IDENTITY_INSERT property to ON to allow identity insert value.
If there is already IDENTITY_INSERT property ON for particular table and again you set to ON It will raise error "states SET IDENTITY_INSERT is already ON and reports the table it is set ON".
After Set ON and Insert records we need to set OFF to avoid unnecessary errors.
You need permission to set this property. By default "sysadmin" server role and the "db_owner" and "db_ddladmin" database role and the object owner has permission.

Here is example for this.
In this example we take one database table "product_master". This table has one identity column "product_id" and other column "product_name".
Now we are inserting some records and we can see that it's identity column has serial unique number. After that we delete one records from middle of that records. So now one gap introduce in serial unique number. Now if you want to insert another product it did not generate that missing serial number it will simply generate greater value from existing numbers.
Suppose we have certain situations where we want to insert that particular identity number record. So at that time we can use "IDENTITY_INSERT" property. You can see in screen shot that there is product_id="5" record is deleted and is missing from list. Now we again insert that identity value using "IDENTITY_INSERT".

SQL Query :

SET IDENTITY_INSERT [product_master] ON
INSERT INTO product_master (product_id,product_name) values(5,'USB')
SET IDENTITY_INSERT [product_master] OFF

Output (Missing Record Id Sequence) : 

Output (After inserting Missing Record Id Sequence) :

 

This is the very useful SQL Server Scripts.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.

Sql Server : How to Reset the Identity Column of a table in Sql Server

Sometimes we have situation in which we want delete all records of table and that table containts identity column as primary key and we want again insert some new records in this table ,
so in this case identity column does not start with 1 but it start with last number that we had before deleted records.
To avoid this situation we need to reset identity column.
After reset identity column it will start with 1.

Here are sample example for that :

We have one table "product_master" , in this table we have one identity column "product_id".
Now we inserted 3 records into this , so the table entries look like this :

After Delete all records using this query :

     delete from product_master

Now table is empty.

After delete now if we insert new record. The newly added record have product_id value 4. because identity column does not reset.You can see this in below image

To reset Identity column Here are Syntax:
Syntax :

    DBCC CHECKIDENT('TableName', RESEED, 0)

Now again delete all records and execute this command :

    DBCC CHECKIDENT('product_master', RESEED, 0)

This command resent the identity of given table.

Now again you insert one record this record has product_id value 1. You can see this in below image.

FDTBBTJNWV8H