TOP 0


  • Impossible Mission - B

    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

    1. To get columns names from a UNION ALL (act as source for column names)
    2. Its a way of getting an empty set; for example to create a new empty table with the same columns as an existing one
    3. Or as a way to return column details but no data to a client layer
    4. 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.


  • Impossible Mission - B

    @djls45 Wow, those are actual legitimate reasons. Go figure.



  • @djls45 said in TOP 0:

    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's ObstacleOracle.


  • sockdevs

    @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.


  • Winner of the 2016 Presidential Election

    @djls45 said in TOP 0:

    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.


  • Winner of the 2016 Presidential Election

    @Karla said in TOP 0:

    where 1 = 0

    :eek:


  • Impossible Mission Players - A

    @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: :hanzo: I'm impressed.


  • Winner of the 2016 Presidential Election

    @pydsigner said in TOP 0:

    @Karla said in TOP 0:

    where 1 = 0

    :eek:

    I didn't say I was proud of it.


  • Winner of the 2016 Presidential Election

    @Maciejasjmj said in TOP 0:

    @djls45 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 :doing_it_wrong: If you find yourself with more than 100 rows in a table, split out some of them into a new table.

    </civilised database>


  • Discourse touched me in a no-no place

    @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.


  • Impossible Mission Players - A

    @Gąska said in TOP 0:

    @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.


  • Impossible Mission Players - A

    @Gąska said in TOP 0:

    @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.


  • Impossible Mission Players - A

    @Gąska said in TOP 0:

    @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....



  • @Gąska said in TOP 0:

    @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 :doing_it_wrong:?


  • Discourse touched me in a no-no place

    @Gąska said in TOP 0:

    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!

    @dkf said in TOP 0:

    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.


  • Discourse touched me in a no-no place

    @Gąska said in TOP 0:

    Not if there's no way to tell apart the "expected" failure from an "unexpected" one.

    :doing_it_wrong:

    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.)



  • @Gąska 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.



  • @flabdablet A query should still be able to return zero results though...



  • @flabdablet said in TOP 0:

    @Gąska 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.



  • @Gąska said in TOP 0:

    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.



  • @tufty said in TOP 0:

    @sloosecannon Nobody will ever need more than 100 rows in a table. More than 100 rows is :doing_it_wrong: 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? :trolleybus:



  • @tufty said in TOP 0:

    @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;
    


  • @djls45 said in TOP 0:

    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?




Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.