함수 vs 저장 프로시저

함수 vs 저장 프로시저

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

요컨대, 내가 알고 싶은 것은:

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

이 코드 조각의 결과를 다른 테이블과 결합하고 싶다면 테이블 값 함수를 사용하여 결과를 단일 SELECT 문으로 구성할 수 있습니다.

일반적으로 계층이 있습니다(표시 <TV 기능> <저장된 프로세서).각 기능에서 더 많은 작업을 수행할 수 있지만 출력을 구성하는 기능 및 최적기가 실제로 관여하는 기능은 기능이 증가함에 따라 감소합니다.

따라서 원하는 결과를 표현할 수 있는 최소한의 방법을 사용하십시오.

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

함수는 크고 복잡한 쿼리에 큰 확장성 문제를 일으키므로 사용을 제한해야 합니다.쿼리 옵티마이저의 '블랙박스'가 되어 함수를 사용하는 것과 단순히 코드를 쿼리에 삽입하는 것 사이에 성능 면에서 큰 차이가 있음을 알 수 있습니다.

그러나 매우 구체적인 경우 테이블 값 수익률에는 확실히 유용합니다.

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

문서에서:

스토어드 프로시저가 다음 기준을 충족하면 테이블 값 함수로 다시 쓰기에 적합합니다.

• 로직은 단일 SELECT 문으로 표현할 수 있지만 파라미터가 필요하기 때문에 뷰가 아닌 스토어드 프로시저입니다.

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

• 다이내믹 EXECUTE 문은 필요 없습니다.

• 저장 프로시저는 하나의 결과 세트를 반환합니다.

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

저장 프로시저와 기능의 흥미로운 차이점을 몇 가지 적어보겠습니다.

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

• 함수에서는 비결정론적 함수를 사용할 수 없지만 저장 프로시저에서는 비결정론적 함수를 사용할 수 있습니다.이제 비결정론적 함수가 무엇인지에 대한 질문이 나옵니다.앤스:-

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

  예외:-

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

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

• 저장 프로시저 내에서 DML(insert, update, delete) 문을 사용할 수 있지만 물리적 테이블 또는 영구 테이블의 함수에서는 DML 문을 사용할 수 없습니다.함수에서 DML 연산을 하려면 영구 테이블이 아닌 테이블 변수를 사용할 수 있습니다.

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

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

2. 프로시저는 입력/출력 파라미터를 가질 수 있지만 함수는 입력 파라미터만 가질 수 있습니다.

3. 프로시저에서는 DML 문뿐만 아니라 Select 문도 사용할 수 있지만 함수는 Select 문만 허용합니다.

4. 프로시저에서 함수를 호출할 수 있는 반면, 프로시저는 함수에서 호출할 수 없습니다.

5. 예외는 절차에서 try-catch 블록으로 처리할 수 있지만 try-catch 블록은 함수에서 사용할 수 없습니다.

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

7. Select 스테이트먼트에는 프로시저를 사용할 수 없지만 Select 스테이트먼트에는 함수를 포함할 수 있습니다.

8. Function)는 UDF(User Defined Function)의할 수 .WHERE/HAVING/SELECT을 사용법

9. 테이블을 반환하는 UDF는 다른 행 세트로 취급할 수 있습니다.은 '아까부터'에서 할 수 .JOIN을 하다

10. 할 수 UDF에서 할 수 .JOIN아, 네, 네, 네.

예를 들어 함수가 있는 경우 SQL 문의 일부로 사용할 수 있습니다.

SELECT function_name(field1) FROM table

저장 프로시저에서는 이 방법으로 동작하지 않습니다.

Table Valued Function과 Stored Procedure 양쪽에서 동일한 비트의 코드(긴 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

이 기술은 여러 함수에 사용되었으며, 때로는 이 유형의 "옵션" 파라미터의 긴 목록과 함께 사용되기도 합니다.

저는 개인적으로 테이블 값 함수를 사용하여 반환하는 것은 영향을 받지 않는 단일 테이블뿐입니다.기본적으로 저는 그것들을 매개 변수화된 뷰처럼 취급합니다.

여러 레코드 세트를 반환해야 하거나 테이블에 값이 업데이트될 경우 저장 프로시저를 사용합니다.

나의 2센트

위에서 설명한 바와 같이 함수는 가독성/구성성/자체 문서화가 가능하지만 일반적으로 성능이 떨어집니다.또, 이러한 함수에 열중해 있으면, 퍼포먼스가 큰폭으로 저하하는 일이 있습니다.

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

대부분의 경우 TVF가 제거할 수 있는 코드의 용장성(용이한 퍼포먼스 비용)을 받아들여야 합니다.

아직 언급하지 않은 다른 한 가지 포인트는 다중 스테이트 TVF 내에서 데이터베이스 상태를 변경하는 임시 테이블을 사용할 수 없다는 것입니다.임시 테이블과 가장 기능적으로 동등한 메커니즘은 메모리 테이블 변수 내의 비상태 변화이며, 대규모 데이터 세트의 경우 임시 테이블이 테이블 변수보다 더 높은 성능을 발휘할 수 있습니다(다른 대안으로는 동적 테이블 및 일반 테이블 값 표현 등이 있지만, 어느 정도 복잡도는 있습니다).이것들은, IMO 의 좋은 옵션이 되지 않게 됩니다).

둘 다 퍼포먼스 테스트를 할 거예요sp 접근법 또는 파생된 표는 함수보다 상당히 빠를 수 있으며, 만약 그렇다면 그 접근법을 사용해야 한다.일반적으로 성능 호그가 될 수 있기 때문에 기능은 피합니다.

상황에 따라 달라집니다.테이블 값 결과를 다른 프로시저로 사용하려면 TableValued 함수를 사용하는 것이 좋습니다.결과가 클라이언트에 대한 것이라면 보통 저장된 proc가 더 나은 방법입니다.

스토어드 프로시저는 SQL 주입에서 더 빨리 실행되고 저장되는 미리 컴파일된 쿼리입니다.0 또는 N 값을 반환할 수 있습니다.저장 프로시저 내에서 DML 작업을 수행할 수 있습니다.절차 내의 함수를 사용할 수 있으며 선택 쿼리의 함수를 사용할 수 있습니다.함수는 모든 값을 반환하는 데 사용되며 함수에서는 불가능한 DML 연산을 반환합니다.함수는 스칼라와 테이블 값의 두 가지 유형입니다.스칼라 함수는 테이블의 행을 반환하는 데 사용되는 단일 값 테이블 값의 함수를 반환합니다.

언급URL : https://stackoverflow.com/questions/178128/functions-vs-stored-procedures