March 29, 2012 at 7:30 am
Thanks. unfortunately i was wrong just because of hurry.
you remind me something.
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
March 29, 2012 at 9:59 am
I think it's OK to strongly discourage the use of 'SELECT *' but resorting to the introduction and whitewashing of bugs to do so is a no-go as far as I am concerned. Disallow it completely or support it properly!
I think "SELECT *" is standard SQL, but SQL Server translates the view definition as "SELECT col1, col2, ..., coln" to avoid translating "*" to all columns every time the view is referenced/queried.
"El" Jerry.
March 29, 2012 at 4:20 pm
@"El" Jerry: I understand that it might be an optimisation, but if it is then it is a very buggy one.
Besides, if the reason for this behaviour is really that a significant amount of effort is required to determine what 'SELECT *' evaluates to, then that only justifies the existence of a separate 'SELECT *' (as opposed to allowing only 'SELECT <fix column list>') and the importance of evaluating it properly if and when it is used.
March 29, 2012 at 4:23 pm
SQLRNNR (3/27/2012)
Stewart "Arturius" Campbell (3/27/2012)
Great question, Ronthanks
As to who creates views with "SELECT * FROM...":
we recently had a vendor app breaking due to schema changes to the underlying tables (an "upgrade" by the vendor), where the relevant forms use views to reflect data.
upon investigation, we found "SELECT *..." in almost all these views...
I see that kind of VIEW coding on a regular basis.
Trouble is that it would break anyway if the table schema changed and the views were no changed to match; the solution is (a) make views schema bound and (b) test the apps properly if you change the schema. Doing either will prevent the particular problem (customer gets broken app when an upgrade with a schema change happens because the app hasn't been properly tested and it was possible to change the schema to break views without fixing the views).
Eschewing the "select *" form of view definition does nothing to prevent the problem. In some cases it will make the problem more obvious. Of course making the problem more obvious is a good thing, but it is nowhere near as good as using schema binding to have the database warn the dba or developer who is creating the problem so that he doesn't create it in the first place, nr is it anywhere near as good as having proper testing as a part of decent quality control, and it is certainly not a patch on doing both.
Tom
March 30, 2012 at 5:39 am
Good Question.....
March 31, 2012 at 8:49 am
L' Eomot Inversé (3/29/2012)
the solution is (a) make views schema bound and (b) test the apps properly if you change the schema. Doing either will prevent the particular problem (customer gets broken app when an upgrade with a schema change happens because the app hasn't been properly tested and it was possible to change the schema to break views without fixing the views).
If views are important for your business by interfacing objects to users, intermediating report generation, etc. it seems like a natural thing to create them with SCHEMABINDING. Unless there are clear disadvantages of using that feature. Does anyone has opinions on this?
Thank you for the discussion.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
March 31, 2012 at 8:23 pm
@ Andre Guerreiro Neto
Yes I would agree with that, because IF the views are used for those purposes then modifying the underlying schema will probably break long chains of dependencies, from the database schema all the way to the GUI. Schemabinding will offer some resistance to such changes to ensure that you can only make them very deliberately.
April 1, 2012 at 6:53 am
@alex: indeed. In my last job, views were used everywhere but they were not schema bound. I talked them into using that option whenever new objects were created and we saw a significant reduction in bugs after schema modifications. Maybe there are cases in which SCHEMABINDING is not a good idea? 🙂
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
April 1, 2012 at 4:44 pm
@ Andre Guerreiro Neto
I am not always in line with the consensus here (as you can see from my earlier posts) but I'd say yes.
For example I might want to have a view that denormalises a part of my database, e.g. Customer.* plus lookups and perhaps some additional info (e.g. order counts) to populate a search grid. In this case I might want any new columns to show up automatically. I.e. I want my * to work properly and no schemabinding.
This cheaply adds value to the business because (in many cases) new columns show up automatically and removed coumns disappear automatically without any additional development.
April 2, 2012 at 4:05 am
Got it wrong but learnt something new today - thanks for this.
April 2, 2012 at 6:00 am
codebyo (3/31/2012)
L' Eomot Inversé (3/29/2012)
the solution is (a) make views schema bound and (b) test the apps properly if you change the schema. Doing either will prevent the particular problem (customer gets broken app when an upgrade with a schema change happens because the app hasn't been properly tested and it was possible to change the schema to break views without fixing the views).If views are important for your business by interfacing objects to users, intermediating report generation, etc. it seems like a natural thing to create them with SCHEMABINDING. Unless there are clear disadvantages of using that feature. Does anyone has opinions on this?
Thank you for the discussion.
Well, I have one very specific example:
WITH SCHEMABINDING is not a good option if you are creating customized code around a third-party, SQL-based application; when the vendor makes updates to the database, those updates will fail if they try to ALTER any table upon which your custom views were based.
Rich
April 2, 2012 at 8:39 am
Alex-668179 (4/1/2012)
@ Andre Guerreiro NetoI am not always in line with the consensus here (as you can see from my earlier posts) but I'd say yes.
For example I might want to have a view that denormalises a part of my database, e.g. Customer.* plus lookups and perhaps some additional info (e.g. order counts) to populate a search grid. In this case I might want any new columns to show up automatically. I.e. I want my * to work properly and no schemabinding.
This cheaply adds value to the business because (in many cases) new columns show up automatically and removed coumns disappear automatically without any additional development.
You will sometimes get away with it if you are lucky. There is no imaginable way you will get away with it without recompilking the view if there are removed columns. So best to use schemabinding, so that removal of one col;umn and addition of another doesn't make your view display complete nonsense.
Of course if you don't mid giving complete nonsense results ro your users, that's completely different. :hehe:
Tom
April 2, 2012 at 9:03 am
rmechaber (4/2/2012)
codebyo (3/31/2012)
L' Eomot Inversé (3/29/2012)
the solution is (a) make views schema bound and (b) test the apps properly if you change the schema. Doing either will prevent the particular problem (customer gets broken app when an upgrade with a schema change happens because the app hasn't been properly tested and it was possible to change the schema to break views without fixing the views).If views are important for your business by interfacing objects to users, intermediating report generation, etc. it seems like a natural thing to create them with SCHEMABINDING. Unless there are clear disadvantages of using that feature. Does anyone has opinions on this?
Thank you for the discussion.
Well, I have one very specific example:
WITH SCHEMABINDING is not a good option if you are creating customized code around a third-party, SQL-based application; when the vendor makes updates to the database, those updates will fail if they try to ALTER any table upon which your custom views were based.
Rich
That is of course false. Alterations to tables on which a schema bound view depends only fail if the alterations invalidate the view definition. So alterations after which your views will still work will go through OK, those updates will not fail. If the views don't have schemabinding, alterations whioch invalidate them will not fail and you will get errors (or nonsense results) at run time.
I would anyway be inclined to say that I would want the vendor to provide the updates and let me run them (on a test system first) to see if any of my views need redefining (which I would see because they were all defined with schemabinding). If they do, I can delete the offending views, apply the vendos updates, design and create replacement views, and test - and then move to the production system.
Writing those views without schemabinding and allowing the schema to be updated with no indication that the updates have blown those views out of the water just means that the first time you discover a problem is much later (and the problem may have caused more damage) that if you used schemabinding. A very false ecomomy.
Tom
April 2, 2012 at 9:22 am
L' Eomot Inversé (4/2/2012)
rmechaber (4/2/2012)
codebyo (3/31/2012)
L' Eomot Inversé (3/29/2012)
the solution is (a) make views schema bound and (b) test the apps properly if you change the schema. Doing either will prevent the particular problem (customer gets broken app when an upgrade with a schema change happens because the app hasn't been properly tested and it was possible to change the schema to break views without fixing the views).If views are important for your business by interfacing objects to users, intermediating report generation, etc. it seems like a natural thing to create them with SCHEMABINDING. Unless there are clear disadvantages of using that feature. Does anyone has opinions on this?
Thank you for the discussion.
Well, I have one very specific example:
WITH SCHEMABINDING is not a good option if you are creating customized code around a third-party, SQL-based application; when the vendor makes updates to the database, those updates will fail if they try to ALTER any table upon which your custom views were based.
Rich
That is of course false. Alterations to tables on which a schema bound view depends only fail if the alterations invalidate the view definition. So alterations after which your views will still work will go through OK, those updates will not fail. If the views don't have schemabinding, alterations whioch invalidate them will not fail and you will get errors (or nonsense results) at run time.
I would anyway be inclined to say that I would want the vendor to provide the updates and let me run them (on a test system first) to see if any of my views need redefining (which I would see because they were all defined with schemabinding). If they do, I can delete the offending views, apply the vendos updates, design and create replacement views, and test - and then move to the production system.
Writing those views without schemabinding and allowing the schema to be updated with no indication that the updates have blown those views out of the water just means that the first time you discover a problem is much later (and the problem may have caused more damage) that if you used schemabinding. A very false ecomomy.
Nope, sorry, my statement is correct. I said "updates will fail if they try to ALTER any table upon which your custom views were based" and that is true. Perhaps I should have been more specific in stating that "if the ALTER TABLE command, say, dropped columns that were included in the view", but I thought that would have been clear from my reply.
Whether you decide this is desirable or not is, of course, up to you. But, you asked for an example where WITH SCHEMABINDING might not be recommended, and I gave you one. You could certainly proceed as you suggest, and not apply database changes without reviewing/testing to see if any custom views would be invalidated. But that presumes that you will be at the company forever or that your successor will follow strict guidelines in updating the application. Also assumes that you will always have access to the T-SQL that the vendor wants to run in the future. For some of the "black box" apps out there that you might want to run queries against, I myself would view schema-bound views as a time bomb.
Again, this isn't something that is characteristic of most mainstream production environments. But it is an example of a potential pitfall of schema-bound views.
Rich
April 2, 2012 at 10:58 am
rmechaber (4/2/2012)
Nope, sorry, my statement is correct. I said "updates will fail if they try to ALTER any table upon which your custom views were based" and that is true. Perhaps I should have been more specific in stating that "if the ALTER TABLE command, say, dropped columns that were included in the view", but I thought that would have been clear from my reply.
So you are happier to have the upgrade deliver incorrect data to your users (which you may not detect until this incorrect data has prolifereated into all corners of the business and done heaven alone knows what damage), or stop your code working at all, instead of having a failure at the upgrade stage? Really?
Whether you decide this is desirable or not is, of course, up to you. But, you asked for an example where WITH SCHEMABINDING might not be recommended, and I gave you one.
Someone else asked for an example, not me, and as far as I can tell you haven't given one.
You could certainly proceed as you suggest, and not apply database changes without reviewing/testing to see if any custom views would be invalidated. But that presumes that you will be at the company forever or that your successor will follow strict guidelines in updating the application. Also assumes that you will always have access to the T-SQL that the vendor wants to run in the future. For some of the "black box" apps out there that you might want to run queries against, I myself would view schema-bound views as a time bomb.
"black box"??? But you are writing custome views against it? Either it's a black box or you can see inside it, not both, but you are asserting that it is a black box and you know enough about what's inside to write views against it, which seems to me to be a contradiction.
As for strict update guidelines, I would expect that to be a given: anything (such as an upgrade of 3rd party software) that may invalidate code on which my emloyer depends (whether by making it deliver incorrect results or by making it crash with an error message or be in any other way invalid) has to be checked before it can go into production; it may need new versions of the code on which we depend, or it may result in a decision not to apply the upgrade, or it may result in us withdrawing features that we can no longer provide. It's preferable (not essential) that the checking be automated (schemabinding is a mechanism for automatically checking some things). It doesn't matter whether I'm still in charge or it's my successor or his successor or whoever - no matter who is in charge, his or her primary objective will be to keep the company afloat and the customers happy, and allowing unchecked changes to inflict damage on the company or on its customers is incompatible with that objective.
No access to the third party SQL? So you don't know the schema, how on earth do you think you can write custom views against it and maintain them?
Of course there are cases where you can't schema bind your custom views: this happens any time you can't create those views in the database that contains the tables (and views) that the views use, because a view that references tables or views by 3-part or 4-part names can't be schema bound. If you have a grey box where you can see inside but not put things inside, you can't use schemabinding for views on that box that you write, because you can't do them using only 2-part names. That just makes doing the checking harder, because you can't use schemabinding to do it for you. If the third party wants to operate that way (and I've known it happen) you are maybe stuck with it (and maybe not: but read the license very carefully and make sure what you plan to do is allowed before messing about inside the grey box). Obviously schemabinding is appropriate only in cases where it is possible (and not going to get you sued) to have it.
Again, this isn't something that is characteristic of most mainstream production environments. But it is an example of a potential pitfall of schema-bound views.
It doesn't appear to me to be an example of that, unless you mean specifically the grey box case where the third party won't let you do mods, but in that case schemabinding is irrelevant because as noted above it isn't possible.
It's maybe an example of the pitfalls of writing custom views against a schema over which you have no control, which may in future change in an unpredictable manner, and which may take on a new form of which you have no knowledge at all. I had troubles like that with a certain third party schema once (their app had bugs that they weren't interested in fixing, so we fixed things our side of the API but that required us to understand the schema. Fortunately the third party concerned never did issue any updates to the schema while we were still using his rubbish, and even more fortunately we quite soon found better options than that third party.
Tom
Viewing 15 posts - 46 through 60 (of 69 total)
You must be logged in to reply to this topic. Login to reply