Resetting a SQL Server 2012 sequence 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 &amp; 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.
Try

ALTER SEQUENCE foo.fee
RESTART


Or:

ALTER SEQUENCE foo.fee
RESTART WITH 1


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