InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 01:18:43 GMT20http://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxI took the schema change part of the question to mean that the schema to which the table belonged changed, e.g. from 'dbo' to 'Person' in which case the correct answer is to drop and recreate the view. :angry:Fri, 24 Aug 2012 09:33:33 GMTLempsterhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxNice question.But referencing an Sql 2005 BoL page seems a bit odd, as SQL 2005 is out of support (even though the same SP is still there now).Fri, 20 Jul 2012 11:31:09 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspx[quote][b]GPO (7/5/2012)[/b][hr]I wonder whether all SELECT * in views are created equal. That is to say equally evil. For example is a SELECT* from a preceding CTE just as bad as select * from a base table? I would have thought that selecting from the CTE was a lesser evil because everything the * is dependent on is encapsulated within the view and therefore can't be broken by a change to the underlying objects.Not advocating anything, merely wondering;-)[/quote]SELECT * in the final select (that defines the result set of the view) is always wrong.SELECT * in subqueries or CTEs is not always bad, though I personally try to avoid it.Thu, 05 Jul 2012 13:34:28 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxI wonder whether all SELECT * in views are created equal. That is to say equally evil. For example is a SELECT* from a preceding CTE just as bad as select * from a base table? I would have thought that selecting from the CTE was a lesser evil because everything the * is dependent on is encapsulated within the view and therefore can't be broken by a change to the underlying objects.Not advocating anything, merely wondering;-)Thu, 05 Jul 2012 13:12:05 GMTGPOhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspx[quote][b]Andrew Diniz (7/3/2012)[/b][hr]On a serious note, where is a view's meta-data kept and under what circumstances might a view return inconsistent results as a result of base table schema changes?[/quote]The metadata is stored in internal tables that you cannot access directly. You can access them through several object management views, including sys.objects, and sys.views for the view itself, sys.columns for the columns defined in a view, and sys.sql_modules for the definition.Here is one of many possible ways to demonstrate inconsistent results after base table schema changes:[code="sql"]CREATE TABLE dbo.MyTab (IntCol int, CharCol char(1));INSERT INTO dbo.MyTab (IntCol, CharCol) VALUES(1, 'a');goCREATE VIEW dbo.MyViewAS SELECT * FROM MyTab;goSELECT * FROM dbo.MyView;goALTER TABLE dbo.MyTab DROP COLUMN IntCol;ALTER TABLE dbo.MyTab ADD NumCol numeric(5,3);goUPDATE dbo.MyTab SET NumCol = 1;goSELECT * FROM dbo.MyView;goDROP VIEW dbo.MyView;DROP TABLE dbo.MyTab;go[/code]Tue, 03 Jul 2012 04:10:42 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxIndeed. And depending on how you interpret "ensure the view returns the [b]correct[/b] results", you may have to alter the view. The 'correct' results may be those which have no impact downstream and therefore do not pick up changes to the base table(s) :hehe: On a serious note, where is a view's meta-data kept and under what circumstances might a view return inconsistent results as a result of base table schema changes?Tue, 03 Jul 2012 03:54:51 GMTAndrew Dinizhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspx[quote][b]Hugo Kornelis (7/3/2012)[/b][hr]It's a bit disappointing that nobody has yet commented that the "best" way to deal with the issue is to drop the view, then create a new one that does not use SELECT * but spells out the relevant columns. :-DNice question, Steve. Thanks![/quote]Would not an ALTER VIEW with a proper column list also suffice? No need to reassign permissions afterwards too.A few people's posts have implicitly said they'd do a column list in place of SELECT * :-DTue, 03 Jul 2012 02:59:09 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxIt's a bit disappointing that nobody has yet commented that the "best" way to deal with the issue is to drop the view, then create a new one that does not use SELECT * but spells out the relevant columns. :-DNice question, Steve. Thanks!Tue, 03 Jul 2012 01:12:26 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxThanks Steve for the easy one !!!!Mon, 02 Jul 2012 21:53:18 GMTbaabhuhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxA nice one to start the week. Thanks, Steve!Mon, 02 Jul 2012 16:39:10 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspx[quote][b]sknox (7/2/2012)[/b][hr][quote][b]Bob Razumich (7/2/2012)[/b][hr]For a moment, I thought the correct answer was a trick answer. "sp_refreshview"? It just seemed too cute for a Monday, but I looked it up first, anyway...[/quote]+1I figured the first answer was wrong because DROP...CREATE VIEW is not the only way to fix that problem, even without the existence of sp_refreshview. You can also use an ALTER VIEW statement. Since the third answer was patently untrue as well, whatever remained, however improbable, had to be true. That's how I deduced that there was, indeed, a stored procedure called sp_refreshview.[/quote]Awesome deduction! Much more entertaining than the method I used.Mon, 02 Jul 2012 11:54:45 GMTBob Razumichhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspx[quote][b]Bob Razumich (7/2/2012)[/b][hr]For a moment, I thought the correct answer was a trick answer. "sp_refreshview"? It just seemed too cute for a Monday, but I looked it up first, anyway...[/quote]+1I figured the first answer was wrong because DROP...CREATE VIEW is not the only way to fix that problem, even without the existence of sp_refreshview. You can also use an ALTER VIEW statement. Since the third answer was patently untrue as well, whatever remained, however improbable, had to be true. That's how I deduced that there was, indeed, a stored procedure called sp_refreshview.Mon, 02 Jul 2012 11:34:57 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspx[quote][b]steve.casey (7/2/2012)[/b][hr]Some of us have to work with versions older than SS 2005, you know! DROP...CREATE is the only one that will work with ALL versions.[/quote]That's what I thought too... of course I don't write many views.... tables yes... stored procs yes...... views.... rarely.That said, I had to find out what the heck sp_refreshview was if it existed... and it changed my answer. :)Yay +1Mon, 02 Jul 2012 10:07:39 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxEZ PZMon, 02 Jul 2012 09:36:22 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxThanks for the easy one Steve! I think I have used this command maybe once before.Mon, 02 Jul 2012 08:49:08 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxGreat easy question this morning Steve. Thanks for that pick me up on Monday. :-)Mon, 02 Jul 2012 07:12:40 GMTDana Medleyhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspx[quote][b]Bob Razumich (7/2/2012)[/b][hr]For a moment, I thought the correct answer was a trick answer. "sp_refreshview"? It just seemed too cute for a Monday, but I looked it up first, anyway. Plus, I'd be strung up if I created a view as Select * from anything. Glad I researched it. But I have to say that the command would seem to enable poor coding practice in creating a view, IMHO.[/quote]It's like Bob was reading my mind! (Except for the fact that he wrote this BEFORE I saw the question. How DO you do that Bob?)So, +1, and thanks for a great question for a Monday Steve.Mon, 02 Jul 2012 06:31:38 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxFor a moment, I thought the correct answer was a trick answer. "sp_refreshview"? It just seemed too cute for a Monday, but I looked it up first, anyway. Plus, I'd be strung up if I created a view as Select * from anything. Glad I researched it. But I have to say that the command would seem to enable poor coding practice in creating a view, IMHO.Mon, 02 Jul 2012 06:13:29 GMTBob Razumichhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxLearnt something this morning so thanks for a good start to the week.:-DMon, 02 Jul 2012 04:41:35 GMTskankerhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxI always learn something new at SQLSeverCentral!!! :-)Thanks guys!!!Mon, 02 Jul 2012 03:32:55 GMTMr Quillzhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxSome of us have to work with versions older than SS 2005, you know! DROP...CREATE is the only one that will work with ALL versions.Mon, 02 Jul 2012 03:32:16 GMTsteve.caseyhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxRead the question as "with schemabinding", grumble.Serves me right for not having a coffee yet! :doze:Mon, 02 Jul 2012 02:30:15 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxGood question - made me think for a bit (I never use SELECT * in views, of course ;-) )Mon, 02 Jul 2012 01:34:48 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxThanks for the QOTD Steve.Mon, 02 Jul 2012 01:27:46 GMTpaul s-306273http://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxEasy one for a Monday, thanks Steve!Mon, 02 Jul 2012 01:12:04 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxThank you Steve, easy start for the week :-)Sun, 01 Jul 2012 09:31:18 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1323556-32-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/T-SQL/91144/">Views and changes</A>[/B]Sun, 01 Jul 2012 09:30:48 GMTSteve Jones - SSC Editor