Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
masterdineen
Aged Yak Warrior
550 Posts |
 Posted - 2014-05-22 : 10:21:46
|
| Hello everyone,what are the most commonly created User Defined Functions ? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-05-22 : 10:37:25
|
| I don't think there is anything that one could categorize as "commonly created". It depends on the data and how one uses the data. Usually they are created so you can refactor a computation or a query that is used in multiple places. However, one has to be careful about that kind of refactoring because it can add a performance penalty.An example of a user-defined function might be "GetNextWeekday" that given a date, returns the date for the next weekday, skipping over weekends. Even though I use that logic in lot of my queries, I have not made that into a UDF, instead embedding the raw code in the queries, for performance reasons. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2014-05-22 : 10:47:12
|
| just based on reading people's posts on sqlteam.com for about 10 years I would have to say that the most commonly created functions should never have been created.But snarking aside, I would say the most universally beneficial udfs are various types of string splitting table valued functions. where a delimited string of values can be transposed to a table of values that you can JOIN on.Maybe not so common but in my opinion very useful are inline table valued functions. You can incorporate fairly complex logic in a set based fashion. Much better than putting the same logic in a scalar function and including it in the column list of a select statement. why do you ask?Be One with the OptimizerTG |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2014-05-25 : 17:50:52
|
| I ask because I have been developing warehouses for a few years now. I have create loads of Stored procs & Views but I was thinking I haven't had much call to create udf functions. only a couple. mostly to dell with strings and to sort out IBM dates from an external source. so just want to ask what are the most common uses are for udf functions. |
|
|
|
GouravSaxena1987
Starting Member
23 Posts |
Posted - 2014-05-26 : 02:04:03
|
| Most common use of function will be when you want to perform certain steps in SQL itself, you can create a function and call it from SQL/Store Procs.Since it slow performance of query, mostly we should ignore it.I guess table value functions are most widely created in SQL Server.Regards,Gourav SaxenaData Warehouse CounsultantGouravSaxena1987@gmail.com |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2014-05-26 : 05:12:52
|
| ok. thank you all very much for your advice. take care. Rob ( Masterdineen ) |
|
|
|
|
|
|
|
|
User Defined Functions common uses