Feeds:
Posts
Comments

Posts Tagged ‘sys.sequences’

SQL SERVER 2012 – How to check Sequence has reached its minimum or maximum value

Posted in Solutions, SQL SERVER, tagged , , , , , , on October 5, 2013| Leave a Comment »

Recently I came across an issue related to the sequence object (shipped in SQL Server 2012) because usually we generate the next ID from the sequence object without checking whether it has reached its limit and it cannot generate a new ID. Due to this it generates an error as well. Given below are the error details.

Msg 11728, Level 16, State 1, Line 1
The sequence object ‘Seq_Sample1’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. Sequence object maintain its minimum and maximum value by default.

So what is the solution ? But before proceeding with the solution let me create a sample.

SAMPLE :

--This script is compatible with SQL Server 2012 and above.
--Create a sequence object
USE [AdventureWorks2012]
GO
--DROP SEQUENCE [dbo].[Seq_Sample1]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample1]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
GO
--Generate sequence number up to 4 ID
--because it has a maximum limit of 4.
SELECT Next value for [dbo].[Seq_Sample1]
GO 4

SOLUTION 1 : (Not recommended)
In this solution, you need to check the minimum and maximum value of the sequence with the current value and if it matches either of them, it means that sequence has been reached to its minimum or value and you cannot generate a new ID. It is not recommended because it is a lengthy procedure. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @minimum_value As sql_variant
DECLARE @maximum_value As sql_variant
DECLARE @current_value As sql_variant

SELECT
@minimum_value=[minimum_value]
,@maximum_value=[maximum_value]
,@current_value=[current_value]
FROM
sys.sequences AS seq
WHERE name ='Seq_Sample1'

SELECT IIF(@minimum_value=@current_value
OR @maximum_value=@current_value
,'You cannot generate a new ID'
,'You can generate a new ID') AS [Result]
--OUTPUT

sequence limit.1.1

SOLUTION 2 : Using Is_exhausted property (Recommended)
In this solution, we will use a sequence property namely “is_exhausted” and can quickly check whether it reached its limit or not.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT IIF((SELECT is_exhausted FROM sys.sequences AS seq
WHERE name ='Seq_Sample1')=1
,'You cannot generate a new ID'
,'You can generate a new ID')
AS [Result]
--OUTPUT

sequence limit.1.1

CONCLUSION :
As you can see, both solutions generate the same output but solution 2 is recommended because we used a property namely “is_exhausted” and reduced the size of the script.

Read Full Post »

SQL SERVER – Resetting sequence values for entire database

Posted in Solutions, SQL SERVER, tagged , , , , , , , on May 24, 2013| Leave a Comment »

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

resetsequence1.2

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

resetsequence1.1

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

resetsequence1.3

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

resetsequence1.4

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

Read Full Post »

SQL Server 2012 – List of all sequences available in the Database

Posted in Solutions, SQL SERVER, tagged , , , , , , on October 24, 2012| Leave a Comment »

SQL Server 2012 came up with multiple good features. One of them is sequence. I have discussed this feature in my earlier artcile.

In this article, we will discuss how to find list of all sequences along with  its current value & other different properties in the database. So, we can achieve this with the help of system view namely sys.sequences.

Given below are the list of properties, we need to find in all sequences in the database.

  • Sequence Name
  • object ID
  • Creation Date
  • Last Modified Date
  • Schema
  • Numeric Precision
  • Numeric Scale
  • Start Value
  • Increment Valye
  • Min Value
  • Max Value
  • Is Cycle Enabled
  • Sequence Cache Type
  • Cache Size
  • Current Value
SELECT
seq.name AS [Sequence Name],
seq.object_id AS [Object ID],
seq.create_date AS [Creation Date],
seq.modify_date AS [Last Modified Date],
SCHEMA_NAME(seq.schema_id) AS [Schema],
CAST(seq.precision AS int) AS [Numeric Precision],
CAST(seq.scale AS int) AS [Numeric Scale],
ISNULL(seq.start_value,N'''') AS [Start Value],
ISNULL(seq.increment,N'''') AS [Increment Value],
ISNULL(seq.minimum_value,N'''') AS [Min Value],
ISNULL(seq.maximum_value,N'''') AS [Max Value],
CAST(seq.is_cycling AS bit) AS [Is Cycle Enabled],
ISNULL(seq.cache_size,0) AS [Cache Size],
ISNULL(seq.current_value,N'''') AS [Current Value]
FROM
sys.sequences AS seq

Read Full Post »