bedbmsguru

Function
(1)특징
-에러가 발생하면 Function은 중지된다.
-Function은 독립적으로 실행할 수 없다.
     -->Select, From, WHERE 절과 같은 쿼리 문장에서 호출되어 사용된다.
-에러처리(TRY...CATCH) 사용불가
    -->다른 SQL 문에 종속되어 호출 되기 때문에 에러 처리 불가능 에러처리를 하려면 함수를 호출한 SQL문에 대       하여 에러 처리를 해야한다.
-Function 내부에서는 데이타 갱신 작업이 불가능
    -->외부 테이블에 Insert, Delete, Update, DDL쿼리등이 불가능 단 함수내의 개체에서는 가능함
       
(2)종류
-스칼라 함수 int, nvarchar, bit... 등의 스칼라 데이타 타입을 Return 한다.
-테이블 함수 Table 을 Return 한다.

    CF)테이블함수와 뷰를 통한 결과값 반환과의 차이'
    뷰에서는 조건을 지정할 수 없는 고정된 SELECT 문만 사용가능 조건에 따라 다른 결과를 출력하게 할려면 여    러개의 뷰가 필요함 테이블 함수는 한개의 테이블 반환 함수로 가능
    CF2)테이블 함수와 저장프로시저 와의 차이
    저장 프로시저를 통한 테이블은 테이블중 일부 칼럼만 가져오는 등의 작업이 불가능하기 때문에
    결과 테이블을 다른 테이블과 Joiin 하는등 여러가지 이용이 불가능하다.

(3)테이블 함수의 장점
저장프로시저 처럼 외부 사용자의 요청을 파라미터 받아 다양한 형태의 테이블 만드는 것이 가능하고
SELECT FROM에서 호출 되기 때문에 원하는 필드만 출력도 가능하고 JOIN도 가능하다.


(4)테이블 함수의 단점
-여러개의 결과 테이블을 반환 불가능
-함수 내부에서 업데이트 작업 불가능(저장프로시저는 업데이트 하다가 결과 출력도 가능하고 다른작업도 가능)
-동적 Execute 함수로 테이블 반환 불가능 Exec(SELECT * FROM TABLE 불가능)

/*****

주 서버 로그 전달 데이터베이스, 백업 폴더 및 공유, 백업 보유 기간, 백업 작업, 최종 백업 시간 및 파일 정보

******/
SELECT primary_database, backup_directory, backup_share, backup_retention_period AS backup_retention_minutes,
       j.name AS backup_job_name, monitor_server, last_backup_file, last_backup_date
FROM     msdb.dbo.log_shipping_primary_databases pd
JOIN msdb.dbo.sysjobs j ON j.job_id = pd.backup_job_id
order by last_backup_date
GO


/*****

주 서버 로그 전달 데이터베이스에 대한 보조 서버 및 데이터베이스 정보

******/
/*****

주 데이터베이스 모니터링 이력 정보

******/
SELECT pd.primary_database,
        CASE agent_type WHEN 0 THEN 'Backup' WHEN 1 THEN 'Copy' WHEN 2 THEN 'Restore' END AS agent_type,
        database_name,
    CASE session_status WHEN 0 THEN 'Starting' WHEN 1 THEN 'Running' WHEN 2 THEN 'Success'
    WHEN 3 THEN 'Error' WHEN 4 THEN 'Warning' END AS session_status,
log_time, message
FROM     msdb.dbo.log_shipping_monitor_history_detail hd
JOIN msdb.dbo.log_shipping_primary_databases pd ON pd.primary_id = hd.agent_id
WHERE   log_time >= CAST(CONVERT(CHAR(10), getdate(), 120) AS DATETIME)
ORDER BY pd.primary_database, log_time
GO

/*****

주 데이터베이스 상세 오류 정보

******/

SELECT pd.primary_database,
 CASE agent_type WHEN 0 THEN 'Backup' WHEN 1 THEN 'Copy' WHEN 2 THEN 'Restore' END AS agent_type,
 database_name, log_time, message
    FROM     msdb.dbo.log_shipping_monitor_error_detail ed
    JOIN msdb.dbo.log_shipping_primary_databases pd ON pd.primary_id = ed.agent_id
    WHERE   log_time >= CAST(CONVERT(CHAR(10), getdate(), 120) AS DATETIME)
    ORDER BY pd.primary_database, log_time

 
/*****

보조 서버에 대한 로그 전달 데이터베이스, 백업 원본 및 대상 폴더, 백업 파일 보유 기간, 백업 파일 복사 및 복원 작업, 최종 복원 시간 정보

******/
SELECT s.primary_server, s.primary_database, last_restored_date , backup_source_directory, backup_destination_directory,
                           file_retention_period AS file_retention_minutes,
                           j1.name AS copy_job_name, j2.name AS restore_job_name, monitor_server
FROM     msdb.dbo.log_shipping_secondary s
                           JOIN msdb.dbo.sysjobs j1 ON j1.job_id = s.copy_job_id
                           JOIN msdb.dbo.sysjobs j2 ON j2.job_id = s.restore_job_id
                           JOIN msdb.dbo.log_shipping_monitor_secondary ms ON ms.secondary_id = s.secondary_id
            
order by last_restored_date
GO 

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

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.임시테이블 대신 테이블 변수를 활용하라
    임시테이블은 프로시저를 다시 컴파일 하지만 테이블 변수는  프로시저를 다시 컴파일 하지
    않는다.