Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Views and changes Expand / Collapse
Author
Message
Posted Tuesday, July 3, 2012 2:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:33 AM
Points: 1,978, Visits: 3,292
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 *
Post #1324214
Posted Tuesday, July 3, 2012 3:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:11 AM
Points: 216, Visits: 273
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)

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?
Post #1324250
Posted Tuesday, July 3, 2012 4:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Post #1324260
Posted Thursday, July 5, 2012 1:12 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:35 PM
Points: 831, Visits: 1,581
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
Post #1325656
Posted Thursday, July 5, 2012 1:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Post #1325681
Posted Friday, July 20, 2012 11:31 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 8,835, Visits: 9,393
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
Post #1333129
Posted Friday, August 24, 2012 9:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 2,059, Visits: 1,430
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.
Post #1349762
Posted Monday, June 16, 2014 8:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 2,935, Visits: 239
Thanks everyone. This is good info.
Post #1581143
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse