MS SQL sequence 만들기

 

ORACLE에는 sequence가 있듯,

MS SQL에는 자동증가로  IDENTITY가 있다. 하지만 칼럼타입이 int 같은 정수여야만 사용가능하다...

CREATE TABLE TEST

(

   USER_INDEX INT IDENTITY(1,1),

   USER_NAME VARCHAR(20)

)

-- IDENTITY(시작수,증가수)

INSERT시 증가된 값 얻어오기

IDENTITYFMF 설정해준 테이블에 INSERT한 경우 INSERT한 IDENTITY값을 불러오기 위해 다시 한번 SELECT를 해주게 됩니다. 이때 SELECT @@IDENTITY를 붙여주면 INSERT 한 ROW의 IDENTITY값을 불러올수 있습니다.

 

INSERT INTO TEST(NAME) VALUES ('홍길동') SELECT @@ IDENTITY 

 

증가값 임의 지정

IDENTITY를 선언한 경우에는 INSERT시 IDENTITY로 선언된 컬럼의 값을 임의로 지정 할 수 없습니다.

IDENTITY로 설정할 때는 COLUMN에 수동으로 INSERT하는 것을 허용하지 않습니다.

이상태에서 IDENTITY 설정이 되어 있는 COLUMN은 자동으로 값이 입력됩니다.

중간값이 있는 행을 삭제하더라도 새로 들어온 값이 중간값으로 할당하지 않습니다.

 

만약 임의의 번호를 지정하고 싶거나 삭제된 번호를 채워넣고 싶을 때 유용한 방법입니다.

SET IDENTITY_INSERT [테이블명] ON -- 증가값을 수동으로 지정

SET IDENTITY_INSERT [테이블명] OFF -- 증가값을 자동으로 지정

DBCC CHECKIDENT([테이블명], RESEED, 0)  -- IDENTITY 초기화 (테이블명, RESEED, 설정할 초기값)

 

문제는 시퀀스 칼럼타입이 INT가 아니고 varchar타입이다.

(컬럼타입을 바꿔서 해결하면 좋겟지만 현실은 냉정하듯 해당컬럼의 타입을 바꿀수 없는 상황이다.)

그리고 테이블에 동시에 여러 곳에서 입력이 빈번하게 일어난다고 한다. 입력시 키값이 듀플리케이션이 발생한다고 한다.  

 

1. MS SQL 시퀀스 만들기

 ①. Create a sequence table

CREATE TABLE SEQ_TAB
(
   seq_name   VARCHAR(64) PRIMARY KEY,
   cyclic_yn   CHAR(1) NOT NULL,
   seq_value   INT NOT NULL
)

 

 ②. Create a procedure to get sequence number
 

----------------------------------------------
-- 시퀀스 갖고 오는 프로시저
-- 작업성공이면 0보다 큰 수, 실패면 음수 반환
----------------------------------------------
create procedure sp_get_nextval
  @p_seq_name varchar(100),
  @v_seq_value int output
as
begin
  declare @v_cyclic_yn char(1)

  set @v_seq_value = -1

  begin transaction trans1

  select @v_cyclic_yn = cyclic_yn from seq_tab where seq_name = @p_seq_name

  if (@@rowcount = 0)
    return @v_seq_value -- 찾는 자료 없음
  else
  begin
    update seq_tab set @v_seq_value = seq_value = seq_value + 1 where seq_name = @p_seq_name

    -- 시퀀스가 순환이고, 현재 값이 int의 최대 값이면 0으로 되돌린다.
    if (@v_seq_value = 2147483647) and (@v_cyclic_yn = 'y')
    begin
      update seq_tab set seq_value = 0 where seq_name = @p_seq_name
    end

    commit transaction trans1
  end

  return @v_seq_value
end

 

 

1. 시간값으로

시간값으로 천분의 일초까지 지정 한후 별도의 sequence 테이블을 생성 201203270900000 + 0000  네자리를 추가한다.