Feeds:
Posts
Comments

Archive for the ‘Sequence Object’ Category

Today, I received a query from one of my blog readers asking how to get the current value from a sequence ? He also mentioned to me that he usually uses given below (Database console command) script to get the current value from identity column.

 DBCC CHECKIDENT ('Table Name', NORESEED)

So, do we have any DBCC command like this to get the current value from sequence object? Basically we do not have any DBCC command to get the current value from sequence same like identity but we do have work around to find it. Given below are the two methods to get the current value from any sequence object.

METHOD 1 :
In this method, you need to open SQL Server Management Studio and select the particular database and further select the sequence object in which current value is required. Then right click on it and browse its property to view the current value as shown in the image below.

sequence current value.1.1

sequence current value.1.2

METHOD 2 :
In this method, you need to open a NEW query window in SSMS and write the given below script to get the current value of sequence using sys.sequences  (A new system view shipped in SQL Server 2012).

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
SELECT current_value FROM sys.sequences
WHERE [name]='Seq_sample'
--OUTPUT

sequence current value.1.3

Advertisements

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 »