MSSQL OUTPUT 절 사용법 및 활용

MSSQL OUTPUT 절은 query 결과를 실시간으로 확인하고, 다른 작업에 활용할 수 있어 데이터 무결성을 유지하고, 데이터베이스 작업의 효율성 높여줄 수 있습니다.

MSSQL OUTPUT 활용

데이터 감사 기능.

• OUTPUT 절을 사용하여 데이터 변경 사항을 사용자가 원하는 테이블에 저장할 수 있습니다. 즉, 데이터의 변경 이력을 저장하는 데 매우 효율적입니다.
• OUTPUT 절의 사용은 데이터베이스 작업의 투명성을 높여주고 실시간으로 데이터 변경 사항을 추적하는 데 활용됩니다.
• 또한 INTO 키워드를 사용하여 반환되는 data를 다른 테이블에 저장할 수도 있습니다.
• INSERT, UPDATE, DELETE 명령문에서는 변경되거나 삭제된 DATA의 원래 데이터와 새 데이터를 비교할 수 있습니다.

트랜잭션 중인 데이터 확인.

• 트랜잭션에서 OUTPUT 절을 사용하여 중간 단계의 데이터를 검색하여 추가적인 로직을 작성할 수 있습니다.
• 즉, 복잡한 트랜잭션 구조에서 데이터의 흐름을 이해하는 데 중요한 역할을 합니다.

임시 table 하고 상호작용.

• OUTPUT 절을 사용하여 임시 table에 data를 insert 한 후 다른 query에 활용할 수 있습니다.

MERGE 명령문과의 결합.

• MERGE 명령문을 사용할 때 OUTPUT 절을 활용하여 삽입, 업데이트, 삭제된 각 행의 정보를 얻을 수 있습니다.

MSSQL OUTPUT 절 예제

DELETE 문과 함께 OUTPUT 사용.

• OUTPUT DELETED.* 은 삭제된 data를 확인할 수 있습니다.
• OUTPUT DELETED 소스예제입니다.

--Use OUTPUT with a DELETE statement
DROP TABLE IF EXISTS InfoForOutputInto;
CREATE TABLE InfoForOutputInto (
    UserId SMALLINT,
    UserNm VARCHAR(50)  
); 

INSERT INTO dbo.InfoForOutputInto(UserId,UserNm) 
VALUES (272, N'Ken'),(273, N'Brian'),(274, N'Stephen');

-- DELETE OUTPUT
DELETE InfoForOutputInto
OUTPUT DELETED.*
WHERE UserId = 272;
  
--Check the rows in the table  
SELECT * FROM InfoForOutputInto;

UPDATE 문과 함께 OUTPUT INTO 사용.

• OUTPUT 절을 사용하여 DELETED 열에서 UPDATE 문을 적용하기 전에 존재했던 값과 INSERTED 열에서 업데이트된 값을 테이블 변수에서 확인할 수 있습니다.
• OUTPUT INTO UPDATE 소스예제입니다.

-- Use OUTPUT INTO with an UPDATE statement
DROP TABLE IF EXISTS InfoForOutputInto;
CREATE TABLE InfoForOutputInto (
    UserId SMALLINT,
    UserNm VARCHAR(50)  
);
 
INSERT INTO dbo.InfoForOutputInto(UserId,UserNm) 
VALUES (272, N'Ken'),(273, N'Brian'),(274, N'Stephen');

DECLARE @VarForOutputInto TABLE (
	VarUserId SMALLINT,
	OldVarUserNm VARCHAR(50),
	NewVarUserNm VARCHAR(50) 
);
  
UPDATE TOP (1) InfoForOutputInto
SET UserNm = UserNm + ' test Out Into'
OUTPUT INSERTED.UserId,
       DELETED.UserNm,
       INSERTED.UserNm        
INTO @VarForOutputInto;  
--Display the result set of the table variable.
SELECT VarUserId, OldVarUserNm, NewVarUserNm 
FROM @VarForOutputInto;

INSERT 문과 함께 OUTPUT INTO 사용.

• 지정한 테이블에 data를 삽입하고 OUTPUT 절을 사용하여 결과를 테이블 변수에 insert 한 후 입력된 data를 확인할 수 있습니다.
• OUTPUT INTO INSERT 소스예제입니다.

-- Use OUTPUT INTO with an INSERT statement
DROP TABLE IF EXISTS InfoForOutputInto;
CREATE TABLE InfoForOutputInto (
    UserId SMALLINT,
    UserNm VARCHAR(50),
    ModiDate DATETIME
);  
DECLARE @VarForOutputIntoInsert TABLE (
    VarUserId SMALLINT,
    VarUserNm VARCHAR(50),
    VarModiDate DATETIME);
    
INSERT InfoForOutputInto
    OUTPUT INSERTED.UserId, INSERTED.UserNm, INSERTED.ModiDate
        INTO @VarForOutputIntoInsert
VALUES (101,N'ben', GETDATE());
  
--Display the result set of the table variable.
SELECT VarUserId, VarUserNm, VarModiDate FROM @VarForOutputIntoInsert;

• OUTPUT 절 그림 설명입니다.

OUTPUT 절 그림 설명.

MSSQL OUTPUT 주의 사항

트랜잭션 관리.

• OUTPUT 절은 데이터 변경 시 발생하는 결과를 기록할 수 있지만, 사용할 때는 트랜잭션을 적절히 관리해줘야 합니다.

트리거와의 상호작용.

• OUTPUT 절은 트리거 내에서 사용될 때 예상치 못한 결과를 초래할 수 있어 주의해서 사용해야 합니다.

성능에 문제가 되는지 확인.

• 대량의 데이터를 처리할 때 OUTPUT 절을 사용하면 성능저하가 발생할 수 있습니다.