함수와 저장 프로 시저

함수와 저장 프로 시저

---

결과로 테이블을 반환해야하는 T-SQL 코드를 구현해야한다고 가정 해 보겠습니다. 테이블 반환 함수 또는 행 집합을 반환하는 저장 프로 시저를 구현할 수 있습니다. 무엇을 사용해야합니까?

요컨대 내가 알고 싶은 것은 다음과 같습니다.

함수와 저장 프로 시저의 주요 차이점은 무엇입니까? 둘 중 하나를 사용할 때 고려해야 할 사항은 무엇입니까?

---

이 코드 조각의 결과를 다른 테이블과 결합하려는 경우 분명히 테이블 반환 함수를 사용하면 단일 SELECT 문으로 결과를 구성 할 수 있습니다.

일반적으로 계층 구조가 있습니다 (View <TV Function <Stored Proc). 각각에서 더 많은 작업을 수행 할 수 있지만 출력을 구성하는 능력과 기능이 증가함에 따라 최적화 프로그램이 실제로 관여하는 능력은 감소합니다.

따라서 원하는 결과를 표현할 수 있도록 최소한으로 사용하십시오.

---

함수는 결정적이어야하며 데이터베이스를 변경하는 데 사용할 수 없지만 저장 프로 시저를 사용하면 삽입 및 업데이트 등을 수행 할 수 있습니다.

크고 복잡한 쿼리에 대해 큰 확장 성 문제를 야기하므로 함수 사용을 제한해야합니다. 쿼리 옵티 마이저를위한 일종의 "블랙 박스"가되며 함수를 사용하는 것과 단순히 코드를 쿼리에 삽입하는 것 사이의 성능면에서 엄청난 차이를 볼 수 있습니다.

그러나 그들은 매우 특정한 경우에 테이블 값 반환에 확실히 유용합니다.

쉼표로 구분 된 목록을 구문 분석해야하는 경우 배열을 프로 시저에 전달하는 것을 시뮬레이션하기 위해 함수가 목록을 테이블로 변환 할 수 있습니다. 이것은 Sql Server 2005의 일반적인 관행입니다. 아직 테이블을 저장 프로 시저에 전달할 수 없기 때문입니다 (2008에서는 가능합니다).

---

문서에서 :

저장 프로 시저가 다음 기준을 충족하는 경우 테이블 반환 함수로 다시 작성하기에 적합한 후보입니다.

• 논리는 단일 SELECT 문으로 표현할 수 있지만 매개 변수가 필요하기 때문에 뷰가 아니라 저장 프로 시저입니다.

• 저장 프로시 저는 테이블 변수를 제외하고 업데이트 작업을 수행하지 않습니다.

• 동적 EXECUTE 문이 필요하지 않습니다.

• 저장 프로시 저는 하나의 결과 집합을 반환합니다.

• 저장 프로 시저의 주요 목적은 임시 테이블에로드 될 중간 결과를 빌드 한 다음 SELECT 문에서 쿼리하는 것입니다.

---

저장 프로 시저와 함수간에 몇 가지 흥미로운 차이점을 작성하겠습니다.

• 선택 쿼리에서는 함수를 사용할 수 있지만 선택 쿼리에서는 저장 프로 시저를 사용할 수 없습니다.

• 함수에서는 비 결정적 함수를 사용할 수 없지만 저장 프로 시저에서는 비 결정적 함수를 사용할 수 있습니다. 이제 질문이 나오면 비 결정적 함수가 무엇인지 .. Ans는 다음과 같습니다.

  비 결정적 함수는 getdate ()와 같이 서로 다른 시간에 동일한 입력 값에 대해 서로 다른 출력을 반환하는 함수입니다. 실행될 때마다 항상 다른 값을 반환합니다.

  예외:-

  SQL 2000 이전 버전의 SQL Server에서는 사용자 정의 함수에서 getdate () 함수를 사용할 수 없지만 2005 버전 이상에서는 사용자 정의 함수 내에서 getdate () 함수를 사용할 수 있습니다.

  Newid ()는 비 결정적 함수의 또 다른 예이지만 사용자 정의 함수에서는 사용할 수 없지만 저장 프로 시저에서는 사용할 수 있습니다.

• 저장 프로 시저 내에서 DML (삽입, 업데이트, 삭제) 문을 사용할 수 있지만 물리적 테이블 또는 영구 테이블의 함수에는 DML 문을 사용할 수 없습니다. 함수에서 DML 작업을 수행하려면 영구 테이블이 아닌 테이블 변수에 대해 수행 할 수 있습니다.

• 함수 내에서 오류 처리를 사용할 수 없지만 저장 프로 시저에서 오류 처리를 할 수 있습니다.

---

1. 프로시 저는 0 또는 n 개의 값을 반환 할 수있는 반면 함수는 필수 인 하나의 값을 반환 할 수 있습니다.

2. 프로시 저는 입력 / 출력 매개 변수를 가질 수 있지만 함수는 입력 매개 변수 만 가질 수 있습니다.

3. 프로시 저는 선택 문과 DML 문을 허용하는 반면 함수는 선택문 만 허용합니다.

4. 함수는 프로 시저에서 호출 할 수 있지만 프로시 저는 함수에서 호출 할 수 없습니다.

5. 예외는 프로 시저에서 try-catch 블록으로 처리 할 수있는 반면 try-catch 블록은 함수에서 사용할 수 없습니다.

6. 우리는 절차 상 거래 관리를 할 수 있지만 기능을 할 수는 없습니다.

7. 프로시 저는 select 문에서 사용할 수 없지만 function은 select 문에 포함 할 수 있습니다.

8. UDF (사용자 정의 함수)는 WHERE/ HAVING/ SELECT섹션 의 SQL 문에서 사용할 수 있지만 저장 프로시 저는 사용할 수 없습니다.

9. 테이블을 반환하는 UDF는 다른 행 집합으로 처리 될 수 있습니다. 이것은 JOIN다른 테이블과 함께 s 에서 사용할 수 있습니다 .

10. 인라인 UDF는 매개 변수를 사용하는 뷰로 간주 될 수 있으며 JOINs 및 기타 행 집합 작업에 사용할 수 있습니다 .

---

함수가있는 경우이를 SQL 문의 일부로 사용할 수 있습니다. 예를 들면 다음과 같습니다.

SELECT function_name(field1) FROM table

저장 프로 시저에 대해서는 이러한 방식으로 작동하지 않습니다.

---

테이블 값 함수와 저장 프로 시저 모두에서 실행되는 동일한 코드 (긴 SELECT 문)와 곧은 EXEC / SELECT를 사용하여 오래 실행되는 논리 비트로 몇 가지 테스트를 실행했으며 각각 동일하게 수행했습니다.

제 생각에는 항상 저장 프로 시저 대신 테이블 반환 함수를 사용하여 결과 집합을 반환합니다. 그 이유는 이후에 조인되는 쿼리에서 논리를 훨씬 쉽고 읽기 쉽게 만들고 동일한 논리를 재사용 할 수있게 해주기 때문입니다. 너무 많은 성능 저하를 방지하기 위해 종종 "선택적"매개 변수 (즉, NULL을 전달할 수 있음)를 사용하여 함수가 결과 집합을 더 빨리 반환 할 수 있도록합니다. 예 :

CREATE FUNCTION dbo.getSitePermissions(@RegionID int, @optPersonID int, optSiteID int)
AS
RETURN 
    SELECT DISTINCT SiteID, PersonID
    FROM dbo.SiteViewPermissions
    WHERE (@optPersonID IS NULL OR @optPersonID = PersonID)
    AND (@optSiteID IS NULL OR @optSiteID = SiteID)
    AND @RegionID = RegionID

이렇게하면 다양한 상황에서이 기능을 사용할 수 있으며 성능이 크게 저하되지 않습니다. 나중에 필터링하는 것보다 이것이 더 효율적이라고 생각합니다.

SELECT * FROM dbo.getSitePermissions(@RegionID) WHERE SiteID = 1

필자는이 기술을 여러 기능에서 사용했으며 때로는이 유형의 "선택적"매개 변수 목록이 길기도합니다.

---

내가 반환하는 모든 것이 영향이없는 단일 테이블 일 때 개인적으로 테이블 값 함수를 사용합니다. 기본적으로 매개 변수화 된 뷰처럼 취급합니다.

If I need multiple recordsets returned or if there will be values updated in tables, I use a stored procedure.

My 2 cents

---

As mentioned above, functions are more readable/composable/self documenting, but are less performant in general, and can be seriously less performant if you get carried away with them in joins such as

SELECT *
FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1
INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2
    ON (tvf1.JoinId = tvf2.JoinId)

Often, you just have to accept the redundancy of code that a tvf could eliminate (at a unacceptable performance cost.)

One other point I haven't yet seen mentioned is that you can't use database state-changing temp tables inside of a multi-statement tvf. The most functionally equivalent mechanism to a temp table is the non-state changing, in memory table variable, and for large datasets, a temp table will likely be more performant than a table variable. (Other alternatives include dynamic tables & common table valued expressions, but at some level of complexity, these cease to be a good option IMO.)

---

I would perfromance test both. It is likely the sp approach or a derived table would be significantly faster than a function and if so that approach should be used. In general I avoid functions becasue they can be performance hogs.

---

It depends :) If you want to use the table-valued result in another procedure, you're better of using a TableValued Function. If the results is for a client, the stored proc is usualy the better way to go.

참고URL : https://stackoverflow.com/questions/178128/functions-vs-stored-procedures