SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Views and changes


Views and changes

Author
Message
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4048 Visits: 5843
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. :-D

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 * :-D
Andrew Diniz
Andrew Diniz
Old Hand
Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)Old Hand (360 reputation)

Group: General Forum Members
Points: 360 Visits: 293
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?
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11142 Visits: 12004
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




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
GPO
GPO
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1317 Visits: 1929
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 one's work is terribly important.
Bertrand Russell

Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11142 Visits: 12004
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.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14473 Visits: 12233
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

Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3231 Visits: 1657
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search