#title SQL Server 2011 New Built-in Functions
[[TableOfContents]]

==== 예제 테이블 ====
{{{
--drop table #temp
create table #temp
(
    seq int identity(1,1)
,   std_dt date
,   val float
)
go

insert #temp values 
    ('20110801', 600)
,   ('20110801', 200)
,   ('20110801', 800)
,   ('20110802', 700)
,   ('20110802', 500)
,   ('20110802', 100)
,   ('20110803', 700)
,   ('20110803', 300)
,   ('20110803', 900)
go
}}}

==== Date and time functions ====
 * datefromparts ( year, month, day )
 * datetime2fromparts ( year, month, day, hour, minute, seconds, fractions, precision )
  * precision = 7 이면, fraction은 100 nanoseconds를 표현할 수 있음
  * precision = 4 이면, fraction은 1 milliseconds를 표현할 수 있음
 * datetimefromparts ( year, month, day, hour, minute, seconds, milliseconds )
 * datetimeoffsetfromparts ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
 * eomonth ( start_date [, month_to_add ] )
 * smalldatetimefromparts ( year, month, day, hour, minute )
 * timefromparts ( hour, minute, seconds, fractions, precision )

{{{
select 
    DateFromParts ( 2010, 12, 31 )
,   Datetime2FromParts ( 2010, 12, 31, 23, 59, 59, 9999999, 7 )
,   DatetimeFromParts ( 2010, 12, 31, 23, 59, 59, 0 )

/*
---------- --------------------------- -----------------------
2010-12-31 2010-12-31 23:59:59.9999999 2010-12-31 23:59:59.000
*/

select
    EoMonth('20110109') --1월의 마지막날
,   EoMonth('20110109', 3) --4월(=1월+3개월)의 마지막날

/*
---------------------- ----------------------
2011-01-31 00:00:00.00 2011-04-30 00:00:00.00
*/

select 
    SMALLDATETIMEFROMPARTS ( 2010, 12, 31, 23, 59 )
,   TIMEFROMPARTS ( 23, 59, 59, 0, 0 )
/*
----------------------- ----------------
2010-12-31 23:59:00     23:59:59
*/
}}}
==== String functions ====
 * concat
 * format
  * [http://technet.microsoft.com/en-us/library/hh213505(SQL.110).aspx books online]
  * [http://technet.microsoft.com/en-us/library/hh213505(SQL.110).aspx Formatting Types]
  * [http://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo%28VS.71%29.aspx CultureInfo Class]
  * attachment:SQLServer2011NewBuilt-inFunctions/sql2011_ctp3_format_function.docx --> [http://sqlblog.com/blogs/aaron_bertrand/archive/2011/08/09/sql-server-v-next-denali-ctp3-t-sql-enhancements-format.aspx 좋은 예제1], [http://redju.tistory.com/148 좋은 예제2]

{{{
select concat('hello ', 111, ' world', '!') --숫자, 문자 섞일 수 있음..
/*
------------
hello 111 world!
*/
}}}
==== Conversion functions ====
 * [http://technet.microsoft.com/en-us/library/hh213316(SQL.110).aspx parse]
 * try_convert
 * try_pase

{{{
select
    parse('12/16/2010' as datetime)
,   parse('000111' as int) --111
,   parse('20110229' as date) --error!! 
,   convert(date, '20110229') --error!! 

select
    parse('12/16/2010' as datetime)
,   parse('000111' as int) --111
,   try_parse('20110229' as date) --null
,   try_convert(date, '20110229') --null
}}}


==== Logical functions ====
 * iif
 * choose

{{{
select
    iif(val >= 500, '500이상', '500미만') 조건
,   count(*) 건수
from #temp
group by
    iif(val >= 500, '500이상', '500미만')

/*
조건    건수
------- -----------
500미만   3
500이상   6
*/

select
    choose(3
    ,   min(case when std_dt = '20110801' then std_dt end)
    ,   min(case when std_dt = '20110802' then std_dt end)
    ,   min(case when std_dt = '20110803' then std_dt end)
    ,   min(case when std_dt = '20110804' then std_dt end)
    ,   min(case when std_dt = '20110805' then std_dt end)
    ) 선택
from #temp 

/*
선택
----------
2011-08-03
*/
}}}

==== EOMONTH() ====
함수의 매개변수로 받은 날짜의 마지막날을 리턴하는 함수. 조금 덜 귀찮아질 듯...
{{{
DECLARE     
	@date DATE = '20110501';

SELECT     
	CurrentMonth   = EOMONTH(@date)		--2011-05-31
,    	FollowingMonth = EOMONTH(@date, 1)	--2011-06-30
,    	PreviousMonth  = EOMONTH(@date, -1);	--2011-04-30
}}}

==== format() ====
format()함수는 정말 느리다. 귀찮을 때만 쓰자.

{{{
select format(getdate(), 'yyyy-MM-dd HH') --대소문자에 주의해야 한다.
}}}

{{{
select format(2, '00#') result

/*
result
----------------------
002
*/

select format(5000000000000/100000000, '#,#억') result

/*
result
----------------------
50,000억
*/
}}}

헐..미친..format()을 썼더니만.. 퍼포먼스가 아주 거지 같다. 대충 다음과 같이 테스트해보니 format()함수를 쓰지 않았을 때가 약 10배 빨랐다.
{{{
if object_id('tempdb.dbo.#time') is not null
    drop table #time
;with dummy
as
(
        select convert(datetime, '20000412') dt 
        union all
        select dateadd(hh, 1, dt) from dummy
        where dateadd(hh, 1, dt) < '20130513'
)
select 
	dt
into #time
from dummy
option (maxrecursion 0);
go

set statistics io on
set statistics time on

if object_id('tempdb.dbo.#1') is not null
    drop table #1

select
    dt
,   count(*) cnt
into #1
from (
    select 
        convert(smalldatetime, format(dt, 'yyyy-MM-dd HH:00')) dt
    from #time
) t
group by
    dt
option(maxdop 1, hash group)
--CPU 시간 = 8393밀리초, 경과 시간 = 9521밀리초

if object_id('tempdb.dbo.#2') is not null
    drop table #2

select 
    convert(smalldatetime, convert(char(13), dt, 121) + ':00') dt
,   count(*) cnt
into #2
from #time
group by
    convert(smalldatetime, convert(char(13), dt, 121) + ':00')
option(maxdop 1, hash group)
--CPU 시간 = 515밀리초, 경과 시간 = 974밀리초
}}}

==== compress/decompress ====
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/12/08/built-in-functions-for-compression-decompression-in-sql-server-2016.aspx Built-in functions for compression/decompression in SQL Server 2016]
==== 참고자료 ====
 * [http://www.sql-server-performance.com/2012/tsql-functions-sql-server-2012/ New Built-In TSQL Functions in SQL Server 2012]