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 «««123

VIEWS 5 Expand / Collapse
Author
Message
Posted Wednesday, April 4, 2012 7:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:59 AM
Points: 7,732, Visits: 9,475
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:17 PM
Points: 5,332, Visits: 25,261
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.

Correct answers: 79% (442)
Incorrect answers: 21% (116)
Total attempts: 558



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 #1278093
Posted Friday, April 6, 2012 10:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 7, 2014 10:22 PM
Points: 1,126, Visits: 1,387
Britt Cluff (4/8/2012)
Good question. Thanks for submitting.

+1


Thanks
Post #1308162
Posted Tuesday, October 9, 2012 1:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 1,916, Visits: 2,337
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 6,018, Visits: 8,287
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




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1370204
Posted Tuesday, October 9, 2012 3:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 1,916, Visits: 2,337
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


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:38 AM
Points: 945, Visits: 558
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..
Post #1404753
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse