🤔UDF in SQL: pros and cons
Data scientists and data analysts often write SQL queries that take a very long time. User Defined Functions (UDFs) can improve the speed of writing SQL queries. A UDF is at a basic level similar to a typical SQL function, but is used by the user. Technically, a UDF is a function that takes a set of typed parameters, applies change logic, and then returns a typed value. UDFs have a wide range of uses, including optimizing repetitive code and centralizing business logic to help you write SQL queries more efficiently. The use of UDF can be noted, but it has its repetitions. The main advantages of UDF:
• allows you to replace parts of complex or repetitive SQL code with one-line strings that make the code more readable. For example, a complex CASE statement spans many lines. With a UDF, it can be reduced to a single line.
• Encourages the use of process calculations, use them in parentheses to reuse them in new queries.
Thus, UDF speeds up code development, but it also has some disadvantages:
• Too many obscure UDFs can make code less readable, especially if they themselves expose obscurities like "func1" - it's not clear that the function does a division and the reader has to look up its definition, which takes more time than just reading it in the code .
• It will be necessary to keep a record of all emergency functions that arise, which is difficult when there are a large number of them. To do this, it is recommended to save the dictionary with the created UDFs and share it with the team. You can also create more generic functions to avoid rework.
https://towardsdatascience.com/save-time-writing-sql-with-udfs-24b002bf0192