Reset identity column values after delete and insert error in SQL Server

samsmuthu
 
on May 12, 2021 11:21 PM
765 Views

Hi,

SQL primary key IDENTITY problem

I have created a MS-SQL table as follows; 

CREATE TABLE [dbo].[UserRoles](
	[UserRolelD] [int] IDENTITY(1,1) NOT NULL,
	[Date_Time] [datetime] NOT NULL DEFAULT (getdate()),
	[UserID] [int] NOT NULL DEFAULT ((1)),
	[RegDate] [datetime] NOT NULL DEFAULT (getdate()),
	[Active] [bit] NOT NULL DEFAULT ((1)),
	[RoleName] [nvarchar](100) NOT NULL,
	[RoleDescription] [nvarchar](1000) NULL,
	[ModulerID] [int] NOT NULL,
 CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED 
(
	[UserRolelD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

PROCEDURE for date insert

CREATE PROCEDURE [dbo].[UserRoles_Insert]
	@LoginName nvarchar(20) = 'sampath',
	@RoleName nvarchar(100) = 'xxx',
	@RoleDescription nvarchar(1000) = 'xxx',
	@ModulerID int = 5
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @UserID int
	SET @UserID = (SELECT UserID FROM [SATHUTA-ERP].[dbo].[ErpUsers] WHERE LoginName = @LoginName)

	DECLARE @RoleDescription1 nvarchar(1000) 
	SET @RoleDescription1 = (SELECT CASE WHEN LTRIM(RTRIM(@RoleDescription)) = '' THEN NULL ELSE LTRIM(RTRIM(@RoleDescription)) END)

	IF NOT EXISTS(SELECT UserID FROM [SATHUTA-ERP].[dbo].[ErpUsers] WHERE RoleId = 1 AND ActiveUser = 1 AND UserID = @UserID)
		BEGIN
			SELECT -1
		END
	ELSE
	IF EXISTS(SELECT UserRolelD FROM UserRoles WHERE ModulerID = @ModulerID and RoleName = LTRIM(RTRIM(@RoleName)))
		BEGIN
			SELECT -2
		END
	ELSE
		BEGIN
			INSERT INTO UserRoles (
							RoleName,
							RoleDescription,
							ModulerID,
							Date_Time,
							UserID,
							RegDate,
							Active)
			VALUES (	LTRIM(RTRIM(@RoleName)),
						@RoleDescription1,
						@ModulerID,
						GETDATE(),
						@UserID,
						GETDATE(),
						1)
			END
	END
GO

After 

https://drive.google.com/file/d/1xZgflBPiIfAiyJcuWzzVYqt8bW0Uz6mo/view?usp=sharing 

My problem was;

[UserRolelD] [int] IDENTITY(1,1) is not 1,2,3,4,5,….n

Thanking you

samsmuthu

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 12, 2021 11:35 PM

Hi samsmuthu,

As per the data when there are errors while inserting record to the database or records deleted from the table the identity value not resets.

So you see gap between the numbers.

You can refer the below link to avoid such case to reseed the identity value.

https://dba.stackexchange.com/questions/62151/what-could-cause-an-auto-increment-primary-key-to-skip-numbers

https://www.c-sharpcorner.com/blogs/how-to-reset-identity-column-values-in-sql-server1