Feeds:
Posts
Comments

Posts Tagged ‘Sequence in SQL Server 2012’

SQL SERVER 2012 – Generating serial numbers for a result set by using sequence object

Posted in Solutions, SQL SERVER, tagged , , , , , on December 29, 2013| 3 Comments »

Generating Serial number is something that is very usual for a SQL Server developer, either it is for report or any result set. I would not be wrong if I said that it is something that we need to use every now and then. I usually use ROW_NUMBER to generate the serial number in most of the places unless otherwise any compatibility issues. However, I wanted to generate it in new ways and recently, I found a new method to generate serial number while playing with Sequence Object.
Given below are scripts to generate a serial number using ROW_NUMBER & SEQUENCE Object.

  • Using ROW_NUMBER
--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
ROW_NUMBER() OVER(ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--OUTPUT

serial number using sequence.1.1

  • Using SEQUENCE Object 
--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--DROP SEQUENCE dbo.Seq_Sample
--GO
--Create Sequence Object
CREATE SEQUENCE dbo.Seq_Sample
AS int     START WITH 1
INCREMENT BY 1 ;
GO
--Generate serial number using Sequence object
SELECT
NEXT VALUE FOR dbo.Seq_Sample OVER (ORDER BY Name) AS [S.No]
, ProductID
, Name
, ProductNumber
FROM Production.Product
GO
--Reset Sequence object to 1, So it will start from 1
--in the next result set.
ALTER SEQUENCE [dbo].Seq_Sample RESTART WITH 1
GO
--OUTPUT

serial number using sequence.1.1

Read Full Post »

SQL SERVER 2012 – How to check last modified date of Sequence Object

Posted in Solutions, SQL SERVER, tagged , , , , , on December 1, 2013| Leave a Comment »

I deployed some Sequence objects on production database and after couple of days, I was facing some problem with Sequence objects. It was little difficult to debug all sequence objects at once, so I thought of checking the sequence objects modified dates to find if I modified any Sequence object after deployment.

Given below are the two methods to find the modified dates of Sequence Object.

METHOD 1 : Using sys.sequence
Given below is the script to find the modified date of all sequence object using sys.sequences

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.sequences
GO
--OUTPUT

Sequence modified dates

METHOD 2 : Using sys.objects
Given below is the script to find the modified date of all sequence object using sys.objects

USE AdventureWorks2012
GO
SELECT
name As [Sequence Name]
,modify_date
FROM sys.objects
WHERE [type] ='SO'
GO
--OUTPUT

Sequence modified dates

Read Full Post »

SQL SERVER 2012 – How to find if a Sequence exist in database

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

I came across this query many times whenever I was working on SEQUENCE object (A new object introduced in SQL Server 2012). Because whenever you need to use any sequence object in any piece of code, make sure it exists.

Given below are the two methods, you can use to check the existence of the sequence object.

METHOD 1 :
This is the preferred method because, it will not only check the existence of the sequence object but also it will give you the details about the sequence object.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--Do not forget to change you sequence object name
--in the below query.
SELECT * FROM sys.sequences
WHERE object_id=object_id('Seq_sample')
GO
--OUTPUT

existance of sequence object.1.1

METHOD 2 :
This is the same method we have been using since the earlier version of SQL Server. In this method, we use one of the renowned systems
object catalog view namely sys.objects. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
--Do not forget to change you sequence object name
--in the below query.
SELECT * FROM sys.objects
WHERE object_id=object_id('Seq_sample')
AND [TYPE]='SO'
--OUTPUT

existance of sequence object.1.2

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 »