It’s September. I think that means that autumn is upon us. It also means that it’s time for me – and my coworkers – to work on our Professional Development Plans. I’m not going to lie, I love the idea behind doing a PDP (I’m already sick of typing Professional Development Plan). You should probably think of the phrase Personal Development Plan instead of Professional Development Plan whenever you see PDP.

SQL SERVER Optimizing Queries That Access Correlated datetime ColumnsDATE_CORRELATION_OPTIMIZATION is some good stuff. If you have an InvoiceDate column that happens to be close to the OrderDate column in a related table, and this setting is on, the compiler will generate what could be a much better optimization plan. Difference between an index and a primary key Denny Cherry does a great job of explaining this one. I frequently misuse primary key and clustered index as the same in conversation (yes, I do have A LOT of conversations about databases, even outside of work).

A member of my user group recently asked me about the best practices for monitoring and automating database maintenance. AUTOMATE EVERYTHING If I do something once, I automate it. I learned this valuable lesson at last year’s PASS Summit during a panel discussion. Any time I find an error situation, I try to determine which conditions might have caused that situation and I create a repeatable script that I can put on every server to check for a problem and email me.

That’s right, free and/or cheap stuff. Right now you’re probably saying to yourself “ZOMG! He’s got free stuff!” FREE STUFF! So, what do I know about that’s free? Data Cleansing with SSIS – Eric Veerman is going to be talking all about data cleansing. You know, getting all of the crud and nastiness out of your incoming data. You know, the data where people have extra spaces in their name because your CRM system treats that as a different person and sales people can steal commissions if it’s a “new” customer.

The Problem A reader ran into a problem on an old post about Automating T-SQL Testing. Thankfully, rather than decide that I’m some useless crank on the internet, this reader took the time to write in to me and tell me about the problem. But, he went one step further: he documented step by step his problem! The Process I fired up SSMS and verified his problem (using the thoughtfully provided steps to duplicate).

The PASS Summit is coming up in a few months and I bet you’re worried about attending. It costs money; I totally understand that. But there are a lot of ways to go for cheap. Be a speaker – oh, that deadline has come and gone Be a chapter leader – hrmm… not too many of those, let’s try another one SPONSORS! – You didn’t think you could get a sponsor for a conference?

You know what, there’s no magic here. However, it’s kinda magic to me since I didn’t write it and reading other people’s code is something that is almost like magic to me. And, I have to admit that I did not write this code. One of my co-workers, Brent Morrow, came up with this solution. Sadly, Brent does not have a blog. Luckily, I do. So now I will share with you Brent’s solution for keeping track of objects to move from one platform to another.

- OR - A BRIEF FORAY INTO THE REALM OF IDENTITY COLUMNS We all know, or should know, when we insert into a table using using SET IDENTITY_INSERT, that the identity value will be reset to the next available value in the sequence. Meaning that the identity value will either be the next value that it was supposed to be (if we filled a gap in the sequence) or the next value larger than the row we’ve just inserted (so if we inserted 100, the next identity will be 101).

SQL SERVER Commenting Your Code Do it, people. I don’t comment enough of my code, but it’s something I keep in the back of my mind all the time. Fast Running Totals Solution With Ordered CTE I love Mladen’s solution to this particular problem. I usually would use a nasty self join or some kind of hideous cross join to accomplish this. Teaser: Left Join..the SQL ego crusher Mmmmm SQL brain teaser.

Yesterday I encountered a fun little problem where I had to round to the nearest 0.5. Admittedly, I solved the problem in C#, but the solution is the same no matter what language you are in. So, here’s our starting data set:``` IF OBJECT_ID(’tempdb..#ratings’) IS NOT NULL DROP TABLE #ratings ; CREATE TABLE #ratings ( rating INT ) ; INSERT INTO #ratings SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 2 UNION ALL SELECT 7 UNION ALL SELECT 9 UNION ALL SELECT 8 UNION ALL SELECT 3 ; And, of course, the average is really easy to get as well: SELECT AVG(CAST(rating AS DECIMAL)) – Use decimal so we don’t lose precision FROM #ratings ; But that average really doesn’t do anybody any good since it’s nice and precise.