Developer Calssess

일반적으로 데이터베이스의 트리거(특정 상황이 발생하면 자동으로 실행되는 저장 프로시져)를 사용할 경우 제공되는 기능중에 inserted와 deleted라는 이름의 특별한 테이블들이 있습니다. 수정 작업시에 수정전의 데이터와 수정 후의 데이터를 갖고 있는 테이블을 지정해서 사용할 경우 접근할 수 있는 특별한 테이블입니다. 트리거를 사용하는 경우가 아니면 이러한 특별한 테이블에 접근할 수 있습니다.
그러나 SQL Server 2005부터는 OUTPUT문장을 지원함으로 일반 INSERT, UPDATE, DELETE문장에서도 이런 특별한 테이블에 접근할 수 있습니다. 테이블 변수를 선언한 후에 접근이 필요한 값을 OUTPUT문장을 사용해서 값을 담아서 사용할 수 있습니다.  다만 예외 상황도 있는데 아래와 같은 경우는 해당되지 않습니다.
 * 뷰를 대상으로 하는 INSERT구문인 경우
 * 원격지의 테이블이나 뷰에 대한 DML문인 경우
 * 로컬 또는 분산된 파티션 뷰에 대한 DML문인 경우

아래의 예제는 INSERT에서 OUTPUT절을 사용하는 경우입니다. 입력된 데이터를 테이블 변수에 담아서 그 값을 출력해 보았습니다. INSERT구문을 보면 VALUES앞쪽에 OUTPUT절을 사용해서 inserted.ProductModelID와 suser_name()함수를 지정했습니다. 지금 입력되는 레코드의 ProductModelID값과 접속한 유저이름을 @insert란 이름의 테이블 변수에 담도록 했습니다. 어떤 제품이 입력되고 누가 작업했는지를 확인할수 있습니다.

형식
INSERT INTO 데이터를 삽입할 테이블 (데이터를 삽입할 컬럼 명)
OUTPUT INSERTED.삽입된 값을 조회할 컬럼 명
VALUES (삽입할 데이터)

--OUTPUT키워드사용
DECLARE @insert TABLE
(ProductModelID int, InsertedBy sysname)

INSERT INTO Production.ProductModel(Name, ModifiedDate)
OUTPUT inserted.ProductModelID, suser_name() INTO @insert
VALUES('DVD 2.0', getdate())

SELECT * FROM @insert

이번에는 UPDATE구문에서 사용하는 예제도 보도록 합니다. UPDATE에서는 inserted, deleted 라는 열 접두사를 사용해서 입력된 값과 삭제된 값의 각 컬럼에 접근할 수 있습니다. @NameChange라는 이름으로 테이블 변수를 선언합니다. 여기에 새로 입력된 이름과 기존 이름을 담도록 합니다. 아래쪽의 Update절을 보면 INSERTED.ProductModelID는 새로 입력된 제품의 ID를 선택하고 DELETED.Name은 삭제된 제품의 이름을 선택해서 앞에서 만든 테이블 변수에 저장합니다. 저장된 값을 출력하면 수정된 제품의 이름이 어떻게 업데이트 되었는지 알 수 있습니다. 그리고 UPDATE 구문에서 사용하는 OUTPUT 절은 데이터가 변경될 때 변경되는 데이터로 인식하지 않고 데이터를 삭제했다가 다시 삽입되는 형식으로 인식하기 때문에 변경되기 전 데이터는 삭제된 데이터로 변경 후의 데이터는 삽입된 데이터로 판단합니다.

기본 형식
UPDATE 데이터를 변경할 테이블
SET 데이터를 변경할 컬럼 명 = 변경할 값 또는 식
OUTPUT DELETED.변경 전 조회할 컬럼 명, INSERTED.변경 후 조회할 컬럼 명
WHERE 데이터를 변경할 행의 조건

--UPDATE구문에서사용되는예제
DECLARE @NameChange TABLE
(ProductModelID int, 
 OldName nvarchar(50),
 NewName nvarchar(50),
 UpdateBy sysname)

UPDATE Production.ProductModel
SET Name = 'DVD 3.0' 
OUTPUT INSERTED.ProductModelID, DELETED.Name, INSERTED.Name,
suser_name() INTO @NameChange
WHERE ProductModelID=133
select * from @NameChange

DELETE절에서 사용하는 경우입니다. 앞에서 사용되었던 내용들과 비슷합니다. 삭제작업을 한 내용을 담아서 사용하기 때문에 DELETED.을 붙여서 삭제된 데이터의 컬럼 데이터를 선택해서 담은 결과를 출력합니다.

--DELETE구문에서사용된예제
DECLARE @Delete TABLE
(ProductModelID int,
 DeletedBy sysname)

DELETE Production.ProductModel 
OUTPUT DELETED.ProductModelID, suser_name() INTO @Delete
WHERE ProductModelID=132

SELECT * FROM @Delete
 

출처: https://gdbt.tistory.com/51 [Gravity DB Team]

테이블에 자동증가 컬럼의 값이 추가되었고, 증가된 값을 반환하고 싶은 경우 SCOPE_IDENTITY(), IDENT_CURRENT(), @@IDENTITY 함수를 사용하여 원하는 값을 얻을 수 있다.

세 함수는 자동증가 컬럼에 삽입된 값을 반환하기 때문에 비슷한 함수이지만, 차이점이 있어 주의해서 사용해야 한다.

아래의 결과를 보면 같은 값을 반환한다.

 CREATE TABLE dbo.TEST_TBL (

    COL1   INT           IDENTITY(1,1)

  , COL2   VARCHAR(10)

 )

 GO

 

 INSERT INTO dbo.TEST_TBL (COL2) VALUES ('ABC')

 INSERT INTO dbo.TEST_TBL (COL2) VALUES ('DEF')

 INSERT INTO dbo.TEST_TBL (COL2) VALUES ('GHI')

 GO

 

 SELECT @@IDENTITY                    AS '@@IDENTITY'

      , SCOPE_IDENTITY()              AS 'SCOPE_IDENTITY()'

      , IDENT_CURRENT('TEST_TBL')     AS 'IDENT_CURRENT()'

 GO

그렇다면 세 함수의 차이점은 무엇일까?

IDENT_CURRENT() 는 범위와 세션으로 제한되는 것이 아니라 지정된 테이블로 제한된다.

SCOPE_IDENTITY() 와 @@IDENTITY 는 현재 세션의 테이블에서 생성된 마지막 자동증가 컬럼을 반환한다.

그러나 SCOPE_IDENTITY() 는 현재 범위 내에 삽입된 값을 반환하고, @@IDENTITY 는 특정 범위로 제한되지 않는 차이점이 있다.

@@IDENTITY 는 트랜잭션 내에 여러번의 자동증가 컬럼이 있는경우 @@IDENTITY 값이 변하기 때문에 사용하는 것을 자제해야하고, SCOPE_IDENTITY() 는 트랜잭션 내에서 일정한 값을 계속 유지하고 있으므로 이 값으로 사용하여야 한다.

출처 : https://lovedb.tistory.com/371

※뷰(View), 프로시져(Procedure), 트리거(Trigger), 함수(Function) 내부 단어 검색

해당 테이블이 어디에서 사용하는지 한번에 검색하고 싶을때 아래명령을 통하여 검색하면된다.

Function의 경우는 스칼라와 테이블반환이 구분되어 검색된다.

(테스트 해본 결과 뷰(VIEW), 프로시져(SQL_STORED_PROCEDURE), 트리거(SQL_TRIGGER), 함수(SQL_SCALAR_FUNCTION & SQL_TABLE_VALUED_FUNCTION) 타입이 검색되는부분은 확인됨)

DECLARE @SEARCH_TEXT NVARCHAR(MAX) = '검색텍스트';
 
SELECT B.NAME       AS NAME     -- 이름
     , B.TYPE_DESC  AS TYPE     -- 타입 구분
     , A.DEFINITION AS CONTENTS -- 내용
  FROM SYS.SQL_MODULES A WITH (NOLOCK)
  LEFT JOIN SYS.OBJECTS B WITH (NOLOCK) ON A.OBJECT_ID = B.OBJECT_ID
 WHERE DEFINITION LIKE '%' + @SEARCH_TEXT + '%'      
 ORDER BY TYPE, NAME  

출처 https://mirwebma.tistory.com/187

방법1:

select top 10 * from tblname

where id not in (

    select top 100 id from tblname

);

방법2:

select * from (

    select row_number() over (order by id) as rownum, * from tblname

) t1

where t1.rownum between 101 and 110;

방법3:

SQL Server 2012의 새로운 기능인 OFFSET은 지정한 행의 수만큼 건너 뛴 후에 출력하는 기능을 한다.

ex)

SELECT userID, name, birthYear FROM userTbl

ORDER BY birthYear

OFFSET 4 ROWS;

전체 10명인데 그 중에서 4명을 건너 뛰고 5번째 행부터 출력되었다.

이는 나이가 어린 6명을 출력하는데 사용할 수 있다.

주의할 점은 OFFSET을 사용하려면 ORDER BY 문이 함께 나와야 한다는 점이다.

FETCH NEXT는 출력될 행의 수를 지정할 수 있다.

SELECT userID, name, birthYear FROM userTBL

ORDER BY birthYear

OFFSET 4 ROWS

FETCH NEXT 3 ROWS ONLY;

FETCH NEXT에 3을 지정하면 3행만 출력하게 된다.

지금은 데이터가 적어서 큰 효과가 없지만, 대용량의 데이터에서 특정 열로 지정한 후, 몇번째 행부터

몇 개 행 가져오기를 할 때 유용하게 사용될 수 있다.

출처 : https://developerking.tistory.com/18