SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


why should we use schemabinding with views?


why should we use schemabinding with views?

Author
Message
shad.saleem41
shad.saleem41
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 46
CREATE VIEW Sales.OrderTotalsByYear
WITH SCHEMABINDING
AS
SELECT
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);
GO

I have this view which uses schema binding clause.why do we actually need it?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87267 Visits: 45272
You don't need it, unless you're planning on creating an index on that view (creating an indexed view)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
WITH SCHEMABINDING has these effects:

1) As Gail says, it is required if you want to create an indexed over the view.

2) It prevents any of the tables and the columns in the query to from being dropped, and thus prevents the view from becoming invalid.

3) In user-defined functions WITH SCHEMABINDING is required to make the function deterministic, even if the function does not perform any database access. This can have some good effects on performance.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14304 Visits: 12197
So that if someone tries to drop or modify the underlying table (or tables) in such a way that it would affect the definition of the view their ALTER TABLE command will fail with an appropriate message. If you have people changing table schema or dropping tables without ensuring that they fix any affected view at the same time, not having SCHEMABINDING on your views is a disaster; of course some of us think that people like that being able to modify the schema is a disaster anyway. But even with good people, mistakes can be made and if any of the mistakes are ones that SCHEMABINDING will prevent you obviously gain from it.
So in general it is a good idea to have SCHEMABINDING on all views, not just indexed ones. I think this is about the first time I've disagreed with Gail about anything. She's better at most SQL Server things than me, but here she's wrong: there are very good reasons for using SCHEMABINDING even when you don't want to index the view.

And you can't index a view that doesn't have SCHEMABINDING (i.e. what people used to call a MANIFEST VIEW has to have SCHEMABINDING) so any view you want to index has to have it.

There is something that some call a downside to SCHEMABINDING of course: it forces you to have a proper disciplined approach to upgrades - but again, some of us think of that as an upside, not a downside.

You should apply it to all your UDFs as well as to all your views.

Tom

GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87267 Visits: 45272
L' Eomot Inversé (7/29/2013)
I think this is about the first time I've disagreed with Gail about anything. She's better at most SQL Server things than me, but here she's wrong: there are very good reasons for using SCHEMABINDING even when you don't want to index the view.


I never said there aren't reasons to use it. I said you don't need it, i.e. it's not required.

The problem with it on all views is that it makes changing the tables incredibly difficult, even when making changes such as setting a column's nullability that won't have any effect on the results of the view. Personally I'm a fan of proper impact analysis, proper testing and carefully regulated schema changes rather than putting SchemaBinding everywhere.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14304 Visits: 12197
GilaMonster (7/29/2013)
L' Eomot Inversé (7/29/2013)
I think this is about the first time I've disagreed with Gail about anything. She's better at most SQL Server things than me, but here she's wrong: there are very good reasons for using SCHEMABINDING even when you don't want to index the view.


I never said there aren't reasons to use it. I said you don't need it, i.e. it's not required.

Yes, so you did - point taken. However, I think most development shops do need it.

The problem with it on all views is that it makes changing the tables incredibly difficult, even when making changes such as setting a column's nullability that won't have any effect on the results of the view. Personally I'm a fan of proper impact analysis, proper testing and carefully regulated schema changes rather than putting SchemaBinding everywhere.

All one has to do is alter the views to remove schemabinding before doing the table changes and alter them back again afterwards, except in those cases where the change requires a change to the definition of the view (e.g. a column it returns has been deleted) in which case it required that change anyway without schema binding. Yes it would be a pain to do that by hand, but keeping two versions of every view definition (one with schemabinding and one without) in the form of ALTER VIEW scripts is not hard, so the only issue is whether you have some code which can run the right set of alter view scripts, that's needed only assuming that you have too many views, most of which don't use the tables that are changing, for it to make sense just to run the whole lot (apart from the ones which need real changes, of course, but they have to be treated specially whether you have schemabinding or not).

It's a trade-off between the risk of someone making a mistake and you having to recover and the pain of altering each view twice every time you change an underlying table. Having to recover from that potential make would be a much bigger pain, but in a really professional shop with lots of manual checks and testing and everything carefully regulated it may be a low enough risk that the pain of using schemabinding offsets it; then it makes more sense to leave schembinding undone than to do it. however, I believe that most work is done in shops which are nowhere near up to that standard (as demonstrated by the performance of really big contractors like EDS and their like, especially when they have to get it right while coping with requirements changes from the public servants who contracted them).

Tom

T.Ashish
T.Ashish
SSC Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 Visits: 599
A view without any Index, but bound with schema.

Does it gives any performance difference.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
T.Ashish (2013-07-29)
A view without any Index, but bound with schema.

Does it gives any performance difference.


For a view, no. But for a scalar user-defined function WITH SCHEMABINDING can be important.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Eugene Elutin
Eugene Elutin
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4982 Visits: 5478
Erland Sommarskog (7/30/2013)
T.Ashish (2013-07-29)
A view without any Index, but bound with schema.

Does it gives any performance difference.


For a view, no. But for a scalar user-defined function WITH SCHEMABINDING can be important.


As it's one of the conditions to make UDF deterministic.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search