Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I should note that this is a SQL 2012 SP 1 Enterprise installation. –  Techie Joe Feb 19 '13 at 15:11
add comment

2 Answers 2

up vote 3 down vote accepted

Try

ALTER SEQUENCE foo.fee
RESTART

Or:

ALTER SEQUENCE foo.fee
RESTART WITH 1

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

share|improve this answer
2  
As a workaround? Shouldn't be needed since the SEQUENCE is being dropped and recreated, right? –  Paul White Feb 19 '13 at 4:20
1  
@PaulWhite I would say you shouldn't need to drop and recreate it. Dropping an entire schema just to reset a sequence is a bit severe, and that's my reading what he's doing. I'd be interested in having him try to stop and restart the server after dropping and recreating the sequence and schema to see if that causes the sequence to reset, though. I'm also curious if he's got SP1 applied. –  Bacon Bits Feb 19 '13 at 4:32
    
I see, thank you. –  Paul White Feb 19 '13 at 4:44
    
@BaconBits I do have SP1 applied on an Enterprise server. –  Techie Joe Feb 19 '13 at 15:15
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.