Pages

▼

Friday, 31 March 2017

UDF

User-Defined Functions:

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.

With SQL Server 2000, Microsoft has introduced the concept of User-Defined Functions.

 

Where Can I Use A Function?

Anywhere!

Well, we can use a function almost anywhere that we would use a table or column. We can use a function anywhere that we can use a scalar value or a table. Functions can be used in constraints, computed columns, joins, WHERE clauses, or even in other functions. Functions are an incredibly powerful part of SQL Server.

 

Functions can be Deterministic or Nondeterministic:

A deterministic function will return the same result when it is called with the same set of input parameters. Adding two numbers together is an example of a deterministic function.

A nondeterministic function, on the other hand, may return different results every time they are called with the same set of input values. Even if the state of the data in the database is the same, the results of the function might be different.
The GETDATE function, for example, is nondeterministic. One caveat of almost all nondeterministic functions is that they are executed once per statement, not once per row. If you query 90,000 rows of data and use the RAND function to attempt to produce a random value for each row you will be disappointed; SQL Server will only generate a single random number for the entire statement. The only exception to this rule is NEWID, which will generate a new GUID for every row in the statement.
When we create a function, SQL Server will analyze the code we’ve created and evaluate whether the function is deterministic. If our function makes calls to any nondeterministic functions, it will, itself, be marked as nondeterministic. SQL Server relies on the author of a SQL CLR function to declare the function as deterministic using an attribute.
Deterministic functions can be used in indexed views and computed columns whereas nondeterministic functions cannot.

 








User-defined functions

Why use them?

·         They allow 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.

·         They allow 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 does not need to be reparsed and re optimized with each use resulting in much faster execution times.

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.

·         They can reduce network traffic.
An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.

NOTE:  Transact-SQL user-defined functions in queries can only be executed on a single thread (serial execution plan).


























Components of a User-defined Function:
User-defined functions can be written in Transact-SQL, or in any .NET programming language. For more information about using .NET languages in functions, see CLR User-Defined Functions.

All user-defined functions have the same two-part structure: a header and a body. The function takes zero or more input parameters and returns either a scalar value or a table.

The header defines:
Function name with optional schema/owner name
Input parameter name and data type
Options applicable to the input parameter
Return parameter data type and optional name
Options applicable to the return parameter

The body defines the action, or logic, the function is to perform. It contains either:
One or more Transact-SQL statements that perform the function logic
A reference to a .NET assembly

 
IF OBJECT_ID (N'dbo.GetWeekDay', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetWeekDay;
GO
CREATE FUNCTION dbo.GetWeekDay       -- function name
(@Date datetime)                     -- input parameter name and data type
RETURNS int                          -- return parameter data type
AS
BEGIN                                -- begin body definition
RETURN DATEPART (weekday, @Date)     -- action performed
END;
GO

The following example shows the function used in a Transact-SQL statement.

SELECT dbo.GetWeekDay(CONVERT(DATETIME,'20020201',101)) AS DayOfWeek;
GO
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 

Types of functions:

Scalar Function:
User-defined scalar functions return a single data value of the type defined in the RETURNS clause. For an inline scalar function, there is no function body; the scalar value is the result of a single statement. For a multi statement scalar function, the function body, defined in a BEGIN...END block, contains 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. They are deprecated and will be removed in a future version of SQL Server.

The following example creates a multi statement scalar function in the AdventureWorks2012 database. The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL 
    DROP FUNCTION ufnGetInventoryStock; 
GO 

CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int) 
RETURNS int  
AS  
-- Returns the stock level for the product. 
BEGIN 
    DECLARE @ret int; 
    SELECT @ret = SUM(p.Quantity)  
    FROM Production.ProductInventory p  
    WHERE p.ProductID = @ProductID  
        AND p.LocationID = '6'; 
     IF (@ret IS NULL)  
        SET @ret = 0; 
    RETURN @ret; 
END; 
     GO


SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply 
FROM Production.Product 
WHERE ProductModelID BETWEEN 75 and 80;















Table-Valued Functions:
User-defined table-valued functions return a table data type.

A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.

Inline User-defined Function Rules

Inline user-defined functions follow these rules:
·         The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.

·         There is no function body delimited by BEGIN and END.

·         The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.

·         The table-valued function accepts only constants or @local_variable arguments

For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.

Inline User-Defined Functions:
Inline user-defined functions are a subset of user-defined functions that return a table data type. Inline functions can be used to achieve the functionality of parameterized views.

The following example returns store names and cities for a specified region.

USE AdventureWorks2008R2;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.BusinessEntityAddress AS BEA ON BEA.BusinessEntityID = S.BusinessEntityID
    JOIN Person.Address AS A ON A.AddressID = BEA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

This view would be better if it were more generalized and let users specify the region they are interested in viewing. Views, however, do not support parameters in the search conditions specified in the WHERE clause. Inline user-defined functions can be used to support parameters in the search conditions specified in the WHERE clause. The following example creates an inline function that allows users to specify the region in their query:

 
 
 
USE AdventureWorks2008R2;
GO
 
IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;
GO
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT s.Name AS Store, a.City
        FROM Sales.Store AS s
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS sp 
            ON sp.StateProvinceID = a.StateProvinceID
        WHERE sp.Name = @Region
       );
GO
 
-- Example of calling the function for a specific region
 
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington')
ORDER BY City;
GO


Inline Functions and Indexed Views:
Inline functions can also be used to increase the power of indexed views. The indexed view itself cannot use parameters in its WHERE clause search conditions to tailor the stored result set to specific users. You can, however, define an indexed view that stores the complete set of data that matches the view, and then define an inline function over the indexed view that contains parameterized search conditions that allow users to tailor their results. If the view definition is complex, most of the work performed to build a result set involves operations such as building aggregates or joining several tables when the clustered index is created on the view.

If you then create an inline function that references the indexed view, the function can apply the user's parameterized filters to return specific rows from the materialized result set of the indexed view. For example:
You define a view vw_QuarterlySales that aggregates all sales data into a result set that reports summarized sales data by quarter for all stores.

You create a clustered index on vw_QuarterlySales to materialize a result set containing the summarized data.
You create an inline function to filter the summarized data:

CREATE FUNCTION dbo.ufn_QuarterlySalesByStore
     ( @StoreID int )
RETURNS table
AS
RETURN (
        SELECT *
        FROM SalesDB.dbo.vw_QuarterlySales
        WHERE StoreID = @StoreID
       )

Users can then get the data for their specific store by selecting from the inline function:

SELECT *
FROM fn_QuarterlySalesByStore(14432)

Most of the work needed to satisfy the queries issued at Step 4 is to aggregate the sales data by quarter. This work is done once at Step 2. Each individual SELECT statement in Step 4 uses the function fn_QuarterlySalesByStore to filter out the aggregated data specific to their store.

Components of a Table-Valued User-defined Function

In a table-valued user-defined function:
·         The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.

·         The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.

·         When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.

USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
    -- Columns returned by the function
    ContactID int PRIMARY KEY NOT NULL,
    FirstName nvarchar(50) NULL,
    LastName nvarchar(50) NULL,
    JobTitle nvarchar(50) NULL,
    ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
    DECLARE
        @FirstName nvarchar(50),
        @LastName nvarchar(50),
        @JobTitle nvarchar(50),
        @ContactType nvarchar(50);
..................
..................
..................
..................
..................
..................
    -- Return the information to the caller
    IF @ContactID IS NOT NULL
    BEGIN
        INSERT @retContactInformation
        SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
    END;
    RETURN;
END;
GO

No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.
The body of a TVF can either contain just a single statement or multiple statements, but the two cases are handled very differently by the optimizer. If the function body contains just a single statement (often referred to as an “inline TVF”), then the optimizer treats it in a similar fashion to a view in that it will “decompose” it and simply reference the underlying objects (there will be no reference to the function in the resulting execution plan).
However, by contrast, multi-statement TVFs present an optimization problem for SQL Server; it doesn’t know what to do with them. It treats them rather like a table for which it has no available statistics – the optimizer assumes that it the TVF will always return one row. As a result, even a very simple multi-statement TVF can cause severe performance problems.
The thing to remember is, there are no statistics generated for these things. That means the optimizer thinks they return a single row of data. When they do only return a few rows, everything is fine. When they return even as little as a hundred rows, like the example I’m posting below, they stink. 

The following example uses the table-valued function dbo.ufnGetContactInformation in the FROM clause of two SELECT statements.

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

Built-in Functions:

Built-in functions are provided by SQL Server to help you perform a variety of operations. They cannot be modified. You can use built-in functions in Transact-SQL statements to:

·         Access information from SQL Server system tables without accessing the system tables directly. For more information, see Using System Functions.

·         Perform common tasks such as SUM, GETDATE, or IDENTITY. For more information, see Built-in Functions (Transact-SQL).

Built-in functions return either scalar or table data types. For example, @@ERROR returns 0 if the last Transact-SQL statement executed successfully. If the statement generated an error, @@ERROR returns the error number. And the function SUM(parameter) returns the sum of all the values for the parameter.

Creating User-Defined Functions (Database Engine)

User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name (schema_name.function_name) must be unique.

 

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 cannot 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 can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not 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 cannot be performed in a function.

NOTE: If a CREATE FUNCTION statement produces side effects against resources that do not exist when the CREATE FUNCTION statement is issued, SQL Server executes the statement. However, SQL Server does not execute the function when it is invoked.

The number of times that a function specified in a query is actually 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.

 

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 are not 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
@@MAX_CONNECTIONS
GET_TRANSMISSION_STATUS
@@PACK_RECEIVED
GETDATE
@@PACK_SENT
GETUTCDATE
@@PACKET_ERRORS
@@CONNECTIONS
@@TIMETICKS
@@CPU_BUSY
@@TOTAL_ERRORS
@@DBTS
@@TOTAL_READ
@@IDLE
@@TOTAL_WRITE
@@IO_BUSY


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

NEWID
RAND
NEWSEQUENTIALID
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.

 

Specifying 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 1024 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 do not support output parameters.

Executing User-Defined Functions (Database Engine):

User-defined functions can be invoked in queries or in other statements or expressions such as computed columns or string expressions. Scalar-valued functions can be executed using the EXECUTE statement.

Invoking User-defined Functions That Return a Scalar Value:

You can invoke a user-defined function that returns a scalar value anywhere that a scalar expression of the same data type is allowed in Transact-SQL statements. Scalar-valued functions must be invoked by using at least the two-part name of the function. For more information about multipart names. 

User-defined functions that return scalar values are allowed in these locations:

As an expression in the select_list of a SELECT statement:

USE AdventureWorks;
GO
SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
    StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE ListPrice > .0000
ORDER BY ProductID, StartDate;
GO
As an expression or string_expression in a WHERE or HAVING clause predicate:
USE AdventureWorks;
GO
SELECT ProductID, ListPrice, StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE dbo.ufnGetProductDealerPrice(ProductID, StartDate) > .0000
ORDER BY ProductID, StartDate;
GO
As a group_by_expression in a GROUP BY clause.
As an order_by_expression in an ORDER BY clause.
As an expression in the SET clause in an UPDATE statement:
USE AdventureWorks;
GO
UPDATE Production.ProductListPriceHistory
SET ListPrice = dbo.ufnGetProductDealerPrice(ProductID, StartDate)
WHERE ProductID > 900;
GO
As an expression in the VALUES clause of an INSERT statement:
User-defined functions referenced in these locations are logically executed one time per row.



CHECK Constraints
User-defined functions that return scalar values can be invoked in CHECK constraints if the argument values passed to the function reference columns only in the table or constants. Every time the query processor checks the constraint, the query processor calls the function with the argument values associated with the current row being checked. The owner of a table must also be the owner of the user-defined function invoked by a CHECK constraint on the table.
DEFAULT Definitions
User-defined functions can be invoked as the constant_expression of DEFAULT definitions if the argument values passed to the function only contain constants. The owner of the table must also be the owner of the user-defined function invoked by a DEFAULT definition on the table.
Computed Columns
Functions can be invoked by computed columns if the argument values passed to the function reference only columns in the table or constants. The owner of the table must also be the owner of the user-defined function invoked by a computed column in the table.
Assignment Operators
Assignment operators (left_operand = right_operand) can invoke user-defined functions that return a scalar value in the expression specified as the right operand.
Control-of-Flow Statements
User-defined functions that return scalar values can be invoked by control-of-flow statements in their Boolean expressions.
CASE Expressions
User-defined functions that return a scalar value can be invoked in any of the CASE expressions.
PRINT Statements
User-defined functions that return a character string can be invoked as the string_expr expression of PRINT statements.
Functions and Stored Procedures
Function arguments can also be a reference to a user-defined function that returns a scalar value.
RETURN integer_expression statements in stored procedures can invoke user-defined functions that return an integer as the integer_expression.
RETURN return_type_spec statements in user-defined functions can invoke user-defined functions that return a scalar data type such as the return_type_spec, provided the value returned by the invoked user-defined function can be implicitly converted to the return data type of the invoking function.

Executing User-defined Functions That Return a Scalar Value
You can execute user-defined functions that return scalar values in the same manner as stored procedures. When executing a user-defined function that returns a scalar value, the parameters are specified in the same way they are for stored procedures:
The argument values are not enclosed in parentheses.
Parameter names can be specified.
If parameter names are specified, the argument values do not have to be in the same sequence as the parameters.
The following example creates a user-defined function that returns a decimal scalar value.
IF OBJECT_ID(N'dbo.ufn_CubicVolume', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_CubicVolume;
GO
CREATE FUNCTION dbo.ufn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

 

The following example executes the dbo.ufn_CubicVolume function. Using the Transact-SQL EXECUTE statement, the arguments are identified in an order different from the parameters in the function definition:
DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5;
SELECT @MyDecimalVar;
GO
 

Invoking User-defined Functions That Return a table Data Type:
You can invoke a user-defined function that returns a table where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. An invocation of a user-defined function that returns a table can be followed by an optional table alias. The following example illustrates calling the table-valued function dbo.ufnGetContactInformation in the FROM clause of a SELECT statement.
SQL

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

When a user-defined function that returns a table is invoked in the FROM clause of a subquery, the function arguments cannot reference any columns from the outer query.
Static, read-only cursors are the only type of cursor that can be opened on a SELECT statement whose FROM clause refers to a user-defined function that returns a table.
A SELECT statement that references a user-defined function that returns a table invokes the function one time.

Invoking Built-in Table-valued Functions
There are several built-in table-valued functions that return a table value. The invocation of these built-in user-defined functions can be either unqualifed or can use the sys schema qualifier. You should use the sys schema qualifier for built-in table-valued functions, because it prevents conflicts with user-defined functions of the same name. The following example shows how to invoke the system built-in function fn_helpcollations.
SELECT *
FROM sys.fn_helpcollations();
GO
Using Hints with Table-valued Functions:
When you create a user-defined function, you can apply a table hint in any queries that form the function definition. Hints applied to views that reference Transact-SQL table-valued functions are also applied to the functions. These functions can conflict with the hints in the function definition. For more information, see View Resolution.
You cannot apply hints on views that reference CLR table-valued functions.
Note:The ability of the Database Engine to apply hints on views to multi-statement table-valued functions that are part of the view definition will be removed in a future version of SQL Server.
You cannot apply a table hint to the result of any table-valued function in the FROM clause of a query.


 

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.

 

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 cannot return multiple result sets. Use a stored procedure if you need to return multiple result sets.


·         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:
o    BEGIN DIALOG CONVERSATION
o    END CONVERSATION
o    GET CONVERSATION GROUP
o    MOVE CONVERSATION
o    RECEIVE
o    SEND

  • Statements with side effects (insert/update/delete) and temporary tables may not be used. You can, however, use table variables. Table variables are allowed in UDFs because they are created as variables, not through DDL. DDL is viewed as producing a side effect and is not allowed.

  • TRY/CATCH statements are not allowed since CATCH can have the side effect of masking the error state of a given function.
Error handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR.

Execute User-defined Functions

Execute a user defined function using Transact-SQL.

Limitations and restrictions

In Transact-SQL, parameters can be supplied either by using value or by using @parameter_name=value. A parameter is not part of a transaction; therefore, if a parameter is changed in a transaction that is later rolled back, the value of the parameter does not revert to its previous value. The value returned to the caller is always the value at the time the module returns.

 

Security

Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. Permissions are checked at the time EXECUTE statement is encountered, even if the EXECUTE statement is included within a module. For more information, see EXECUTE (Transact-SQL)

 

 

Modify User-defined Functions

You can modify user-defined functions in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. Modifying user-defined functions as described below will not change the functions’ permissions, nor will it affect any dependent functions, stored procedures, or triggers.
-- Scalar-Valued Function 
USE [AdventureWorks2012] 
GO 
ALTER FUNCTION [dbo].[ufnGetAccountingEndDate]() 
RETURNS [datetime]   
AS  
BEGIN 
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112)); 
END; 

 

Delete User-defined Functions

You can delete (drop) user-defined functions in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL

Limitations and Restrictions

·         You will not be able to delete the function if there are Transact-SQL functions or views in the database that reference this function and were created by using SCHEMABINDING, or if there are computed columns, CHECK constraints, or DEFAULT constraints that reference the function.

·         You will not be able to delete the function if there are computed columns that reference this function and have been indexed.

 

Security

Permissions

Requires ALTER permission on the schema to which the function belongs, or CONTROL permission on the function.
USE AdventureWorks2012; 
GO 
-- determines if function exists in database 
IF OBJECT_ID (N'Sales.fn_SalesByStore', N'IF') IS NOT NULL 
-- deletes function 
    DROP FUNCTION Sales.fn_SalesByStore; 
GO

Rename User-defined Functions:

You can rename user-defined functions in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL.

Limitations and Restrictions

·         Function names must comply with the rules for identifiers.
·         Renaming a user-defined function will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that you do not rename this object type. Instead, drop and re-create the stored procedure with its new name.
·         Changing the name or definition of a user-defined function can cause dependent objects to fail when the objects are not updated to reflect the changes that have been made to the function.

 

Security

Permissions

To drop the function, requires either ALTER permission on the schema to which the function belongs or CONTROL permission on the function. To re-create the function, requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created.

Using SQL Server Management Studio

To rename user-defined functions

1.      In Object Explorer, click the plus sign next to the database that contains the function you wish to rename and then
2.      Click the plus sign next to the Programmability folder.
3.      Click the plus sign next to the folder that contains the function you wish to rename:
o    Table-valued Function
o    Scalar-valued Function
o    Aggregate Function
4.      Right-click the function you wish to rename and select Rename.
5.      Enter the function’s new name.

 

Using Transact-SQL

To rename user-defined functions

This task cannot be performed using Transact-SQL statements. To rename a user-defined function using Transact-SQL, you must first delete the existing function and then re-create it with the new name. Ensure that all code and applications that used the function’s old name now use the new name.

View User-defined Functions:

Several system stored procedures and catalog views provide information about stored procedures. Using these, you can:
See the definition of the function. That is, the Transact-SQL statements used to create a user-defined function. This can be useful if you do not have the Transact-SQL script files used to create the function.
Get information about a function such as its schema, when it was created, and its parameters.
List the objects used by the specified function, and the objects that use the specified function. This information can be used to identify the functions affected by the changing or removal of an object in the database.
To view the definition of a user-defined function
·         sys.sql_modules (Transact-SQL)
·         sp_helptext (Transact-SQL)

To view information about a user-defined function
·         sys.objects (Transact-SQL)
·         sys.parameters (Transact-SQL)
·         sp_help (Transact-SQL)

To view the dependencies of a user-defined function

 

Security

Permissions

Using sys.sql_expression_dependencies to find all the dependencies on a function requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. System object definitions, like the ones returned in OBJECT_DEFINITION, are publicly visible.

Rewriting Stored Procedures as Functions
This topic describes how to determine whether to rewrite existing stored procedure logic as user-defined functions. For example, if you want to invoke a stored procedure directly from a query, repackage the code as a user-defined function.

In general, if the stored procedure returns a, single, result set, define a table-valued function. If the stored procedure computes a scalar value, define a scalar function.

Criteria for Table-Valued Functions
If a stored procedure meets the following criteria, it is a good candidate for being rewritten as a table-valued function:
The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters. This scenario can be handled with an inline table-valued function.

The stored procedure does not perform update operations, except to table variables.

There is no need for dynamic EXECUTE statements

The stored procedure returns one result set.

The primary purpose of the stored procedure is to build intermediate results that are to be loaded into a temporary table, which is then queried in a SELECT statement. INSERT...EXEC statements can be written using table-valued functions. For example, consider the following sequence:

INSERT #temp EXEC sp_getresults
SELECT ...
FROM #temp, t1
WHERE ...

The sp_getresults stored procedure can be rewritten as a table-valued function, for example fn_results(), which means the preceding statements can be rewritten as:
INSERT #temp
SELECT ...
FROM fn_results(), t1
WHERE ...

Rewriting Extended Stored Procedures Using CLR

CLR functions provide a more reliable and scalable alternative to extended stored procedures. Many extended stored procedures perform some computational task that is harder to express in Transact-SQL. Such stored procedures can be rewritten using the CLR given the benefits described above. Further, extended stored procedures that return result sets by accessing an external resource such as a file or Web Service can be rewritten using a CLR table-valued function. For more information, see Creating CLR Functions.




Performance Considerations:
Issue1:
IF OBJECT_ID(N'Production.ProductCostDifference', N'FN') IS NOT NULL
    DROP FUNCTION Production.ProductCostDifference ;
GO

CREATE FUNCTION Production.ProductCostDifference
    (
      @ProductId INT ,
      @StartDate DATETIME ,
      @EndDate DATETIME
    )
RETURNS MONEY
AS
    BEGIN
        DECLARE @StartingCost AS MONEY ;
        DECLARE @CostDifference AS MONEY ;

        SELECT TOP 1
                @StartingCost = pch.StandardCost
        FROM    Production.ProductCostHistory AS pch
        WHERE   pch.ProductID = @ProductId
                AND EndDate BETWEEN @StartDate
                                AND @EndDate
        ORDER BY StartDate ASC ;

        SELECT TOP 1
                @CostDifference = StandardCost - @StartingCost
        FROM    Production.ProductCostHistory AS pch
        WHERE   pch.ProductID = @ProductId
                AND EndDate BETWEEN @StartDate
                                AND @EndDate
        ORDER BY StartDate DESC ;

        RETURN  @CostDifference ;
    END

SELECT Production.ProductCostDifference(707, '1999-01-01', GETDATE()) ;

/*
 column1   
 ----------
 1.8504 
*/












--QUERY 1
SELECT  ProductID ,
        Name AS ProductName ,
        Production.ProductCostDifference
          (ProductID, '2000-01-01', GETDATE())
           AS CostVariance
FROM    Production.Product ;

--QUERY 2
SELECT  ProductID ,
        Name AS ProductName ,
        Production.ProductCostDifference
         (ProductID, '2000-01-01', GETDATE())
          AS CostVariance
FROM    Production.Product
WHERE   Production.ProductCostDifference
          (ProductID, '2000-01-01', GETDATE())
            IS NOT NULL ;
Unfortunately, SQL Server is not terribly intelligent in the way that it works with scalar functions. In the first query, our ProductCostDifference function will be executed once for each of the 504 rows in the Production.Product table. This leads to an increase in disk access, CPU utilization, and memory utilization.
We can see that all data is read from disk in the Index Scan operator before being sent to the Compute Scalar operator, where our function is applied to the data. If we open up the Properties page for the Compute Scalar node (pressing F4 will do this if you haven’t changed the default SQL Server Management Studio settings) and examine the Define Values property list. If this references the function name (rather than the column name), as it will in this case, then the function is being called once per row.
StmtText
-----------------------------------------------------------------------------------  |--ComputeScalar(DEFINE:([Expr1003]=[AdventureWorks2008].[Production].[ProductCostDifference]([AdventureWorks2008].[Production].[Product].[ProductID],'2000-01-01 00:00:00.000',getdate())))
       |--IndexScan(OBJECT:([AdventureWorks2008].[Production].[Product].[AK_Product_Name]))
The situation is even worse for the second query in Listing 4 in that the function needs to be evaluated twice: once for every 504 rows in the Production.Product table and once again for the 157 rows that produce a non-NULL result from our scalar function. The execution plan for this query is  as shown below.
StmtText
-----------------------------------------------------------------------------------  |--ComputeScalar(DEFINE:([Expr1003]=[AdventureWorks2008].[Production].[ProductCostDifference]([AdventureWorks2008].[Production].[Product].[ProductID],'2000-01-01 00:00:00.000',getdate())))
       |--Filter(WHERE:([AdventureWorks2008].[Production].[ProductCostDifference]([AdventureWorks2008].[Production].[Product].[ProductID],'2000-01-01 00:00:00.000',getdate()) IS NOT NULL))
            |--IndexScan(OBJECT:([AdventureWorks2008].[Production].[Product].[AK_Product_Name]))

(3 row(s) affected)
Again, we can establish whether or not a function is being executed once per row by examining the details of this plan; in this case, the properties of either the Compute Scalar or the Filter node. The Predicate property of the Filter node shows that that the filter operation is filtering on:
:([AdventureWorks2008].[Production].[ProductCostDifference]([AdventureWorks2008].[Production].[Product].[ProductID],'2000-01-01 00:00:00.000',getdate()) IS NOT NULL)
In other words, SQL Server is evaluating the function once for every row in the product table. No function ‘inlining’ has been performed; we would be able to see the ‘inlined’ source code if it had been.
This may seem like a trivial point to labor over, but it can have far reaching performance implications. Imagine that you have a plot of land. On one side of your plot of land is a box of nails. How long would it take you to do anything if you only used one nail at a time and kept returning to the box of nails every time you needed to use another one? This sort of thing might not be bad for small tasks like hanging a picture on the wall, but it would become incredibly time consuming if you were trying to build an addition for your house. The same thing is happening within your T-SQL. During query evaluation, SQL Server must evaluate the output of the scalar function once per row. This could require additional disk access and potentially slow down the query.
Scalar functions, when used appropriately, can be incredibly effective. Just be careful to evaluate their use on datasets similar to the ones you will see in production before you make the decision to use them; they have some characteristics that may cause undesirable side effects. If your scalar UDF needs to work on many rows, one solution is to rewrite it as a table-valued function, as will be demonstrated a little later.

Issue 2:
Scalar functions in the WHERE Clause
Using a scalar function in the WHERE clause can also have disastrous effects on performance. Although the symptoms are the same (row-by-row execution), the cause is different. Consider the call to the built-in scalar function, DATEADD, in Listing 5.
SELECT  *
FROM    Sales.SalesOrderHeader AS soh
WHERE   DATEADD(mm, 12, soh.OrderDate) < GETDATE()
Poor Usage of Where Clause

This code will result in a full scan of the Sales.SalesOrderHeader table because SQL Server can’t use any index on the OrderDatecolumn. Instead, SQL Server has to scan every row in the table and apply the function to each row. A better, more efficient way to write this particular query would be to move the function, as shown in Listing 6.
SELECT  *
FROM    Sales.SalesOrderHeader AS soh
WHERE   soh.OrderDate < DATEADD(mm, -12, GETDATE())
Better use of a function in the WHERE clause
Optimizing the use of a function in the WHERE clause isn’t always that easy, but in many occasions this problem can be alleviated through the use of careful design, a computed column, or a view.

Issue 3: (Kalen Delaney)
I discovered some bad behavior shortly after SQL Server 2000 came out when I was contracted to do some performance tuning and troubleshooting for an insurance company in Atlanta. One of the developers had written a scalar UDF to mask social security numbers in a report they needed to run frequently. The function took a social security number as a character string input value, replaced some of the digits with special characters, and returned the masked value as output.

When we ran the report with only about 10,000 customer rows, it took more than 20 minutes to run. Something wasn’t right. I started a trace to see what statements in the report were taking the most time and saw 10,000 separate calls to the masking function. For each row, the trace showed SQL Server calling the function, replacing the string values, and returning the result, just as if we had written code with a cursor to process the data one row at a time. If I replaced the function call with the actual code used to mask the characters, the trace showed a single call to the function and the report finished in under a minute.

For example, here’s a scalar UDF that takes a social security number (nine digits and two hyphens) as input and replaces the first five digits with X’s.

CREATE FUNCTION dbo.fnMaskSSN (@ssn char(11))
RETURNS char(11)                             
AS BEGIN                             
SELECT @SSN = 'xxx-xx-' + right(@ssn,4)                             
RETURN @SSN                              
END;

To test it, you could use any table that has a social security number column, including the authors table from the old sample database pubs.

                              SELECT dbo.fnMaskSSN(au_id), au_lname, au_fname FROM authors;

To improve the performance, you would need to forget about the UDF, and replace the SELECT with the function definition:

                              SELECT 'xxx-xx-' + right(au_id,4), au_lname, au_fname FROM authors;

Of course, with 23 rows in the authors table, you wouldn’t see the performance advantage, but if you ran a trace you would see a difference in the amount of work that SQL Server was doing behind the scenes. Although the developer wasn’t happy to have his function ignored, the DBA, who was ultimately responsible for system performance, was delighted.







Behavior with NULL input:
“I’m creating a scalar function that will be used against a column that contains multiple NULL values. Is there a way to stop the function from being executed when a NULL is passed in as a parameter value?”
Yes. In fact, doing so is actually a very straightforward process, at least for scalar functions. You simply include the RETURNSNULL ON NULL INPUT option in your WITH clause, as shown in the following example (option highlighted):
CREATE FUNCTION dbo.searchString
(
      @string NVARCHAR(MAX),
      @keyword NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE     @retValue NVARCHAR(MAX)
SET         @retValue = 'Input Valid…'
RETURN      @retValue
END
GO
By default, when you call a scalar function, the database engine executes the function body whether or not a NULL is passed in as a parameter value. However, by including the RETURNS NULL ON NULL INPUT option, the database engine will not execute the function body when a NULL value is passed in. For example, the following SELECT statement returns a NULL value:
SELECT dbo.searchString(N'SqlAndMe', N'Test'),   -- Input Valid…
SELECT dbo.searchString(N'SqlAndMe', N''),       -- Input Valid…
SELECT dbo.searchString(N'SqlAndMe', NULL),      -- NULL
SELECT dbo.searchString(NULL, N'Test')           -- NULL
GO
When the database engine sees the NULL input value, it simply returns NULL without processing the function body. If your function supports multiple input parameters, the database engine returns NULL if NULL is passed into any one of those parameters and does not execute the function body.
However, you cannot use the RETURNS NULL ON NULL INPUT option for a table-valued function. Because a table-value function returns a full result set (table), it is possible for the function to return data even if a parameter value is NULL. As such, the option cannot be practically applied to a table-valued function.
Users of SQL Server 2000 and earlier are out of luck, though, as this feature was introduced in SQL Server 2005.

CROSS APPLY:
I created a table-valued function that I want to apply to a column in each row returned by the query. However, when I try to join the function to the target table in the query’s FROM clause, I receive an error. Is there an effective way to invoke the function for each row in the result set?

There is as long as you’re using SQL Server 2005 or later. Starting with SQL Server 2005, you’ve been able to use the APPLY operator to join one or more tables to a table-valued function in order to invoke that function against each row in the result set. Prior to SQL Server 2005, you had to come up with a complex workaround to achieve this.

The best way to understand how the operator works is to look at an example. The following T-SQL creates a table-valued function that returns the total number of items sold for each sale listed in the SalesOrderDetail table of the AdventureWorks2012database:

USE AdventureWorks2012;
      GO

      IF OBJECT_ID(N'dbo.ifGetTotalItems', N'IF') IS NOT NULL
      DROP FUNCTION dbo.ifGetTotalItems;
      GO
      CREATE FUNCTION dbo.ifGetTotalItems (@OrderID INT)
      RETURNS TABLE
      WITH SCHEMABINDING
      AS
      RETURN
      (
        SELECT SUM(OrderQty) AS TotalItems
        FROM Sales.SalesOrderDetail
        WHERE SalesOrderID = @OrderID
        GROUP BY SalesOrderID
      );

The function takes as an argument the SalesOrderID value that identifies the sale. In this case, the function returns only a single row with one column, but you can just as easily create a function that returns multiple rows. The simplest way to verify that the function is working as you expect is to run a simple SELECT statement that calls the function and passes in a SalesOrderIDvalue:

SELECT TotalItems FROM dbo.ifGetTotalItems(43659);

For this example, we pass in 43659 the parameter value. The function then returns 26. However, all we’ve so far is to demonstrate that the function works as we expect. Let’s create a more complex SELECT statement that applies the function to each row returned from the SalesOrderHeader table. That’s where the APPLY operator comes in, as shown in the following example:

SELECT s.SalesOrderID, s.OrderDate, s.SalesPersonID,
        f.TotalItems
      FROM Sales.SalesOrderHeader s
        CROSS APPLY dbo.ifGetTotalItems(s.SalesOrderID) f
      ORDER BY SalesOrderID;

Notice that after we specify the SalesOrderHeader table in the FROM clause, we then include the CROSS APPLY keywords, following by the function, with the SalesOrderID column passed in as the parameter value.

The APPLY operator takes two forms: CROSS APPLY and OUTER APPLY. The CROSS APPLY combination returns rows from the primary table (in this case, SalesOrderHeader) only if they produce a result set from the table-valued function. The OUTERAPPLY combination returns all rows from the primary table. In this case, both forms of APPLY return the same number of rows. 

As you can see, the results include the TotalItems column, which is the number of items associated with that sale. In other words, we were able to apply the table-valued function to each row returned from the SalesOrderHeader table. If our function had returned multiple rows, the result set would have included that number of rows for each row returned by the function. For example, if the function always returned three rows for each SalesOrderID value, our result set would include three times the number of rows than it currently does.

Default Value for Input parameter:
“I’m creating a user-defined function and want to specify a default value for an input parameter. Is that possible?”

Yes, it is possible and fairly easy to do. When defining the parameter, include the default value, along with the equal sign, as shown the following example (highlighted):

USE AdventureWorks2012;
      GO
      IF OBJECT_ID(N'dbo.GetPersonTypeCount', N'IF') IS NOT NULL
      DROP FUNCTION dbo.GetPersonTypeCount;
      GO
      CREATE FUNCTION dbo.GetPersonTypeCount
        (@type NCHAR(2) = 'IN')
      RETURNS TABLE
      WITH SCHEMABINDING
      AS
      RETURN
      (
        SELECT COUNT(*) AS PersonCount
        FROM Person.Person
        WHERE PersonType = @type
        GROUP BY PersonType
      );
      GO


In this case, we’ve merely specified that the default value for the @type parameter is IN. However, if we want to use the default value when calling the view, we must specify the default keyword as the parameter value, as shown in the following example:

SELECT * FROM dbo.GetPersonTypeCount(default);

The function will now use the IN default value and return a value of 18484. That said, even though we’ve defined a default value, we can still specify that value when calling the function:

SELECT * FROM dbo.GetPersonTypeCount('in');

Or we can specify a different value, as we would if no default had been specified:

SELECT * FROM dbo.GetPersonTypeCount('em');
Parameterized View Vs Function:
“I’ve heard you can use a function to parameterize a view, but I don’t see how you can incorporate a function into a view definition in such a way to support parameters. Can you explain how that is done?”

Using a function to parameterize a view has little to do with the view definition itself. It merely means you’re creating a function that either duplicates the logic of the view or calls the view within the function. In either case, you use a parameter to qualify the function’s SELECT statement.

For example, suppose we create the following view to retrieve data about the number of employees per job title:

USE AdventureWorks2012;
     GO
     IF OBJECT_ID(N'dbo.JobData', N'V') IS NOT NULL
     DROP VIEW dbo.JobData;
     GO
     CREATE VIEW dbo.JobData
     WITH SCHEMABINDING
     AS
       SELECT JobTitle, COUNT(*) AS TotalEmps
       FROM HumanResources.Employee
       GROUP BY JobTitle;
     GO

As you can see, the view is very straightforward. The SELECT statement groups the data by the JobTitle column and retrieves a count for each group. You can, of course, create a view that is far more complex than this one, but what we’ve done here is enough to demonstrate how this all works.

Once you’ve created the view, you can test it by running a simple SELECT statement, similar to the following:

SELECT * FROM dbo.JobData;

Not surprisingly, the statement returns all rows and columns returned by the view.

As with any SELECT statement that retrieves data from a view, we can further refine our SELECT statement by including the logic necessary to return the results we need, as shown in the following example:

SELECT TotalEmps FROM dbo.JobData
WHERE JobTitle = 'Buyer';

In this case, we’ve merely specified a column in the SELECT list and added a WHERE clause that limits the results to those rows in which the JobTitle value equals Buyer. Now the statement returns only a value of 9 because that’s how many employees have that title.

Rather than creating a view and then qualifying the SELECT statements that call the view, we can instead create a table-valued function that incorporates the view’s logic, but also provides the ability to qualify the results through a parameter, as shown in the following example:
USE AdventureWorks2012;
GO
IF OBJECT_ID(N'dbo.GetJobData', N'IF') IS NOT NULL
DROP FUNCTION dbo.GetJobData;
GO
CREATE FUNCTION dbo.GetJobData (@title NVARCHAR(25))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
  SELECT COUNT(*) AS TotalEmps
  FROM HumanResources.Employee
  WHERE JobTitle = @title
  GROUP BY JobTitle
);
GO

Notice that the function’s SELECT statement is similar to that of the view’s except that we also include a WHERE clause that compares the JobTitle column to the @title input parameter. When you call the function, you simply pass in the job title as an argument:

SELECT * FROM dbo.GetJobData('Buyer');

As to be expected, the SELECT statement returns a value of 9. Chances are, however, you’ll want to incorporate the function in a more complex query. In the following example, we use the APPLY operator to join the Person and Employee tables to the GetJobData function:

SELECT
p.FirstName + ' ' + p.LastName AS FullName,
e.JobTitle,
f.TotalEmps
FROM
HumanResources.Employee e
INNER JOIN Person.Person p
  ON e.BusinessEntityID = p.BusinessEntityID
CROSS APPLY dbo.GetJobData(e.JobTitle) f;

For each row returned from the joined Person and Employee tables, the GetJobData function is applied to that row, based on the value of the JobTitle column. The results will then include a TotalEmps column, which will provide the total number of employees who share the same title as the person listed in that row. The following table provides a partial list of employees, their job titles, and the number of people who share that title.












UDF Vs View
User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries. While views are limited to a single SELECT statement, user-defined functions can contain additional statements that allow more powerful logic than is possible in views.

UDF Vs SP:
A table-valued user-defined function can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.


at

No comments:

Post a Comment