July 29, 2013 at 4:12 am
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?
July 29, 2013 at 4:32 am
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
July 29, 2013 at 4:55 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 29, 2013 at 5:12 am
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
July 29, 2013 at 6:43 am
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
July 29, 2013 at 1:28 pm
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
July 29, 2013 at 11:15 pm
A view without any Index, but bound with schema.
Does it gives any performance difference.
July 30, 2013 at 1:17 am
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2013 at 2:51 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply