SQL Server SCHEMABINDING

SCHEMABINDING is an option that is available for objects in T-SQL which contain user defined code. Examples include, stored procedures, indexes, and functions. Straight from MSDN, SCHEMABINDING…

Specifies that the schema is bound to the database objects that it references. This condition will prevent changes to the schema if other schema bound objects are referencing it.

This concept is fairly straight forward on the surface but this post is more than an overview of the SCHEMABINDING option. It will also demonstrate the level of granularity that it is using and how the query optimizer can use it to make better performance decisions.

User protection

Any type of restriction is there to protect us. SCHEMABINDING helps us safely maintain our user defined objects. Specifically, views seem to be the biggest offenders. You create a number of views and then decide to change the tables, not realizing or not remembering that your view will now be broken. SQL Server provides no prompts or warnings unless you use SCHEMABINDING, in which case it will throw an exception and not allow the change.This is easy enough to work around, if you planned ahead. All you have to do is ALTER the objects in question with the same definition, minus the WITH SCHEMABINDING clause, then make your changes and re-apply. I have seen a number of people complain about this but the only argument against it is that it requires more code to make a change when it is in place. I do not see this as a negative, personally. The protection that SCHEMABINDING provides is very useful and worth the extra code to make changes because it forces you to know every object which will be affected by your change.

Granularity

SQL Server is pretty smart. When it comes to SCHEMABINDING, SQL Server is smart enough to know exactly what parts of an object are being referenced and will allow for other parts of the object to be modified because it knows that the change will not affect the usage of the schema bound object. Below you can see what I am talking about.

BEGIN TRAN

CREATE TABLE [dbo].[DemoTable]
(
[id] int NOT NULL,
[txt] varchar(50) NULL,
[txt2] varchar(50) NULL
)
GO

CREATE VIEW dbo.DemoView_SCHEMABINDING
WITH SCHEMABINDING
AS
SELECT id, txt
FROM dbo.DemoTable
GO

ALTER TABLE dbo.DemoTable DROP COLUMN txt2
RAISERROR ('Drop column txt2 completed.',1,1)
WITH NOWAIT
GO

ALTER TABLE dbo.DemoTable DROP COLUMN txt
GO

ROLLBACK

The demonstration is showing how you can successfully drop the column txt2 because it is not referenced in the schema bound view but you cannot drop column txt. Here is the query output.

Drop column txt2 completed.

Msg 50000, Level 1, State 1

Msg 5074, Level 16, State 1, Line 25

The object ‘DemoView_SCHEMABINDING’ is dependent on column ‘txt’.

Msg 4922, Level 16, State 9, Line 25

ALTER TABLE DROP COLUMN txt failed because one or more objects access this column.

Performance

The performance gain of SCHEMABINDING comes from SQL Server’s need to provide Halloween Protection, or lack of need in this case. If you are interested in the details check out Craig Freedman’s explanation here.To summarize it, Halloween Protection is when the DBMS has to isolate the read cursor from the write cursor during a DML statement. Using an UPDATE as an example, if you were to update all records where [id] > 5, but you are also changing the [id] value, you could end up updating the same records more than once. Halloween Protection inserts a blocking operator between the read cursor and the write cursor so that the reads have to complete before the writes can begin.The presence of a blocking operator inherently slows down your query and this performance hit can be significant. If you have a user defined function which does not require data access, then Halloween Protection is not required. Run this demonstration with Show Actual Execution Plan on to see how this works out.

CREATE TABLE [dbo].[DemoTable]
(
[id] int NOT NULL,
[txt] varchar(50) NULL,
[txt2] varchar(50) NULL
)
GO
INSERT INTO dbo.DemoTable (id, txt, txt2)
VALUES
(1, '1', '2'),
(2, '12', '24'),
(6, '16', '32'),
(10, '110', '220'),
(12, '112', '224')
GO
GO
CREATE FUNCTION dbo.AddFive(@n int)
RETURNS int
BEGIN
RETURN @n + 5
END
GO
CREATE FUNCTION dbo.AddFive_SCHEMABINDING(@n int)
RETURNS int
WITH SCHEMABINDING
BEGIN
RETURN @n + 5
END
GO
UPDATE t
SET id = dbo.AddFive(id)
FROM dbo.DemoTable t
GO
UPDATE t
SET id = dbo.AddFive_SCHEMABINDING(id)
FROM dbo.DemoTable t
GO

The two functions only have SCHEMABINDING as a difference but their execution plans are also different. There is an Eager Table Spool operator in the plan that does not use SCHEMABINDING which consumes 50% of the query cost. This makes the schema bound version twice as fast as the non-schema bound version.demo_exec_plan

The Eager Table Spool operator is the blocking operator that was needed to grant Halloween Protection. This is necessary because, without SCHEMABINDING, SQL Server cannot guarantee that the UDF’s definition will not change during the execution of a query or simply after the plan is compiled. SQL Server needs to be certain whether or not this UDF will read from data that could cause an issue with non-isolated read and write cursors. When SCHEMABINDING is not present, SQL Server takes the safe bet and assumes that it will access data. This can be seen in the object properties with these queries.

SELECT OBJECTPROPERTYEX
(
OBJECT_id('dbo.AddFive_SCHEMABINDING')
, 'SYSTEMDATAACCESS'
) [AddFive_SCHEMABINDING SYSTEMDATAACCESS]
SELECT OBJECTPROPERTYEX
(
OBJECT_id('dbo.AddFive_SCHEMABINDING')
, 'USERDATAACCESS'
) [AddFive_SCHEMABINDING USERDATAACCESS]

SELECT OBJECTPROPERTYEX
(
OBJECT_id('dbo.AddFive')
, 'SYSTEMDATAACCESS'
) [AddFive SYSTEMDATAACCESS]
SELECT OBJECTPROPERTYEX
(
OBJECT_id('dbo.AddFive')
, 'USERDATAACCESS'
) [AddFive USERDATAACCESS]

demo_obj_properties

When SCHEMABINDING is used, SQL Server can evaluate the UDF and be certain that it will not access data while the execution plan is executing. Thus, the Eager Table Spool is removed and your query runs faster.

This article has 3 comments

  1. Excellent article and nice to see people showing SCHEMABINDING some proper love.

  2. […] for SQL Server Adding Extended Properties to a Table 101 Things I Wish You Knew About SQL Server SQL Server SCHEMABINDING Building Your Chef Workstation on Windows — Steven Murawski How to Fix a Corrupt MSDB SQL Server […]

  3. […] a schema bound function for performance optimization. Before we go into the detail you should check this link to know that how the query optimizer can use it to make better performance decisions. Alright, now […]

Leave a Reply