Resetting a SQL Server 2012 sequence

Question

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 SCHEMA & 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.

Answer 1

Try

ALTER SEQUENCE foo.fee
RESTART

Or:

ALTER SEQUENCE foo.fee
RESTART WITH 1

http://msdn.microsoft.com/en-us/library/ff878572.aspx

Answer 2

Using your script with a few slight changes:

CREATE SCHEMA foo;
GO
CREATE SEQUENCE foo.fee
START WITH 1
INCREMENT BY 1
NO CYCLE
NO CACHE;
GO
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
SET NOCOUNT ON
GO
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')
GO 50000
SELECT
 MIN(order_number), 
 MAX(order_number)
FROM foo.sample_table_with_data AS stwd
GO
DROP SEQUENCE foo.fee;
GO
DROP TABLE foo.sample_table_with_data
GO
DROP SCHEMA foo;

...I cannot reproduce the issue on SQL Server 2012 SP1 (build 3000) or above.

I can't find a Connect item or KB article mentioning this particular scenario either (and there have been plenty of other SEQUENCE issues). That's not to say it didn't exist pre-SP1 because not all fixes end up being documented.