Sorry for the click bait but I just couldn’t resist, and to be fair I was pretty impressed with this little trick.

The other week I did a post on SCHEMABINDING and Diana sent me this great link on SCHEMABINDING UDFs. In it TomErv discribes SCHEMABINDING scalar UDFs that don’t reference any tables. It turns out that by SCHEMABINDING this type of UDF SQL is able to change a number of useful settings that can have an effect on the query plan. I’ve got a handful of links at the bottom about the actual performance benefits (even with the change in query plan). I did find a few other useful effects however.

I’m doing my testing with a function I found on a BeyondRelational forum question about the usefulness of scalar UDFs.

-- Non schema bound version
CREATE FUNCTION GetExtPrice
(
    @quantity INT,
    @rate MONEY
)
RETURNS MONEY 
AS
BEGIN
    DECLARE @price MONEY
    SELECT @price = @quantity * @rate 
    RETURN @price 
END

-- Schema bound version
CREATE FUNCTION GetExtPriceSB
(
    @quantity INT,
    @rate MONEY
)
RETURNS MONEY 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @price MONEY
    SELECT @price = @quantity * @rate 
    RETURN @price 
END

I put both of them in my Adventureworks2012 database and then ran this query to check out a handful of their object properties.

SELECT 'GetExtPrice' AS Name,
OBJECTPROPERTYEX(OBJECT_id('GetExtPrice'), 'SYSTEMDATAACCESS') SystemDataAccess,
OBJECTPROPERTYEX(OBJECT_id('GetExtPrice'), 'USERDATAACCESS') UserDataAccess,
OBJECTPROPERTYEX(OBJECT_ID('GetExtPrice'),'IsDeterministic') IsDeterministic,
OBJECTPROPERTYEX(OBJECT_id('GetExtPrice'), 'IsPrecise') IsPrecise,
OBJECTPROPERTYEX(OBJECT_id('GetExtPrice'), 'IsSystemVerified') IsSystemVerified
UNION ALL
SELECT 'GetExtPriceSB' AS Name,
OBJECTPROPERTYEX(OBJECT_id('GetExtPriceSB'), 'SYSTEMDATAACCESS') SystemDataAccess,
OBJECTPROPERTYEX(OBJECT_id('GetExtPriceSB'), 'USERDATAACCESS') UserDataAccess,
OBJECTPROPERTYEX(OBJECT_ID('GetExtPriceSB'),'IsDeterministic') IsDeterministic,
OBJECTPROPERTYEX(OBJECT_id('GetExtPriceSB'), 'IsPrecise') IsPrecise,
OBJECTPROPERTYEX(OBJECT_id('GetExtPriceSB'), 'IsSystemVerified') IsSystemVerified;

So you can see not only are the SystemDataAccess and UserDataAccess flags marked as false (as mentioned in the link above) but IsDeterministic, IsPrecise and IsSystemVerified are all marked as true. I didn’t find anything particularly useful about IsPrecise and IsSystemVerified but IsDeterministic on the other hand is required for a couple of very useful things.

Persisted computed columns

If a computed column is created using a deterministic function then it can be persisted. This causes the calculated value to be written to disk just like a normal value. It is then updated when the column is updated. Persisting a computed column takes up some extra space and takes a performance hit on inserts & updates but completely avoids the normal performance issues associated with a UDF.

-- Doesn't work
ALTER TABLE Sales.SalesOrderDetail ADD ExtPrice 
AS dbo.GetExtPrice(OrderQty, UnitPrice)  Persisted;
GO
-- Works
ALTER TABLE Sales.SalesOrderDetail ADD ExtPriceSB 
AS dbo.GetExtPriceSB(OrderQty, UnitPrice) Persisted;
GO

Indexes & Statistics

You can also put indexes and statistics on a computed column that is deterministic. Again a hit on space (although smaller) and on inserts & updates. However, it also is of course an index and can be a big performance gain depending on the usage.

ALTER TABLE Sales.SalesOrderDetail ADD ExtPrice 
AS dbo.GetExtPrice(OrderQty, UnitPrice);  
ALTER TABLE Sales.SalesOrderDetail ADD ExtPriceSB AS 
dbo.GetExtPriceSB(OrderQty, UnitPrice); 
GO
-- Doesn't work
CREATE INDEX ix_Temp ON Sales.SalesOrderDetail(ExtPrice);
GO
-- Works
CREATE INDEX ix_TempSB ON Sales.SalesOrderDetail(ExtPriceSB);
GO

This is not an exhaustive list of tasks that require determinism. Indexed views, for example, also require that any functions used be deterministic. But they also require SCHEMABINDING so it didn’t seem worth mentioning. Remember that if a scalar UDF doesn’t reference any tables or views then SCHEMABINDING it is essentially free. It also allows SQL to process the UDF more carefully and mark several important flags. All told it seems like a win-win to me.

Other related links
Connect Item
Hugo Kornelis
Atif Shehzad
Dave Wentzel

Filed under: Microsoft SQL Server, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL