VIEWS 4

  • 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[/url]
    Before posting a performance problem please read[/url]

  • 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.

  • Easy question!

    Good practice is never use "SELECT * FROM" in view, too.

    😉

  • 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.

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thanks Ron - good question.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere 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

  • 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.

  • 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.

  • Guessed, and guessed wrong.

    Learnt something today.

    (Although who would use select * for a view in the first place?)

  • This was removed by the editor as SPAM

  • Hi,

    It was a good question.

    Thenks.

  • 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

  • Good question.Thanks.

    M&M

  • 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 *!

  • 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