Wednesday, 13 September 2017

Resetting IDENTITY Seed in Table Variable

For a "normal" table, you would use: DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value]).
you cannot reseed a table variable

A workaround might be to use ROW_NUMBER() instead.

declare @a table (sno int identity,aa nvarchar(100))
insert into @a(aa) values ('testw'),('testwsfsf')

select row_number() OVER (ORDER BY sno) as sno,aa from @a

delete from @a
  
insert into @a(aa) values ('testw'),('testwsfsf')
select row_number() OVER (ORDER BY sno) as sno,aa from @a


No comments:

Post a Comment

Getting Available Server Disk Space (Total Size / Free Space) SQL Query

SELECT distinct(volume_mount_point), total_bytes/1048576 as Size_in_MB, total_bytes/1048576/1024 as Size_in_GB, available_bytes/1048576 ...