ver valores tipos tabla stored procedimientos listado linea funciones funcion entre diferencia con codigo almacenados sql sql-server tsql stored-procedures sql-function

valores - tipos de funciones en sql server



Función vs. Procedimiento Almacenado en SQL Server (17)

¡Las funciones de SQL Server, como los cursores, deben usarse como su última arma! Tienen problemas de rendimiento y, por lo tanto, el uso de una función con valores de tabla debe evitarse tanto como sea posible. Hablar de rendimiento es hablar de una tabla con más de 1,000,000 de registros alojados en un servidor en un hardware de clase media; de lo contrario, no necesita preocuparse por el impacto en el rendimiento causado por las funciones.

  1. Nunca use una función para devolver un conjunto de resultados a un código externo (como ADO.Net)
  2. Use vistas / combinación de procs almacenados tanto como sea posible. puede recuperarse de futuros problemas de crecimiento de rendimiento con las sugerencias que le proporcionará DTA (Database Tuning Adviser) (como vistas y estadísticas indizadas) - ¡a veces!

Para mayor referencia, consulte: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

He estado aprendiendo funciones y procedimientos almacenados durante bastante tiempo, pero no sé por qué y cuándo debo usar una función o un procedimiento almacenado. Me parecen iguales, tal vez porque soy un poco novato en eso.

¿Puede alguien decirme por qué?

Aquí hay una razón práctica para preferir funciones sobre procedimientos almacenados. Si tiene un procedimiento almacenado que necesita los resultados de otro procedimiento almacenado, debe utilizar una instrucción insert-exec. Esto significa que tiene que crear una tabla temporal y usar una declaración exec para insertar los resultados del procedimiento almacenado en la tabla temporal. Está desordenado. Un problema con esto es que insert-execs no puede ser anidado .

Si está atascado con procedimientos almacenados que llaman a otros procedimientos almacenados, puede encontrarse con esto. Si el procedimiento almacenado anidado simplemente devuelve un conjunto de datos, puede reemplazarse con una función con valores de tabla y ya no aparecerá este error.

( esta es otra razón por la que debemos mantener la lógica empresarial fuera de la base de datos )

Comience con funciones que devuelven un solo valor. Lo bueno es que puede poner el código de uso frecuente en una función y devolverlos como una columna en un conjunto de resultados.

Luego, puede usar una función para una lista de ciudades parametrizada. dbo.GetCitiesIn ("NY") Eso devuelve una tabla que se puede usar como una combinación.

Es una forma de organizar el código. Saber cuándo algo es reutilizable y cuándo es una pérdida de tiempo es algo que solo se obtiene a través del ensayo y error y la experiencia.

Además, las funciones son una buena idea en SQL Server. Son más rápidos y pueden ser bastante potentes. Selección en línea y directa. Cuidado de no abusar.

Diferencias entre procedimientos almacenados y funciones definidas por el usuario:

  • Los procedimientos almacenados no se pueden usar en declaraciones Select.
  • Los procedimientos almacenados admiten la resolución de nombres diferidos.
  • Los procedimientos almacenados se utilizan generalmente para realizar la lógica empresarial.
  • Los procedimientos almacenados pueden devolver cualquier tipo de datos.
  • Los procedimientos almacenados pueden aceptar un mayor número de parámetros de entrada que las funciones definidas por el usuario. Los procedimientos almacenados pueden tener hasta 21,000 parámetros de entrada.
  • Los procedimientos almacenados pueden ejecutar SQL dinámico.
  • Los procedimientos almacenados soportan el manejo de errores.
  • Las funciones no deterministas se pueden utilizar en procedimientos almacenados.
  • Las funciones definidas por el usuario se pueden usar en las declaraciones Select.
  • Las funciones definidas por el usuario no admiten la resolución de nombres diferidos.
  • Las funciones definidas por el usuario se utilizan generalmente para los cálculos.
  • Las funciones definidas por el usuario deben devolver un valor.
  • Las funciones definidas por el usuario no pueden devolver imágenes.
  • Las funciones definidas por el usuario aceptan números más pequeños de parámetros de entrada que los procedimientos almacenados. Las UDF pueden tener hasta 1.023 parámetros de entrada.
  • Las tablas temporales no se pueden utilizar en funciones definidas por el usuario.
  • Las funciones definidas por el usuario no pueden ejecutar SQL dinámico.
  • Las funciones definidas por el usuario no admiten el manejo de errores. RAISEERROR O @@ERROR no están permitidos en UDF.
  • Las funciones no deterministas no se pueden utilizar en UDF. Por ejemplo, GETDATE() no se puede utilizar en UDF.

En SQL Server, las funciones y el procedimiento almacenado son dos tipos diferentes de entidades.

Función: En la base de datos de SQL Server, las funciones se utilizan para realizar algunas acciones y la acción devuelve un resultado inmediatamente. Las funciones son dos tipos:

  1. Sistema definido

  2. Usuario definido

Procedimientos almacenados: en SQL Server, los procedimientos almacenados se almacenan en el servidor y pueden devolverse cero, valores únicos y múltiples. Los procedimientos almacenados son dos tipos:

  1. Procedimientos almacenados en el sistema
  2. Procedimientos definidos por el usuario

Escriba una función definida por el usuario cuando desee calcular y devolver un valor para usar en otras sentencias de SQL; escribir un procedimiento almacenado cuando lo que desea es agrupar un conjunto de instrucciones SQL posiblemente complejo. ¡Estos son dos casos de uso muy diferentes, después de todo!

La diferencia entre SP y UDF se enumera a continuación:

+---------------------------------+----------------------------------------+ | Stored Procedure (SP) | Function (UDF - User Defined | | | Function) | +---------------------------------+----------------------------------------+ | SP can return zero , single or | Function must return a single value | | multiple values. | (which may be a scalar or a table). | +---------------------------------+----------------------------------------+ | We can use transaction in SP. | We can''t use transaction in UDF. | +---------------------------------+----------------------------------------+ | SP can have input/output | Only input parameter. | | parameter. | | +---------------------------------+----------------------------------------+ | We can call function from SP. | We can''t call SP from function. | +---------------------------------+----------------------------------------+ | We can''t use SP in SELECT/ | We can use UDF in SELECT/ WHERE/ | | WHERE/ HAVING statement. | HAVING statement. | +---------------------------------+----------------------------------------+ | We can use exception handling | We can''t use Try-Catch block in UDF. | | using Try-Catch block in SP. | | +---------------------------------+----------------------------------------+

Las funciones son valores calculados y no pueden realizar cambios ambientales permanentes en SQL Server (es decir, no se permiten sentencias INSERT o UPDATE).

Una función se puede usar en línea en sentencias de SQL si devuelve un valor escalar, o se puede unir si devuelve un conjunto de resultados.

Un punto que vale la pena destacar de los comentarios, que resumen la respuesta. Gracias a @Sean K Anderson:

Las funciones siguen la definición de dominio del equipo en el sentido de que DEBEN devolver un valor y no pueden alterar los datos que reciben como parámetros (los argumentos). Las funciones no pueden cambiar nada, deben tener al menos un parámetro y deben devolver un valor. Los procesos almacenados no tienen que tener un parámetro, pueden cambiar los objetos de la base de datos y no tienen que devolver un valor.

Las funciones y procedimientos almacenados sirven propósitos separados. Aunque no es la mejor analogía, las funciones se pueden ver literalmente como cualquier otra función que usarías en cualquier lenguaje de programación, pero los procesos almacenados son más como programas individuales o un script por lotes.

Las funciones normalmente tienen una salida y opcionalmente entradas. La salida se puede usar como la entrada para otra función (un servidor SQL incorporado como DATEDIFF, LEN, etc.) o como un predicado para una consulta SQL, por ejemplo, SELECT a, b, dbo.MyFunction(c) FROM table o SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c) .

Los procesos almacenados se utilizan para unir consultas SQL en una transacción y para interactuar con el mundo exterior. Los marcos como ADO.NET, etc. no pueden llamar directamente a una función, pero pueden llamar directamente a un proceso almacenado.

Sin embargo, las funciones tienen un peligro oculto: pueden ser mal utilizadas y causar problemas de rendimiento bastante desagradables: considere esta consulta:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Donde MyFunction se declara como:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER AS BEGIN DECLARE @retval INTEGER SELECT localValue FROM dbo.localToNationalMapTable WHERE nationalValue = @someValue RETURN @retval END

Lo que sucede aquí es que se llama a la función MyFunction para cada fila de la tabla MyTable. Si MyTable tiene 1000 filas, esas son otras 1000 consultas ad-hoc contra la base de datos. De manera similar, si se llama a la función cuando se especifica en la especificación de columna, entonces se llamará a la función para cada fila devuelta por SELECT.

Así que necesitas tener cuidado al escribir las funciones. Si selecciona SELECT de una tabla en una función, debe preguntarse si se puede realizar mejor con un JOIN en el proceso almacenado principal o alguna otra construcción SQL (como CASE ... CUANDO ... ELSE ... FIN).

Para decidir cuándo usar lo que podrían ayudar los siguientes puntos:

  1. Los procedimientos almacenados no pueden devolver una variable de tabla donde una función puede hacerlo.

  2. Puede usar procedimientos almacenados para alterar los parámetros del entorno del servidor donde, al usar funciones, no puede.

aclamaciones

Procedimiento almacenado:

  • Es como un programa en miniatura en SQL Server.
  • Puede ser tan simple como una declaración de selección, o tan complejo como un script largo que agrega, elimina, actualiza y / o lee datos de varias tablas en una base de datos.
  • (Puede implementar bucles y cursores, que le permiten trabajar con resultados más pequeños o operaciones fila por fila en los datos).
  • Debe llamarse utilizando la sentencia EXEC o EXECUTE .
  • Devuelve las variables de la tabla, pero no podemos usar el parámetro OUT .
  • Soporta transacciones.

Función:

  • No se puede utilizar para actualizar, eliminar o agregar registros a la base de datos.
  • Simplemente devuelve un solo valor o un valor de tabla.

  • Solo se puede utilizar para seleccionar registros. Sin embargo, se puede llamar muy fácilmente desde SQL estándar, como:

    SELECT dbo.functionname(''Parameter1'')

    o

    SELECT Name, dbo.Functionname(''Parameter1'') FROM sysObjects

  • Para operaciones de selección reutilizables simples, las funciones pueden simplificar el código. Solo desconfíe de usar cláusulas JOIN en sus funciones. Si su función tiene una cláusula JOIN y la llama desde otra instrucción de selección que devuelve múltiples resultados, esa llamada a la función JOIN esas tablas para cada línea devuelta en el conjunto de resultados. Entonces, aunque pueden ser útiles para simplificar algo de lógica, también pueden ser un cuello de botella en el rendimiento si no se usan correctamente.

  • Devuelve los valores utilizando el parámetro OUT .
  • No admite transacciones.

una función definida por el usuario es una herramienta importante disponible para un programador de servidores SQL. Puede usarlo en línea en una declaración SQL como tal

SELECT a, lookupValue(b), c FROM customers

donde lookupValue será un UDF. Este tipo de funcionalidad no es posible cuando se utiliza un procedimiento almacenado. Al mismo tiempo, no puedes hacer ciertas cosas dentro de un UDF. Lo básico para recordar aquí es que los UDF''s:

  • no se pueden crear cambios permanentes
  • no puede cambiar los datos

Un procedimiento almacenado puede hacer esas cosas.

Para mí, el uso en línea de un UDF es el uso más importante de un UDF.

Diferencia basica

La función debe devolver un valor, pero en el Procedimiento almacenado es opcional (el Procedimiento puede devolver cero o n valores).

Las funciones solo pueden tener parámetros de entrada, mientras que los procedimientos pueden tener parámetros de entrada / salida.

La función toma un parámetro de entrada, es obligatorio, pero el procedimiento almacenado puede tomar de uno a n parámetros de entrada.

Las funciones se pueden llamar desde el procedimiento, mientras que los procedimientos no se pueden llamar desde la función.

Diferencia anticipada

El procedimiento permite la instrucción SELECT y DML (INSERT / UPDATE / DELETE), mientras que la función solo permite la instrucción SELECT.

Los procedimientos no se pueden utilizar en una instrucción SELECT mientras que la función se puede incrustar en una instrucción SELECT.

Los procedimientos almacenados no se pueden usar en las sentencias de SQL en ninguna parte de la sección WHERE / HAVING / SELECT mientras que Function puede ser.

Las funciones que devuelven tablas se pueden tratar como otro conjunto de filas. Esto puede ser usado en unir con otras tablas.

La función en línea se puede considerar como vistas que toman parámetros y se pueden usar en uniones y otras operaciones de conjunto de filas.

El bloque try-catch en un procedimiento puede manejar las excepciones, mientras que el bloque try-catch no puede usarse en una función.

Podemos optar por la gestión de transacciones en el procedimiento, mientras que no podemos ir en función.

source

Los procedimientos almacenados se utilizan como scripts . Ejecutan una serie de comandos para usted y puede programar su ejecución en ciertos momentos.

Las funciones se utilizan como métodos. Le pasas algo y te devuelve un resultado. Debe ser pequeño y rápido, lo hace sobre la marcha.

STORE PROCEDURE FUNCTION (USER DEFINED FUNCTION) * Procedure can return 0, single or | * Function can return only single value multiple values. | | * Procedure can have input, output | * Function can have only input parameters. | parameters. | * Procedure cannot be called from | * Functions can be called from function. | procedure. | * Procedure allows select as well as | * Function allows only select statement DML statement in it. | in it. | * Exception can be handled by | * Try-catch block cannot be used in a try-catch block in a procedure. | function. | * We can go for transaction management| * We can''t go for transaction in procedure. | management in function. | * Procedure cannot be utilized in a | * Function can be embedded in a select select statement | statement. | * Procedure can affect the state | * Function can not affect the state of database means it can perform | of database means it can not CRUD operation on database. | perform CRUD operation on | database. | * Procedure can use temporary tables. | * Function can not use temporary tables | temporary tables. | * Procedure can alter the server | * Function can not alter the environment parameters. | environment parameters. | * Procedure can use when we want | * Function can use when we want instead is to group a possibly- | to compute and return a value complex set of SQL statements. | for use in other SQL statements.

  • Es obligatorio que la función devuelva un valor mientras no sea para el procedimiento almacenado.
  • Las declaraciones seleccionadas solo se aceptan en UDF, mientras que las declaraciones en DML no son necesarias.
  • El procedimiento almacenado acepta cualquier declaración, así como las declaraciones de DML.
  • UDF solo permite entradas y no salidas.
  • El procedimiento almacenado permite tanto entradas como salidas.
  • Los bloques de captura no se pueden usar en UDF, pero se pueden usar en un procedimiento almacenado.
  • No se permiten transacciones en funciones en UDF, pero en el procedimiento almacenado están permitidas.
  • Solo las variables de tabla pueden usarse en UDF y no en tablas temporales.
  • El procedimiento almacenado permite tanto variables de tabla como tablas temporales.
  • UDF no permite que los procedimientos almacenados sean llamados desde funciones, mientras que los procedimientos almacenados permiten la llamada de funciones.
  • UDF se usa en la cláusula de unión, mientras que los procedimientos almacenados no se pueden usar en la cláusula de unión.
  • El procedimiento almacenado siempre permitirá el retorno a cero. UDF, por el contrario, tiene valores que deben regresar a un punto predeterminado.

  • Las funciones se pueden usar en una declaración de selección donde los procedimientos no pueden.

  • El procedimiento almacenado toma los parámetros de entrada y salida, pero las Funciones solo toman parámetros de entrada.

  • Las funciones no pueden devolver valores de texto de tipo, ntext, imagen y marcas de tiempo donde pueden hacerlo los procedimientos.

  • Las funciones se pueden usar como tipos de datos definidos por el usuario en la tabla de creación, pero los procedimientos no pueden.

*** Ej .: -crear table <tablename>(name varchar(10),salary getsal(name))

Aquí getsal es una función definida por el usuario que devuelve un tipo de salario, cuando se crea una tabla, no se asigna almacenamiento para el tipo de salario, y la función getsal tampoco se ejecuta, pero cuando estamos obteniendo algunos valores de esta tabla, la función getsal se ejecuta y el Tipo devuelto se devuelve como el conjunto de resultados.