기능 vsSQL Server에 저장 프로시저

기능 vsSQL Server에 저장 프로시저

SQL에 저장 프로시저가 아닌 함수를 사용해야 하는 경우 및 그 반대는 무엇입니까?각각의 목적은 무엇입니까?

을 수행할 수 .SQL Server)INSERT ★★★★★★★★★★★★★★★★★」UPDATE허가됩니다).

할 수 .SQL할 수 경우.

답을 정리한 코멘트에서 주의할 점.@Sean K Anderson 덕분에:

함수는 반드시 값을 반환해야 하며 매개 변수(인수)로 수신하는 데이터를 변경할 수 없다는 점에서 컴퓨터 과학 정의를 따릅니다.함수는 아무것도 변경할 수 없으며 매개 변수가 하나 이상 있어야 하며 값을 반환해야 합니다.저장된 proc에는 매개 변수가 필요하지 않으며 데이터베이스 개체를 변경할 수 있으며 값을 반환할 필요가 없습니다.

다음은 차이점을 정리한 표입니다.

	스토어드 프로시저	기능.
돌아온다	0 이상의 값	단일 값(스칼라 또는 테이블일 수 있음)
트랜잭션을 사용할 수 있습니까?	네.	아니요.
파라미터로 출력할 수 있습니까?	네.	아니요.
서로 전화해도 돼요?	함수를 호출할 수 있습니다.	저장 프로시저를 호출할 수 없습니다.
SELECT, WHERE 및 HAVING 문장에서 사용할 수 있습니까?	아니요.	네.
예외 처리 지원(시행/캐치를 통한)	네.	아니요.

기능 및 저장 프로시저는 별도의 용도로 사용됩니다.함수는 말 그대로 어떤 프로그래밍 언어에서도 사용할 수 있는 다른 함수로 볼 수 있지만 저장된 프로그램은 개별 프로그램이나 배치 스크립트에 가깝습니다.

함수에는 일반적으로 출력과 선택적으로 입력이 있습니다.은 다른 등 SQL 에 대한 Query(, LEN, SQL Server)에 대한 할 수 .SELECT a, b, dbo.MyFunction(c) FROM table ★★★★★★★★★★★★★★★★★」SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

저장된 프로세서는 트랜잭션에서 SQL 쿼리를 함께 바인딩하고 외부와 인터페이스하는 데 사용됩니다.ADO 등의 프레임워크NET 등은 함수를 직접 호출할 수 없지만 저장된 프로세서를 직접 호출할 수 있습니다.

그러나 함수는 오용될 수 있으며 다소 심각한 성능 문제를 일으킬 수 있습니다.다음 질문을 고려해 주십시오.

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

MyFunction은 다음과 같이 선언됩니다.

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

여기서는 MyTable 테이블의 모든 행에 대해 MyFunction 함수가 호출됩니다.MyTable에 1000개의 행이 있는 경우 데이터베이스에 대한 1000개의 임시 쿼리입니다.마찬가지로 열 사양에 지정된 함수가 호출되면 SELECT에 의해 반환되는 각 행에 대해 함수가 호출됩니다.

그래서 글쓰기 기능에 주의를 기울일 필요가 있습니다.함수의 테이블에서 SELECT를 실행하는 경우 부모 스토어드 proc 또는 기타 SQL 구성(CASE 등)에서 JOIN을 사용하여 더 나은 성능을 발휘할 수 있는지 자문해야 합니다.언제...그렇지 않으면... 종료).

저장 프로시저와 사용자 정의 기능의 차이:

• Select 문에 저장 프로시저를 사용할 수 없습니다.
• 저장 프로시저는 지연 이름 확인을 지원합니다.
• 저장 프로시저는 일반적으로 비즈니스 로직을 수행하기 위해 사용됩니다.
• 저장 프로시저는 모든 데이터 유형을 반환할 수 있습니다.
• 저장 프로시저는 사용자 정의 함수보다 더 많은 수의 입력 파라미터를 수용할 수 있습니다.저장 프로시저는 최대 21,000개의 입력 파라미터를 가질 수 있습니다.
• 스토어드 프로시저는 다이내믹 SQL을 실행할 수 있습니다.
• 저장 프로시저는 오류 처리를 지원합니다.
• 비결정론적 함수는 저장 프로시저에서 사용할 수 있습니다.

---

• Select 문에서는 사용자 정의 함수를 사용할 수 있습니다.
• 사용자 정의 함수는 지연 이름 확인을 지원하지 않습니다.
• 사용자 정의 함수는 일반적으로 계산에 사용됩니다.
• 사용자 정의 함수는 값을 반환해야 합니다.
• 사용자 정의 함수는 이미지를 반환할 수 없습니다.
• 사용자 정의 함수는 저장 프로시저보다 적은 수의 입력 파라미터를 받아들입니다.UDF에는 최대 1,023개의 입력 파라미터를 설정할 수 있습니다.
• 임시 테이블은 사용자 정의 함수에서 사용할 수 없습니다.
• 사용자 정의 함수는 동적 SQL을 실행할 수 없습니다.
• 사용자 정의 함수는 오류 처리를 지원하지 않습니다. RAISEERROR ★★★@@ERRORUDF를 사용하다
• UDF를 사용하다를 들어, 「」라고 하는 것은,GETDATE()UDF를 사용하다

스토어 절차	함수(사용자 정의 함수)
프로시저는 0, 단일 또는 다중 값을 반환할 수 있습니다.	함수는 단일 값만 반환할 수 있습니다.
절차에는 입력 및 출력 매개 변수가 포함될 수 있습니다.	함수는 입력 매개 변수만 가질 수 있습니다.
함수에서 프로시저를 호출할 수 없습니다.	프로시저에서 함수를 호출할 수 있습니다.
절차에서는 DML 문과 함께 선택 가능	함수는 선택문만 허용합니다.
예외는 절차에서 트라이캐치 블록으로 처리할 수 있습니다.	함수에 트라이캐치 블록을 사용할 수 없습니다.
절차상 거래관리를 할 수 있다.	기능상 트랜잭션 관리를 할 수 없습니다.
선택 문에서는 프로시저를 사용할 수 없습니다.	함수를 선택 문에 포함할 수 있습니다.
절차는 데이터베이스 상태에 영향을 줄 수 있으며, 이는 데이터베이스에서 CRUD 작업을 수행할 수 있음을 의미합니다.	함수가 데이터베이스 상태에 영향을 줄 수 없음은 데이터베이스에서 CRUD 작업을 수행할 수 없음을 의미합니다.
이 절차에서는 임시 테이블을 사용할 수 있습니다.	함수는 임시 테이블을 사용할 수 없습니다.
절차에 따라 서버 환경 파라미터 변경 가능	함수가 환경 매개 변수를 변경할 수 없습니다.
필요할 때 사용할 수 있는 절차는 복잡한 SQL 문 세트를 그룹화하는 것입니다.	함수는 다른 SQL 문에 사용할 값을 계산하고 반환할 때 사용할 수 있습니다.

다른 SQL 문에서 사용할 값을 계산하고 반환하려면 사용자 정의 함수를 작성하고, 대신 복잡한 SQL 문 집합을 그룹화하는 저장 프로시저를 작성합니다.결국 이 두 가지 사용 사례는 상당히 다릅니다.

기본적인 차이

함수는 값을 반환해야 하지만 저장 프로시저에서는 값을 반환할 수 있습니다(프로시저는 0 또는 n 값을 반환할 수 있습니다).

함수에는 입력 파라미터만 지정할 수 있는 반면 절차에는 입력/출력 파라미터를 지정할 수 있습니다.

함수는 필수 입력 매개 변수를 하나만 사용하지만 저장 프로시저는 o ~ n개의 입력 매개 변수를 사용할 수 있습니다.

Procedure에서 함수를 호출할 수 있는 반면, Procedures는 Function에서 호출할 수 없습니다.

어드밴스 차이

이 절차에서는 SELECT 문뿐만 아니라 DML(INSERT/UPDATE/DELETE) 문도 허용하지만 함수는 SELECT 문만 허용합니다.

SELECT 문에는 프로시저를 사용할 수 없지만 함수는 SELECT 문에 포함될 수 있습니다.

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

테이블을 반환하는 함수는 다른 행 집합으로 취급할 수 있습니다.이것은 다른 테이블과 함께 JOIN에서 사용할 수 있습니다.

인라인 함수는 파라미터를 사용하는 뷰로 간주할 수 있으며 JOIN 및 기타 Rowset 작업에서 사용할 수 있습니다.

예외는 절차에서 트라이캐치 블록으로 처리할 수 있지만, 함수는 트라이캐치 블록을 사용할 수 없습니다.

Procedure에서는 Transaction Management를 할 수 있지만 Function에서는 할 수 없습니다.

원천

사용자 정의 함수는 SQL 서버 프로그래머가 사용할 수 있는 중요한 도구입니다.다음과 같은 SQL 문에서 인라인으로 사용할 수 있습니다.

SELECT a, lookupValue(b), c FROM customers 

서 ''는lookupValueUDF를 사용하다스토어드 프로시저를 사용하는 경우는, 이러한 종류의 기능은 사용할 수 없습니다.동시에 UDF 내에서 특정 작업을 수행할 수 없습니다.해야 할 과 같습니다.

• 영구 변경을 생성할 수 없습니다.
• 데이터를 변경할 수 없습니다.

저장 프로시저가 그런 일을 할 수 있습니다.

UDF의 인라인 사용은 UDF의 가장 중요한 사용법입니다.

저장 프로시저는 스크립트로 사용됩니다.이러한 명령어는 일련의 명령을 실행하며 특정 시간에 실행되도록 예약할 수 있습니다.보통 INSERT, UPDATE, DELETE 등의 여러 DML 문을 실행하거나 SELECT를 실행하기도 합니다.

함수는 메서드로 사용됩니다.무언가를 전달하면 결과가 반환됩니다.작고 빨라야 합니다. 즉석에서 할 수 있습니다.보통 SELECT 문에서 사용됩니다.

SQL Server 기능은 커서와 마찬가지로 마지막 무기로 사용됩니다.이러한 기능에는 성능 문제가 있기 때문에 테이블 값 함수의 사용은 가능한 한 피해야 합니다.퍼포먼스에 대해서는, 미들 클래스의 하드웨어상의 서버상에서 호스트 되고 있는 1,000,000 이상의 레코드가 있는 테이블을 말합니다.그렇지 않으면 이 기능에 의한 퍼포먼스 저하를 걱정할 필요가 없습니다.

1. 함수를 사용하여 결과 세트를 외부 코드(ADO 등)로 반환하지 마십시오.네트워크)
2. 가능한 한 보기/저장된 프로의 조합을 사용하십시오.DTA(Database Tuning Advisor)가 제공하는 제안(인덱스 뷰 및 통계 등)을 사용하여 미래의 성장 성능 문제에서 복구할 수 있습니다.

자세한 것은, http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html 를 참조해 주세요.

저장 프로시저:

• SQL Server의 미니어처 프로그램입니다.
• select 스테이트먼트만큼 단순할 수도 있고 데이터베이스의 여러 테이블에서 데이터를 추가, 삭제, 업데이트 및/또는 읽는 긴 스크립트만큼 복잡할 수도 있습니다.
• (루프와 커서를 구현할 수 있습니다.이러한 루프를 사용하면, 보다 작은 결과나 데이터상의 행 단위 조작을 실시할 수 있습니다).
• 하다를 사용해서 해요.EXEC ★★★★★★★★★★★★★★★★★」EXECUTE★★★★★★ 。
• 변수를 할 수 OUT파라미터를 지정합니다.
• 트랜잭션을 지원합니다.

기능:

• 레코드를 업데이트, 삭제 또는 데이터베이스에 추가하는 데 사용할 수 없습니다.
• 단일 값 또는 테이블 값을 반환합니다.

• 레코드를 선택하는 데만 사용할 수 있습니다.그러나 다음과 같은 표준 SQL 내에서 매우 쉽게 호출할 수 있습니다.

  SELECT dbo.functionname('Parameter1')
  

  또는

  SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
  

• 간단히 재사용 가능한 선택 작업의 경우 함수는 코드를 단순화할 수 있습니다..JOIN절이 포함되어 있습니다.에 음음음음 가 있는 JOIN하여 여러 하는 다른 호출은 """선택""""선택""""""""""""""""""""을 선택합니다""""""""""""""""""""""""""""""""""""""""""""""""""JOIN결과 세트에 반환되는 각 행에 대해 이들 테이블을 함께 표시합니다.따라서 일부 논리를 단순화하는 데 도움이 될 수 있지만 올바르게 사용하지 않으면 성능 병목 현상이 발생할 수도 있습니다.

• 사용하여 값을 합니다.OUT파라미터를 지정합니다.
• 트랜잭션을 지원하지 않습니다.

다음 사항에 도움이 될 수 있는 것을 언제 사용할지 결정하려면 -

1. 저장 프로시저는 as 함수가 이를 수행할 수 있는 테이블 변수를 반환할 수 없습니다.

2. 저장 프로시저를 사용하여 서버 환경 매개변수를 변경할 수 있지만 함수를 사용할 때는 변경할 수 없습니다.

건배.

단일 값을 반환하는 함수부터 시작합니다.좋은 점은 자주 사용하는 코드를 함수에 넣고 결과 집합의 열로 반환할 수 있다는 것입니다.

그런 다음 매개 변수화된 도시 목록에 대한 함수를 사용할 수 있습니다. dbo.GetCitiesIn("NY") Join으로 사용할 수 있는 테이블을 반환합니다.

코드를 정리하는 방법입니다.어떤 것이 언제 재사용 가능하고 언제 시간 낭비인지 아는 것은 시행착오와 경험을 통해서만 얻을 수 있는 것입니다.

또한 SQL Server에서는 기능을 사용하는 것이 좋습니다.그것들은 더 빠르고 꽤 강력할 수 있다.인라인 및 직접 선택과용하지 않도록 주의하세요.

다음은 저장 프로시저보다 기능을 선호하는 실용적인 이유입니다.다른 저장 프로시저의 결과가 필요한 저장 프로시저가 있는 경우 insert-exec 문을 사용해야 합니다., 하여 ", ", "를 .exec저장 프로시저의 결과를 임시 테이블에 삽입합니다.지저분해요.이 문제의 1가지 문제는 삽입 실행은 네스트할 수 없다는 것입니다.

다른 스토어드 프로시저를 호출하는 스토어드 프로시저에 얽매여 있는 경우는, 이 문제에 직면할 가능성이 있습니다.중첩된 저장 프로시저가 단순히 데이터 집합을 반환하는 경우 테이블 값 함수로 대체할 수 있으므로 더 이상 이 오류가 발생하지 않습니다.

(이것은, 비즈니스 로직을 데이타베이스로부터 제외하는 또 하나의 이유이기도 합니다).

이것이 매우 오래된 질문인 것은 알지만, 어느 답변에도 중요한 한 가지 측면이 언급되지 않았습니다. 바로 질의 계획에 포함시키는 것입니다.

기능은 다음과 같습니다.

1. 스칼라:

   CREATE FUNCTION ... RETURNS scalar_type AS BEGIN ... END

2. 다중 스테이트먼트 테이블 값:

   CREATE FUNCTION ... RETURNS @r TABLE(...) AS BEGIN ... END

3. 인라인 테이블 값:

   CREATE FUNCTION ... RETURNS TABLE AS RETURN SELECT ...

세 번째 종류(인라인 테이블 값)는 기본적으로 쿼리 옵티마이저에 의해 (파라미터화된) 뷰로 처리됩니다.즉, 쿼리에서 함수를 참조하는 것은 함수의 SQL 본문을 복사 붙여넣는 것과 비슷하므로(실제로 복사 붙여넣지 않고) 다음과 같은 이점이 있습니다.

• 쿼리 플래너는 다른 하위 쿼리와 마찬가지로 인라인 함수 실행을 최적화할 수 있습니다(예: 사용되지 않는 열 제거, 술어 밀어넣기, 다른 JOIN 전략 선택).
• 여러 개의 인라인 함수를 조합하면 첫 번째 함수를 사용한 결과를 다음 함수를 제공하기 전에 구체화할 필요가 없습니다.

위의 내용은 특히 여러 수준의 기능을 조합할 경우 상당한 성능 절감으로 이어질 수 있습니다.

---

메모: SQL Server 2019는 어떤 형태의 스칼라 함수 인라인도 도입할 예정입니다.

• 저장 프로시저가 아닌 경우 함수에서 값을 반환해야 합니다.
• DML 문은 필수가 아니지만 UDF에서만 허용되는 문을 선택합니다.
• 스토어드 프로시저에서는 DML 문뿐만 아니라 모든 문을 사용할 수 있습니다.
• UDF는 입력만 허용하고 출력은 허용하지 않습니다.
• 저장 프로시저는 입력과 출력을 모두 허용합니다.
• 캐치 블록은 UDF에서는 사용할 수 없지만 스토어드 프로시저에서는 사용할 수 있습니다.
• UDF 함수에서는 트랜잭션이 허용되지 않지만 스토어드 프로시저에서는 트랜잭션이 허용됩니다.
• UDF에서는 테이블 변수만 사용할 수 있으며 임시 테이블은 사용할 수 없습니다.
• 저장 프로시저에서는 테이블 변수와 임시 테이블을 모두 사용할 수 있습니다.
• UDF에서는 함수에서 스토어드 프로시저를 호출할 수 없지만 스토어드 프로시저는 함수 호출을 허용합니다.
• UDF는 join 절에서 사용되고 스토어드 프로시저는 join 절에서 사용할 수 없습니다.
• 저장 프로시저는 항상 0으로 되돌릴 수 있습니다.반대로 UDF에는 미리 정해진 포인트로 돌아와야 하는 값이 있습니다.

• 함수는 select 문에서 사용할 수 있지만 절차는 사용할 수 없습니다.

• 저장 프로시저는 입력 및 출력 파라미터를 모두 사용하지만 함수는 입력 파라미터만 사용합니다.

• 함수는 text, ntext, image 및 timestamp 유형의 값을 반환할 수 없습니다(순서대로).

• 함수는 작성 테이블에서 사용자 정의 데이터 유형으로 사용할 수 있지만 프로시저는 사용할 수 없습니다.

***예:-작성table <tablename>(name varchar(10),salary getsal(name))

여기서 getsal은 급여 유형을 반환하는 사용자 정의 함수입니다. 테이블이 생성될 때 급여 유형에 대해 스토리지가 할당되지 않으며 getsal 함수도 실행되지 않습니다. 그러나 이 테이블에서 일부 값을 가져오면 getsal 함수가 실행되고 반환 유형이 결과 세트로 반환됩니다.

일반적으로 성능에는 저장 프로시저를 사용하는 것이 좋습니다.예를 들어 이전 버전의 SQL Server에서 함수를 JOIN 상태로 설정한 경우 카디널리티 추정치는 1(SQL 2012 이전)과 100(SQL 2012 이후 및 SQL 2017 이전)이며 엔진이 잘못된 실행 계획을 생성할 수 있습니다.

또한 WHERE 절에 넣으면 SQL Engine이 잘못된 실행 계획을 생성할 수 있습니다.

SQL 2017에서 Microsoft는 보다 정확한 견적을 내기 위해 인터리브 실행이라는 기능을 도입했지만 스토어드 프로시저는 여전히 최고의 솔루션입니다.

상세한 것에 대하여는, Joe Sack 의 다음의 기사를 참조해 주세요.https://techcommunity.microsoft.com/t5/sql-server/introducing-interleaved-execution-for-multi-statement-table/ba-p/385417

SQL Server에서 함수와 저장 프로시저는 두 가지 유형의 엔티티입니다.

기능:SQL Server 데이터베이스에서 함수는 일부 작업을 수행하는 데 사용되며 작업은 결과를 즉시 반환합니다.함수는 다음 두 가지 유형입니다.

1. 시스템 정의

2. 사용자 정의

저장 프로시저:SQL Server에서 저장 프로시저는 서버에 저장되며 0, 단일 및 다중 값을 반환할 수 있습니다.저장 프로시저는 다음 두 가지 유형입니다.

1. 시스템 스토어드 프로시저
2. 사용자 정의 절차

언급URL : https://stackoverflow.com/questions/1179758/function-vs-stored-procedure-in-sql-server