December 26, 2012 at 9:14 am
Ugh, spent way too long trying to figure out which one of 1, 4, 5 wasn't reliable before simply guessing to see the explaination. Glad to know I wasn't missing something obvious.
December 26, 2012 at 11:30 am
Hugo, Great question that realy made me think.
However part of your explination seems incomplete or missleading.
If statements and other control flow language create new batches, and that is why they can not be used in the definition of a view. Anything that creates more than one batch can not be used in the definition of a view.
December 26, 2012 at 11:33 am
Post was removed cause poster did not understand the difference of values returned by NULLIF and ISNULL.
December 26, 2012 at 4:03 pm
SanDroid (12/26/2012)
Hugo, Great question that realy made me think.However part of your explination seems incomplete or missleading.
If statements and other control flow language create new batches, and that is why they can not be used in the definition of a view. Anything that creates more than one batch can not be used in the definition of a view.
Hi SanDroid,
Thank you for your kind words! (and thank you to other people who wrote kind words as well).
I don't really understand the point you raise about the incomplete explanation. What you say is right - you cannot use control flow in a view definition; in fact, a view definition has to be a single SELECT. But how does that relate to the question and explanation? Can you clarify (maybe with some sample code to illustrate the issue)?
December 26, 2012 at 11:40 pm
Checked 1 and 4 and then stopped thinking because I only had to select two 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 27, 2012 at 7:03 am
Hugo Kornelis (12/26/2012)
SanDroid (12/26/2012)
Hugo, Great question that realy made me think.However part of your explination seems incomplete or missleading.
If statements and other control flow language create new batches, and that is why they can not be used in the definition of a view. Anything that creates more than one batch can not be used in the definition of a view.
Hi SanDroid,
Thank you for your kind words! (and thank you to other people who wrote kind words as well).
I don't really understand the point you raise about the incomplete explanation. What you say is right - you cannot use control flow in a view definition; in fact, a view definition has to be a single SELECT. But how does that relate to the question and explanation? Can you clarify (maybe with some sample code to illustrate the issue)?
:ermm: You do not know the difference between creating a new TSQL batch and a control flow function? 😛 With all the ";" followed by a "go" in your example code I guess that is no suprise. Here is some infomration online you can read...
Also your red colored quote above is totaly wrong. More than one select can be in any View, but only one batch. Please tell me you do not need a code example to know the differnce between one select statement and one TSQL batch... :hehe:
Don't bait me... I'm no fish... :
December 27, 2012 at 8:22 am
SanDroid (12/27/2012)
:ermm: You do not know the difference between creating a new TSQL batch and a control flow function? 😛
I don't even know what a control flow function is. All control flow language elements I know are statements: BEGIN...END, BREAK, CONTINUE, GOTO, IF...ELSE, RETURN, THROW, TRY...CATCH, WAITFOR, and WHILE. (See http://msdn.microsoft.com/en-us/library/ms174290.aspx).
If you meant to write controol flow statements, then the answer is yes, obviously I do know the difference between creating a new batch and a control flow statement. I just don't see how that relates to this question of the day.
With all the ";" followed by a "go" in your example code I guess that is no suprise.
I fail to see the relation between properly terminating statements and control-flow language. And you make it sound as if a semicolon before the batch seperator is a bad thing. It is not.
For the record, the semicolon is the statement terminator in T-SQL. Using it has always been allowed, but used to be optional. As of SQL Server 2005 (if I recall correctly), not terminating statements has been put on the deprecated list, meanning it is still supported now, but will not be supported in a future release. It is already to properly terminate every statement that precedes a CTE or a Service Broker query (anything that starts with WITH), and it is also already mandatory to terminate MERGE statements.
Since 2005, I have forced myself to write all new code in the advised form - with semicolons terminating all statements.
Sources: Transact-SQL Syntax Conventions and Deprecated Database Engine Features in SQL Server 2012.
Here is some infomration online you can read...
Thanks for the link. It didn't learn me anything I didn't already know, but it might be useful for other readers of this discussion.
For the record, I always prefer to post links to Books Online or other official Microsoft documentation. If I can't find any of that, my second choice is a blog from one of the people who work on the SQL Server team. The third choice is then a blog, where I still try to limit myself to the most authorative SQL Server authors - people like Kalen Delaney, Paul Randal, Kimberly Tripp, Paul White, and a few others.
Also your red colored quote above is totaly wrong. More than one select can be in any View, but only one batch.
You're right, I should have said: "a view definition has to be a single query". ("query" instead of "select"). Using subqueries, that single query can contain multiple occurrences of the keyword select.
Please tell me you do not need a code example to know the differnce between one select statement and one TSQL batch... :hehe:
No, I don't. I do still need either code examples or a much better explanation to understand why you think that part of the explanation of the question "seems incomplete or missleading". So far, you only managed to confuse me more.
December 28, 2012 at 4:52 am
I really like the question - you had to carefully go through each example to get the right answers - shame about the 'choose 2' cock-up but it did make me check my reasoning several times :-). Learnt something new about dividing by NULL, and NULLIF.
January 2, 2013 at 1:56 am
DugyC (12/24/2012)
Merde! If only I'd waited a bit longer... however Steve might be on hols with the family already.....
Having determined 1,4 & 5 were correct in SQL2008, thought maybe this question was over all versions of SQL. So hooked up to a SQL2000 box and tried that, which only allowed 4 & 5. Hence my answers.
....
Wishing both Hugo and Tom, and all other SQL bods out there, a very Merry Christmas and a memorable New Year... hic! :w00t: :hehe:
+1
Doesn't help that we haven't got an install of 2012 here either so any questions on 2012 have to be educated guesses for me anyway
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There 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
January 2, 2013 at 7:29 am
The "official" answer given is not accurate. Views 3 and 6 are not created due to syntax errors and from the rest 1, 4 and 5 don't return errors if the Divider is 0. I gave 1 and 5 as a result but the correct was given for 1 and 4. The question was to indicate 2 views that don't cause error and not the first 2 views that don't cause errors. Here is the code:
create table MyTable(KeyColumn int, Dividend int, Divisor int)
go
insert into MyTable values(1,2,0)
-- View 1
CREATE VIEW dbo.View1
AS
SELECT KeyColumn, Dividend, Divisor,
CASE WHEN Divisor <> 0 THEN Dividend / Divisor END AS Division
FROM dbo.MyTable;
go
-- View 2
CREATE VIEW dbo.View2
AS
SELECT KeyColumn, Dividend, Divisor,
NULLIF(Dividend / Divisor, 0) AS Division
FROM dbo.MyTable;
go
-- View 3
CREATE VIEW dbo.View3
AS
SELECT KeyColumn, Dividend, Divisor,
IF (Divisor <> 0) THEN Dividend / Divisor AS Divisor
FROM dbo.MyTable;
go
-- View 4
CREATE VIEW dbo.View4
AS
SELECT KeyColumn, Dividend, Divisor,
Dividend / CASE WHEN Divisor <> 0 THEN Divisor END AS Division
FROM dbo.MyTable;
go
-- View 5
CREATE VIEW dbo.View5
AS
SELECT KeyColumn, Dividend, Divisor,
Dividend / NULLIF(Divisor, 0) AS Division
FROM dbo.MyTable;
go
-- View 6
CREATE VIEW dbo.View6
AS
SELECT KeyColumn, Dividend, Divisor,
Dividend / IF (Divisor <> 0) THEN Divisor AS Divisor
FROM dbo.MyTable;
go
select '1' vw,* from dbo.View1
go
select '2' vw,* from dbo.View2
go
select '4' vw,* from dbo.View4
go
select '5' vw,* from dbo.View5
go
Results:
vw KeyColumn Dividend Divisor Division
---- ----------- ----------- ----------- -----------
1 1 2 0 NULL
(1 row(s) affected)
vw KeyColumn Dividend Divisor Division
---- ----------- ----------- ----------- -----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
vw KeyColumn Dividend Divisor Division
---- ----------- ----------- ----------- -----------
4 1 2 0 NULL
(1 row(s) affected)
vw KeyColumn Dividend Divisor Division
---- ----------- ----------- ----------- -----------
5 1 2 0 NULL
(1 row(s) affected)
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
January 2, 2013 at 7:59 am
Hugo Kornelis (12/23/2012)
I have to offer my apologies for this question. I don't know what went wrong. I always check my question, answers and explanation several times before submitting, but I don't keep screenshots - so I cannot check if I really did mess this one up, or if something changed the data I submitted.The question should of course have read "check THREE answers". And the correct answer options should have been 1, 4, and 5, as explained in the explanation. I'll contact Steve and ask him to correct this as soon as possible.
woah, that's a relief... Hopefully steve comes back soon. I was able to deduce in my head that 1,4,5 would work, and then was a bit shocked when I was wrong 🙂
All is good Hugo... sometimes the keyboard hates us is all.
January 8, 2013 at 12:04 pm
I answered 1,4,5 on this one.
It was a good refresher experience. Thanks for the question!
Andre
January 10, 2013 at 3:12 am
Hugo Kornelis (12/23/2012)
While hunting for this information, I did find a few Connect items about errors with WHEN (something that's never true) THEN MIN(1/0) or WHEN (something that's always true) THEN 1 ELSE MIN(1/0) - they are the connect items that prompted this clarification to be added to Books Online.
I think this more a theoretical problem though.
SQL Server seems short-cirquit constant expressions (1/0) first, but usually you wont have such an expression in your queries (unless generated by code).
Example: The version with 1/0 returns the error, but the semantically identical version with 1/(x-x) does not return the error:
SELECT CASE WHEN object_id = object_id+1 THEN MIN(1/0) ELSE 1 END
FROM sys.objects GROUP BY object_id
-----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
SELECT CASE WHEN object_id = object_id+1 THEN MIN(1/(object_id-object_id)) ELSE 1 END
FROM sys.objects GROUP BY object_id
...
(1456 row(s) affected)
And we can even short-cirquit version 1 once more to make it work again:
SELECT CASE WHEN 1<>1 THEN MIN(1/0) ELSE 1 END FROM sys.objects GROUP BY object_id
...
(1456 row(s) affected)
SQL Server knows that 1<>1 is never true, and therefore doesn't bother to evaluate MIN(1/0)
Best Regards,
Chris Büttner
January 15, 2013 at 5:36 am
I also answered 1, 4 and 5. Thanks for all the explanations.
January 15, 2013 at 1:19 pm
Good One.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply