Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

Global Sequence - a new feature in SQL Server 11

May 1 2011 10:16PM by Paresh Prajapati   

It is very hardly when we need to retain sequence globally. We can use identity property of column but it is limited to that table only.

SQL Server 2011 has introduced new feature named "SEQUENCE". We can retain the global sequence and can be used in multiple tables or in any queries.
It's really global sequence generator and alternative option of Identity columns.

Let's see the simple example using it.

Creating database and table objects.
-- Creating database
CREATE DATABASE SEQUENCEDB
GO
USE SEQUENCEDB
GO
-- Creating table
IF( OBJECT_ID('SequenceTab') > 0 )
  DROP TABLE SequenceTab
GO
CREATE TABLE SequenceTab
  (
     Seq_Id INT,
     Name   VARCHAR(20)
  )
GO 
Now we will create a Sequence.
-- Creating Sequence

IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence')
DROP SEQUENCE NewSequence;
GO

CREATE SEQUENCE [NewSequence] AS INT
START with 1
INCREMENT by 1
MAXVALUE 1000
GO
You can create sequence in Object Explorer,



Let us insert some records in table using sequence and see the data in table.
-- Inserting records using Sequence

INSERT INTO SequenceTab
SELECT next value for [NewSequence], 'Sequence-1'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-2'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-3'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-4'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-5'
UNION ALL
SELECT next value for [NewSequence], 'Sequence-6'
GO

-- Records in table 

SELECT Seq_Id,Name FROM SequenceTab
GO


Now we will alter the sequence and reset to 1 and then review the next sequence to be generated.
-- Reset the sequence , starting from 1

ALTER SEQUENCE [NewSequence]
RESTART WITH 1
GO
Let's see the next values from sequence from first run.
SELECT
next value for [NewSequence] as NeSequence
,*
FROM SYS.DATABASES
GO


Next values for sequence from second run.
SELECT 
next value for [NewSequence] as NeSequence
,* FROM SYS.DATABASES
GO

Tags: sql, tsql, sql server, ms sql, ms sql server, t-sql, new features, feature, sqll server 2011, sql server denali, #SQL Server, mssql, #sql,


Paresh Prajapati
6 · 23% · 7379
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Global Sequence - a new feature in SQL Server 11" rated 5 out of 5 by 4 readers
Global Sequence - a new feature in SQL Server 11 , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]