Funciones definidas por el usuario

Se aplica a:sí SQL Server (todas las versiones compatibles) Sí Azure SQL Base de datos

Al igual que las funciones en lenguajes de programación, SQL Server funciones definidas por el usuario son rutinas que aceptan parámetros, realizan una acción, como un cálculo complejo, y devuelven el resultado de esa acción como valor. El valor devuelto puede ser un valor escalar único o un conjunto de resultados.

Ventajas de las funciones definidas por el usuario

¿Por qué usar funciones definidas por el usuario (UDF)?

  • Programación modular. Puede crear la función una vez, almacenarla en la base de datos y llamarla desde el programa tantas veces como desee. Las funciones definidas por el usuario se pueden modificar, independientemente del código de origen del programa.

  • Ejecución más rápida. De forma similar a los procedimientos almacenados, las funciones definidas por el usuario de Transact-SQL reducen el costo de compilación del código de Transact-SQL almacenando en caché los planes y reutilizándolos para ejecuciones repetidas. Esto significa que la función definida por el usuario no necesita volver a analizarse y volver a personalizarse con cada uso, lo que da lugar a tiempos de ejecución mucho más rápidos.

    Las funciones CLR ofrecen una ventaja de rendimiento significativa sobre las funciones de Transact-SQL para las tareas computacionales, la manipulación de cadenas y la lógica de negocios. Las funciones de Transact-SQL son más adecuadas para la lógica intensiva de acceso a datos.

  • Reduzca el tráfico de red. Una operación que filtra los datos en función de alguna restricción compleja que no se puede expresar en una sola expresión escalar se puede expresar como una función. La función se puede invocar luego en la cláusula WHERE para reducir el número de filas que se envían al cliente.

Importante

Las UDF de Transact-SQL en las consultas solo se pueden ejecutar en un único subproceso (plan de ejecución serie). Por tanto, el uso de UDF impide el procesamiento de consultas en paralelo. Para obtener más información sobre el procesamiento de consultas en paralelo, vea la Guía de arquitectura de procesamiento de consultas.

Tipos de funciones

Funciones escalares

Las funciones escalares definidas por el usuario devuelven un único valor de datos del tipo definido en la cláusula RETURNS. En una función escalar insertada, el valor escalar es el resultado de una sola instrucción. Para una función escalar de varios estados, el cuerpo de la función puede contener una serie de instrucciones Transact-SQL que devuelven el valor único. El tipo devuelto puede ser de cualquier tipo de datos excepto text, ntext, image, cursory timestamp. Para obtener ejemplos, consulte Creación de funciones definidas por el usuario (motor de base de datos).

Funciones con valores de tabla

Las funciones con valores de tabla (TVF) definidas por el usuario devuelven un tipo de datos de tabla . Las funciones insertada con valores de tabla no tienen cuerpo; la tabla es el conjunto de resultados de una sola instrucción SELECT. Para obtener ejemplos, consulte Creación de funciones definidas por el usuario (motor de base de datos).

Funciones del sistema

SQL Server proporciona muchas funciones del sistema que puede usar para realizar varias operaciones. No se pueden modificar. Para obtener más información, vea Funciones integradas (Transact-SQL),Funciones almacenadas del sistema (Transact-SQL) y Funciones y vistas y funciones de administración dinámica (Transact-SQL).

Directrices

Los errores de Transact-SQL que hacen que se cancele una instrucción y continúen con la siguiente instrucción del módulo (como desencadenadores o procedimientos almacenados) se tratan de forma diferente dentro de una función. En las funciones, estos errores hacen que se detenga la ejecución de la función. Esto hace que se cancele la función que invocó la instrucción.

Las instrucciones de un BEGIN...END bloque no pueden tener ningún efecto secundario. Los efectos secundarios de una función son cambios definitivos del estado de un recurso que está fuera del ámbito de la función, como una modificación de una tabla de base de datos. Los únicos cambios que pueden realizar las instrucciones de la función son cambios en objetos locales de la función, como cursores o variables locales. Las modificaciones en las tablas de base de datos, las operaciones en cursores que no son locales para la función, el envío de correo electrónico, el intento de modificar un catálogo y la generación de un conjunto de resultados que se devuelve al usuario son ejemplos de acciones que no se pueden realizar en una función.

Si una CREATE FUNCTION instrucción produce efectos secundarios en los recursos que no existen cuando se emite la CREATE FUNCTION instrucción, SQL Server ejecuta la instrucción . Sin embargo, SQL Server no ejecuta la función cuando se invoca.

El número de veces que se ejecuta una función especificada en una consulta puede variar entre los planes de ejecución creados por el optimizador. Un ejemplo es una función invocada por una subconsulta en una cláusula WHERE. El número de veces que se ejecuta la subconsulta y su función puede variar con diferentes rutas de acceso seleccionadas por el optimizador.

Las funciones deterministas deben estar enlazadas al esquema. Use la SCHEMABINDING cláusula al crear una función determinista.

Para obtener más información y consideraciones de rendimiento sobre las funciones definidas por el usuario, vea Crear funciones definidas por el usuario (motor de base de datos).

Instrucciones válidas en una función

Entre los tipos de instrucciones válidos en una función se incluyen:

  • Las instrucciones DECLARE se pueden usar para definir variables y cursores de datos locales de la función.

  • La asignación de valores a objetos locales de la función, como el uso de SET para asignar valores a variables locales escalares y de tabla.

  • Las operaciones de cursores que hacen referencia a cursores locales que están declarados, abiertos, cerrados y no asignados en la función. FETCH No se permiten instrucciones que devuelven datos al cliente. Solo se permiten las instrucciones FETCH que asignan valores a variables locales mediante la cláusula INTO.

  • Instrucciones de control de flujo excepto instrucciones TRY...CATCH.

  • Instrucciones SELECT que contienen listas de selección con expresiones que asignan valores a las variables locales para la función.

  • Instrucciones UPDATE, INSERT y DELETE que modifican las variables de tabla locales de la función.

  • Instrucciones EXECUTE que llaman a un procedimiento almacenado extendido.

Funciones del sistema integradas

Las siguientes funciones integradas no deterministas se pueden usar en funciones Transact-SQL definidas por el usuario.

  • CURRENT_TIMESTAMP
  • GET_TRANSMISSION_STATUS
  • GETDATE
  • GETUTCDATE
  • @@CONNECTIONS
  • @@CPU_BUSY
  • @@DBTS
  • @@IDLE
  • @@IO_BUSY
  • @@MAX_CONNECTIONS
  • @@PACK_RECEIVED
  • @@PACK_SENT
  • @@PACKET_ERRORS
  • @@TIMETICKS
  • @@TOTAL_ERRORS
  • @@TOTAL_READ
  • @@TOTAL_WRITE

Las siguientes funciones integradas no deterministas no se pueden usar en funciones Transact-SQL definidas por el usuario.

  • NEWID
  • NEWSEQUENTIALID
  • RAND
  • TEXTPTR

Para consultar una lista de las funciones de sistema integradas deterministas y no deterministas, vea Funciones deterministas y no deterministas.

Funciones enlazadas a esquema

CREATE FUNCTION admite una cláusula SCHEMABINDING que enlaza la función con el esquema de cualquier objeto al que haga referencia, como tablas, vistas y otras funciones definidas por el usuario. Se producen errores al intentar modificar o quitar objetos a los que hace referencia una función enlazada con un esquema.

Para poder especificar SCHEMABINDING en CREATE FUNCTION se deben cumplir estas condiciones:

  • Todas las vistas y las funciones definidas por el usuario a las que hace referencia la función deben estar enlazadas con un esquema.

  • Todos los objetos a los que hace referencia la función deben encontrarse en la misma base de datos que la función. Se debe hacer referencia a los objetos mediante nombres de una o dos partes.

  • Se debe disponer de permisos REFERENCES en todos los objetos (tablas, vistas y funciones definidas por el usuario) a los que hace referencia la función.

Se puede usar ALTER FUNCTION para quitar el enlace con el esquema. La instrucción ALTER FUNCTION debe volver a definir la función sin especificar WITH SCHEMABINDING.

Especificar parámetros

Una función definida por el usuario tiene de cero a varios parámetros de entrada y devuelve un valor escalar o una tabla. Una función puede tener un máximo de 1024 parámetros de entrada. Cuando un parámetro de la función tiene un valor predeterminado, debe especificarse la palabra clave DEFAULT al llamar a la función para poder obtener el valor predeterminado. Este comportamiento es diferente del de los parámetros con valores predeterminados de procedimientos almacenados definidos por el usuario, para los cuales omitir el parámetro implica especificar el valor predeterminado. Las funciones definidas por el usuario no admiten parámetros de salida.

Vea también