The Value Of Schemabinding

Kevin Feasel

2018-02-15

T-SQL

Vitaly Bruk explains what schemabinding is and why we sometimes need WITH SCHEMABINDING in our code:

In SQL Server, when we use the “WITH SCHEMABINDING” clause in the definition of an object (view or function), we bind the object to the schema of all the underlying tables and views. This means that the underlying tables and views cannot be modified in a way that would affect the definition of the schema-bound object. It also means that the underlying objects cannot be dropped. We can still modify those tables or views, as longs as we don’t affect the definition of the schema-bound object (view or function).

If we reference a view or function in a schema-bound object (view or function), then the underlying view or function must also be schema-bound. And we cannot change the collation of a database, if it contains schema-bound objects.

I’ve only used schemabinding when mandated (e.g., using row-level security or creating an indexed view), but I can see the value behind using it with normal development.

Related Posts

Using Calendar Tables

I have a post up on using calendar tables: There’s one problem with picking a SQL Saturday in April: Easter and Passover tend to run right around that time, and nobody wants a SQL Saturday on Passover or the day before Easter. Unfortunately, our calendar table doesn’t include holiday information. So let’s add it! Working […]

Read More

A Rant About ORMs

Ned Otter is not a fan of ORMs: I’ve seen a lot of tech come and go in my time, but nothing I’ve seen vexes me more than “framework generated SQL”.  No doubt I’m ignorant about some aspects of it, but its usage continues to confound many a DBA. To troubleshoot one of these bad […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728