July 2, 2012 at 11:34 am
Bob Razumich (7/2/2012)
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...
+1
I 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.
July 2, 2012 at 11:54 am
sknox (7/2/2012)
Bob Razumich (7/2/2012)
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...+1
I 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.
Awesome deduction! Much more entertaining than the method I used.
July 2, 2012 at 4:39 pm
A nice one to start the week. Thanks, Steve!
July 2, 2012 at 9:53 pm
Thanks Steve for the easy one !!!!
July 3, 2012 at 1:12 am
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. 😀
Nice question, Steve. Thanks!
July 3, 2012 at 2:59 am
Hugo Kornelis (7/3/2012)
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. 😀Nice question, Steve. Thanks!
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 * 😀
July 3, 2012 at 3:54 am
Indeed. And depending on how you interpret "ensure the view returns the correct 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?
July 3, 2012 at 4:10 am
Andrew Diniz (7/3/2012)
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?
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:
CREATE TABLE dbo.MyTab (IntCol int, CharCol char(1));
INSERT INTO dbo.MyTab (IntCol, CharCol) VALUES(1, 'a');
go
CREATE VIEW dbo.MyView
AS SELECT * FROM MyTab;
go
SELECT * FROM dbo.MyView;
go
ALTER TABLE dbo.MyTab DROP COLUMN IntCol;
ALTER TABLE dbo.MyTab ADD NumCol numeric(5,3);
go
UPDATE dbo.MyTab SET NumCol = 1;
go
SELECT * FROM dbo.MyView;
go
DROP VIEW dbo.MyView;
DROP TABLE dbo.MyTab;
go
July 5, 2012 at 1:12 pm
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;-)
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
July 5, 2012 at 1:34 pm
GPO (7/5/2012)
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;-)
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.
July 20, 2012 at 11:31 am
Nice 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).
Tom
August 24, 2012 at 9:33 am
I 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:
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply