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