개발새발

자동 증가 값 생성 (IDENTITY)

DB를 설계하는데 있어서 데이터가 생성될 때마다 자동으로 값이 증가하는 컬럼을 생성해야 하는 경우가 존재한다. 이럴 때 주로 사용하는 것이, MSSQL에서는 IDENTITY이다.

사용법

IDENTITY는 아래와 같은 방법으로 테이블을 생성할 때 사용한다.

CREATE TABLE test (
	idx int identity (1, 1) -- (초기값, 증가값)
)

identity (초기값, 증가값) 을 입력하여 초기값과 증가 값을 조절할 수 있으며, 아예 입력을 하지 않은 경우에는 기본 값으로 1부터 하나씩 증가하도록 되어있다.

CREATE TABLE test (
	idx int identity
)

임의 지정 및 초기화

기본적으로 identity를 설정하면, 증가값을 설정된 값으로 증가 값을 자동으로 생성한다. 그렇기 때문에, 중간에 데이터가 삭제되는 경우 해당 증가 값을 뛰어 넘어서 생긴다. 이렇게 중간에 비는 데이터를 채워 넣거나 기존 시작 값을 변경하고 싶은 경우에 해당 방법을 사용할 수 있다.

-- 증가값 자동 지정 (기본 설정)
SET IDENTITY INSERT test OFF; -- test : 테이블명

-- 증가값 수동 지정
SET IDENTITY INSERT test ON;

-- 시작값 재지정
DBCC CHECKIDENT ('test', RESEED, 0) -- 0 : 시작값

증가 값은 기본적으로 자동 지정으로 설정되어 있으며, 이 상태의 경우 데이터를 강제적으로 insert해도 값을 넣을 수 없고, 아래와 같은 에러를 볼 수 있다. 

[S001][544] IDENTITY_INSERT가 OFF로 설정되면 테이블 'test'의 ID 열에 명시적 값을 삽입할 수 없습니다.

그렇기 때문에 중간에 비는 데이터를 다시 채워 넣고 싶은 경우 IDENTITY_INSERT를 ON으로 설정한 뒤에 데이터를 입력하여야 정상적으로 처리할 수 있다.

초기화의 경우 DBCC CHECKIDENT를 사용하여, 진행할 수 있다. 데이터를 전부 삭제하여도 기존에 증가 값에 대한 값은 초기화하지 않으면 삭제된 상태로 유지되기 때문에, 다시 기존 초기값부터 재설정하여야 원하는 대로 데이터 처리가 가능하다.

자동 증가값 조회

프로시저에서 Insert 한 뒤 자동으로 증가한 IDENTITY 값을 조회하여야 하는 경우 사용한다.

주로, @@IDENTITY, IDENT_CURRENT(), SCOPE_IDENTITY() 세 방법을 사용하며 각각 사용처가 다르게 사용된다.

-- 현재 세션의 테이블에서 생성된 마지막 ID 값 반환 (제한 x)
SELECT @@IDENTITY

-- 현재 세션의 테이블에서 생성된 마지막 ID 값 반환 (현재 범위만)
SELECT SCOPE_IDENTITY()

-- 지정된 테이블에서 생성된 마지막 ID 값 반환
SELECT IDENT_CURRENT('test') -- 테이블명(test)

오류가 발생하거나 개체를 볼 수 있는 권한이 없으면 NULL을 반환하며, 주로 특정 테이블의 마지막 ID 반환에는 IDENT_CURRENT를 사용하고 현재 세션 내의 작업만을 대상으로 하는지 여부에 따라 @@IDENTITY와 SCOPE_IDENTITY가 다르게 사용된다고 생각되면 조금 이해가 편하다.

변수란?
임시 저장 영역으로, 값을 담아두는 공간이다.

기본적인 프로그래밍 영역에서 사용하듯이 저장 프로시저에서도 동일하게 사용 가능하다.

변수 선언

변수 선언 시에는 DECLARE를 사용하며, 사용법은 아래와 같다.

-- 단일 형태의 변수 생성
DECLARE @name nvarchar(10), @age int -- @name, @age 필요한 변수명 사용

-- 테이블 형태의 변수 생성
DECLARE @data table (name nvarchar(10), age int)

각각 필요한 데이터 형태에 따라서 단일 형태로 선언하거나, 테이블 형태로 여러 행의 변수를 담을 수도 있다.

변수 값 대입

변수는 초기 생성 시 null 값을 가지고 있으며, 변수에 값을 대입하여 사용할 수 있다.

주로, SET이나 SELECT 하여 특정 데이터를 변수에 담을 수 있으며 사용법은 아래와 같다.

-- 변수 선언
DECLARE @name nvarchar(10)

-- SET을 통한 변수 값 대입
SET @name = '개발새발'

-- SELECT를 통한 변수 값 대입
SELECT @name = name FROM test WHERE name = '개발새발'

기본적으로는 두가지 방법을 사용하지만, SET 결과에 SELECT 쿼리를 사용하거나 하는 등의 변형도 가능하다.

IF EXISTS 란

프로시저를 활용하다 보면, 데이터를 확인하여 특정 조건에 따라 다른 조건의 쿼리를 실행해야 하는 경우가 생긴다.

이럴 때 주로 활용하는 조건문이 IF EXISTS이다.

문법

기본적인 문법은 다음과 같으며, 조건문에 값의 유무에 따라 실행되는 쿼리를 다르게 실행할 수 있다.

IF EXISTS (
	-- 조건문
	SELECT 1 FROM TEST WHERE TEST_NAME = '개발새발'
    )
	BEGIN
    	-- 조건문에 값이 있는 경우 실행
    	UPDATE TEST SET TEST_TIME = GETDATE()
    END
ELSE
	BEGIN
    	-- 조건문에 값이 없는 경우 실행
    	INSERT INTO TEST (TEST_NAME, TEST_TIME) VALUES ('개발새발', GETDATE())
    END

IF EXISTS 문법과 다르게, 조건 결과가 없을 때의 경우도 사용할 수 있으며, 문법은 다음과 같다.

IF NOT EXISTS (
	-- 조건문
	SELECT 1 FROM TEST WHERE TEST_NAME = '개발새발'
    )
	BEGIN
    	-- 조건문에 값이 없는 경우 실행
    	INSERT INTO TEST (TEST_NAME, TEST_TIME) VALUES ('개발새발', GETDATE())
    END
ELSE
	BEGIN
    	-- 조건문에 값이 있는 경우 실행
        UPDATE TEST SET TEST_TIME = GETDATE()
    END

해당 함수의 경우 조건에 따라 다양하게 사용되며, 코드단에서 데이터를 두 번 이상 호출하지 않아도 되기 때문에 경우에 따라 유용하게 활용할 수 있는 함수이다.

저장 프로시저 (Stored Procedure) 

프로시저는 SQL Server에서 제공되는 프로그래밍 기능이며, 쿼리 문의 집합체라고 볼 수 있다.

간단하게로는 SELECT, INSERT, UPDATE, DELETE 등의 DQL, DML을 사용할 수 있으며,

조금 더 나아가서 IF 문이나 DECLARE, SET 등의 프로그래밍 문법을 사용할 수도 있다.

장점 및 단점

장점

• 보안성 향상
  • 프로시저 단위로 실행 권한을 부여할 수 있기 때문에, 기본적인 보안 사고에 대처가 유연한 편이다.
• 네트워크 소요 시간 절감
  • 쿼리를 다중으로 실행하는 경우, 한번의 호출을 통해 다중의 쿼리가 실행되기 때문에 네트워크에 대한 부담과 소요 시간을 줄일 수 있다.
• 운영 배포 용이성
  • 별도의 WAS 서버 재기동 없이 SP 수정으로 조회, 수정, 추가 등의 가벼운 소스 변경 등이 가능하여, 긴급 배포 등이 용이하다.

단점

• 낮은 처리 성능
  
  • 프로시저의 경우 성능이나 최적화가 부족하여 수행 능력이 떨어지며, 특히 문자열이나 숫자 연산에 사용하면 JAVA, C 등에 비해서 효율이 좋지 않다.
• 디버깅 및 유지보수의 어려움
  
  • 배포, 버전 관리 등에 대한 이력 관리가 힘들다. (별도의 Description 사용)
  • SP가 수정되는 경우 현재 운영 중인 서비스에 영향도 분석이 어렵다.
  • APP에서 SP를 호출하여 사용하는 경우 문제가 생겨도 해당 이슈에 대한 추적이 힘들다. (별도의 에러 테이블 사용)

프로시저 생성, 수정, 삭제

기본적인 생성 방식은 다음과 같다.

USE [DB명]
GO

CREATE PROCEDURE [dbo].[프로시저명] (@파라미터명 NVARCHAR(10), @파라미터명2 int)
AS
BEGIN
    -- 사용할 쿼리
    SELECT 1 FROM test
END
GO

사용할 DB명과 프로시저명을 입력하여 사용하며, 파라미터의 경우 필요한 경우 자료형과 함께 입력하여 사용한다.

BEGIN과 END 사이에는 사용할 쿼리나, 프로그래밍 문법을 사용하여 실행할 수 있다.

수정의 경우, 생성과 기본 방식은 동일하며, CREATE가 아닌 ALTER를 사용하여 수정한다.

다만, 해당 SP의 경우 가장 기본적인 문법으로 현업에서는 아래와 같은 디스크립션을 추가하기도 한다.

USE [DB명]
GO

/*=============================================
    작성자: 개발새발
    생성날짜: 2021-09-13
    업데이트날짜: 2021-09-13
    설명: 프로시저 기본 생성 방식
    exec 프로시저명 'TEST', 1
=============================================*/

ALTER PROCEDURE [dbo].[프로시저명] (@파라미터명 NVARCHAR(10), @파라미터명2 int)
AS
BEGIN
    -- 사용할 쿼리
    SELECT 1 FROM test
END
GO

삭제는 DROP 구문을 사용하여 삭제할 수 있다.

DROP PROCEDURE 프로시저명

프로시저 조회 및 실행

프로시저 조회는 sp_helptext를 사용하여, 텍스트 형식으로 결과를 표기할 수 있다.

sp_helptext 프로시저명

어떤 툴에서 DB를 연결하여 사용하느냐에 따라 다른데, 기본적으로 사용하는 SSMS (SQL Server Management Studio)에서는 GUI를 통해 저장 프로시저 스크립팅으로 CREATE, ALTER, DROP 등의 문법을 자동으로 생성해주며,

인텔리제이에서 데이터베이스를 연결하는 경우 SQL Generator (Ctrl + Alt + G) 통해 프로시저를 조회하는 것이 더 간단하다.

프로시저 실행은 exec 구문을 사용하여 해당 프로시저를 사용할 수 있다. 실행할 SP의 입력할 파라미터가 존재하는 경우, 순차적으로 해당 파라미터를 입력하면 정상적으로 실행된다.

exec 프로시저명 'TEST', 1