Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Implement Functions and Aggregate Data

  1. Home
  3. Exam 70-761: Querying Data with Transact-SQL
  5. Implement Functions and Aggregate Data

Create User-defined Functions (Database Engine)

The describes how to create a user-defined function (UDF) in SQL Server by using Transact-SQL.

Limitations and restrictions

  • User-defined functions cannot be used to perform actions that modify the database state.
  • User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.
  • User-defined functions can not return multiple result sets. Use a stored procedure if you need to return multiple result sets.
  • Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.
  • User-defined functions cannot call a stored procedure, but can call an extended stored procedure.
  • User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed.
  • SET statements are not allowed in a user-defined function.
  • The FOR XML clause is not allowed.

User-defined functions can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail. Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit.

The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:

  • BEGIN DIALOG CONVERSATION
  • END CONVERSATION
  • GET CONVERSATION GROUP
  • MOVE CONVERSATION
  • RECEIVE
  • SEND

Permissions
Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. If the function specifies a user-defined type, requires EXECUTE permission on the type.

Avoid SQL Server functions in the WHERE clause for Performance

Problem Case

SQL Server offers many handy functions that can be used either in your SELECT clause or in your WHERE clause. For the most part these functions provide complex coding that would be very difficult to get this same functionality without these functions. In addition to the built in functions you also have the ability to develop your own user defined functions. When functions are used in the SELECT clause to return uppercase output, a substring or whatever, it doesn’t affect performance that much, but when functions are used improperly in the WHERE clause these functions can cause major performance issues.

Solution Case

When functions are used in the SELECT clause, the function has to be run with each data value to return the proper results. This may not be a bad thing if you are only returning a handful of rows of data. But when these same functions are used in the WHERE clause this forces SQL Server to do a table scan or index scan to get the correct results instead of doing an index seek if there is an index that can be used. The reason for this is is that the function value has to be evaluated for each row of data to determine it matches your criteria.

Here are some simple statements that show you the affect of using a function in the WHERE clause. To get a better understanding of how these queries are working we are also getting the query plan. This can be done by hitting Ctrl-M in a query window to turn this function on before running the query.

Illustration 1: This first example uses the LEFT function to get the first two characters of the email address. Once this is done each row is evaluated to see if it matches the “As” criteria. The EmailAddress is indexed, so SQL Server should be able to use an index seek to find the data.

SELECT EmailAddress
FROM person.contact
WHERE left(EmailAddress,2) = ‘As’

From the query plan output we can see that this query does an index scan, which means it reviews all rows before returning the results. This is because of the LEFT function that is being used.

Another version of this same query which will return the same results uses the LIKE clause instead. This query uses the like clause to get all data that begins with “As”. Since there is an index on the the EmailAddress column SQL Server can do an index seek which is much more efficient then an index scan.

SELECT EmailAddress
FROM person.contact
WHERE EmailAddress like ‘As%’

Illustration 2: Here is another example where the UPPER clause is used to transform the EmailAddress into upper case before evaluating the data. Again the EmailAddress is indexed.

SELECT EmailAddress
FROM person.contact
WHERE upper(EmailAddress) like ‘AS%’

We can see that the query plan for this also does an index scan versus an index seek.

A second version of this query again just uses the LIKE clause to get the same results.

SELECT EmailAddress
FROM person.contact
WHERE EmailAddress like ‘AS%’

Again this query does an index seek versus a index scan.

Illustration 3: Here is another example where the function DateDiff is used. The function is getting rows where the difference in minutes between the ModifiedDate and the getdate() function is greater then zero. In addition, column ModifiedDate is indexed.

SELECT ModifiedDate
FROM person.contact
WHERE datediff(minute,ModifiedDate,getdate())>0

This first query is using the function and therefore an index scan has to occur.

In this version we are just doing a straight comparison of ModifiedDate compared to getdate().

SELECT ModifiedDate
FROM person.contact
WHERE ModifiedDate < getdate()

Since we are not using a function this query is using an index seek.

Precautions

  • Look for poor performing statements in your databases where scans are occurring to see if functions are being used in the WHERE clause
  • Look for alternative methods for getting the same query results, such as some of the examples have shown

OUTPUT Clause (Transact-SQL)

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Note

An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

Used in:

  • DELETE
  • INSERT
  • UPDATE
  • MERGE
  • SYNTAX

Image

Arguments

@table_variable

Specifies a table variable that the returned rows are inserted into instead of being returned to the caller. @table_variable must be declared before the INSERT, UPDATE, DELETE, or MERGE statement.

If column_list is not specified, the table variable must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns, which must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

For more information about table variables, see table (Transact-SQL).

output_table
Specifies a table that the returned rows are inserted into instead of being returned to the caller. output_table may be a temporary table.

If column_list is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If column_list is specified, any omitted columns must either allow null values or have default values assigned to them.

output_table cannot:

  • Have enabled triggers defined on it.
  • Participate on either side of a FOREIGN KEY constraint.
  • Have CHECK constraints or enabled rules.

column_list
Is an optional list of column names on the target table of the INTO clause. It is analogous to the column list allowed in the INSERT statement.

scalar_expression
Is any combination of symbols and operators that evaluates to a single value. Aggregate functions are not permitted in scalar_expression.

Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.

column_alias_identifier
Is an alternative name used to reference the column name.

DELETED
Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed.

DELETED cannot be used with the OUTPUT clause in the INSERT statement.

INSERTED
Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

INSERTED cannot be used with the OUTPUT clause in the DELETE statement.

from_table_name
Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

*
Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table.

For example, OUTPUT DELETED.* in the following DELETE statement returns all columns deleted from the ShoppingCartItem table:

DELETE Sales.ShoppingCartItem
OUTPUT DELETED.*;

column_name
Is an explicit column reference. Any reference to the table being modified must be correctly qualified by either the INSERTED or the DELETED prefix as appropriate, for example: INSERTED.column_name.

$action
Is available only for the MERGE statement. Specifies a column of type nvarchar(10) in the OUTPUT clause in a MERGE statement that returns one of three values for each row: ‘INSERT’, ‘UPDATE’, or ‘DELETE’, according to the action that was performed on that row.

Remarks
The OUTPUT clause and the OUTPUT INTO { @table_variable | output_table } clause can be defined in a single INSERT, UPDATE, DELETE, or MERGE statement.

Note: Unless specified otherwise, references to the OUTPUT clause refer to both the OUTPUT clause and the OUTPUT INTO clause.

The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.

When a computed column is included in the , the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

The OUTPUT clause is not supported in the following statements:

  • DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
  • INSERT statements that contain an EXECUTE statement.
  • Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
  • The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
  • A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

  • Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.
  • A column from a view or inline table-valued function when that column is defined by one of the following methods:
  1. A subquery.
  2. A user-defined function that performs user or system data access, or is assumed to perform such access.
  3. A computed column that contains a user-defined function that performs user or system data access in its definition.

When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised.

Inserting Data Returned From an OUTPUT Clause Into a Table

When you are capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement and inserting those results into a target table, keep the following information in mind:

The whole operation is atomic. Either both the INSERT statement and the nested DML statement that contains the OUTPUT clause execute, or the whole statement fails.

The following restrictions apply to the target of the outer INSERT statement:

  • The target cannot be a remote table, view, or common table expression.
  • The target cannot have a FOREIGN KEY constraint, or be referenced by FOREIGN KEY constraint.
  • Triggers cannot be defined on the target.

The target cannot participate in merge replication or updatable subscriptions for transactional replication.

The following restrictions apply to the nested DML statement:

  • The target cannot be a remote table or partitioned view.
  • The source itself cannot contain a clause.

The OUTPUT INTO clause is not supported in INSERT statements that contain a clause.

@@ROWCOUNT returns the rows inserted only by the outer INSERT statement.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.

Query notifications treat the statement as a single entity, and the type of any message that is created will be the type of the nested DML, even if the significant change is from the outer INSERT statement itself.

In the clause, the SELECT and WHERE clauses cannot include subqueries, aggregate functions, ranking functions, full-text predicates, user-defined functions that perform data access, or the TEXTPTR function.

Parallelism

An OUTPUT clause that returns results to the client will always use a serial plan.

In the context of a database set to compatibility level 130 or higher, if an INSERT…SELECT operation uses a WITH (TABLOCK) hint for the SELECT statement and also uses OUTPUT…INTO to insert into a temporary or user table, then the target table for the INSERT…SELECT will be eligible for parallelism depending on the subtree cost. The target table referenced in the OUTPUT INTO clause will not be eligible for parallelism.

Triggers

Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.

For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation. If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT.

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

If the sp_configure option disallow results from triggers is set, an OUTPUT clause without an INTO clause causes the statement to fail when it is invoked from within a trigger.

Data Types

The OUTPUT clause supports the large object data types: nvarchar(max), varchar(max), varbinary(max), text, ntext, image, and xml. When you use the .WRITE clause in the UPDATE statement to modify an nvarchar(max), varchar(max), or varbinary(max) column, the full before and after images of the values are returned if they are referenced. The TEXTPTR( ) function cannot appear as part of an expression on a text, ntext, or image column in the OUTPUT clause.

Queues

You can use OUTPUT in applications that use tables as queues, or to hold intermediate result sets. That is, the application is constantly adding or removing rows from the table. The following example uses the OUTPUT clause in a DELETE statement to return the deleted row to the calling application.


USE AdventureWorks2012;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

This example removes a row from a table used as a queue and returns the deleted values to the processing application in a single action. Other semantics may also be implemented, such as using a table to implement a stack. However, SQL Server does not guarantee the order in which rows are processed and returned by DML statements using the OUTPUT clause. It is up to the application to include an appropriate WHERE clause that can guarantee the desired semantics, or understand that when multiple rows may qualify for the DML operation, there is no guaranteed order. The following example uses a subquery and assumes uniqueness is a characteristic of the DatabaseLogID column in order to implement the desired ordering semantics.

USE tempdb;  
GO  
CREATE TABLE dbo.table1  
(  
    id INT,  
    employee VARCHAR(32)  
);  
GO  
  
INSERT INTO dbo.table1 VALUES   
      (1, 'Fred')  
     ,(2, 'Tom')  
     ,(3, 'Sally')  
     ,(4, 'Alice');  
GO  
  
DECLARE @MyTableVar TABLE  
(  
    id INT,  
    employee VARCHAR(32)  
);  
  
PRINT 'table1, before delete'   
SELECT * FROM dbo.table1;  
  
DELETE FROM dbo.table1  
OUTPUT DELETED.* INTO @MyTableVar  
WHERE id = 4 OR id = 2;  
  
PRINT 'table1, after delete'  
SELECT * FROM dbo.table1;  
  
PRINT '@MyTableVar, after delete'  
SELECT * FROM @MyTableVar;  
  
DROP TABLE dbo.table1;  
  
--Results  
--table1, before delete  
--id          employee  
------------- ------------------------------  
--1           Fred  
--2           Tom  
--3           Sally  
--4           Alice  
--  
--table1, after delete  
--id          employee  
------------- ------------------------------  
--1           Fred  
--3           Sally  
--@MyTableVar, after delete  
--id          employee  
------------- ------------------------------  
--2           Tom  
--4           Alice

 Note: Use the READPAST table hint in UPDATE and DELETE statements if your scenario allows for multiple applications to perform a destructive read from one table. This prevents locking issues that can come up if another application is already reading the first qualifying record in the table.

Permissions

SELECT permissions are required on any columns retrieved through <dml_select_list> or used in <scalar_expression>.

INSERT permissions are required on any tables specified in <output_table>.

Menu