Resetting a SQL Server 2012 sequence
Try
ALTER SEQUENCE foo.fee
RESTART
Or:
ALTER SEQUENCE foo.fee
RESTART WITH 1
http://msdn.microsoft.com/en-us/library/ff878572.aspx
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.