1 SQL Server 2000은 Group By시 무조건 정렬되지 않는다

2 테스트 준비

3 SQL Server 2005에서의 정렬

4 결론

5 참고자료

MSSQL Server 2000의 옵티마이저는 7.0의 옵티마이저 그대로 따라온 것이 아니다. 가장 많은 혼란을 격고 있는 것은 바로 GROUP BY의 정렬여부이다. 결론부터 말하자면 Group By는 정렬을 하기도 하고, 하지 않을 수도 있다. 왜냐하면 옵티마이저가 상황에 따라서 어떤 연산을 하느냐가 틀려지기 때문이다. 물론 확실히 정렬연산을 한다고 확신한다면 정렬에 대한 명시를 하지 않아도 된다.

USE NORTHWIND
GO
 
SELECT CUSTOMERID FROM ORDERS
GROUP BY CUSTOMERID

결과를 보면 알 수 있듯이 Stream Aggregate 연산을 했다. 결과를 보면 정렬이 되어 있는데, 이것은 정렬하여 그룹연산을 했기 때문이다. 만약 CustomerID에 인덱스가 없다면 즉, 인덱스를 사용하지 않으면 따로 정렬비용이 생긴다.

SELECT CUSTOMERID FROM ORDERS (INDEX = 0)
GROUP BY CUSTOMERID

그러나 다음과 같은 쿼리를 하게 되면 Stream Aggregate는 없어지고 Hash Match 연산을 하게 된다.

SELECT CUSTOMERID, SUM(FREIGHT)
FROM ORDERS
GROUP BY CUSTOMERID

결과를 보면 알겠지만 결과가 정렬되지 않았음을 알 수 있다. 그러므로 MSSQL Server 2000 이상의 버전을 사용할 때는 Group By를 한다고 해서 반드시 정렬되는 것이 아니므로 Order By를 명시해야 한다. 물론 인덱스를 위한 정렬도 MSSQL Server가 알아서 해준다. 인덱스를 이용하여 정렬을 할 때는 정방향으로 읽을 것인지 역방향으로 읽을 것인지 Order By에 명시한 대로 알아서 움직인다. 이런 것을 보면 꽤 똑똑하다.

일단 테스트를 위한 준비를 한다.

USE AdventureWorks2008;

IF OBJECT_ID('dbo.TEST') IS NOT NULL
	DROP TABLE dbo.TEST;
GO

CREATE TABLE dbo.TEST
(
	CustID varchar(20)
,	NM varchar(20)
,	Dumy varchar(50)
,	RegDT datetime
);

CREATE CLUSTERED INDEX CIX ON dbo.TEST(RegDT);
CREATE INDEX NIX ON dbo.TEST(CustID);

각각의 세션1,2,3에서 다음의 스크립트를 약 1분간 실행한다.

--Session 1
USE AdventureWorks2008;

SET NOCOUNT ON 
SET STATISTICS IO OFF

WHILE(1=1)
BEGIN
	INSERT dbo.TEST 
	SELECT
		LEFT(NEWID(),20)
	,	LEFT(NEWID(),20)
	,	NEWID()
	,	GETDATE()
	FROM Person.Person
END

--Session 2
USE AdventureWorks2008;

SET NOCOUNT ON 
SET STATISTICS IO OFF

WHILE(1=1)
BEGIN
	INSERT dbo.TEST 
	SELECT
		LEFT(NEWID(),20)
	,	LEFT(NEWID(),20)
	,	NEWID()
	,	GETDATE()
	FROM Person.Person
END

--Session 3
USE AdventureWorks2008;

SET NOCOUNT ON 
SET STATISTICS IO OFF

WHILE(1=1)
BEGIN
	INSERT dbo.TEST 
	SELECT
		LEFT(NEWID(),20)
	,	LEFT(NEWID(),20)
	,	NEWID()
	,	GETDATE()
	FROM Person.Person
END

SQL Server 2005에서 정렬 방식이 약간 바뀌었다. SQL Server 2000 이하버전은 기본적으로 테이블에 클러스터드 인덱스가 있으면 클러스터드 인덱스를 이용하여 스캔을 했다. 클러스터드 인덱스가 테이블에 없다면 IAM(Index Allocation Map)을 이용하여 스캔을 한다. 어쨌든 SQL Server는 다음과 같은 2가지 방식의 스캔을 한다. 왜 2가지? 스캔방식에 따른 성능차이가 존재하기 때문이다.

• Index-Order Scan
  
• Allocation-Order Scan
  
  

각각 어떤 상황에서 발생하는지 살펴보자.

SELECT TOP 10000 CustID FROM dbo.TEST

위 쿼리의 결과는 인덱스의 정렬과는 무관하다. 그러므로 Ordered:False이다.만약 ORDER BY CustID가 추가가 된다면 Ordered:True가될 것이다. 즉, 인덱스를 이용한 정렬을 필요로 한다.

SELECT TOP 10000 * FROM dbo.TEST(NOLOCK)
--Table 'TEST'. Scan count 1, logical reads 136, physical reads 0

두 번째 쿼리도 인덱스의 정렬과는 무관하다고 SQL Server는 판단한다. 사용자는 클러스터드 인덱스의 순서대로 TOP 10000 개의 행을 결과로 받아 볼 것을 예상했지만 SQL Server는 NOLOCK 힌트로 가장 빠른 결과를 받아 볼 수 있는 스캔방식인 "Allocation-Order Scan"을 선택한다.

SELECT TOP 10000 * FROM dbo.TEST
--Table 'TEST'. Scan count 1, logical reads 144, physical reads 0

세 번째 쿼리는 애매모호하다. 실제로 데이터를 확인해보니 클러스터드 인덱스를 이용한 정렬방식을 사용했음에도 불구하고 Ordered:False로나왔다. 이에 대해서는 어떤 문서도 찾지 못했다.

SELECT TOP 10000 * FROM dbo.TEST ORDER BY RegDT
--Table 'TEST'. Scan count 1, logical reads 144, physical reads 0

네 번째 쿼리는 ORDER BY RegDT를 명시했으므로 SQL Server는 인덱스를 이용한 정렬이 필요하다고 판단한다. 그래서 Ordered:True로나온다. Ordered:False와 Ordered:True에대한 실제 데이터를 비교해보자.

SELECT TOP 10000 * FROM dbo.TEST(NOLOCK)
SELECT TOP 10000 * FROM dbo.TEST ORDER BY RegDT
--Table 'TEST'. Scan count 1, logical reads 136, physical reads 0
--Table 'TEST'. Scan count 1, logical reads 144, physical reads 0

NOLOCK 힌트를 줬을 때에 "Allocation-Order Scan" 방식(Ordered:False)으로 스캔을 하였고, ORDER BY RegDT를 명시한 쿼리는 "Index-Order Scan" 방식(Ordered:True)으로 스캔을 하였다. "Allocation-Order Scan"은 정렬과는 무관하므로 두 쿼리의 결과는 결과는 틀리게 나올 수 있다는 것을 확인할 수 있다. 재수가 좋으면 두 쿼리의 결과는 같을 수 있다. IAM과 클러스터드 인덱스의 논리적 연결 상태(페이지의 링크드 리스트 순서)가 같을 경우가 같은 결과를 볼 수 있는 경우다.

예전에는 위의 쿼리의 결과는 모두 동일하였다. 하지만 SQL Server 2005버전부터는 달라졌다. 무조건 Index-Order Scan을 하는 것이 아니므로 원하는 정렬된 결과를 원한다면 반드시 ORDER BY를 명시해야 한다. 이를 무시한다면 황당한 결과가 나올 수 있으니 주의해야 한다.

SQL Server 2005의 정렬에 대한 변경사항은 SQL Server Read-Consistency Problems, Part 1 (SQL매거진, Itzik Ben-Gan)를 참고하였습니다. Ordered에 대한 내용은 윤주형님께서 도움을 주셨습니다.