SQL Server UDF user custom functions

Definition of UDF

Similar to stored procedures, user-defined functions are also an ordered set of T-SQL statements, and UDFs are pre-optimized and compiled and grams as a unit of love. The main difference between UDFs and stored procedures is how the results are returned.

You can pass in parameters when using UDFs, but not outgoing parameters. The concept of an output parameter is replaced by a more robust return value. As with system functions, you can return a scalar value, which has the advantage that it is not limited to shaping data types as in stored procedures, but can return most SQL Server data types.

The following two types of UDFs are available:

• A UDF that returns a scalar value.
• Returns a UDF for a table.

Create syntax:

CREATE FUNCTION [<schema name>.]<functionName>([< @parameter name> [ as] [<schema name>.]<Data type> [= <default value> [READONLY]][,... N] ])RETURNS{<Scalar type> | TABLE [(<table definition>)] }[With [Encryption] | [SCHEMABINDING] | [RETURNS null on null INPUT | Called on NULL INPUT] |[EXECUTE as {caller| Self| owner|< ' user name ';][ as]{EXTERNAL NAME<Externam method> |BEGIN[<function statements>]{RETURN <Type asDefinedinch RETURNSClause| RETURN(<SELECTStatement>)}END}[;]

second, a UDF that returns a scalar value

This type of UDF, like most SQL Server built-in functions, returns a scalar value to the calling script or stored procedure, such as the GETDATE () or the user () function, which returns a scalar value.

The return value of a UDF is not limited to integers, but it can return any valid SQL Server data type except the BLOB, cursor, and timestamp (including user-defined types). When you want to return an integer, the UDF has the following two appealing aspects.

Unlike stored procedures, the purpose of a user-defined function return value is to provide meaningful data, whereas for a stored procedure, the return value can only indicate success or failure, and if it fails, it provides some specific information about the nature of the failure.
You can inline execute functions in a query, such as as part of a SELECT statement, but not with stored procedures.

Create a UDF as follows:

CREATE FUNCTION Dateonly (@DateDateTime)  RETURNSvarchar(a) as   BEGIN    RETURNCONVERT(varchar),@Date ,101)  END　　　　

Then try to use:

SELECT *  from nx_comment WHERE dbo = ' 2012.04.28 '-note that the preceding dbo is required. 

In fact, the above SQL statement is equivalent to:

SELECT *  from nx_comment WHERE CONVERT (varchar), Com_posttime,102='2012.04.28' 

Notice that the SQL statement using the UDF is more readable. The results appear as follows:

　　

Let's look at a simple query:

SELECT　　Name,age,    (SelectAVG from as Avgage,     - (Select AVG  from  as   from person　　

The above SQL query returns the result set as follows:

　　

To illustrate, the meaning of the column is the name, age, average age and the difference between the average age and the mean.

Below we use UDF to implement, first define two UDF as follows:

CREATE FUNCTIONdbo. Avgage ()RETURNS int 　　  as 　　 BEGIN     　　 RETURN(SELECT AVG(age) fromPerson )END 　　 GO 　　 CREATE FUNCTIONDbo. Agedifference (@Age int)RETURNS int 　　  as 　　 BEGIN     　　 RETURN @Age -Dbo. Avgage ();--referencing another UDF within a UDF, it's nice to say 　　 END

Then execute the query:

SELECT  as  as difference
From

The above query is the same as the single SQL above on the returned result set, but why do I feel that the speed seems to be a lot slower? Yes, buddy.

Third, the UDF that returns the table

User-defined functions in SQL Server are not limited to returning scalar values, but can also return tables. The returned table is to a large extent the same as the other tables. You can perform a join on a UDF that returns a table, and even apply a where condition to the result.

Instead of using a table as the return value is not difficult, for UDFs, the table is like any other SQL Server data type.

To illustrate the situation, I have specially built a table as follows:

　　

Create a UDF as follows:

CREATE FUNCTION Dbo.fncontactname () RETURNS TABLE  as RETURN (        　　SELECT+','+ as  Name         　　from  Mans        )　　　　

Then we can use the UDF like a table.

SELECT *  from Dbo.fncontactname ()

The output results are as follows:

　　

Now let's look at a simple usage, and define the UDF as follows:

CREATE FUNCTIONDbo.fnnamelike (@LName varchar( -))RETURNS TABLE 　　  as 　　 RETURN(SELECTId,lastname+ ',' +FirstName asName fromMansWHERELastName like @LName + '%')

Then the query can be used this way:

SELECT *  from Dbo.fnnamelike (' Liu ')

The results appear as follows:

　　

Without a WHERE clause, without filtering the select list, you can reuse the function without cutting and pasting. And this example does not do well, in fact, can be connected to the other table first, and then query, which is not the stored procedure.

Iv. Understanding Certainty

User-defined functions can be deterministic or non-deterministic. Determinism is not defined by any parameter type, but by the function's functionality. Given a specific set of valid inputs, each time the function returns the same result, it is said that the function is deterministic. SUM () is a deterministic built-in function. The sum of 3, 5, 10 is always 18, and the value of GETDATE () is nondeterministic, because GETDATE () will change every time it is called.
To achieve deterministic requirements, the function must meet the following 4 conditions.

• The function must be pattern-bound. This means that any object on which the function depends has a dependency record, and it is not allowed to change these objects until the dependent function is removed.
• All other functions referenced by a function, whether user-defined or system-defined, must be deterministic.
• You cannot reference a table defined outside a function (you can use table variables and temporary tables, as long as they are defined within the scope of the function).
• You cannot use extended stored procedures.

The importance of certainty is that it shows whether you want to index on a view or a computed column. If the results of a view or computed column can be reliably determined, the index on the view or computed column is allowed. This means that if a view or computed column references a nondeterministic function, no index will be allowed on that view or column.

　　What if the decision function is deterministic? In addition to the rules described above, this information is stored in the object's IsDeterministic property and can be checked with the ObjectProperty property.

SELECT ObjectProperty　　(object_id('dateonly'),'isdeterministic'); -just that custom function

The output results are as follows:

Incredibly non-deterministic. The reason for this is that this "with SCHEMABINDING" is not added to the previous definition of the function.

ALTER FUNCTION dbo. Dateonly (@Date  date)  RETURNS  date   with SCHEMABINDING -- When we add this sentence as 　　 BEGINRETURN@DateEND　　　　　　　　

In executing the query, the function is deterministic.

　　

　　

SQL Server UDF user custom functions