frequently used

빈 행으로 채우기 (출력물)

DECLARE @v_ordnum varchar(20) = '201811260004' -- 8건

--# 임시 테이블 변수 정의
DECLARE @tb_table Table
(
    idx int
    primary key (idx)
)

DECLARE	@v_total_count	int = 30, -- 한 페이지에 최대 표현할 데이터 건 수
        @v_data_count	int = 0,  -- 상세 테이블 데이터 건 수
        @v_temp_idx		int = 0

SELECT @v_data_count = COUNT(1) FROM sa110t WHERE ordnum = @v_ordnum

--# 표현할 행의 수 만큼 테이블 변수에 INSERT
WHILE (@v_total_count > @v_temp_idx)
BEGIN
    INSERT INTO @tb_table
    VALUES (@v_temp_idx + 1)
    SET @v_temp_idx = @v_temp_idx + 1
END


--# 실제 결과
SELECT itemcd, itemnm, qty FROM sa110t WHERE ordnum = @v_ordnum

UNION ALL

SELECT '' as itemcd, '' as itemnm, 0 as qty 
FROM @tb_table B
--# 위 쿼리와 동일한 결과의 COUNT를 조건으로 사용
WHERE idx <= (((@v_data_count / @v_total_count) + 1)  * @v_total_count) - @v_data_count

Database ONLINE/OFFLINE

-- 오프라인 
ALTER DATABASE [DATABASE_NAME] SET OFFLINE WITH ROLLBACK IMMEDIATE

-- 온라인
ALTER DATABASE [DATABASE_NAME] SET ONLINE

임시 테이블 삭제

• 임시 테이블 선언하기 전과, BEGIN CATCH 문의 시작 부분에 작성하여 방어적으로 쿼리를 작성

IF OBJECT_ID('tempdb..#임시테이블명') IS NOT NULL
    DROP TABLE #임시테이블명

DBCC SHRINKFILE

로그 데이터 정리

use tempdb
--backup log tempdb WITH NO_LOG
dbcc shrinkfile (templog,0)
dbcc shrinkfile (tempdev,0)

로그 정리가 되지 않으면 아래 쿼리 실행 후 재시도

--프로시저 캐시

DBCC FREEPROCCACHE

GO



-- Buffer Pool에 존재하는 데이터 페이지를 제거
DBCC DROPCLEANBUFFERS
GO

--사용 하지 않는 모든 캐시 삭제
DBCC FREESYSTEMCACHE ('ALL')

--분산 쿼리에서 Microsoft SQL Server 인스턴스에 대해 사용한 분산 쿼리 연결 캐시를 플러시 
DBCC FREESESSIONCACHE
GO

프로시저내 문자열 검색

DECLARE @v_str varchar(100) = 'string'

SELECT TT.name
FROM (
		SELECT B.name
		FROM syscomments A, sysobjects B 
		WHERE A.id = B.id
		AND A.text LIKE '%' + @v_str + '%'
		GROUP BY B.name
) TT

프로시저에 포함된 테이블

DECLARE @v_procedure_name varchar(100) = '{프로시저 이름}'

SELECT	B.name AS proc_name,
		C.name AS table_name
FROM sysdepends A
		INNER JOIN sysobjects B ON A.id = B.id
		INNER JOIN sysobjects C ON A.depid = C.id
WHERE B.xtype = 'P'
AND B.name = @v_procedure_name
GROUP BY B.name, C.name

IDENTITY

• (on/off) : data insert

SET IDENTITY_INSERT [TABLE_NAME] ON

    INSERT INTO [TABLE_NAME]
            (    
                // ...
            )
    VALUES  (
                //...
            )

SET IDENTITY_INSERT [TABLE_NAME] OFF

• check

-- DBCC CHECKIDENT([테이블명], noreseed)

DBCC CHECKIDENT(tbl_test, noreseed)

• initial

-- DBCC CHECKIDENT([테이블명], reseed, [초기화 시킬 값])

DBCC CHECKIDENT(tbl_test, reseed, 0)

Service Broker

Enable/Disable

-- 활성화
ALTER DATABASE MyDatabase
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO

-- 비활성화
ALTER DATABASE MyDatabase
SET DISABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO

-- 상태 조회
SELECT is_broker_enabled FROM sys.database WHERE name = 'MyDatabase'

특정 테이블의 연도별 실제 저장된 데이터 크기

• @v_table_name : 테이블 이름

• @v_date_field_name : 집계할 날짜 유형의 필드명

DECLARE 
    @v_table_name        varchar(40) = 'SA100T',
    @v_date_field_name    varchar(40) = 'orddt',
    @v_query                nvarchar(MAX) = ''

SET @v_query = 'SELECT YEAR(CONVERT(datetime, ' + @v_date_field_name +')) AS YEAR, ' + char(13)

SELECT @v_query = @v_query + 'SUM(ISNULL(DATALENGTH(' + B.name + '),0)) +'  + char(13)
FROM sys.tables A, sys.columns B
WHERE A.object_id = B.object_id
AND A.name = @v_table_name
AND B.name <> @v_date_field_name
ORDER BY column_id

SET @v_query = SUBSTRING(@v_query, 0, LEN(@v_query) - 1)
SET @v_query = @v_query + ' FROM ' + @v_table_name + char(13) + 'WHERE ISNULL(' + @v_date_field_name + ', '''') <> ''''' + ' GROUP BY YEAR(CONVERT(datetime, ' + @v_date_field_name +'))'

EXEC (@v_query)

SQL Server CLR 통합

어셈블리 등록, 스칼라 함수 정의

USE master
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO


ALTER DATABASE {database} SET trustworthy on


CREATE ASSEMBLY [GST.PlusWin6]
from 'D:\MSSQL\GST.PlusWin6.SqlFunction.dll'
WITH permission_set = unsafe
GO


CREATE FUNCTION [dbo].[EncryptAES256](@value1 [nvarchar](max), @value2 [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [GST.PlusWin6].[GST.PlusWin6.SqlFunction.Functions].[EncryptAES256]
GO

CREATE FUNCTION [dbo].DecryptAES256(@value1 [nvarchar](max), @value2 [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [GST.PlusWin6].[GST.PlusWin6.SqlFunction.Functions].DecryptAES256
GO