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. |