가늘어도 긴놈이 장땡

다음 sp 를 봅시다.



test 테이블의 데이터는 이렇게 되어 있습니다.


데이터가 이렇게 되어 있으니까 select문은 아무것도 리턴하지 않겠지요. 아마도 이런 상황을 대비해서 isnull() 을 썼을 겁니다. 그러면 과연 예상대로 0 이 나올까요??

...

돌려보시면 안그렇습니다. isnull() 에도 불구하고 NULL 이 리턴됩니다. isnull 이 동작하려면 실제로 NULL이라는 값이 테이블에 존재하고, 그것을 읽어와야지 됩니다. 그런데 이 경우, 아예 읽어온 것이 아무것도 없죠. 따라서 declare 다음에 set @v = 0 을 해줘야 합니다. 초기화를 한 후에 sp 를 돌려봅시다.

어떻습니까? 0 이 나오지요??

사실 어떤 언어로 개발을 하던 변수 초기화는 매우 중요한 문제입니다. T-SQL이라고 예외는 아니지요. sp가 알 수 없는 데이터를 리턴한다던가, 예상치 않은 방식으로 동작한다면 저런 경우가 없는지 한번쯤 의심해 볼 일입니다. ;-)


--------추가---------
위의 예문에서 초기화를 하지 않았을 경우 조회하는 결과값이 항상 하나만 리턴된다면

select @v = isnull(min(col1),0) from test where col2 = '1'

이렇게 해도 됩니다.

Posted by maceo

박노철
maceo.park@gmail.com
http://merritt.co.kr

SQL Server 를 보면 isnull 과 coalesce 가 있다. isnull 은 다 아는거고 coalesce 는 사람들이 잘 모르는데, 넘겨받은 n개의 파라미터중 null 이 아닌 첫번째 값을 리턴하는 함수다. (사족인데, isnull 은 ANSI 표준이 아니고 coalesce 는 ANSI 표준이다. Joe Celko 책에는 isnull 의 자리에 coalesce 을 사용하고 있다. )

우찌됐던간에, coalesce 는 함수 자체의 특성상 다음과 같은 상황에 유용하게 쓰일 수 있다.


어떤 제약사항이 우선순위를 가지고 적용되는 경우라 할 수 있겠다. coalesce 를 써서 위의 로직을 대충 적어보면 다음과 같다.



물론 isnull 로도 구현할 수 있다. isnull 을 중첩해서 쓰면 되니까.



하지만 코드는 coalesce 쪽이 더 깔끔해보인다.

그리고 이제사 본론인데, coalesce 가 실제 성능상 더 좋은 효율을 보인다. 만약 cate 에서 NULL 이 아닌 val 값이 리턴되었다면 "상식적으로" prd 나 seller 테이블을 액세스할 필요가 없다. 그런데 isnull 은 그런 상식과 반대로 행동한다. 일단 무조건 cate, prd, seller 를 모두 액세스한 다음, isnull 연산을 시작한다. 반면 coalesce 는 첫번째 파라미터가 NULL 이 아닌 값이 리턴이 되면 그 다음 파라미터들은 evaluation 하지 않고 처리를 끝낸다. 따라서 cate 에서 NULL 아닌 값이 리턴되었다면 prd, seller 테이블은 액세스하지 않는다.

믿기지 않겠지만 사실이다. ^^;

확인해보기 위해서는 set statistics io on 을 해서 나오는 scan count 를 보면 된다. isnull 을 쓰면 세개 테이블 모두 scan 카운트가 1이지만 coalesce 는 cate 만 scan count 가 1이고 나머지 테이블의 scan count 는 0 이다.

coalesce 를 쓸 때 주의할 점이 하나 있다. 위의 사례는 coalesce 안에 서브쿼리를 사용한 것이다. SQL서버 2000, 2005 모두 coalesce 안에 서브쿼리가 들어가면 실행계획이 엉망이 되는 버그? 가 있다. 아마 위의 쿼리를 그냥 실행하면 cate, prd, seller 테이블을 두번씩 액세스하는 실행계획이 세워질 것이다. (참조 : http://www.aspfaq.com/show.asp?id=2532 )아까전에는 scan count 가 1 이라고 했지만, 사실은 잘못된 실행계획때문에 scan count가 2이다. 이를 방지하기 위해서는 쿼리를 다음과 같이 만들어야 한다.



cate_id, prd_id, seller_id 가 PK 이지만 일부러 min 을 씌워서 값이 없을때도 확실하게 NULL 을 리턴하도록 했고, top 1 을 해서 한개의 값만 가져오도록 했다 .이렇게 하면 정확하게 우리가 원하느대로 scan count 는 1이 나오면서 불필요한 테이블을 액세스하지 않는다. coalesce 와 서브쿼리의 실행계획에 대한 문제, 기타 isnull 과 coalesce 에 관해서는 다음 문서를 참조한다.

http://www.examnotes.net/archive79-2002-8-55509.html 에서 Umachandar Jayachandran 의 리플라이

http://toponewithties.blogspot.com/2004/08/differences-between-coalesce-and.html

여기를 시작으로 블로그 트랙백을 따라가다 보면 엄청많은 문서들이 있다.

Posted by maceo