SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


VIEWS 5


VIEWS 5

Author
Message
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14394 Visits: 12213
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

bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7869 Visits: 25280
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
rajeshrevelli
rajeshrevelli
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 6
Could have been little more interesting if there is one more answer on step 2
Britt Cluff
Britt Cluff
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1803 Visits: 253
Good question. Thanks for submitting.

http://brittcluff.blogspot.com/
Hardy21
Hardy21
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1588 Visits: 1399
Britt Cluff (4/8/2012)
Good question. Thanks for submitting.

+1

Thanks
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3099 Visits: 2766
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/
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11078 Visits: 12001
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
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3099 Visits: 2766
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/
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 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..

--
Dineshbabu
Desire to learn new things..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search