Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 VIEWS 5 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, April 4, 2012 7:43 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 7:26 AM Points: 9,830, Visits: 11,900
 Nice easy question.Like Hugo, I found it somewhat odd that only one option was provided for the result of step 2; it might have been a good i=dea to offer an error option for this step, it might have caught some people. Tom
Post #1278059
 Posted Wednesday, April 4, 2012 8:10 AM
 SSCertifiable Group: General Forum Members Last Login: Sunday, January 4, 2015 7:55 AM Points: 5,333, Visits: 25,280
Post #1278093
 Posted Friday, April 6, 2012 10:17 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, April 10, 2012 8:54 AM Points: 15, Visits: 6
 Could have been little more interesting if there is one more answer on step 2
Post #1279571
 Posted Sunday, April 8, 2012 11:21 AM
 SSCommitted Group: General Forum Members Last Login: Sunday, April 27, 2014 7:45 PM Points: 1,589, Visits: 253
 Good question. Thanks for submitting. http://brittcluff.blogspot.com/
Post #1279985
 Posted Wednesday, May 30, 2012 2:35 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, August 23, 2016 12:03 AM Points: 1,134, Visits: 1,399
 Britt Cluff (4/8/2012)Good question. Thanks for submitting.+1 Thanks
Post #1308162
 Posted Tuesday, October 9, 2012 1:25 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, November 8, 2016 4:31 AM Points: 2,222, Visits: 2,760
 why query for step 2 result only 5 rows--What will the original SELECT * FROM VOrder_Details (Step 2 above) statement return when executed? As, I think that when we made any changes in any table then changes will get effected automatically into its corresponding view. Am i right or wrong?I am confused why step 2 return 5 columns... _______________________________________________________________To get quick answer follow this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1370192
 Posted Tuesday, October 9, 2012 1:49 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 1:48 PM Points: 7,765, Visits: 11,376
 kapil190588 (10/9/2012)why query for step 2 result only 5 rows--What will the original SELECT * FROM VOrder_Details (Step 2 above) statement return when executed? As, I think that when we made any changes in any table then changes will get effected automatically into its corresponding view. Am i right or wrong?I am confused why step 2 return 5 columns...You are wrong. This is one of the (many) reasons why SELECT * in production is bad, and SELECT * in view definitions is even worse. The definition of the view will not pick up changes made to the table at a later time, unless sp_refreshview is used or the view is recreated.Here is some code to play with, just for fun. To see what is happening, I recommend executing the blocks one by one (in sequence)`-- Block 1: Set upCREATE TABLE TestTable (CharCol varchar(100) NOT NULL, IntCol int NOT NULL, DateCol date NOT NULL);INSERT INTO TestTable (CharCol, IntCol, DateCol)VALUES ('The text', 11, '2012-10-09');goCREATE VIEW TestViewAS SELECT * FROM TestTable;go-- So far, everything is okaySELECT * FROM TestView;SELECT IntCol FROM TestView;go-- Block 2: Add a column, remove another oneALTER TABLE TestTableADD NewCharCol varchar(50);goUPDATE TestTableSET NewCharCol = 'New text';goALTER TABLE TestTableDROP COLUMN CharCol;go-- Block 3: Try to query the view after this change-- Look what happened now!-- (Check column heading vs content)SELECT * FROM TestView;SELECT IntCol FROM TestView;go-- Block 4: Updates through the view now behave spooky-- (you may want to execute these statements one by one)UPDATE TestViewSET DateCol = 'I can now put text in a date column!';UPDATE TestViewSET CharCol = 'But putting text in a char column gives an error...';go-- Block 5: Remove the column I just addedALTER TABLE TestTableDROP COLUMN NewCharCol;go-- Block 6: ... interesting error message!SELECT * FROM TestView;go-- Block 7: Clean upDROP VIEW TestView;DROP TABLE TestTable;go` Hugo Kornelis, SQL Server MVPVisit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1370204
 Posted Tuesday, October 9, 2012 3:36 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, November 8, 2016 4:31 AM Points: 2,222, Visits: 2,760
 Hey Hugo,Thanks a lot for the explanation...Specially for the code which makes fun .. learn new things from that _______________________________________________________________To get quick answer follow this link:http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1370258
 Posted Wednesday, January 9, 2013 6:52 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, February 5, 2015 4:29 AM Points: 1,028, Visits: 569
 Hugo Kornelis (10/9/2012) The definition of the view will not pick up changes made to the table at a later time, unless sp_refreshview is used or the view is recreated.Hi Hugo , I xpected this part of xplaination in ur first thread itself. thanks.. --DineshbabuDesire to learn new things..
Post #1404753

 Permissions