How to reset identity column value on date change in sql ser

 

Option 1: Create a scheduler/job which will reset the identity column value. It will run everyday at  12:00 AM which will execute the below SQL query: 

DBCC CHECKIDENT('TableName', RESEED, 0)

Option 2: You can do it manually: Get no of records as per today date and increase its value by 1. See below SQL query

 

Declare @temp INT;

 

SELECT @temp = Count(ID) + 1

FROm tblName

 

Hi Anil, It is possible but do one job /schedule. That job is doing below steps

1 . Every day take backup and original  data move to table tbl_bup_op.

2.  Truncate table data in original table.

 It is automatically identity column  start  wit 1 only for every day. I hope it is resolve the for your requirement.