Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

why should we use schemabinding with views? Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 4:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 16, 2014 1:38 AM
Points: 9, 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?
Post #1478512
Posted Monday, July 29, 2013 4:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 40,426, Visits: 36,876
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 2008, MVP
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

Post #1478520
Posted Monday, July 29, 2013 4:55 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 816, Visits: 742
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
Post #1478525
Posted Monday, July 29, 2013 5:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1478533
Posted Monday, July 29, 2013 6:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 40,426, Visits: 36,876
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 2008, MVP
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

Post #1478556
Posted Monday, July 29, 2013 1:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 7,860, Visits: 9,606
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
Post #1478726
Posted Monday, July 29, 2013 11:15 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 3, 2014 1:52 AM
Points: 720, Visits: 553
A view without any Index, but bound with schema.

Does it gives any performance difference.

Post #1478824
Posted Tuesday, July 30, 2013 1:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 816, Visits: 742
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
Post #1478851
Posted Tuesday, July 30, 2013 2:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873, Visits: 5,189
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1478880
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse