SQL Server-聚焦在視圖和UDF中使用SCHEMABINDING（二十六）

標籤：對象   操作   限制   put   方式   bin   ssdb   table   沒有   

前言

上一節我們討論了視圖中的一些限制以及建議等，這節我們講講關於在UDF和視圖中使用SCHEMABINDING的問題，簡短的內容，深入的理解，Always to review the basics。

SCHEMABINDING

在上節中我們講到在視圖建立索引時必須指定SCHEMABINDING，所以我們有必要先去瞭解下這個知識點再繼續往下講解。SCHEMABINDING到底是什麼呢？在視圖和UDF中有這個選項，如果在視圖和UDF函數中指定了這個選項，那麼說明會將視圖和UDF嚴格綁定到資料庫物件中去，一來指定此選項可以將其嚴格綁定到資料庫物件中去，二來可以提高查詢計劃執行的效能。下面我們來看看關於SCHEMABINDING在UDF和視圖中的使用。

在UDF中的使用

建立UDF函數有三種方式，我們一一來過一遍。

（1）建立TVF內嵌資料表值函式

USE TSQL2012GOIF OBJECT_ID(‘dbo.GetOrderId‘) IS NOT NULL    DROP FUNCTION dbo.GetOrderId;GOCREATE FUNCTION dbo.GetOrderId     (@custid INT) RETURNS TABLE WITH SCHEMABINDINGAS   RETURN SELECT orderid FROM Sales.Orders WHERE custid = @custidGO  

上述UDF是通過TVF的方式來建立，當需要在裡面聲明一個臨時變數並返回時我們需要像如下操作。

（2）建立純量值函式

USE TSQL2012GOIF OBJECT_ID(‘dbo.GetOrderId‘) IS NOT NULL    DROP FUNCTION dbo.GetOrderId;GOCREATE FUNCTION dbo.GetOrderId (@custid INT)  RETURNS INT WITH SCHEMABINDINGAS BEGIN    DECLARE @tempID INT    SELECT @tempID = orderid   FROM Sales.Orders    WHERE custid = @custid;        RETURN @tempID;  END;  

當利用UDF來對查詢出來的資料進行插入到暫存資料表中時，我們可以像如下操作

（3）建立多語句TVF內嵌資料表值函式

USE TSQL2012GOCREATE FUNCTION [UDF](@PageNum int, @PageSize int)RETURNS @TestTable TABLE (RowNumber INT, ID INT, Name VARCHAR(20))ASBEGIN    declare @RowNumber int    ;WITH C    As (        SELECT ‘RowNumber‘ = ROW_NUMBER() OVER(ORDER BY id DESC),             orderid, shipname        FROM Sales.Orders        )    INSERT    @TestTable    SELECT rownumber, orderid, shipname    from C    RETURNEND

好了我們過了一遍關於UDF建立的幾種方式，我們回到主題，我們建立一個如下UDF

USE TSQL2012GOIF OBJECT_ID(‘dbo.GetId‘) IS NOT NULL    DROP FUNCTION dbo.GetId;GOCREATE FUNCTION dbo.GetId     (@id INT) RETURNS TABLE WITH SCHEMABINDINGAS   RETURN SELECT val1 FROM compare.t_inner WHERE id = @idGO

此時我們在對應資料庫中的資料表值函式檔案夾下能看到我們建立的函數

 

因為上述我們是查詢表compare.t_inner中的值，此時我們刪除該表看看。

此時我們會發現該表無法刪除出現上述錯誤。因為我們上述建立的UDF依賴於compare.t_inner表，所以現在無法刪除該表，該表引用了自訂函數GetId。下面我們修改上述我們在UDF中查詢的列val1為val3看看

在VIEW中的使用

USE TSQL2012GOIF OBJECT_ID(‘dbo.GetId‘) IS NOT NULL    DROP FUNCTION dbo.GetId;GOCREATE VIEW GetId WITH SCHEMABINDINGASSELECT val1 FROM compare.t_inner

此時刪除表compare.t_inner依然會出現和UDF中的錯誤。在使用SCHEMABINDING約束時不能進行*操作，會出現如錯誤：

USE TSQL2012GOIF OBJECT_ID(‘dbo.GetId‘) IS NOT NULL    DROP FUNCTION dbo.GetId;GOCREATE VIEW GetId WITH SCHEMABINDINGASSELECT * FROM compare.t_inner

下面再看其他情況利用視圖到跨資料庫進行查詢，我們建立兩個資料庫並分別在對應資料庫建立一個測試表。

CREATE DATABASE TEST1CREATE DATABASE TEST2GO-- Table1USE Test1GOCREATE TABLE TABLE1 (ID INT)GOUSE Test2GO-- Table2CREATE TABLE TABLE2 (ID INT)GOUSE Test1GO

接下來通過執行SCHEMABINDING來建立視圖

CREATE VIEW CrossDBViewWITH SCHEMABINDINGASSELECT t1.ID AS t1id, t2.ID AS t2idFROM Test1.dbo.Table1 t1INNER JOIN Test2.dbo.Table2 t2 ON t1.ID = t2.IDGO

上述指定SCHEMABINDING出現錯誤也就是說在跨資料庫查詢時會出現錯誤，對於引用對象僅限於兩部分名稱。到這裡我們為在視圖和UDF中使用SCHEMABINDING作出如下結論：

（1）在視圖和UDF中使用SCHEMABINDING時必須滿足兩個要求，第一個是不允許在SELECT子句中使用*，第二個則是當引用對象時必須使用架構限定的兩部分名稱。

（2）在視圖上建立索引時必須指定SCHEMABINDING。

如上講了這麼多關於SCHEMABINDING使用的限制，可以算是缺點吧，難道就沒優點了麼，如果沒優點我們也不會講了，當然也沒必要給出SCHEMABINDING的使用了。當指定SCHEMABINDING時能提高UDF和視圖的查詢效能，當對象指定架構對象時，在查詢計劃中不會產生不必要的Spoll操作。我們看如下例子：

CREATE FUNCTION dbo.ComputeNum(@i int)  RETURNS int  BEGIN    RETURN @i * 2 + 50  END  

上述我們沒有提供SCHEMABINDING選項，此時UDF不會訪問任何資料庫物件，當一個函數和視圖沒有SCHEMABINDING選項時就無法確保底層的資料庫物件是什麼，所以此時會去訪問每個正在執行的UDF，為了避免這種效能問題，我們通過指定SCHEMABINDING是安全的並且不會去遍曆訪問每一個正在啟動並執行UDF。所以在視圖和UDF中一般建議指定SCHEMABINDING選項。

總結 

本節我們討論了在UDF和視圖中指定SCHEMABINDING的問題，其實對視圖查詢還是有諸多限制，大部分情況下利用常規查詢和預存程序來實現更加靈活。我們下節看看APPLY運算子的使用，簡短的內容，深入的理解，我們下節再會。

SQL Server-聚焦在視圖和UDF中使用SCHEMABINDING（二十六）