[MSSQL2K] ISNULL 사용시 주의사항

NULL VALUE,
누구에게나 골이 아픈 NULL VALUE 다.
간단하게 NULL +  어떤값? 의 결과는 어떻게 될까?

NULL

이 된다.

모든 데이터베이스 사용자의 교과서라고 할 수 있는,
대용량 데이터베이스 솔루션 I,II 에 보면 NULL값 처리시 주의할 점이 나와있다.

하지만 실제로 해보면 조금씩 다르다.
RDBMS의 버젼에 기인한 차이다. (새로 쓴 1권 말고)2권이 10년전 책임을 감안하면 말이다.

[sql]
CREATE TABLE dbo.TB_SALES
(
SEQ INT IDENTITY(1,1) NOT NULL,
SALESQTY INT NULL,
SALESQTY2 INT NULL,
)
GO

INSERT INTO dbo.TB_SALES VALUES (0,10);
INSERT INTO dbo.TB_SALES VALUES (1,9);
INSERT INTO dbo.TB_SALES VALUES (2,8);
INSERT INTO dbo.TB_SALES VALUES (3,7);
INSERT INTO dbo.TB_SALES VALUES (4,6);
INSERT INTO dbo.TB_SALES VALUES (5,5);
INSERT INTO dbo.TB_SALES VALUES (6,4);
INSERT INTO dbo.TB_SALES VALUES (7,3);
INSERT INTO dbo.TB_SALES VALUES (8,2);
INSERT INTO dbo.TB_SALES VALUES (9,1);
INSERT INTO dbo.TB_SALES VALUES (NULL,0);
INSERT INTO dbo.TB_SALES VALUES (0,NULL);

[/sql]

1부터 10까지의 값과 NULL이 들어간 2행이 더 들어갔다.
전체합을 구해보자

[sql]

SELECT SUM(CASE WHEN ISNULL(A.SALESQTY,0)=0 THEN ISNULL(A.SALESQTY2,0)
ELSE ISNULL(A.SALESQTY,0) END)
FROM DBO.TB_SALES A
GO

SELECT SUM(CASE WHEN A.SALESQTY = 0 THEN A.SALESQTY2
ELSE A.SALESQTY END)
FROM DBO.TB_SALES A
GO
[/sql]

위 쿼리와 아래쿼리의 차이는 ISNULL이 있냐 없냐(오라클의 NVL())의 차이,
위나 아래나 결과가 똑같다. 이유는 SUM() 함수가 NULL 값을 무시하기 때문이다.
따라서 NULL 컬럼이라고 무작정 ISNULL(컬럼,NULL대체값)을 사용하는 것은 비효율을 초래 한다는 것이 책의 설명이고 실제도 그렇다.

그렇다면 2개 이상의 컬럼의 합은 어떻게 될까?
앞서서 분명히 NULL + 임의의 값(어떤 데이터형이든)은 NULL이라고 했다.

[sql]

SELECT ISNULL(SUM( CASE WHEN A.SALESQTY=0 THEN A.SALESQTY + A.SALESQTY2
ELSE A.SALESQTY END),0)
FROM DBO.TB_SALES A
GO

[/sql]

이는 사실 책 관점에서 보면 의미가 없는 쿼리다. SALESQTY1,2 두개 중 하나의 컬럼 값이 NULL만 되도 그 결과값이 틀려지기 때문이다.

저것을 분리해서 표현해야 한다는 것인데, 결과는 어떤가? 알아서 돌려보고.
같다. 아무문제 없다. 메시지창에보면 ANSI_WARNING 만 표시될거다.
집계함수에 의해 NULL이 무시 되었다는 내용의 메시지.

그럼 뭐냐?
언제 주의해야 되는거냐?
다음을 보자.

[sql]

SELECT AVG(CASE WHEN A.SALESQTY = 0 THEN A.SALESQTY2
ELSE A.SALESQTY END)
FROM DBO.TB_SALES A
GO

[/sql]

결과는 왜 이 모양일까?
다음을 보자

[sql]

SELECT SUM(CASE WHEN A.SALESQTY = 0 THEN A.SALESQTY2
ELSE A.SALESQTY END) / COUNT(*)
FROM DBO.TB_SALES A
GO

[/sql]

같은 평균이지만 다르다.
이유는 다음과 같다.

대부분의 집계함수(Aggreate Function)는 NULL 값을 무시(Ignore)한다.

따라서 다음과 같은 쿼리를 실행하면 마찬가지로 잘못된 값을 가져오겠다.

[sql]

SELECT SUM(CASE WHEN A.SALESQTY = 0 THEN A.SALESQTY2
ELSE A.SALESQTY END) / COUNT(A.SALESQTY)
FROM DBO.TB_SALES A
GO

[/sql]

COUNT()함수도 마찬가지로 NULL값을 무시한다.
하지만 *로 수행했을때는 NULL이 없지만, 컬럼명으로 했으니 NULL값이 무시된 것.
그렇다면 AVG()함수도 NULL값을 무시하게 안하려면?
ISNULL을 이때 사용하면 된다.

그럼 다시 처음으로 돌아가자. 왜 무작정 ISNULL을 사용하는 것은 비효율을 초래한다고?
책의 이 한문장만 기억하면 된다.

집계함수의 안쪽은 행(ROW)단위로 수행되고 바깥은 그 결과행(집계가 이루어진, 책에서는 중분류 라고 표현)만큼 수행된다.

[MSSQL2k] Show msg 7306, msg 7395 When using Linked Server in MSSQL 2000

When we are using linked server in sql 2000.
Mostly shown error msgs are msg 7395 and msg 7306.

First msg 7395 is an error message due to xact abort option of session(or database) :

[code lang=”sql”]

서버: 메시지 7395, 수준 16, 상태 2, 줄 1
Unable to start a nested transaction for OLE DB provider ‘SQLOLEDB’. A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]
OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].

[/code]

Solution is simple,
Add
[code lang=”sql”]
SET XACT_ABORT ON
[/code]
to your query will solve the problem.

msg 7306 is an error message due to absence of PRIMARY KEY or CLUSTERED INDEX of destination table.

[code lang=”sql”]
서버: 메시지 7306, 수준 16, 상태 2, 줄 1
Could not open table ‘"linkedserver"."DBO"."yourtablename"’ from OLE DB provider ‘SQLOLEDB’. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider ‘SQLOLEDB’ IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA…
[/code]

Solution is simple,
Create primary key or clustered index to destination table.