November 21, 2014 at 6:11 am
Recently been trying to wrap my brain around APPLY and it's sibling CROSS APPLY as I have some situations where they could prove useful.
I'm using 2005 at the moment, so I don't get to play with some of the other toys...(sniff) :crying:
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
November 21, 2014 at 6:11 am
LoudClear (11/21/2014)
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.
If you want to learn PIVOT, take a look at crosstabs as well. I think you'll find the performance pretty good. If you want a good place to start, try http://www.sqlservercentral.com/articles/T-SQL/63681/ and then move on to http://www.sqlservercentral.com/articles/Crosstab/65048/. Jeff Moden does his usual excellent job of exploration and performance testing.
November 21, 2014 at 6:18 am
lshanahan (11/21/2014)
Recently been trying to wrap my brain around APPLY and it's sibling CROSS APPLY as I have some situations where they could prove useful.I'm using 2005 at the moment, so I don't get to play with some of the other toys...(sniff) :crying:
APPLY lets you use a TVF (table-valued function) as a table in your query. Think of CROSS APPLY as an INNER JOIN and OUTER APPLY as an OUTER JOIN. To get you going, Paul White has published a couple of articles at http://www.sqlservercentral.com/articles/APPLY/69953/ and http://www.sqlservercentral.com/articles/APPLY/69954/.
Just as a hint so you start off in the right direction, for good performance your TVFs should be ITVFs - inline table-valued functions.
November 21, 2014 at 6:25 am
I have used APPLY. I was tasked with finding all the production routing steps for inspection within a given date range. Simple enough, but the kicker was that they also wanted to see if the previous sequential routing step (which would not have been an inspection routing and could potentially be outside the given date range) was done. And this all displayed on the same line on the report (no linked sub rpts). How do you query into table with a where string of just insp records with a given date range and for each record returned simultaneously return records from the same table outside the where string? APPLY/CROSS APPLY to the rescue! I created a Table Valued function and joined it to the main query via APPLY. Recursion? A bit, but it was still faster then my original sub query approach with much more compact code. Was it the best solution? That is debatable of course but it worked well for me.
November 21, 2014 at 6:39 am
@david.Andrews.
Not that this really helps but..
The MSDN entry for the TRY_CONVERT function (http://technet.microsoft.com/en-us/library/hh230993(v=sql.110).aspx) has the remarks "TRY_CONVERT is a new keyword in compatibility level 110" which the IIF one does not.
It seems that for whatever reason, you just cant use it in any other compatibility level.
The 2014 version adds "and higher" to the remark
November 21, 2014 at 6:46 am
Ed Wagner (11/21/2014)
APPLY lets you use a TVF (table-valued function) as a table in your query. Think of CROSS APPLY as an INNER JOIN and OUTER APPLY as an OUTER JOIN. To get you going, Paul White has published a couple of articles at http://www.sqlservercentral.com/articles/APPLY/69953/ and http://www.sqlservercentral.com/articles/APPLY/69954/.Just as a hint so you start off in the right direction, for good performance your TVFs should be ITVFs - inline table-valued functions.
I've read up on them (don't remember if I read Paul's article, but I will check it out), but to understand something I really have to sit down and tinker with it.
I was recently asked to create some reporting metrics from one of my creations and while I was able to accomplish it using correlated sub-queries, I looked at the code and went "yuck". One of the articles I read mentioned using APPLY/CROSS APPLY to simplify such scenarios. Had a bit of a time crunch so I basically went with what I knew until I could sit down and play with APPLY.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
November 21, 2014 at 6:54 am
I've used the immediate-If (IIF) quite a bit, as it was one of my most-used functions back in the days of DOS-based FoxPro. (Yes, Access stole it from FoxPro when Microsoft acquired them.) I was surprised it took this long to show up in T-SQL, as it's fantastically useful for checking any logical expression inline without having to resort to separate functions for every conceivable logic test, like ISNULL(), or having to write multiple CASE WHEN..END AS blocks for simple comparisons.
I've also used the LAG/LEAD functions to do things like calculate event durations when all I have are records with a single DATETIME field as an event time. For the current record, I use the event time as the start, then the event time of the LEAD 1 record minus one second as the ending time. It can also be checked in reverse with the LAG function.
Regards,
R.T. Watkins
November 21, 2014 at 6:59 am
Thanks to Jeff Moden I have used tally tables. They make life so much easier. I use them instead of looping constructs.
I have also used TRY...CATCH to catch potential errors and TRY_CONVERT for data coming from 3rd parties.
Tom
November 21, 2014 at 7:01 am
Some of these newer T-SQL features (like SEQUENCE and IIF function) are not an evolutionary improvement over what we've had in SQL Server for the past 15 years but rather adaptations from other database platforms like Oracle, and they were added to SQL Server primarily to ease the effort of porting databases or writing cross platform SQL.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 21, 2014 at 7:04 am
I've used tally tables and IIF. Tally Tables changed a lot for me.
Thanks for the list of things to research!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 21, 2014 at 7:06 am
james.brown 6144 (11/21/2014)
@david.Andrews.Not that this really helps but..
The MSDN entry for the TRY_CONVERT function (http://technet.microsoft.com/en-us/library/hh230993(v=sql.110).aspx) has the remarks "TRY_CONVERT is a new keyword in compatibility level 110" which the IIF one does not.
It seems that for whatever reason, you just cant use it in any other compatibility level.
The 2014 version adds "and higher" to the remark
that is weird. never mind, i was just curious as to how it worked!
November 21, 2014 at 7:22 am
David McKinney (11/21/2014)
As for TRY...CONVERT, Steve's obviously thinking back to his rugby playing days!Ireland / Australia tomorrow btw....come on Ireland!! 😛
Indeed, though I was neither of those things. I was scrum half, just getting things moving a little quicker each time.
November 21, 2014 at 7:24 am
lshanahan (11/21/2014)
I've read up on them (don't remember if I read Paul's article, but I will check it out), but to understand something I really have to sit down and tinker with it.I was recently asked to create some reporting metrics from one of my creations and while I was able to accomplish it using correlated sub-queries, I looked at the code and went "yuck". One of the articles I read mentioned using APPLY/CROSS APPLY to simplify such scenarios. Had a bit of a time crunch so I basically went with what I knew until I could sit down and play with APPLY.
Check this out from the master: http://www.microsoftvirtualacademy.com/training-courses/boost-your-t-sql-with-the-apply-operator
November 21, 2014 at 7:27 am
APPLY – all the time. I use a lot of table value functions in my code, and use CROSS APPLY with them. I’ve even used it in some CTE code.
LAG/LEAD – I’m running 2008R2 everywhere, and end up using the ROW_NUMBER / CTE work-around to get this benefit. Once I get a 2012 system running, I’ll give this a TRY.
Tally Table – Not so much for “tricky” code work like splitting strings apart, etc, but it gets constant use in my time dimensions.
Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
November 21, 2014 at 7:50 am
Often times "tricky" techniques are not the best solution. What I mean is that windowing functions are very useful, practicaly essential, for BI type reporting or ad-hoc discovery type SQL queries. They do perform well, considering what they're expected to do. However, if you find yourself resorting to these windowing type functions, like using them routinely within transactional or operational database stored procedures, it may be time to ask yourself if the database could be refactored a little better to meet the needs of your applications.
For example, I sometimes am asked to include something like "the customer's primary phone number" in a report or call center work list. There is usualy (but not always) some specific criteria for what identifies the one and only "primary" phone number, like the most recently added and/or preferring cell phone over work phone. If the CustomerPhone table doesn't contain a pre-ranked indicator column, then I have to resort to ranking at runtime within the SQL query, which is not efficient. It's optimal to have this primary/secondary/tertiary type ranking populated by default within a column during the initial ETL load. The application can further allow the user to override and exlicitly set primary indicator on a case by case basis if needed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply