Real World T-SQL Tricks

Question

Real World T-SQL Tricks

Steve Jones - SSC Editor

SSC Guru

Points: 736183
More actions
November 20, 2014 at 8:25 pm

#292209

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

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

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

P Jones SSChampion Points: 12361 More actions · Answer 1

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.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

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

😎

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

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!!!

call.copse SSCoach Points: 18050 More actions · Answer 4

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.

james.brown 6144 SSC Enthusiast Points: 144 More actions · Answer 5

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

h.tobisch SSCommitted Points: 1671 More actions · Answer 6

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 ... )

h.tobisch SSCommitted Points: 1671 More actions · Answer 7

it's

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

David McKinney SSChampion Points: 10472 More actions · Answer 8

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

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

Vijay Mils SSC Journeyman Points: 82 More actions · Answer 9

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

Fun article Steve, thanks!

Sean Peffer Grasshopper Points: 21 More actions · Answer 10

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.

james.brown 6144 SSC Enthusiast Points: 144 More actions · Answer 11

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

davidandrews13 SSCertifiable Points: 6808 More actions · Answer 12

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?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 13

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

LoudClear SSC Eights! Points: 999 More actions · Answer 14

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.