SQL Server 2012 T-SQL New Feature 씨퀄로김민정책임컨설턴트
목차 SQL Server 2012 SSMS 수정사항 SQL Server 2012 향상된프로그래밍기능 SQL Server 2012 에서지원되지않는데이터베이스엔진기능
SQL Server 2012 SSMS 수정사항 Multi Monitor 기본글꼴변경 Courier New -> Consolas 키보드단축키 Visual Studio 2010 Compatible기능사용 CTRL+R 설정 코드조각삽입, 코드감싸기기능추가 도움말뷰어사용 SSMS 소스코드 ZOOM OUT 기능 SSMS 인텔리전스기능 OFF 권고
향상된프로그래밍기능 SEQUENCE 개체 OFFSET/PAGING 기능 ERROR HANDLING 기능향상 OVER절의향상 (WINDOW함수) 분석함수 새로운기본함수제공 (14개)
SEQUENCE 개체 IDENTITY 와비슷, 일련번호생성 하나의테이블에제한되지않음 INSERT 전값이필요한경우사용가능 CYCYLING, MINVALUE,MAXVALUE, RESTART 옵션지원 테이블제약조건으로사용가능 OVER 구문의 ORDER BY 순서에따라 NEXT VALUE 사용가능 SP_SEQUENCE_GET_RANGE 지원 CACHE 기본값 50, 메모리저장
SEQUENCE 개체문법 CREATE SEQUENCE [ schema_name. ] sequence_nam [ AS { <built_in_integer_type> <user-defined_integer_type> } ] [ START WITH <constant> ] [ INCREMENT BY <constant> ] [ MINVALUE <constant> NO MINVALUE [ MAXVALUE <constant> NO MAXVALUE [ CYCLE NO CYCLE ] [ CACHE [<constant> ] NO CACHE ]
IDENTITY 와 SEQUENCE 성능테스트
OFFSET/PAGING 기능 ORDER BY <Column1,2,3 n> OFFSET n {ROW ROWS} and FETCH NEXT n ROWS ONLY OFFSET : 행을반환하기전건너띌행수지정 FETCH NEXT : 읽어올개수지정
Error Handling 기능향상 SQL Server 2000 @@ERROR 함수 / GOTO 사용 RAISERROR문사용 SQL Server 2005 TRY~CATCH 구문사용 ERROR_MESSAGE(),ERROR_NUMBER() 등 SQL Server 2012 THORW문추가 오류처리구문완성
RAISERROR 문 온라인설명서 새응용프로그램에서는 THROW 를대신사용해야합니다 RAISERROR integer 'string' 형식은지원안된다 RAISERROR 50001 'this is a test' 안됨 RAISERROR (50001,16,1, Bike') RAISERROR ('SQL Server 2012 Error!!!',10,1)
THROW 문 THROW <error_number>,<message>,<state> 예외를발생시키고 CATCH 블록으로실행이전 매개변수없이지정된 THROW 는 CATCH 블록내정의 THROW 문앞의문다음에는세미콜론 (;) 필요 RAISERROR 문 msg_id 가 RAISERROR 에 ID 가 sys.messages 에정의 msg_str 매개변수는 printf 서식스타일을포함 severity 를지정할수있음 THROW 문 error_number 매개변수는 sys.messages 에정의될필요없음 message 매개변수에는 printf 스타일서식을사용할수없습니다 severity 매개변수없이 16 으로고정됨
SQL Server 2012 OVER 절향상 적용대상 순위함수 집계함수 분석함수 NEXT FOR VALUE함수 기능 이동평균 누적집계 누계또는그룹결과당상위 N개결과등..
Window Function SQL Server 2005 Window Function 제공 OVER 절지원 Framing 지원하지않음 SQL Server 2012 신규기능 OVER 절에 ROWS/RANGE 절추가행제한가능 신규함수추가지원
Window Function Id Year Amount Balance 1 2,000 1,000 1,000 1 2,001 2,000 3,000 1 2,002 350 3,350 2 2,000 500 500 2 2,001 750 1,250 2 2,002 350 1,600 Multiple Window The Frame
Supported framing options ROWS UNBOUNDED PRECEDING ROWS <unsigned integer literal> PRECEDING ROWS CURRENT ROW ROWS BETWEEN UNBOUNDED PRECEDING AND <unsigned integer literal> PRECEDING ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ROWS BETWEEN UNBOUNDED PRECEDING AND <unsigned integer literal> FOLLOWING ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ROWS BETWEEN <unsigned integer literal> PRECEDING AND <unsigned integer literal> PRECEDING ROWS BETWEEN <unsigned integer literal> PRECEDING AND CURRENT ROW ROWS BETWEEN <unsigned integer literal> PRECEDING AND <unsigned integer literal> FOLLOWING ROWS BETWEEN <unsigned integer literal> PRECEDING AND UNBOUNDED FOLLOWING ROWS BETWEEN CURRENT ROW AND CURRENT ROW ROWS BETWEEN CURRENT ROW AND <unsigned integer> FOLLOWING ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ROWS BETWEEN <unsigned integer literal> FOLLOWING AND <unsigned integer> FOLLOWING ROWS BETWEEN <unsigned integer literal> FOLLOWING AND UNBOUNDED FOLLOWING RANGE UNBOUNDED PRECEDING RANGE CURRENT ROW RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING RANGE BETWEEN CURRENT ROW AND CURRENT ROW
분석함수 함수명 LAG LEAD FIRST_VALUE LAST_VALUE CUME_DIST 설명 accesses data from a previous row accesses data from a subsequent row the first value among the set of ordered values according to specified ordered & partitioned criteria the last value among the set of ordered values according to specified ordered & partitioned criteria the last value until that row in that group cumulative distribution PERCENTILE_DISC discrete interpolated value at the specific offset PERCENTILE_CON T PERCENT_RANK continuous interpolated value at the specific offset the percentage value of rank of the element among its group
새로운기본함수 범주함수이름설명 변환 PARSE 문자열을날짜, 시간, 수치로변환 CULTURE 지정 USING절사용가 능 TRY_PARSE TRY_CONVERT 변환실패시오류대신 NULL 반환 변환실패시오류대신 NULL 반환 논리 CHOOSE 지정된인덱스에있는항목반환 IF 문자열 CONCAT FORMAT 부울식에따라반환 문자열결과로연결 지정된형식및선택적 CULTURE 지정반환 날짜 FROMPARTS 함수 6 개 DATEFROMPARTS EOMONTH DATETIME2FROM PARTS DATETIMEOFFSETFROMPARTS 지정한날짜가포함된달의마지막날을반환
SQL 2012 에서지원되지않는. 기능 80 호환성수준이제공되지않음 Sp_dboption -> Alter Database 문으로 AWE 기능제공되지않음 -> 64 비트운영체제 Compute/Compute By -> Rollup 이후버전에서제거될기능 어떤버전에서제거될지는아직정해지지않음 Text,ntext,image -> varchar(max),nvarchar(max),varbinary(max)