April 4, 2012 at 4:20 am
All three answers are in fact correct.
"Returns from step 4 Returns 5 columns and 2,155 rows of data" is true - it returns 5 columns and also another column.
:Whistling:
April 4, 2012 at 4:30 am
I'm a bit surprised to see the question asking for two answers (one about step 2, one about step 4), and only one answer option for step 2. Why not simply omit that part, present two answer options for step 4, and make us choose the correct one?
(Or, better yet, provide more answer options for step 2).
Or was the question changed to remove an error before I got here?
April 4, 2012 at 6:32 am
Nice easy question, but it really does make you think about what is happening with views.
April 4, 2012 at 7:27 am
I thought "Select *" was banned from production environments for this (and other) reasons.
Are we saying it is acceptable for View definitions with the caveat that someone remembers to update the definition after a schema change? Wouldn't a schema-bound view make that update more reliable (preventing the change to schema alerts the DBA that the view exists)
It's a good question; common enough that many would/will encounter the scenario.
I am curious how we feel about using "select *" in this way.
April 4, 2012 at 7:32 am
I do not think this was saying it was acceptable, but just used to demonstrate how the view is created. That even though * was used the SQL Engine still used a full query definition and not the column wildcard (which could logically be expected).
April 4, 2012 at 7:43 am
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
April 4, 2012 at 8:10 am
L' Eomot Inversé (4/4/2012)
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.
Boldness added to the above quote by this poster.
My objective was NOT to catch some people, but hopefully to teach some people and from the looks of it, the QOD apparently has done just that.
Incorrect answers: 21% (116)
Total attempts: 558
April 6, 2012 at 10:17 am
Could have been little more interesting if there is one more answer on step 2
April 8, 2012 at 11:21 am
Good question. Thanks for submitting.
http://brittcluff.blogspot.com/
May 30, 2012 at 2:35 am
Britt Cluff (4/8/2012)
Good question. Thanks for submitting.
+1
Thanks
October 9, 2012 at 1:25 am
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/
October 9, 2012 at 1:49 am
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 up
CREATE 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');
go
CREATE VIEW TestView
AS SELECT * FROM TestTable;
go
-- So far, everything is okay
SELECT * FROM TestView;
SELECT IntCol FROM TestView;
go
-- Block 2: Add a column, remove another one
ALTER TABLE TestTable
ADD NewCharCol varchar(50);
go
UPDATE TestTable
SET NewCharCol = 'New text';
go
ALTER TABLE TestTable
DROP 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 TestView
SET DateCol = 'I can now put text in a date column!';
UPDATE TestView
SET CharCol = 'But putting text in a char column gives an error...';
go
-- Block 5: Remove the column I just added
ALTER TABLE TestTable
DROP COLUMN NewCharCol;
go
-- Block 6: ... interesting error message!
SELECT * FROM TestView;
go
-- Block 7: Clean up
DROP VIEW TestView;
DROP TABLE TestTable;
go
October 9, 2012 at 3:36 am
Hey Hugo,
Thanks a lot for the explanation...
Specially for the code which makes fun :-P.. learn new things from that 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 9, 2013 at 6:52 am
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..
--
Dineshbabu
Desire to learn new things..
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply