TOP 0
-
I got assigned to fix up a bug in which [frob server] was not processing frobs, which is literally the only thing it does.
After a bit of debugging, I found that the
QueryEligibleFrobs
method was returning an empty result set from the query. I wondered how this could happen, so I turned on SQL Server's profiler and ran a trace, and got back a query that looked like:SELECT TOP (@P1) FROM FROBS WHERE blah blah blah ,0,other values
Well, that would do it! I just have to wonder, why does "Top 0" not cause a query error? Is there any legitimate reason to use it?
-
@masonwheeler said in TOP 0:
Is there any legitimate reason to use it?
Well, if you feel sorry that the query server has been working so hard and you wanted to reward it by asking it something easy...
-
@masonwheeler said in TOP 0:
s there any legitimate reason to use it?
Could the parameter to TOP be the result of a query? Like...
select TOP (select bar from foo where baz = 'bat') ... from frobs ....
-
@masonwheeler
http://stackoverflow.com/questions/6033957/sql-server-select-top-0
- To get columns names from a UNION ALL (act as source for column names)
- Its a way of getting an empty set; for example to create a new empty table with the same columns as an existing one
- Or as a way to return column details but no data to a client layer
- Or as a query to check connectivity
-
@masonwheeler making It execute a trigger?
Filed under: bad ideas thread
-
Because if it didn't and you wanted to get the top N records, where N is a parameter that might be 0, you'd be rightly angry with the sql language for raising an error there.
-
@djls45 Wow, those are actual legitimate reasons. Go figure.
-
query to check connectivity
SELECT * FROM smallish_table
TOP 0 is so much typing.
-
@Maciejasjmj
SELECT 1
would be quicker
-
SELECT 1 FROM DUAL
if it'sObstacleOracle.
-
@masonwheeler said in TOP 0:
Is there any legitimate reason to use it?
yes actually. if you want to get the schema of a query result without actually doing the query.
it's bad code smell but some apps make decen t use of it.
-
Its a way of getting an empty set; for example to create a new empty table with the same columns as an existing one
I have used:
select blah blah blah from complex join where 1 = 0
Never would have occurred to me to use top 0
When you have to do it fast rather than do it right.
-
-
@masonwheeler said in TOP 0:
Well, that would do it! I just have to wonder, why does "Top 0" not cause a query error? Is there any legitimate reason to use it?
I use that as a poor-man's way to generate temp tables. Not very often mind you, but, yeah...
Also important when I want to tell SSIS what the resultset should look like for a given stored procedure without actually executing it properly.
Edit: I'm impressed.
-
-
@Maciejasjmj said in TOP 0:
query to check connectivity
SELECT * FROM smallish_table
TOP 0 is so much typing.
Until
smallish_table
becomes not so smallish
-
@sloosecannon Nobody will ever need more than 100 rows in a table. More than 100 rows is If you find yourself with more than 100 rows in a table, split out some of them into a new table.
</civilised database>
-
@tufty As a bonus, with only at most 100 rows you won't ever need to use an index when finding something, and can skip the expensive index creation step, leading to a substantial performance boost!!!!!
-
@dkf If there's one thing I'm certain of, it's that "that" team could manage to make a query on a single table containing 100 rows inefficient to the point of dragging a dedicated database server to its knees.
-
@masonwheeler said in TOP 0:
I just have to wonder, why does "Top 0" not cause a query error? Is there any legitimate reason to use it?
At my work, we have that internal API that is severely underspecified. Recently we've found out that the authors of that API think the same way as you - the hard way; test machine crashed with communication error because our clever method of finding out if Foo exists by checking if querying all Foos gives non-empty list didn't exactly work out.
-
@masonwheeler said in TOP 0:
I just have to wonder, why does "Top 0" not cause a query error? Is there any legitimate reason to use it?
At my work, we have that internal API that is severely underspecified. Recently we've found out that the authors of that API think the same way as you - the hard way; test machine crashed with communication error because our clever method of finding out if Foo exists by checking if querying all Foos gives non-empty list didn't exactly work out.
I guess they were intending you to
select count (*) from table
?
-
@Tsaukpaetra it's not SQL - it's a custom protobuf-based object-oriented protocol. When designing API, they simply didn't think of the possibility that someone would want to check if the object exists before operating on it.
-
@Tsaukpaetra it's not SQL - it's a custom protobuf-based object-oriented protocol. When designing API, they simply didn't think of the possibility that someone would want to check if the object exists before operating on it.
Yay for assumptions!
-
@Tsaukpaetra and when we came to them and told them about our use case, they kept yelling at us that WE'RE USING THEIR API WRONG!!!
...without proposing any alternative solution.
-
@Tsaukpaetra and when we came to them and told them about our use case, they kept yelling at us that WE'RE USING THEIR API WRONG!!!
...without proposing any alternative solution.
,so, what, they'd rather you assume it was there and possibly fail elsewhere (maybe even at an "it's too late to go back" stage) than to prepare and be safe?
That would be akin to a web server accepting a whole 1tb file upload and only once it's finished tell you that the limit is 2mb....
-
@Tsaukpaetra and when we came to them and told them about our use case, they kept yelling at us that WE'RE USING THEIR API WRONG!!!
...without proposing any alternative solution.
Your API was written by ?
-
When designing API, they simply didn't think of the possibility that someone would want to check if the object exists before operating on it.
Sometimes it is better to just make the request and handle the failure, since that avoids the race condition between asking if a thing exists and operating on that thing. The whole business of asking for permission as a separate step before asking to actually do the operation just invites more complex failures.
-
@Tsaukpaetra said in TOP 0:
,so, what, they'd rather you assume it was there and possibly fail elsewhere (maybe even at an "it's too late to go back" stage) than to prepare and be safe?
That's exactly what we do with all other features. And we also have assertions on what those shared objects contain!
Sometimes it is better to just make the request and handle the failure, since that avoids the race condition between asking if a thing exists and operating on that thing.
Not if there's no way to tell apart the "expected" failure from an "unexpected" one. And thanks to layers upon layers of OOPy internal IPC frameworks, it's impossible to do without rewriting half of said frameworks.
-
Not if there's no way to tell apart the "expected" failure from an "unexpected" one.
It's important to be able to know what failure mode you've got. Yes, it can break the abstract API model that people like, but shit sucks like that sometimes, and being able to work out if the failure matters, what you can do about it, and whether you should throw up you hands and run around in a panic, that's more valuable than API beauty. (Or rather, a well-designed API will give you the info you need to decide this in its failure modes.)
-
they simply didn't think of the possibility that someone would want to check if the object exists before operating on it
To be fair, that pattern smells of race conditions.
-
@flabdablet A query should still be able to return zero results though...
-
@flabdablet said in TOP 0:
they simply didn't think of the possibility that someone would want to check if the object exists before operating on it
To be fair, that pattern smells of race conditions.
It's not the update-if-exists pattern, it's the check-hardware-capabilities-before-enabling-extension-module pattern.
-
if there's no way to tell apart the "expected" failure from an "unexpected" one
then your system's error-handling design blows goats.
-
@flabdablet never claimed otherwise.
-
@sloosecannon Nobody will ever need more than 100 rows in a table. More than 100 rows is If you find yourself with more than 100 rows in a table, split out some of them into a new table.
</civilised database>
On the off chance that you're serious, what about situations where the data is a set that contains >100 elements? Should the table still be split then?
Or maybe it should be transposed to be one row with 100+ columns?
-
@dkf If there's one thing I'm certain of, it's that "that" team could manage to make a query on a single table containing 100 rows inefficient to the point of dragging a dedicated database server to its knees.
I can almost see it now
-- Validate usernames have only allowed characters SELECT c1.val, c2.val, c3.val, c4.val, c5.val, c6.val--, etc etc ... FROM USERNAME_CHARS c1, USERNAME_CHARS c2, USERNAME_CHARS c3, USERNAME_CHARS c4, USERNAME_CHARS c5, USERNAME_CHARS c6;
-
Or maybe it should be transposed to be one row with 100+ columns?
Now you're thinking post-relationally. Are you sure your name isn't atwood?
-
var Mind = 0; Top(Mind);
There's your problem....
-
This post is deleted!