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:

CREATEFUNCTION[<schema Name>.]<Function name>([< @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 as defined in returns clause | Span style= "color: #0000ff;" >return (<select statement >)} 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:

FUNCTION dateonly (DateTime) varchar (a) as BEGIN CONVERT (varchar (12 ),@Date,101) END             

Then try to use:

From nx_comment '2012.04.28'--note that the preceding dbo is required. 

In fact, the above SQL statement is equivalent to:

From nx_comment CONVERT (varchar), com_posttime,'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,    (as-(difference      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:

CREATEFUNCTIONDbo. Avgage ()RETURNSIntAsbegin select Span style= "color: #ff00ff;" >avg (age) from person) go create function Dbo. Agedifference ( @Age int ) returns int as begin return  @Age Span style= "color: #808080;" >-dbo. Avgage (); --refers to another UDF within a UDF, which is gorgeous to say end      

Then execute the query:

Difference  

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:

FUNCTION dbo.fncontactname () TABLE  as RETURN ( ',as Name  from Man )

Then we can use the UDF like a table.

From Dbo.fncontactname ()

The output results are as follows:

　　

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

CREATEFUNCTION Dbo.fnnamelike (@LNamevarchar20))RETURNS TABLE  as RETURN ( SELECT id,lastname + ',' + FirstName as
                     
                       Name 
                      from Mans WHERE LastName like @LName + '%' ) 
                     

Then the query can be used this way:

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.

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.

FUNCTION dbo. Dateonly (@Date date) RETURNS date with SCHEMABINDING --After we add this sentence as  BEGIN 
              
                @Date 
               END 
                      

In executing the query, the function is deterministic.

　　

SQL Server UDF user custom functions