MSSQL 결정적 함수, 비결정적 함수 설명

함수 구현 시 사용 가능 문법, 불가능한 문법을 구별하지 못해 힘들어하는 구독자님을 위해서 결정적 함수와 비결정적 함수의 정확한 개념을 설명하겠습니다.

 

결정적 함수, 비결정적 함수 비교.

결정적 함수(Deterministic Functions).

• 결정적 함수는 데이터베이스의 입장에서 Data가 결정되어 결과를 반환할 수 있다는 의미로 생각하면 이해하기 쉽습니다.
• 즉, 대표적인 결정적 함수가 SUM() 함수가 있습니다. 
• 데이터베이스는 결정된 테이블, 칼럼의 합계입니다.

비결정적 함수(nondeterministic functions).

 

 

결정적 함수, 비결정적 함수 에러.

• 결정적 함수는 데이터베이스의 입장에서 반환해야 하는 Data를 결정하지 못해서 함수에서 사용하면 에러가 발생합니다.
• 즉, 대표적인 결정적 함수가 NEWID() 함수가 있습니다. 
• NEWID()는 Random 함수로 데이터베이스의 입장으로, 함수에서 반환할 때 값을 결정할 수 없다는 의미입니다.
• 추가로 try-catch, delete, update, print 등도 함수에서 사용하면 에러가 발생합니다.
• 비결정적 함수의 원리를 정확하게 이해했으면 NEWID()를 직접 호출하지 않고 view를 통해서 반환받으면 가능합니다.
• View 대한 참조 링크입니다.  https://aurumguide.tistory.com/7

NEWID() 사용 시 에러.

• Msg 443, Level 16, State 1, Procedure AurumGuide_DeterministicFunc, Line 10 [Batch Start Line 21]
  Invalid use of a side-effecting operator 'newid' within a function.
• 소스 코드.

CREATE FUNCTION dbo.AurumGuide_DeterministicFunc 
(
 @PARAM1 varchar(10)
,@PARAM2 varchar(10)
)
RETURNS varchar(100)
AS
BEGIN
	declare @returnStr varchar(100);
	set @returnStr  = 'Hello World ' +  CAST(NEWID() AS VARCHAR(50));
	RETURN @returnStr
END;
go

 

try-catch, delete, update, print 사용 시 에러.

• Msg 443, Level 16, State 15, Procedure AurumGuide_DeterministicFunc1, Line 16 [Batch Start Line 40]
  Invalid use of a side-effecting operator 'DELETE' within a function.
• Error 대한 설명.

비결정적 함수 에러 설명 입니다.

• 소스코드.

DROP TABLE IF EXISTS AurumGuide_Deterministic;
-- Create Sample Table 
CREATE TABLE AurumGuide_Deterministic (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255) NOT NULL,
    AurumAge          INT  NULL,
    AurumAddress      VARCHAR(500)  NULL
);
-- Create Sample Data
INSERT INTO dbo.AurumGuide_Deterministic(AurumId,AurumNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
      ,(276, N'Linda');
GO      
CREATE FUNCTION AurumGuide_DeterministicFunc1 (@AurumId int)
RETURNS @Return_AurumGuide  TABLE
(	AurumId int,
	AurumNm varchar(255) NULL 
) AS
BEGIN
	/*
	 PRINT(@ParamAurumId);
	 DELETE FROM AurumGuide_Deterministic;
	 INSERT AurumGuide_Deterministic(AurumId) values(100);
	 UPDATE AurumGuide_Deterministic SET AurumId=1;
	 SELECT newid();
   */
	IF(@AurumId IS NOT NULL)
	BEGIN
		DELETE FROM AurumGuide_Deterministic;

		INSERT @Return_AurumGuide
		SELECT AurumId,AurumNm
		  FROM AurumGuide_Deterministic af	 
		 WHERE af.AurumId  =  @AurumId;
	END 
	RETURN;
END;