SQL server-focus in views and UDFs using Schemabinding (26)

Objective

In the previous section we discussed some of the limitations and recommendations in the view, which we talk about using schemabinding in UDFs and views, short content, in-depth understanding, always to review the basics.

SCHEMABINDING

In the previous section we talked about the need to specify schemabinding when creating an index on a view, so it is necessary to get to know the next bit of knowledge before continuing. What exactly is schemabinding? This option is available in views and UDFs, and if this option is specified in the View and UDF functions, then the description will bind the view and UDF tightly to the database object, specifying that this option can be tightly bound to the database object, and the performance of the query plan execution can be improved. Let's take a look at the use of schemabinding in UDFs and views.

Use in UDF

There are three ways to create a UDF function, and we hit it over again.

(1) Creating TVF inline table-valued functions

Use tsql2012goif object_id ('dbo. Getorderid') is not aNULL    DROP FUNCTION dbo. Getorderid; Gocreate FUNCTION dbo. Getorderid     (@custid INT) RETURNS TABLE with Schemabindingas   RETURN SELECT OrderID from Sales.orders WHERE CustID = @custidGO  

The above UDF is created by TVF, and when it is necessary to declare a temporary variable inside and return it, we need to do something like the following.

(2) Creating scalar value functions

Use tsql2012goif object_id ('dbo. Getorderid') is not aNULL    DROP FUNCTION dbo. Getorderid; Gocreate FUNCTION dbo. Getorderid (@custid INT)  RETURNS int with Schemabindingas BEGIN    DECLARE @tempID int    = OrderID From   sales.orders     = @custid;        RETURN @tempID;  END;  

When a UDF is used to insert a query into a temporary table, we can do something like this

(3) Creating multi-statement TVF inline table-valued functions

int int ) RETURNS @TestTable TABLE (RowNumber int, ID int, Name VARCHAR)asbegin    INT     ; With C as    (        'RowNumber' = row_number () over (ORDER by ID DESC),             OrderID, ShipName        From Sales.orders        )    INSERT    @TestTable    SELECT rownumber, OrderID, ShipName    from  C    returnend

All right, let's go through this. There are several ways to create UDF, we return to the topic, we create a UDF like this

Use tsql2012goif object_id ('dbo. GetId') is not aNULL    DROP FUNCTION dbo. GetId; Gocreate FUNCTION dbo. GetId     (@id INT) RETURNS TABLE with schemabindingas   = @idGO

At this point we can see the function we created under the table-valued Function folder in the corresponding database.

Since we are the values in the query table Compare.t_inner, let's delete the table and look at this.

At this point we will find that the table cannot be removed with the above error. Because the UDF we created above relies on the Compare.t_inner table, we cannot delete the table now, which references the custom function GetID. Below we modify the above columns we queried in UDF val1 for Val3 look

Use in view

Use tsql2012goif object_id ('dbo. GetId') is not aNULL    DROP FUNCTION dbo. GetId; Gocreate VIEW GetId with Schemabindingasselect val1 from Compare.t_inner

At this point, deleting the table Compare.t_inner will still appear and errors in the UDF. You cannot perform an * operation when using the schemabinding constraint, which can occur as an error:

Use tsql2012goif object_id ('dbo. GetId') is not NULL    * from Compare.t_inner

Let's look at some other scenarios. Using views to query across databases, we create two databases and create a test table in the corresponding database, respectively.

CREATE Database test1create database Test2go-- table1use test1gocreate TABLE TABLE1 (ID INT) Gouse test2go-- table2create TABLE TABLE2 (ID INT) gouse test1go

The next step is to create the view by executing schemabinding

CREATE VIEW crossdbview withSCHEMABINDING= T2. Idgo

The above specified schemabinding error means that an error occurs during a cross-database query and is limited to two-part names for reference objects. Here we make the following conclusions for using schemabinding in views and UDFs:

(1) When using schemabinding in views and UDFs, two requirements must be met, the first one is not allowed in the SELECT clause, and the second is a two-part name that must be used when referencing the object.

(2) schemabinding must be specified when creating an index on a view.

Above said so much about the use of schemabinding restrictions, can be regarded as a disadvantage, there is no merit, if there is no merit we will not speak, of course, there is no need to give the use of schemabinding. When specifying schemabinding, you can improve the query performance of UDFs and views, and when an object specifies a schema object, no unnecessary spoll operations are generated in the query plan. Let's look at the following example:

int )  int  BEGIN    2  END  

We do not provide the schemabinding option above, at which point the UDF does not access any database objects, and when a function and view does not have the schemabinding option, there is no way to ensure that the underlying database object is, so this is the time to access each executing UDF. To avoid this performance problem, we specify that schemabinding is secure and does not go through every running UDF. Therefore, it is generally recommended to specify the SCHEMABINDING option in views and UDFs.

Summarize

In this section we discuss the issue of specifying schemabinding in UDFs and views, but there are still a number of restrictions on view queries, and in most cases more flexibility is achieved with regular queries and stored procedures. Let's take a look at the use of the Apply operator in the next section, short content, deep understanding, and we'll see you next.

SQL server-focus in views and UDFs using Schemabinding (26)