Generating Sequence with no gap

If you mean at the table level then you need to review the column property IDENTITY - http://msdn.microsoft.com/en-us/library/ms186775.aspx

If you want to create a contiguous list of numbers for a recordset then use ROW_NUMBER - http://msdn.microsoft.com/en-us/library/ms186734.aspx

If the number just needs to be auto generated you can look at setting the column's IDENTITY property. This would allow you to set the starting value and then the increment of that value for each new record.

CREATE TABLE myTable
(
 id int IDENTITY(1000,100)
 city varchar(50)
)

INSERT myTable (city)
VALUES ('Montgomery')
VALUES ('Birmingham')

If you query the above table after inserting one record you should see an ID of 1000 and 1100, then the City set to 'Montgomery' and 'Birmingham'.

Forgive me, I'm not sure that I fully understand the question. Do your tables have an identity column on it? Can you post the DDL for the tables?

Not sure if this applies, but you can use a function such as ROW_NUMBER which would allow you to generate a distinct list of row numbers based off of some partition or order by value.

Row_Number: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Depending on the table structure, you could also use a unique non-clustered index or a primary key on the tables to help prevent duplication of values.

Just throwing it out there. Hope this helps!

I don't remember the code exactly.... giving you only the idea

Create a table with only one column defined as identity column

Create a procedure that inserts a new record in that table

The procedure returns output of the inserted identity

Use that output as the input in the required table in a procedure

To keep the values unique, use APPLICATION LOCK on the procedure

I hope it will give you the basic idea .......