Schema Binding

There is an option available with UDFs, which is similar to that for views, for binding the columns used in any SELECT statement creating a TABLE data type to the underlying table. Schema binding will stop a table, a referenced column within a function, or a view from being dropped or altered from the underlying table and therefore keep our existing functions working.

Note that generally any good DBA will check existing dependencies by running sp_depends before dropping a column. However, by making our function schema-bound we can add in a layer of extra safety.

Note

The system function sp_depends lists all dependencies for a given object. It allows you to find out what things might break if you alter an object.

To make a function schema-bound we need to use the WITH SCHEMABINDING statement after the RETURNS statement. Apart from this, we have to meet a few other requirements:

• All objects must be from the same database

• All objects must be referenced by two part names (<ownerName>.<objectName>)

• User must have REFERENCE permissions to the tables, views, and user-defined functions used in code

• We cannot use the SELECT * syntax for returning values, even in an EXISTS expression

• All objects employed are schema-bound

If we look at our fn_CustUnShippedOrders function again, we can make this schema-bound by simply adding the WITH SCHEMABINDING statement, as we have already fulfilled all the other criteria:

     ALTER FUNCTION fn_CustUnShippedOrders (@CustId NCHAR(5))     RETURNS @UnShipped TABLE (CustomerId NCHAR(5),                               OrderDate DATETIME NULL,                               RequiredDate DATETIME NULL,                               Shipper NVARCHAR(40),                               [Order Value] MONEY                              )     WITH SCHEMABINDING     AS     ... -- rest of the code is similar to the previous listing 

Another important point concerning schema-bound functions is that the function itself would have to be deterministic to be able to apply schema binding.

Note

Remember from earlier in the chapter that for a function to be deterministic, it must be schema-bound; however, a schema-bound function need not be deterministic.