Identity column does not start at 1 in an SP

Question

Using SQL Server 2008 R2 Enterprise, I have a stored procedure that builds a # table for output. I create the temp table here

CREATE TABLE #TN_CR_OUT(
 ROW_ID   int identity(1,1),
 CREATE_DATE  datetime,
 PRE    decimal(15,2),
 PST    decimal(15,2),
 PERF  decimal(15,2) default 0
 )

Now I do an INSERT INTO the table from another table like this

 INSERT INTO #TN_CR_OUT(CREATE_DATE,PRE,PST)
 SELECT CREATE_DATE, SUM(PRECount) AS PRE, SUM(POSTCount) AS PST
 FROM #TN_CR
 WHERE IncludeInRpt=1
 GROUP BY CREATE_DATE
 ORDER BY CREATE_DATE

This inputs 30 rows into the table. Now, If I run this in a query window the ROW_ID starts at 1 and goes to 30. I can drop the table and recreate it and still it starts at 1. However, if I run this in a stored procedure, sometimes the ROW_ID starts at 17 everytime I run it, sometimes at 15 everytime I run it - just not 1. I need this to start at 1 or a consistent value because the output is split into 2 tables for data visualization. I would use the CREATE_DATE BUT since it is rolling, it is not my best option. I could also seed the column by a separate query but the identity should take care of this. Any ideas would be helpful. - Thanks

Answer 1

Hmm, odd. Does the problem also occur if you use a regular table instead of a temporary table?

---

Gert-Jan

Answer 2

Thank you for the DDL. It tells us what you are doing wrong. Have you ever considered the ISO-11179 Standards for table names, etc.? Why do you think that your choices are clear and helpful to anyone maintaining this code? No, they look like FORTRAN and BASIC names that we would have used in magnetic tape files. 

A procedure should never build a fake magnetic scratch tape, but since you did not post the procedure, we cannot see what you did wrong. Oh, RDBMS people do ever use the physical insertion attempt count (aka IDENTITY) in a schema. Your totally non-RDBMS magnetic tape record number! 

Why did data element names change in you hidden schema?? 

Is “include_in_pt” an assembly language BIT flag in SQL?! NO!!!

CREATE TABLE #Local_Scratch_Tape_2
(creation_date DATE NOT NULL,
 pre_something_tot DECIMAL(15,2) NOT NULL,
 pst_something_tot DECIMAL(15,2) NOT NULL,
 perf_something_amt DECIMAL(15,2) DEFAULT 0.00 NOT NULL);

>> Now I do an INSERT INTO the table from another table like this

 INSERT INTO Local_Scratch_tape
 SELECT creation_date,
        SUM(pre_something) AS pre_something_tot
        SUM(pst_something) AS pst_something_tot
 FROM #Local_Scratch_Tape_1
 WHERE non_relational_inclusion_flg = 1 --– assembler lives!
 GROUP BY creation_date; <<-

Why did you use an ORDER BY on an insertion? Let me answer that: magnetic tapes are sorted, but tables are not! You are writing mag tape programs in SQL! 

>> If I run this in a query window the ROW_ID [sic: physical mag tape record number!] starts at 1 and goes to 30. I can drop the table and recreate it and still it starts at 1. However, if I run this in a stored procedure, sometimes the ROW_ID [sic] starts at 17 every time I run it, sometimes at 15 every time I run it - just not 1. <<

That is because you did not DISMOUNT the old imaginary tape from the imaginary tape drive, and MOUNT a new one. 

You are being told by someone who has credentials in SQL and RDBMS that everything you are doing is fundamentally wrong; not a little wrong,fundamentally wrong.  Please consider this. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 3

Hi Art,

You can also truncate the table before starting to insert records from scratch, to ensure that its Row_id starts from 1.

---

Many Thanks & Best Regards, Hua Min

Answer 4

Can you share your stored procedure code? Maybe that will give us a clue.

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 5

>I need this to start at 1 or a consistent value because the output is split into 2 tables for data visualization.

You should not rely on IDENTITY for anything other than a meaningless number ideal for surrogate primary key. When a transaction rolled back, identity not thus creating a gap. There is also a 1000 jump bug which popped up recently.

If you need accurate sequencing, use the ROW_NUMBER() based on datetime chronological order:

SELECT	RN=ROW_NUMBER() OVER( ORDER BY OrderDate, SalesOrderID), 
		OrderDate, SalesOrderID
FROM AdventureWorks2012.Sales.SalesOrderHeader
ORDER BY RN;
/*
RN	OrderDate	SalesOrderID
1	2005-07-01 00:00:00.000	43659
2	2005-07-01 00:00:00.000	43660
3	2005-07-01 00:00:00.000	43661
4	2005-07-01 00:00:00.000	43662
5	2005-07-01 00:00:00.000	43663
6	2005-07-01 00:00:00.000	43664
7	2005-07-01 00:00:00.000	43665
....*/

 

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Windows Azure SQL Database Programming & Design

Answer 6

 

IDENTITY fields are guaranteed to be UNIQUE, not sequential.  Whatever you think you are doing, you need to rethink.