Search results matching tags 'sql' and 'mvp'

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!

"SQL Server MVP Deep Dives" Book Available for Early Access/Pre-Orders

Mike C — Thu, 17 Sep 2009 00:21:00 GMT

The MVP authored book, SQL Server MVP Deep Dives is now available for early access and pre-orders at http://manning.com/nielsen/. The book is divided into 5 sections that cover everything from building a proper relational database to developing BI solutions:

1. Database Design and Architecture
2. Database Development
3. Database Administration
4. Performance Tuning and Optimization
5. BI Development

This book covers a lot of ground with a lot of expert tour guides. Weighing in at 850 pages, it was written, tech-reviewed and edited by dozens of Microsoft MVPs with intimate knowledge of SQL Server. The contributor list includes some of the biggest names in SQL Server: Paul Nielsen, Adam Machanic, Kalen Delaney, Itzik Ben-Gan, Aaron Bertrand, Hugo Kornelis, John Paul Cook, and several others. For me getting a copy of this book will be like getting the opportunity to pick the brains of some of the most knowledgable folks in the industry on a wide variety of topics.

This is already one of my favorite books that I've had the chance to contribute to (I wrote a couple of chapters discussing on SQL Server XML in general and SQL Server XQuery specifically) because all of the royalties go to a charity that helps children who are war victims!

So go get the book, learn what the pros know, and do your good deed for the day -- all at the same time!

You can get more info on SQL Server MVP Deep Dives at Manning Publications: http://manning.com/nielsen/ and at Amazon http://www.amazon.com/SQL-Server-MVP-Deep-Dives/dp/1935182048/.

Rodney Landrum's Free e-Book: "SQL Server Tacklebox"

Mike C — Wed, 19 Aug 2009 02:07:00 GMT

Red Gate just published a new free e-book in their High Performance SQL Server series. The book is SQL Server Tacklebox: Essential Tools and Scripts for the Day to Day DBA by Rodney Landrum. Rodney is a professional DBA by trade, an author by choice, and a SQL Server MVP. With the SQL Server Tacklebox Rodney demonstrates dozens of commonplace DBA tasks, approaching each from different perspectives. Whether it's walking the reader through all the steps to properly install a SQL Server instance or demonstrating no less than three different data migration methods, Rodney presents an engaging and thoughtful discussion as he lays out the options.

Now I'm not a DBA by trade--I'm a developer. Where the DBA holds the keys to the kingdom, I generally get to play the barbarian at the gate. I still get stuck playing the "ad hoc DBA" on occasion though, since most business people don't appreciate the difference in skill sets. This book is perfect for ad hoc DBAs like me, since you can easily look up a wide variety of DBA-centric tasks by title and get (1) a thorough explanation of all the things you need to consider before you perform the task, (2) step-by-step directions on how to perform the task, and (3) a detailed description of what you should expect once the task is complete. As a developer, and a guy who's really big on instant SQL gratification, I really appreciate Rodney's free source code (separate download, link is inside the book).

For full-time DBAs, Rodney's book works great as a quick reference for routine day-to-day tasks. Rodney doesn't stop there, however. He also discusses the more advanced topics like setting up notifications, investigating performance problems, and addressing data corruption issues. In order to get the book you have to visit Red Gate's website at http://www.red-gate.com/products/sql_response/offers/response_tacklebox.htm. Download you copy now!