March 26, 2012 at 9:28 pm
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.
March 27, 2012 at 1:24 am
Easy question!
Good practice is never use "SELECT * FROM" in view, too.
😉
March 27, 2012 at 2:16 am
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.
March 27, 2012 at 2:41 am
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.
March 27, 2012 at 3:22 am
Thanks Ron - good question.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]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
March 27, 2012 at 3:26 am
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.
March 27, 2012 at 3:41 am
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.
March 27, 2012 at 4:01 am
Guessed, and guessed wrong.
Learnt something today.
(Although who would use select * for a view in the first place?)
March 27, 2012 at 5:15 am
This was removed by the editor as SPAM
March 27, 2012 at 5:55 am
Hi,
It was a good question.
Thenks.
March 27, 2012 at 5:56 am
I had a hard time finding documentation on this the first time I ran across it, though I'm sure someone will post a relevant link from BOL. What I did find was this helpful note about Sybase, and I guess not much has changed in this regard since the Sybase days:
"However, if you alter the structure of a view's underlying table by adding columns,
the new columns will not appear in a view that is defined with a select * clause unless the view is dropped and redefined.
This is because the asterisk in the original view definition considers only the original columns. "
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/28420;pt=28336
Thanks for the question,
Rich
March 27, 2012 at 7:36 am
Good question.Thanks.
M&M
March 27, 2012 at 7:41 am
Luckily I re-read and checked this a few times. First pass reading I thought it said deleted a row! Couldn't understand what that would really be checking!
As a number of posts say, never use Select *!
March 27, 2012 at 7:52 am
Nice back to basics question. It seems from the discussion that this is still not as widely known as I would expect. For the first time in quite a long time I knew what the answer was before I even finished the first sentence. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 69 total)
You must be logged in to reply to this topic. Login to reply