{"id":116,"date":"2015-06-24T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/sql-server-schemabinding"},"modified":"2026-03-18T21:51:52","modified_gmt":"2026-03-18T21:51:52","slug":"sql-server-schemabinding","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/06\/24\/sql-server-schemabinding\/","title":{"rendered":"SQL Server SCHEMABINDING"},"content":{"rendered":"

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<\/a>, SCHEMABINDING\u2026<\/p>\n

> Specifies that the is bound to the database objects that it references. This condition will prevent changes to the if other schema bound objects are referencing it.<\/p>\n

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.<\/p>\n

User protection<\/h2>\n

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\u00a0provides 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.<\/p>\n

Granularity<\/h2>\n

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\u00a0you can see what I am talking about.<\/p>\n

BEGIN TRAN\n\n
CREATE TABLE [dbo].[DemoTable]\n(\n\t[id] int NOT NULL,\n\t[txt] varchar(50) NULL,\n\t[txt2] varchar(50) NULL\n)\nGO<\/p>\n\n
CREATE VIEW dbo.DemoView_SCHEMABINDING\nWITH SCHEMABINDING\nAS\n\tSELECT id, txt\n\tFROM dbo.DemoTable\nGO<\/p>\n\n
ALTER TABLE dbo.DemoTable DROP COLUMN txt2\nRAISERROR ('Drop column txt2 completed.',1,1)\nWITH NOWAIT\nGO<\/p>\n\n
ALTER TABLE dbo.DemoTable DROP COLUMN txt\nGO<\/p>\n\n
ROLLBACK<\/code><\/pre>\n<\/p>\n
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.<\/p>\n
> Drop column txt2 completed.
\n>
\n>
\n> Msg 50000, Level 1, State 1
\n>
\n>
\n> Msg 5074, Level 16, State 1, Line 25
\n>
\n>
\n> The object \u2018DemoView_SCHEMABINDING\u2019 is dependent on column \u2018txt\u2019.
\n>
\n>
\n> Msg 4922, Level 16, State 9, Line 25
\n>
\n>
\n> ALTER TABLE DROP COLUMN txt failed because one or more objects access this column.<\/p>\n
Performance<\/h2>\n
The performance gain of SCHEMABINDING comes from SQL Server\u2019s need to provide Halloween Protection, or lack of need in this case. If you are interested in the details check out Craig Freedman\u2019s explanation here<\/a>.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,\u00a0you 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\u00a0Show Actual Execution Plan<\/em> on to see how this works out.<\/p>\n
CREATE TABLE [dbo].[DemoTable]\n(\n\t[id] int NOT NULL,\n\t[txt] varchar(50) NULL,\n\t[txt2] varchar(50) NULL\n)\nGO\nINSERT INTO dbo.DemoTable (id, txt, txt2)\nVALUES\n(1, '1', '2'),\n(2, '12', '24'),\n(6, '16', '32'),\n(10, '110', '220'),\n(12, '112', '224')\nGO\nGO\nCREATE FUNCTION dbo.AddFive(@n int)\nRETURNS int\nBEGIN\n  RETURN @n + 5\nEND\nGO\nCREATE FUNCTION dbo.AddFive_SCHEMABINDING(@n int)\nRETURNS int\nWITH SCHEMABINDING\nBEGIN\n  RETURN @n + 5\nEND\nGO\nUPDATE t\nSET id = dbo.AddFive(id)\nFROM dbo.DemoTable t\nGO\nUPDATE t\nSET id = dbo.AddFive_SCHEMABINDING(id)\nFROM dbo.DemoTable t\nGO<\/code><\/pre>\n
The two functions only have SCHEMABINDING as a difference but their execution plans are also different. There is an\u00a0Eager Table Spool<\/em> 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.\"\"<\/a>The\u00a0Eager Table Spool<\/em> operator is the\u00a0blocking operator<\/em> that was needed to grant Halloween Protection. This is necessary because, without SCHEMABINDING, SQL Server cannot guarantee that the UDF\u2019s definition will not change during the execution of a query or simply after the plan is compiled.\u00a0SQL Server needs to be certain\u00a0whether 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.<\/p>\n
SELECT OBJECTPROPERTYEX\n(\n\tOBJECT_id('dbo.AddFive_SCHEMABINDING')\n\t, 'SYSTEMDATAACCESS'\n) [AddFive_SCHEMABINDING SYSTEMDATAACCESS]\nSELECT OBJECTPROPERTYEX\n(\n\tOBJECT_id('dbo.AddFive_SCHEMABINDING')\n\t, 'USERDATAACCESS'\n) [AddFive_SCHEMABINDING USERDATAACCESS]\n\n
SELECT OBJECTPROPERTYEX\n(\n\tOBJECT_id('dbo.AddFive')\n\t, 'SYSTEMDATAACCESS'\n) [AddFive SYSTEMDATAACCESS]\nSELECT OBJECTPROPERTYEX\n(\n\tOBJECT_id('dbo.AddFive')\n\t, 'USERDATAACCESS'\n) [AddFive USERDATAACCESS]<\/code><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
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.<\/p>\n
\n
\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong>Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2026 > Specifies that the is bound to the database objects that it references. This condition will prevent changes to the if other schema bound objects are referencing it. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-116","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/116","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=116"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/116\/revisions"}],"predecessor-version":[{"id":378,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/116\/revisions\/378"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}