SQL 2012 New Feature - SEQUENCE GENERATOR

원문 : Sequence Object in SQL Server 2012

http://www.databasejournal.com/features/mssql/sequence-object-in-sql-server-2012.html
http://www.sql-server-performance.com/2011/tsql-sql-server-2011-features/4/  

 
오라클에서 지원되던 Sequence Object 부분이 MSSQL에서 지원되다니 이제 좀 간편해졌네요. 중복되지 않은 값을 두 테이블에 넣으려고 할때 꼭 필요했죠.

게임DB에서 유니크한 아이템 시리얼 정보를 얻으려고 할때 테이블에 INSERT해서 유니크한 IDENTITY VALUE를 얻었는데, 이것으로 간단하게 소스 코드가 만들어지겠네요^^

2013.11.25 - identity와 같이 컬럼에 할당이 가능.
http://raresql.com/2013/11/22/sql-server-2012-how-to-make-sequence-as-default-value-for-a-column-in-a-table/

ALTER TABLE dbo.tbl_sample

ADD CONSTRAINT Const_Sample_Seq

DEFAULT (NEXT VALUE FOR dbo.Sample_Seq) FOR [ID];

GO

Syntax

---

CREATE SEQUENCE [schema_name . ] sequence_name
    [ 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 } ]
    [ ; ]

integer type : tinyint, smallint, int, bigint, decimal and numeric etc..

예제
오라클에서 지원되던 Sequence Object 부분이 MSSQL에서 지원되다니 이제 좀 간편해졌네.

USE [master]
GO
 
-- TEST DB 생성
DROP DATABASE [Lab_Sequence_Test]
GO
 
Create database Lab_Sequence_Test;
GO
 
use Lab_Sequence_Test;
GO
 
--// 생성 테스트
-- IDENTITY 형식과 같이 시작값이 1000이며, 증가값은 5으로 설정
Create SEQUENCE [dbo].[InvoiceNumber]
as int
START WITH 1000
INCREMENT BY 5;
GO
 
-- 실행하면
SELECT NEXT VALUE FOR [dbo].[InvoiceNumber]
GO
 
-- 결과
-- 1005
 
 
--// 리사이클 테스트
if (object_id('LLCInvoices') is not NULL)
BEGIN
    DROP TABLE LLCInvoices
END
ELSE
BEGIN
        CREATE TABLE [dbo].[LLCInvoices](
                  [InvoiceID] [int] NULL,
                  [Name] [varchar](100) NULL
        ) ON [PRIMARY]
END
GO
 
--Insert values
 
Insert into LLCInvoices (InvoiceID, Name) VALUES  (NEXT VALUE for [dbo].[InvoiceNumber],'Anderson')
Insert into LLCInvoices (InvoiceID, Name) VALUES  (NEXT VALUE for [dbo].[InvoiceNumber],'Neo')
 
--Query Table
Select * from LLCInvoices
 
-- 현재 할당된 VALUE을 확인할 수 있다.
SELECT current_value
FROM sys.sequences
WHERE name = 'InvoiceNumber'
 
--1015
 
-- 시작 값을 312780968, 증가 값을 1
Create SEQUENCE [dbo].[US_Population]
as Bigint
START WITH 312780968
INCREMENT BY 1;
GO
 
 
Create table NJ_Population
(USPopulationID bigint, PopulationID bigint identity(1,1), FirstName varchar(100),
LastName varchar(100))
Create table NY_Population
(USPopulationID bigint, PopulationID bigint identity(1,1), FirstName varchar(100),
LastName varchar(100))
Create table CT_Population
(USPopulationID bigint, PopulationID bigint identity(1,1), FirstName varchar(100),
LastName varchar(100))
 
 
Insert into NJ_Population (USPopulationID, FirstName,LastName)
VALUES  (NEXT VALUE for [dbo].[US_Population],'Anderson', 'Smith')
Insert into NY_Population (USPopulationID, FirstName,LastName)
VALUES  (NEXT VALUE for [dbo].[US_Population],'Amanda', 'Saunders')
Insert into NJ_Population (USPopulationID, FirstName,LastName)
VALUES  (NEXT VALUE for [dbo].[US_Population],'Kelly', 'Shikari')
Insert into NJ_Population (USPopulationID, FirstName,LastName)
VALUES  (NEXT VALUE for [dbo].[US_Population],'Sarah', 'Parker')
Insert into NY_Population (USPopulationID, FirstName,LastName)
VALUES  (NEXT VALUE for [dbo].[US_Population],'July', 'cruz')
Insert into CT_Population (USPopulationID, FirstName,LastName)
VALUES  (NEXT VALUE for [dbo].[US_Population],'William', 'Benz')
Insert into CT_Population (USPopulationID, FirstName,LastName)
VALUES  (NEXT VALUE for [dbo].[US_Population],'Kate', 'Johnston')
Insert into NJ_Population (USPopulationID, FirstName,LastName)
VALUES  (NEXT VALUE for [dbo].[US_Population],'Len', 'cucino')
 
 
SELECT * from NJ_Population
SELECT * from NY_Population
SELECT * from CT_Population
 
-- 재사용할 수 있도록 CYCLE KEYWORD를 설정한다. 최대 값을 255으로 설정하여 재사용
Create SEQUENCE [dbo].[ReUsable_Sequence]
as tinyint
START WITH 252
INCREMENT BY 1
MINVALUE  0
MAXVALUE  255
CYCLE
GO
 
 
SELECT Name,start_value,minimum_value,maximum_value ,current_value
FROM sys.sequences
WHERE name = 'ReUsable_Sequence'
 
-- ReUsable_Sequence   252     0       255     252
 
declare @range_first_value sql_variant
declare @startingrangeout sql_variant 
declare @rangesize bigint
set @rangesize =10
 
EXEC sp_sequence_get_range
@sequence_name = N'dbo.ReUsable_Sequence',
@range_size = @rangesize ,
@range_first_value=  @startingrangeout OUTPUT ;
 
SELECT @startingrangeout as StartingNumber
 
 
 
SELECT Name,start_value,minimum_value,maximum_value ,current_value
FROM sys.sequences
WHERE name = 'ReUsable_Sequence'
 
 
CREATE SEQUENCE DBO.BIGINT
        AS BIGINT
        START WITH 1   -- 시작값
        INCREMENT BY 1
GO
 
SELECT Name,start_value,minimum_value,maximum_value ,current_value
FROM sys.sequences
WHERE name = 'BIGINT'
GO
 
 
SELECT NEXT VALUE for [dbo].BIGINT
GO
 
SELECT 'TEST',  NEXT VALUE for [dbo].BIGINT AS SerialNo
UNION ALL
SELECT 'TEST',  NEXT VALUE for [dbo].BIGINT AS SerialNo
UNION ALL
SELECT 'TEST',  NEXT VALUE for [dbo].BIGINT AS SerialNo
UNION ALL
SELECT 'TEST',  NEXT VALUE for [dbo].BIGINT AS SerialNo
 
 
 
CREATE SEQUENCE DBO.BIGINT
        AS BIGINT              --
        START WITH 1   -- 시작값
        INCREMENT BY 1
GO
 
SELECT Name,start_value,minimum_value,maximum_value ,current_value
FROM sys.sequences
WHERE name = 'BIGINT'
GO
 
 
SELECT 'TEST',  NEXT VALUE for [dbo].BIGINT AS SerialNo
UNION ALL
SELECT 'TEST',  NEXT VALUE for [dbo].BIGINT AS SerialNo
UNION ALL
SELECT 'TEST',  NEXT VALUE for [dbo].BIGINT AS SerialNo
UNION ALL
SELECT 'TEST',  NEXT VALUE for [dbo].BIGINT AS SerialNo
 
 
--(열 이름 없음)        SerialNo
--TEST  110
--TEST  111
--TEST  112
--TEST  113