SQL_SEQUENCE(시퀀스), ALTER, RESTART, CYCLE, INCREMENT,

USE tempdb;
CREATE TABLE testTbl3
(	id int,
	userName nchar(3),
	age int,
	nation nchar(4) DEFAULT '대한민국');
	GO
SELECT * FROM testTbl3;

CREATE SEQUENCE idSEQ
	START WITH 1 -- 시작값
	INCREMENT BY 1; -- 증가값
	GO

INSERT INTO testTbl3 VALUES (NEXT VALUE FOR idSEQ, '지민', 25, DEFAULT);
SELECT * FROM testTbl3;


INSERT INTO testTbl3 VALUES (11, '쯔위', 25, '대만');
ALTER SEQUENCE idSEQ
  RESTART WITH 12; -- 시작값을 다시 설정
INSERT INTO testTbl3 VALUES (NEXT VALUE FOR idSEQ, '미나', 25, '일본');
SELECT * FROM testTbl3;

 

 

CREATE TABLE testTbl4 (id INT);
GO
CREATE SEQUENCE cycleSEQ
START WITH 100
INCREMENT BY 100
MINVALUE 100 -- 최소값
MAXVALUE 300 -- 최대값
CYCLE;	-- 반복설정

GO
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
GO
SELECT * FROM testTbl4;

 

 

 

USE tempdb;
CREATE  SEQUENCE autoSEQ
  START WITH 1
  INCREMENT BY 1; -- 자동으로 1씩 증가
GO
CREATE TABLE testTbl5
(id int DEFAULT(NEXT VALUE FOR autoSEQ), userName nchar(3));
GO
INSERT INTO testTbl5(userName) VALUES ('지민');
INSERT INTO testTbl5(userName) VALUES ('쯔위');
INSERT INTO testTbl5(userName) VALUES ('미나');
GO
SELECT * FROM testTbl5;