I SCHEMABOUND my scalar UDF and you won’t believe what happened next.

1

September 15, 2014 by Kenneth Fisher

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;

SchemaBoundUDF1

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

One thought on “I SCHEMABOUND my scalar UDF and you won’t believe what happened next.

  1. […] I SCHEMABOUND my scalar UDF – Kenneth Fisher (Blog) […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,653 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: