Real World T-SQL Tricks

  • Comments posted to this topic are about the item Real World T-SQL Tricks

  • Best thing that has happenned to my T-SQL coding in years was going on Itzik Ben-Gan's Advanced T-SQL course eighteen months ago.

    As a result I got my head around several things I'd not used before and use now in production, such as APPLY, running totals and deleting in blocks using TOP and I generally write far better SQL now than I did before the course.

  • Quick thought, IIF is kind of an oddity in this list as it brings nothing new but a Access SQL compatibility

    😎

  • No.

    Some of these things have come in with 2012 and I haven't developed much code on that. Certainly none on 2014.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Sadly not. I'm just putting out my first new 2012 project - I'm guessing there are a few very pertinent items there, but we have recently used EF more heavily.

    I really should try and do a course of some kind.

  • Going to have to look into LAG/LEAD and CONCAT/FORMAT but from that list, I use APPLY a lot. If you tend be using loops a lot, look into tally tables, they are very useful and will speed up your code 10 fold.

    TRY...CONVERT?? shouldn't that be TRY..CATCH? I use this too.

    One that is not on the list is the MERGE block which is incredibly useful for data loads. Load your data into a staging table and then MERGE it with your main table and you can INSERT, UPDATE and DELETE records all at the same time

  • liked this one.

    used most of the features since, should have used all but one.

    by the way: is there a forum or platform keeping track of desirable new features in TSQL,

    ons hot candidate being a multiway MERGE statement allowing more than one update clause

    (when matched and ... then update .. when matched and .. then update ... )

  • it's

    TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

  • As for TRY...CONVERT, Steve's obviously thinking back to his rugby playing days!

    Ireland / Australia tomorrow btw....come on Ireland!! 😛

  • I've used symmetric keys for encrypting data at the column level. Sadly, that's it.

    Fun article Steve, thanks!

  • I've previously used the LAG function in SAS which is very useful. Glad to see this functionality has been added to SQL. In the BI/data warehouse environment there is often a need to populate flags based on a business action that has happened in previous months (rows). This function saves you the effort of needing to interleave the same table.

  • Ah yes, that could be handy. LAG/LEAD would be useful too (access the previous/next records values) just a shame my company is still on SQL server 2008

  • i've been trying to get TRY_CONVERT to work but it states its an unrecognized built in function for sql server 2012 (even though sql server *does* recognises it because i get it in the intellisense, and its not underlined as unrecognised)

    i note that the compatibility mode for the database is sql server 2008 (100).

    but surely that would mean IIF wouldn't work either? but that works fine.

    reference: http://stackoverflow.com/questions/22310486/try-convert-fails-on-sql-server-2012

    http://msdn.microsoft.com/en-us/library/hh230993.aspx

    DECLARE @int INT = 1

    DECLARE @varchar VARCHAR(50) = 'david'

    SELECT

    CASE WHEN TRY_CONVERT(INT,@varchar) IS NULL THEN 'failed' ELSE 'succeeded' END AS result

    anyone see what i'm doing wrong?

  • I use tally tables and APPLY all the time. They're very efficient approaches to accomplishing the work that's needed.

    I've used symmetric keys, but not nearly as much. They work well, but I don't like the performance hit that comes along with them.

    LEAD and LAG are functions I have a use for, but we're not on 2012 yet, so I don't get to play with them yet.

  • I, like Steve, have been intentional about trying to stretch my SQL skills this year. Part of that plan has been that I now follow a number of T-SQL forums on this site, and actively participate in them whenever I feel I can contribute.

    Of the list in the editorial, I have only used Tally Tables, and LAG/LEAD. I learned about Tally Tables through one of the daily e-mails, and I've used them a few times in the wild.

    I picked up LAG from someone's response to a forum post, and it immediately set off bells in my head that I'd found the answer to a challenge I'd been facing for over a month. (It turns out that when you post a solution in a forum, you can help more than just the original poster, you can potentially help many people.)

    Up next on my to-learn list are APPLY and PIVOT. I have seen a number of examples using these in the forums, but I need to do a more formal study to properly absorb them.

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply