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.nameIDENTITY
(on/off) : data insert
SET IDENTITY_INSERT [TABLE_NAME] ON
INSERT INTO [TABLE_NAME]
(
// ...
)
VALUES (
//...
)
SET IDENTITY_INSERT [TABLE_NAME] OFFcheck
-- 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
Last updated