당당한 커피캣

declare @day datetime

set @day = '2/7/2007'

SELECT DATEADD(dd, 2-DATEPART(dw, @day), @day) as mondaydate

-- 현재달의 첫째날 

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

-- 현재주의 월요일

SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)

-- 현재해의 첫째날

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

-- 현재분기의 첫째날

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

-- 현재날의 자정주의 구하기

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

-- 지난달의 마지막날 구하기

SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

-- 지난해의 마지막날 구하기

SELECT DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))

-- 현재달의 마지막날 

SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))

-- 현재해의 마지막날

SELECT DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0))

-- 현재달의 첫째주 월요일

SELECT DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, 6-DATEPART(day, GETDATE()), GETDATE())), 0)

SELECT * INTO [생성될 테이블 명] FROM [원본 테이블 명] [WHERE (옵션)];

 * 옵션 :  '1' = '2'; (데이터 없이 테이블 구조만 복사)

              '1' = '1'; (데이터까지 복사)

  예) SELECT * INTO copy_table FROM original_table WHERE '1' = '1'  -- (데이터까지 모두 복사)

TRY CATCH을 이용하여 오류를 처리해보자

BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
    BEGIN TRY
        BEGIN TRAN
 
        /* 쿼리 작업 */
 
        SELECT '성공'
        COMMIT TRAN;
 
    END TRY
    BEGIN CATCH
    
        DECLARE @ErrorNumber    INT
        DECLARE @ErrorSeverity  INT
        DECLARE @ErrorState     INT
        DECLARE @ErrorProcedure NVARCHAR(MAX)
        DECLARE @ErrorLine      INT
        DECLARE @ErrorMessage   NVARCHAR(MAX)
 
        SELECT  @ErrorNumber    = ERROR_NUMBER(),   -- 오류 번호
                @ErrorSeverity  = ERROR_SEVERITY(), -- 오류 심각도
                @ErrorState     = ERROR_STATE(),    -- 오류 상태 번호
                @ErrorProcedure = ERROR_PROCEDURE(),-- 오류 발생 프로시저
                @ErrorLine      = ERROR_LINE(),     -- 오류 발생 행 번호
                @ErrorMessage   = ERROR_MESSAGE();  -- 오류 메세지
 
        SELECT '에러발생'
        IF XACT_STATE() <> 0 ROLLBACK TRAN;
 
    END CATCH
 
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
END

쿼리 실행 중 에러 없이 정상 실행시

TRY안에서 COMMIT TRAN과 함께 쿼리가 성공적으로 실행된다.

그러나 쿼리 실행 중 에러가 발생한다면,

CATCH로 빠지게 되고, 해당 에러를 파악 후 ROLLBACK TRAN으로 해당 쿼리를 롤백시킨다.

XACT_STATE()

1 : 활성상태의 커밋 가능한 트랜잭션 존재

-1 : 활성상태의 커밋 불가능한 트랜잭션 존재

0 : 비 활성 트랜잭션

에러 메세지 예)

출처: http://ggmouse.tistory.com/206?category=938079 [초보개발자꽁쥐]

 데이터를 암호화해보자

1. PWDENCRYPT

암호화

PWDENCRYPT('암호화할 문자')

SELECT PWDCOMPARE('비교 대상 문자', '암호화된 Hash값')

SELECT PWDENCRYPT('123')

DECLARE @pwd VARBINARY(MAX);
SET @pwd = PWDENCRYPT('123');
 
SELECT PWDCOMPARE('123', @pwd)
SELECT PWDCOMPARE('12345', @pwd)

HASHBYTES('암호화알고리즘', '암호화할 문자')

SELECT HASHBYTES('SHA2_512', '123')

▶ 0x3C9909AFEC25354D551DAE21590BB26E38D53F2173B8D3DC3EEE4C047E7AB1C1EB8B85103E3BE7BA613B31BB5C9C36214DC9F14A42FD7A2FDB84856BCA5C44C2

DECLARE @pwd VARBINARY(MAX);
SET @pwd = HASHBYTES('SHA2_512', '123')
 
IF (@pwd = HASHBYTES('SHA2_512', '123'))
    SELECT 1
ELSE
    SELECT 0

▶ 1 (true)

 효율적인 동적쿼리 실행을 위해 sp_executesql 저장 프로시저를 사용해보자

▶ sp_executesql 저장 프로시저는 매개 변수 대체를 지원하기 때문에 EXECUTE보다 융통성이 뛰어나다.

▶ sp_executesql은 SQL Server에서 재사용이 쉬운 실행 계획을 생성하므로 sp_executesql이 EXECUTE 문보다 효율적이다.

CREATE PROCEDURE [dbo].[USP_TEST_PROC]
    @in_Name VARCHAR(20) = '',
    @in_Age INT = ''
AS
BEGIN
 
    DECLARE @query VARCHAR(MAX);
    DECLARE @whereQuery VARCHAR(MAX);
 
    SET @query = '';
    SET @whereQuery = '';
 
    IF (@in_Name <> '')
        BEGIN
            SET @whereQuery = N' AND A.Name = ''' + @in_Name + ''' '
        END
 
    IF (@in_Age > 0)
        BEGIN
            SET @whereQuery = N' AND A.Age = ''' + @in_Age + ''' '
        END
 
    SET @query = N'SELECT *
                    FROM TBL_TEST_TABLE A
                        INNER JOIN TBL_TEST_TABLE2 B ON A.ID = B.ID
                    WHERE 1=1'
 
    /*****************************************************************/
 
    -- 1. EXECUTE 사용
    EXEC (@query + @whereQuery)
 
    -- 2. sp_executesql 사용
    DECLARE @SQLString NVARCHAR(MAX);
    DECLARE @ParmDefinition NVARCHAR(500);
 
    SET @SQLString = @query + @whereQuery;
 
    SET @ParmDefinition = N'@in_Name    VARCHAR(20),
                            @in_Age        INT'
 
    EXECUTE sp_executesql
        @SQLString,
        @ParmDefinition,
        @in_Name = @in_Name,
        @in_Age = @in_Age
 
END

https://technet.microsoft.com/ko-kr/library/ms175170(v=sql.105).aspx

시퀀스를 이용해서 별도의 유니크한 번호를 생성해보자

시퀀스 생성

CREATE SEQUENCE [schema_name . ] sequence_name  
    [ AS [ built_in_integer_type | user-defined_integer_type ] ]  
    [ START WITH <constant> ]  
    [ INCREMENT BY <constant> ]  
    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ <constant> ] } | { NO CACHE } ]  
    [ ; ] 

다음과 같은 정수형 시퀀스를 생성해보자

CREATE SEQUENCE [dbo].[SEQ_TEST_NO] 
AS [int]                -- 정수형
START WITH 0            -- 0 부터 시작
INCREMENT BY 1          -- 1씩 증가 (-1일 경우 1씩 감소)
MINVALUE -2147483648    -- 최소값
MAXVALUE 2147483647     -- 최대값
CACHE

이제, 생성한 시퀀스를 사용해보자

SELECT NEXT VALUE FOR 시퀀스명

SELECT NEXT VALUE FOR SEQ_TEST_NO    -- 0
SELECT NEXT VALUE FOR SEQ_TEST_NO    -- 1
SELECT NEXT VALUE FOR SEQ_TEST_NO    -- 2

[출처]

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/create-sequence-transact-sql

출처: http://ggmouse.tistory.com/214?category=938079 [초보개발자꽁쥐]

EXEC sp_lock

SPID : 잠금을 요청하는 프로세스의 세션 ID

DBID : 데이터베이스의 식별번호

OBID : 개체의 식별번호

INDLD : 인덱스의 식별번호

TYPE : 잠금유형

RESOURCE : 리소스 식별값

MODE : 잠금

STATUS : 잠금요청상태

MODE

S : 공유 잠금

U : 업데이트 잠금

X : 독점 잠금

IS : 의도 공유

IU : 의도 업데이트

IX : 독점 의도

BU : 대량 업데이트

DBCC inputbuffer (spid값)

EXEC sp_who (spid값)

kill (spid값)

출처: http://ggmouse.tistory.com/214?category=938079 [초보개발자꽁쥐]