InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 04:05:52 GMT20http://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]Hugo Kornelis (3/27/2012)[/b][hr]What exactly are you confused about? This question is not at odds with the "unexpected results" quote in Books Online, as this error is just one of the possible unexpected results you can get; you only get this when the number of columns has been reduced. Other weird stuff is possible as well - see for instance this demo, that removes the "first" column of the table, then adds a new one.Finally, note that just adding columns does not affect the results from the view; if you comment out the alter table drop column in the code above, the view will keep returning correct results. But dropping the table and recreating it with the columns in a different order (or reordering the columns through the SSMS table designer - which does the exact same thing under the covers) will have similar effects as the code above.[/quote]Thanks Hugo..Thu, 10 Jan 2013 05:15:41 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]kapil190588 (10/10/2012)[/b][hr]Hi Hugo,Ya i got the answer now..So if a column is dropped from a base table which is using in view then it will throw an error..but if after deleting a column if we add another column and without refreshing the view if we do Select query then it will show 5 columns and data will be wierd.Right now?[/quote]If the view was created with SELECT *, then (and only then) the above is correct. That is because the metadata of the view stores the number of columns and their relative positions in the table, but not their names and data types. However, this should (in an ideal world) be purely academic, as nobody should ever create a view with SELECT *; you sould always enumerate the columns (which avoids all this weirdness).Wed, 10 Oct 2012 04:42:50 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxHi Hugo,Ya i got the answer now..So if a column is dropped from a base table which is using in view then it will throw an error..but if after deleting a column if we add another column and without refreshing the view if we do Select query then it will show 5 columns and data will be wierd.Right now?Wed, 10 Oct 2012 04:03:04 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]kapil190588 (10/10/2012)[/b][hr]Hi Hugo,according to another QOD i.e view 5there was condition that if a add a new column to the table (previously there were 5 columns) and didnt refresh the view then view return only 5 rows. It will return 6 column when we alter or refresh he view.You told that view do not get effect untill we refresh it or alter.so why in this question if we drop a column n thn do SELECT * FROM VOrder_Details,in this condition also 5 columns should get return as view not altered nor refereshed.I am confused with view behaviour. :w00t:can you plz explain ?[/quote]One of the examples in the sample code I posted in that topic covered this scenario. I can't find the topic now (and I don't have the time to hunt it down), but if you look for the discussion and check the code again, I'm sure you'll recognize it.In this scenario, since the view was not refreshed, it tries to fetch five columns from the base table. But since one of the five was dropped, the base table now has only four columns. So you get an error. I believe the message is something like "more column definitions then there are columns".Wed, 10 Oct 2012 03:37:12 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxHi Hugo,according to another QOD i.e view 5there was condition that if a add a new column to the table (previously there were 5 columns) and didnt refresh the view then view return only 5 rows. It will return 6 column when we alter or refresh he view.You told that view do not get effect untill we refresh it or alter.so why in this question if we drop a column n thn do SELECT * FROM VOrder_Details,in this condition also 5 columns should get return as view not altered nor refereshed.I am confused with view behaviour. :w00t:can you plz explain ?Wed, 10 Oct 2012 01:16:36 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxAnother reason to never use SELECT *Mon, 02 Apr 2012 19:01:58 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx"If you mean ..."All I mean is that if you allow "SELECT *" then you should make it always behave like "SELECT *" and not just when you are lucky.I know that optimisation can be very hard and tricky. But it is no excuse to justify breaking the functionality. And I wonder if the people who do not consider this a bug (but a "feature") would think the same way about it if SQL Server would always evaluate "SELECT *" based on the current schema definition while Oracle would break the same way as SQL Server does now... and what does the SQL standard say anyway?Mon, 02 Apr 2012 18:48:54 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]Alex-668179 (4/2/2012)[/b][hr]@Tom re " Of course if you don't mid giving complete nonsense results ro your users, that's completely different."I do mind. But the data is only incorrect because of the bug in SQL Server that does not handle SELECT * correctly inside a view. Which is the point I was trying to make all along and everybody tries to ignore. :-)Bugs should not be a means to discourage features.[/quote]I agree that bugs should never be allowed to discourage the provision of features. BUTIf you mean you would prefer "select *" when executed as part of a view to be recompiled if need be to fit the current schema definition, then I can understand your point of view, but it would be better for the view definition to be updated automatically as part of the schema change since (especially if schema changes are rare) checking at execute time is not a viable option - it costs too much.If on the other hand you mean that a schema bound view which is invalidated solely because of a * in the select list shouldn't be invalidated but should be rebuilt as part of the schema change that would otherwise have invalidated it then there's no performance penalty on use of the view I don't believe you can suggest a case where it's workable, in fact I believe that no view such that redefining * to fit the current schema version will never render the view useless no matter what the schema change is can ever exist. So I don't think the current implementation is a bug: it's the only rational way of handling schema changes for views (allowing views in the same database as the subject tables without schemabinding might be considered a design error, though; I don't think it is, but when I see a view without schemabinding I tend to suspect sloppy thinking, because it's very hard to dream up situations where leaving schemabinding out makes sense - certainly I've never managed to find a case where all the tables are in the database which contains the view).Mon, 02 Apr 2012 18:12:09 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote]Alex-668179I do mind. But the data is only incorrect because of the bug in SQL Server that does not handle SELECT * correctly inside a view. [/quote]It being a "bug" is your opinion and not an opinion shared by many others ... I could/would call it a feature, as it allows the Database Engine to allow one to select data from a view in the most rapid time possible.Mon, 02 Apr 2012 16:31:15 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx@Tom re " Of course if you don't mid giving complete nonsense results ro your users, that's completely different."I do mind. But the data is only incorrect because of the bug in SQL Server that does not handle SELECT * correctly inside a view. Which is the point I was trying to make all along and everybody tries to ignore. :-)Bugs should not be a means to discourage features.Mon, 02 Apr 2012 16:20:41 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]rmechaber (4/2/2012)[/b][hr]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. [/quote]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?[quote]Whether you decide this is [u]desirable[/u] 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. [/quote] Someone else asked for an example, not me, and as far as I can tell you haven't given one.[quote]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.[/quote] "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.[quote]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.[/quote]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.Mon, 02 Apr 2012 10:58:03 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]L' Eomot Inversé (4/2/2012)[/b][hr][quote][b]rmechaber (4/2/2012)[/b][hr][quote][b]codebyo (3/31/2012)[/b][hr][quote][b]L' Eomot Inversé (3/29/2012)[/b][hr]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). [/quote]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.[/quote]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[/quote]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.[/quote]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 [u]desirable[/u] 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.RichMon, 02 Apr 2012 09:22:10 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]rmechaber (4/2/2012)[/b][hr][quote][b]codebyo (3/31/2012)[/b][hr][quote][b]L' Eomot Inversé (3/29/2012)[/b][hr]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). [/quote]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.[/quote]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[/quote]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.Mon, 02 Apr 2012 09:03:11 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]Alex-668179 (4/1/2012)[/b][hr]@ 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.[/quote]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:Mon, 02 Apr 2012 08:39:28 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]codebyo (3/31/2012)[/b][hr][quote][b]L' Eomot Inversé (3/29/2012)[/b][hr]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). [/quote]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.[/quote]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.RichMon, 02 Apr 2012 06:00:20 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxGot it wrong but learnt something new today - thanks for this.Mon, 02 Apr 2012 04:05:01 GMTskankerhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx@ 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.Sun, 01 Apr 2012 16:44:16 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx@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? :-)Sun, 01 Apr 2012 06:53:26 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx@ Andre Guerreiro NetoYes 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.Sat, 31 Mar 2012 20:23:20 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]L' Eomot Inversé (3/29/2012)[/b][hr]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). [/quote]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.Sat, 31 Mar 2012 08:49:15 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxGood Question.....Fri, 30 Mar 2012 05:39:42 GMTRamana Reddy Phttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]SQLRNNR (3/27/2012)[/b][hr][quote][b]Stewart "Arturius" Campbell (3/27/2012)[/b][hr]Great question, RonthanksAs 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...[/quote]I see that kind of VIEW coding on a regular basis.[/quote]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.Thu, 29 Mar 2012 16:23:01 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx@"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.Thu, 29 Mar 2012 16:20:48 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote]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![/quote]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.Thu, 29 Mar 2012 09:59:40 GMTEL Jerryhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxThanks. unfortunately i was wrong just because of hurry.you remind me something.Thu, 29 Mar 2012 07:30:50 GMTDanny Oceanhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxGood question.But sad to see that this is not generally regarded as a massive bug. If and when I write 'SELECT *' then I really mean 'SELECT *' at execution time, regardless of what was there at design time. 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!Wed, 28 Mar 2012 20:02:19 GMTAlex Fekkenhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]palotaiarpad (3/28/2012)[/b][hr][quote][b]Hugo Kornelis (3/27/2012)[/b][hr][quote][b]palotaiarpad (3/27/2012)[/b][hr]I'm a bit confused.BOL: If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. [u]Otherwise, the view might produce unexpected results when it is queried.[/u][/quote]What exactly are you confused about? This question is not at odds with the "unexpected results" quote in Books Online, as this error is just one of the possible unexpected results you can get; you only get this when the number of columns has been reduced. [/quote]I'm confused maybe due to my poore english. :-D I wanted to solve the question - as usual - without running the code in SSMS. I learned in my previous FoxPro sessions, that Select * is stored as Select field1,field2,field3... I thought, in SQL Server it should also give an error.I turned myself to BOL and found the above sentence about it. For me unexpected result means, i'll have no error and the returned values are time to time different.The question is great, i'll never forget the right answer, even if i'm not using * in views.[/quote]Easy enough to check how SQL Server stores the definition:[code="sql"]CREATE TABLE ViewTest ( ID INT IDENTITY(1,1), SomeText VARCHAR(12) DEFAULT 'abc' )GOCREATE VIEW vTest As (SELECT * FROM ViewTest);GO-- Following returns: CREATE VIEW vTest As (SELECT * FROM ViewTest) sp_helptext vTest-- We even preserve the case of the view definition (we're running a CI collation here) DROP VIEW vTest; GO CREATE VIEW vTest As (SELECT * fRoM vIeWtEsT); GO-- Following returns: CREATE VIEW vTest As (SELECT * fRoM vIeWtEsT); sp_helptext vTestDROP VIEW vTest;DROP TABLE ViewTest;[/code]RichWed, 28 Mar 2012 05:57:22 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]Hugo Kornelis (3/27/2012)[/b][hr][quote][b]palotaiarpad (3/27/2012)[/b][hr]I'm a bit confused.BOL: If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. [u]Otherwise, the view might produce unexpected results when it is queried.[/u][/quote]What exactly are you confused about? This question is not at odds with the "unexpected results" quote in Books Online, as this error is just one of the possible unexpected results you can get; you only get this when the number of columns has been reduced. [/quote]I'm confused maybe due to my poore english. :-D I wanted to solve the question - as usual - without running the code in SSMS. I learned in my previous FoxPro sessions, that Select * is stored as Select field1,field2,field3... I thought, in SQL Server it should also give an error.I turned myself to BOL and found the above sentence about it. For me unexpected result means, i'll have no error and the returned values are time to time different.The question is great, i'll never forget the right answer, even if i'm not using * in views.Wed, 28 Mar 2012 00:49:10 GMTpalotaiarpadhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxNice question, thanks!Wed, 28 Mar 2012 00:32:28 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxGreat question. I remember some time ago somebody said about a best practice he had about creating views using "SELECT *", but he had documentation on every database object and he would drop and recreate all views that depended on the modified table.Personally I still prefer not to use the SELECT *."El" Jerry.Tue, 27 Mar 2012 16:35:28 GMTEL Jerryhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxThat's what I get for reading too fast and my brain read, "I deleted a row" instead of the correct "I deleted a column" statement. Oh well.JTue, 27 Mar 2012 16:13:04 GMTJ DBAhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxGood back to basics question - do not use 'select *' in a view defination! Thanks.Tue, 27 Mar 2012 14:29:34 GMTKangana Berihttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxA very interesting question -- thanks!Tue, 27 Mar 2012 13:41:16 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxThe only two cases when I use "SELECT *" are:1) Quick ad-hoc queries that will be used only once;2) On the outer query when the inner queries already defined the columns to retrieve like below:[code="sql"]SELECT TB.* FROM (SELECT NAME, OBJECT_ID FROM SYS.OBJECTS WHERE TYPE = 'P') AS TB;[/code]But I didn't know about the issue with views. Another good reason to start modifying old inherited objects from the database.Tue, 27 Mar 2012 13:28:12 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxGreat question! It was worth the time to look at the examples and understand what really happens to data results from views when changes are made to underlying objects. Thanks :)Tue, 27 Mar 2012 12:04:53 GMTBarbWhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspxNice question Ron! I have had to deal with this once or twice before. Why you would write a view that just performs a select * from a single table is beyond me. I have seen it done but never knew the point.Tue, 27 Mar 2012 10:58:47 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]cengland0 (3/27/2012)[/b][hr]You got me on this one. I didn't know this would produce an error and according to the current stats on who answered it incorrectly, I'm not the only one.[/quote]I'm honored to be in your company :-). FTR, in my limited experience, I had not seen another programmer's VIEW simply coded as a SELECT *, nor had I coded my own VIEWs that way (can someone explain what's the immediate purpose of a VIEW which only does a SELECT of one table's columns in the order they are defined?).Tue, 27 Mar 2012 10:45:44 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]Rose Bud (3/27/2012)[/b][hr][quote][b]davidandrews13 (3/27/2012)[/b]that's the reason why i thought it didn't matter if you specified SELECT *, or not - because it always expanded it out for me.[/quote]It appears to break out the * to the actual columns if you reopen the view in the graphic designer again.But if you --> Right-click, View Script As, Create To, New Query Editor Windowyou will see the * is part of the view's definition.[/quote]i see. i created another view in the Designer as SELECT * and then Scripted it as Create To and it showed all the columnsi then created another view as a CREATE View statement as SELECT *, Scripted it as Create To and it shows SELECT *.this is where i was getting mixed up.i never doubted that SELECT * was bad practice, i just thought that Management Studio changed it for you.thanksTue, 27 Mar 2012 09:59:26 GMTdavidandrews13http://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]Stewart "Arturius" Campbell (3/27/2012)[/b][hr]Great question, RonthanksAs 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...[/quote]I see that kind of VIEW coding on a regular basis.Tue, 27 Mar 2012 09:57:41 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1273140-1222-1.aspx[quote][b]davidandrews13 (3/27/2012)[/b]that's the reason why i thought it didn't matter if you specified SELECT *, or not - because it always expanded it out for me.[/quote]It appears to break out the * to the actual columns if you reopen the view in the graphic designer again.But if you --> Right-click, View Script As, Create To, New Query Editor Windowyou will see the * is part of the view's definition.Tue, 27 Mar 2012 09:49:56 GMTRose Bud