Speaking at PASS 2012… Exciting and Scary… As usual…

drsql — Sat, 23 Jun 2012 23:03:00 GMT

Edit: As I reread this, I felt I should clarify.. As usual refers mostly to the "Scary" part. I have a lot of stage fright that I have to work through. And it is always exciting to be picked.

I have been selected this year at the PASS Summit 2012 to do two sessions, and they are both going to be interesting.

Pre-Con: Relational Database Design Workshop - Abstract

Triggers: Born Evil or Misunderstood? - Abstract

The pre-con session entitled Relational Database Design Workshop will be (at least) the third time I will have done this pre-con session, and I am pretty excited to take it to a bit larger scale. The one big change that I am forcing this time is a limit on the lecture time. Each of the first two times I have done this session the biggest disappointment has been that we didn't get nearly enough time for the exercises. If people get tired of the exercises, I will certainly have a lot of extra material to do, but the focus will be on getting at least three hours of design time in. Some as a full group on the screen, and some broken up into groups. (Of course, we will adjust the schedule based on the size of the group and whatever they are throwing at me verbally… and physically! I will have material to pad out at least an hour if people start looking bored (or if the group members start screaming at each other…you know, like a real database design session would be like if people weren't concerned with losing their jobs.))

The triggers session is the one that I have been mulling over for years now, and it is going to be minimally interesting, and probably a lot of fun (particularly if Hugo Kornelis (@Hugo_Kornelis) and Tom LaRock (@SQLRockstar) (Tom is not a fan of triggers! http://thomaslarock.com/2009/03/sql-database-triggers/) show up to offer their opinions). Triggers are probably the most controversial of SQL Server objects, and for good reason. There are server and database settings that affect how they work, and it is not trivial to write them in a manner that doesn't harm performance. Worst yet, they can cause side effects that (if not performed correctly) really harm performance, data integrity, and the sanity of the developers who don't realize they exist. But for all of the negatives, there are some tasks that just fit the trigger to a T. I might be trying to do too much in a 1:15 session, but it would not be the first time!

So I hope to see a good number of you there, for the pre- con, and certainly for the Trigger session. Just leave the potential projectiles in your hotel room...

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 'T-SQL' and 'MVP'

Speaking at PASS 2012… Exciting and Scary… As usual…

Please Vote: Windowing Enhancements in SQL Server