November 21, 2014 at 7:52 am
I've used several on the list:
Tally table - useful for splits, especially, but haven't done nearly as much as I should have with this.
APPLY - I've used this to replace things that you used to have to do with either a temp table or a correlated subquery or even inline scalar function/sub-query type code. It's great to reduce the impact on SQL to handle that scalar code in a more set-based fashion.
Try_Cast/Convert - I've been using this recently because it's an easy way to try to cast the data and if it doesn't work to not return anything rather than return an error.
Keys - we used them for Service Broker, but didn't do anything else with them.
Concat/Format - I've played with them briefly, but haven't gone too far with them yet.
November 21, 2014 at 7:52 am
Most of my work is on Sybase (ASE and IQ), so I don't get to use the more recent SQL Server functionality as much as I would like. I did create a two-dimensional tally table (for work order number and date, for work scheduled over the next few weeks) after reading Jeff Moden's fabulous articles. We are moving some of our stuff to SQL Server, so I can use the "fun stuff" a little more now, such as THROW and CTEs (which Sybase does not have :()
Steve, I'm wondering why you included tally tables in this list. They can help with performance tremendously, and should be in any SQL developer's toolkit, but they aren't really a T-SQL feature.
November 21, 2014 at 8:38 am
davidandrews13 (11/21/2014)
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?
Compatibility level of your database?
Gerald Britton, Pluralsight courses
November 21, 2014 at 12:03 pm
You should see the SQL Extensions in AsterData. It would be great to see a few of them in T-SQL for the polybase stuff.
November 21, 2014 at 3:13 pm
Ed Wagner (11/21/2014)
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.
+11
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 22, 2014 at 5:25 am
I recently used LAG for calculating the difference in values between two rows in an ordered set. It was for a manufacturing application in which product weights are recorded in a cumulative fashion and we needed the weight differentials for performing certain calculations.
Cheers,
Adolfo Socorro
November 24, 2014 at 9:22 am
james.brown 6144 (11/21/2014)
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
I've used MERGE too.
August 5, 2016 at 4:00 pm
You need to do a new version of this article after 2016 has been out a while, Steve. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 6, 2016 at 4:50 am
as I am also a Oracle/DB2 person I have used these a lot in production - and the current projects I am working on 2014 will use them also.
LAG/LEAD
SEQUENCE
Tally tables also use a lot - sometimes the one from the forum here, and also a small variation for lower values using group by cube
APPLY - use it a lot, and contrary to what I have seen most people mentioning using for TVF is not the main use I have for it
sample code where calculated value using outer apply is reused several times. situations where I used this construct were a lot more complex, some times with cascading apply where the result of one is used by the following one
select val * 20
, val * 50
, val * 100
from tbl
outer apply (select case
when item = 2 then price * unitprice
when item = 4 then price * (unitprice * discount / 100)
end as val) cval
TRY..CONVERT and CONCAT/FORMAT will most likely use on the projects I am working on
August 8, 2016 at 9:37 am
The Dixie Flatline (8/5/2016)
You need to do a new version of this article after 2016 has been out a while, Steve. 🙂
For sure.
August 8, 2016 at 11:49 am
While we are still old school then, I think Cross Applying a VALUES clause is a great trick. Very clean and powerful alternative to UNPIVOT.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply