70세 코딩

① 저장 프로시저(이하 SP) 존재 여부 검사

  IF EXISTS (SELECT * FROM sysobjects WHERE id=object_id('SP명') AND objectproperty(id, 'IsProcedure')=1) ...

② SP가 배치(batch)의 첫번째 문장에서 실행된다면 EXECUTE 는 필요하지 않다.

③ SP 정의시 body는 BEGIN END 로 묶지 않아도 된다. 그냥 AS 뒤에 T-SQL이 오면 된다(사용자 함수 정의와 차이점 이다. 함수는 BEGIN END 사이에 정의 되어야 한다).

④ SP가 정보를 되돌릴 수 있는 방법은 3가지 이다.

  1. ResultSet (SELECT 문장 또는 다른 SP 호출로 가능)

  2. OUTPUT 파라미터

  3. 리턴값

  리턴값은 정수만 가능 하다. 리턴값이 설정되어 있지 않고 그냥 RETURN 만 하는 경우 기본값인 0을 되돌려 준다.

  SP호출시 리턴값은 다음과 같은 식으로 전달 받을 수 있다.

  DECLARE @intRet INT

  EXECUTE @intRet = Proc1 Pram1

⑤ SP에 매개변수값을 넘기는 방법에는 위치 매개변수 전달과 이름 매개변수 전달 방법이 있다.

    전자의 경우 SP에서 정의된 파라미터의 순서와 일치하게 넘기는 것이고 후자의 경우 @를 포함한 파라미터이름을 지정하여 넘기는 것이다.

    이름 매개변수 전달은 다음과 같은 식으로 한다.

   EXEC Proc1 @Param1 = 100, @Param2 = 'FOO'

⑥ SP 재컴파일

  때때로 Index 생성등을 통해 실행계획이 변경될 것을 예상 했으나 기존의 실행계획을 계속 유지하는 경우가 있다. 이럴 때는 SP를 재컴파일 해주면 새로운 실행계획이 반영될 수 있다.

특정 SP를 재컴파일 하기 위해서는 다음과 같이 한다.

  EXEC sp_recompile SP명

그런데 SP의 매개변수 값에 따라 데이터 분포도가 차이가 많이 나는 경우 하나의 실행계획으로만 처리되면 성능이 나오지 않는 경우가 있다. 이때는 다음과 같이 WITH RECOMPILE 옵션을 이용해서 SP를 정의하면 SP가 호출 될 때마다 매번 실행계획을 다시 수립하여 성능이 향상 될 수 있다.

CREATE PROC SP명

WITH RECOMPILE

AS

⑦ SP명 바꾸기

  ALTER PROC 으로는 SP명을 변경할 수 없다. 이를 때 sp_rename 프로시저를 호출하여 SP명을 변경하면 된다.

  EXEC sp_rename SP명1, SP명2

⑧ SP 종속(의존) 객체 확인

  sp_depends 를 이용하면 특정 SP에서 사용하고 있는 테이블, 컬럼등의 객체정보를 확인 할 수 있다.

  EXEC sp_depends SP명

⑨ 긴 SP 내용 검색

  SP 소스코드는 syscomments 시스템 테이블에 기록되는데, SP 소스 크기가 8000바이트 이상인 경우 2개 이상의 row로 소스가 분할 해서 저장이 된다. 그래서 여러 문자를 AND 로 LIKE 검색 하는 경우 제대로 해당하는 SP를 찾지 못할 수 있다. 이럴 때 다음과 같이 하면 원하는 SP를 찾을 수 있다. 

SELECT a.name

FROM sysObjects a

    INNER JOIN (    

        SELECT 

            id , STUFF ((

                SELECT ',' + text

                FROM sysComments y

                WHERE y.id = x.id

                FOR XML PATH ('')

                ) , 1,1 , '') AS AllText

        FROM  syscomments x

        GROUP BY id

    ) b

    ON a.id = b.id

WHERE    

    a.xtype = 'P'

    AND b.AllText Like '%문자열1%'

    AND b.AllText Like '%문자열2%'

SQL서버의 트리거에서는 UPDATE() 함수를 이용해서 컬럼이 수정되었는 지를 확인 할 수 있는데,

해당 함수는 실제로 값이 변경되었는지가 아니라 단순히 UPDATE 구문의 SET 에 해당 컬럼이 사용되었는 지를

나타낸다고 보는게 더 맞는것 같습니다.

아래와 같이 테스트를 해 보았습니다.

우선 테스트용 테이블을 만들고 트리거를 작성 합니다.

CREATE TABLE TriggerTest
(id int identity Primary Key,
 prodName varchar(10),
 Spec varchar(10),
 Qty int
)
go

CREATE TRIGGER TR_TriggerTest_AU 
ON TriggerTest
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
   IF UPDATE(Qty) 
   BEGIN
        SELECT 'Qty is updated', *
        FROM inserted
   END

   SELECT
        'Qty is modified', a.*
   FROM 
    inserted a
        LEFT JOIN deleted b
            ON a.id = b.id
   WHERE
    a.Qty <> b.Qty
END
go

 
insert into TriggerTest
 values ('Prod1', '2*3', 100)

 insert into TriggerTest
 values ('Prod2', '10*3', 200)

 insert into TriggerTest
 values ('Prod3', '8*5', 300)
 go

 insert into TriggerTest
 values ('Prod4', '15*10', 400)
 go

이 때 트리거에 의해서 다음과 같이 출력 되는걸 확인 할 수 있는데

이번에는 아래와 같이 트리거에서 체크하지 않는 컬럼에 대하여 UPDATE를 시도 해 보면

UPDATE 구문에 Spec 컬럼만 있기 때문에 트리거에서 아무것도 처리되고 있지 않습니다.

다음으로 트리거에서 체크 하고 있는 Qty 컬럼에 대하여 UPDATE를 해보면 당연히 다음과 같이 조회가 됩니다.

위에서는 Qty 컬럼의 값을 실제로 변경 시키고 있는데, 이번에는 컬럼 값을 동일한 값으로 UPDATE 해 보겠습니다.

실무적으로는 UPDATE용 SP를 작성 해 놓고 해당 SP에 들어오는 인자 값을 이용해서 UPDATE 구문을 수행하는 경우가 많습니다.

이때 해당 테이블의 모든 컬럼을 UPDATE 구문에 포함하게 되어서 실제로 값이 변경된 컬럼이 아닌 컬럼도 (수정 전후가 동일한 값으로) UPDATE 처리가 되기 때문에 이 경우를 주의 깊게 봐야 할 것 입니다.

결과를 보면 Qty 컬럼은 수정 전후 값이 동일하지만 UPDATE() 함수는 true 값을 반환 했다는 것을 알 수 있습니다.

결론은, 트리거 내에서 특정 컬럼의 수정여부(실제로 값의 변화가 있는 지)를 확인 하려면 UPDATE() 함수를 사용해서는 알 수가 없고 inserted 테이블 과 deleted 테이블을 PK로 JOIN 하여 두 테이블간 컬럼의 값을 서로 비교해야 한다는 것 입니다.

이 방법 말고 더 좋은 방법을 알고 계신 분은 댓글 부탁 드립니다.

어떤 테이블을 삭제하려고 할 때 "FOREIGN KEY 제약 조건에서 참조하므로 삭제할 수 없습니다" 라는 오류가 나면서

삭제가 안되는 경우가 있다. 이는 해당 테이블을 외래키로 참조하는 다른 테이블이 존재 하기 때문 이다.

그런데 삭제 하려는 테이블 자체에 대한 외래키 제약조건은 sp_helpconstraint 로 확인 할 수 있으나,

해당 테이블을 참조하는 다른 테이블은 sp_helpconstraint 로는 알 수가 없다.

이럴때는 다음과 같이 하면 참조하고 있는 다른 테이블과 연결된 컬럼명을 확인 할 수 있다.

SELECT 
   f.name,
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = '테이블명'