멍콩 스토리

UTC Time 값을 조회하는 쿼리

SELECT GETUTCDATE() AS UTC_TIME

UTC Time값을 저장한 Datatime 변수를 Local Time으로 조회하는 함수

DECLARE @DT1 DATETIME
      , @DT2 DATETIME

    SELECT @DT1 = GETUTCDATE()    
	
    SELECT @DT1    // 출력값 : 2025-02-26 08:24:22.130

    SELECT @DT2 = SWITCHOFFSET(CONVERT(DATETIMEOFFSET, @DT1),DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))
	
    SELECT @DT2    // 출력값 : 2025-02-26 17:24:22.130

아래 스택 오버플로우 글을 참고함.

https://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement

SSMS에서 임시테이블을 사용하여 데이터를 확인하는 쿼리를 사용하다보면,

임시테이블을 제거(DROP)하고 다시 생성해서 사용해야하는 경우가 있다.

쿼리의 마지막 라인에 사용한 임시테이블을 제거(DROP)하는 방법으로 쿼리 작성도 가능하지만

실행 시 임시테이블이 있는지 확인하고 있다면 지우고 다시 생성하도록 하는 방법도 있다.

    SELECT *
      INTO #TEMP_TT
      FROM (
            SELECT 'A' AS T UNION ALL
            SELECT 'B' AS T UNION ALL
            SELECT 'C' AS T
           ) A

    SELECT *
      FROM #TEMP_TT

<임시테이블에 데이터를 입력하고 입력된 데이터를 확인하는 쿼리>

위 쿼리를 처음 실행하면 아래와 같이 정상적으로 실행된다.

위 쿼리를 다시한번 실행하면 아래와 같은 오류가 발생한다.

쿼리 상단에 아래와 같은 코드를 넣어주면, 쿼리 실행 시 임시테이블의 존재 여부를 확인하고 

임시테이블이 있다면 제거 후 쿼리를 실행하게 된다.

* #이 2개 붙어있는 전역 임시테이블에 사용할 때는 다른 SESSION에서 사용되고 있을 수 있으니 주의해야한다.

    IF OBJECT_ID('TEMPDB..#TEMP_TT') IS NOT NULL
    BEGIN
        DROP TABLE #TEMP_TT
        PRINT 'DROP TABLE #TEMP_TT.'
    END
    ELSE
    BEGIN
        PRINT '#TEMP_TT IS NOT EXISTS.'
    END

    SELECT *
      INTO #TEMP_TT
      FROM (
            SELECT 'A' AS T UNION ALL
            SELECT 'B' AS T UNION ALL
            SELECT 'C' AS T
           ) A

    SELECT *
      FROM #TEMP_TT

임시테이블이 있다면 제거 후 쿼리를 실행할 수 있도록 조건문 추가.

위 쿼리를 실행하면 아래와 같이 임시테이블 존재 여부에 따라 메시지가 표시되며, 정상 실행된다.

숫자형 데이터를 문자형 데이터로 변경할 때 앞에 0을 채워서 지정된 길이로 만들어 사용하고 싶을 때가 있습니다.

단순히 CONVERT를 사용하면 앞에 0이 채워지지 않은 상태로 숫자만 문자형으로 변경되기 때문에

다른 방법을 사용해야 합니다.

FORMAT 함수를 사용하면 앞에 0을 채워 지정한 길이의 문자열로 만들어 사용할 수 있습니다.

    -- FORMAT 함수를 사용하여 숫자를 10자리 문자열로 변경
    SELECT FORMAT(1230,'0000000000') AS FORMATTED_TEXT

    -- CONVERT 함수를 사용하여 숫자를 문자로 변경
    SELECT CONVERT(NVARCHAR(10),1230) AS CONVERTED_TEXT

위 코드를 실행하면 아래와 같은 결과가 나옵니다.

기타 다른 상황에 대한 실행 결과

    -- 숫자 길이가 서식보다 길 때
    SELECT FORMAT(12345678901234567890,'0000000000') AS FORMATTED_OVER_TEXT
    
    -- 숫자를 3자리 마다 쉼표로 구분하고 싶을 때
    SELECT FORMAT(12345678901234567890,'0,000,000,000') AS FORMATTED_COMMAS_TEXT

    -- 숫자 앞에 문자를 고정으로 넣어주고 싶을 때
    SELECT FORMAT(12345678901234567890,'FORMATTED_0000000000') AS FORMATTED_COMMAS_TEXT

LAG 함수를 통해 이전 데이터의 값을 가져와 계산에 사용할 수 있다.

SQL Server2012버전 이후 부터 사용 가능하다.

사용방법

SELECT 문에 함수 작성.

LAG(컬럼명, 이전 행 수, 이전 행이 없을 때 기본값) OVER ( PARTITION BY 그룹으로 묶을 컬럼 리스트

                                                                                               ORDER BY 이전행을 찾을 때 사용할 정렬 기준)

파란색 으로 표시한 글자 생략 가능

/*********************************
* 이전 행 데이터 가져와 계산 TEST 쿼리
**********************************/

-- 1. TEST에 필요한 BASE DATA 만들기

    SELECT A.ITEM
         , A.VALUE
      INTO #TEMP1
      FROM (
            SELECT 'A' ITEM, 1 VALUE UNION ALL
            SELECT 'A' ITEM, 2 VALUE UNION ALL
            SELECT 'A' ITEM, 3 VALUE UNION ALL
            SELECT 'A' ITEM, 4 VALUE UNION ALL
            SELECT 'A' ITEM, 5 VALUE UNION ALL
            SELECT 'B' ITEM, 6 VALUE UNION ALL
            SELECT 'B' ITEM, 7 VALUE UNION ALL
            SELECT 'B' ITEM, 8 VALUE UNION ALL
            SELECT 'B' ITEM, 9 VALUE UNION ALL
            SELECT 'B' ITEM, 10 VALUE
           ) A

-- 2. BASE DATA 확인

    SELECT *
      FROM #TEMP1

-- 3. LAG 윈도우 함수 사용

    SELECT ITEM
         , VALUE
         , LAG(VALUE,1,0) OVER( PARTITION BY ITEM ORDER BY VALUE ) STACKED_SUM
      FROM #TEMP1

MSDN 참고

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16 

MSSQL Server가 재시작된 이후의 Index별 사용 횟수를 조회함.

VIEW SERVER STATE 권한이 있는 sa 와 같은 계정으로 실행해야 정상 실행됨.

-- Table의 Index 별 사용 횟수 확인.
-- USER_SEEK : 정상적으로 INDEX만 사용하여 검색됨.
-- USER_SCANS : INDEX 내에서 SCAN됨.
-- USER_LOOKUPS : INDEX 내에서 조회됨.
-- RANGE_SCAN_COUNT : INDEX에서 범위로 찾은 후 데이터에서 다시 조회됨.
--                    INDEX KEY, PK 외 다른 조건이 붙어 있는 경우.

DECLARE @V_WORK_DATE NVARCHAR(8) = CONVERT(NVARCHAR,GETDATE(),112)
    SELECT LEFT(@V_WORK_DATE,4) + '-' + SUBSTRING(@V_WORK_DATE,5,2) + '-' + RIGHT(@V_WORK_DATE,2) AS WORK_DATE
         , OBJECT_NAME(IUS.OBJECT_ID) AS TABLE_NAME
         , I.NAME AS INDEX_NAME
         , IUS.INDEX_ID
         , IZ.INDEX_SIZE
         , IUS.USER_SEEKS
         , IUS.USER_SCANS
         , IUS.USER_LOOKUPS
         , IOS.RANGE_SCAN_COUNT
      FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS
           INNER JOIN SYS.INDEXES AS I
        ON I.OBJECT_ID = IUS.OBJECT_ID
       AND I.INDEX_ID  = IUS.INDEX_ID
           INNER JOIN SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(),null,NULL,NULL) AS IOS
        ON IOS.OBJECT_ID = IUS.OBJECT_ID
       AND IOS.INDEX_ID = IUS.INDEX_ID
           INNER JOIN
           (
            SELECT I.OBJECT_ID
                 , I.INDEX_ID AS INDEX_ID
                 , SUM(AU.USED_PAGES) * 8 / 1024 AS 'INDEX_SIZE'
              FROM SYS.INDEXES I
                   INNER JOIN SYS.PARTITIONS AS P
                ON P.OBJECT_ID = I.OBJECT_ID
               AND P.INDEX_ID   = I.INDEX_ID
                   INNER JOIN SYS.ALLOCATION_UNITS AS AU
                ON AU.CONTAINER_ID = P.PARTITION_ID
             WHERE 1=1--I.object_id = OBJECT_ID(@V_TABLE_NAME)
          GROUP BY I.OBJECT_ID
                 , I.INDEX_ID
           ) AS IZ
        ON IZ.OBJECT_ID = I.OBJECT_ID
       AND IZ.INDEX_ID  = I.INDEX_ID
     WHERE 1=1 --IUS.OBJECT_ID = OBJECT_ID(@V_TABLE_NAME)
       AND IUS.DATABASE_ID = DB_ID()
    ORDER BY 1,2,4

자세한 사항은 msdn 참고

https://learn.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver16

         SELECT O.NAME
           FROM SYS.SYSOBJECTS AS T
                INNER JOIN SYS.SYSOBJECTS AS O
             ON T.PARENT_OBJ = O.ID
          WHERE T.XTYPE = 'TR'
            AND T.NAME = N'트리거 이름'

위 코드로 트리거 이름을 통해 연결된 테이블을 확인할 수 있습니다.

         SELECT T.NAME
           FROM SYS.SYSOBJECTS AS T
                INNER JOIN SYS.SYSOBJECTS AS O
             ON T.PARENT_OBJ = O.ID
          WHERE T.XTYPE = 'TR'
            AND O.NAME = N'테이블 이름'

위 코드로 테이블 이름을 통해 연결된 트리거를 확인 할 수 있습니다.

SELECT 문에서 TAB, 개행문자를 지우기 위한 컬럼에 REPLACE 처리로 TAB, 개행문자를 빈문자로 변경처리한다.

엑셀 붙여넣기 할 때, TAB이나 개행문자가 있을 때, 틀에 맞게 들어가지 않는 경우가 있음. 이럴때에 사용.

REPLACE(REPLACE(REPLACE(컬럼명, CHAR(13), ''), CHAR(10), ''), CHAR(9), '')

탭 : CHAR(9)

개행문자 : CHAR(13), CHAR(10)

만약 년별 구매요청 금액이 있을 때, 년별 구매요청금액과 현재까지 누적 구매 요청금액을 확인하고 싶을 경우,

윈도우 함수를 이용하여 쉽게 구할 수 있다.

    SELECT NO_SQ
         , CD_ITEM
         , QT_ITEM
         , SUM(QT_ITEM) OVER(ORDER BY NO_SQ ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적
      FROM (   
            SELECT 01 NO_SQ, 'A01' CD_ITEM, 1 QT_ITEM UNION ALL
            SELECT 02 NO_SQ, 'A02' CD_ITEM, 2 QT_ITEM UNION ALL
            SELECT 03 NO_SQ, 'A03' CD_ITEM, 3 QT_ITEM UNION ALL
            SELECT 04 NO_SQ, 'A04' CD_ITEM, 4 QT_ITEM UNION ALL
            SELECT 05 NO_SQ, 'A05' CD_ITEM, 5 QT_ITEM UNION ALL
            SELECT 06 NO_SQ, 'A06' CD_ITEM, 6 QT_ITEM UNION ALL
            SELECT 07 NO_SQ, 'A07' CD_ITEM, 7 QT_ITEM UNION ALL
            SELECT 08 NO_SQ, 'A08' CD_ITEM, 8 QT_ITEM UNION ALL
            SELECT 09 NO_SQ, 'A09' CD_ITEM, 9 QT_ITEM UNION ALL
            SELECT 10 NO_SQ, 'A10' CD_ITEM, 10 QT_ITEM UNION ALL
            SELECT 11 NO_SQ, 'A11' CD_ITEM, 11 QT_ITEM UNION ALL
            SELECT 12 NO_SQ, 'A12' CD_ITEM, 12 QT_ITEM UNION ALL
            SELECT 13 NO_SQ, 'A13' CD_ITEM, 13 QT_ITEM UNION ALL
            SELECT 14 NO_SQ, 'A14' CD_ITEM, 14 QT_ITEM UNION ALL
            SELECT 15 NO_SQ, 'A15' CD_ITEM, 15 QT_ITEM

           ) A
  ORDER BY NO_SQ

SUM(QT_ITEM) OVER(ORDER BY NO_SQ ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적

QT_ITEM : 누적합을 구할 컬럼

ORDER BY NO_SQ ASC : 누적합 구할 때 정렬 기준 열과 정렬 방법

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 누적합 구하는 방법, 처음부터 현재 행 까지 누적합

MSSQL에서 특정 테이블에 값을 INSERT, UPDATE, DELETE 중에 트랜잭션이 COMMIT 되기 전에

해당 데이터를 SELECT 하게 되면 잠금일 발생하여 SELECT 전에 발생한 트랜잭션이 COMMIT 되기를 기다린다.

이때, 기다림 없이 바로 조회하고 싶다면, SELECT 문의 테이블 뒤에 WITH (NOLOCK) 을 추가해주면 된다.

프로시저 작성 시 사용하는 전체 테이블에 WITH (NOLOCK) 처리를 하고 싶은 경우,

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

프로시저 상단에 아래 한줄의 코드를 추가해두면, 각 테이블 뒤에 일일이 WITH (NOLOCK) 을 작성하지 않아도 

전체 테이블에 WITH (NOLOCK) 처리한 것과 같은 효과를 낸다.

SELECT 문을 실행하였을 때 여러행으로 출력되는 데이터를 1줄의 문자열로 묶을 필요가 있다.

문자열로 만들 때 각 행의 데이터를 구분자로 구분하여 출력할 수 있다.

데이터 : 배 A, B의 승무원이 다음과 같이 있다.

SELECT *
  FROM (
        SELECT 'A' NM_SHIP, '김바둑' NM_CREW UNION ALL
        SELECT 'A' NM_SHIP, '강누렁' NM_CREW UNION ALL
        SELECT 'A' NM_SHIP, '박흰둥' NM_CREW UNION ALL
        SELECT 'A' NM_SHIP, '장까미' NM_CREW UNION ALL
        SELECT 'A' NM_SHIP, '곽얼룩' NM_CREW UNION ALL
        SELECT 'B' NM_SHIP, '이나비' NM_CREW UNION ALL
        SELECT 'B' NM_SHIP, '고치즈' NM_CREW UNION ALL
        SELECT 'B' NM_SHIP, '황삼색' NM_CREW UNION ALL
        SELECT 'B' NM_SHIP, '신고등' NM_CREW UNION ALL
        SELECT 'B' NM_SHIP, '임꼬물' NM_CREW 
       ) A

요구 조건 : 배 A의 승무원을 한 줄의 문자열로 확인하고 싶다.

       SELECT ''+A.NM_CREW
         FROM (
               SELECT 'A' NM_SHIP, '김바둑' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '강누렁' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '박흰둥' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '장까미' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '곽얼룩' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '이나비' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '고치즈' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '황삼색' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '신고등' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '임꼬물' NM_CREW 
              ) A
        WHERE A.NM_SHIP = 'A'
          FOR XML PATH('')

추가 요구 조건 : 이름 사이에 ',' 로 구분하고 싶다.

       SELECT ','+A.NM_CREW -- '' 공백문자를 ',' 쉼표로 변경
         FROM (
               SELECT 'A' NM_SHIP, '김바둑' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '강누렁' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '박흰둥' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '장까미' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '곽얼룩' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '이나비' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '고치즈' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '황삼색' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '신고등' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '임꼬물' NM_CREW 
              ) A
        WHERE A.NM_SHIP = 'A'
          FOR XML PATH('')

추가 요구 조건 2 : 맨 앞에 있는 쉼표를 제거하고 싶다.

 SELECT STUFF((SELECT ','+A.NM_CREW
         FROM (
               SELECT 'A' NM_SHIP, '김바둑' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '강누렁' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '박흰둥' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '장까미' NM_CREW UNION ALL
               SELECT 'A' NM_SHIP, '곽얼룩' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '이나비' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '고치즈' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '황삼색' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '신고등' NM_CREW UNION ALL
               SELECT 'B' NM_SHIP, '임꼬물' NM_CREW 
              ) A
        WHERE A.NM_SHIP = 'A'
          FOR XML PATH('')),1,1,'')