MS SQL Server

참조

CLR 정보 조회

SELECT * FROM SYS.ASSEMBLIES
SELECT * FROM SYS.ASSEMBLY_FILES

.NET Framework에서 사용자 코드를 실행할 수 없습니다. "clr enabled" 구성 옵션을 설정하십시오. 라는 오류를 만나면 CLR 사용을 가능하게 설정해야 합니다.
Enabling CLR Integration

EXEC sp_configure 'clr enabled', 1
RECONFIGURE

쿼리 결과를 파일로 저장

BCP 유틸리티를 사용

사용법: bcp {dbtable | query} {in | out | queryout | format} 데이터 파일
  [-m 최대 오류 수]                  [-f 서식 파일]          [-e 오류 파일]
  [-F 첫 행]                         [-L 마지막 행]          [-b 일괄 처리 크기]
  [-n 네이티브 유형]                 [-c 문자 유형]          [-w 와이드 문자 유형]
  [-N 비텍스트 네이티브 유지]        [-V 파일 형식 버전]    [-q 따옴표 붙은 식별자]
  [-C 코드 페이지 지정자]            [-t 필드 종결자]        [-r 행 종결자]
  [-i 입력 파일]                     [-o 출력 파일]          [-a 패킷 크기]
  [-S 서버 이름]                     [-U 사용자 이름]        [-P 암호]
  [-T 트러스트된 연결]               [-v 버전]               [-R 국가별 설정 사용]
  [-k Null 값 유지]                   [-E ID 값 유지]
  [-h "힌트 로드"]                   [-x xml 서식 파일 생성]
  [-d 데이터베이스 이름]        [-K 응용 프로그램 의도]

다음과 같이 사용한다.

C:\>bcp "select * from <데이터베이스>.<스키마>.<테이블>" queryout "c:\result.txt" -k -c -S <서버> -U "<사용자>" -P "<비밀번호>" -c

OFFSET

MS SQL Server 2012 에 추가된 OFFSET 사용 예제

-- exec sp_board_select 1, 11
alter procedure sp_board_select
@page int,          -- current page
@count int          -- record per page
as
begin
    set nocount on

    SELECT id,
           title 
      FROM dbo.board
    ORDER BY id DESC
    OFFSET (@page - 1) * @count  ROWS 
    FETCH NEXT @count ROWS ONLY;

end
go

테이블 물리적 크기 조회

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
 FROM sys.tables t
INNER JOIN sys.indexes i 
   ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p 
   ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a 
   ON p.partition_id = a.container_id
 LEFT OUTER JOIN sys.schemas s 
   ON t.schema_id = s.schema_id
WHERE  
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

현재 세션 확인

select * from sys.dm_exec_sessions

최근 실행된 쿼리 확인

select * from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle)
where last_execution_time >= '2014-11-10'
order by last_execution_time desc

base64 Encoding / Decoding

Encode

CREATE FUNCTION dbo.FN_ENCODE_BASE64
(
    @SOURCE         NVARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN

    DECLARE @RESULT  NVARCHAR(4000),
            @vSOURCE NVARCHAR(1000)
    
    SET @vSOURCE = @SOURCE;

    IF @vSOURCE IS NULL SET @vSOURCE = N'' -- OR RETURN NULL

    SELECT
         @RESULT = CAST(N'' AS XML).value(
                            'xs:base64Binary(xs:hexBinary(sql:column("BIN")))', 
                            'VARCHAR(MAX)')
    FROM 
        (
            SELECT
                CAST(@vSOURCE AS VARBINARY(1000)) AS BIN
        ) BIN_DATA

    RETURN @RESULT
END
GO

Decode

CREATE FUNCTION dbo.FN_DECODE_BASE64
(
    @SOURCE         NVARCHAR(4000)
)
RETURNS NVARCHAR(1000)
AS
BEGIN

    DECLARE 
        @RESULT     NVARCHAR(1000),
        @vSOURCE    NVARCHAR(4000)

    SET @vSOURCE = @SOURCE 

    IF @vSOURCE IS NULL RETURN NULL

    SELECT  
        @RESULT = CAST(
                        CAST(N'' AS XML).value('xs:base64Binary(sql:column("DATA"))', 'VARBINARY(MAX)')
                        AS NVARCHAR(4000))
    FROM
        (
            SELECT 
                @vSOURCE AS DATA
        ) BIN_DATA

    RETURN @RESULT

END
GO

Generate Random String

함수 내에서 RAND() 를 실행할 수 없으므로 뷰를 만들어 사용한다.

CREATE VIEW dbo.VrndView
AS
    SELECT RAND() RNDRESULT

문자열 생성

CREATE FUNCTION dbo.FN_RANDOM_STRING
(
    @STRING_LENGTH INT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE 
        @RND INT,
        @STRING NVARCHAR(4000)

    SET @STRING = N'';
    IF @STRING_LENGTH IS NULL SET @STRING_LENGTH = 16;

    WHILE(1 = 1) BEGIN
        
        SELECT 
            @RND = CEILING(RNDRESULT * 1000) --CEILING(RAND() * 1000)
        FROM 
            dbo.VrndView

        /*
            33	!
            34	"
            35	#
            36	$
            37	%
            38	&
            39	'
            40	(
            41	)
            42	*
            43	+
            44	,
            45	-
            46	.
            47	/
            0(48) ~ 9(57)  A(65) ~ Z(90)  A(97) ~ (Z)122
        */ 
        --IF 
        --    @RND NOT BETWEEN 48 AND 57 AND
        --    @RND NOT BETWEEN 65 AND 90 AND
        --    @RND NOT BETWEEN 97 AND 122
        --CONTINUE; 

        IF @RND NOT BETWEEN 33 AND 122 CONTINUE;

        --IF @RND = 0 CONTINUE;

        SET @STRING += NCHAR(@RND);

        IF (LEN(@STRING) = @STRING_LENGTH) BREAK;
    END

    RETURN @STRING
END

테스트

select
    tmp.String,
    len(tmp.String) as StringLength,
    dbo.FN_ENCODE_BASE64(tmp.String) as EncodedString,
    len(dbo.FN_ENCODE_BASE64(tmp.String)) as EncodedLength,
    dbo.FN_DECODE_BASE64(dbo.FN_ENCODE_BASE64(tmp.String)) as DecodedString,
    case when dbo.FN_DECODE_BASE64(dbo.FN_ENCODE_BASE64(tmp.String)) = tmp.String then 'Valid' else 'Invalid' end as Valid
from 
    (
        select 
            dbo.FN_RANDOM_STRING(12) as String
    ) tmp