If you want to ensure no duplicates enter the table then you should declare your IDENTITY column to be unique either as the primary key or with a unique index or constraint.

This:

CREATE TABLE #Tmp

(

TmpId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

SrNo INT NULL

);

Or:

CREATE TABLE #Tmp

(

TmpId INT IDENTITY(1,1) NOT NULL UNIQUE,

SrNo INT NULL

);

If you truly need to prevent gaps then I would suggest staying away from IDENTITY since there is no guarantee. You can manage it with DDL commands (see Hugo's reference) but those are (in my opinion) more difficult solutions than simply managing your own sequence transactionally to ensure no gaps are introduced.