CREATE FUNCTION (Transact-SQL)

Artigo
04/26/2024

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance

Crea una función definida por el usuario (UDF), que es una rutina de Transact-SQL o Common Language Runtime (CLR). Una función definida por el usuario acepta parámetros, realiza una acción como un cálculo complejo y devuelve el resultado de esa acción como un valor. El valor devuelto puede ser un valor escalar (único) o una tabla. Utilice esta instrucción para crear una rutina reutilizable que se pueda utilizar de estas formas:

En instrucciones Transact-SQL, como SELECT
En aplicaciones que llaman a la función
En la definición de otra función definida por el usuario
Para parametrizar una vista o mejorar la funcionalidad de una vista indizada
Para definir una columna en una tabla
Para definir una CHECK restricción en una columna
Para reemplazar un procedimiento almacenado
Usar una función insertada como predicado de filtro de la directiva de seguridad

En este artículo se describe la integración de CLR de .NET Framework en SQL Server. La integración clR no se aplica a Azure SQL Database.

Para obtener Azure Synapse Analytics o Microsoft Fabric, consulte CREATE FUNCTION (Azure Synapse Analytics y Microsoft Fabric).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para funciones escalares de Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
 [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]

Sintaxis para funciones insertadas con valores de tabla de Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Sintaxis para funciones con valores de tabla de varias instrucciones Transact-SQL.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ , ...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN
    END
[ ; ]

Sintaxis para cláusulas de función transact-SQL.

<function_option> ::=
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
  | [ INLINE = { ON | OFF } ]
}

<table_type_definition> ::=
( { <column_definition> <column_constraint>
  | <computed_column_definition> }
    [ <table_constraint> ] [ , ...n ]
)
<column_definition> ::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ]
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ]
}

<column_constraint> ::=
{
    [ NULL | NOT NULL ]
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
      [ ON { filegroup | "default" } ] ]
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<computed_column_definition> ::=
column_name AS computed_column_expression

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ]
      ( column_name [ ASC | DESC ] [ , ...n ]
        [ WITH FILLFACTOR = fillfactor
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ , ...n ]
}

<index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
}

Sintaxis para funciones escalares clR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS { return_data_type }
    [ WITH <clr_function_option> [ , ...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Sintaxis para funciones con valores de tabla CLR.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ NULL ]
    [ = default ] }
    [ , ...n ]
)
RETURNS TABLE <clr_table_type_definition>
    [ WITH <clr_function_option> [ , ...n ] ]
    [ ORDER ( <order_clause> ) ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Sintaxis para cláusulas de función CLR.

<order_clause> ::=
{
   <column_name_in_clr_table_type_definition>
   [ ASC | DESC ]
} [ , ...n ]

<method_specifier> ::=
    assembly_name.class_name.method_name

<clr_function_option> ::=
{
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_table_type_definition> ::=
( { column_name data_type } [ , ...n ] )

Sintaxis OLTP en memoria para funciones escalares definidas por el usuario compiladas de forma nativa.

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
    [ NULL | NOT NULL ] [ = default ] [ READONLY ] }
    [ , ...n ]
  ]
)
RETURNS return_data_type
     WITH <function_option> [ , ...n ]
    [ AS ]
    BEGIN ATOMIC WITH (set_option [ , ... n ] )
        function_body
        RETURN scalar_expression
    END

<function_option> ::=
{
  |  NATIVE_COMPILATION
  |  SCHEMABINDING
  | [ EXECUTE_AS_Clause ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

OR ALTER

Se aplica a: SQL Server 2016 (13.x) SP 1 y versiones posteriores, y Azure SQL Database.

Modifica condicionalmente la función solo si ya existe.

La sintaxis opcional OR ALTER está disponible para CLR, a partir de SQL Server 2016 (13.x) SP 1 CU 1.

schema_name

Nombre del esquema al que pertenece la función definida por el usuario.

function_name

Nombre de la función definida por el usuario. Los nombres de funciones deben seguir las reglas de los identificadores y deben ser únicos en la base de datos y para su esquema.

Los paréntesis son necesarios después del nombre de la función, incluso si no se especifica un parámetro.

@parameter_name

Parámetro de la función definida por el usuario. Es posible declarar uno o varios parámetros.

Una función puede tener un máximo de 2.100 parámetros. El usuario debe proporcionar el valor de cada parámetro declarado cuando se ejecuta la función, a menos que se defina un valor predeterminado para el parámetro.

Especifique un nombre de parámetro con una arroba (@) como primer carácter. El nombre del parámetro debe cumplir las mismas reglas que los identificadores. Los parámetros son locales para la función; los mismos nombres de parámetro se pueden utilizar en otras funciones. Los parámetros pueden tener lugar solo de constantes; no se pueden usar en lugar de nombres de tabla, nombres de columna o nombres de otros objetos de base de datos.

ANSI_WARNINGS no se respeta cuando se pasan parámetros en un procedimiento almacenado, una función definida por el usuario o cuando se declaran y establecen variables en una instrucción batch. Por ejemplo, si una variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan hasta el tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.

[ type_schema_name. ] parameter_data_type

Tipo de datos de parámetro y, opcionalmente, el esquema al que pertenece. Para las funciones de Transact-SQL, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario CLR y los tipos de tablas definidos por el usuario, a excepción del tipo de datos timestamp. Para las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario clR, excepto text, ntext, image, tipos de tabla definidos por el usuario y tipos de datos de marca de tiempo. Los tipos no escalados, el cursor y la tabla no se pueden especificar como un tipo de datos de parámetro en las funciones Transact-SQL o CLR.

Si no se especifica type_schema_name, el Motor de base de datos busca el scalar_parameter_data_type en el orden siguiente:

El esquema que contiene los nombres de los tipos de datos del sistema de SQL Server.
El esquema predeterminado del usuario actual en la base de datos actual.
El esquema dbo de la base de datos actual.

[ = valor predeterminado ]

Valor predeterminado para el parámetro . Si se define un valor default, la función se puede ejecutar sin especificar un valor para ese parámetro.

Los valores de parámetro predeterminados se pueden especificar para las funciones CLR, excepto para los tipos de datos varchar(max) y varbinary(max).

Cuando un parámetro de la función tiene un valor predeterminado, la palabra clave DEFAULT debe especificarse cuando se llama a la función para recuperar el valor predeterminado. Este comportamiento es distinto del uso de parámetros con valores predeterminados en los procedimientos almacenados, donde la omisión del parámetro implica especificar el valor predeterminado. Sin embargo, la DEFAULT palabra clave no es necesaria al invocar una función escalar mediante la EXECUTE instrucción .

READONLY

Indica que el parámetro no se puede actualizar ni modificar dentro de la definición de la función. READONLY es necesario para los parámetros de tipo de tabla (TVP) definidos por el usuario y no se puede usar para ningún otro tipo de parámetro.

return_data_type

Valor devuelto de una función escalar definida por el usuario. Para las funciones Transact-SQL, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario CLR, a excepción del tipo de datos timestamp. Para las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario CLR, a excepción de los tipos de datos text, ntext, image y timestamp. Los tipos no escalados, el cursor y la tabla no se pueden especificar como un tipo de datos devuelto en las funciones Transact-SQL o CLR.

function_body

Especifica que una serie de instrucciones Transact-SQL, que juntas no generan un efecto secundario, como modificar una tabla, definen el valor de la función. function_body solamente se usa en funciones escalares y en funciones con valores de tabla de múltiples instrucciones (MSTVF).

En las funciones escalares, function_body es una serie de instrucciones Transact-SQL que se evalúan como un valor escalar.

En MSTVFs, function_body es una serie de instrucciones Transact-SQL que rellenan una TABLE variable de retorno.

scalar_expression

Especifica el valor escalar que devuelve la función escalar.

TABLE

Especifica que el valor devuelto de la función con valores de tabla (TVF) es una tabla. Solamente se pueden pasar constantes y @local_variables a las TVF.

En las TVF insertadas, el TABLE valor devuelto se define a través de una sola SELECT instrucción. Las funciones insertadas no tienen variables de retorno asociadas.

En MSTVF, @return_variable es una TABLE variable, que se usa para almacenar y acumular las filas que se deben devolver como el valor de la función. @return_variable solamente se puede especificar para funciones Transact-SQL, no para funciones CLR.

select_stmt

Instrucción única SELECT que define el valor devuelto de una función insertada con valores de tabla (TVF).

ORDER (<order_clause>)

Especifica el orden en que los resultados se devuelven de la función con valores de tabla. Para obtener más información, consulte la sección Uso del criterio de ordenación en funciones con valores de tabla CLR más adelante en este artículo.

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

Se aplica a: SQL Server 2008 (10.0.x) SP 1 y versiones posteriores.

Especifica el ensamblado y el método al que deberá hacer referencia el nombre de la función creada.

nombre_de_ensamblado: debe coincidir con un valor en la columna name de SELECT * FROM sys.assemblies;.

Nombre que se usó en la CREATE ASSEMBLY instrucción .
nombre_de_clase: debe coincidir con un valor en la columna assembly_name de SELECT * FROM sys.assembly_modules;.

A menudo el valor contiene un punto incrustado. En tales casos, la sintaxis de Transact-SQL requiere que el valor esté enlazado con un par de corchetes ([]) o con un par de comillas dobles ("").
nombre_de_método: debe coincidir con un valor en la columna method_name de SELECT * FROM sys.assembly_modules;.

El método debe ser estático.

En un ejemplo típico de MyFood.dll, en el que todos los tipos están en el MyFood espacio de nombres , el EXTERNAL NAME valor podría ser MyFood.[MyFood.MyClass].MyStaticMethod.

De manera predeterminada, SQL Server no puede ejecutar código CLR. Puede crear, modificar y quitar objetos de base de datos que hacen referencia a módulos de Common Language Runtime. Sin embargo, no puede ejecutar estas referencias en SQL Server hasta que habilite la opción clr enabled. Para habilitarla, use sp_configure. Esta opción no está disponible en las bases de datos independientes.

<> table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

Define el tipo de datos de tabla para una función Transact-SQL. La declaración de tabla incluye definiciones de columna y restricciones de columna o de tabla. La tabla se coloca siempre en el grupo de archivos principal.

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

Se aplica a: SQL Server 2008 (10.0.x) SP 1 y versiones posteriores, y Azure SQL Database (versión preliminar en algunas regiones).

Define los tipos de datos de tabla para una función CLR. La declaración de tabla solamente incluye nombres de columna y tipos de datos. La tabla se coloca siempre en el grupo de archivos principal.

NULL | NOT NULL

Solo admite funciones escalares definidas por el usuario compiladas de forma nativa. Para obtener más información, vea Funciones escalares definidas por el usuario para OLTP en memoria.

NATIVE_COMPILATION

Indica si una función definida por el usuario se compila de forma nativa. Este argumento es obligatorio en funciones escalares definidas por el usuario compiladas de forma nativa.

BEGIN ATOMIC WITH

Requerido y solo compatible, para funciones escalares definidas por el usuario compiladas de forma nativa. Para obtener más información, vea Bloques atómicos en procedimientos nativos.

SCHEMABINDING

El SCHEMABINDING argumento es necesario para funciones escalares definidas por el usuario compiladas de forma nativa.

EXECUTE AS

EXECUTE AS es necesario para funciones escalares definidas por el usuario compiladas de forma nativa.

<> function_option ::= y <clr_function_option> ::=

Especifica que la función tiene una o varias de las siguientes opciones.

ENCRYPTION

Se aplica a: SQL Server 2008 (10.0.x) SP 1 y versiones posteriores.

Indica que el Motor de base de datos convierte el texto original de la CREATE FUNCTION instrucción en un formato ofuscado. La salida de la ofuscación no es visible directamente en ninguna vista de catálogo. Los usuarios que no tienen acceso a tablas del sistema ni a archivos de base de datos no pueden recuperar el texto ofuscado. Sin embargo, el texto está disponible para los usuarios con privilegios que pueden acceder a las tablas del sistema a través de la conexión de diagnóstico para los administradores de bases de datos o acceder directamente a los archivos de base de datos. Además, los usuarios que pueden adjuntar un depurador al proceso del servidor pueden recuperar el procedimiento original de la memoria en tiempo de ejecución. Para más información sobre cómo tener acceso al sistema, vea Configuración de visibilidad de los metadatos.

El uso de esta opción impide que la función se publique como parte de la replicación de SQL Server. Esta opción no se puede especificar para las funciones CLR.

SCHEMABINDING

Especifica que la función está enlazada a los objetos de base de datos a los que hace referencia. Cuando SCHEMABINDING se especifica , los objetos base no se pueden modificar de una manera que afecte a la definición de función. En primer lugar, se debe modificar o quitar la propia definición de la función para quitar las dependencias en el objeto que se va a modificar.

El enlace de la función a los objetos a los que hace referencia solamente se quita cuando se ejecuta una de las acciones siguientes:

Se quita la función.
La función se modifica con la instrucción ALTER sin especificar la opción SCHEMABINDING.

Una función se puede enlazar a esquema solamente si se cumplen las siguientes condiciones:

La función es una función de Transact-SQL.
Las funciones definidas por el usuario y las vistas a las que hace referencia la función también están enlazadas al esquema.
La función hace referencia a los objetos utilizando un nombre en dos partes.
La función y los objetos a los que hace referencia pertenecen a la misma base de datos.
El usuario que ejecutó la instrucción CREATE FUNCTION tiene permisos REFERENCES para los objetos de base de datos a los que hace referencia la función.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Especifica el OnNULLCall atributo de una función escalar. Si no se especifica, CALLED ON NULL INPUT está implícito de forma predeterminada. En otras palabras, el cuerpo de la función se ejecuta incluso si NULL se pasa como argumento.

Si RETURNS NULL ON NULL INPUT se especifica en una función CLR, indica que SQL Server puede devolver NULL cuando cualquiera de los argumentos que recibe es NULL, sin invocar realmente el cuerpo de la función. Si el método de una función CLR especificada en <method_specifier> ya tiene un atributo personalizado que indica RETURNS NULL ON NULL INPUT, pero la CREATE FUNCTION instrucción indica CALLED ON NULL INPUT, la CREATE FUNCTION instrucción tiene prioridad. El OnNULLCall atributo no se puede especificar para funciones con valores de tabla CLR.

EXECUTE AS

Especifica el contexto de seguridad en el que se ejecuta la función definida por el usuario. Por lo tanto, es posible controlar la cuenta de usuario que utiliza SQL Server para validar los permisos en los objetos de base de datos a los que hace referencia la función.

EXECUTE AS no se puede especificar para las funciones insertadas con valores de tabla.

Para obtener más información, vea EXECUTE AS (cláusula de Transact-SQL).

INLINE = { ON | OFF }

Se aplica a: SQL Server 2019 (15.x) y versiones posteriores, y Azure SQL Database.

Especifica si esta UDF escalar se debe insertar o no. Esta cláusula solo se aplica a las funciones escalares definidas por el usuario. La cláusula INLINE no es obligatoria. Si no se especifica la INLINE cláusula , se establece ON automáticamente en o OFF en función de si la UDF es inlineable. Si INLINE = ON se especifica pero se detecta que la UDF no es insertable, se produce un error. Para obtener más información, vea Scalar UDF inlining (Inserción de UDF escalar).

<> column_definition ::=

Define el tipo de datos de tabla. La declaración de tabla incluye definiciones de columna y restricciones. En las funciones CLR, solo se pueden especificar column_name y data_type.

column_name

Nombre de una columna de la tabla. Los nombres de columna se deben ajustar a las reglas para los identificadores y deben ser únicos en la tabla. column_name puede tener entre 1 y 128 caracteres.

data_type

Especifica el tipo de datos de la columna. En las funciones Transact-SQL, se permiten todos los tipos de datos, incluidos los tipos CLR definidos por el usuario, a excepción de timestamp. Para las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario clR, excepto text, ntext, image, char, varchar, varchar(max)y timestamp. El cursor de tipo no escalado no se puede especificar como un tipo de datos de columna en funciones Transact-SQL o CLR.

DEFAULT constant_expression

Especifica el valor suministrado para la columna cuando no se ha especificado explícitamente un valor durante una inserción. constant_expression es una constante, NULLo un valor de función del sistema. DEFAULT las definiciones se pueden aplicar a cualquier columna excepto a las que tienen la IDENTITY propiedad . DEFAULT no se puede especificar para funciones con valores de tabla CLR.

COLLATE collation_name

Especifica la intercalación de la columna. Si no se especifica, se asigna a la columna la intercalación predeterminada de la base de datos. El nombre de intercalación puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Para obtener una lista y más información sobre las intercalaciones, consulte Nombre de intercalación de Windows (Transact-SQL) y Nombre de intercalación de SQL Server (Transact-SQL).

La COLLATE cláusula se puede usar para cambiar las intercalaciones solo de las columnas de los tipos de datos char, varchar, nchar y nvarchar . COLLATE no se puede especificar para funciones con valores de tabla CLR.

ROWGUIDCOL

Indica que la nueva columna es una columna de identificador único global de fila. Solo se puede designar una columna uniqueidentifier por tabla como columna ROWGUIDCOL . La ROWGUIDCOL propiedad solo se puede asignar a una columna uniqueidentifier .

La ROWGUIDCOL propiedad no aplica la unicidad de los valores almacenados en la columna. Tampoco genera automáticamente valores para las nuevas filas insertadas en la tabla. Para generar valores únicos para cada columna, use la función en INSERT instrucciones NEWID . Se puede especificar un valor predeterminado; sin embargo, NEWID no se puede especificar como valor predeterminado.

IDENTITY

Indica que la nueva columna es una columna de identidad. Cuando se agrega una nueva fila a la tabla, SQL Server proporciona un valor incremental único para la columna. Normalmente, las columnas de identidad se usan junto con PRIMARY KEY restricciones para servir como identificador de fila único para la tabla. La propiedad IDENTITY se puede asignar a columnas tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). Solo se puede crear una columna de identidad para cada tabla. Los valores predeterminados y DEFAULT restricciones enlazados no se pueden usar con una columna de identidad. Se debe especificar los dos argumentos, seed e increment, o ninguno. Si no se especifica ninguno, el valor predeterminado es (1,1).

IDENTITY no se puede especificar para funciones con valores de tabla CLR.

seed

Valor entero que se va a asignar a la primera fila de la tabla.

increment

Valor entero que se va a agregar al valor de inicialización para las filas sucesivas de la tabla.

<> column_constraint ::= y <table_constraint> ::=

Define la restricción para la columna o tabla especificada. En el caso de las funciones CLR, el único tipo de restricción permitido es NULL. No se permiten restricciones con nombre.

NULL | NOT NULL

Determina si se permiten valores NULL en la columna. NULL no es estrictamente una restricción, pero se puede especificar igual NOT NULLque . NOT NULL no se puede especificar para funciones con valores de tabla CLR.

PRIMARY KEY

Restricción que exige la integridad de la entidad para una columna especificada a través de un índice único. En las funciones definidas por el usuario con valores de tabla, la PRIMARY KEY restricción solo se puede crear en una columna por tabla. PRIMARY KEY no se puede especificar para funciones con valores de tabla CLR.

UNIQUE

Una restricción que proporciona la integridad de entidad para una o varias columnas especificadas a través de un índice único. Una tabla puede tener varias UNIQUE restricciones. UNIQUE no se puede especificar para funciones con valores de tabla CLR.

CLUSTERED | NONCLUSTERED

Indique que se crea un índice agrupado o no clúster para la PRIMARY KEY restricción o UNIQUE . PRIMARY KEY las restricciones usan CLUSTERED, y UNIQUE las restricciones usan NONCLUSTERED.

CLUSTERED solo se puede especificar para una restricción. Si CLUSTERED se especifica para una UNIQUE restricción y también se especifica una PRIMARY KEY restricción, usa PRIMARY KEYNONCLUSTERED.

CLUSTERED y NONCLUSTERED no se pueden especificar para funciones con valores de tabla CLR.

CHECK

Es una restricción que exige la integridad del dominio limitando los valores posibles que se pueden escribir en una o varias columnas. CHECK No se pueden especificar restricciones para las funciones con valores de tabla CLR.

logical_expression

Expresión lógica que devuelve TRUE o FALSE.

<> computed_column_definition ::=

Especifica una columna calculada. Para obtener más información sobre las columnas calculadas, consulte CREATE TABLE (Transact-SQL).

column_name

Nombre de la columna calculada.

computed_column_expression

Una expresión que define el valor de una columna calculada.

<index_option> ::=

Especifica las opciones de índice para el PRIMARY KEY índice o UNIQUE . Para más información sobre las opciones de índice, vea CREATE INDEX (Transact-SQL).

PAD_INDEX = { ON | OFF }

Especifica el relleno del índice. El valor predeterminado es OFF.

FILLFACTOR = fillfactor

Especifica un porcentaje que indica cuánto debe llenar el Motor de base de datos el nivel hoja de cada página de índice durante la creación o modificación de los índices. fillfactor debe ser un valor entero comprendido entre 1 y 100. El valor predeterminado es 0.

IGNORE_DUP_KEY = { ON | OFF }

Especifica la respuesta de error cuando una operación de inserción intenta insertar valores de clave duplicados en un índice único. La opción IGNORE_DUP_KEY se aplica solamente a operaciones de inserción realizadas tras crear o volver a generar el índice. El valor predeterminado es OFF.

STATISTICS_NORECOMPUTE = { ON | OFF}

Especifica si se vuelven a calcular las estadísticas de distribución. El valor predeterminado es OFF.

ALLOW_ROW_LOCKS = { ON | OFF }

Especifica si se permiten los bloqueos de fila. El valor predeterminado es ON.

ALLOW_PAGE_LOCKS = { ON | OFF }

Especifica si se permiten bloqueos de página. El valor predeterminado es ON.

procedimientos recomendados

Si no se crea una función definida por el usuario con la SCHEMABINDING cláusula , los cambios realizados en objetos subyacentes pueden afectar a la definición de la función y generar resultados inesperados cuando se invoca. Recomendamos implementar uno de los siguientes métodos para garantizar que la función no queda sin actualizar como consecuencia de los cambios realizados en sus objetos subyacentes:

Especifique la WITH SCHEMABINDING cláusula al crear la función. Esta opción garantiza que los objetos a los que se hace referencia en la definición de función no se puedan modificar, a menos que también se modifique la función.
Ejecute el procedimiento almacenado sp_refreshsqlmodule después de modificar cualquier objeto que se especifique en la definición de la función.

Para obtener más información y consideraciones de rendimiento sobre las funciones insertadas con valores de tabla (TVF en línea) y las funciones con valores de tabla de varias instrucciones (MSTVF), consulte Creación de funciones definidas por el usuario (Motor de base de datos).

Tipos de datos

Si se especifican parámetros en una función CLR, deben ser de tipos de SQL Server tal como se definieron anteriormente para scalar_parameter_data_type. Para obtener más información sobre la comparación de tipos de datos del sistema de SQL Server con tipos de datos de integración clR o tipos de datos de Common Language Runtime de .NET Framework, consulte Asignación de datos de parámetros CLR.

Para que SQL Server haga referencia al método correcto cuando está sobrecargado en una clase, el método indicado en <method_specifier> debe tener las siguientes características:

Reciba el mismo número de parámetros que se especifica en [ , ...n ].
Recibir todos los parámetros por valor y no por referencia.
Use tipos de parámetros compatibles con los tipos especificados en la función de SQL Server.

Si el tipo de datos devuelto de la función CLR especifica un tipo de tabla (RETURNS TABLE), el tipo de datos devuelto del método en <method_specifier> debe ser de tipo IEnumerator o IEnumerabley supone que la interfaz la implementa el creador de la función. A diferencia de las funciones de Transact-SQL, las funciones CLR no pueden incluir PRIMARY KEYrestricciones , UNIQUEo CHECK en <table_type_definition>. Los tipos de datos de las columnas especificadas en <table_type_definition> deben coincidir con los tipos de las columnas correspondientes del conjunto de resultados que devuelve el método en <method_specifier> en tiempo de ejecución. Esta comprobación de tipos no se realiza en el momento en que se crea la función.

Para más información sobre la programación de funciones CLR, vea CLR User-Defined Functions (Funciones CLR definidas por el usuario).

Comentarios

Se pueden invocar funciones escalares en las que se usan expresiones escalares, que incluyen columnas calculadas y CHECK definiciones de restricciones. Las funciones escalares también se pueden ejecutar mediante la instrucción EXECUTE (Transact-SQL). Las funciones escalares deben invocarse mediante al menos el nombre de dos partes de la función (<schema>.<function>). Para más información sobre los nombres con varias partes, vea Convenciones de sintaxis de Transact-SQL (Transact-SQL). Las funciones con valores de tabla se pueden invocar cuando se admiten expresiones de tabla en la cláusula FROM de instrucciones SELECT, INSERT, UPDATE o DELETE. Para obtener más información, consulte Ejecución de funciones definidas por el usuario.

Interoperabilidad

Las siguientes instrucciones son válidas en una función:

Instrucciones de asignación.
Instrucciones de control de flujo excepto instrucciones TRY...CATCH.
Instrucciones DECLARE que definen variables de datos y cursores locales.
Instrucciones SELECT que contienen listas de selección con expresiones que asignan valores a variables locales.
Operaciones de cursor que hacen referencia a cursores locales que se declaran, abren, cierran y cuya asignación se cancela en la función. Solo FETCH se permiten instrucciones que asignan valores a variables locales mediante la INTO cláusula ; FETCH no se permiten instrucciones que devuelven datos al cliente.
Instrucciones INSERT, UPDATE y DELETE que modifican variables de tabla locales.
Instrucciones EXECUTE que llaman a procedimientos almacenados extendidos.

Para obtener más información, consulte Creación de funciones definidas por el usuario (Motor de base de datos).

Interoperabilidad de columna calculada

Las funciones tienen las propiedades siguientes. Los valores de estas propiedades determinan si las funciones se pueden utilizar en columnas calculadas, que pueden ser persistentes o indizadas.

Propiedad	Descripción	Notas
`IsDeterministic`	La función es determinista o no determinista.	En las funciones deterministas, se permite el acceso a los datos locales. Por ejemplo, las funciones que siempre devuelven el mismo resultado siempre que se llama mediante un conjunto específico de valores de entrada y con el mismo estado de la base de datos se etiquetarían deterministas.
`IsPrecise`	La función es precisa o imprecisa.	Las funciones imprecisas contienen operaciones, como operaciones de punto flotante.
`IsSystemVerified`	SQL Server puede comprobar las propiedades de precisión y determinismo de la función.
`SystemDataAccess`	La función tiene acceso a los datos del sistema (catálogos del sistema o tablas del sistema virtuales) en la instancia local de SQL Server.
`UserDataAccess`	La función tiene acceso a los datos del usuario en la instancia local de SQL Server.	Incluye las tablas temporales y las definidas por el usuario, pero no las variables de tabla.

SQL Server determina automáticamente las propiedades de precisión y determinismo de las funciones de Transact-SQL. El usuario puede especificar las propiedades de acceso a datos y determinismo de las funciones CLR. Para obtener más información, consulte Integración de CLR: atributos personalizados para rutinas CLR.

Para mostrar los valores actuales de estas propiedades, use OBJECTPROPERTYEX (Transact-SQL).

Importante

Se deben crear las funciones de manera que el SCHEMABINDING sea determinista.

Una columna calculada que llama a una función definida por el usuario se puede utilizar en un índice cuando la función definida por el usuario tiene los siguientes valores de propiedades:

IsDeterministic es true.
IsSystemVerified es true (a menos que se conserve la columna calculada)
UserDataAccess es false.
SystemDataAccess es false.

Para obtener más información, consulte Índices en columnas calculadas.

Llamada a procedimientos almacenados extendidos desde funciones

El procedimiento almacenado extendido, al llamarlo desde dentro de una función, no puede devolver conjuntos de resultados al cliente. Las API ods que devuelven conjuntos de resultados al cliente, devuelven FAIL. El procedimiento almacenado extendido podría volver a conectarse a una instancia de SQL Server; sin embargo, no debe intentar unirse a la misma transacción que la función que invocó el procedimiento almacenado extendido.

De forma similar a las invocaciones de un procedimiento almacenado o por lotes, el procedimiento almacenado extendido se ejecuta en el contexto de la cuenta de seguridad de Windows en la que se ejecuta SQL Server. El propietario del procedimiento almacenado debe tener en cuenta este escenario al conceder EXECUTE permiso a los usuarios.

Limitaciones

Las funciones definidas por el usuario no se pueden utilizar para realizar acciones que modifican el estado de la base de datos.

Las funciones definidas por el usuario no pueden tener una cláusula OUTPUT INTO que tenga una tabla como destino.

Las siguientes instrucciones de Service Broker no se pueden incluir en la definición de una función definida por el usuario de Transact-SQL:

BEGIN DIALOG CONVERSATION
END CONVERSATION
GET CONVERSATION GROUP
MOVE CONVERSATION
RECEIVE
SEND

Las funciones definidas por el usuario se pueden anidar; es decir, una función definida por el usuario puede llamar a otra. El nivel de anidamiento aumenta cuando se empieza a ejecutar la función llamada y disminuye cuando se termina de ejecutar la función llamada. Las funciones definidas por el usuario se pueden anidar hasta un máximo de 32 niveles. Si se superan los niveles máximos de anidamiento, la cadena completa de funciones de llamada produce un error. Cualquier referencia a código administrado desde una función Transact-SQL definida por el usuario cuenta como uno de los 32 niveles de anidamiento. Los métodos invocados desde el código administrado no cuentan para este límite.

Uso del criterio de ordenación en funciones con valores de tabla CLR

Cuando utilice la cláusula ORDER en funciones CLR con valores de tabla, siga estas instrucciones:

Debe asegurarse de que los resultados siempre se ordenan según el criterio especificado. Si los resultados no están en el orden especificado, SQL Server genera un mensaje de error cuando se ejecuta la consulta.
Si se especifica una cláusula ORDER, el resultado de la función con valores de tabla debe estar ordenado según la intercalación de la columna (explícita o implícita). Por ejemplo, si la intercalación de columna es china, los resultados devueltos deben ordenarse según las reglas de ordenación chinas. (La intercalación se especifica en el DDL para la función con valores de tabla o se obtiene de la intercalación de la base de datos).
SQL Server siempre comprueba la ORDER cláusula si se especifica, mientras devuelve resultados, independientemente de si el procesador de consultas lo usa o no para realizar más optimizaciones. Use solo la ORDER cláusula si sabe que es útil para el procesador de consultas.
El procesador de consultas de SQL Server utiliza de forma automática la cláusula ORDER en los siguientes casos:
- Consultas Insert en las que la cláusula ORDER es compatible con un índice.
- Cláusulas ORDER BY que son compatibles con la cláusula ORDER.
- Agregados, donde GROUP BY es compatible con cláusula ORDER.
- Agregados DISTINCT donde las columnas distintas son compatibles con la cláusula ORDER.

La ORDER cláusula no garantiza resultados ordenados cuando se ejecuta una SELECT consulta, a menos ORDER BY que también se especifique en la consulta. Consulte sys.function_order_columns (Transact-SQL) para obtener más información sobre cómo consultar las columnas incluidas en el criterio de ordenación para las funciones con valores de tabla.

Metadatos

En la siguiente tabla se enumeran las vistas de catálogo del sistema que puede utilizar para devolver metadatos sobre las funciones definidas por el usuario.

Vista de sistema	Descripción
sys.sql_modules	Vea el ejemplo E en la sección Ejemplos.
sys.assembly_modules	Muestra información sobre funciones definidas por el usuario CLR.
sys.parameters	Muestra información sobre los parámetros definidos en funciones definidas por el usuario.
sys.sql_expression_dependencies	Muestra los objetos subyacentes a los que hace referencia una función.

Permisos

Se requiere el permiso CREATE FUNCTION en la base de datos y el permiso ALTER en el esquema en el que se va a crear la función. Si la función especifica un tipo definido por el usuario, requiere el permiso EXECUTE en el tipo.

Ejemplos

Para obtener más ejemplos y consideraciones de rendimiento sobre las UDF, consulte Creación de funciones definidas por el usuario (Motor de base de datos).

A Usar una función definida por el usuario con valores escalares que calcula la semana ISO

En el ejemplo siguiente se crea la función definida por el usuario ISOweek. Esta función toma un argumento de fecha para calcular el número de semana ISO. Para que esta función calcule correctamente, se debe invocar SET DATEFIRST 1 antes de llamar a la función.

En el ejemplo también se muestra el uso de la cláusula EXECUTE AS (Transact-SQL) para especificar el contexto de seguridad en el que se puede ejecutar un procedimiento almacenado. En el ejemplo, la opción CALLER especifica que el procedimiento se ejecuta en el contexto del usuario que lo llama. Las otras opciones que puede especificar son SELF, OWNER, y user_name.

Esta es la llamada de función. El valor de DATEFIRST está establecido en 1.

CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @ISOweek INT;

    SET @ISOweek = DATEPART(wk, @DATE) + 1 -
        DATEPART(wk, CAST(DATEPART(yy, @DATE) AS CHAR(4)) + '0104');

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0)
        SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy, @DATE) - 1 AS CHAR(4))
           + '12' + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2))) + 1;

    --Special case: Dec 29-31 may belong to the next year
    IF ((DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28))
    SET @ISOweek = 1;

    RETURN (@ISOweek);
END;
GO

SET DATEFIRST 1;

SELECT dbo.ISOweek(CONVERT(DATETIME, '12/26/2004', 101)) AS 'ISO Week';

El conjunto de resultados es el siguiente:

ISO Week
----------------
52

B. Creación de una función alineada con valores de tabla

En el ejemplo siguiente se devuelve una función insertada con valores de tabla en la base de datos AdventureWorks2022. Devuelve tres columnas ProductID, Name y el agregado de ventas totales anuales hasta la fecha por tienda como YTD Total para cada producto vendido a la tienda.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid INT)
RETURNS TABLE
AS
RETURN (
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    INNER JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    INNER JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    INNER JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

Para invocar la función, ejecute esta consulta.

SELECT * FROM Sales.ufn_SalesByStore (602);

C. Creación de una función con valores de tabla de varias instrucciones

En el ejemplo siguiente se crea la función insertada con valores de tabla fn_FindReports(InEmpID)en la base de datos AdventureWorks2022. Cuando se suministra un identificador de empleado válido, la función devuelve una tabla de todos los empleados que están bajo las órdenes de ese empleado tanto directa como indirectamente. La función utiliza la expresión de tabla común (CTE) recursiva para producir la lista jerárquica de empleados. Para obtener más información sobre las CTE recursivas, consulte WITH common_table_expression (Transact-SQL).

CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INT)
RETURNS @retFindReports TABLE (
    EmployeeID INT PRIMARY KEY NOT NULL,
    FirstName NVARCHAR(255) NOT NULL,
    LastName NVARCHAR(255) NOT NULL,
    JobTitle NVARCHAR(50) NOT NULL,
    RecursionLevel INT NOT NULL
    )
    --Returns a result set that lists all the employees who report to the
    --specific employee directly or indirectly.
AS
BEGIN
    WITH EMP_cte (
        EmployeeID,
        OrganizationNode,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
        ) -- CTE name and columns
    AS (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
            OrganizationNode = ISNULL(e.OrganizationNode, CAST('/' AS HIERARCHYID)),
            p.FirstName,
            p.LastName,
            e.JobTitle,
            0
        FROM HumanResources.Employee e
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        
        UNION ALL
        
        -- Join recursive member to anchor
        SELECT e.BusinessEntityID,
            e.OrganizationNode,
            p.FirstName,
            p.LastName,
            e.JobTitle,
            RecursionLevel + 1
        FROM HumanResources.Employee e
        INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
        INNER JOIN Person.Person p
            ON p.BusinessEntityID = e.BusinessEntityID
        )
    -- Copy the required columns to the result of the function
    INSERT @retFindReports
    SELECT EmployeeID,
        FirstName,
        LastName,
        JobTitle,
        RecursionLevel
    FROM EMP_cte

    RETURN
END;
GO

-- Example invocation
SELECT EmployeeID,
    FirstName,
    LastName,
    JobTitle,
    RecursionLevel
FROM dbo.ufn_FindReports(1);
GO

D. Creación de una función CLR

En el ejemplo se crea la función CLR len_s. Antes de crear la función, el ensamblado SurrogateStringFunction.dll se registra en la base de datos local.

Se aplica a: SQL Server 2008 (10.0.x) SP 1 y versiones posteriores.

DECLARE @SamplesPath NVARCHAR(1024);

-- You may have to modify the value of this variable if you have
-- installed the sample in a location other than the default location.
SELECT @SamplesPath = REPLACE(physical_name,
    'Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf',
    'Microsoft SQL Server\130\Samples\Engine\Programmability\CLR\'
)
FROM master.sys.database_files
WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str NVARCHAR(4000))
RETURNS BIGINT
AS
EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

Para ver un ejemplo de creación de una función CLR con valores de tabla, vea CLR Table-Valued Functions (Funciones CLR con valores de tabla).

E. Mostrar la definición de funciones definidas por el usuario

SELECT DEFINITION,
    type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
    AND type IN ('FN', 'IF', 'TF');
GO

La definición de funciones creadas mediante la ENCRYPTION opción no se puede ver mediante sys.sql_modules; sin embargo, se muestra otra información sobre las funciones cifradas.

CREATE FUNCTION (Transact-SQL)

Sintaxis

Argumentos

OR ALTER

schema_name

function_name

@parameter_name

[ type_schema_name. ] parameter_data_type

[ = valor predeterminado ]

READONLY

return_data_type

function_body

scalar_expression

TABLE

select_stmt

ORDER (<order_clause>)

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

<> table_type_definition ( { <column_definition><column_constraint | <> computed_column_definition } [ <table_constraint> ] [ , ...n ] )

<> clr_table_type_definition ( { column_namedata_type } [ , ...n ] )

NULL | NOT NULL

NATIVE_COMPILATION

BEGIN ATOMIC WITH

SCHEMABINDING

EXECUTE AS

<> function_option ::= y <clr_function_option> ::=

ENCRYPTION

SCHEMABINDING

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

EXECUTE AS

INLINE = { ON | OFF }

<> column_definition ::=

column_name

data_type

DEFAULT constant_expression

COLLATE collation_name

ROWGUIDCOL

IDENTITY

seed

increment

<> column_constraint ::= y <table_constraint> ::=

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED | NONCLUSTERED

CHECK

logical_expression

<> computed_column_definition ::=

column_name

computed_column_expression

<index_option> ::=

PAD_INDEX = { ON | OFF }

FILLFACTOR = fillfactor

IGNORE_DUP_KEY = { ON | OFF }

STATISTICS_NORECOMPUTE = { ON | OFF}

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

procedimientos recomendados

Tipos de datos

Comentarios

Interoperabilidad

Interoperabilidad de columna calculada

Llamada a procedimientos almacenados extendidos desde funciones

Limitaciones

Uso del criterio de ordenación en funciones con valores de tabla CLR

Metadatos

Permisos

Ejemplos

A Usar una función definida por el usuario con valores escalares que calcula la semana ISO

B. Creación de una función alineada con valores de tabla

C. Creación de una función con valores de tabla de varias instrucciones

D. Creación de una función CLR

E. Mostrar la definición de funciones definidas por el usuario

Contenido relacionado

Comentarios

Comentarios

Recursos adicionais