bedbmsguru

비슷한 일을 하는 쿼리를 만들어야 할때 참고할 것
혹시 이글 보시고 더 좋은 방법 아시는분은 알려주세요^^;;

DECLARE CHECK_COLUMN CURSOR FOR
select OBJECT_NAME(object_id) AS table_name, object_id  from sys.columns where system_type_id in(231, 167) and object_id > 100
grOUP BY object_id

DECLARE @table_name AS VARCHAR(100)
DECLARE @table_id AS BIGINT

OPEN CHECK_COLUMN
    FETCH NEXT FROM CHECK_COLUMN INTO @table_name, @table_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @RESULT_QUERY AS VARCHAR(2000)
       
        SET @RESULT_QUERY = 'SELECT TOP 1 '''  + @table_name + ''', '
        DECLARE PROCESS_COLUMN CURSOR FOR
            select name from sys.columns where system_type_id in(231, 167) and object_id = @table_id
           
        DECLARE @column_name AS VARCHAR(100)
        DECLARE @FkFirstColumn BIT SET @FkFirstColumn = 1
        OPEN PROCESS_COLUMN
        FETCH NEXT FROM PROCESS_COLUMN INTO @column_name
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF(@FkFirstColumn = 1)
                SET @FkFirstColumn = 0
            ELSE
                SET @RESULT_QUERY = @RESULT_QUERY + ', '       
            SET @RESULT_QUERY = @RESULT_QUERY + @column_name
           
            FETCH NEXT FROM PROCESS_COLUMN INTO @column_name
        END
        CLOSE PROCESS_COLUMN
        DEALLOCATE PROCESS_COLUMN
    SET @RESULT_QUERY = @RESULT_QUERY + ' FROM ' + @table_name
    PRINT @RESULT_QUERY
    FETCH NEXT FROM CHECK_COLUMN INTO @table_name, @table_id
    END
CLOSE CHECK_COLUMN
DEALLOCATE CHECK_COLUMN

DECLARE cIX CURSOR FOR
   SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
      FROM Sys.Indexes SI
         LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
      WHERE TC.CONSTRAINT_NAME IS NULL
         AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
      ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @IXSQL NVARCHAR(4000) --SET @PKSQL = ''
   SET @IXSQL = 'CREATE '

   -- Check if the index is unique
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
      SET @IXSQL = @IXSQL + 'UNIQUE '
   -- Check if the index is clustered
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
      SET @IXSQL = @IXSQL + 'CLUSTERED '

   SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

   -- Get all columns of the index
   DECLARE cIxColumn CURSOR FOR
      SELECT SC.Name
      FROM Sys.Index_Columns IC
         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
      WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
      ORDER BY IC.Index_Column_ID

   DECLARE @IxColumn SYSNAME
   DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

   -- Loop throug all columns of the index and append them to the CREATE statement
   OPEN cIxColumn
   FETCH NEXT FROM cIxColumn INTO @IxColumn
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      IF (@IxFirstColumn = 1)
         SET @IxFirstColumn = 0
      ELSE
         SET @IXSQL = @IXSQL + ', '

      SET @IXSQL = @IXSQL + @IxColumn

      FETCH NEXT FROM cIxColumn INTO @IxColumn
   END
   CLOSE cIxColumn
   DEALLOCATE cIxColumn

   SET @IXSQL = @IXSQL + ')'
   -- Print out the CREATE statement for the index
   PRINT @IXSQL

   FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

declare foreignkey_make_cursor cursor for
    select name, parent_object_id, referenced_object_id, delete_referential_action, update_referential_action from sys.foreign_keys

declare @foreignkey_name as varchar(100)
declare @current_table_id as int
declare @refrenced_table_id as int
declare @delete_refrencial_action as tinyint
declare @update_refrencial_action as tinyint

open foreignkey_make_cursor
fetch next from foreignkey_make_cursor    into @foreignkey_name, @current_table_id, @refrenced_table_id, @delete_refrencial_action, @update_refrencial_action
while (@@fetch_status = 0)
begin
    declare @result_query  as varchar(3000)
    set @result_query ='alter table ' + object_name(@current_table_id)
    set @result_query = @result_query  + ' add constraint ' + @foreignkey_name + ' foreign key '
    set @result_query = @result_query  + '('
   
    --foreign key 에  들어갈 column 을 불러오는 커서
    declare process_column_cursor cursor for
        select b.name from sys.foreign_key_columns as a
        inner join sys.columns as b
        on a.parent_object_id = b.object_id
        and a.parent_column_id = b.column_id
        where parent_object_id = @current_table_id

        declare @fkcolumn sysname
        declare @fkfirstcolumn bit set @fkfirstcolumn = 1

        open process_column_cursor
        fetch next from process_column_cursor into @fkcolumn
        while (@@fetch_status = 0)
        begin
            if (@fkfirstcolumn = 1)
                set @fkfirstcolumn = 0
            else
                set @result_query = @result_query + ', '

            set @result_query = @result_query + @fkcolumn

            fetch next from process_column_cursor into @fkcolumn
        end
        close process_column_cursor
        deallocate process_column_cursor
    --###########process_column_cursor 커서의 끝###############################
          
    set @result_query = @result_query  + ') '
    set @result_query = @result_query  + 'references ' + object_name(@refrenced_table_id) +'('
   
    --원본테이블에서 참조되고 있는 column 을 불러오는 커서   
    declare process_rdcolumn_cursor cursor for
        select b.name from sys.foreign_key_columns as a
        inner join sys.columns as b
        on a.referenced_object_id= b.object_id
        and a.referenced_column_id = b.column_id
        where referenced_object_id = @refrenced_table_id

        declare @rdcolumn sysname
        declare @rdfirstcolumn bit set @rdfirstcolumn = 1

        open process_rdcolumn_cursor
        fetch next from process_rdcolumn_cursor into @rdcolumn
        while (@@fetch_status = 0)
        begin
            if (@rdfirstcolumn = 1)
                set @rdfirstcolumn = 0
            else
                set @result_query = @result_query + ', '

            set @result_query = @result_query + @rdcolumn
            fetch next from process_rdcolumn_cursor into @rdcolumn
        end
    close process_rdcolumn_cursor
    deallocate process_rdcolumn_cursor
    --###########process_rdcolumn_cursor 커서의 끝###############################

    set @result_query = @result_query  + ')'
   
    if(@delete_refrencial_action = 0)
        set @result_query = @result_query + ' on delete no action'
    else if(@delete_refrencial_action = 1)
        set @result_query = @result_query + ' on delete cascade'
    else if(@delete_refrencial_action = 2)
        set @result_query = @result_query + ' on delete set null'
    else if(@delete_refrencial_action = 3)
        set @result_query = @result_query + ' on delete set default'
   
       
       
       
    if(@update_refrencial_action = 0)
        set @result_query = @result_query + ' on update no action'
    else if(@update_refrencial_action = 1)
        set @result_query = @result_query + ' on update cascade'
    else if(@update_refrencial_action = 2)
        set @result_query = @result_query + ' on update set null'
    else if(@update_refrencial_action = 3)
        set @result_query = @result_query + ' on update set default'
   
    print @result_query
    fetch next from foreignkey_make_cursor    into @foreignkey_name, @current_table_id, @refrenced_table_id, @delete_refrencial_action, @update_refrencial_action
end
close foreignkey_make_cursor
deallocate foreignkey_make_cursor

MEMORY

1)Memory
    Available Bytes            System dependent
    Pages/sec             평균값50이하   
    Page Faults/sec Counters    기준값과비교

2)SQL Server:Buffer Manager
    Buffer Cache Hit Ratio        평균값이 90%이상이어야함
    Page Life Expectancy        평균값이 300이상
    Checkpoint Pages/sec        평균값이 30이하
    Lazy writes/sec            평균값이 20이하

3)SQL Server:Memory Manager
    Memory Grants Pending        평균값이 0이어야 함
    Target Server Memory (KB)     물리적메모리와 비슷해야함
    Total Server Memory (KB)    Targer Memory와 비슷해야함


DISK
% Disk Time             평균값이 85% 이하여야함
Current Disk Queue Length    디스크당 평균값이 2 이하여야함
Disk Transfers/sec        디스크당 최고값이 100 이하여야함
Disk Bytes/sec            초당최고값이 10MB 이하여야함
Avg. Disk Sec/Read         평균값이 10ms 이하여야함
Avg. Disk Sec/Write        평균값이 10ms 이하여야함


Processor

% Processor Time         평균값이 80% 이하여야함
% Privileged Time        평균값이 10% 이하여야함
Processor Queue Length        평균값이  2 이하여야함
Context Switches/sec        평균값이 1000 이하여야함
Batch Requests/sec       
SQL Compilations/sec        평균값이 100 이상이어야함
SQL Recompilations/sec

A

Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]

Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]

Unique Index와 Unique constraint 는 내부적으로 같은 방법을 사용한다.

Unique constraint 도 unique index를 만들어서 중복을 막는다.

Unique Index와 Unique Constraint의 차이는 Unique Index가 fillfactor를 지정할 수 있다는 점이다.


d

Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]

Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]

Unique Index와 Unique constraint 는 내부적으로 같은 방법을 사용한다.

Unique constraint 도 unique index를 만들어서 중복을 막는다.

Unique Index와 Unique Constraint의 차이는 Unique Index가 fillfactor를 지정할 수 있다는 점이다.


Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]

Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]

Unique Index와 Unique constraint 는 내부적으로 같은 방법을 사용한다.

Unique constraint 도 unique index를 만들어서 중복을 막는다.

Unique Index와 Unique Constraint의 차이는 Unique Index가 fillfactor를 지정할 수 있다는 점이다.


d Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]

Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]

Unique Index와 Unique constraint 는 내부적으로 같은 방법을 사용한다.

Unique constraint 도 unique index를 만들어서 중복을 막는다.

Unique Index와 Unique Constraint의 차이는 Unique Index가 fillfactor를 지정할 수 있다는 점이다.


Add Unique Constraint
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]

Add Unique Index
CREATE UNIQUE NONCLUSTERED INDEX
<namingconventionconstraint> ON dbo.<tablename>
(
<columnname>
) ON [PRIMARY]

Unique Index와 Unique constraint 는 내부적으로 같은 방법을 사용한다.

Unique constraint 도 unique index를 만들어서 중복을 막는다.

Unique Index와 Unique Constraint의 차이는 Unique Index가 fillfactor를 지정할 수 있다는 점이다.


exec 프로시저이름 을 하게 되면 프로시저를 batch의 중간에서도 실행가능하다.
exec가 없을경우 다른 쿼리와 함께 실행해야할 경우 문장의 제일 처음에 procedure가  와야 한다.

1.가독성을 높이기 위해 SQL키워드는  대문자로 작성한다.

2.문법은 ANSI 92에 맞춰서 작성한다

3.가능한한 변수는 적게 사용한다.
4.다이나믹 SQL은 실행시 마다 다시 컴파일을 하게 되므로 적게 사용하는 것이 좋다.

6.SET NOCOUNT ON 해둘것

7.sp_ 로 시작하도록 작성하지말것
    sp_는 시스템 저장 프로시저이기 때문에 sp_로 시작하는 프로시저는
    master디비를 조회하는 추가적 부담이 발생하고 만약 master디비에 같은 이름의
    프로시저가 있다면 이용자가 작성한 프로시저는 실행되지 않는다.

8. sp_executeSQL and the KEEPFIXED PLAN options
    다이나믹 쿼리엣 프로시저를 다시 컴파일 하지 않도록 한다.

9. 변수값을 저장할때는 하나의 select가 여러개의 set보다 낫다.
    SELECT @Var1 = @Var1 + 1, @Var2 = @Var2 - 1 <--이런식으로 사용할 것
    --안좋은 방법
    SET @Var1 = @Var1 + 1
    SET @Var2 = @Var2 - 1

10. Where 필터링시 성능순서 --> (  =, >, <, >=, <=, <>, !=, !>, !< )
    가장왼쪽이 가장 성능이 좋음

11. boolean형 비교는 in 이나 not in 보다 exists 나 not exists를 사용할것
    exist는 boolean형으로 비교하나 in은 일단 조건에 해당하는 모든값을 가져온다.

12. CONVERT 대신에 CAST를 사용하라.
    CAST가 ANSI-92표준이다. CONVERT는 MSSQL에서만 사용한다.
    Datetime에서 형식을 지정해줄때는 Convert를 사용하라 CAST로는 불가능하다.

13. 필요하지 않다면 DISTINCT나 ORDER BY를 사용하지 마라
    DBMS 퍼포먼스에 오버헤드가 발생한다.

14. CURSOR 사용은 피하라

15. SELECT 시에 필요한 칼럼만 지정하고 * 를 사용하지 마라

16. CREATE TABLE vs. SELECT INTO
    데이타가 적은 테이블에 대해서는 관계가 없지만 데이타가 많은 테이블에 대해서

17.임시테이블 대신 테이블 변수를 활용하라
    임시테이블은 프로시저를 다시 컴파일 하지만 테이블 변수는  프로시저를 다시 컴파일 하지
    않는다.

트리거(TRIGGER)

데이타 베이스 서버에서 특정 이벤트의 결과로 자동으로 수행되는 루틴
트리거는 트랜잭션의 일부로 트리거가 완료 되어야 트랜잭션도 왼료된것이다.
트리거에서 롤백이 일어날경우 트리거뿐만 아니라 원 트랜잭션도 롤백시킴
트리거를 사용해서 무결성 강화를 하는 것보다 제약조건을 통해서 무결성
강화를 할 수 있다면 제약조건을 통해서 하는편이 더 좋음

AFTER 트리거
SQL 명령이 수행된 후에 실행되는 트리거

1.Permanent Table에서만 생성가능 (VIEW나 임시테이블에서는 불가능)
2.테이블에 정의된 제약조건이 변경또는 취소되면 트리거는 작동하지 않음
   실행시 행단위가 아닌 하나의 문장 단위로 호출
3.즉 쿼리가 실행되었을때 몇개의 행이 영향을 받았느냐에 관계없이 한번만 실행됨
4.여러개의 Trigger가 존재할 경우 Trigger 들은 순차적으로 하나씩 실행된다.
  처음과 마지막에 실행될 Trigger 는 sp_settriggerorder로정할수 있지만
  그 이외의 Trigger들은 순서를 정할 수 없다.

Inserted 와 deleted

새로 변경될 이미지와 삭제되기전 이미지를 가지고 있는 특수 테이블
테이블의 스키마는 Trigger가 정의된 테이블과 같은 구조를 가진다.

SQL2000에서는 트랜잭션로그를 이용한 VIEW였기 때문에 집중적인 Trigger 작업은 트랜잭션 로그의 병목현상을 유발할 수 있었음
SQL2005에서는 tempdb의 row versioning 으로 구현하여 트랜잭션 병목현상은 없으나 tempdb의 부하를 유발할 수 있다.

@@rowcount 를 이용해서 몇개의 행이 영향을 받았는지 확인하고 이에 따라 분기 처리가 가능하다.

Ex) CREATE TRIGGER TR_TEST ON TABLE1 AFTER INSERT
    AS
    (
    SET @rc = @@rowcount;
         if @rc = 0 --영향 받는 행이 없음
       RETURN
    if @rc = 1   --단일행
    begin
     작업처리
    end
   
    if @rc > 1   
    begin
      작업처리
    end
     )
CF) AFTER Trigger를 정의하기위해서는 AFTER 이외에도 FOR 를 사용할 수도 있다.

트리거 (비)활성화
Alter TABLE 테이블이름  ENABLE TRIGGER 트리거이름  --활성화
Alter TABLE 테이블이름  DISABLE TRIGGER 트리거이름 --비활성화


트리거의 중첩(Nested)및 재귀(Recursive)
중첩: 한트리거가 호출된 결과로 다른 트리거가 호출
재귀: 트리거가 자기자신을 직접하출 하든지 다른 트리거를 통해서 자기 자신이 계속 호출될때

재귀는 무한루프를 방지하기 위해서 32단계까지만 지원(32는하드코딩되어있음)한다 33번째 재귀호출을 할려고 하면 모든 작업이 롤백된다

UPDATE함수및 COLUMNS_UPDATED

1.Update함수
특정 column이 업데이트 되었는가를 확인할때 사용하는 방법
if Update(칼럼) --특정 칼럼 한개가 업데이트 되었는지 확인

2.COLUMNS_UPDATE 함수 다시 잘 볼것
각각의 칼럼을 bit로 가지는 이진문자열 반환 칼럼이 변경된경우 1 그렇지 않은경우 0 설정
칼럼의 순서는 왼쪽에서 오른쪽 가장왼쪽 byte는 처음8개의 칼럼 (1~8까지) 두번째는 그다음 8개의칼럼(9~16)
 

INSTEAD OF 트리거
대상 개체에 일어나야 할 원래의 변경작업 대신에 수행된다.(대상개체의 변경작업이 일어나지 않으므로 BEFORE 트리거가 아니다.)
INSTEAD OF 트리거에서 inserted나 deleted 테이블은 변경되어야 할 데이타를 담고 있다.
뷰에서도 생성이가능하고 재귀호출을 허용하지 않는다.


DDL트리거
데이타베이스에서 일어나는 DDL이벤트에 대해 반응
규칙에 맞지 않는 스키마의 변경을 롤백하거나 스키마의 변경을 감시할 수 있다.
(AFTER트리거만 지원)

실행계획이 다시 컴파일 되는 경우

쿼리가 참조하는 테이블의 스키마가 변경되는 경우
쿼리가 참조하는 인덱스가 변경되거나 없어지는 경우
쿼리가 참조하는 통계정보가 업데이트 되는경우
sp_recompile 메소드가 호출되는 경우
트리거가 있는 테이블에 상당한 양의 insert나 delete가 호출되는 경우
하나릐 쿼리에 DDL과 DML이 같이 포함되어 있는 경우
쿼리에서 set 옵션을 변경하는경우
클라인트쪽 커서를 사용하고 있는데 FOR BROWSE 옵션이 변경되었을때 (이게 먼말이여.ㅡ.ㅡ)
쿼리에서 커서 옵션을 변경하였을 경우

--Plan Cache 날리는법

DBCC FREEPROCCACHE