This problem has multiple approaches. If we are using identity columns, there are different approaches to remove the gaps in identity column. If we are using custom sequences, there are different approaches.

When using Identities

Generally By using identities,we can get the sequence numbers. But in some cases, these identity numbers will broke because of any key violations or any tran. rollbacks or we might delete some records in between. There are multiple ways to overcome this problem.

If we use identity fields, these are the following method

General table structure is

Create table Customers(CustomerID int identity(1,1), CustomerName varchar(100),insertedate datetime)

Method 1 : (Peso's method)

If we are not interested about the order of the records, and just you wants to maintain Sequential Ids,

Move last records in place of gaps. Suppose, you inserted 1 to 100 records, when 45 and 50 are deleted,

as there are 2 gaps, take the last 2 records 99 and 100, insert into them middle. You can set identity_insert on

to set the identity of that to intermediate values, then you can re-set identities.

Peter explained this in http://www.sqlteam.com/article/efficiently-reuse-gaps-in-an-identity-column

Method 2:

Maintaining an another table with Ids and when some rows are deleted in between, drop and recreate the Ids table and later join that table with original table. However this method will be useful whenever there are multiple rows deleted and so many gaps are exists.

create table Ids(ID int identity(1,1),CustId int)
create  table Nums(CustID int identity(1,1),CustName varchar(100))

insert into Nums values ('a')
insert into Ids values (SCOPE_IDENTITY())
insert into Nums values ('b')
insert into Ids values (SCOPE_IDENTITY())
insert into Nums values ('c')
insert into Ids values (SCOPE_IDENTITY())

delete from Nums where CustID = 2
delete from Ids where CustId =  2 

truncate table Ids 

insert into Ids 

select CustID  from Nums 
select N.*,I.ID from Nums N 
inner join Ids I on N.CustID = i.CustId

Method 3 :

In this method, we will not be remoing Gaps physically. While we are exposing the data to the users, we will remove the Gaps.

create an Non-Clustered Index on the table with only Id column, write a subquery

to use row_number() method and get the Sequential Nos.

create  table Customers(CustID int identity(1,1),CustName varchar(100))
create index idx_CustId on Customers(CustID)

insert into Customers values ('a')
insert into Customers values ('b')
insert into Customers values ('c')

delete from Customers where CustID = 2

select C.CustID,C.CustName,t.CustID from Customers C 

inner join (select CUstId,ROW_NUMBER() over (Order by CUstID) 
    as rn from Customers) t on C.CustID = t.CustID

Method 4: a Simple row_number() query

Again In this method, we will not be remoing Gaps physically. While we are exposing the data to the users, we will remove the Gaps.

 select *,ROW_NUMBER() over (order by CustID) as rn from Customers

By using Sequences

We can also use custom sequences for this, Where we will be maintaining the last inserted record id in a seperate table.

 create table Customers(CustomerID int,CustomerName varchar(100),Inserteddate datetime)

 create table CustomerSequence(LastCustomeriD int)

or we can also create a stored procedure, which will returns the maximum inserted record id in customer table and add 1 to it and insert it into table.

well, if you go for IDENTITY number, you won't have gaps but at the same time you have mentioned that "We should get the system datetime for each sequence number which they were inserted." so does it mean that you don't want solution of Identity but "DATETIME" value should be used for sequence number. if yes, we can probably do something like this

eg:

10101901

10101902

where first two character belongs to year, second two belongs to month third two character belongs to day and than sequence number like 01, 02 etc. we will need one SP or function which will return latest number when we try to INSERT records. I can provide you with this kind of function or SP but I am still not sure whether you want this only or something else.

Answer:

Use tempdb
go

drop table tbl_Sequence
go

---- Create the table to maintain Sequence, this to provide high concurrency
create table tbl_Sequence (
    SeqID int identity(1,1) primary key ,
    SeqVal varchar(1)  
)

---- Create Store Procedure to Generate the Sequence
create procedure GetNewSeqVal_tbl_Sequence
as
begin
    declare @NewSeqValue int
    set NOCOUNT ON
    insert into tbl_Sequence  (SeqVal) values ('a')
    set @NewSeqValue = scope_identity()
    delete from tbl_Sequence WITH (READPAST)
return @NewSeqValue
end

---- Create the actual table, where the sequence no will be used
Create table tbl_Invoice
( 
    InvNo int primary key,
    InsDate Bigint
)

-- Declare the temp variabls
Declare @NewSeqVal int
Exec @NewSeqVal =  GetNewSeqVal_tbl_Sequence
Declare @InsertDt bigint
select @InsertDt = cast(convert( char (8), getdate(),112) 
    	+ substring( convert( char (15), getdate(),113), 13, 2) 
    	+ substring( convert( char (18), getdate(),113), 16, 2)as bigint)

---- Populate the records
insert into tbl_Invoice values(@NewSeqVal,@InsertDt)

--- Verify the result
select * from tbl_Invoice


---- Result
InvNo    InsDate

1              201010201351

2              201010201352

3              201010201352

4              201010201352

5              201010201352

6              201010201352

7              201010201352

8              201010201352

9              201010201352

10           201010201352

11           201010201352

12           201010201352

13           201010201352

14           201010201352

15           201010201352

16           201010201352

Reference: SQL Server Sequence Number

I would simply create a trigger to trig after insertion and update that datetime field with current date.

Did I miss the point of the question?

I think using identity can cause gaps if transactions are rolled back. In my opinion, we should create a seed type table that maintains the sequence number. The update to the core table should be followed by output clause in the seed table. To get the system datetime in the core table, just use default constraint with Getdate(), is the question more complicated ?

Here is an example to make it clear :

CREATE TABLE Seed ( CoreTableSeed INT PRIMARY KEY NOT NULL )
go
CREATE TABLE CoreTable ( CoreTableKey INT PRIMARY KEY NOT NULL,
                      AutoDate DATETIME DEFAULT GETDATE()
                    )                   
go  
INSERT INTO Seed (CoreTableSeed) VALUES (0) -- one time initilization code.
go  
UPDATE Seed
  SET CoreTableSeed = CoreTableSeed + 1
  OUTPUT INSERTED.CoreTableSeed INTO  CoreTable(CoreTableKey)

go

SELECT * FROM  Seed
go
SELECT * FROM  CoreTable

Oh right, the rollback side effect...

With no identity column and an instead of trigger one could manage it.

CREATE TRIGGER...

 INSERT INTO MyTable (id, date, v1, v2 ,v3)
 SELECT
  M + ROW_NUMBER() OVER (ORDER BY (SELECT 1))
 , GETDATE()
 , I.v1
 , I.v2
 , I.v3

FROM inserted AS I
CROSS APPLY (
  SELECT MAX(id) FROM MyTable
) EXT(M)

If deletion were to be handled as well, an after delete trigger should be created as well. And in order to prevent manual tempering with id & date an update instead trigger could be used (were no effective update of id & date would happen).

Answer:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sequences](
    [Sequence] [int] NOT NULL,
    [Sequence_date] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [Sequence] 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
ALTER TABLE [dbo].[Sequences] ADD  DEFAULT (getdate()) FOR [Sequence_date]
GO

In order to populate this table, we can use another table, and use the following UPDATE COMMAND to populate Sequences table

CREATE TABLE Update_Sequences
(LastNumber INT PRIMARY KEY,
UpdateDate DATETIME DEFAULT GETDATE())
GO
INSERT INTO Update_Sequences 
(LastNumber) 
OUTPUT Inserted.LastNumber, GETDATE() INTO Sequences 
VALUES(1)
DECLARE @i INT = 0
WHILE @i<10000
BEGIN 
    UPDATE Update_Sequences 
    SET LastNumber = COALESCE(LastNumber,0) + 1
    OUTPUT Inserted.LastNumber, GETDATE() INTO Sequences
    SET @i = @i + 1
END

We can verify, that there are no gaps in the table

SELECT * FROM Sequences

Sequence table populated this way will never have gaps.

Interestingly, the new version of SQL Server, code-named Denali, will have Sequences tables built in.

Take a look at this blog post A first look at sequences in SQL Server Denali and also this very interesting blog by Aaron Bertrand Sequences tables in Denali

There was a question very similar to this earlier in the quiz. I would make the same recommendation: use a surrogate table and composable DML. Something similar to the below should work:

use tempdb
go

set nocount on
go

-- clear the objects if they exist
if object_id(N'dbo.Surrogate') is not null drop table dbo.Surrogate
if object_id(N'dbo.Usage') is not null drop table dbo.Usage
go

-- create a surrogate table
create table dbo.Surrogate
 (
   ObjectName sysname not null constraint pk_Surrogate primary key clustered,
   NextKey bigint not null constraint ak_Surrogate unique nonclustered,
   constraint ck1_Surrogate check (NextKey>=0)
 )
go

-- create a usage table
create table dbo.Usage
 (
   Surrogate bigint not null constraint pk_Usage primary key clustered,
   SomeData nvarchar(100) not null,
   InsertTime datetime2(3) not null
 )
go

-- seed the surrogate table
insert dbo.Surrogate
 (
   ObjectName,
   NextKey
 )
values
 (
   N'dbo.Usage',
   0
 )
go

-- declare a variable to loop for example only
declare @i bigint; select @i=1

while @i<=10 begin

   -- insert the data and use the output clause
   -- to get the next key from our surrogate table
   update
      dbo.Surrogate
   set
      NextKey+=1  -- or NextKey=NextKey+1
   output
      inserted.NextKey,
      ('random data: '+convert(nvarchar(10),inserted.NextKey)),
      getutcdate()
   into dbo.Usage
    (
      Surrogate,
      SomeData,
      InsertTime
    )
   where
      ObjectName=N'dbo.Usage'

   -- wait for a moment and move to the next rec
   waitfor delay '00:00:00.01'
   select @i+=1

end
go

-- return the results
select * from dbo.Usage
go

As a simple follow up to my last post, the below statements are written to actually throw an error. So you can see, the 'update' statement is not commited should there be an issue with the 'output' / 'into' clause, i.e. the next key (prior to the +1) in the surrogate table is still 9. In turn, we can ensure there is no gap in the sequence...

use tempdb
go

set nocount on
go

-- clear the objects if they exist
if object_id(N'dbo.Surrogate') is not null drop table dbo.Surrogate
if object_id(N'dbo.Usage') is not null drop table dbo.Usage
go

-- create a surrogate table
create table dbo.Surrogate
 (
   ObjectName sysname not null constraint pk_Surrogate primary key clustered,
   NextKey bigint not null constraint ak_Surrogate unique nonclustered,
   constraint ck1_Surrogate check (NextKey>=0)
 )
go

-- create a usage table
create table dbo.Usage
 (
   Surrogate bigint not null constraint pk_Usage primary key clustered,
   SomeData nvarchar(100) not null,
   InsertTime datetime2(3) not null
 )
go

-- seed the surrogate table
insert dbo.Surrogate
 (
   ObjectName,
   NextKey
 )
values
 (
   N'dbo.Usage',
   0
 )
go

-- declare a variable to loop for example only
declare @i bigint; select @i=1

while @i<=10 begin

   -- insert the data and use the output clause
   -- to get the next key from our surrogate table
   update
      dbo.Surrogate
   set
      NextKey+=1  -- or NextKey=NextKey+1
   output
      inserted.NextKey,
      case when @i=10 then null else ('some data: '+convert(nvarchar(10),inserted.NextKey)) end,
      getutcdate()
   into dbo.Usage
    (
      Surrogate,
      SomeData,
      InsertTime
    )
   where
      ObjectName=N'dbo.Usage'

   -- wait for a moment and move to the next rec
   waitfor delay '00:00:00.01'
   select @i+=1

end
go

-- return the results
select * from dbo.Usage
select * from dbo.Surrogate
go

I think following answer from my earlier question should do it for this question.

--Prepare data environment
CREATE TABLE Sequences
(  
   NextInvNum bigint,
    NextCustId  bigint
);
GO
CREATE TABLE Invoices
(  
   InvNum bigint,
    InvDate date
);
GO
INSERT INTO Sequences ( NextInvNum ) 
   VALUES ( 1193955 )
-- The current stored procedure is something like this:
-- Simplified schema, no error handling
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET XACT_ABORT ON
DECLARE @NextInv bigint
BEGIN TRANSACTION
   SELECT @NextInv = ( NextInvNum + 1 )
   FROM Sequences

   UPDATE Sequences
      SET NextInvNum = ( NextInvNum + 1 )

   INSERT INTO Invoices
      (  
        InvNum,
         InvDate
      )
      VALUES
        (  
           @NextInv,
           GETDATE()
        )
COMMIT TRANSACTION

UPDATE Sequences
SET NextInvNum = ( NextInvNum + 1 )
OUTPUT Inserted.NextInvNum, GETDATE() INTO Invoices

SELECT *
FROM Invoices
SELECT *
FROM Sequences