Compartir a través de

FUNCIÓN CREATE (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de datos de Azure SQL de Microsoft Fabric

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.

Use CREATE FUNCTION para crear una rutina de T-SQL reutilizable que se pueda usar de estas maneras:

  • En Transact-SQL declaraciones como SELECT
  • En las 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
  • Uso de una función insertada como predicado de filtro para una política de seguridad

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

Nota:

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

Sugerencia

Puede especificar CREATE OR ALTER FUNCTION para crear una nueva función si no existe por ese nombre, o modificar una función existente, en una sola instrucción.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para funciones escalares 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 Transact-SQL funciones insertadas con valores de tabla.

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 Transact-SQL funciones con valores de tabla de varias instrucciones.

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 de Transact-SQL cláusulas de función.

<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 de las 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 de las 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 de las 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 de 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 ]
}

Argumentos

O ALTER

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

Altera 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 función deben cumplir con las reglas para identificadores y deben ser únicos dentro de 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. Se pueden declarar uno o más parámetros.

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

Especifique un nombre de parámetro utilizando un signo arroba (@) como primer carácter. El nombre del parámetro debe cumplir con las reglas de los identificadores. Los parámetros son locales para la función; Los mismos nombres de parámetros se pueden utilizar en otras funciones. Los parámetros solo pueden tomar el lugar de las constantes; No se pueden usar en lugar de nombres de tablas, nombres de columnas o los 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 por lotes. 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

El tipo de datos del parámetro y, opcionalmente, el esquema al que pertenece. En el caso de las funciones Transact-SQL, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario de CLR y los tipos de tabla definidos por el usuario, excepto el tipo de datos de marca de tiempo . En el caso de las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario de CLR, excepto text, ntext, image, los tipos de tabla definidos por el usuario y los tipos de datos de marca de tiempo . Los tipos no escalares, cursor y 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 en scalar_parameter_data_type el orden siguiente:

  • 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 en la base de datos actual.

[ = predeterminado ]

Valor predeterminado del parámetro. Si se define un valor predeterminado , 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, se debe especificar la palabra DEFAULT clave cuando se llama a la función para recuperar el valor predeterminado. Este comportamiento es diferente del uso de parámetros con valores predeterminados en procedimientos almacenados, en los que la omisión del parámetro también implica el valor predeterminado. Sin embargo, la DEFAULT palabra clave no es necesaria cuando se invoca una función escalar mediante la EXECUTE instrucción.

SOLO LECTURA

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 definidos por el usuario (TVP) 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 de CLR, excepto el tipo de datos de marca de tiempo . En el caso de las funciones CLR, se permiten todos los tipos de datos, incluidos los tipos definidos por el usuario de CLR, excepto los tipos de datos text, ntext, image y timestamp . Los tipos no escalares, cursor y tabla, no se pueden especificar como tipo de datos devueltos en las funciones Transact-SQL o CLR.

function_body

Especifica que una serie de instrucciones Transact-SQL, que juntas no producen un efecto secundario, como la modificación de una tabla, definen el valor de la función. function_body solo se utiliza en funciones escalares y funciones con valores de tabla de varias instrucciones (MSTVF).

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

En MSTVF, 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.

TABLA

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

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

En las funciones con valores de tabla de varias instrucciones (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 solo se puede especificar para las funciones Transact-SQL y no para las funciones CLR.

select_stmt

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

PEDIDO (<order_clause>)

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

NOMBRE EXTERNO <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 hará referencia el nombre de la función creada.

  • assembly_name - debe coincidir con un valor en la name columna de SELECT * FROM sys.assemblies;.

    El nombre que se usó en la CREATE ASSEMBLY declaración.

  • class_name - debe coincidir con un valor en la assembly_name columna de SELECT * FROM sys.assembly_modules;.

    A menudo, el valor contiene un punto o punto incrustado. En tales casos, la sintaxis Transact-SQL requiere que el valor esté delimitado con un par de corchetes ([]) o con un par de comillas dobles ("").

  • method_name - debe coincidir con un valor en la method_name columna 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 espacio de MyFood nombres, el EXTERNAL NAME valor podría ser MyFood.[MyFood.MyClass].MyStaticMethod.

De forma predeterminada, SQL Server no puede ejecutar código CLR. Puede crear, modificar y quitar objetos de base de datos que hagan 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 habilitar esta opción, use sp_configure. Esta opción no está disponible en una base de datos independiente.

< > 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 tabla. La tabla siempre se coloca 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 incluye solo nombres de columna y tipos de datos. La tabla siempre se coloca en el grupo de archivos principal.

NULL | NO NULL

Solo se admite para funciones escalares definidas por el usuario compiladas de forma nativa. Para obtener más información, consulte Funciones escalares User-Defined para OLTP In-Memory.

COMPILACIÓN_NATIVA

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

COMIENCE ATOMIC CON

Obligatorio, y solo se admite, 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 las funciones escalares definidas por el usuario compiladas de forma nativa.

EXECUTE AS

EXECUTE AS es necesario para las 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.

ENCRIPTACIÓN

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

Indica que Motor de base de datos convierte el texto original de la CREATE FUNCTION instrucción a 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 las tablas del sistema o a los archivos de la base de datos no pueden recuperar el texto ofuscado. Sin embargo, el texto está disponible para los usuarios privilegiados que pueden acceder a las tablas del sistema a través de la conexión de diagnóstico para 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 obtener más información sobre cómo acceder a los metadatos del sistema, consulte Configuración de visibilidad de 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 la función. Primero se debe modificar o quitar la propia definición de función para eliminar las dependencias del objeto que se va a modificar.

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

  • La función se elimina.
  • La función se modifica mediante la ALTER instrucción con la SCHEMABINDING opción no especificada.

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

  • La función es una función Transact-SQL.
  • Las funciones y vistas definidas por el usuario a las que hace referencia la función también están enlazadas al esquema.
  • Se hace referencia a los objetos a los que hace referencia la función mediante un nombre de 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 CREATE FUNCTION instrucción tiene REFERENCES permiso sobre los objetos de base de datos a los que hace referencia la función.

DEVUELVE NULL EN UNA ENTRADA NULA | LLAMADO EN UNA ENTRADA NULA

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 alguno 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 las funciones con valores de tabla de CLR.

EXECUTE AS

Especifica el contexto de seguridad en el que se ejecuta la función definida por el usuario. Por lo tanto, puede controlar qué cuenta de usuario usa 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 con valores de tabla en línea.

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

EN LÍNEA = { EN | APAGADO }

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

Especifica si esta UDF escalar debe estar insertada o no. Esta cláusula solo se aplica a las funciones escalares definidas por el usuario. La INLINE cláusula no es obligatoria. Si no se especifica la INLINE cláusula, se establece automáticamente en ON o OFF en función de si la UDF es inlineable. Si INLINE = ON se especifica, pero se determina que la UDF no se puede insertar, se produce un error. Para obtener más información, consulte inserción de UDF escalar.

< > column_definition ::=

Define el tipo de datos de la tabla. La declaración de tabla incluye definiciones de columna y restricciones. En el caso de 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 deben cumplir las reglas de identificadores y deben ser únicos en la tabla. column_name puede constar de 1 a 128 caracteres.

data_type

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

DEFAULT constant_expression

Especifica el valor proporcionado para la columna cuando no se proporciona 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 las funciones con valores de tabla de CLR.

COTEJAR collation_name

Especifica la intercalación de la columna. Si no se especifica, a la columna se le asigna 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 acerca de las intercalaciones, vea Nombre de intercalación de Windows (Transact-SQL) y Nombre de intercalación de SQL Server (Transact-SQL).

La COLLATE cláusula solo se puede utilizar para cambiar las intercalaciones de las columnas de los tipos de datos char, varchar, ncar y nvarchar . COLLATE no se puede especificar para las funciones con valores de tabla de 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 ROWGUIDCOL columna. La ROWGUIDCOL propiedad solo se puede asignar a una columna uniqueidentifier .

La ROWGUIDCOL propiedad no exige 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, utilice la NEWID función on INSERT statements. Se puede especificar un valor predeterminado; sin embargo, NEWID no se puede especificar como valor predeterminado.

IDENTIDAD

Indica que la nueva columna es una columna de identidad. Cuando se agrega una nueva fila a la tabla, SQL Server proporciona un valor único e incremental para la columna. Normalmente, las columnas de identidad se utilizan junto con PRIMARY KEY las restricciones para actuar como identificador de fila único de la tabla. La IDENTITY propiedad se puede asignar a columnas tinyint, smallint, int, bigint, decimal(p,0) o numeric(p,0). Solo se puede crear una columna de identidad por tabla. Los valores predeterminados enlazados y DEFAULT las restricciones no se pueden usar con una columna de identidad. Debe especificar tanto la inicialización como el incremento o ninguno. Si no se especifica ninguno, el valor predeterminado es (1,1).

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

semilla

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

incremento

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

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

Define la restricción de una columna o tabla especificada. En el caso de las funciones CLR, el único tipo de restricción permitido es NULL. Las restricciones con nombre no están permitidas.

NULL | NO NULL

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

CLAVE PRINCIPAL

Restricción que aplica 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 las funciones con valores de tabla de CLR.

ÚNICO

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

AGRUPADO | NO AGRUPADO

Indica que se crea un índice agrupado o no agrupado para la PRIMARY KEY restricción or UNIQUE . PRIMARY KEY Las restricciones utilizan CLUSTERED, y UNIQUE las restricciones utilizan 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, el PRIMARY KEY utiliza NONCLUSTERED.

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

COMPROBAR

Restricción que aplica la integridad del dominio limitando los valores posibles que se pueden especificar en una columna o columnas. CHECK No se pueden especificar restricciones para las funciones con valores de tabla de CLR.

logical_expression

Una expresión lógica que devuelve TRUE o FALSE.

< > computed_column_definition ::=

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

column_name

Nombre de la columna calculada.

computed_column_expression

Expresión que define el valor de una columna calculada.

< > index_option ::=

Especifica las opciones de índice para el PRIMARY KEY índice or UNIQUE . Para obtener más información acerca de 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 el grado de llenado del motor de base de datos (Motor de base de datos) en el nivel hoja de cada página de índice durante la creación o el cambio del índice. fillfactor debe ser un valor entero de 1 a 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 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 cláusula, los SCHEMABINDING cambios que se realizan en los objetos subyacentes pueden afectar a la definición de la función y producir 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 sp_refreshsqlmodule procedimiento almacenado después de modificar cualquier objeto especificado en la definición de la función.

Para obtener más información y consideraciones de rendimiento acerca de las funciones con valores de tabla insertadas (TVF insertadas) y las funciones con valores de tabla de varias instrucciones (MSTVF), vea Crear funciones definidas por el usuario (Motor de base de datos).

Tipos de datos

Si los parámetros se especifican en una función CLR, deben ser tipos de SQL Server tal y como se definieron anteriormente para scalar_parameter_data_type. Para obtener más información sobre la comparación de los tipos de datos del sistema de SQL Server con los tipos de datos de integración de CLR o los tipos de datos de Common Language Runtime de .NET Framework, vea Asignación de datos de parámetros de 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 ].
  • Reciba todos los parámetros por valor, no por referencia.
  • Utilice tipos de parámetros que sean compatibles con los tipos especificados en la función de SQL Server.

Si el tipo de datos devueltos de la función CLR especifica un tipo de tabla (RETURNS TABLE), el tipo de datos devueltos del método en <method_specifier> debe ser de tipo IEnumerator o IEnumerable, y supone que el creador de la función implementa la interfaz. A diferencia de Transact-SQL funciones, las funciones CLR no pueden incluir PRIMARY KEY, UNIQUEo CHECK restricciones 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 devuelto por el método en <method_specifier> tiempo de ejecución. Esta comprobación de tipos no se realiza en el momento en que se crea la función.

Para obtener más información acerca de cómo programar funciones CLR, vea CLR User-Defined Functions.

Observaciones

Las funciones escalares se pueden invocar cuando se utilizan expresiones escalares, lo que incluye columnas calculadas y CHECK definiciones de restricción. Las funciones escalares también se pueden ejecutar mediante la instrucción EXECUTE (Transact-SQL). Las funciones escalares deben invocarse como mínimo con el nombre de dos partes de la función (<schema>.<function>). Para obtener más información acerca de los nombres de varias partes, vea Transact-SQL Convenciones de sintaxis (Transact-SQL). Las funciones con valores de tabla se pueden invocar cuando se permiten expresiones de tabla en la FROM cláusula de SELECT, INSERT, UPDATE, o DELETE instrucciones. Para obtener más información, consulte Ejecutar funciones definidas por el usuario.

Interoperabilidad

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

  • Declaraciones de asignación.
  • Instrucciones de control de flujo, excepto TRY...CATCH las instrucciones.
  • DECLARE sentencias que definen variables de datos locales y cursores locales.
  • SELECT Instrucciones que contienen listas SELECT con expresiones que asignan valores a variables locales.
  • Operaciones de cursor que hacen referencia a cursores locales declarados, abiertos, cerrados y desasignados en la función. Solo FETCH se permiten instrucciones que asignen valores a variables locales mediante la INTO cláusula; FETCH no se permiten instrucciones que devuelvan datos al cliente.
  • INSERT, UPDATEy DELETE sentencias que modifican las variables de la tabla local.
  • EXECUTE instrucciones que llaman a procedimientos almacenados extendidos.

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

Interoperabilidad de columnas calculadas

Las funciones tienen las siguientes propiedades. Los valores de estas propiedades determinan si las funciones se pueden utilizar en columnas calculadas que se pueden conservar o indexar.

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

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

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

Importante

Las funciones deben crearse con SCHEMABINDING para ser deterministas.

Una columna calculada que invoca una función definida por el usuario se puede usar en un índice cuando la función definida por el usuario tiene los siguientes valores de propiedad:

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

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

Llamar a procedimientos almacenados extendidos desde funciones

El procedimiento almacenado extendido, cuando se llama desde dentro de una función, no puede devolver conjuntos de resultados al cliente. Cualquier API de ODS que devuelva conjuntos de resultados al cliente, devuelva FAIL. El procedimiento almacenado extendido podría volver a conectarse a una instancia de SQL Server; Sin embargo, no debe intentar combinar la misma transacción que la función que invocó el procedimiento almacenado extendido.

De forma similar a las invocaciones de un lote o un procedimiento almacenado, 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 sobre él.

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 Transact-SQL:

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

Las funciones definidas por el usuario pueden anidarse; es decir, una función definida por el usuario puede invocar a otra. El nivel de anidamiento se incrementa cuando la función llamada inicia la ejecución y disminuye cuando la función llamada finaliza la ejecución. Las funciones definidas por el usuario se pueden anidar hasta 32 niveles. Exceder los límites máximos de anidamiento provoca que toda la cadena de funciones de llamada falle. Cualquier referencia al código administrado desde una función definida por el usuario de Transact-SQL se considera como un nivel respecto al límite de anidamiento de 32 niveles. Los métodos invocados desde el código administrado no cuentan para este límite.

Usar el criterio de ordenación en funciones con valores de tabla de CLR

Al utilizar la ORDER cláusula en las funciones con valores de tabla de CLR, siga estas directrices:

  • Debe asegurarse de que los resultados siempre se ordenen en el orden 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 ORDER cláusula, la salida de la función con valores de tabla debe ordenarse de acuerdo con la intercalación de la columna (explícita o implícita). Por ejemplo, si la intercalación de columnas es china, los resultados devueltos deben ordenarse según las reglas de ordenación chinas. (La intercalación se especifica en el DDL de 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, al devolver los resultados, independientemente de si el procesador de consultas la usa o no para realizar optimizaciones adicionales. Utilice la ORDER cláusula solo si sabe que es útil para el procesador de consultas.

  • El procesador de consultas de SQL Server aprovecha la ORDER cláusula automáticamente en los siguientes casos:

    • Inserte consultas en las que la ORDER cláusula sea compatible con un índice.
    • ORDER BY cláusulas que sean compatibles con la ORDER cláusula.
    • Agregados, donde GROUP BY sea compatible con ORDER la cláusula.
    • DISTINCT agregados en los que las columnas distintas son compatibles con la ORDER cláusula.

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

Metadatos

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

Vista del sistema Descripción
sys.sql_módulos Consulte el ejemplo E en la sección Ejemplos.
sys.assembly_modules Muestra información sobre las funciones definidas por el usuario de CLR.
sys.parameters Muestra información sobre los parámetros definidos en las funciones definidas por el usuario.
sys.sql_dependencias_de_expresión_ 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 acerca de las UDF, vea Crear funciones definidas por el usuario (Motor de base de datos).

Un. Utilice una función definida por el usuario con valores escalares que calcule la semana ISO

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

En el ejemplo también se muestra el uso de la cláusula EXECUTE AS (cláusula) (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, OWNERy user_name.

Esta es la llamada a la 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';

Este es el conjunto de resultados.

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

B. Creación de una función con valores de tabla en línea

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 los totales del año 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 fn_FindReports(InEmpID) con valores de tabla en la base de AdventureWorks2022 datos. Cuando se proporciona un ID de empleado válido, la función devuelve una tabla que corresponde a todos los empleados que informan al empleado directa o indirectamente. La función utiliza una expresión de tabla común (CTE) recursiva para generar 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 len_sCLR. 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 obtener un ejemplo de cómo crear una función con valores de tabla CLR, vea CLR Table-Valued Functions.

E. Mostrar la definición de las 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 las 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.

Contenido relacionado