Please Vote: Windowing Enhancements in SQL Server

Mike C — Fri, 20 Nov 2009 03:45:00 GMT

Back in January 2007, SQL guru Itzik Ben-Gan posted a series of MS Connect enhancement requests concerning windowing function enhancements. Those who have used the ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() functions on SQL 2005 and 2008, you already know how useful they are. They simplify code and can improve performance considerably over the alternatives, which usually include self-joins, temp tables and/or cursors in various combinations.

Well, the windowing functionality that you've seen in SQL 2005 and 2008 is just the tip of the iceberg. The ISO SQL standard actually defines several additional options for these functions that SQL Server doesn't yet support. These additional options allow you to do some pretty amazing calculations. The ROWS and RANGE window subclauses that the standard defines allows you to perform "sliding window" calculations; the ORDER BY clause for aggregate functions which simplifies complex running sum (and other) calculations.

As a SQL developer or DBA, these enhancements will simplify your life. But don't take my word for it - read Itzik's white paper at http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc. Then let Microsoft know you want these enhancements in SQL Server by voting on Itzik's enhancement requests at the following links:

Progressive Ordered Calculations: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254397

FIRST_VALUE, LAST_VALUE functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254395

DISTINCT clause for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393

ROWS and RANGE window subclauses: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254392

Vector expressions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391

TOP OVER: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

LAG and LEAD functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388

ORDER BY for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387

Read Itzik's article, vote on the Connect items, and spread the word!

Search results matching tags 'DBA', 'SQL 2008', 'database', 'professionals', 'functions', 'windowing functions', 'query', 'sql 2005', and 'msdn'

Please Vote: Windowing Enhancements in SQL Server