Archive

After reading my previous article about SEQUENCE one of my friend asked a question about the performance of using Sequence for quick and continuous generation and whether there is a way to create and manage this using Management Studio ?

Yes, SEQUENCE support CACHE option

/* Create Sequence with Cache */

CREATESEQUENCERunningNumbers

AsINT

MINVALUE 1

NOMAXVALUE

STARTWITH 1

CACHE 500

NOCYCLE;

Using CACHE option, you can specify how many values should be pre generated and available ready, you can set the cache size based on your application’ requirement.

Default cache size is 20

To use in Loop for quick processing

To get a range of numbers to use in a loop, you can use sp_sequence_get_range System stored procedure, so that you can get a range and not have to use NEXT VALUE option for each loop call

/* Get Range of Numbers using Sequence */

DECLARE

@RANGE_FIRST_VALUESQL_VARIANT,

@RANGE_LAST_VALUESQL_VARIANT,

@RANGE_CYCLE_COUNTINT,

@SEQUENCE_INCREMENTSQL_VARIANT,

@SEQUENCE_MIN_VALUESQL_VARIANT,

@SEQUENCE_MAX_VALUESQL_VARIANT;

EXECsp_sequence_get_range

@sequence_name='RunningNumbers',

@range_size= 15,

@range_first_value=@range_first_valueOUTPUT,

@range_last_value=@range_last_valueOUTPUT,

@range_cycle_count=@range_cycle_countOUTPUT,

@sequence_increment=@sequence_incrementOUTPUT,

@sequence_min_value=@sequence_min_valueOUTPUT,

@sequence_max_value=@sequence_max_valueOUTPUT;

SELECT

'RunningNumbers'AS[Sequence Name],

@range_first_valueAS[Sequence First Value],

@range_last_valueAS[Sequence Last Value],

@range_cycle_countAS[Range Cycle Count],

@sequence_incrementAS[Sequence Increment],

@sequence_min_valueAS[Sequence Min Value],

@sequence_max_valueAS[Sequence Max Value];

You can use [Sequence First Value] and [Sequence Last Value] and use it in your loop to insert data.

Step 2: Expand “Programmability” and then expand "Sequences" to currently available ones

Step 3: To create a new sequence, right click on Sequence and click on “New Sequence”

Step 4: Specify the name and other required options such as Start Value, Increment By, etc.. and click “Ok” to create Sequence

Step 5: To modify the Sequence, right click on the selected sequence and click “Properties”

If you see the above screenshot, you might notice that Data Type, Precision or name of sequence can not be modified. You can change the other values, similarly you will see an option to restart the sequence as well.

Note: To rename you can use the “Rename” option in Context menu.

Step 6: To drop the sequence, right click on the selected sequence and click “Delete”

SQL Server “Denali” introduces a new feature called “SEQUENCE”. SEQUENCE is a user-defined object that generates a sequence of numeric values according to specified options.

Its not bound to a table like IDENTITY, You can use SEQUENCE across tables

Supported Data Types for creating SEQUENCE are as follows:

TinyInt

SmallInt

Int

BigInt

Decimal

Numeric

/* Create Sequence With Start Values */

CREATESEQUENCERunningNumbers

STARTWITH 1

INCREMENTBY 1;

GO

The above code creates a simple SEQUENCE with starting number as 1 and increments by 1

To get the next number in SEQUENCE, you need to use “NEXT VALUE” option

SELECT (NEXTVALUEFORRunningNumbers)AS[RunningNumbers];

The below code might syntactically look same as the SEQUENCE "- RunningNumbers, but if you look at the output, its entirely different

/* Create Sequence Without Start Values */

CREATESEQUENCERunningNumbers2

INCREMENTBY 1;

GO

SELECT (NEXTVALUEFORRunningNumbers2)AS[RunningNumbers2];

If you see the output from RunningNumbers2, you will be getting a Negative value, its due to not specifying the Start number, so make sure you specify a Positive Start Number if you don’t want your table keys to Negative

Other options available in Creating Sequence are as follows:

CREATESEQUENCERunningNumbers3

AStinyint

STARTWITH 1

INCREMENTBY 1

MINVALUE 1

MAXVALUE 5

CYCLE;

GO

You can specify Minimum Value and Maximum Value and whether to Cycle the numbers when reaching maximum value or not

SELECTNEXTVALUEFORRunningNumbers3ASID,NameFROMsys.objects;

GO

If you see the below output, for every 5 records, the IDs are recycling and starts from 1 again

Restarting the Sequence

To restart the Sequence, you need to ALTER the sequence with RESTART option and start value

/* Restart the Sequence */

ALTERSEQUENCE[dbo].[RunningNumbers2]

RESTARTWITH 1

SELECT (NEXTVALUEFORRunningNumbers2)AS[RunningNumbers2];

Since we restarted the Sequence with 1, Now we are getting output as 1 for RunningNumber2

Find the available Sequences in a database

SELECT

object_id,name,type_desc,start_value,current_value,increment,

minimum_value,maximum_value,is_cycling,is_cached,cache_size

FROMsys.sequences

You can Sys.Sequence system view to query the list of available Sequences in a database

How to use it as Identity across tables ?

Create tables for each reqion

— Create tables

CREATETABLEOrders_West

(OrderIDintPRIMARYKEY,

Namevarchar(20)NOTNULL,

QtyintNOTNULL);

GO

CREATETABLEOrders_East

(OrderIDintPRIMARYKEY,

Namevarchar(20)NOTNULL,

QtyintNOTNULL);

GO

CREATETABLEOrders_South

(OrderIDintPRIMARYKEY,

Namevarchar(20)NOTNULL,

QtyintNOTNULL);

GO

CREATETABLEOrders_North

(OrderIDintPRIMARYKEY,

Namevarchar(20)NOTNULL,

QtyintNOTNULL);

GO

Insert data to table using Sequences

— Insert Four records

INSERTOrders_West(OrderID,Name,Qty)

VALUES (NEXTVALUEFORRunningNumbers2,'Apples', 2);

INSERTOrders_East(OrderID,Name,Qty)

VALUES (NEXTVALUEFORRunningNumbers2,'Oranges', 1);

INSERTOrders_South(OrderID,Name,Qty)

VALUES (NEXTVALUEFORRunningNumbers2,'Grapes', 1);

INSERTOrders_North(OrderID,Name,Qty)

VALUES (NEXTVALUEFORRunningNumbers2,'Banana', 1);

GO

Query the inserted data

SELECT*FROMOrders_West

UNION

SELECT*FROMOrders_East

UNION

SELECT*FROMOrders_North

UNION

SELECT*FROMOrders_South

If you see the Union output from 4 different tables, we are able to create a identity column across 4 tables, This will be very helpful when you horizontally partition data across tables

Delete the Sequences

DROPSEQUENCERunningNumbers;

DROPSEQUENCERunningNumbers2;

I hope you all find this information about Sequences useful and informative !!!