5 Time Saving Features Of SQL Server 2012 Querying
Why would you want to upgrade to SQL Server 2012? Sure, if you’re using an older version of SQL Server, there’s many big deal reasons to justify pulling out the checkbook. Sometimes, though, it’s the little niceties that get me excited. These five features promise to eliminate hours of work you’ve no doubt spent on database queries in the past to help get your next project online quicker and make your day just a little nicer.
Eliminate Daily Pain With New Querying Features
Some of the greatest upgrades to SQL Server in the past 4 years have been basic developer or DBA productivity features. Often these features don’t do much and aren’t that magical, but in the hands of a time squeezed developer, the smallest feature can return hours of time to spend on more important activities, like napping. In my work, I’ll use one of these features in particular in the coming months and promises to make my job a lot easier. I can’t wait.1. TRY_CONVERT and TRY_CAST
If you’ve ever imported some data from somewhere, you’ve perhaps run across this problem: everything is humming right along and, suddenly, the data load stops because some jack-ass put some random string value in for a date. That’s right folks, “I like food” is not a date. Of course, the entire job stops and you’re left wondering, where in the world did it all go wrong? That’s because CONVERT and CAST fail hard when they’re unable to do their job. Game over.Using TRY_CONVERT and TRY_CAST, those mean data scoundrels will never get the best of you. If one of these operators finds a value that it can’t do anything with, no problem. They just return NULL and everyone carries on. Sure, maybe you do want it to fail if it can’t convert. But you know what? Sometimes, you just don’t care.
It’s the little things in SQL Server that generate the most smiles.
2. IIF Function
Ah ternary, you magnificent bastard. I love the ternary syntax. Sure, this is really “Inline IF”, but it gives us a built-in, simple way to do a ternary operation inside of T-SQL. Before, you could easily use the CASE statement, but it was always too wordy for my tastes. It looks like this:IIF ( boolean_expression, true_value, false_value )
Under the covers, SQL Server translates IIF into CASE, so it’s not like we get some massive performance boost. No, just some plain old developer productivity and code readability. Check out IIF on MSDN.
3. OFFSET/FETCH
This is another one of those things that developers have rolled their own for years. You want to page a result set in T-SQL so you don’t need to return tons of data to your application. There were a few different ways to do it, such as using the ROW_NUMBER feature, but with SQL Server 2012, OFFSET and FETCH are built into the T-SQL language. It’s part of the ORDER BY clause. Unfortunately, there’s no real magic or performance gain here. It’s really just a syntactical helper to standardize this oh so common task. I’ll take it!4. Date Functions
We’ve all been there. We’ve got discrete pieces of data like a year, month, and day and we want to make a date type out of it. Everybody rolled their own functions to do this. No more. Now, we can do something very simple using DATEFROMPARTS.DATEFROMPARTS ( year, month, day )
Boom. That was easy, wasn’t it? And this is just one of the many new functions we’ve been given so that we no longer have to dig through the internet trying to find the best way. Built-in is best, I always say. (Okay, I’ve never said that before, but it sounds pretty good!)
5. Window Functions
The big deal about the new windowing functions is that referencing other rows in a query is now simple and tuned. In the past, if you wanted running aggregations such as sliding window average you either had to use cursors (DON’T USE CURSORS UNLESS YOU KNOW WHAT YOU ARE DOING) or correlated sub-queries. SQL Server 2012 now supports windowing functions inside your queries without having to resort to execution plan twisting tricks.The usual suspects are available for use in these windowing functions: AVG, MIN, MAX, and SUM to name just a few. Joining this prestigious group are distribution functions like PERCENTILE_CONT and PERCENTILE_DISC not to mention various offset functions like LAG, LEAD, FIRST_VALUE, and LAST_VALUE which allow you to point at specific rows in your resulting data. Unfortunately, you cannot use your user-defined CLR functions. Maybe next time.
These windowing functions look very familiar, you might say. In fact, they look a lot like the windowing functions of yore. The difference is that now, you can apply the aggregate function to a subset of the previous rows. Whoop dido, you could do it before with correlated sub-queries, right? Indeed. However, the runtime of correlated sub queries approaches O(n²) where the windowing functions have a runtime of O(1). Who said that Big O stuff from CS wasn’t going to come in handy?!