Feeds:
Posts
Comments

Posts Tagged ‘Sequence object’

In my earlier article, I wrote about how to generate a serial number by using the sequence object for a result set . However, sometimes you need to generate a negative serial number for any result set and you can easily achieve it by adjusting few properties in the sequence object.

Let me explain it step by step to demonstrate the solution.

Step 1 :
First of all, you need to create a sequence object along with some specific negative parameters as shown below.

USE AdventureWorks2012
GO
--DROP SEQUENCE dbo.Seq_Sample
--GO
CREATE SEQUENCE dbo.Seq_Sample
   AS int
    START WITH -1
    INCREMENT BY -1
    MINVALUE -3000
    MAXVALUE -1
    CYCLE ;
GO
--OUTPUT

Step 2 :
In this step, you are ready to use the above sequence object to generate a negative serial number as shown below.

USE AdventureWorks2012
GO
SELECT
NEXT VALUE FOR Seq_Sample AS [Sno], Name
FROM sys.all_objects ;
GO
--OUTPUT

Negative serial number - Sequence.1.1

Step 3 :
In this step, you need to reset the sequence number in order to start the sequence number from -1 each time. Given below is the script.

USE AdventureWorks2012
GO
ALTER SEQUENCE dbo.Seq_Sample
RESTART WITH -1 ;
GO
--OUTPUT

Let me know if you came across this situation and how you fixed it.

Read Full Post »

Sequence object is one of the new objects shipped with SQL Server 2012. I wrote multiple articles on its features / solutions in earlier articles. One of the major facts that Sequence object is not a table dependent like IDENTITY columns enables you to inset its value manually in any table. I have been asked in a forum whether we can make Sequence as a default value for a column in a table ? The answer is YES & NOYES because we have a work around to make it happen and NO because it is not implemented by default, like identity in a table.

So what is the work around to make SEQUENCE as a default for a column ?

Lets do it step by step.

Step 1 :
In this step, we need to create a sample table and a sequence object to demonstrate it.

 -- This script is compatibile with SQL Server 2012 and above.
-- CREATE TABLE
USE tempdb
GO
CREATE TABLE dbo.tbl_sample
( [ID] VARCHAR(8) ,
[Product Name] varchar(50)
)
GO

--CREATE SEQUENCE
USE tempdb
GO
CREATE SEQUENCE dbo.Sample_Seq AS
INT START WITH 1
INCREMENT BY 1 ;
GO

Step 2 :
In this step, we need to create a default constraint on the above created table on [ID] column to make SEQUENCE as DEFAULT value for [ID] column.
Given below is the script.

-- This script is compatibile with SQL Server 2012 and above.
-- CREATE DEFAULT VALUE OF SEQUENCE
USE tempdb
GO
ALTER TABLE dbo.tbl_sample
ADD CONSTRAINT Const_Sample_Seq
DEFAULT (NEXT VALUE FOR dbo.Sample_Seq) FOR [ID];
GO

Step 3 :
Lets insert few records into the table.

-- Insert records into the table
USE tempdb
GO
INSERT INTO dbo.tbl_sample ([Product Name])
VALUES ('SQL SERVER 2005'),('SQL SERVER 2008')
,('SQL SERVER 2012'),('SQL SERVER 2014')
GO

Step 4 :
Once the data has been inserted, you can browse the table and view the [ID] column data to check whether SEQUENCE became the DEFAULT value of this column or not.

--Browse Table
USE tempdb
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

sequence as a default.1.1

Read Full Post »

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 »

Sequence object is one of the nice features shipped with SQL Server 2012. It gives much more flexibility than identity column. Due to its flexibility, I usually recommend to use it instead of identity. But recently I came across with a so called issue related to sequence object. The issue is that sequence object does not enforce uniqueness by itself.

Let me create a sample to demonstrate the issue step by step.
Step 1:
Create a sample table. Given below is the script.

USE [AdventureWorks2012]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample Table
CREATE TABLE [dbo].[tbl_sample]
(
[ID] [int] NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Step 2 :
Create a sequence object with CYCLE property.

--Create a sample Sequence object
--DROP SEQUENCE [dbo].[Seq_Sample]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2
CYCLE
GO
--

Step 3 :
Insert few records in the above sample table using sequence object.

USE [AdventureWorks2012]
GO
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-1')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-2')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-3')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-4')
GO

Step 4 :
Browse the table. Given below is the script.

USE [AdventureWorks2012]
GO
SELECT * FROM [tbl_sample]
GO
--OUTPUT

sequence_eniqueness.1.1

As you can see in the above result set, there are duplicate IDs in the ID column. Ooopsss it does not enforce uniqueness by default. So what is the solution ?

SOLUTION :
Let me explain the solution step by step.

Step 1 :
Create a sample table. Given below is the script.

USE [AdventureWorks2012]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample Table
CREATE TABLE [dbo].[tbl_sample]
(
[ID] [int] NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Step 2 :
Create a unique index on column ID of sample table.

USE [AdventureWorks2012]
GO
CREATE UNIQUE INDEX AK_tbl_sample_ID
  ON dbo.tbl_sample (ID)
GO

Step 3 :
Create a sequence object with NO CYCLE property.

USE [AdventureWorks2012]
GO
--Create a sample Sequence object
--DROP SEQUENCE [dbo].[Seq_Sample]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
NO CYCLE
GO
--

Step 4 :
Insert few records in the above sample table using sequence object to check whether the uniqueness is enforced or not.

USE [AdventureWorks2012]
GO
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-1')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-2')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-3')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-4')
GO
--OUTPUT

Step 5 :
Browse the table again. Given below is the script.

USE [AdventureWorks2012]
GO
SELECT * FROM [tbl_sample]
GO
--OUTPUT

sequence_eniqueness.1.2

Conclusion :
Whenever you need to enforce uniqueness in the table using sequence object, make sure you have a UNIQUE INDEX on that particular column of the table. Also make sure that SEQUENCE object has NO CYCLE property.

Read Full Post »

‘How to change the datatype of type Sequence object’ became critical for me when I created a sequence object with a wrong datatype and deployed it on client side and later I tried to change the datatype of the sequence object but I could not 😦

Let me demonstrate the issue and its resolution step by step.

Step 1 :
In this step, we will create a sequence object using smallint data type.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [SMALLINT]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
NO CACHE
GO

Step 2 :
In this step, we will use 3 sequence IDs. This step is just for demonstration purpose only.

Use AdventureWorks2012
GO
SELECT NEXT VALUE FOR [Seq_Sample]
GO 3

Step 3 :
Let’s try to alter its datatype. This step is just to demonstrate the error only.

Use AdventureWorks2012
GO
ALTER SEQUENCE [dbo].[Seq_Sample] AS INT
GO
--OUTPUT

Msg 11711, Level 15, State 1, Line 1
Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.

Ooopsss cannot change it. So, what is the solution ?

Step 4:
In this step, we need to get the current value (last value) of the above sequence object. Never skip this step because once you drop the above sequence object, it would be very difficult to get the current value if it is implemented for multiple tables.
Given below is the script.

Use AdventureWorks2012
GO
SELECT current_value FROM SYS.SEQUENCES
WHERE name='Seq_Sample'
--OUTPUT

current_value
————-
3

(1 row(s) affected)

Step 5:
Once you have the current value of the above sequence, it is time to drop the sequence object.

Use AdventureWorks2012
GO
DROP SEQUENCE [dbo].[Seq_Sample]
GO

Step 6:
Now, it is time to re-create the sequence object.  Make sure that you change the datatype of the sequence object. Also you must change the START value of the new sequence object’s to the current value + 1 (we got the current value in step 3).
Given below is the script.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [INT]
START WITH 4
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
NO CACHE
GO

Step 7:
Finally, check the next value of the sequence.
Given below is the script.

Use AdventureWorks2012
GO
Select NEXT VALUE FOR [Seq_Sample] AS [Result]
GO
--OUTPUT

Result
———–
4

(1 row(s) affected)

Read Full Post »

A couple of month ago, I migrated one of my client’s data from SQL Server earlier versions to SQL Server 2012. The most important part was, I changed all the identity columns of the tables from identity to sequence in the entire database, here is the solution. But another important aspect that I did not discuss is that, whenever you migrate or create a new sequence column make sure that it is not be changeable like identity columns. In the identity column it is implemented by default but in sequence column, you must implement it. But how ?

First of all let me update an identity column of a table to show how identity columns are prevented from being changed (by default). Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
UPDATE HumanResources.Department
SET [DepartmentID]=17
WHERE [DepartmentID]=16
--OUTPUT

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column ‘DepartmentID’.

Solution :

By default, sequence columns are not prevented from being changed, you need to manually control this aspect. Let me show you the solution step by step.

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample_Seq
--GO
CREATE TABLE tbl_sample_Seq
(
[ID] int,
[Name] varchar(50)
)
GO

Step 2 :
Once you created the table, create a sequence object to insert [ID]s in the table.

USE tempdb
GO
--DROP SEQUENCE [dbo].[Seq_Student]
--GO
CREATE SEQUENCE [dbo].[Seq_Student]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
GO

Step 3 :
Once you created the sequence object, insert few records using sequence object in the table. Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Imran')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Bob')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Alexander')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Hosanna')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'William')
GO

Step 4 :
Let me browse the table and show the records that have been inserted successfully.

USE tempdb
GO
SELECT * FROM tbl_sample_Seq
GO
--OUTPUT

prevent sequence1.1

Step 5 :
Lets update the sequence column. Do not DO this step in your production database. This step is just to demonstrate the issue.

USE tempdb
GO
UPDATE tbl_sample_Seq SET [ID]=33 WHERE [ID]=3
GO
--OUTPUT

(1 row(s) affected)

Step 6 :
Ooops….in the above step the [ID] column of the table got modified. Let me create an update trigger on the [ID] column of the table, so that we can prevent sequence values from being changed. This is the most important step while implementing sequence object in a table. Do not skip it.

USE tempdb
GO
CREATE TRIGGER trg_update
ON tbl_sample_Seq
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @OldID int
DECLARE @NewID int

SELECT @OldID =[ID] FROM deleted
SELECT @NewID =[ID] FROM inserted

--Print @OldID
--Print @NewID

IF @OldID <> @NewID
BEGIN
RAISERROR('Failed', 16, 1);
ROLLBACK TRANSACTION
END
END
GO

Step 7 :
Once the update trigger has been created, lets update the sequence column ([ID]) again.

USE tempdb
GO
UPDATE tbl_sample_Seq SET [ID]=33 WHERE [ID]=3
GO
--OUTPUT

Msg 50000, Level 16, State 1, Procedure trg_update, Line 19
Failed
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Conclusion :
Whenever you implement sequence object in any table, make sure you create the trigger to prevent sequence values from being changed.

Let me know if you came across this issue and its solutions.

Read Full Post »

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 »