Search

OVER clause enhancement request - TOP OVER by Itzik Ben-Gan

Active

155
3
Sign in
 to vote
Type: Suggestion
ID: 254390
Opened: 1/28/2007 11:47:55 AM
Access Restriction: Public
Duplicates: 329781 337299
0
Workaround(s)
SQL Server 2005 introduced only partial support for the OVER clause. It is our strong believe that a more complete implementation of the OVER clause should be prioritized highly in consideration for future enhancements in SQL Server. A more complete implementation of the OVER clause can help in solving many common business problems with simpler, more intuitive, and faster solutions than available today and also substantially reduce the need for cursors.
The following paper details the feature enhancement requests:
http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc
The current feedback item addresses the OVER clause enhancement request - TOP OVER, discussed in the paper in section 4 item iii.
Details (expand)
Product Language
English

Category

SQL Engine

Proposed Solution

Implement the OVER clause enhancement request - TOP OVER.

Benefits

Faster Development
Improved Performance

Other Benefits

Simpler, more intuitive, and faster code
File Attachments
0 attachments
Sign in to post a comment.
Posted by Chris Sherlock1 on 9/15/2010 at 6:38 AM
Sorry, where it reads "every table?", obviously that should be "every row in the table?"
Posted by Chris Sherlock1 on 9/15/2010 at 6:37 AM
Uh.... I would HATE to see what the execution plan looks like for that suggested workaround - surely that derived subquery is going to look at every table? Have not tested, but if you had a 1 million row table then I suspect performance would tank!
Posted by AKuz on 2/23/2008 at 1:26 PM
Great suggestion!
In TOP() OVER(), I would suggest using PARTITION BY clause, not only ORDER BY clause as specified in the attached document.

Posted by Microsoft on 9/11/2007 at 4:49 PM
Note: This feedback is for a series of requests for OVER clause enhancement. These items will be looked at both individually and together in future feature design.

This feature unfortunately did not fit into our schedule for SQL Server 2008. Based on customer input, we had prioritized ORDER BY for aggregates and the ROW/RANGE feature, but in the end decided that in order for this feature to be truly useful, we needed to implement more functionality than time would allow. This is a top priority for the next release, and we continue to look forward to great suggestions from you
Posted by SwePeso on 4/3/2007 at 2:10 AM
"TOP OVER" can be written as this in SQL Server 2005 ( top 3 with each group)

SELECT        [Type],
        Number,
        RecID
FROM        (
            SELECT    [Type],
                Number,
                ROW_NUMBER() OVER (PARTITION BY [TYPE] ORDER BY Number) AS RecID
            FROM    master..spt_values
        ) AS x
WHERE        RecID BETWEEN 1 AND 3
ORDER BY    [Type],
        Number
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement