Adam Machanic, Boston-based independent database consultant, writer, and speaker, shares his experiences with programming, performance tuning, and optimizing SQL Server 2000, 2005, and 2008, in conjunction with related technologies such as .NET.
On Tuesday night I was honored to present a new talk to the Cape Cod .NET Users Group that meets in Plymouth, MA. The talk is called "T-SQL Power! Learning to Harness the Under-Used OVER Clause". Following is the abstract for the talk:
First introduced in SQL Server 2005, the OVER clause is an ANSI SQL
enhancement that gives you tremendous control when dealing with
aggregations. By using the OVER clause, query writers can
simultaneously aggregate columns based on multiple groups. The feature
also enables the query engine to provide windowing mechanisms for
ranking and row numbering. Leveraging these powerful language
enhancements allows you to solve a surprisingly large number of
difficult query problems—including custom paging schemes, data
de-duplication, "top-N" problems, and complex statistical calculations.
Even better, this feature can be creatively applied to help with
performance optimization of certain tough queries. In this session, you
will learn all of these techniques and see why, after applying the OVER
clause in dozens of projects since the release of SQL Server 2005, I
consider it to be one of the most powerful T-SQL features available.
I always enjoy trying out a new talk for the first time, especially when the talk is on a topic that I really enjoy. Based on some of the feedback and reactions I got, I have a few tweaks to make, but I think the talk is on the right track. My next scheduled delivery of this talk is in November, at the SQL Server Connections conference. Should be a great time!
As promised on Tuesday night, demos are attached to this blog post. Enjoy!
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Adam Machanic
Adam Machanic is a Boston-based independent database consultant, writer, and speaker. He has been involved in dozens of SQL Server implementations for both high-availability OLTP and large-scale data warehouse applications, and has optimized data access layer performance for several data-intensive applications. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "Expert SQL Server 2005 Development" (Apress, 2007) and "Inside SQL Server 2005: Query Tuning and Optimization" (Microsoft Press, 2007). Adam regularly speaks at user groups, community events, and conferences on a variety of SQL Server and .NET-related topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and a member of the INETA North American Speakers Bureau.