SQL Server 2005 introduced only partial support for the OVER clause.
It is our strong belief 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 OVER clause feature enhancement requests:
"SQL Server Feature Enhancement Request – OVER Clause and Ordered
Calculations by Itzik Ben-Gan and Sujata Mehta"
Direct link:
http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc
Link from resources/whitepapers page:
http://www.insidetsql.com/resources.htm#Whitepapers
The document is long, detailing why those enhancements are needed and
what is currently available in the product.
If you are already familiar with the problems related to ordered calculations,
and with the existing support for the OVER clause in SQL Server 2005, feel
free to jump directly to section 4, which details the OVER clause feature
enhancement requests.
For those of you who believe that it is important to enhance the OVER
clause, please cast your votes.
I submitted multiple requests via the connect.microsoft.com website, each
covering a separate feature enhancement request.
You can vote for the requests here:
https://connect.microsoft.com/SQLServer/feedback/SearchResults.aspx?SearchQuery=over+clause+enhancement
Or through the following individual links:
ORDER BY for aggregates:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387
LAG and LEAD functions:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388
TOP OVER:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390
Vector expressions for calculations based on OVER clause:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391
ROWS and RANGE window sub-clauses:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254392
DISTINCT clause for aggregate functions:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393
FIRST_VALUE, LAST_VALUE functions:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254395
Progressive ordered calculations:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254397
Thanks for your support!
--
BG
End of Article
