#title T-SQL 템플릿
[[TableOfContents]]

이곳은 주인놈이 귀찮음에 만든 곳이당..

alter database dw set trustworthy on;

==== 숫자 ====
{{{
select *
from (values (1),(2),(3),(4),(5),(6),(7)) t(seq)
}}}

==== bigint, int, smallint, tinyint 범위 ====
||데이터형|| 범위 ||
||bigint||-2^63(-9223372036854775808) ~ 2^63-1(9223372036854775807)||
||int||-2^31(-2147483648) ~ 2^31-1(2147483647)||
||smallint||-2^15(-32768) ~ 2^15-1(32767)||
||tinyint||0 ~ 255||
==== waitfor delay ====
{{{
while(1=1)
begin
    if datepart(hh, getdate()) in (1,2,3,4,5,6,7)
        waitfor delay '00:00:10'
    else
        break
end
}}}
==== 인덱스 생성 및 압축 ====
{{{
create nonclustered index 인덱스명 
on 스키마.테이블명(컬럼명)
with (data_compression = page)
on 파티션스킴(파티션컬럼) 

create index 인덱스명 
on 스키마.테이블명(컬럼명) 
include (포함될 컬럼명)
where 필터링할컬럼명 is not null
with (data_compression = page, online=on)
on 파티션스킴(파티션컬럼) ;

alter table 테이블명 rebuild with (data_compression = page);
alter index 인덱스명 on 테이블명 rebuild with (data_compression = page);	

select * --data_compression_desc 컬럼 참고
from sys.partitions 
where object_id = object_id('dbo.aaa')
}}}

==== 락 에스컬레이션 설정 ====
{{{
alter table [schema_name].[table_name] set (lock_escalation = disable)
}}}
==== SQL문 검증 ====
 * 구문검사는 했는가?
 * where 조건은 완벽한가?
 * inner join으로 row가 늘어나거나 줄어들지 않는가?
 * 전체 count(*)는 맞는가?
 * 검증 후 추가적인 변경부분이 있는가? 변경부분이 있다면 검증은 했는가?

==== 0으로 나누기 오류 무시 ====
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
==== Dummy: 숫자 ====
{{{
;with dummy
as
(
        select 0 seq
        union all
        select seq + 1 from dummy
        where seq + 1 <= 10
)
select 
    seq 
,   convert(int, substring(convert(varbinary(50), newid()), 1, 1)) * 1.0 / 255 random 
from dummy
option (maxrecursion 0);
}}}

==== Dummy: 날짜 ====
{{{
with dummy
as
(
        select convert(datetime, '20000101') dt 
        union all
        select dt + 1 from dummy
        where dt + 1 < '20010101'
)
select 
	dt dt_dtime
,	convert(char(8), dt, 112) dt_char8
,	convert(char(10), dt, 121) dt_char10
,	convert(char(8), dt, 112) begindt
,	convert(char(8), dt+1, 112) enddt
,	convert(datetime, convert(char(8), dt, 112)) begindt_dt
,	convert(datetime, convert(char(8), dt+1, 112)) enddt_dt
from dummy
option (maxrecursion 0);
}}}

{{{
declare 
	@begin char(8)
,	@end char(8);

set @begin = '20100101';
set @end = '20100115';

with dummy
as
(
        select convert(datetime, @begin) dt 
        union all
        select dt + 1 from dummy
        where dt + 1 <= @end
)
select dt
from dummy
option (maxrecursion 0);
}}}

==== Cube 파티션 만들때 ====
{{{
select
	left(date_key,7)
,	min(date_key)
,	max(date_key)
,	'where date_key between ''' + convert(char(10), min(date_key)) + ''' and ''' + convert(char(10), max(date_key)) + ''''
from dm.dim.date
where date_key between '2010-08-01' and '2011-12-31'
group by
	left(date_key,7)
}}}
==== 날짜형 데이터의 between ====
{{{
create table #temp
(
	begin_dt datetime
,	end_dt datetime
)

insert #temp
select '2010-01-19 00:00:00.000', '2010-01-19 10:51:06.640'
union all
select '2010-01-18 10:51:06.640', '2010-01-18 11:51:06.640'
union all
select '2010-01-18 10:51:06.640', '2010-01-19 10:51:06.640'

--현재일이 20100118 라면.. 
select * from #temp
where 1=1
and begin_dt < '20100119'
and end_dt >= '20100118'
}}}


==== Dummy: 날짜(주) ====
{{{ 
SET DATEFIRST 1; --월요일부터 시작, 값은 1
WITH Dumy
AS
(
        SELECT CONVERT(datetime, '20000103') DT 
        UNION ALL
        SELECT DT + 1 FROM Dumy
        WHERE DT + 1 < GETDATE()+1
), WW
AS
(
	SELECT 
		CONVERT(char(8), DT , 112) BeginDT_WK
	,	CONVERT(char(8), DATEADD(dd, 6, DT), 112) EndDT_WK
	FROM Dumy
	WHERE DATEPART(weekday, DT) IN (1)
	--AND GETDATE()-1 BETWEEN CONVERT(char(8), DT , 112)
	--AND CONVERT(char(8), DATEADD(dd, 6, DT), 112)
), MM
AS
(
	SELECT
		CONVERT(char(8), DT , 112) BeginDT_MM
	,	CONVERT(char(8), DATEADD(mm, 1, DT)-1, 112) EndDT_MM
	FROM Dumy
	WHERE DATEPART(dd, DT) IN (1)
)
SELECT 
	CONVERT(char(8), DT, 112) CurrDT
,	BeginDT_WK
,	EndDT_WK
,	BeginDT_MM
,	EndDT_MM
FROM Dumy A
	INNER JOIN WW B
		ON A.DT BETWEEN BeginDT_WK AND EndDT_WK
	INNER JOIN MM C
		ON A.DT BETWEEN BeginDT_MM AND EndDT_MM
OPTION (MAXRECURSION 0);
}}}
==== 월단위 loop ====
{{{
set nocount on
set statistics io off
declare
	@bdt char(8)
,	@edt char(8)

set @bdt = '20110701'

while (@bdt <= '20120701')
begin
	set @edt = convert(char(8), dateadd(mm, 1, @bdt)-1, 112)

	raiserror ('%s, %s', 0, 1, @bdt, @edt) with nowait;
	set @bdt = convert(char(8), dateadd(dd, 1, @edt), 112)
end
}}}

==== 일단위 loop - 순방향 ====
{{{
set nocount on
set statistics io off
declare
	@bdt char(8)
,	@edt char(8)

set @bdt = '20100101'

while (@bdt <= '20100128')
begin
	set @edt = convert(char(8), dateadd(dd, 1, @bdt), 112)

	raiserror ('%s, %s', 0, 1, @bdt, @edt) with nowait;
    --waitfor delay '00:00:10'
	set @bdt = @edt
end
}}}

{{{
set nocount on
set statistics io off
declare
	@bdt char(8)
,	@edt char(8)
,   @exec_start_time datetime
,   @exec_time varchar(20)

declare @std_dt char(8)

set @bdt = '20181225'

while (@bdt <= '20190707')
begin
	set @edt = convert(char(8), dateadd(dd, 1, @bdt), 112)
    set @exec_start_time = getdate()


    select
        @exec_time = concat(format(exec_time/600, '0#'), ':', format((exec_time%600)/60, '0#'), ':', format((exec_time%600)%60, '0#'))
    from (select datediff(ss, @exec_start_time, getdate()) exec_time) t
	raiserror ('%s, %s, exec_time:%s ', 0, 1, @bdt, @edt, @exec_time) with nowait;
    --waitfor delay '00:00:10'
	set @bdt = @edt
end
}}}
==== 일단위 loop - 역방향 ====
{{{
set nocount on
set statistics io off
declare
	@bdt char(8)
,	@edt char(8)
,	@next_dt char(8)

set @bdt = '20100101'
set @edt = '20100115'

while (@bdt <= @edt)
begin
	set @next_dt = convert(char(8), dateadd(dd, 1, @edt), 112)

	print @edt + ', ' + @next_dt
	set @edt = convert(char(8), dateadd(dd, -1, @edt), 112)
end
}}}

==== 커서 ====
{{{
declare 
    @tname nvarchar(4000) 

declare cur cursor for
--select

open cur;
fetch next from cur into @tname;
while @@FETCH_STATUS not in (-1, -2)
begin

	raiserror ('%s', 0, 1, @tname) with nowait;
	fetch next from cur into @tname;
end

close cur;
deallocate cur;
}}}

==== 기술통계값(대충) ====
{{{
;with sale
as
(
	select 
		accountkey
	,	sum(saleamt) amt 
	,	log(sum(saleamt)) log_amt 
	from cji_eis.PubSales.Fact_Sales 
	where datekey between 20090601 and 20090630
	and gamekey = 20
	and saleamt > 0
	group by
		accountkey
), rs
as
(
	select
		row_number() over(order by convert(int, log_amt)) 번호
	,	sum(amt) 구매금액
	,	count(*) 구매계정수
	,	min(amt) 구매금액구간시작
	,	max(amt) 구매금액구간종료
	,	max(amt) - max(amt) / 2 중앙값
	,	avg(amt) 평균구매금액
	,	stdev(amt) 구매금액표준편차
	,	convert(int, log_amt) [log(구매금액)]
	,	case
			when convert(int, log_amt) in (5,6,7,8) then 'A'
			when convert(int, log_amt) in (9,10) then 'B'
			when convert(int, log_amt) in (11,12,13) then 'C'
			when convert(int, log_amt) in (14, 15,16) then 'D'
		end 등급
	from sale
	group by
		convert(int, log_amt)
)
select *
,	구매금액 / sum(구매금액*1.0) over() * 100 구매금액비율
,	구매계정수 / sum(구매계정수*1.0) over() * 100 구매계정비율
from rs
order by 1	
}}}
==== job 정보 ====
{{{
select
	a.name job_name
,	a.date_modified
,	b.step_id
,	b.step_name
,	b.subsystem
,	substring(right('000000' + convert(varchar, b.last_run_duration),6),1,2) + ':' +
	substring(right('000000' + convert(varchar, b.last_run_duration),6),3,2) + ':' +
	substring(right('000000' + convert(varchar, b.last_run_duration),6),5,2) last_run_duration
,	b.command
from msdb.dbo.sysjobs a
	inner join msdb.dbo.sysjobsteps b
		on a.job_id = b.job_id
where a.enabled = 1
}}}

{{{
--활성화된 Job만..(한번만 실행, 비활성화된 작업 제외)
select
	a.name job_name
,	a.date_modified
,	b.step_id
,	b.step_name
,	b.subsystem
,	substring(right('000000' + convert(varchar, b.last_run_duration),6),1,2) + ':' +
	substring(right('000000' + convert(varchar, b.last_run_duration),6),3,2) + ':' +
	substring(right('000000' + convert(varchar, b.last_run_duration),6),5,2) last_run_duration
,	b.command
from msdb.dbo.sysjobs a
	inner join msdb.dbo.sysjobsteps b
		on a.job_id = b.job_id
	inner join msdb.dbo.sysjobschedules c
		on a.job_id = c.job_id
where a.enabled = 1
and c.next_run_date >= convert(char(8), getdate(), 112)
order by last_run_duration desc
}}}

==== 관계맺기 ====
{{{
ALTER TABLE [Common].[Fact_ConnectHistory]  
WITH CHECK 
ADD  CONSTRAINT [FK_Fact_ConnectHistory_Dim_Date] FOREIGN KEY([DateKey]) REFERENCES [Common].[Dim_Date] ([DateKey])

ALTER TABLE [Common].[Fact_ConnectHistory]  
WITH NOCHECK 
ADD  CONSTRAINT [FK_Fact_ConnectHistory_Dim_Date] FOREIGN KEY([DateKey]) REFERENCES [Common].[Dim_Date] ([DateKey])
}}}


====  시작일이 랜덤인 주(week)의 시작일과 종료일 구하기 ====
{{{
DECLARE
	@BeginDT datetime
,	@LoginDT datetime
SET @BeginDT = '20100307'
SET @LoginDT = '20100423'

--검색시작일이 '20100307'일 때, 주의 시작일이 '20100307'라면..
--'20100423'에 해당하는 주는..
SELECT 
	CONVERT(char(8), DATEADD(dd, DATEDIFF(dd, CONVERT(char(8), @BeginDT, 112), @LoginDT)%7*-1, @LoginDT), 112) + '~' +
	CONVERT(char(8), DATEADD(dd, DATEDIFF(dd, CONVERT(char(8), @BeginDT, 112), @LoginDT)%7*-1, @LoginDT)+6, 112)

--결과
--20100418~20100424
}}}

==== 숫자 - 바이너리 - 문자바이너리 맵핑 테이블 ====
{{{
select 
	convert(tinyint, num) num
,	bin
,	str_bin
into dbo.binary_map
from (
	select 0 num, 0x00 bin ,'00' str_bin union all
	select 1, 0x01 bin ,'01' str_bin union all
	select 2, 0x02 bin ,'02' str_bin union all
	select 3, 0x03 bin ,'03' str_bin union all
	select 4, 0x04 bin ,'04' str_bin union all
	select 5, 0x05 bin ,'05' str_bin union all
	select 6, 0x06 bin ,'06' str_bin union all
	select 7, 0x07 bin ,'07' str_bin union all
	select 8, 0x08 bin ,'08' str_bin union all
	select 9, 0x09 bin ,'09' str_bin union all
	select 10, 0x0A bin ,'0A' str_bin union all
	select 11, 0x0B bin ,'0B' str_bin union all
	select 12, 0x0C bin ,'0C' str_bin union all
	select 13, 0x0D bin ,'0D' str_bin union all
	select 14, 0x0E bin ,'0E' str_bin union all
	select 15, 0x0F bin ,'0F' str_bin union all
	select 16, 0x10 bin ,'10' str_bin union all
	select 17, 0x11 bin ,'11' str_bin union all
	select 18, 0x12 bin ,'12' str_bin union all
	select 19, 0x13 bin ,'13' str_bin union all
	select 20, 0x14 bin ,'14' str_bin union all
	select 21, 0x15 bin ,'15' str_bin union all
	select 22, 0x16 bin ,'16' str_bin union all
	select 23, 0x17 bin ,'17' str_bin union all
	select 24, 0x18 bin ,'18' str_bin union all
	select 25, 0x19 bin ,'19' str_bin union all
	select 26, 0x1A bin ,'1A' str_bin union all
	select 27, 0x1B bin ,'1B' str_bin union all
	select 28, 0x1C bin ,'1C' str_bin union all
	select 29, 0x1D bin ,'1D' str_bin union all
	select 30, 0x1E bin ,'1E' str_bin union all
	select 31, 0x1F bin ,'1F' str_bin union all
	select 32, 0x20 bin ,'20' str_bin union all
	select 33, 0x21 bin ,'21' str_bin union all
	select 34, 0x22 bin ,'22' str_bin union all
	select 35, 0x23 bin ,'23' str_bin union all
	select 36, 0x24 bin ,'24' str_bin union all
	select 37, 0x25 bin ,'25' str_bin union all
	select 38, 0x26 bin ,'26' str_bin union all
	select 39, 0x27 bin ,'27' str_bin union all
	select 40, 0x28 bin ,'28' str_bin union all
	select 41, 0x29 bin ,'29' str_bin union all
	select 42, 0x2A bin ,'2A' str_bin union all
	select 43, 0x2B bin ,'2B' str_bin union all
	select 44, 0x2C bin ,'2C' str_bin union all
	select 45, 0x2D bin ,'2D' str_bin union all
	select 46, 0x2E bin ,'2E' str_bin union all
	select 47, 0x2F bin ,'2F' str_bin union all
	select 48, 0x30 bin ,'30' str_bin union all
	select 49, 0x31 bin ,'31' str_bin union all
	select 50, 0x32 bin ,'32' str_bin union all
	select 51, 0x33 bin ,'33' str_bin union all
	select 52, 0x34 bin ,'34' str_bin union all
	select 53, 0x35 bin ,'35' str_bin union all
	select 54, 0x36 bin ,'36' str_bin union all
	select 55, 0x37 bin ,'37' str_bin union all
	select 56, 0x38 bin ,'38' str_bin union all
	select 57, 0x39 bin ,'39' str_bin union all
	select 58, 0x3A bin ,'3A' str_bin union all
	select 59, 0x3B bin ,'3B' str_bin union all
	select 60, 0x3C bin ,'3C' str_bin union all
	select 61, 0x3D bin ,'3D' str_bin union all
	select 62, 0x3E bin ,'3E' str_bin union all
	select 63, 0x3F bin ,'3F' str_bin union all
	select 64, 0x40 bin ,'40' str_bin union all
	select 65, 0x41 bin ,'41' str_bin union all
	select 66, 0x42 bin ,'42' str_bin union all
	select 67, 0x43 bin ,'43' str_bin union all
	select 68, 0x44 bin ,'44' str_bin union all
	select 69, 0x45 bin ,'45' str_bin union all
	select 70, 0x46 bin ,'46' str_bin union all
	select 71, 0x47 bin ,'47' str_bin union all
	select 72, 0x48 bin ,'48' str_bin union all
	select 73, 0x49 bin ,'49' str_bin union all
	select 74, 0x4A bin ,'4A' str_bin union all
	select 75, 0x4B bin ,'4B' str_bin union all
	select 76, 0x4C bin ,'4C' str_bin union all
	select 77, 0x4D bin ,'4D' str_bin union all
	select 78, 0x4E bin ,'4E' str_bin union all
	select 79, 0x4F bin ,'4F' str_bin union all
	select 80, 0x50 bin ,'50' str_bin union all
	select 81, 0x51 bin ,'51' str_bin union all
	select 82, 0x52 bin ,'52' str_bin union all
	select 83, 0x53 bin ,'53' str_bin union all
	select 84, 0x54 bin ,'54' str_bin union all
	select 85, 0x55 bin ,'55' str_bin union all
	select 86, 0x56 bin ,'56' str_bin union all
	select 87, 0x57 bin ,'57' str_bin union all
	select 88, 0x58 bin ,'58' str_bin union all
	select 89, 0x59 bin ,'59' str_bin union all
	select 90, 0x5A bin ,'5A' str_bin union all
	select 91, 0x5B bin ,'5B' str_bin union all
	select 92, 0x5C bin ,'5C' str_bin union all
	select 93, 0x5D bin ,'5D' str_bin union all
	select 94, 0x5E bin ,'5E' str_bin union all
	select 95, 0x5F bin ,'5F' str_bin union all
	select 96, 0x60 bin ,'60' str_bin union all
	select 97, 0x61 bin ,'61' str_bin union all
	select 98, 0x62 bin ,'62' str_bin union all
	select 99, 0x63 bin ,'63' str_bin union all
	select 100, 0x64 bin ,'64' str_bin union all
	select 101, 0x65 bin ,'65' str_bin union all
	select 102, 0x66 bin ,'66' str_bin union all
	select 103, 0x67 bin ,'67' str_bin union all
	select 104, 0x68 bin ,'68' str_bin union all
	select 105, 0x69 bin ,'69' str_bin union all
	select 106, 0x6A bin ,'6A' str_bin union all
	select 107, 0x6B bin ,'6B' str_bin union all
	select 108, 0x6C bin ,'6C' str_bin union all
	select 109, 0x6D bin ,'6D' str_bin union all
	select 110, 0x6E bin ,'6E' str_bin union all
	select 111, 0x6F bin ,'6F' str_bin union all
	select 112, 0x70 bin ,'70' str_bin union all
	select 113, 0x71 bin ,'71' str_bin union all
	select 114, 0x72 bin ,'72' str_bin union all
	select 115, 0x73 bin ,'73' str_bin union all
	select 116, 0x74 bin ,'74' str_bin union all
	select 117, 0x75 bin ,'75' str_bin union all
	select 118, 0x76 bin ,'76' str_bin union all
	select 119, 0x77 bin ,'77' str_bin union all
	select 120, 0x78 bin ,'78' str_bin union all
	select 121, 0x79 bin ,'79' str_bin union all
	select 122, 0x7A bin ,'7A' str_bin union all
	select 123, 0x7B bin ,'7B' str_bin union all
	select 124, 0x7C bin ,'7C' str_bin union all
	select 125, 0x7D bin ,'7D' str_bin union all
	select 126, 0x7E bin ,'7E' str_bin union all
	select 127, 0x7F bin ,'7F' str_bin union all
	select 128, 0x80 bin ,'80' str_bin union all
	select 129, 0x81 bin ,'81' str_bin union all
	select 130, 0x82 bin ,'82' str_bin union all
	select 131, 0x83 bin ,'83' str_bin union all
	select 132, 0x84 bin ,'84' str_bin union all
	select 133, 0x85 bin ,'85' str_bin union all
	select 134, 0x86 bin ,'86' str_bin union all
	select 135, 0x87 bin ,'87' str_bin union all
	select 136, 0x88 bin ,'88' str_bin union all
	select 137, 0x89 bin ,'89' str_bin union all
	select 138, 0x8A bin ,'8A' str_bin union all
	select 139, 0x8B bin ,'8B' str_bin union all
	select 140, 0x8C bin ,'8C' str_bin union all
	select 141, 0x8D bin ,'8D' str_bin union all
	select 142, 0x8E bin ,'8E' str_bin union all
	select 143, 0x8F bin ,'8F' str_bin union all
	select 144, 0x90 bin ,'90' str_bin union all
	select 145, 0x91 bin ,'91' str_bin union all
	select 146, 0x92 bin ,'92' str_bin union all
	select 147, 0x93 bin ,'93' str_bin union all
	select 148, 0x94 bin ,'94' str_bin union all
	select 149, 0x95 bin ,'95' str_bin union all
	select 150, 0x96 bin ,'96' str_bin union all
	select 151, 0x97 bin ,'97' str_bin union all
	select 152, 0x98 bin ,'98' str_bin union all
	select 153, 0x99 bin ,'99' str_bin union all
	select 154, 0x9A bin ,'9A' str_bin union all
	select 155, 0x9B bin ,'9B' str_bin union all
	select 156, 0x9C bin ,'9C' str_bin union all
	select 157, 0x9D bin ,'9D' str_bin union all
	select 158, 0x9E bin ,'9E' str_bin union all
	select 159, 0x9F bin ,'9F' str_bin union all
	select 160, 0xA0 bin ,'A0' str_bin union all
	select 161, 0xA1 bin ,'A1' str_bin union all
	select 162, 0xA2 bin ,'A2' str_bin union all
	select 163, 0xA3 bin ,'A3' str_bin union all
	select 164, 0xA4 bin ,'A4' str_bin union all
	select 165, 0xA5 bin ,'A5' str_bin union all
	select 166, 0xA6 bin ,'A6' str_bin union all
	select 167, 0xA7 bin ,'A7' str_bin union all
	select 168, 0xA8 bin ,'A8' str_bin union all
	select 169, 0xA9 bin ,'A9' str_bin union all
	select 170, 0xAA bin ,'AA' str_bin union all
	select 171, 0xAB bin ,'AB' str_bin union all
	select 172, 0xAC bin ,'AC' str_bin union all
	select 173, 0xAD bin ,'AD' str_bin union all
	select 174, 0xAE bin ,'AE' str_bin union all
	select 175, 0xAF bin ,'AF' str_bin union all
	select 176, 0xB0 bin ,'B0' str_bin union all
	select 177, 0xB1 bin ,'B1' str_bin union all
	select 178, 0xB2 bin ,'B2' str_bin union all
	select 179, 0xB3 bin ,'B3' str_bin union all
	select 180, 0xB4 bin ,'B4' str_bin union all
	select 181, 0xB5 bin ,'B5' str_bin union all
	select 182, 0xB6 bin ,'B6' str_bin union all
	select 183, 0xB7 bin ,'B7' str_bin union all
	select 184, 0xB8 bin ,'B8' str_bin union all
	select 185, 0xB9 bin ,'B9' str_bin union all
	select 186, 0xBA bin ,'BA' str_bin union all
	select 187, 0xBB bin ,'BB' str_bin union all
	select 188, 0xBC bin ,'BC' str_bin union all
	select 189, 0xBD bin ,'BD' str_bin union all
	select 190, 0xBE bin ,'BE' str_bin union all
	select 191, 0xBF bin ,'BF' str_bin union all
	select 192, 0xC0 bin ,'C0' str_bin union all
	select 193, 0xC1 bin ,'C1' str_bin union all
	select 194, 0xC2 bin ,'C2' str_bin union all
	select 195, 0xC3 bin ,'C3' str_bin union all
	select 196, 0xC4 bin ,'C4' str_bin union all
	select 197, 0xC5 bin ,'C5' str_bin union all
	select 198, 0xC6 bin ,'C6' str_bin union all
	select 199, 0xC7 bin ,'C7' str_bin union all
	select 200, 0xC8 bin ,'C8' str_bin union all
	select 201, 0xC9 bin ,'C9' str_bin union all
	select 202, 0xCA bin ,'CA' str_bin union all
	select 203, 0xCB bin ,'CB' str_bin union all
	select 204, 0xCC bin ,'CC' str_bin union all
	select 205, 0xCD bin ,'CD' str_bin union all
	select 206, 0xCE bin ,'CE' str_bin union all
	select 207, 0xCF bin ,'CF' str_bin union all
	select 208, 0xD0 bin ,'D0' str_bin union all
	select 209, 0xD1 bin ,'D1' str_bin union all
	select 210, 0xD2 bin ,'D2' str_bin union all
	select 211, 0xD3 bin ,'D3' str_bin union all
	select 212, 0xD4 bin ,'D4' str_bin union all
	select 213, 0xD5 bin ,'D5' str_bin union all
	select 214, 0xD6 bin ,'D6' str_bin union all
	select 215, 0xD7 bin ,'D7' str_bin union all
	select 216, 0xD8 bin ,'D8' str_bin union all
	select 217, 0xD9 bin ,'D9' str_bin union all
	select 218, 0xDA bin ,'DA' str_bin union all
	select 219, 0xDB bin ,'DB' str_bin union all
	select 220, 0xDC bin ,'DC' str_bin union all
	select 221, 0xDD bin ,'DD' str_bin union all
	select 222, 0xDE bin ,'DE' str_bin union all
	select 223, 0xDF bin ,'DF' str_bin union all
	select 224, 0xE0 bin ,'E0' str_bin union all
	select 225, 0xE1 bin ,'E1' str_bin union all
	select 226, 0xE2 bin ,'E2' str_bin union all
	select 227, 0xE3 bin ,'E3' str_bin union all
	select 228, 0xE4 bin ,'E4' str_bin union all
	select 229, 0xE5 bin ,'E5' str_bin union all
	select 230, 0xE6 bin ,'E6' str_bin union all
	select 231, 0xE7 bin ,'E7' str_bin union all
	select 232, 0xE8 bin ,'E8' str_bin union all
	select 233, 0xE9 bin ,'E9' str_bin union all
	select 234, 0xEA bin ,'EA' str_bin union all
	select 235, 0xEB bin ,'EB' str_bin union all
	select 236, 0xEC bin ,'EC' str_bin union all
	select 237, 0xED bin ,'ED' str_bin union all
	select 238, 0xEE bin ,'EE' str_bin union all
	select 239, 0xEF bin ,'EF' str_bin union all
	select 240, 0xF0 bin ,'F0' str_bin union all
	select 241, 0xF1 bin ,'F1' str_bin union all
	select 242, 0xF2 bin ,'F2' str_bin union all
	select 243, 0xF3 bin ,'F3' str_bin union all
	select 244, 0xF4 bin ,'F4' str_bin union all
	select 245, 0xF5 bin ,'F5' str_bin union all
	select 246, 0xF6 bin ,'F6' str_bin union all
	select 247, 0xF7 bin ,'F7' str_bin union all
	select 248, 0xF8 bin ,'F8' str_bin union all
	select 249, 0xF9 bin ,'F9' str_bin union all
	select 250, 0xFA bin ,'FA' str_bin union all
	select 251, 0xFB bin ,'FB' str_bin union all
	select 252, 0xFC bin ,'FC' str_bin union all
	select 253, 0xFD bin ,'FD' str_bin union all
	select 254, 0xFE bin ,'FE' str_bin union all
	select 255, 0xFF bin ,'FF' str_bin
) t
}}}

이따위로 써먹자..
{{{
create function dbo.binary_to_string (@bin varbinary(16))
returns varchar(32)
as
begin
	declare @var varchar(32)
	set @var = ''

	select
		@var = @var + str_bin
	from dbo.binary_map a
		inner join (
			select 
				substring(@bin, num+1,1) bin
			from dbo.binary_map
			where num <= datalength(@bin)-1
		) b
		on a.bin = b.bin

	return @var
end
go

select dbo.binary_to_string(0x002421A4B8EB)
/*
002421A4B8EB
*/
}}}

==== binary -> binary형 문자열 ====
짧은 binary라면 노가다가 빠르다. 긴 binary라면 clr 함수를 작성하는게 좋을꺼다
{{{
select top 100 
	substring(replace(convert(uniqueidentifier, convert(binary(1), substring(MacAddress,1,1))), '-', ''), 7, 2) +
	substring(replace(convert(uniqueidentifier, convert(binary(1), substring(MacAddress,2,1))), '-', ''), 7, 2) +
	substring(replace(convert(uniqueidentifier, convert(binary(1), substring(MacAddress,3,1))), '-', ''), 7, 2) +
	substring(replace(convert(uniqueidentifier, convert(binary(1), substring(MacAddress,4,1))), '-', ''), 7, 2) +
	substring(replace(convert(uniqueidentifier, convert(binary(1), substring(MacAddress,5,1))), '-', ''), 7, 2) +
	substring(replace(convert(uniqueidentifier, convert(binary(1), substring(MacAddress,6,1))), '-', ''), 7, 2)
}}}
==== alter schema ====
{{{
--ABC.TableAAA ==> dbo.TableAAA
alter schema dbo transfer ABC.TableAAA
}}}

==== 테이블 size ====
{{{
create table #temp
(
	name nvarchar(255)
,	rows bigint
,	reserved varchar(100)
,	data varchar(100)
,	index_size varchar(100)
,	unused varchar(100)
)

create table #rs
(
	table_name nvarchar(500)
,	rows bigint
,	[reserved(MB)] varchar(100)
,	[data(MB)] varchar(100)
,	[index_size(MB)] varchar(100)
,	[unused(MB)] varchar(100)
)

declare 
	@tname varchar(255)
,	@sql varchar(1000); 

declare cur cursor for
select 
	table_schema + '.' + table_name tname
,	'exec sp_spaceused ''' + table_schema + '.' + table_name + '''' sql
from information_schema.tables
where table_type = 'BASE TABLE'

open cur;
fetch next from cur into @tname, @sql;
while @@FETCH_STATUS not in (-1, -2)
begin
	insert #temp exec(@sql)
	insert #rs
	select
		@tname
	,	rows 
	,	convert(bigint, replace(reserved, 'KB', '')) / 1024
	,	convert(bigint, replace(data, 'KB', '')) / 1024
	,	convert(bigint, replace(index_size, 'KB', '')) / 1024
	,	convert(bigint, replace(unused, 'KB', '')) / 1024
	from #temp
	truncate table #temp
	fetch next from cur into @tname, @sql;
end

close cur;
deallocate cur;

select * from #rs
order by convert(bigint, replace([reserved(MB)], 'KB', '')) desc

drop table #temp
drop table #rs
}}}

==== 왜도, 첨도, 정규분포 ====
{{{
-- skewness(왜도), kurtosis(첨도) 확인
-- 왜도 > 0 : 오른쪽 꼬리
-- 왜도 = 0, 첨도 = 3 이면 정규분포
select 
    mean
,   sigma
,   N
,   sum (( x - mean) * square(x - mean) / (N * sigma * square(sigma))) as skewness
,   sum(square(square(x - mean)) / (N*square ( square(sigma)))) as kurtosis
from #t 
    cross join ( 
        select 
            avg(x) mean
        ,   stdev(x) sigma
        ,   count(x) N
        from #t
 ) s
group by mean, sigma, N
}}}

==== bcp format 파일 만들기 ====
{{{
비xml 서식 파일
bcp db.dbo.tbl format null -T -c -f d:\temp\format.fmt

xml 서식 파일
bcp db.dbo.tbl format null -T -c -x -f d:\temp\format.fmt
}}}