Muokkaa

Jaa

User-defined functions

  • Artikkeli

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

Benefits of user-defined functions

Why use user-defined functions (UDFs)?

  • Modular programming. You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.

  • Faster execution. Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function doesn't need to be reparsed and reoptimized with each use, resulting in much faster execution times.

    Common language runtime (CLR) functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.

  • Reduce network traffic. An operation that filters data based on some complex constraint that can't be expressed in a single scalar expression can be expressed as a function. The function can then be invoked in the WHERE clause to reduce the number of rows sent to the client.

Important

Transact-SQL UDFs in queries can only be executed on a single thread (serial execution plan). Therefore using UDFs inhibits parallel query processing. For more information about parallel query processing, see the Query Processing Architecture Guide.

Types of functions

This section describes the differences between scalar functions, table-valued functions, and system functions.

Scalar functions

User-defined scalar functions return a single data value of the type defined in the RETURNS clause. For an inline scalar function, the returned scalar value is the result of a single statement. For a multistatement scalar function, the function body can contain a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp. For examples, see Create user-defined functions (database engine).

Table-valued functions

User-defined table-valued functions (TVFs) return a table data type. For an inline table-valued function, there's no function body; the table is the result set of a single SELECT statement. For examples, see Create user-defined functions (database engine).

System functions

SQL Server provides many system functions that you can use to perform various operations. They can't be modified. For more information, see What are the SQL database functions?, System Functions by category for Transact-SQL, and System dynamic management views.

Guidelines

Transact-SQL errors that cause a statement to be canceled and continue with the next statement in the module (such as triggers or stored procedures) are treated differently inside a function. In functions, such errors cause the execution of the function to stop. This in turn causes the statement that invoked the function to be canceled.

The statements in a BEGIN...END block can't have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that statements in the function can make, are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that aren't local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that can't be performed in a function.

If a CREATE FUNCTION statement produces side effects against resources that don't exist when the CREATE FUNCTION statement is issued, SQL Server executes the statement. However, SQL Server doesn't execute the function when it's invoked.

The number of times that a function specified in a query is executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer.

Deterministic functions must be schema-bound. Use the SCHEMABINDING clause when creating a deterministic function.

For more information and performance considerations on user-defined functions, see Create user-defined functions (Database Engine).

Valid statements in a function

The types of statements that are valid in a function include:

  • DECLARE statements can be used to define data variables and cursors that are local to the function.

  • Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

  • Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client aren't allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.

  • Control-of-flow statements except TRY...CATCH statements.

  • SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

  • UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

  • EXECUTE statements calling an extended stored procedure.

Built-in system functions

The following nondeterministic built-in functions can be used in Transact-SQL user-defined functions.

  • CURRENT_TIMESTAMP
  • GET_TRANSMISSION_STATUS
  • GETDATE
  • GETUTCDATE
  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@PACKET_ERRORS
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE

The following nondeterministic built-in functions cannot be used in Transact-SQL user-defined functions.

  • NEWID
  • NEWSEQUENTIALID
  • RAND
  • TEXTPTR

For a list of deterministic and nondeterministic built-in system functions, see Deterministic and nondeterministic functions.

Schema-bound functions

CREATE FUNCTION supports a SCHEMABINDING clause that binds the function to the schema of any objects it references, such as tables, views, and other user-defined functions. An attempt to alter or drop any object referenced by a schema-bound function fails.

These conditions must be met before you can specify SCHEMABINDING in CREATE FUNCTION:

  • All views and user-defined functions referenced by the function must be schema-bound.

  • All objects referenced by the function must be in the same database as the function. The objects must be referenced using either one-part or two-part names.

  • You must have REFERENCES permission on all objects (tables, views, and user-defined functions) referenced in the function.

You can use ALTER FUNCTION to remove the schema binding. The ALTER FUNCTION statement should redefine the function without specifying WITH SCHEMABINDING.

Specify parameters

A user-defined function takes zero or more input parameters and returns either a scalar value or a table. A function can have a maximum of 1,024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in user-defined stored procedures in which omitting the parameter also implies the default value. User-defined functions don't support output parameters.

Related content