SQL Server Portal

It is a common practice that whenever you need to deploy a production database, you need to first delete all the test data and reset almost all the identity values in the entire database. In SQL Server 2012 also, you need to do the same but in addition to this you need to reset the SEQUENCE object as well (if utilized).
Lets do it step by step.

Step 1 :
First of all, you need to check whether the SEQUENCE object exits in your database or not and what its current value is.
Given below is the script.

USE AdventureWorks2012
GO
SELECT
name AS [Sequence Name]
,SCHEMA_NAME(schema_id) AS [Scheme Name]
,[start_value] AS [Start Value]
,[current_value] AS [Current Value]
FROM sys.sequences
GO
--OUTPUT

Step 2 :
As you can see, in the above list of sequences, the current value of sequences is not equal to the start value. Lets create the script to reset the sequences.

USE AdventureWorks2012
GO
SELECT
'ALTER SEQUENCE '
+  QUOTENAME(schema_name(schema_id))
+  '.'
+  QUOTENAME(name)
+  ' RESTART WITH '
+  TRY_CONVERT(nvarchar(50),[start_value])
AS [QUERY]
FROM sys.sequences
GO
--OUTPUT

Step 3 :
Once you executed the above query, just copy the result set of the above query and paste it on to a new query window. Make sure you are in a correct database and then execute the result set of above query.

USE AdventureWorks2012
GO
ALTER SEQUENCE [dbo].[Ticket] RESTART WITH 1
ALTER SEQUENCE [dbo].[PurchaseOrderID] RESTART WITH 1
ALTER SEQUENCE [dbo].[SalesOrderID] RESTART WITH 1
ALTER SEQUENCE [dbo].[EmployeeID] RESTART WITH 1
ALTER SEQUENCE [dbo].[ContactID] RESTART WITH 1
GO
--OUTPUT

Step 4 :
Once you execute the above reset statements in a new query window, execute the STEP 1 query again to check whether sequences are reset or not.

USE AdventureWorks2012
GO
SELECT
name AS [Sequence Name]
,SCHEMA_NAME(schema_id) AS [Scheme Name]
,[start_value] AS [Start Value]
,[current_value] AS [Current Value]
FROM sys.sequences
GO
--OUTPUT

Now, you can see that all the sequence objects have been reset.

Read Full Post »