November 11, 2014 at 9:45 am
Nadrek (11/11/2014)
I copy and pasted into a text editor, verified that 1 and 4 were the same, guessed 1 was more likely to get the point, and lost with a correct answer.Can we have some proofreading of these? Or code that prevents duplicates?
Steve will fix the question and retroactively award you the 1 point (which of course you already got yourself by posting ;-)).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 11, 2014 at 10:10 am
if refer to this link:
http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf
all answers is not right
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
November 11, 2014 at 10:18 am
Hello,
I think 1 and are the same ?
Thanks
November 11, 2014 at 10:25 am
Order BY is last, Top should before it.
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
November 11, 2014 at 10:28 am
#1 and #4. Choose wisely! I chose poorly.
November 11, 2014 at 10:56 am
chgn01 (11/11/2014)
Order BY is last, Top should before it.
No that wouldn't work. How would you know what rows to retrieve when using TOP if you had not already determined the order?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 11, 2014 at 11:25 am
Sean Lange (11/11/2014)
chgn01 (11/11/2014)
Order BY is last, Top should before it.No that wouldn't work. How would you know what rows to retrieve when using TOP if you had not already determined the order?
Magic ; )
Seriously, chgn01, Sean Lange is correct. You can test this yourself with the following query:
DECLARE @test-2 TABLE (col INT);
INSERT @test-2 VALUES (4), (3), (1), (2), (5);
--returns all rows in order of insert
--technically this is not guaranteed because we don't have an ORDER BY clause
--but because of the way SQL Server works, it would be inefficient to do it
--another way with this small a table
SELECT col
FROM @test-2;
--returns 4, 3
--because we have no ORDER BY
SELECT TOP 2 col
FROM @test-2;
--now, if TOP is processed before ORDER BY, this should return 3, 4
--because it would first do the TOP, which as we saw above, returns 4, 3
--and then do the ORDER BY, which would sort it into 3, 4
--if, on the other hand, ORDER BY comes before TOP, this should return 1,2
--because it would first sort the table into (1, 2, 3, 4, 5)
--and then return the first two.
SELECT TOP 2 col
FROM @test-2
ORDER BY col;
November 11, 2014 at 12:02 pm
It just reinforces that sometimes when you’re right, your still wrong.
November 11, 2014 at 12:03 pm
points are awarded back. #1 changed to be different.
Apologies. The interface for adding answers makes it hard to compare and it seemed like they were different in looking at things. In hindsight, should have reordered answers side by side with the same beginnings near each other to catch differences.
Bug submitted to prevent dup answers.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 11, 2014 at 3:20 pm
The results is right. But for logical query processing, I think efficient way for SQL Compiler is do Top first then Order by. For a large table, do not need sort whole rows, just top rows. Right?
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
November 11, 2014 at 3:31 pm
chgn01 (11/11/2014)
The results is right. But for logical query processing, I think efficient way for SQL Compiler is do Top first then Order by. For a large table, do not need sort whole rows, just top rows. Right?
No. Without first knowing the order TOP is meaningless. How do you decide what rows are top unless they are ordered?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 11, 2014 at 5:18 pm
Sean Lange (11/11/2014)
chgn01 (11/11/2014)
The results is right. But for logical query processing, I think efficient way for SQL Compiler is do Top first then Order by. For a large table, do not need sort whole rows, just top rows. Right?No. Without first knowing the order TOP is meaningless. How do you decide what rows are top unless they are ordered?
Sean is right. If data is not sorted according to ORDER BY columns, then TOP can not be correctly applied.
But that is a discussion on physical execution order (what steps are taken in what order to get the results), which is determined by the Query Optimizer.
The logical order that this question refers to is how you defined the correctness of the results. Even if there is a clever way to get the correct results faster by delaying the sort, the correct result is still defined based on the logical processing order - i.e., you need to get the rows AS IF you had first sorted that 3TB table and then selected the first three.
November 12, 2014 at 12:45 am
And here I thought ON and JOIN were the exact same thing 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 1:23 am
PDGsDBA (11/11/2014)
It just reinforces that sometimes when you’re right, your still wrong.
Wrong, actually "you're still wrong."
But apart from that you're right! 😛
November 12, 2014 at 2:36 am
The joke is totally on me. I was looking at this all yesterday as I was sure #1 & #4 were correct as they were the same. I have come in this morning and decided to submit the #1 that I had left selected yesterday. Without refreshing the page!!!
As I have submitted an imperfect QotD in the past, I fully sympathise that errors are easy to do. I will not complain (and don't think I have done so).
It was a great refresher which confirmed that I remembered it correctly but added to by knowledge as I learnt it before CUBE/ROLLUP was part of the mix. Thanks for the question.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply