I'm in the process of testing and populating a specific table that leverages the
SEQUENCE object. In this process I'm testing populating the table with tens of thousands of insert lines (as I'm unfamiliar with how to program this). The problem I'm seeing with this specific table is that when I start another population test, the
SEQUENCE does not reset back to the first number I want (which is 1).
When I wish to re-run a new test I delete the table in question then run the following:
DROP SEQUENCE foo.fee; GO DROP SCHEMA foo; GO
When I want to re-run the test I run the following
SEQUENCE commands, which are fired in the order below:
CREATE SCHEMA foo; GO CREATE SEQUENCE foo.fee START WITH 1 INCREMENT BY 1 NO CYCLE NO CACHE; GO
I then create the table:
CREATE TABLE foo.sample_table_with_data (order_number bigint PRIMARY KEY NOT NULL, sample_column_one nvarchar(max) NULL, sample_column_two nvarchar(max) NULL, sample_column_three nvarchar(max) NULL) GO
Once that is completed I run the following insert command 50,000 times:
INSERT INTO [foo].[sample_table_with_data] ( [order_number], [sample_column_one], [sample_column_two], [sample_column_three] ) VALUES ( NEXT VALUE FOR foo.fee, 'Blah', 'Blah Blah', 'Blah Blah Blah' )
Now there is absolutely no problem with the data entering into the table. The challenge I'm encountering is that when I delete the table, drop the schema and sequence then re-create the table, sequence, and schema the
SEQUENCE picks up from the last number in the previous database incarnation and not reset back to one.
For example, if the last number in the sequence is say 634,534 the next sequence number in the new table is 634,535.
After deleting the table and dropping the schema and sequence I run the following to verify removal of the sequence and schema:
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA GO SELECT * FROM sys.sequences GO
I'm stumped as to why this is happening. Is there another command that I'm missing here that would help me localize what exactly is going on here?
I should note that this table belongs to a database with 7 other tables all running the
SEQUENCE command correctly.
This is a SQL 2012 SP1 Enterprise Edition installation.