| Author |
Topic |
|
Lesley249
Starting Member
11 Posts |
 Posted - 2007-10-02 : 02:30:58
|
| I have a problem here with the query below for example: -SELECT TOP 1 reknr FROM grtbkThe result return is different during runtime in VB application and SQL Analyzer. Let's said i execute this query in application runtime i get 1513 but when run it in the SQL Analyzer it will give 125 as a result. Why will this occur? It is i must adding the ORDER BY clause? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-02 : 02:33:48
|
Yes.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Lesley249
Starting Member
11 Posts |
Posted - 2007-10-02 : 03:10:07
|
| thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 04:20:03
|
| "Why will this occur?"SQL server will satisfy part of the request from data already in memory, if it can, and then get the remainder of the data from Disk. So, potentially, you will get a different order every time for ANY Select statement that does NOT have an Order By.In practice you very frequently get the same order, so it can be very hard to find bugs caused by absence of Order By statement, and therefore always having an Order By (on Select statements that will be display by front end with no further reordering, at least) is a good habitKristen |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-10-02 : 04:41:07
|
| I can't think of why TOP without order by is even legal syntax. Can one of the gurus enlighten me as to why this would be a useful feature? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 04:45:55
|
"Can one of the gurus enlighten me as to why this would be a useful feature?"Sure, no problem.Bug trap to enable the unwitting to pay lots of $$ in consultancy  SQL SERVER needs LINT ... </rant> |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-02 : 04:45:57
|
When you don't bother which TOP record is returned.It saves you some time in the execution plan when you are not forced to sort the complete resultset just to get one value.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-10-02 : 04:58:43
|
| Yeah, well I've always been suspicious of the old 'I don't care which one it is'. That often just means the data model is stuffed.Any realistic examples of when you would not care? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-02 : 05:18:23
|
For example if you are using correlated subqueries.SELECT Col1, Col2, (SELECT TOP 1 Green FROM Yak WHERE Apple = Col3),FROM Table1 If the Customer has GREEN at all in the Yak table, ir returns any of the green values.If no greens are found, NULL is returned instead.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-02 : 05:21:51
|
[code]SELECT Col1, Col2, (SELECT TOP 1 1 FROM Yak WHERE Apple = Col3 AND Green IS NOT NULL) AS HasGreen,FROM Table1[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-02 : 07:51:48
|
Any benefit / preference to doing this instead (for that scenario):CASE WHEN EXISTS (SELECT * FROM FROM Yak WHERE Apple = Col3 AND Green IS NOT NULL) THEN 1 ELSE NULL END AS HasGreen I suppose either can stop as soon as they have one record ...Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-02 : 08:11:00
|
quote:
Originally posted by Kristen
CASE WHEN EXISTS (SELECT * FROM FROM Yak WHERE Apple = Col3 AND Green IS NOT NULL) THEN 1 ELSE NULL END AS HasGreen
Can't see any difference in SQL Profiler.However there is a little difference in execution plans, but both suggestions takes 50% each.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-10-02 : 19:06:49
|
| Kristen - I know someone was going to come up with that one. Thanks for answering for me. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-02 : 19:25:18
|
| I use select top without an order by in production so that I can quickly get familiar with the table design and data. Yes there are other ways to do this, but when I'm in a hurry and don't want millions of rows returned to the client, select top without an order by is very fast and efficient.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-02 : 19:38:47
|
quote:
Originally posted by tkizer
I use select top without an order by in production so that I can quickly get familiar with the table design and data. Yes there are other ways to do this, but when I'm in a hurry and don't want millions of rows returned to the client, select top without an order by is very fast and efficient.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
Thats the best use of TOP without an ORDER BY I can think of too. And I've used it millions of times for the same reasons as you.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Lesley249
Starting Member
11 Posts |
Posted - 2007-10-02 : 22:03:14
|
| Thanks for the sharings :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-03 : 01:02:45
|
| "I use select top without an order by in production so that I can quickly get familiar with the table design and data."Me too, now you come to mention it.Lots of our diagnostic "snippets" have SELECT TOP 10 ... to stop them going wayward if there is more [matching] data than expected.Kristen |
|
|
|
|
Problem with SELECT TOP 1