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 12345»»»

VIEWS 4 Expand / Collapse
Author
Message
Posted Monday, March 26, 2012 8:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:36 PM
Points: 5,572, Visits: 24,800
Comments posted to this topic are about the item VIEWS 4

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1273140
Posted Monday, March 26, 2012 9:28 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:19 AM
Points: 1,282, Visits: 1,114
Hi Bit bucket,

Thanks for the question. I would expect to attach any external reference to the question. Thought it is easier question for experienced person, it will help for the beginners.

Thank you.
Post #1273148
Posted Tuesday, March 27, 2012 12:31 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 550, Visits: 1,062
Thanks!
Easy one for a Tuesday....
Post #1273209
Posted Tuesday, March 27, 2012 1:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 2,451, Visits: 2,342
Easy question!
Good practice is never use "SELECT * FROM" in view, too.
Post #1273238
Posted Tuesday, March 27, 2012 2:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:22 AM
Points: 1,297, Visits: 471
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. Otherwise, the view might produce unexpected results when it is queried.
Post #1273272
Posted Tuesday, March 27, 2012 2:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 5,926, Visits: 8,175
palotaiarpad (3/27/2012)
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. Otherwise, the view might produce unexpected results when it is queried.

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.

CREATE TABLE t1
(IntCol int, CharCol varchar(20), DateCol date);
GO
INSERT INTO t1(IntCol, CharCol, DateCol)
VALUES (1, 'One', '2012-01-01'),
(2, 'Two', '2012-02-02');
GO
CREATE VIEW v1
AS SELECT * FROM t1;
GO
SELECT * FROM v1;
GO
ALTER TABLE t1
DROP COLUMN IntCol;
ALTER TABLE t1
ADD NewIntCol int;
GO
UPDATE t1
SET NewIntCol = 0;
GO
SELECT * FROM v1;
GO
DROP VIEW v1;
DROP TABLE t1;
GO

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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1273285
Posted Tuesday, March 27, 2012 3:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
Thanks Ron - good question.

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1273312
Posted Tuesday, March 27, 2012 3:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 1,625, Visits: 1,069
This just underlines the 'never use select * from... in a view' rule. I've been bitten by this parachuting in for a quick fix before.

Say you realise a need to include an extra column in a table for some purpose and do this. All seems good. Then you get a call 'such and such is not working'. What on earth? This was nothing to do with anything you have conceivably changed. Then the penny drops - a view was based on the table you have updated, tested and rolled out and included a dreaded 'select *'. This actually causes the columns to be output aliased with other column names - for instance productId might now be rebadged productDescription, and all columns shifted across.

The moral is check all view definitions when making structural changes to databases you are unfamiliar with, that have possibly been designed by those less knowledgeable.
Post #1273316
Posted Tuesday, March 27, 2012 3:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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.
Post #1273325
Posted Tuesday, March 27, 2012 4:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 4, 2014 9:03 AM
Points: 1,415, Visits: 796
Guessed, and guessed wrong.

Learnt something today.
(Although who would use select * for a view in the first place?)
Post #1273334
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse