[출처 : 데브피아]
김정선
Microsoft SQL Server MVP
1. SQL Server 2005 GROUP BY 구문 변경
SQL Server 2005에서 변경된 구문들을 몇 가지 살펴보고자 합니다.
그 첫 번째로 바로 얼마전에 알게된, GROUP BY 입니다.
BOL이나 문서의 언급이 없어서 참 당황스러운 결과였습니다.
Itzik (SQL Server Magazine 저자, Inside SQL Server 2005 필자)에게 문의를 한 결과
변경된 사실을 최종 확인했습니다.
대신에, 정확히 어떻게 바뀌었다는 것에 대해서는 상세 자료가 없습니다.
간단히 정리해 드리면,
- GROUP BY 절에 칼럼을 포함한 수식을 적용해서, 수식의 결과를 기준으로 만들 수 있고,
- SELECT 절의 결과 집합을 그 수식과 달리 변형해서 처리하는 것이 가능해 졌다.
입니다.
아래 예제를 직접 실행하셔서 확인해 보시면 됩니다.
-- 아래와 같은 코드는 2000, 2005 모두 가능 SELECT ISNULL(CONVERT(CHAR(7), OrderDate, 120), '') , COUNT(*) AS 'OrderCount' FROM Northwind.dbo.Orders GROUP BY OrderDate
-- 아래 코드는, 2000에서 안됨 SELECT ISNULL(CONVERT(CHAR(7), OrderDate, 120), '') , COUNT(*) AS 'OrderCount' FROM Northwind.dbo.Orders GROUP BY CONVERT(CHAR(7), OrderDate, 120) |
2. SQL Server 2005 Table Hint의 WITH 키워드 구문 변경
힌트(Hint) 많이 쓰시나요?, 다른 건 몰라도 NOLOCK(혹은 ReadUncommitted)은 많이 쓰시죠?
FROM table_source WITH (table_hint, ...n) |
2000에서는 WITH 키워드가 옵션이었죠! 역시나 귀차니즘으로 인해 생략하고 사용하는 경우가
대부분이었습니다. 그러다 보니 너무 많이 생략해서 문제가 되는 경우도 있었습니다.
( 괄호까지 생략해 버렸다는...그럼, 어떻게 될까요? ^^; )
2005에서 WITH 절에 강제 사항이 붙었습니다. 굳이 문법적인 내용이 아니더라도
너무 생략해서 쓰시는 습관은 좋지 않죠?
어떤 경우가 실제 문법적으로 문제가 되는지 한 번 보실 필요가 있습니다.
아래 예제 코드를 실행하고, 비교해 보시구요,
더 자세한 내용은, 온라인 설명서의 sp_dbcmptlevel 와 테이블 힌트를 참조하십시오.
참고.
Hint의 번역이 2000의 "참고"에서, 2005의 "힌트"로 변경된 것으로 보입니다. 한글 번역이 많이 바뀐 거 아시죠? 더 좋아진 내용이 많았으면 하는 개인적인 바램입니다...아~ 걱정, 걱정 |
예제.
-- 1. -- 하나의 Hint만 사용하는 경우 -- 2000, 2005: OK SELECT TOP 5 * FROM Northwind.dbo.Orders (READUNCOMMITTED)
-- 2-1. -- Hint를 다른 옵션과 함께 사용하는 경우, 반드시 WITH 키워드를 사용해야 합니다. -- 2000: OK, 2005: Error SELECT TOP 5 * FROM Northwind.dbo.Orders (INDEX(0), READUNCOMMITTED)
메시지 1018, 수준 15, 상태 1, 줄 2 'INDEX' 근처의 구문이 잘못되었습니다. 테이블 힌트의 일부인 경우에는 WITH 키워드 및 괄호가 필요합니다. 적합한 구문은 SQL Server 온라인 설명서를 참조하십시오.
-- 2-2. -- 두 개의 Hint를 사용하는 경우 (WITH 지정) -- 2000: OK, 2005: OK SELECT TOP 5 * FROM Northwind.dbo.Orders WITH (INDEX(0), READUNCOMMITTED) |
3. SQL Server 2005 Order By절 구문 변경
SQL Server 2000의 ORDER BY 절에서는 모호함이 많이 있었습니다. 이것이 제약없는 쿼리를
사용할 수 있다는 편리함을 주기도 하지만, "데이터 일관성"의 대명제를 가진 DB에서는 옳지 못한
구현이죠.
SQL Server 2005의 ORDER BY 는 그러한 모호함을 줄이고자 노력한 흔적이 보입니다.
아래 예제를 통해서, 2005에서 달라진 구문 제약 능력을 보시기 바랍니다.
더불어, 혹여 모르고 있었을만한 2000의 문제에 대해서도 알아보는 시간이 될 수 있습니다.
예제.
-- BOL 예제 USE tempdb; CREATE TABLE SampleTable(c1 int, c2 int); GO
-- SELECT 절에서 c1 이라는 별칭이 중복된다. -- 2000: OK, 2005: Error SELECT c1, c2 AS c1 FROM SampleTable ORDER BY c1; -- c1이 중복되므로... GO
-- 오류 발생 메시지 209, 수준 16, 상태 1, 줄 2 열 이름 'c1'이(가) 불확실합니다.
-- 테이블 이름(별칭)과 함께, 칼럼 별칭을 사용한 경우 -- 2000: OK, 2005: Error SELECT c1 AS x FROM SampleTable ORDER BY SampleTable.x; -- 해당 열이 없으므로 GO -- 오류 발생 메시지 207, 수준 16, 상태 1, 줄 3 열 이름 'x'이(가) 잘못되었습니다.
-- 2000: 테이블 별칭 p가 무시되고 수식 UnitPrice에 대해서 정렬됨 -- 2005: 칼럼 UnitPrice에 대해서 정렬됨 SELECT TOP 5 ProductID , UnitPrice = UnitPrice * (-1) , UnitPrice AS U FROM Northwind.dbo.Products p ORDER BY p.UnitPrice
|
4. SQL Server 2005 T-SQL Outer Join 미지원
"SQL Server 2005에서는 T-SQL 외부 조인(*=, =*) 연사자를 지원하지 않습니다, OUTER JOIN 키워드를 사용하거나, 혹은 DB를 2000 호환 버전 상태로 사용해야 합니다."
이미 많이 알려진 내용이라 잘 아실거라 짐작되지만, 역시 그렇지 않은 분들도 많이 계실 것이므로 여기에도 소개를 합니다.
SQL Server에서 사용할 수 있는 조인 문법은 크게 두 가지 버전이 있습니다. ANSI 표준 구문 형식과, T-SQL 조인 구문(흔히 Old-style이라고도 부르는) 이 그것입니다. 이 중에서 특히 현업에서 많이 볼 수 있는 것이 T-SQL 조인 구문입니다.
이미 오래전부터 ANSI 표준 구문 사용을 권장했음에도 불구하고, 여러가지 이유로 인해서 여전히 Old-style을 사용하는 분들이 많이 계시죠. 예전 세미나에서, "특정 패키지가 모두 T-SQL 외부 조인을 사용하고 있는데 2005로 업그레이드를 하면 어떻게 해야 하느냐?" 는 질문을 받으적도 있었습니다...상당히 부담스러운 상황이죠?
(SQL Server 게시판에 Q/A를 검색해 보시면 참 많은 이슈들을 보실 수 있습니다.)
특히 Outer Join(외부 조인)의 경우, 그 두 가지 버전의 결과가 달라지므로 인해서 불평을 하시는 경우도 있었습니다. 실제로 술 자리에서 한 시간동안 토론을 벌인 적도 있습니다. (하여튼 술 만 들어가면 이 놈의 직업 이야기... *^^*)
이제는 오랜된 얘기라 여기서 다 풀지는 않겠습니다. 과거 자료를 참조해 보시구요, 물론 앞으로도 2000의 코드는 많이 사용될 겁니다만. 2005부터는 사용할 수도 없겠되었지만, (옵션을 변경해서라도) 사용하지 않는 것이 좋겠습니다! (차기 버전에서는 옵셔으로도 지원이 안될 수 있으니까요 ^^;)
아래 간단한 예제를 올립니다. 도움이 되시길...
예제.
| ||
| ||
"숫자 유형이 변환하지 않고 비교됩니다. 따라서 성능이 향상됩니다.
그러나 이런 경우 동작이 약간 변경될 수 있습니다.
특히, 변환으로 인해 오버플로 예외가 발생하는 경우에 더욱 그렇습니다."
"SQL Server 2000 버전까지는
숫자 유형(smallint, tinyint, int, bigint, numeric, decimal, smallmoney, money) 간 비교는
유형 계층에서 우선 수위가 낮은 피비교수를 우선 순위가 높은 유형으로 변환하여 수행합니다."
이러한 변화는 크게 두 가지 의미에서 그 중요성을 고려해야 합니다. 첫 번째는 데이터 비교 동작 시의
발생할 수 있는 오류 즉 정확성에 대한 문제. 두 번째는 - 사실은 가장 관심 있는 - 성능 문제입니다.
직접적인 예를 들어보시죠. 아래의 WHERE절에 사용된 검색 조건식이 성능 문제를 가지고 있다고
가정을 합니다. 어떤 문제가 있을까요?
SELECT select-list FROM table_source |
이 글을 읽는 분들 중에 상당 수는 관계형 데이터베이스의 SARG(Search ARGument)에 대해서 알고
계시리라 짐작됩니다-혹 모르신다면 반드시 공부하십시오. 그 중에서도 암시적 형 변환으로 인한
SARG의 위반은 사용자의 눈에 잘 드러나지 않는다는 어려움을 가지고 있으면서도 성능에 큰 영향을
미칩니다.
위 쿼리에서 Column 이 문자 타입(char, varchar, 등)이라면 문자 = 숫자 형 간의 비교가 요구되고,
이를 해결하기 위해서 위에서 언급된대로 우선 순위가 낮은 문자 타입를 숫자 형으로 변환하는
중간 과정에서 암시적 형 변환이 일어나는 것입니다.
SQL Server 2005에서 SARG에 대한 Optimizer의 동작에 변화가 보입니다. 그 한 가지가 바로 지금
다루고 있는 암시적 형 변환입니다. 물론 대부분이 좋은 결과를 가져오지만, 여전히 문제가 되는 부분도
있습니다.
암시적 형 변환은 데이터 형에 따라서 그 동작과 결과가 달라집니다.
우선 그 중 한가지, 바로 숫자형에 대한 변환 동작입니다. 이 글의 처음에 주지한대로
SQL Server 2005에서는 숫자 형 간의 변환이 일어나지 않는다는 것입니다.
참고. BOL에서 언급된 내용에는 약간의 오류가 있을 수 있습니다. 숫자형 데이터는 종류가 많은데도
워낙 광범위한 표현을 쓰고 있기 때문입니다. 따라서 각각의 상황은 달라질 수 있습니다.
SARG를 알고 계신다면, SQL Server 2005에서 SARG가 어떻게 변화되었는지에 대해서 자세히
살펴보실 필요가 있으실 겁니다. 이것은 그 중 하나의 포함된 또 하나의 작은 내용일 뿐입니다.
(차후에 그 변화에 대해 전반적으로 소개하는 기회가 있을 것입니다)
간단하지만 아래 예제를 통해서 우선 이 주제를 살펴보시기 바랍니다.
도움이 되시길...
예제. SQL Server 2000
USE Northwind go
/* SQL 2000 */ SET SHOWPLAN_TEXT ON GO -- 숫자값 간의 변환이 일어나지 않는다. SELECT * FROM northwind.dbo.orders WHERE orderid = 1.0 GO
-- 아래는, SHOWPLAN_TEX로 인한 출력 결과. WHERE 조건의 Convert()를 볼 수 있다.
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1002], [Expr1003], [Expr1004])) |--Compute Scalar(DEFINE:([Expr1002]=Convert([@1])-1, [Expr1003]=Convert([@1])+1, [Expr1004]=If (Convert([@1])-1=NULL) then 0 else 6|If (Convert([@1])+1=NULL) then 0 else 10)) | |--Constant Scan |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]),SEEK:([Orders].[OrderID] > [Expr1002] AND [Orders].[OrderID] < [Expr1003]), WHERE:(Convert([Orders].[OrderID])=[@1]) ORDERED FORWARD)
SET SHOWPLAN_TEXT OFF
|
예제. SQL Server 2005
USE Northwind go
/* SQL 2000 */ SET SHOWPLAN_TEXT ON GO -- 숫자값 간의 변환이 일어나지 않는다. SELECT * FROM northwind.dbo.orders WHERE orderid = 1.0 GO
-- 아래는, SHOWPLAN_TEX로 인한 출력 결과. Convert()가 일어나지 않고, SEEK 조건을 만족한다.
|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]),SEEK:([Northwind].[dbo].[Orders].[OrderID]=[@1]) ORDERED FORWARD)
SET SHOWPLAN_TEXT OFF
|
6. SQL Server 2005 ISNUMERIC() 함수에서 쉼표(,)를 무시한다.
"SELECT ISNUMERIC('<string>')에서 <strin>내에 포함된 쉼표는 무시됩니다.
따라서 숫자로 인정될 수 있습니다."
이번 내용은 간단합니다. 말 그대로 쉼표를 숫자 형식에서 무시한다는 것이죠.
따라서 ISNUMERIC() 이 1을 반환합니다.
SQL Server 2000에서는 쉼표를 포함한 경우 숫자 형식이 아닌 것으로 취급, 0을 반환합니다.
아래 그 예제를 보겠습니다.
예제. ISNUMERIC() 함수
SELECT ISNUMERIC('121212,12') -- 숫자 (2000은 0)
----------- 1
(1개 행 적용됨)
|
그런데 온라인 설명서의 언급하고 있는 내용에 문제가 있어 보입니다.
"반환 값이 1인 경우에는expression을 적어도 이러한 숫자 형식 중 하나로 변환할 수 있습니다."
라고 언급합니다. 그렇다면, 일반적인 변환 방법으로 가능할까요? 아니면 특별함 방법을 써야할까요?"
예제. 숫자 형식 변환 작업
SELECT CAST('121212,12' AS int) -- SELECT CONVERT(INT, '121212,12')
----------- 메시지 245, 수준 16, 상태 1, 줄 1 varchar 값 '121212,12'을(를) 데이터 형식 int(으)로 변환하지 못했습니다.
|
SQL Server 2000에서 쉼표를 사용하지 않았으므로, 2005로 변환 시 문제의 소지는 없겠지만 새로운 개발에서 사용자의
실수가 포함된다면 문제 발생의 원인이 될 수도 있겠지요.
차후에 정확한 내용이 규정되리라 기대합니다.