Wednesday, February 27, 2013 10:07 PM
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),
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
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
Wednesday, February 27, 2013 10:10 PM
Hmm, odd. Does the problem also occur if you use a regular table instead of a temporary table?
Thursday, February 28, 2013 1:14 AMThank 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
SUM(pre_something) AS pre_something_tot
SUM(pst_something) AS pst_something_tot
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
Thursday, February 28, 2013 3:58 AM
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
Thursday, February 28, 2013 4:59 AMCan 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.
Aalam | Blog (http://aalamrangi.wordpress.com)
Wednesday, March 06, 2013 6:45 PMModerator
>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
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Wednesday, March 06, 2013 6:45 PM
- Edited by Kalman TothMicrosoft Community Contributor, Moderator Wednesday, March 06, 2013 6:46 PM
- Proposed As Answer by Chuck Pedretti Wednesday, March 06, 2013 6:49 PM
- Marked As Answer by Iric WenModerator Thursday, March 07, 2013 9:03 AM
Wednesday, March 06, 2013 6:59 PMModerator
IDENTITY fields are guaranteed to be UNIQUE, not sequential. Whatever you think you are doing, you need to rethink.