SQL IDENTITIY Column not starting with 1

Question

I have a temp table in SQL 2014. In this table variable I have an identity column declared.

DECLARE  @TempTable TABLE
(
    ID int IDENTITY(1,1) PRIMARY KEY
    , IncidentResolvedOn date
    , IncidentCreatedOn date
    , IncidentClosedOn date
    , TaskAssigned date
    , TaskCompleted date
    , TaskID float
    , IncidentTeamID int
    , TotalDaysOutstanding int
    , TierInfo varchar(15)
    , Task_NoTask varchar(15)
    , Tier_2_Days_Outstanding int
    , Tier_1_Days_Outstanding int
    , DaysToResolve int
    , BadDays int
    , StartDate date
    , EndDate date
)

When I run the rest of the query the ID column sometimes doesn't start with 1, instead it starts with some random number. The code below is what I use to insert into this table variable.

INSERT INTO @TempTable(IncidentResolvedOn, IncidentCreatedOn, IncidentClosedOn,TaskAssigned, TaskCompleted, TaskID, IncidentTeamID )
SELECT  [Incident Resolved On]
        , [Incident Created On]
        , [Incident Closed On]
        , [Task Assigned On]
        , [Task Completed On]
        , [Task ID]
        , IncidentTeamID

FROM HEATData

This happens in both a table variable and temp table. I've never seen this happen before. Usually when I use the IDENTITY(1,1) phrase it always starts with 1 no matter how many times I create that table. Any suggestions out there?

If you select from your temp table, and order by ID - what do you get for the first value? — Eli, Aug 29 '17 at 19:10
Is it possible that after creating the table, you are inserting rows, deleting them, and then inserting again, and expecting the second insert to start with 1? — Tab Alleman, Aug 29 '17 at 19:12
Sometimes if you run failed insert operations, the sequence goes off, as if the failed inserted id had been used. — Lamar, Aug 29 '17 at 19:12
I am wondering, if this is a variable and the column is an identity, why does it matter whether it's 1 or something else? — JuanR, Aug 29 '17 at 19:13
refer to :[reset identity](https://stackoverflow.com/questions/1280705/set-start-value-for-column-with-autoincrement) — Nolan Shang, Aug 29 '17 at 19:13
Ultimately **this should not matter**. `IDENTITY` = the database controls the inserted value for that column. If a number or range of numbers are skipped it should not matter. If it does matter than you need to fix that dependency and read back the inserted identity value with each insert OR insert your own values and do not be dependent on IDENTITY. — Igor, Aug 29 '17 at 19:16
@Lamar not sometimes, every time. The identity is a running count of the number of attempted inserts. It will ALWAYS increment in an insert even if the insert fails because of constraint violations or whatever. — Sean Lange, Aug 29 '17 at 19:34

score 0 · Answer 1 · answered Aug 29 '17 at 19:43

I imagine your connection is staying open and thus, your identity isn't resetting for your local variable. Here's an example.

DECLARE  @TempTable TABLE
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    ID2 int)
insert into @TempTable
values
(1),(2)

select * from @TempTable

delete from @TempTable

insert into @TempTable
values
(1),(2)

select * from @TempTable

Now, if you'd wrap this in it's own batch using GO you could see this wouldn't happen. In fact, you have to re-declare your table variable.

DECLARE  @TempTable TABLE
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    ID2 int)
insert into @TempTable
values
(1),(2)

select * from @TempTable

go


DECLARE  @TempTable TABLE
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    ID2 int)
insert into @TempTable
values
(1),(2)

select * from @TempTable

This would be the same for a #TempTable as well if you didn't explicitly drop the #TempTable or the connection remained open. Of course, for actual tables the increment will continue similarly to the first examaple.

SQL IDENTITIY Column not starting with 1

1 Answers1