IDENTITY() 함수

IDENTITY()함수는 결과집합의 정렬순서를 지정하기 위해서 사용하기에 적합하지 않다.

T-SQL이 매우 강력한 도구이기는 하지만, 결과집합에 순위(rank)를 부여하는 것과 같은, 일부 꼭 필요한 작업이기는 하지만, T-SQL 언어의 기본기능으로 제공되지 않는 기능도 있다. 이번 호 기사에서 결과집합에 순위를 부여하는 방법을 설명하고자 하는 것은 아니다.
대신에 지난 수 개월 동안 여러 고객 사이트에서 작업을 진행하면서 각 사이트에서 사용되고 있었던 순위 부여에 관련된 기술의 문제점에 대해서 소개하고자 한다.
이번 호의 팁은 마이크로소프트의 기사 "INF: ORDER BY 절, TOP 연산자, SET ROWCOUNT 구문이 포함된 SELECT INTO 문장에서 IDENTY함수가 동작하는 방법"
(http://support.microsoft.com/?kbid=273586)을 근거로 한 것이다.

[리스트1]에 나타나 있는, Col1을 기준으로 order by 된 데이터집합에 순위를 부여하는 예제를 살펴보자.
T-SQL에서는 순위를 부여하는 기능을 제공하지 않기 때문에 많은 SQL서버 사용자들은 다음과 같이 IDENTITY()함수를 사용하여 순위를 부여하는 작업을 해 왔다.

SELECT EmpId, EmpSalary, LastName, SalaryRank
              = IDENTITY(int, 1,1)
INTO      ListOfHighestPaidEmployees
FROM    employees
ORDER BY EmpSalary desc

위의 쿼리는 기존 결과집합에 ORDER BY 절을 사용하여 정렬한 결과를 순위 컬럼으로 추가하여 새로운 테이블로 생성한다.
예를 들어[그림1]과 같이 세 행의 정보가 들어 있는 Employees테이블이 있다면 최종 사용자는 정렬된 상태의 ListOfHighestPaidEmployees테이블을 사용하기를 원할 것이다.

EmpID     EmpSalary     LastName
1               1,000,000           Moran
2                    50,000           Jones
3                  100,000            Smith
>> 그림1. Employees테이블

EmpID      EmpSalary          LastName        SalaryRank
1                1,000,000                Moran          1
3                   100,000                 Smith          2
2                     50,000                Jones          3
>> 그림2. ListOfHighestPaidEmployees테이블

IDENTITY()함수는 각 행에 유일한 값을 생성한다는 것에 착안하여 사용자들은 SQL서버가 ORDER BY 한 결과를 기준으로 하여 identity값을 부여할 것이라고 생각한다. 하지만, IDENTITY()함수는 ORDER BY 절에 의해서 정렬된 결과에 순차적으로 갑을 부여하는 것은 아니다.
 앞에서 언급한 마이크로소프트 기사에 포함되어 있는 예제 쿼리를 실행해 보면 ORDER BY 절에 지정된 행의 위치와 부여된 순위가 일치하는 것도 있고, 그렇지 않은 것도 있다는 것을 알 수 있다. 대부분 ORDER BY 절에 포함된 컬럼 목록을 기준으로 결과값에 순위가 부여될 것이라고 기대하지만, 실제로 SQL 서버 관계형 엔진에서는 결과집합에 ORDER BY 절을 이용하기 전에 먼저 IDENEITY()함수를 적용한다. 즉 SQL서버는 ORDER BY 절에 의해 결과집합을 정렬하기 전에 먼저 IDENTITY 값을 할당하게 된다. 병렬 처리 쿼리는 ORDER BY 절을 처리하기 전에 IDENTITY값을 할당해야 하는 이유를 설명할 수 있는 좋은 예제가 된다. 데이터집합에 5행 이상의 데이터가 포함되어 있고, 이를 4개의 병렬처리 스레드로 분할하여 쿼리하는 경우를 생각해 보자. Employees테이블에 100,000개의 행이 있고, 이를 4개의 스레드로 분할하여 병렬로 쿼리를 처리한다고 가정해보자. 이러한 경우, SQL서버가 행에 대한 처리를 하는 과정에서 각 스레드별 경렬처리 대상마다 별도의 identity값을 할당해야만 한다. 반면에, SQL서버에서는 분할된 4개의 스레드로부터 행에 대한 가져오기 작업을 완료한 다음, 병렬처리의 결과값을 다시 순서에 따라 정렬하게 된다. 이러한 이유로, IDENTITY()함수를 사용하더라도, ORDER BY절에 의해서 정렬된 결과값을 기준으로 순위를 부여할 수 없게 되는 것이다.
 물론, ORDER BY 절의 정렬순서를 반영한 상태에서 identity값을 부여하는 기능을 하는, IDENEITY()함수와 유사한 함수를 제공해야 한다고 생각할 수도 있다.
하지만, 아직까지는 IDENTITY()함수는 정렬순서와 상관없이 identity값을 부여한다. 반가운 소식은 SQL서버 2005에서는 새로운 RANK()함수와 DENSE_RANK() T-SQL 확장을 제공하여 순위를 부여하는 작업에 대한 여러가지 문제를 간단하고 효율적으로 해결할 수 있도록 해 준다는 사실이다.

- Brian Moran - 북버지니아 SQL서버 유저그룹 회장. Image Database의 설립자. MCDBA, MCT