Lets say you have an employee table definition:
CREATE TABLE [dbo].[Employee](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[DateOfBirth] [datetime] NULL,
[SequenceNumber] [int] NOT NULL,
[DateTimeStamp] [datetime] NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[Id] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [DF_Employee_DateTimeStamp] DEFAULT (getdate()) FOR [DateTimeStamp]
GO
The ID is set to primary key and auto increment. The SequenceNumber column is the one we are interested in. It will be kept in sequence as your requirements are. DateTimeStamp is also set to current datetime when a new record is added - just to keep the logs.
Now we have a stored procedure that will add a new employee record:
CREATE PROCEDURE spAddNewEmployee
@FirstName AS VARCHAR(50),
@LASTNAME AS VARCHAR(50),
@DATEOFBIRTH AS DATETIME
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @LAST_SEQUENCE_NUMBER AS INT;
SELECT @LAST_SEQUENCE_NUMBER = MAX([SequenceNumber]) FROM dbo.Employee
IF(@LAST_SEQUENCE_NUMBER IS NULL)
BEGIN
SET @LAST_SEQUENCE_NUMBER = 1;
END
ELSE
BEGIN
SET @LAST_SEQUENCE_NUMBER = @LAST_SEQUENCE_NUMBER + 1
END
INSERT INTO dbo.Employee
(
[FirstName]
,[LastName]
,[DateOfBirth]
,[SequenceNumber]
)
VALUES
(
@FirstName,
@LastName,
@DateOfBirth,
@LAST_SEQUENCE_NUMBER
)
END
GO
It will retrieve the last SequenceNumber value if its not null (which will be when first record will be added) it will add 1 to it. Next the record will be added along with the auto field DateTimeStamp.
So far so good. Now lets get to the Delete employee part, here is the Delete Employee stored proc:
CREATE PROCEDURE [dbo].[spDeleteEmployee]
@EmployeeId AS INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @DELETED_SEQUENCE_NUMBER AS INT, @LAST_SEQUENCE_NUMBER AS INT;
SELECT @LAST_SEQUENCE_NUMBER = MAX([SequenceNumber]) FROM dbo.Employee
SELECT @DELETED_SEQUENCE_NUMBER = [SequenceNumber] FROM dbo.Employee WHERE ID = @EMPLOYEEID
DELETE FROM dbo.Employee
WHERE ID = @EMPLOYEEID
WHILE(@DELETED_SEQUENCE_NUMBER <= @LAST_SEQUENCE_NUMBER)
BEGIN
UPDATE dbo.Employee
SET SequenceNumber = @DELETED_SEQUENCE_NUMBER
WHERE SequenceNumber = @DELETED_SEQUENCE_NUMBER + 1
SET @DELETED_SEQUENCE_NUMBER = @DELETED_SEQUENCE_NUMBER + 1
END
END
GO
To keep the SequenceNumber constant incremental first we are getting the last sequence number and the one which is being deleted. Then after deleting the record, we are looping through each record that comes after the deleted one and aligning the SequenceNumber until we reach to the end of the records i.e. while loop condition.
To make it better, you can use of transaction to encapsulate the whole operation.
EDIT: You can also make SequenceNumber column unique:
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT UQ_SequenceNumber UNIQUE ([SequenceNumber])
Hope this answers your question.
