| Author |
Topic |
|
joe8079
Posting Yak Master
127 Posts |
 Posted - 2012-07-25 : 22:32:01
|
| What would you say is the function or procedure in SQL that always baiils you out of the most complicated situation. Could be a function that is rarely used by others or something is used a lot by others. For me, it would have to be the rownumber() function. I cannot tell you how many times this has helped me out with the craziest problems i've run into. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-25 : 22:52:45
|
| for me it was the APPLY operator that has helped me on numerous occasions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-07-26 : 06:06:52
|
quote:
Originally posted by visakh16
for me it was the APPLY operator that has helped me on numerous occasions
I was so surprised that i fainted.  How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-26 : 06:53:12
|
quote:
Originally posted by visakh16
for me it was the APPLY operator that has helped me on numerous occasions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
 PS: Thanks to WebFred for that image from a previous post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=173517 I can't tell you how many times I have smiled to myself looking at the expression on the guy's face and in his eyes. Even if the thought had been expressed by Don, I couldn't pass up an opportunity to use it  As for me, it is the recursion in CTE's. It was almost like that is the answer to all my problems because I was trained in procedural programming and recursive CTE looked very much like a loop. So I end up using it where it is useful and even when it is the worst performer. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-07-26 : 07:29:23
|
For me it is ROW_NUMBER() too.especially for Sunita, here comes the "guy" 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-26 : 07:45:36
|
| You don't say! He is for real!?! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-07-26 : 07:46:24
|
Nicolas Cage - you know?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-26 : 07:50:13
|
| No! Couldn't be!! He looks much younger in this picture and there is only a vague resemblance!! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-07-26 : 08:01:40
|
The image of Nicolas Cage was taken from a scene in the 1988 black comedy film Vampire’s Kiss, which revolves around a troubled literary agent’s descent into madness after convincing himself that he is turning into a vampire. His unique facial expression was taken from a particular scene in which Cage’s character Peter Loew torments his secretary in a disturbing manner.http://knowyourmeme.com/memes/you-dont-say
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-07-26 : 11:50:54
|
I like this one from Raising Arizona  |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-07-26 : 12:08:42
|
In order of utility day to day...1) APPLY 2) ROW_NUMBER()?) ??? OUTPUT (composable DML is pretty awesome)?) ??? ;WITH (CTE's can be very useful)?) ??? MERGE (is very powerful)6) FOR XML PATH('') (combined with APPLY generally)7) QUOTENAME() Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-26 : 13:33:46
|
| I tend to use merge combined with output, cte's and row_number() a lot.I would use apply if I knew how to use it - similarly the xml functions.Also recently - frrom, converrt, And the usual - seletc, colaesce, updaet,==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-07-27 : 09:11:18
|
| CTE's and derived tables opened a whole new world to me, and by nesting them I can more or less solve anything. Row_number() is also a real life-saver...- LumbagoMy blog-> http://thefirstsql.com |
|
|
|
joe8079
Posting Yak Master
127 Posts |
Posted - 2012-07-27 : 16:38:30
|
| What would merge and output be used for? Most of the work I do is business intelligence, retrieving data, updating and deleting tables. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-27 : 17:10:36
|
| Merge literally lets you merge two (virtual) tables. You may get a set or records from a client program to store into an existing table. Some records may exist, some may have some changes and some may not be there etc. Merge lets you insert, delete, update - all in a single statement (i.e., atomic). The syntax is really conversational too.A nice article about MERGE here: http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/That article also discusses OUTPUT clause.If you are updating and deleting data, Merge and output would be VERY useful. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-07-30 : 04:34:55
|
quote:
Originally posted by joe8079
What would merge and output be used for? Most of the work I do is business intelligence, retrieving data, updating and deleting tables.
I use it for updating dimensions - particularly handy for type 2.Here's an audit trail version - the dimension update is along the same lines - It'' have to add that sometime.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
question