SQL Server Mentalist

Home | Pages | Archives

BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number –All Explanation !!

August 30, 2011 10:29 am

Continuing from my last post where u have explained Denali Feture – FileTable :

BISQL # 49 : SQL Server Denali Feature # 1–FileTables #1 – Introduction & Theory

BISQL # 50 : SQL Server Denali Feature # 1- FileTables # 2 – Demo and Scripting Through !!

Today we are looking into sequence number concept

Introduction to sequence number

Some of the feature of sequence number :

• Sequence number are customized generated number by custom syntax programmatically.
• The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value.
• The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.
• A sequence is created independently of the tables by using the CREATE SEQUENCE statement. Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance. For information about the options, see CREATE SEQUENCE.
• Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function.
• The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table. The NEXT VALUE FOR function can be used as the default value for a column in a table definition.
• Use sp_sequence_get_range to get a range of multiple sequence numbers at once.
• A sequence can be defined as any integer data type.
• If the data type is not specified, a sequence defaults to bigint.

Why Should I Used it if I have Identify property with me ?

1. The application requires a number before the insert into the table is made.
2. The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
3. The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
4. The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause’s ORDER BY clause.
5. An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers.
6. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers.
7. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
8. You need to change the specification of the sequence, such as the increment value.

What are the Various components od Sequence Number

How I am going to Create Sequence ?

• Identify the schema where you want to create the sequence
• Decide the name of the sequence
• The sequence type can have any possible built-in integer types.
• For example in type we can have tinyint, smallint, int, bigint, decimal or numeric; decimal or numeric require a scale of 0
• Ii User Defined type where built in integer value inside1.
• The default sequence type is INT
• Use START WITH <integer constant> to specify the first sequence number to be assigned; this can be a negative number
• INCREMENT BY <integer constant> determines the next value assigned by the sequence number; this can be a positive or negative value but not 0
• MINVALUE <integer constant> and MAXVALUE <integer constant> provide the bounds for the sequence number; the default for MINVALUE is 0 for a tinyint and the smallest negative number for the type of the sequence number; the default for MAXVALUE is the maximum value for the type of the sequence number
• Specify CYCLE to restart the sequence number at the MINVALUE after the MAXVALUE is reached
• Specify NO CYCLE to throw an exception after the MAXVALUE is reached rather than restarting with the MINVALUE
• Use CACHE <integer constant> to retrieve a sequential block of sequence numbers
• Use the NEXT VALUE FOR statement to assign and retrieve the next value for a sequence number

What are the various Syntax for  Sequence

Create Sequence Syntax

CREATE SEQUENCE [schema_name . ] sequence_name
        [ <sequence_property_assignment> [ ,…n ] ]
    [ ; ]
<sequence_property_assignment>::=
{
    [ AS { built_in_integer_type | user-defined_integer_type } ]
    | START WITH <constant> 
        | INCREMENT BY <constant> 
        | { MINVALUE <constant> | NO MINVALUE }
        | { MAXVALUE <constant> | NO MAXVALUE }
        | { CYCLE | NO CYCLE }
        | { CACHE [<constant> ] | NO CACHE }
}

Alter Sequence Syntax

ALTER SEQUENCE [schema_name. ] sequence_name
    [ RESTART [ WITH <constant> ] ]
    [ INCREMENT BY <constant> ]
    [ { MINVALUE <constant> } | { NO MINVALUE } ]
    [ { MAXVALUE <constant> } | { NO MAXVALUE } ]
    [ CYCLE | { NO CYCLE } ]
    [ { CACHE [ <constant> ] } | { NO CACHE } ]
    [ ; ]

Drop Sequence object

DROP SEQUENCE 
    { [ database_name . [ schema_name ] . | schema_name. ]    
    sequence_name } [ ,...n ]
     [ ; ]

Most of the keywords are self-explanatory, and from a comparison of syntax you

can see that SQL Server and Oracle are pretty similar in terms of the syntax.

Following are few example for same

create sequence MyFirstSequence as integer
start with 1
increment by 1
minvalue 1
maxvalue 10000

Points to Note :

• A sequence object is like any other user defined object and hence permission can be granted in the same way you grant permissions for other objects.
• Sequence objects are not transaction aware, which means when you use the NEXT VALUE FOR function in a transaction and then you rollback the transaction, it doesn’t mean it will give the same value again when you call the NEXT VALUE FOR function again but rather it will give the subsequent value irrespective of rollback.
• The sample code, example and UI is based on SQL Server Denali CTP 1; it might change in the final or RTM release.

Content you might be interested more

• CREATE SEQUENCE (Transact-SQL)
• ALTER SEQUENCE (Transact-SQL)
• DROP SEQUENCE (Transact-SQL)
• IDENTITY (Property) (Transact-SQL)

We will have review on some more topics on Denali !!

Hope this helps !!

Thanks for reading my blog !!

For more interesting links and daily update please subscribe our link resource website

Todays link are follows:

Link Resource # 20 : Aug 26 – Aug 28 « Dactylonomy of Web Resource

Posted by Vishal Pawar

Categories: Query, SQL

Tags:

« Older Newer »

Leave a Reply

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.