You don't have access to this class

Keep learning! Join and start boosting your career

Aprovecha el precio especial y haz tu profesión a prueba de IA

Antes: $249

Currency
$209
Suscríbete

Termina en:

2 Días
17 Hrs
40 Min
19 Seg

Tipos de funciones

20/31
Resources

What are user-defined functions?

User-defined functions are a powerful tool in programming and database management. These routines are created to accept parameters and return a result that can be a simple value or even a complete table. These functions are especially useful when you need to perform complex or repetitive calculations, thus optimizing the process and avoiding unnecessary code replication.

How to create a function to optimize SQL queries?

To illustrate the creation of a function in SQL, we use the example of the importers database. Here, we want to find a more efficient way to calculate the total sales of a specific product. Let's start by looking at creating a function that returns a sum total.

Steps to create a function in SQL

  1. Define the purpose of the function: In this case, we want to create a function that calculates the total sold per product.
  2. Specify the parameters: Our function will receive StockItemID as a parameter.
  3. Write the function: The return type is defined, in this case, decimal, and will be used within the BEGIN and END statements.
  4. Include the necessary logic: Perform calculations within the function to determine the total sales by multiplying the quantity by the unit value.
  5. Create and test the function: Once created, you must execute it and compare its efficiency with the original code.

Here is an example in SQL code:

CREATE FUNCTION TotalSoldByProduct (@StockItemID INT)RETURNS DECIMALASBEGIN DECLARE @TotalSold DECIMAL;    
 SELECT @TotalSold = SUM(Quantity * UnitPrice) FROM SalesOrdersLine WHERE StockItemID = @StockItemID;    
 RETURN @TotalSold;END;

Comparison with the original code

To demonstrate the efficiency of the created function, it can be compared to the original query using the SQL execution plan. The results typically show that the use of functions optimizes performance and reduces execution costs.

  • Original query: This usually has a higher execution cost, around 93%, due to its multiple operations.
  • Query with function: By using the function, the execution cost is significantly reduced, showing how functions can optimize complex queries.

Why use functions in your SQL queries?

  • Efficiency: Functions allow you to encapsulate complex logic that, when executed repeatedly, improves overall performance.
  • Reusability: They promote the use of predefined logic that can be applied to multiple queries without the need for code duplication.
  • Readability and Maintainability: Because the code is kept separate, it is easier to read and update without affecting multiple parts of the system.

These advantages make user-defined functions a crucial tool for those who work with large volumes of data and require process optimization, so don't hesitate to incorporate them in your future SQL queries to speed up and improve your results!

Contributions 7

Questions 2

Sort by:

Want to see more contributions, questions and answers from the community?

Las funciones definidas por el usuario son rutinas que aceptan parámetros y devuelven un valor después de haber hecho un calculo o devuelven una tabla.
Ej:

CREATE FUNCTION f_TotalVendidoXProducto
(
	@StockItemID int
)
RETURNS decimal
AS
BEGIN

	DECLARE @total decimal

	SELECT @total = SUM(Quantity * UnitPrice)
	  FROM  Sales.OrderLines
	 WHERE StockItemID = @StockItemID

	RETURN @total

Siempre he pensado que usar funciones agiliza los procesos, pero no sabía que serían tan potentes. El problema que encuentro es cuando se cambian los tipos de datos o nombres, por ejemplo, y se tiene que cambiar en cada función creada. Eso lo volvería tedioso.

Yo suelo usar las funciones cuando se van a realizar cálculos o procesos que son muy repetitivos, pero cuando hago un simple “select” a una tabla no las creo tan necesarias.

Al inicio de esta clase “Tipos de funciones” estuve un poco incrédulo en que las funciones ayudarían en gran parte a optimizar, pero la verdad es que estoy sumamente sorprendido por el resultado. ¡Gracias Profesor Roy!

Este video es el video 19 no el 20 y el video 19 es el 20 el orden no es el correcto

Honestamente creo que este es el primer video al que le veo verdadera utilidad, voy a investigar más acerca de las funciones que parece algo interesante y muy potente!

Jamás creí que las UDF serían tan eficientes. 😮