February 7, 2016 at 3:14 am
Q1) I am using follwing tem table in stored proc to loop throught tmpid , will it create any gaps or duplicate on single database? i just want to loop each record.
as temp table will get created and deleted every time so server restart will not affect , if there is any thing else then pls tel me. ( what i need from identity(1,1) is unique sequencal no (with out gaps))
CREATE TABLE #Tmp(
TmpId INT IDENTITY(1,1) NOT NULL,
[SrNo] [int] NULL
)
insert into #tmp (srno) select srno from emp;
February 7, 2016 at 3:56 am
Do you really need to loop? In most cases, it is much more efficient to use a set-based approach.
If you do need to loop, then using a temp table is not the best approach. Using a cursor with the FAST_FORWARD option is less code, less hassle, and performs better (or rather, performs less bad).
To also respond to your question, IDENTITY can leave gaps if inserts were rolled back. It cannot create duplicates unless you explicitly tamper with the values (DBCC CHECKIDENT or SET IDENTITY_INSERT).
February 7, 2016 at 6:40 am
You mention server restarts not impacting the series of numbers. Please understand that the temp table is limited in scope to your current session.
You could just fire your insert like it's written without a loop.
insert into #tmp (srno) select srno from emp;
February 7, 2016 at 9:47 am
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 7, 2016 at 1:13 pm
February 7, 2016 at 1:54 pm
Ed Wagner (2/7/2016)
This was solved over at www.sqlservercentral.com/Forums/Topic1759103-3077-1.aspx.
I get an error navving to the link...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 7, 2016 at 3:30 pm
Orlando Colamatteo (2/7/2016)
Ed Wagner (2/7/2016)
This was solved over at www.sqlservercentral.com/Forums/Topic1759103-3077-1.aspx.I get an error navving to the link...
Trying again: http://www.sqlservercentral.com/Forums/Topic1759103-3077-1.aspx#bm1759164
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply