sql-server 프로시저 - SQL Server에서 함수 대 저장 프로 시저




명명 규칙 (15)

SQL Server 기능은 커서와 마찬가지로 마지막 무기로 사용됩니다. 그들은 성능 문제가 있으므로 테이블 값 함수를 사용하는 것은 가능한 한 피해야합니다. 성능에 관해 이야기하는 것은 중산층 하드웨어의 서버에서 호스팅되는 1,000,000 개가 넘는 레코드가있는 테이블을 말하는 것입니다. 그렇지 않으면 함수로 인해 발생하는 성능에 대해 걱정할 필요가 없습니다.

  1. 함수를 사용하여 결과 세트를 외부 코드 (예 : ADO.Net)로 리턴하지 마십시오.
  2. 가능한 한보기 / 저장된 procs 조합을 사용하십시오. DTA (Database Tuning Adviser)가 제공하는 제안 (인덱싱 된 뷰 및 통계와 같은)을 사용하여 향후 성장 성능 문제를 복구 할 수 있습니다 - 때로는!

자세한 내용은 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 절이 있고 여러 결과를 리턴하는 다른 select 문에서 호출하는 경우 해당 함수 호출은 결과 세트에 리턴 된 행에 대해 해당 테이블을 함 2 JOIN 합니다. 따라서 일부 논리를 단순화하는 데 도움이 될 수 있지만 제대로 사용하지 않으면 성능 병목 현상이 될 수 있습니다.

  • OUT 매개 변수를 사용하여 값을 리턴합니다.
  • 거래를 지원하지 않습니다.

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

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

그것은 코드를 구성하는 방법입니다. 무언가를 재사용 할 수 있고 언제 시간 낭비인지 아는 것은 시행 착오와 경험을 통해서만 얻을 수 있습니다.

또한 함수는 SQL Server에서 좋은 아이디어입니다. 그들은 더 빠르며 아주 강력 할 수 있습니다. 인라인 및 직접 선택. 과용하지 않도록 조심하십시오.


다른 SQL 문에서 사용할 값을 계산하고 반환하려는 경우 사용자 정의 함수를 작성하십시오. 복잡한 SQL 문 집합을 그룹화하는 것이 필요할 때 저장 프로 시저를 작성하십시오. 결국 두 개의 꽤 다른 사용 사례입니다.


저장 프로 시저보다 기능을 선호하는 실질적인 이유가 있습니다. 다른 저장 프로 시저의 결과가 필요한 저장 프로 시저가있는 경우 insert-exec 문을 사용해야합니다. 즉, 임시 테이블을 만들고 exec 문을 사용하여 저장 프로 시저의 결과를 임시 테이블에 삽입해야합니다. 지저분 해. 이 문제의 한 가지 문제점은 insert-execs를 중첩 할 수 없다는 것 입니다.

다른 저장 프로 시저를 호출하는 저장 프로 시저가 붙어 있다면이 프로 시저를 실행할 수 있습니다. 중첩 저장 프로 시저가 단순히 데이터 집합을 반환하면 테이블 반환 함수로 바꿀 수 있으므로 더 이상이 오류가 발생하지 않습니다.

( 이것은 데이터베이스에서 비즈니스 로직을 유지해야하는 또 다른 이유입니다 )


다음 요점이 도움이 될만한 것을 사용할시기를 결정하려면 -

  1. 저장 프로시 저는 함수로 할 수있는 테이블 변수를 리턴 할 수 없습니다.

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

건배


기본 차이점

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

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

함수는 하나의 입력 매개 변수를 취하지 만 필수적이지만 저장 프로 시저는 n 개의 입력 매개 변수를 취할 수 있습니다.

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

사전 차이

프로시 저는 SELECT와 DML (INSERT / UPDATE / DELETE) 문을 허용하지만 Function은 SELECT 문만 허용합니다.

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

저장 프로시 듀어는 WHERE / HAVING / SELECT 절의 SQL 문에서 사용할 수 없지만 Function은 사용할 수 있습니다.

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

인라인 함수는 매개 변수를 취하는 뷰로 사용할 수 있으며 JOIN 및 다른 행 집합 작업에서 사용할 수 있습니다.

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

프로 시저에서는 트랜잭션 관리를 사용할 수 있지만 함수에서는 사용할 수 없습니다.

source


함수는 계산 된 값이므로 SQL Server에 영구적 인 환경 변경을 수행 할 수 없습니다 (즉, INSERT 또는 UPDATE 문이 허용되지 않음).

함수는 스. 라 값을 리턴하는 경우 SQL.에서 인라인으로 사용되거나 결과 세트를 리턴하면 결합 될 수 있습니다.

답변을 요약 한 의견에서 주목할만한 포인트. @Sean K Anderson에게 감사드립니다.

함수는 값을 반환해야하며 매개 변수 (인수)로받는 데이터를 변경할 수 없다는 점에서 컴퓨터 효율성 정의를 따릅니다. 함수는 아무것도 변경할 수 없으며 하나 이상의 매개 변수가 있어야하며 값을 반환해야합니다. 저장된 procs는 매개 변수를 가질 필요가 없으며 데이터베이스 객체를 변경할 수 있으며 값을 반환 할 필요가 없습니다.


              STORE PROCEDURE                 FUNCTION (USER DEFINED FUNCTION)    
 * Procedure can return 0, single or   | * Function can return only single value   
   multiple values.                    |
                                       |
 * Procedure can have input, output    | * Function  can have only input 
   parameters.                         |   parameters.         
                                       |
 * Procedure cannot be called from     | * Functions can be called from 
   function.                           |   procedure.
                                       |
 * Procedure allows select as well as  | * Function allows only select statement 
   DML statement in it.                |   in it.
                                       |
 * Exception can be handled by         | * Try-catch block cannot be used in a 
   try-catch block in a procedure.     |   function.
                                       |
 * We can go for transaction management| * We can't go for transaction 
   in procedure.                       |   management in function.
                                       |
 * Procedure cannot be utilized in a   | * Function can be embedded in a select 
   select statement                    |   statement.
                                       |
 * Procedure can affect the state      | * Function can not affect the state 
   of database means it can perform    |   of database means it can not    
   CRUD operation on database.         |   perform CRUD operation on 
                                       |   database. 
                                       |
 * Procedure can use temporary tables. | * Function can not use 
   temporary tables                    |   temporary tables. 
                                       |
 * Procedure can alter the server      | * Function can not alter the  
   environment parameters.             |   environment parameters.
                                       |   
 * Procedure can use when we want      | * Function can use when we want
   instead is to group a possibly-     |   to compute and return a value
   complex set of SQL statements.      |   for use in other SQL 
                                           statements.

  • 함수가 저장 프로 시저가 아닌 동안 값을 반환해야하는 것은 필수입니다.
  • Select 문은 UDF에서만 허용되며 DML 문은 필요하지 않습니다.
  • 저장 프로시 저는 DML 문은 물론 모든 문을 허용합니다.
  • UDF는 입력 만 허용하고 출력은 허용하지 않습니다.
  • 저장 프로시 저는 입력과 출력을 모두 허용합니다.
  • 캐치 블록은 UDF에서 사용할 수 없지만 저장 프로시 듀어에서 사용할 수 있습니다.
  • UDF의 기능에서는 허용되지 않지만 스토어드 프로 시저에서는 허용되는 트랜잭션이 허용되지 않습니다.
  • UDF에서는 임시 변수가 아닌 임시 변수 만 사용할 수 있습니다.
  • 저장 프로시 저는 테이블 변수와 임시 테이블을 모두 허용합니다.
  • UDF는 저장 프로시 듀어가 함수를 호출 할 수있는 동안 저장 프로시 듀어를 함수에서 호출하는 것을 허용하지 않습니다.
  • UDF는 조인 절에서 사용되지만 저장 프로 시저는 조인 절에서 사용될 수 없습니다.
  • 저장 프로시 저는 항상 0으로 되돌릴 수 있습니다. 반대로, UDF는 미리 정해진 지점으로 되돌아 와야하는 값을 가지고 있습니다.

사용자 정의 함수 (User Defined Function)는 SQL Server 프로그래머가 사용할 수있는 중요한 도구입니다. 당신은 SQL 문에서 인라인으로 사용할 수 있습니다.

SELECT a, lookupValue(b), c FROM customers 

여기서 lookupValue 는 UDF가됩니다. 이러한 종류의 기능은 저장 프로 시저를 사용할 때 불가능합니다. 동시에 UDF에서 특정 작업을 수행 할 수 없습니다. 여기에서 기억해야 할 기본적 사항은 UDF의 것입니다.

  • 영구 변경을 만들 수 없습니다.
  • 데이터를 바꿀 수 없다.

스토어드 프로 시저는 그러한 일을 할 수 있습니다.

나에게 UDF의 인라인 사용은 UDF의 가장 중요한 사용법이다.


SP와 UDF의 차이점은 다음과 같습니다.

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

함수와 저장 프로 시저는 별도의 용도로 사용됩니다. 가장 좋은 비유는 아니지만 함수는 문자 그대로 프로그래밍 언어에서 사용하는 다른 함수로 볼 수 있지만 저장된 procs는 개별 프로그램이나 배치 스크립트와 비슷합니다.

함수는 일반적으로 출력과 선택적으로 입력을가집니다. 그런 다음 출력을 다른 함수 (DATEDIFF, LEN 등과 같은 SQL Server 기본 제공) 또는 SQL 쿼리에 대한 술어 (예 : SELECT a, b, dbo.MyFunction(c) FROM table 또는 SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c) .

저장된 procs는 트랜잭션에서 SQL 쿼리를 함께 바인딩하고 외부 세계와 상호 작용하는 데 사용됩니다. ADO.NET과 같은 프레임 워크는 함수를 직접 호출 할 수 없지만 저장된 proc을 직접 호출 할 수 있습니다.

함수는 숨겨진 위험이 있습니다. 오용되거나 오히려 불쾌한 성능 문제가 발생할 수 있습니다. 다음 쿼리를 고려하십시오.

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 ... WHEN ... ELSE ...)에서 JOIN을 사용하여 더 잘 수행 할 수 있는지 스스로에게 질문해야합니다. 종료).


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

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

  1. 정의 된 시스템

  2. 사용자 정의

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

  1. 시스템 저장 프로 시저
  2. 사용자 정의 프로 시저

SQL Server 2005 이상 :

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith의 버전은 더 짧습니다.

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END




sql sql-server tsql stored-procedures sql-function