Material Examen DyABd

[ FILETABLE_COLLATE_FILENAME = { | database_default } ] [ FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = ] [ FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = ] [ FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = ] } ::= { 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} | DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { | } [ , ...n ] ) ] } ::= TO

Argumentos database_name

Es el nombre de la base de datos en la que se crea la tabla. database_name debe especificar el nombre de una base de datos existente. Si no se especifica, database_name utiliza de manera predeterminada la base de datos actual. El inicio de sesión de la conexión actual debe estar asociado a un identificador de usuario existente en la base de datos especificada por database_name, y ese identificador de usuario debe tener permisos CREATE TABLE. schema_name

Es el nombre del esquema al que pertenece la nueva tabla. table_name

Es el nombre de la nueva tabla. Los nombres de tabla deben seguir las reglas que se aplican a los identificadores. table_name puede contener un máximo de 128 caracteres, excepto para los nombres de tablas temporales locales (nombres precedidos de un único signo de número (#)), que no pueden superar los 116 caracteres. 47

AS FileTable Crea la nueva tabla como un objeto FileTable. No es necesario especificar columnas porque un objeto FileTable tiene un esquema fijo. Para obtener más información sobre objetos FileTable, vea FileTables (SQL Server). column_name

Es el nombre de una columna de la tabla. Los nombres de columna deben seguir las reglas que se aplican a los identificadores y ser únicos en la tabla. column_name puede contener un máximo de 128 caracteres.column_name se puede omitir en las columnas creadas con un tipo de datos timestamp. Si no se especifica column_name, el nombre de una columna timestamp es, de manera predeterminada, timestamp. computed_column_expression

Es una expresión que define el valor de una columna calculada. Una columna calculada es una columna virtual que no está almacenada físicamente en la tabla, a menos que la columna esté marcada con PERSISTED. La columna se calcula a partir de una expresión que utiliza otras columnas de la misma tabla. Por ejemplo, una columna calculada podría tener la definición: cost AS price * qty. La expresión puede ser un nombre de columna no calculada, una constante, una función, una variable o cualquier combinación de estos elementos conectados mediante uno o más operadores. La expresión no puede ser una subconsulta ni contener tipos de datos de alias. Las columnas calculadas se pueden usar en listas de selección, cláusulas WHERE, cláusulas ORDER BY u otras ubicaciones en que se puedan emplear expresiones regulares, con las siguientes excepciones: Las columnas calculadas deben marcarse como PERSISTED para que puedan participar en restricciones FOREIGN KEY o CHECK. Es posible usar una columna calculada como columna de clave en un índice o como parte de una restricción PRIMARY KEY o UNIQUE, si el valor de la columna calculada se define mediante una expresión determinista y el tipo de datos del resultado está permitido en columnas de índice. Por ejemplo, si la tabla contiene las columnas de enteros a y b, la columna calculada a+b puede estar indizada, pero la columna calculada a+DATEPART(dd, GETDATE()) no puede indizarse porque el valor puede cambiar en las siguientes llamadas. Una columna calculada no puede ser el destino de una instrucción INSERT o UPDATE. 





Nota

48

Debido a que cada fila de una tabla puede tener distintos valores para las columnas implicadas en una calculada puede no tener el mismo valor para cada fila.

En función de las expresiones utilizadas, la nulabilidad de las columnas calculadas la determina automáticamente el Motor de base de datos. Se considera que el resultado de la mayoría de las expresiones admite valores NULL incluso si únicamente están presentes columnas que no admiten valores NULL, ya que los posibles subdesbordamientos o desbordamientos también dan como resultado valores NULL. Utilice la función COLUMNPROPERTY con la propiedad AllowsNull para determinar la nulabilidad de las columnas calculadas de la tabla. Una expresión que admita valores NULL se puede convertir en una expresión que no los admita si se especifica ISNULL con la constante check_expression, donde la constante es un valor distinto de NULL que se sustituye por cualquier resultado NULL. Requiere el permiso REFERENCES sobre el tipo para las columnas calculadas basadas en expresiones de tipo definido por el usuario de Common Language Runtime (CLR). PERSISTED Especifica que Motor de base de datos de SQL Server almacena físicamente los valores calculados en la tabla y actualiza los valores cuando se actualizan las columnas de las que depende la columna calculada. El hecho de marcar la columna calculada como PERSISTED le permite crear un índice en una columna calculada que es determinista, pero no precisa. Para obtener más información, vea Índices en columnas calculadas. Las columnas calculadas que se usan como columnas de partición de una tabla con particiones deben marcarse explícitamente como PERSISTED. computed_column_expression debe ser determinista cuando se especifique PERSISTED. ON { | filegroup | "default" } Especifica el esquema de partición o el grupo de archivos en que se almacena la tabla. Si se especifica , la tabla será una tabla con particiones cuyas particiones se almacenan en un conjunto de uno o más grupos de archivos especificados en . Si se especifica filegroup, la tabla se almacena en el grupo de archivos con nombre. El grupo de archivos debe existir en la base de datos. Si se especifica "default" o si ON no se especifica en ninguna parte, la tabla se almacena en el grupo de archivos predeterminado. El mecanismo de almacenamiento de una tabla según se especifica en CREATE TABLE no se puede modificar posteriormente. ON { | filegroup | "default"} se puede especificar también en una restricción PRIMARY KEY o UNIQUE. Estas restricciones 49

crean índices. Si se especifica filegroup, el índice se almacena en el grupo de archivos con nombre. Si se especifica "default" o si ON no se especifica en ninguna parte, el índice se almacena en el mismo grupo de archivos que la tabla. Si la restricción PRIMARY KEY o UNIQUE crea un índice clúster, las páginas de datos de la tabla se almacenan en el mismo grupo de archivos que el índice. Si se especifica CLUSTERED o la restricción crea un índice clúster, y se especifica un distinto del o filegroup de la definición de tabla, o viceversa, únicamente se respeta la definición de restricción y se omite el resto. Nota

En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo d delimitar, como en ON "default" u ON [default]. Si se especifica"default", la opción QUOTED_ID actual. Ésta es la configuración predeterminada. Para obtener más información, veaSET QUOTED_I Nota

Después de crear una tabla con particiones, considere la posibilidad de establecer la opción LOCK_E AUTO. Esto puede mejorar la simultaneidad al permitir que los bloqueos se extiendan al nivel de par obtener más información, vea ALTER TABLE (Transact-SQL).

TEXTIMAGE_ON { filegroup| "default" } Indica que las columnas text, ntext, image, xml, varchar(max), nvarchar(max), varbinar y(max) y de tipo definido por el usuario de CLR (incluyendo geometría y geografía) se almacenan en el grupo de archivos especificado. No se permite TEXTIMAGE_ON si no hay columnas de valores grandes en la tabla. No se puede especificar TEXTIMAGE_ON si se especifica . Si se especifica "default" o si TEXTIMAGE_ON no se especifica en ninguna parte, las columnas de valores grandes se almacenan en el grupo de archivos predeterminado. El almacenamiento de los datos de columna de valores grandes especificados en CREATE TABLE no se puede modificar posteriormente. Nota

En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo d delimitar, como en TEXTIMAGE_ON "default" o TEXTIMAGE_ON[default]. Si se especifica "de QUOTED_IDENTIFIER debe ser ON para la sesión actual. Ésta es la configuración predeterminada. QUOTED_IDENTIFIER (Transact-SQL).

FILESTREAM_ON { partition_scheme_name | filegroup | "default" } Especifica el grupo de archivos para los datos FILESTREAM. 50

Si la tabla contiene datos FILESTREAM y se crean particiones de la tabla, debe incluirse la cláusula FILESTREAM_ON y debe especificarse un esquema de partición de grupos de archivos FILESTREAM. Este esquema de partición debe utilizar la misma función de partición y las mismas columnas de partición que el esquema de partición para la tabla; de lo contrario, se produce un error. Si la tabla no tiene particiones, no se pueden crear particiones en la columna FILESTREAM. Los datos FILESTREAM para la tabla deben almacenarse en un grupo de archivos único. Este grupo de archivos se especifica en la cláusula FILESTREAM_ON. Si la tabla no tiene particiones y no se especifica la cláusula FILESTREAM_ON, se utiliza el grupo de archivos FILESTREAM que tenga la propiedad DEFAULT establecida. Si no hay ningún grupo de archivos FILESTREAM, se produce un error. Al igual que con ON y TEXTIMAGE_ON, el valor establecido utilizando CREATE TABLE para FILESTREAM_ON no se puede cambiar, excepto en los casos siguientes: Una instrucción CREATE INDEX convierte un montón en un índice clúster. En este caso, se puede especificar un grupo de archivos FILESTREAM diferente, un esquema de partición o NULL. Una instrucción DROP INDEX convierte un índice clúster en un montón. En este caso, se puede especificar un grupo de archivos FILESTREAM diferente, un esquema de partición o "default". El grupo de archivos en la cláusula FILESTREAM_ON , o cada grupo de archivos FILESTREAM que se menciona en el esquema de partición, debe tener un archivo definido para el grupo de archivos. Este archivo se debe definir utilizando una instrucción CREATE DATABASE o ALTER DATABASE; de lo contrario, se produce un error. Para consultar temas relacionados con FILESTREAM, vea Datos de objeto binario grande (Blob) (SQL Server). [ type_schema_name. ] type_name Especifica el tipo de datos de la columna y el esquema al que pertenece. El tipo de datos puede ser uno de los siguientes: Un tipo de datos del sistema. Un tipo de alias basado en un tipo de datos del sistema de SQL Server. Los tipos de datos de alias se crean con la instrucción CREATE TYPE para poder utilizarlos en una definición de tabla. La asignación NULL o NOT NULL de un tipo de datos de alias puede invalidarse durante la instrucción CREATE TABLE. No obstante, la especificación 









51

de longitud no se puede cambiar; la longitud del tipo de datos de alias no se puede especificar en una instrucción CREATE TABLE. Un tipo definido por el usuario CLR. Los tipos definidos por el usuario CLR se crean con la instrucción CREATE TYPE para poder utilizarlos en una definición de tabla. Para crear una columna en un tipo definido por el usuario CLR, se necesita el permiso REFERENCES para el tipo. Si no se especifica el parámetro type_schema_name, el Motor de base de datos de SQL Server hace referencia a type_name en el siguiente orden: El tipo 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. 







precision

Es la precisión del tipo de datos especificado. Para obtener más información acerca de los valores de precisión válidos, vea Precisión, escala y longitud (Transact-SQL). scale

Es la escala del tipo de datos especificado. Para obtener más información acerca de los valores de escala válidos, vea Precisión, escala y longitud (Transact-SQL). max Solo se aplica a los tipos de datos varchar, nvarchar y varbinary para almacenar 2^31 bytes de datos de caracteres y binarios, y 2^30 bytes de datos Unicode. CONTENT Especifica que cada instancia del tipo de datos xml de column_name puede contener varios elementos de nivel superior. CONTENT solo se aplica al tipo de datos xml y únicamente se puede especificar si también se especifica xml_schema_collection. Si no se especifica, CONTENT es el comportamiento predeterminado. DOCUMENT Especifica que cada instancia del tipo de datos xml de column_name solo puede contener un elemento de nivel superior. DOCUMENT solo se aplica al tipo de datos xml y únicamente se puede especificar si también se especifica xml_schema_collection. xml_schema_collection

Solo se aplica al tipo de datos xml para asociar una colección de esquemas XML al tipo. Antes de escribir una columna xml en un esquema, el esquema se debe crear primero en la base de datos con CREATE XML SCHEMA COLLECTION. 52

DEFAULT Especifica el valor suministrado para la columna cuando no se ha proporcionado explícitamente un valor durante la inserción. Las definiciones DEFAULT se pueden aplicar a cualquier columna, excepto a las definidas como timestamp o a las que tengan la propiedad IDENTITY. Si se especifica un valor predeterminado para una columna de un tipo definido por el usuario, dicho tipo debe admitir la conversión implícita de constant_expression en el tipo definido por el usuario. Las definiciones DEFAULT desaparecen cuando la tabla se quita. Como valor predeterminado solo se puede utilizar un valor constante, por ejemplo una cadena de caracteres, una función escalar (función del sistema, definida por el usuario o CLR) o NULL. Para mantener la compatibilidad con versiones anteriores de SQL Server, se puede asignar un nombre de restricción a DEFAULT. constant_expression

Es una constante, NULL o una función del sistema que se utiliza como valor predeterminado de una columna. IDENTITY Indica que la nueva columna es una columna de identidad. Cuando se agrega una fila nueva a la tabla, el Motor de base de datos proporciona un valor incremental único para la columna. Las columnas de identidad se utilizan normalmente con las restricciones PRIMARY KEY como identificadores de fila únicos de 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. Las restricciones DEFAULT y los valores predeterminados enlazados no se pueden utilizar en las columnas de identidad. En este caso, deben especificarse el valor de inicialización y el incremento, o ninguno de esto valores. Si no se especifica ninguno, el valor predeterminado es (1,1). seed

Es el valor que se utiliza para la primera fila cargada en la tabla. increment

Es el valor incremental que se agrega al valor de identidad de la fila cargada anterior. NOT FOR REPLICATION En la instrucción CREATE TABLE, la cláusula NOT FOR REPLICATION se puede especificar para la propiedad IDENTITY, las restricciones FOREIGN KEY y las restricciones CHECK. Si se especifica esta cláusula para la propiedad IDENTITY, los valores no se incrementan en las columnas de identidad cuando los agentes de replicación realizan inserciones. Si se especifica esta 53

cláusula para una restricción, la restricción no se aplica cuando los agentes de replicación realizan operaciones de inserción, actualización o eliminación. ROWGUIDCOL Indica que la nueva columna es una columna de GUID de filas. Solo se puede designar una columna uniqueidentifier por tabla como columna ROWGUIDCOL. Si se aplica la propiedad ROWGUIDCOL, se puede hacer referencia a la columna mediante $ROWGUID. La propiedad ROWGUIDCOL únicamente se puede asignar a una columna uniqueidentifier. Las columnas de tipos de datos definidos por el usuario no se pueden designar con ROWGUIDCOL. La propiedad ROWGUIDCOL no aplica la unicidad de los valores almacenados en la columna. ROWGUIDCOL tampoco genera automáticamente valores para nuevas filas insertadas en la tabla. Para generar valores únicos para cada columna, utilice la función NEWID o NEWSEQUENTIALID en instrucciones INSERT o utilice estas funciones como valor predeterminado de la columna. SPARSE Indica que la columna es una columna dispersa. El almacenamiento de columnas dispersas está optimizado para los valores NULL. Las columnas dispersas no se pueden designar como NOT NULL. Para obtener restricciones adicionales y más información sobre columnas dispersas, vea Usar columnas dispersas. FILESTREAM Es válido solo para columnas varbinary(max). Especifica el almacenamiento FILESTREAM para los datos de BLOB varbinary(max). La tabla también debe tener una columna del tipo de datos uniqueidentifier que tenga el atributo ROWGUIDCOL. Esta columna no debe permitir valores nulos y debe tener una restricción de columna única UNIQUE o PRIMARY KEY. El valor de GUID de la columna lo debe proporcionar una aplicación cuando se inserten datos o una restricción DEFAULT que use la función NEWID (). No se puede quitar la columna ROWGUIDCOL ni se pueden cambiar las restricciones relacionadas si hay definida una columna FILESTREAM para la tabla. Solamente se puede quitar la columna ROWGUIDCOL después de quitarse la última columna FILESTREAM. Si se especifica el atributo de almacenamiento FILESTREAM para una columna, todos los valores para dicha columna se almacenan en un contenedor de datos FILESTREAM en el sistema de archivos. COLLATE collation_name

54

Especifica la intercalación de la columna. El nombre de intercalación puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. collation_name solo se aplica a las columnas con los tipos de datos char, varchar, text, nchar, nvarchar y ntext. Si no se especifica, se asignará a la columna la intercalación del tipo de datos definido por el usuario, si la columna es de uno de estos tipos, o la intercalación predeterminada de la base de datos. Para obtener más información acerca de los nombres de intercalación de Windows y SQL, vea Nombre de intercalación de Windows y Nombre de intercalación de SQL. Para obtener más información acerca de la cláusula COLLATE, vea COLLATE (Transact-SQL). CONSTRAINT Es una palabra clave opcional que indica el principio de la definición de una restricción PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY o CHECK. constraint_name

Es el nombre de una restricción. Los nombres de restricción deben ser únicos en el esquema al que pertenece la tabla. NULL | NOT NULL Determina si se permiten valores NULL en la columna. NULL no es estrictamente una restricción, pero se puede especificar de la misma forma que NOT NULL. NOT NULL solo puede especificarse para las columnas calculadas si también se especifica PERSISTED. PRIMARY KEY Es una restricción que exige la integridad de entidad para una o varias columnas especificadas a través de un índice único. Solo se puede crear una restricción PRIMARY KEY para cada tabla. UNIQUE Es una restricción que proporciona integridad de entidad para una o varias columnas especificadas a través de un índice único. Las tablas pueden tener múltiples restricciones UNIQUE. CLUSTERED | NONCLUSTERED Indica que se ha creado un índice clúster o no agrupado para la restricción PRIMARY KEY o UNIQUE. De forma predeterminada, el valor de las restricciones PRIMARY KEY es CLUSTERED, y el de las restricciones UNIQUE es NONCLUSTERED. En una instrucción CREATE TABLE, se puede especificar CLUSTERED tan solo para una restricción. Si se especifica CLUSTERED para una restricción UNIQUE y se especifica también una restricción PRIMARY KEY, el valor predeterminado de PRIMARY KEY es NONCLUSTERED. 55

FOREIGN KEY REFERENCES Es una restricción que proporciona integridad referencial para los datos de la columna o columnas. Las restricciones FOREIGN KEY requieren que cada valor de la columna exista en la columna o columnas de referencia correspondientes de la tabla a la que se hace referencia. Las restricciones FOREIGN KEY pueden hacer referencia solo a columnas que sean restricciones PRIMARY KEY o UNIQUE en la tabla de referencia o a columnas a las que se haga referencia en UNIQUE INDEX en la tabla de referencia. Las claves externas de las columnas calculadas también se deben marcar como PERSISTED. [ schema_name.] referenced_table_name] Es el nombre de la tabla a la que hace referencia la restricción FOREIGN KEY y el esquema al que pertenece. ( ref_column [ ,... n ] ) Es una columna o lista de columnas de la tabla a la que hace referencia la restricción FOREIGN KEY. ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Especifica la acción que tiene lugar en las filas de la tabla creada si dichas filas tienen una relación referencial y la fila a la que se hace referencia se elimina de la tabla primaria. El valor predeterminado es NO ACTION. NO ACTION El Motor de base de datos genera un error y se revierte la acción de eliminación de la fila de la tabla primaria. CASCADE Si esa fila se elimina de la tabla primaria, las filas correspondientes se eliminan de la tabla de referencia. SET NULL Todos los valores que forman la clave externa se establecen en NULL si se elimina la fila correspondiente de la tabla primaria. Para ejecutar esta restricción, las columnas de clave externa deben admitir valores NULL. SET DEFAULT Todos los valores que forman la clave externa se establecen en los valores predeterminados si se elimina la fila correspondiente de la tabla primaria. Para ejecutar esta restricción, todas las columnas de clave externa deben tener valores predeterminados. Si una columna acepta valores NULL y no se ha establecido un valor predeterminado explícito, NULL se convierte en el valor predeterminado explícito de dicha columna. No especifique CASCADE si la tabla se va a incluir en una publicación de combinación que utiliza registros lógicos. Para obtener más información

56

acerca de los registros lógicos, vea Agrupar cambios en filas relacionadas con registros lógicos. No se puede definir ON DELETE CASCADE si ya existe un desencadenador INSTEAD OF en ON DELETE en la tabla en cuestión. Por ejemplo, en la base de datos AdventureWorks2012 , la tabla ProductVendor tiene una relación referencial con la tabla Vendor. La clave externa ProductVendor.BusinessEntityID hace referencia a la clave principal Vendor.BusinessEntityID. Si se ejecuta una instrucción DELETE en una fila de la tabla Vendor y se especifica una acción ON DELETE CASCADE para ProductVendor.BusinessEntityID, Motor de base de datos comprueba si hay una o más filas dependientes de la tabla ProductVendor. Si las hay, las filas dependientes de la tabla ProductVendor se eliminan, así como la fila a la que se hace referencia en la tabla Vendor. Por el contrario, si se especifica NO ACTION, Motor de base de datos genera un error y revierte la acción de eliminación de la fila Vendor si hay al menos una fila en la tabla ProductVendor que hace referencia a ella. ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Especifica la acción que se produce en las filas de la tabla modificada cuando esas filas tienen una relación referencial y la fila a la que se hace referencia se actualiza en la tabla primaria. El valor predeterminado es NO ACTION. NO ACTION El Motor de base de datos genera un error y se revierte la acción de actualización de la fila de la tabla primaria. CASCADE Si esa fila se actualiza en la tabla primaria, las filas correspondientes se actualizan en la tabla de referencia. SET NULL Cuando se actualiza la fila correspondiente en la tabla primaria, todos los valores que componen la clave externa se establecen en NULL. Para que esta restricción se ejecute, las columnas de clave externa deben aceptar valores NULL. SET DEFAULT Cuando se actualiza la fila correspondiente en la tabla primaria, todos los valores que componen la clave externa se establecen en sus valores predeterminados. Para que esta restricción se ejecute, todas las columnas de clave externa deben tener definiciones predeterminadas. Si una columna acepta valores NULL y no se ha establecido un valor predeterminado

57

explícito, NULL se convierte en el valor predeterminado explícito de dicha columna. No especifique CASCADE si la tabla se va a incluir en una publicación de combinación que utiliza registros lógicos. Para obtener más información acerca de los registros lógicos, vea Agrupar cambios en filas relacionadas con registros lógicos. No se puede definir ON UPDATE CASCADE, SET NULL o SET DEFAULT si ya existe un desencadenador INSTEAD OF en ON UPDATE en la tabla que se va a modificar. Por ejemplo, en la base de datos AdventureWorks2012 , la tabla ProductVendor tiene una relación referencial con la tabla Vendor: la clave externa ProductVendor.BusinessEntity hace referencia a la clave principal Vendor.BusinessEntityID. Si se ejecuta una instrucción UPDATE en una fila de la tabla Vendor y se especifica una acción ON UPDATE CASCADE para ProductVendor.BusinessEntityID, Motor de base de datos comprueba si hay una o más filas dependientes de la tabla ProductVendor. Si las hay, las filas dependientes de la tabla ProductVendor se actualizan, así como la fila a la que se hace referencia en la tabla Vendor. Por el contrario, si se especifica NO ACTION, Motor de base de datos genera un error y revierte la acción de actualización de la fila Vendor si hay como mínimo una fila en la tabla ProductVendor que hace referencia a ella. CHECK Es una restricción que exige la integridad del dominio al limitar los valores posibles que se pueden escribir en una o varias columnas. Las restricciones CHECK de las columnas calculadas también se deben marcar como PERSISTED. logical_expression

Es una expresión lógica que devuelve TRUE o FALSE. Los tipos de datos de alias no pueden formar parte de la expresión. column

Es una columna o lista de columnas, entre paréntesis, que se utiliza en las restricciones de tabla para indicar las columnas que se están utilizando en la definición de la restricción. [ ASC | DESC ] Especifica cómo se ordenan la columna o las columnas que participan en las restricciones de la tabla. El valor predeterminado es ASC. partition_scheme_name

58

Es el nombre del esquema de partición que define los grupos de archivos a los que se van a asignar las particiones de una tabla con particiones. El esquema de partición debe existir en la base de datos. [ partition_column_name. ] Especifica la columna en la que se van a crear las particiones de la tabla con particiones. La columna debe coincidir con la que se especifica en la función de partición que partition_scheme_name utiliza en términos de tipo de datos, longitud y precisión. Una columna calculada que participa en una función de partición se debe marcar como PERSISTED de forma explícita. Importante

Se recomienda especificar NOT NULL en la columna de partición de las tablas con particiones y tam sean orígenes o destinos de operaciones ALTER TABLE...SWITCH. De esta forma se garantiza que de partición no tengan que comprobar si hay valores NULL.

WITH FILLFACTOR =fillfactor Especifica en qué medida el Motor de base de datos debe llenar cada página de índice que se va a utilizar para almacenar los datos de índice. Los valores de fillfactor especificados por el usuario pueden estar comprendidos entre 1 y 100. Si no se especifica un valor, el valor predeterminado es 0. Los valores del factor de relleno 0 y 100 son idénticos. Importante

La documentación de WITH FILLFACTOR = fillfactor como única opción de índice que se aplica a UNIQUE se mantiene por compatibilidad con versiones anteriores. No obstante, no se documentará d column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

Es el nombre del conjunto de columnas. Un conjunto de columnas es una representación XML sin tipo que combina todas las columnas dispersas de una tabla en una salida estructurada. Para obtener más información sobre conjuntos de columnas, vea Usar conjuntos de columnas. < table_option> ::= Especifica una o varias opciones de tabla. DATA_COMPRESSION Especifica la opción de compresión de datos para la tabla, el número de partición o el intervalo de particiones especificados. Las opciones son las siguientes: NONE No se comprimen la tabla ni las particiones especificadas. ROW

59

La tabla o las particiones especificadas se comprimen utilizando la compresión de fila. PAGE La tabla o las particiones especificadas se comprimen utilizando la compresión de página. Para obtener más información acerca de la compresión, vea Compresión de datos. ON PARTITIONS ( { | } [ ,...n ] ) Especifica las particiones a las que se aplica el valor DATA_COMPRESSION. Si la tabla no tiene particiones, el argumento ON PARTITIONS generará un error. Si no se proporciona la cláusula ON PARTITIONS, la opción DATA_COMPRESSION se aplicará a todas las particiones de una tabla con particiones. se puede especificar de las maneras siguientes: Proporcione el número de una partición, por ejemplo: ON PARTITIONS (2). Proporcione los números de partición de varias particiones separados por comas, por ejemplo: ON PARTITIONS (1, 5). Proporcione intervalos y particiones individuales, por ejemplo: ON PARTITIONS (2, 4, 6 TO 8). se puede especificar como números de partición separados por la palabra TO, por ejemplo: ON PARTITIONS (6 TO 8). Para establecer tipos de compresión de datos diferentes para particiones distintas, especifique la opción DATA_COMPRESSION más de una vez, por ejemplo: 





WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5) )

::= Especifica una o varias opciones de índice. Para obtener una descripción completa de estas opciones, vea CREATE INDEX (Transact-SQL). PAD_INDEX = { ON | OFF } Cuando es ON, el porcentaje de espacio disponible especificado por FILLFACTOR se aplica a las páginas de nivel intermedio del índice. Cuando es OFF o no se especifica ningún valor de FILLFACTOR, las páginas de nivel intermedio se rellenan casi al máximo de su capacidad dejando espacio suficiente para al menos una fila del tamaño máximo que el índice puede 60

contener teniendo en cuenta el conjunto de claves de las páginas intermedias. 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. Los valores de factor de relleno (fillfactor) 0 y 100 son idénticos. 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. La opción no tiene efecto cuando se ejecutan CREATE INDEX, ALTER INDEX o UPDATE. El valor predeterminado es OFF. ON Se producirá un mensaje de advertencia cuando se inserten valores de clave duplicados en un índice único. Solo las filas que infrinjan la restricción de unicidad darán error. OFF Se producirá un mensaje de error cuando se inserten valores de clave duplicados en un índice único. Toda la operación INSERT se revertirá. IGNORE_DUP_KEY no se puede establecer en ON para los índices creados en una vista, los índices que no sean únicos, los índices XML, los índices espaciales y los índices filtrados. Para ver IGNORE_DUP_KEY, utilice sys.indexes. En la sintaxis compatible con versiones anteriores, WITH IGNORE_DUP_KEY es equivalente a WITH IGNORE_DUP_KEY = ON. STATISTICS_NORECOMPUTE = { ON | OFF } Si es ON, las estadísticas de índices no actualizadas no se vuelven a calcular automáticamente. Si es OFF, se habilita la actualización automática de estadísticas. El valor predeterminado es OFF. ALLOW_ROW_LOCKS = { ON | OFF } Si es ON, los bloqueos de fila se permiten al tener acceso al índice. El motor de base de datos determina cuándo se utilizan los bloqueos de fila. Si es OFF, no se utilizan bloqueos de fila. El valor predeterminado es ON. ALLOW_PAGE_LOCKS = { ON | OFF } Si es ON, los bloqueos de página se permiten al tener acceso al índice. El Motor de base de datos determina el momento en que se utilizan bloqueos

61

de página. Si es OFF, no se utilizan bloqueos de página. El valor predeterminado es ON. FILETABLE_DIRECTORY = directory_name Especifica el nombre de directorio de FileTable compatible con Windows. Este nombre debe ser único entre todos los nombres de directorio de FileTable en la base de datos. La comparación de unicidad no distingue mayúsculas de minúsculas, independientemente de la configuración de intercalación. Si no se especifica este valor, se usa el nombre de la tabla de archivos. FILETABLE_COLLATE_FILENAME = { collation_name | database_default } Especifica el nombre de la collation intercalación que se va a aplicar a la columna Nombre en la tabla de archivos. La intercalación no debe distinguir mayúsculas de minúsculas para cumplir con la semántica de nomenclatura de archivos de Windows. Si no se especifica este valor, se usa la intercalación predeterminada de la base de datos. Si la intercalación predeterminada de la base de datos distingue mayúsculas de minúsculas, se genera un error en la operación CREATE TABLE. collation_name

El nombre de una intercalación sin distinción entre mayúsculas y minúsculas. database_default Especifica que se debe usar la intercalación predeterminada de la base de datos. Esta intercalación no debe distinguir mayúsculas de minúsculas. FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = constraint_name Especifica el nombre que se debe usar para la restricción de clave primaria creada automáticamente en la tabla de archivos. Si no se especifica este valor, el sistema genera un nombre para la restricción. FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = constraint_name Especifica el nombre que se debe usar para la restricción única creada automáticamente en la columna stream_id de la tabla de archivos. Si no se especifica este valor, el sistema genera un nombre para la restricción. FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = constraint_name Especifica el nombre que se debe usar para la restricción única creada automáticamente en las columnas parent_path_locator y name de la tabla de archivos. Si no se especifica este valor, el sistema genera un nombre para la restricción. Comentarios Para obtener información sobre el número de tablas, columnas, restricciones e índices permitidos, vea Especificaciones de capacidad máxima para SQL Server.

62

Normalmente, el espacio se asigna a las tablas e índices en incrementos de una extensión cada vez. Cuando se crea la tabla o el índice, se le asignan páginas de extensiones mixtas hasta que tengan páginas suficientes para llenar una extensión uniforme. Una vez que haya suficientes páginas para llenar una extensión uniforme, se asigna otra extensión cada vez que se llenan las extensiones asignadas actualmente. Para obtener un informe acerca de la cantidad de espacio asignado y utilizado por una tabla, ejecute sp_spaceused. El Motor de base de datos no exige el orden en que DEFAULT, IDENTITY, ROWGUIDCOL o las restricciones de columna se especifican en una definición de columna. Al crear una tabla, la opción QUOTED IDENTIFIER siempre se almacena como ON en los metadatos de la tabla incluso si la opción está establecida en OFF al crear la tabla. Tablas temporales Se pueden crear tablas temporales locales y globales. Las tablas temporales locales son visibles solo en la sesión actual y las tablas temporales globales son visibles para todas las sesiones. No se pueden crear particiones en las tablas temporales. Coloque un prefijo de signo de número único (#table_name) en los nombres de las tablas temporales locales y un prefijo de signo de número doble (##table_name) en los nombres de las tablas temporales globales. Las instrucciones SQL hacen referencia a la tabla temporal mediante el valor especificado para table_name en la instrucción CREATE TABLE. Por ejemplo: CREATE TABLE #MyTempTable (cola INT PRIMARY KEY); INSERT INTO #MyTempTable VALUES (1);

Si se ha creado más de una tabla temporal en un único procedimiento almacenado o lote, deben tener nombres distintos. Si se crea una tabla temporal local en un procedimiento almacenado o una aplicación que varios usuarios pueden ejecutar al mismo tiempo, el Motor de base de datos tiene que ser capaz de distinguir las tablas creadas por los distintos usuarios. El Motor de base de datos realiza esto anexando internamente un sufijo numérico a cada nombre de tabla temporal local. El nombre completo de una tabla temporal tal como se almacena en la tabla sysobjects de tempdb consta del nombre de la tabla especificado en la instrucción CREATE TABLE y el sufijo numérico generado por el sistema. Para permitir que se agregue el sufijo, el parámetro table_nameespecificado para un nombre temporal local no puede superar los 116 caracteres. Las tablas temporales se quitan automáticamente cuando están fuera de ámbito, a menos que ya se hayan quitado explícitamente mediante el uso de DROP TABLE:

63

A.Crear una base de datos sin especificar archivos En este ejemplo se crea la base de datos mytest, y los archivos principal y de registro de transacciones correspondientes. Debido a que la instrucción no tiene elementos , el archivo de la base de datos principal tiene el tamaño del archivo principal de la base de datos model. El registro de transacciones se establece en el mayor de estos valores: 512 KB o el 25% del tamaño del archivo de datos principal. Como no se ha especificado MAXSIZE, los archivos pueden crecer hasta llenar todo el espacio disponible en el disco. En este ejemplo también se muestra la forma de quitar la base de datos denominada mytest si existe, antes de crear la base de datos mytest. Transact-SQL USE master; GO CREATE DATABASE mytest; GO -- Verify the database files and sizes SELECT name, size, size*1.0/128 AS [Size in MBs] FROM sys.master_files WHERE name = N'mytest'; GO

B.Crear una base de datos que especifica los archivos de datos y de registro de transacciones En el ejemplo siguiente se crea la base de datos Sales. Debido a que no se utiliza la palabra clave PRIMARY, el primer archivo (Sales_dat) se convierte en el archivo principal. Como no se especifica MB ni KB en el parámetro SIZE del archivo Sales_dat, se utiliza MB y el tamaño se asigna en megabytes. El tamaño del archivo Sales_log se asigna en megabytes porque el sufijo MB se ha indicado explícitamente en el parámetro SIZE. USE master; GO CREATE DATABASE Sales ON ( NAME = Sales_dat, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\saledat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Sales_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\salelog.ldf', 23

Una tabla temporal local creada en un procedimiento almacenado se quita automáticamente cuando se completa el procedimiento almacenado. Cualquiera de los procedimientos almacenados anidados ejecutados por el procedimiento almacenado que creó la tabla puede hacer referencia a la tabla. El proceso que llamó al procedimiento almacenado que creó la tabla no puede hacer referencia a la tabla. Las demás tablas temporales se quitan automáticamente al final de la sesión actual. Las tablas temporales globales se quitan automáticamente cuando la sesión que creó la tabla finaliza y las tareas restantes han dejado de hacer referencia a ellas. La asociación entre una tarea y una tabla se mantiene solo durante la vida de una única instrucción Transact-SQL. Esto significa que la tabla temporal global se quita al finalizar la última instrucción Transact-SQL que estuviera haciendo referencia activa a la tabla cuando finalizó la sesión que la creó. Una tabla temporal local creada en un procedimiento almacenado o un desencadenador puede tener el mismo nombre que una tabla temporal creada antes de que se llame al procedimiento almacenado o al desencadenador. No obstante, si una consulta hace referencia a una tabla temporal y hay dos tablas temporales con el mismo nombre en ese momento, no está definido en cuál de las dos tablas debe resolverse la consulta. Los procedimientos almacenados anidados pueden crear también tablas temporales con el mismo nombre que la tabla temporal creada por el procedimiento almacenado que la llamó. Sin embargo, en el caso de las modificaciones que se van a resolver en la tabla creada en el procedimiento anidado, la tabla debe tener la misma estructura, con los mismos nombres de columna, que la tabla creada en el procedimiento que realiza la llamada. Esto se muestra en el ejemplo siguiente. 





CREATE PROCEDURE dbo.Test2 AS CREATE TABLE #t(x INT PRIMARY KEY); INSERT INTO #t VALUES (2); SELECT Test2Col = x FROM #t; GO CREATE PROCEDURE dbo.Test1 AS CREATE TABLE #t(x INT PRIMARY KEY); INSERT INTO #t VALUES (1); SELECT Test1Col = x FROM #t; EXEC Test2; GO

64

CREATE TABLE #t(x INT PRIMARY KEY); INSERT INTO #t VALUES (99); GO EXEC Test1; GO

El conjunto de resultados es el siguiente. (1 row(s) affected) Test1Col ----------1 (1 row(s) affected) Test2Col ----------2

Cuando se crean tablas temporales globales o locales, la sintaxis de CREATE TABLE admite la definición de restricciones, excepto restricciones FOREIGN KEY. Si se especifica una restricción FOREIGN KEY en una tabla temporal, la instrucción devuelve un mensaje de advertencia que indica que la restricción se ha omitido. La tabla se sigue creando sin las restricciones FOREIGN KEY. En las restricciones FOREIGN KEY no se puede hacer referencia a tablas temporales. Si una tabla temporal se crea con una restricción con nombre y la tabla temporal se crea dentro del ámbito de una transacción definida por el usuario, solo un usuario a la vez puede ejecutar la instrucción que crea la tabla temporal. Por ejemplo, si un procedimiento almacenado crea una tabla temporal con una restricción de clave principal con nombre, el procedimiento almacenado no puede ser ejecutado a la vez por varios usuarios. Tablas con particiones Antes de crear una tabla con particiones mediante CREATE TABLE, debe crear una función de partición para especificar cómo se van a crear las particiones en la tabla. Para crear una función de partición se usa CREATE PARTITION FUNCTION. A continuación, debe crear un esquema de partición para especificar los grupos de archivos que van a contener las particiones indicadas mediante la función de partición. Para crear un esquema de partición se usa CREATE PARTITION SCHEME. La colocación de restricciones PRIMARY KEY o UNIQUE para separar grupos de archivos no se puede especificar para las tablas con particiones. Para obtener más información, vea Tablas e índices con particiones. Restricciones PRIMARY KEY Una tabla solo puede incluir una restricción PRIMARY KEY. 

65

El índice generado por una restricción PRIMARY KEY no puede hacer que el número de índices de la tabla supere 999 índices no clúster y 1 índice clúster. Si no se especifica CLUSTERED o NONCLUSTERED para una restricción PRIMARY KEY, se utiliza CLUSTERED si no hay índices clúster especificados para las restricciones UNIQUE. Todas las columnas definidas en una restricción PRIMARY KEY se deben definir como NOT NULL. Si no se especifica nulabilidad, la nulabilidad de todas las columnas que participan en una restricción PRIMARY KEY se establece en NOT NULL. Si la clave principal se define en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario. Para obtener más información, vea Tipos definidos por el usuario de CLR. Restricciones UNIQUE Si no se especifica CLUSTERED o NONCLUSTERED para una restricción UNIQUE, de forma predeterminada se utiliza NONCLUSTERED. Cada restricción UNIQUE genera un índice. El número de restricciones UNIQUE no puede hacer que el número de índices de la tabla supere los 999 índices no clúster y 1 índice clúster. Si se define una restricción única en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario o el orden basado en el operador. Para obtener más información, vea Tipos definidos por el usuario de CLR. Restricciones FOREIGN KEY Si se especifica un valor distinto de NULL en la columna de una restricción FOREIGN KEY, el valor debe existir en la columna a que se hace referencia; de lo contrario, se devolverá un error de infracción de clave externa. Las restricciones FOREIGN KEY se aplican a la columna anterior, a menos que se especifiquen columnas de origen. Las restricciones FOREIGN KEY solo pueden hacer referencia a las tablas de la misma base de datos en el mismo servidor. La integridad referencial entre bases de datos debe implementarse a través de desencadenadores. Para obtener más información, vea CREATE TRIGGER (Transact-SQL). Las restricciones FOREIGN KEY pueden hacer referencia a otras columnas de la misma tabla. Esto recibe el nombre de autoreferencia. La cláusula REFERENCES de una restricción FOREIGN KEY de nivel de columna solo puede incluir una columna de referencia. Esta columna debe tener el mismo tipo de datos que la columna en la que se define la restricción. 























66

La cláusula REFERENCES de una restricción FOREIGN KEY de nivel de tabla debe tener el mismo número de columnas de referencia que la lista de columnas de la restricción. El tipo de datos de cada columna de referencia debe ser también el mismo que el de la columna correspondiente de la lista de columnas. No se puede especificar CASCADE, SET NULL o SET DEFAULT si una columna del tipo timestamp forma parte de la clave externa o de la clave con referencia. CASCADE, SET NULL, SET DEFAULT y NO ACTION se pueden combinar en las tablas con relaciones referenciales entre sí. Si el Motor de base de datos detecta NO ACTION, detiene y revierte las acciones CASCADE, SET NULL y SET DEFAULT relacionadas. Cuando una instrucción DELETE hace que se combinen las acciones CASCADE, SET NULL, SET DEFAULT y NO ACTION, todas las acciones CASCADE, SET NULL y SET DEFAULT se aplican antes de que el Motor de base de datos compruebe la existencia de NO ACTION. El Motor de base de datos no tiene un límite predefinido para el número de restricciones FOREIGN KEY que una tabla que hace referencia a otras tablas puede contener, o para el número de restricciones FOREIGN KEY pertenecientes a otras tablas que hacen referencia a una tabla específica. No obstante, el número real de restricciones FOREIGN KEY que se puede utilizar está limitado por la configuración del hardware y por el diseño de la base de datos y de la aplicación. Se recomienda que la tabla no contenga más de 253 restricciones FOREIGN KEY y que no más de 253 restricciones FOREIGN KEY hagan referencia a ella. El límite real en cada caso puede variar en función de la aplicación y el hardware. Debe tener en cuenta el costo que supone la exigencia de restricciones FOREIGN KEY al diseñar la base de datos y las aplicaciones. Las restricciones FOREIGN KEY no se exigen en tablas temporales. Las restricciones FOREIGN KEY solo pueden hacer referencia a columnas de restricciones PRIMARY KEY o UNIQUE de la tabla a la que se hace referencia o a columnas en UNIQUE INDEX de dicha tabla. Si la clave externa se define en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario. Para obtener más información, vea Tipos definidos por el usuario de CLR. Las columnas que participan en una relación de claves externas deben estar definidas con la misma longitud y escala. Definiciones DEFAULT Una tabla solo puede incluir una definición DEFAULT. Una definición DEFAULT puede contener valores constantes, funciones, funciones niládicas SQL-92 o NULL. En la siguiente tabla se muestran las 



















67

funciones niládicas y los valores que devuelven para el valor predeterminado durante la ejecución de una instrucción INSERT. Función niládica SQL-92

Valor devuelto

CURRENT_TIMESTAMP

Fecha y hora actuales.

CURRENT_USER

Nombre del usuario que realiza la inserció

SESSION_USER


SYSTEM_USER


USER


En una definición DEFAULT, constant_expression no puede hacer referencia a otra columna de la tabla ni a otras tablas, vistas o procedimientos almacenados. Las definiciones DEFAULT no se pueden crear en columnas con un tipo de datos timestamp o en columnas con la propiedad IDENTITY. Las definiciones DEFAULT no se pueden crear para columnas con tipos de datos de alias si estos están enlazados a un objeto predeterminado. Restricciones CHECK Una columna puede tener cualquier número de restricciones CHECK y la condición puede incluir varias expresiones lógicas combinadas con AND y OR. Varias restricciones CHECK para una columna se validan en el orden en que se crean. La condición de búsqueda debe evaluarse como una expresión booleana y no puede hacer referencia a otra tabla. Una restricción CHECK en el nivel de columna solo puede hacer referencia a la columna restringida y una restricción CHECK en el nivel de tabla solo puede hacer referencia a columnas de la misma tabla. Las restricciones CHECK y las reglas sirven para la misma función de validación de los datos durante las instrucciones INSERT y UPDATE. Cuando hay una regla y una o más restricciones CHECK para una o varias columnas, se evalúan todas las restricciones. No se pueden definir restricciones CHECK en columnas text, ntext o image. Información adicional sobre las restricciones Un índice creado para una restricción no se puede quitar usando DROP INDEX; la restricción debe quitarse con ALTER TABLE. Un índice creado para una restricción y usado por ella se puede volver a generar mediante ALTER INDEX...REBUILD. Para obtener más información, vea Reorganizar y volver a generar índices. 

















68

Los nombres de restricción deben seguir las reglas de los identificadores, excepto que el nombre no puede empezar por un signo de número (#). Si no se proporciona el parámetro constraint_name, se asigna a la restricción un nombre generado por el sistema. El nombre de la restricción aparece en todos los mensajes de error relativos a infracciones de la restricción. Cuando se infringe una restricción en una instrucción INSERT, UPDATE o DELETE, la instrucción finaliza. Sin embargo, si SET XACT_ABORT se establece en OFF y la instrucción forma parte de una transacción explícita, continúa el procesamiento de la transacción. Si SET XACT_ABORT se establece en ON, se revierte toda la transacción. La instrucción ROLLBACK TRANSACTION también se puede utilizar con la definición de transacción al comprobar la función @@ERROR del sistema. Si ALLOW_ROW_LOCKS = ON y ALLOW_PAGE_LOCK = ON, los bloqueos de nivel de fila, página y tabla se permiten al tener acceso al índice. Motor de base de datos elige el bloqueo apropiado y puede cambiar de escala el bloqueo: de un bloqueo de fila o página a un bloqueo de tabla. Si ALLOW_ROW_LOCKS = OFF y ALLOW_PAGE_LOCK = OFF, solo se permiten los bloqueos de nivel de tabla cuando se tiene acceso al índice. Si una tabla tiene restricciones FOREIGN KEY o CHECK, y desencadenadores, las condiciones de restricción se evalúan antes de que se ejecute el desencadenador. Para obtener un informe de una tabla y sus columnas, utilice sp_help o sp_helpconstraint. Para cambiar el nombre de una tabla, utilice sp_rename. Para obtener un informe de las vistas y los procedimientos almacenados que dependen de una tabla, utilice sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities. Reglas de nulabilidad en una definición de tabla La nulabilidad de una columna determina si esa columna puede permitir un valor nulo (NULL) para sus datos. NULL no es lo mismo que cero o en blanco: NULL significa que no se ha especificado ninguna entrada o que se ha proporcionado un valor NULL explícito, y suele implicar que se desconoce el valor o que no es aplicable. Cuando cree o modifique una tabla con las instrucciones CREATE TABLE o ALTER TABLE, la configuración de la sesión y de la base de datos influirá en la nulabilidad para el tipo de datos utilizado en la definición de columna y, posiblemente, la invalidará. Se recomienda que defina siempre explícitamente una columna como NULL o NOT NULL en el caso de columnas no calculadas o, si utiliza un tipo de datos definido por el usuario, que permita que la columna utilice la nulabilidad predeterminada del tipo de datos. Las columnas dispersas siempre deben permitir valores NULL. 







69

Si la nulabilidad de la columna no se especifica explícitamente, seguirá las reglas que se muestran en la tabla siguiente. Tipo de datos de columna

Regla

Tipo de datos de alias

Motor de base de datos utiliza la nulabilidad especificada al crear el tipo d nulabilidad predeterminada del tipo de datos, utilice sp_help.

Tipo definido por el usuario CLR

La nulabilidad se determina de acuerdo con la definición de columna.

Tipo de datos suministrado por el sistema

Si el tipo de datos suministrado por el sistema solo tiene una opción, esta datos timestamp deben ser NOT NULL. Si la configuración de sesión se establece en ON con SET: Si ANSI_NULL_DFLT_ON = ON, se asigna NULL. Si ANSI_NULL_DFLT_OFF = ON, se asigna NOT NULL. Si la base de datos se configura con ALTER DATABASE: Si ANSI_NULL_DEFAULT_ON = ON, se asigna NULL. Si ANSI_NULL_DEFAULT_OFF = ON, se asigna NOT NULL. Para ver la configuración de la base de datos para ANSI_NULL_D catálogo sys.databases.      

Cuando ninguna de las opciones ANSI_NULL_DFLT está establecida para la sesión y la base de datos tiene la configuración predeterminada (ANSI_NULL_DEFAULT es OFF), se asigna el valor predeterminado NOT NULL. En el caso de una columna calculada, el Motor de base de datos determinará automáticamente su nulabilidad. Para determinar la nulabilidad en este tipo de columna, utilice la función COLUMNPROPERTY con la propiedad AllowsNull. Nota

El controlador ODBC de SQL Server y el proveedor Microsoft OLE DB para SQL Server tienen un valor pr ANSI_NULL_DFLT_ON establecido en ON. Los usuarios de ODBC y OLE DB pueden configurar esto en l propiedades o atributos de la conexión establecidos por la aplicación.

Compresión de datos En las tablas del sistema no se puede habilitar la compresión. Cuando se crea una tabla, la compresión de datos se establece en NONE, a menos que se especifique otra cosa. Si se especifica una lista de particiones o una partición que están fuera del intervalo, se generará un error. Para obtener más información acerca de la compresión de datos, vea Compresión de datos. Para evaluar cómo afecta el cambio del estado de compresión a una tabla, un índice o una partición, utilice el procedimiento almacenado sp_estimate_data_compression_savings. Permisos 70

Se necesita el permiso CREATE TABLE en la base de datos y el permiso ALTER en el esquema en que se crea la tabla. Si las columnas de la instrucción CREATE TABLE se definen como un tipo definido por el usuario CLR, se necesita la propiedad del tipo o el permiso REFERENCES. Si las columnas de la instrucción CREATE TABLE tienen asociada una colección de esquemas XML, se necesita la propiedad de la colección de esquemas XML o el permiso REFERENCES. Cualquier usuario puede crear tablas temporales en tempdb. Ejemplos A.Crear una restricción PRIMARY KEY en una columna En el ejemplo siguiente se muestra la definición de columna para una restricción PRIMARY KEY con un índice clúster en la columna EmployeeID de la tabla Employee. Como no se especifica un nombre de restricción, el sistema proporciona el nombre de la restricción. CREATE TABLE dbo.Employee (EmployeeID int PRIMARY KEY CLUSTERED);

B.Usar restricciones FOREIGN KEY Una restricción FOREIGN KEY se utiliza para hacer referencia a otra tabla. Las claves externas pueden ser claves de una sola columna o de varias columnas. En el siguiente ejemplo se muestra una restricción FOREIGN KEY de una única columna en la tabla SalesOrderHeader que hace referencia a la tabla SalesPerson. Solo se requiere la cláusula REFERENCES para una restricción FOREIGN KEY de una única columna. SalesPersonID int NULL REFERENCES SalesPerson(SalesPersonID)

También puede utilizar la cláusula FOREIGN KEY de forma explícita y volver a formular el atributo de columna. Observe que no es necesario que el nombre de la columna sea el mismo en ambas tablas. FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)

Las restricciones de claves de varias columnas se crean como restricciones de tabla. En la base de datos AdventureWorks2012 , la tabla SpecialOfferProduct incluye una restricción PRIMARY KEY de varias columnas. En el siguiente ejemplo se muestra cómo hacer referencia a esta clave desde otra tabla; el nombre explícito de restricción es opcional. CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY (ProductID, SpecialOfferID) REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)

C.Usar restricciones UNIQUE 71

Las restricciones UNIQUE se utilizan para exigir la unicidad en las columnas de clave no principal. En el siguiente ejemplo se exige la restricción de que la columna Name de la tabla Product debe ser única. Name nvarchar(100) NOT NULL UNIQUE NONCLUSTERED

D.Usar definiciones DEFAULT Los valores predeterminados suministran un valor (con las instrucciones INSERT y UPDATE) cuando no se especifica ninguno. Por ejemplo, la base de datos AdventureWorks2012 puede incluir una tabla de búsqueda con los distintos trabajos que los empleados pueden realizar en la compañía. En la columna que describe cada trabajo, el valor predeterminado de cadena de caracteres puede suministrar una descripción si no se ha escrito una descripción de forma explícita. DEFAULT 'New Position - title not formalized yet'

Además de constantes, las definiciones de DEFAULT pueden incluir funciones. Utilice el siguiente ejemplo para obtener la fecha actual de una entrada. DEFAULT (getdate())

Un recorrido de las funciones niládicas puede mejorar también la integridad de los datos. Para realizar un seguimiento del usuario que ha insertado una fila, utilice la función niládica para USER. No escriba las funciones niládicas entre paréntesis. DEFAULT USER

E.Usar restricciones CHECK En el siguiente ejemplo se muestra una restricción para los valores escritos en la columna CreditRating de la tabla Vendor. La restricción no tiene nombre. CHECK (CreditRating >= 1 and CreditRating <= 5)

En este ejemplo se muestra una restricción con nombre con una restricción de patrón en los datos de caracteres escritos en la columna de la tabla. CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')

En este ejemplo se especifica que los valores se deben incluir en una lista específica o seguir un patrón dado. CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756') OR emp_id LIKE '99[0-9][0-9]')

F.Mostrar la definición de tabla completa En el siguiente ejemplo se muestran las definiciones de tablas completas con todas las definiciones de restricciones para la tabla PurchaseOrderDetail creada en la base de datos AdventureWorks2012 . Tenga en cuenta que, para ejecutar el ejemplo, el esquema de tabla se cambia a dbo. Transact-SQL 72

CREATE TABLE dbo.PurchaseOrderDetail ( PurchaseOrderID int NOT NULL REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID), LineNumber smallint NOT NULL, ProductID int NULL REFERENCES Production.Product(ProductID), UnitPrice money NULL, OrderQty smallint NULL, ReceivedQty float NULL, RejectedQty float NULL, DueDate datetime NULL, rowguid uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT DF_PurchaseOrderDetail_rowguid DEFAULT (newid()), ModifiedDate datetime NOT NULL CONSTRAINT DF_PurchaseOrderDetail_ModifiedDate DEFAULT (getdate()), LineTotal AS ((UnitPrice*OrderQty)), StockedQty AS ((ReceivedQty-RejectedQty)), CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber PRIMARY KEY CLUSTERED (PurchaseOrderID, LineNumber) WITH (IGNORE_DUP_KEY = OFF) ) ON PRIMARY;

G.Crear una tabla con una columna xml con tipo en una colección de esquemas XML En el siguiente ejemplo se crea una tabla con una columna xml con tipo de la colección de esquemas XML HRResumeSchemaCollection. La palabra clave DOCUMENT especifica que cada instancia del tipo de datos xml decolumn_name solo puede contener un elemento de nivel superior. Transact-SQL USE AdventureWorks2012; GO CREATE TABLE HumanResources.EmployeeResumes (LName nvarchar(25), FName nvarchar(25), Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );

H.Crear una tabla con particiones En el siguiente ejemplo se crea una función de partición para crear cuatro particiones en una tabla o en un índice. A continuación, se crea un esquema de partición en el que se especifican los grupos de archivos que van a contener cada 73

una de las cuatro particiones. Finalmente, en el ejemplo se crea una tabla que utiliza el esquema de partición. En este ejemplo se supone que los grupos de archivos ya existen en la base de datos. Transact-SQL CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000) ; GO CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; GO CREATE TABLE PartitionTable (col1 int, col2 char(10)) ON myRangePS1 (col1) ; GO

En función de los valores de la columna col1 de PartitionTable, las particiones se asignan de las maneras siguientes. Grupo de archivos

test1fg

test2fg

test3fg

Partición

1

2

3

Valores

col 1 <= 1

col1 > 1 AND col1 <= 100

col1 > 100 AND col1

I.Usar el tipo de datos uniqueidentifier en una columna En el siguiente ejemplo se crea una tabla con una columna uniqueidentifier. En el ejemplo se utiliza una restricción PRIMARY KEY para impedir que los usuarios inserten valores duplicados, y se utiliza la funciónNEWSEQUENTIALID() de la restricción DEFAULT para proporcionar valores a las nuevas filas. Se aplica la propiedad ROWGUIDCOL a la columna uniqueidentifier de modo que se pueda hacer referencia a la misma mediante la palabra clave $ROWGUID. Transact-SQL CREATE TABLE dbo.Globally_Unique_Data (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, Employee_Name varchar(60) CONSTRAINT Guid_PK PRIMARY KEY (guid) );

J.Usar una expresión para una columna calculada En el siguiente ejemplo se muestra el uso de una expresión ((low + high)/2 ) para calcular la columna calculada myavg. Transact-SQL CREATE TABLE dbo.mytable ( low int, high int, myavg AS (low + high)/2 ) ; 74

K.Crear una columna calculada en función de una columna de tipo definido por el usuario En el siguiente ejemplo se crea una tabla con una columna de tipo definido por el usuario utf8string dando por supuesto que el ensamblado del tipo y el propio tipo ya se han creado en la base de datos actual. La segunda columna se define en función de utf8string y utiliza el método ToString() de type(class)utf8string para calcular el valor de la columna. Transact-SQL CREATE TABLE UDTypeTable ( u utf8string, ustr AS u.ToString() PERSISTED ) ;

L.Usar la función USER_NAME para una columna calculada En el siguiente ejemplo se utiliza la función USER_NAME() en la columna myuser_name. Transact-SQL CREATE TABLE dbo.mylogintable ( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;

M.Crear una tabla que tenga una columna FILESTREAM En el ejemplo siguiente se crea una tabla que tiene una columna PhotoFILESTREAM. Si una tabla tiene una o varias columnas FILESTREAM, la tabla debe tener una columna ROWGUIDCOL. Transact-SQL CREATE TABLE dbo.EmployeePhoto ( EmployeeId int NOT NULL PRIMARY KEY ,Photo varbinary(max) FILESTREAM NULL ,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID() );

N.Crear una tabla que use compresión de fila En el ejemplo siguiente se crea una tabla que usa la compresión de fila. Transact-SQL CREATE TABLE dbo.T1 (c1 int, c2 nvarchar(200) ) WITH (DATA_COMPRESSION = ROW);

Para obtener más ejemplos de compresión de datos, vea Compresión de datos. O.Crear una tabla que tenga columnas dispersas y un conjunto de columnas En los ejemplos siguientes se muestra cómo crear una tabla que tenga una columna dispersa y una tabla que tenga dos columnas dispersas y un conjunto de 75

La primera página de cada archivo es una página de encabezado de archivo que contiene información acerca de los atributos del archivo. Algunas de las otras páginas del comienzo del archivo también contienen información de sistema, como mapas de asignación. Una de las páginas de sistema almacenadas en el archivo de datos principal y en el archivo de registro principal es una página de arranque de la base de datos que contiene información acerca de los atributos de la base de datos. Para obtener más información acerca de las páginas y los tipos de páginas, vea Descripción de páginas y extensiones. extensiones. Tamaño de archivo Los archivos de SQL Server pueden crecer de forma automática a partir del tamaño especificado inicialmente. Cuando se define un archivo, se puede especificar un incremento de crecimiento. Cada vez que se llena el archivo, el tamaño aumenta en la cantidad especificada. Si hay varios archivos en un grupo de archivos, no crecerán automáticamente hasta que todos los archivos estén llenos. A continuación, el crecimiento tiene lugar por turnos. Cada archivo también puede tener un tamaño máximo especificado. Si no se especifica un tamaño máximo, el archivo puede crecer hasta utilizar todo el espacio disponible en el disco. Esta característica es especialmente útil cuando SQL Server se utiliza como una base de datos incrustada en una aplicación para la que el usuario no dispone fácilmente de acceso a un administrador del sistema. El usuario puede dejar que los archivos crezcan automáticamente cuando sea necesario y evitar así las tareas administrativas de supervisar la cantidad de espacio disponible en la base de datos y asignar más espacio manualmente. Archivos de instantáneas de bases de datos La forma de archivo que utiliza una instantánea de base de datos para almacenar sus datos de copia por escritura depende de si la instantánea la ha creado un usuario o se utiliza internamente: Una instantánea de base de datos que crea un usuario almacena sus datos en uno o más archivos dispersos. La tecnología de archivos dispersos es una característica del sistema de archivos NTFS. Al principio, un archivo disperso no incluye datos de usuario y no se le asigna espacio en disco. Para obtener información general sobre el uso de los archivos dispersos en instantáneas de bases de datos y el crecimiento de éstas, vea Funcionamiento de las instantáneas de la base de datos y Descripción del tamaño de los archivos dispersos en instantáneas de bases de datos. datos . Las instantáneas de bases de datos las utilizan internamente algunos comandos DBCC. Entre estos comandos se incluyen: DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC y DBCC CHECKFILEGROUP. Una 



36

SQL Server Database Engine (Motor de base de datos de SQL Server) crea automáticamente un índice único para hacer cumplir los requisitos de unicidad de una restricción PRIMARY KEY o UNIQUE. De forma predeterminada se crea un índice clúster único para hacer cumplir una restricción PRIMARY KEY, a menos que ya exista un índice clúster en la tabla o que usted especifique un índice no clúster único. De forma predeterminada se crea un índice único no clúster para hacer cumplir una restricción UNIQUE a menos que se especifique de explícitamente un índice clúster único y no exista un índice clúster en la tabla. También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición. Un índice creado como parte de una restricción PRIMARY KEY o UNIQUE recibe automáticamente el mismo nombre que la restricción. Para obtener más información, vea Restricciones PRIMARY KEY yRestricciones UNIQUE. Creando un índice independiente de una restricción utilizando la instrucción CREATE INDEX , o el cuadro de diálogo Nuevo índice en el Explorador de objetos de SQL Server Management Studio Debe especificar el nombre del índice, de la tabla y de las columnas a las que se aplica el índice. También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición. De forma predeterminada, se crea un índice que no es único y no está agrupado si no se especifican las opciones únicas o agrupadas. Para crear un índice filtrado, use la cláusula opcional WHERE. Para obtener más información, vea Directrices generales para diseñar índices filtrados. 3. Crear el índice. Un factor importante que debe tenerse en cuenta es si el índice se creará en una tabla vacía o en una tabla con datos. La creación de un índice en una tabla vacía no tiene implicaciones de rendimiento en el momento de creación del índice; sin embargo, el rendimiento se verá afectado cuando se agreguen los datos a la tabla. La creación de índices en tablas grandes debe planearse con cuidado para que el rendimiento de la base de datos no se vea afectado. La mejor manera de crear índices en tablas de gran tamaño es empezar con el índice clúster y, a continuación, generar los índices no clúster. Considere la posibilidad de establecer la opción ONLINE en ON cuando cree índices en tablas existentes. Cuando se establece en ON, los bloqueos a largo plazo no se retienen, lo que permite que continúen consultas o actualizaciones a la tabla o

77

Ejemplo de archivos y grupos de archivos En el siguiente ejemplo se crea una base de datos con una contraseña de SQL Server. La base de datos tiene un archivo de datos principal, un grupo de archivos definido por el usuario y el archivo de registro. El archivo de datos principal está en el grupo de archivos principal y el grupo de archivos definido por el usuario tiene dos archivos de datos secundarios. Una instrucción ALTER DATABASE hace que el grupo de archivos definido por el usuario sea el grupo predeterminado. A continuación, se crea una tabla que especifica el grupo de archivos definido por el usuario. USE master; GO -- Create the database with the default data -- filegroup and a log file. Specify the -- growth increment and the max size for the -- primary data file. CREATE DATABASE MyDB ON PRIMARY ( NAME='MyDB_Primary', FILENAME= 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_Prm.mdf', SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB), FILEGROUP MyDB_FG1 ( NAME = 'MyDB_FG1_Dat1', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_1.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB), ( NAME = 'MyDB_FG1_Dat2', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_2.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB) LOG ON ( NAME='MyDB_log', FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB.ldf', SIZE=1MB, 38

Tipos de datos de objetos grandes (LOB):image, ntext, text, varchar(max),nvarchar(max), varbinary(max) y xml

No pueden ser una c de índice. No obsta columna XML pue columna de clave en un índice XML secu principal. Pueden participar co sin clave (incluidas) clúster, excepto ima Pueden participar si expresión de colum

Columnas calculadas

No se pueden indiza columnas calculadas invocaciones de mét columna del tipo de usuario CLR, mient se marquen como de Las columnas calcul derivan de tipos de pueden indizar com clave o sin clave mi datos de columna ca permita como colu índice o columna si

Columnas de Varchar de inserción no consecutiva

La clave de índice d clúster no puede co columnas varchar c existentes en la unid ROW_OVERFLO índice clúster se cre columna varchar y existentes están en l asignación IN_RO acciones de inserció posteriores de la col constituirían inserci consecutivas produc

geometry

Se puede indizar co espaciales.

Consideraciones adicionales A continuación se ofrecen algunas consideraciones adicionales para crear un índice: 79

Puede crear un índice si tiene el permiso CONTROL o ALTER en la tabla. Cuando se crea, el índice se habilita automáticamente y está disponible para su uso. Puede quitar el acceso a un índice deshabilitándolo. Para obtener más información, vea Deshabilitar índices. Requisitos de espacio en disco 



El espacio en disco necesario para almacenar el índice depende de los siguientes factores: El tamaño de cada fila de datos de la tabla y el número de filas por página. Así se determina el número de páginas de datos que se deben leer del disco para crear el índice. Las columnas del índice y los tipos de datos utilizados. Así se determina el número de páginas de índice que se deben escribir en disco. Para obtener más información, vea Estimar el tamaño de un índice clúster yEstimar el tamaño de un índice no clúster. Espacio temporal en disco necesario durante el proceso de creación del índice. Para obtener más información, vea Determinar requisitos de espacio en disco del índice. Consideraciones de rendimiento 





El tiempo que ocupa la creación física de un índice depende en gran medida del subsistema de disco. Los factores importantes que se deben tener en cuenta son: El modelo de recuperación de la base de datos. El modelo de recuperación optimizado para cargas masivas de registros proporciona un rendimiento mucho mayor y un consumo de espacio de registro más reducido que la recuperación completa durante la operación de creación del índice. Sin embargo, la recuperación por medio de registros de operaciones masivas reduce la flexibilidad para la recuperación a un momento dado. Para obtener más información, vea Elegir un modelo de recuperación para las operaciones de índice. RAID (matriz redundante de discos económicos) utilizada para almacenar los archivos de base de datos y del registro de transacciones. Normalmente, los niveles de RAID que utilizan la creación de bandas tienen un ancho de banda de E/S mejor. Número de discos de la matriz de discos, si se utiliza RAID. Más unidades en la matriz aumentan las tasas de transferencia de datos proporcionalmente. Dónde se almacenan las ordenaciones intermedias de los datos. Si utiliza la opción SORT_IN_TEMPDB puede reducir el tiempo necesario para crear un índice cuando tempdb se encuentra en un conjunto de discos diferente que 







80

Vistas de catálogo de archivos y bases de datos

Vistas de catálogo de la función de partición

Vistas del correo electrónico de base de datos

Vistas de la administración basada en directivas

Vistas de catálogo de creación de reflejo de la base de datos (Transact-SQL)

Vistas de catálogo del regulador de recursos

Vistas de catálogo del recopilador de datos

Vistas de catálogo de tipos escalares

Espacios de datos

Vistas de catálogo de esquema

Vistas de catálogo de extremos

Vistas de catálogo de seguridad

Vistas de catálogo de eventos extendidos

Vistas de catálogo de Service Broker

Vistas de catálogo de propiedades extendidas

Vistas de catálogo de la configuración del servidor

Vistas de catálogo de FileTable

Vistas de catálogo de esquemas XML (sistema de tipo XML)

Vistas de catálogo relacionadas con la búsqueda de texto completo y la búsqueda semántica

Tipos de datos (Transact-SQL)

SQL Server 2012

Personas que lo han encontrado útil: 16 de 23 - Valorar este tema En SQL Server, cada columna, variable local, expresión y parámetro tiene un tipo de datos relacionado. Un tipo de datos es un atributo que especifica el tipo de datos que el objeto puede contener: datos de enteros, datos de caracteres, datos de moneda, datos de fecha y hora, cadenas binarias, etc. SQL Server proporciona un conjunto de tipos de datos del sistema que define todos los tipos de datos que pueden utilizarse con SQL Server. También puede definir sus propios tipos de datos en Transact-SQL o Microsoft .NET 41

la base de datos del usuario. Para obtener más información, vea tempdb y la creación de índices. Creación del índice en línea o sin conexión. Cuando se crea un índice sin conexión (valor predeterminado), los bloqueos exclusivos se mantienen en la tabla subyacente hasta que la transacción que crea el índice se ha completado. La tabla no está accesible para los usuarios mientras se crea el índice. Excepto en el caso de los índices XML y los índices espaciales, es posible especificar que se cree el índice en línea. Cuando la opción en línea está establecida en ON, los bloqueos de la tabla a largo plazo no se conservan, lo que permite que las consultas o actualizaciones a la tabla subyacente continúen mientras se crea el índice. Aunque recomendamos operaciones de índice en línea, se debe evaluar el entorno y los requisitos específicos. Puede ser mejor ejecutar operaciones de índice sin conexión. Al hacerlo así, los usuarios tienen acceso restringido a los datos durante la operación, pero la operación acaba con mayor rapidez y utiliza menos recursos. Para obtener más información, vea Realizar operaciones de índices en línea. Para crear una restricción PRIMARY KEY o UNIQUE al crear una tabla CREATE TABLE Para crear una restricción PRIMARY KEY o UNIQUE en una tabla existente ALTER TABLE Para crear un índice CREATE INDEX CREATE SPATIAL INDEX (Transact-SQL) CREATE XML INDEX (Transact-SQL) Cómo crear un índice espacial (SQL Server Management Studio) 













Reorganizar y volver a generar índices

SQL Server 2012

Personas que lo han encontrado útil: 2 de 2 - Valorar este tema En este tema se describe cómo reorganizar o volver a generar un índice fragmentado en SQL Server 2012 mediante SQL Server Management Studio o Transact-SQL. Motor de base de datos de SQL Server mantiene los índices automáticamente cada vez que se realizan operaciones de inserción, actualización o eliminación en los datos subyacentes. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse por la base de datos (se fragmente). La fragmentación ocurre cuando los índices tienen páginas en las que 81

la ordenación lógica, basada en el valor de clave, no coincide con la ordenación física dentro del archivo de datos.Los índices muy fragmentados pueden reducir el rendimiento de la consulta y ralentizar la respuesta de la aplicación. Puede solucionar la fragmentación del índice reorganizándolo o volviéndolo a generar. Para los índices con particiones generados en un esquema de partición, puede usar cualquiera de estos métodos en un índice completo o en una sola partición de un índice. El proceso de volver a generar un índice quita y vuelve a crear el índice. Quita la fragmentación, utiliza espacio en disco al compactar las páginas según el valor de factor de relleno especificado o existente y vuelve a ordenar las filas del índice en páginas contiguas. Cuando se especifica ALL, todos los índices de la tabla se quitan y se vuelven a generar en una única transacción. La reorganización de un índice emplea muy pocos recursos del sistema. Desfragmenta el nivel hoja de los índices clúster y no clúster de las tablas y las vistas reordenando físicamente las páginas de nivel hoja para que coincidan con el orden lógico de los nodos hoja, de izquierda a derecha. La reorganización también compacta las páginas de índice. La compactación se basa en el valor de factor de relleno existente. En este tema Antes de empezar: Detectar la fragmentación Limitaciones y restricciones Seguridad Para comprobar la fragmentación de un índice, usando: SQL Server Management Studio Transact-SQL Para reorganizar o volver a generar un índice, usando: SQL Server Management Studio Transact-SQL Antes de empezar 





Detectar la fragmentación El primer paso necesario para detectar qué método de desfragmentación utilizar es analizar el índice a fin de determinar la magnitud de la fragmentación. Si utiliza la función del sistema sys.dm_db_index_physical_stats, podrá detectar la fragmentación de un índice específico, de todos los índices de una tabla o vista indizada, de todos los índices de una base de datos o de todos los índices de todas las bases de datos. Para los índices con particiones, sys.dm_db_index_physical_stats también proporciona información de la fragmentación para cada partición. 82

El conjunto de resultados devuelto por la función sys.dm_db_index_physical_stats tiene las columnas siguientes. Columna

Descripción

avg_fragmentation_in_percent

Porcentaje de fragmentación lógica (páginas de un índice que no f

fragment_count

Número de fragmentos (páginas hoja físicamente consecutivas) en

avg_fragment_size_in_pages

Número promedio de páginas en un fragmento del índice.

Una vez determinada la magnitud de la fragmentación, utilice la siguiente tabla para determinar el mejor método para corregir la fragmentación propiamente dicha. Valor de avg_fragmentation_in_percent

Instrucción correctiva

> 5% y < = 30%

ALTER INDEX REORGANIZE

> 30%

ALTER INDEX REBUILD WITH (ON

* La regeneración de un índice se puede ejecutar en línea o sin conexión. La reorganización de un índice siempre se ejecuta en línea. Para lograr una disponibilidad similar a la opción de reorganización, debe volver a generar los índices en línea. Estos valores proporcionan directrices generales para la determinación del punto en el que debe cambiar entre ALTER INDEX REORGANIZE y ALTER INDEX REBUILD. No obstante, los valores reales pueden variar de un caso a otro. Es importante que experimente la determinación del mejor umbral para su entorno. Los niveles de fragmentación muy bajos (inferiores al 5 por ciento) no deben tratarse con ninguno de estos comandos, dado que el beneficio de quitar una cantidad de fragmentación tan pequeña es casi siempre ampliamente superado por el costo de reorganizar o volver a generar el índice. Nota

En general, la fragmentación en índices pequeños normalmente no se puede controlar. Las páginas de índice extensiones mixtas. Las extensiones mixtas pueden estar compartidas por hasta ocho objetos, de modo que e fragmentación en un índice pequeño después de reorganizar o volver a generar dicho índice.

Limitaciones y restricciones Los índices que tienen más de 128 extensiones se vuelven a generar en dos fases independientes: lógica y física. En la fase lógica, las unidades de asignación existentes que utiliza el índice están señaladas para cancelación de asignación las filas de datos se copian y ordenan y luego se mueven a las nuevas unidades de asignación creadas para almacenar el índice 

83

recompilado. En la fase física, las unidades de asignación previamente señaladas para cancelación de asignación se quitan físicamente de las transacciones breves que se realizan en segundo plano y no requieren demasiados bloqueos. Las opciones del índice no se pueden especificar al reorganizar un índice. Seguridad 

Permisos

Requiere el permiso ALTER en la tabla o la vista. El usuario debe ser miembro del rol fijo de servidor sysadmin o de los roles fijos de base de datos db_ddladmin y db_owner. [Arriba] Usar SQL Server Management Studio Para comprobar la fragmentación de un índice 1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea comprobar la fragmentación de un índice. 2. Expanda la carpeta Tablas. 3. Expanda la tabla en la que desea comprobar la fragmentación de un índice. 4. Expanda la carpeta Índices. 5. Haga clic con el botón secundario en el índice en el que desea comprobar la fragmentación y seleccione Propiedades. 6. Bajo Seleccionar una página, seleccione Fragmentación. La siguiente información está disponible en la página Fragmentación: Llenado de página Indica el promedio de llenado de las páginas de índice como un porcentaje. 100% indica que las páginas de índice están completamente llenas. 50% indica que, como promedio, las páginas de índice están llenas a la mitad. Fragmentación total Porcentaje de fragmentación lógica. Indica el número de páginas de un índice que no están almacenadas en orden. Promedio de tamaño de fila Tamaño medio de una fila de nivel hoja. Profundidad Número de niveles del índice, incluido el nivel hoja. Registros reenviados 84

Número de registros de un montón que han reenviado punteros a otra ubicación de datos. Este estado se produce durante una actualización, cuando no existe suficiente espacio para almacenar la nueva fila en la ubicación original. Filas fantasma Número de filas marcadas como eliminadas que todavía no se han quitado. Estas filas se quitarán en un subproceso de limpieza, cuando el servidor no esté ocupado. Este valor no incluye las filas que se retienen debido a una transacción pendiente de aislamiento de instantáneas. Tipo de índice Tipo de índice. Los valores posibles son Índice clúster, Índice no clúster y XML principal. Las tablas también se pueden almacenar como un montón (sin índices), pero en tal caso la página Propiedades del índice no puede abrirse. Filas de nivel de hoja Número de filas de nivel hoja. Tamaño máximo de la fila Tamaño máximo de la fila de nivel de hoja. Tamaño mínimo de la fila Tamaño mínimo de la fila de nivel de hoja. Páginas Número total de páginas de datos. Id. de partición Id. de partición del árbol b que contiene el índice. Filas fantasma de la versión Número de registros fantasma que se conservan debido a una transacción pendiente de aislamiento de instantáneas. [Arriba] Usar Transact-SQL Para comprobar la fragmentación de un índice 1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos. 2. En la barra Estándar, haga clic en Nueva consulta. 85

3. Copie y pegue el ejemplo siguiente en la ventana de consulta y haga clic en Ejecutar. 4. 5. 6. 7. 8. 9.

USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a 10. JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; 11. GO

La instrucción anterior puede devolver un conjunto de resultados similar al siguiente: index_id name avg_fragmentation_in_percent ----------- ---------------------------------------------------- ---------------------------1 PK_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 (6 row(s) affected)

Para obtener más información, vea sys.dm_db_index_physical_stats (Transact-SQL). [Arriba] Usar SQL Server Management Studio Para reorganizar o volver a generar un índice 1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice. 2. Expanda la carpeta Tablas. 3. Expanda la tabla en la que desea reorganizar un índice. 4. Expanda la carpeta Índices. 86

5. Haga clic con el botón secundario en el índice que desee reorganizar y seleccione Reorganizar. 6. En el cuadro de diálogo Reorganizar índices, compruebe que el índice correcto se encuentra en la cuadrícula Índices que se van a reorganizar y haga clic en Aceptar. 7. Active la casilla Compactar datos de columnas de objetos de gran tamaño para especificar que se compacten también todas las páginas que contengan datos de objetos grandes (LOB). 8. Haga clic en Aceptar. Para reorganizar todos los índices de una tabla 1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar los índices. 2. Expanda la carpeta Tablas. 3. Expanda la tabla en la que desea reorganizar los índices. 4. Haga clic con el botón secundario en la carpeta Índices y seleccione Reorganizar todo. 5. En el cuadro de diálogo Reorganizar índices, compruebe que los índices adecuados están en Índices que se van a reorganizar. Para quitar un índice de la cuadrícula Índices que se van a reorganizar, seleccione el índice y, a continuación, presione la tecla SUPR. 6. Active la casilla Compactar datos de columnas de objetos de gran tamaño para especificar que se compacten también todas las páginas que contengan datos de objetos grandes (LOB). 7. Haga clic en Aceptar. Para volver a generar un índice 1. En el Explorador de objetos, expanda la base de datos que contiene la tabla en la que desea reorganizar un índice. 2. Expanda la carpeta Tablas. 3. Expanda la tabla en la que desea reorganizar un índice. 4. Expanda la carpeta Índices. 5. Haga clic con el botón secundario en el índice que desee reorganizar y seleccione Reorganizar. 6. En el cuadro de diálogo Volver a generar índices, compruebe que el índice correcto se encuentra en la cuadrícula Índices que se van a volver a generar y haga clic en Aceptar. 7. Active la casilla Compactar datos de columnas de objetos de gran tamaño para especificar que se compacten también todas las páginas que contengan datos de objetos grandes (LOB). 8. Haga clic en Aceptar. [Arriba] 87

Usar Transact-SQL Para reorganizar un índice desfragmentado 1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos. 2. En la barra Estándar, haga clic en Nueva consulta. 3. Copie y pegue el ejemplo siguiente en la ventana de consulta y haga clic en Ejecutar. 4. USE AdventureWorks2012; 5. GO 6. -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. 7. 8. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee 9. REORGANIZE ; 10. GO

Para reorganizar todos los índices de una tabla 1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos. 2. En la barra de Estándar, haga clic en Nueva consulta. 3. Copie y pegue el ejemplo siguiente en la ventana de consulta y haga clic en Ejecutar. 4. USE AdventureWorks2012; 5. GO 6. -- Reorganize all indexes on the HumanResources.Employee table. 7. ALTER INDEX ALL ON HumanResources.Employee 8. REORGANIZE ; 9. GO

Para volver a generar un índice desfragmentado 1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos. 2. En la barra Estándar, haga clic en Nueva consulta. 3. Copie y pegue el ejemplo siguiente en la ventana de consulta y haga clic en Ejecutar. En el ejemplo se vuelve a generar un único índice en la tabla Employee. Transact-SQL 88

USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO

Para volver a generar todos los índices de una tabla 1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datos. 2. En la barra Estándar, haga clic en Nueva consulta. 3. Copie y pegue el ejemplo siguiente en la ventana de consulta. En el ejemplo se especifica la palabra clave ALL. Así se regeneran todos los índices asociados a la tabla. Se especifican tres opciones. Transact-SQL USE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO Integridad de los datos

SQL Server 2008 R2 La exigencia de integridad de los datos garantiza la calidad de los datos de la base de datos. Por ejemplo, si se especifica para un empleado el valor de identificador de 123, la base de datos no debe permitir que ningún otro empleado tenga el mismo valor de identificador. Si tiene una columna employee_rating para la que se prevean valores entre 1 y 5, la base de datos no debe aceptar valores fuera de ese intervalo. Si en la tabla hay una columna dept_id en la que se almacena el número de departamento del empleado, la base de datos sólo debe permitir valores que correspondan a los números de departamento de la empresa. Dos pasos importantes en el diseño de las tablas son la identificación de valores válidos para una columna y la determinación de cómo forzar la integridad de los datos en la columna. La integridad de datos pertenece a una de las siguientes categorías: Integridad de entidad Integridad de dominio Integridad referencial Integridad definida por el usuario 







89

Integridad de entidad La integridad de entidad define una fila como entidad única para una tabla determinada. La integridad de entidad exige la integridad de las columnas de los identificadores o la clave principal de una tabla, mediante índices y restricciones UNIQUE, o restricciones PRIMARY KEY. Integridad de dominio La integridad de dominio viene dada por la validez de las entradas para una columna determinada. Puede exigir la integridad de dominio para restringir el tipo mediante tipos de datos, el formato mediante reglas y restricciones CHECK, o el intervalo de valores posibles mediante restricciones FOREIGN KEY, restricciones CHECK, definiciones DEFAULT, definiciones NOT NULL y reglas. Integridad referencial La integridad referencial protege las relaciones definidas entre las tablas cuando se crean o se eliminan filas. En SQL Server la integridad referencial se basa en las relaciones entre claves externas y claves principales o entre claves externas y claves exclusivas, mediante restricciones FOREIGN KEY y CHECK. La integridad referencial garantiza que los valores de clave sean coherentes en las distintas tablas. Para conseguir esa coherencia, es preciso que no haya referencias a valores inexistentes y que, si cambia el valor de una clave, todas las referencias a ella se cambien en consecuencia en toda la base de datos. Cuando se exige la integridad referencial, SQL Server impide a los usuarios: Agregar o cambiar filas en una tabla relacionada si no hay ninguna fila asociada en la tabla principal. Cambiar valores en una tabla principal que crea filas huérfanas en una tabla relacionada. Eliminar filas de una tabla principal cuando hay filas relacionadas coincidentes. Por ejemplo, en las tablas Sales.SalesOrderDetail y Production.Product de la base de datos AdventureWorks2008R2, la integridad referencial se basa en la relación entre la clave externa (ProductID) de la tablaSales.SalesOrderDetail y la clave principal (ProductID) de la tabla Production.Product. Esta relación garantiza que un pedido de ventas no pueda nunca hacer referencia a un producto que no existe en la tablaProduction.Product. 





90

Integridad definida por el usuario La integridad definida por el usuario permite definir reglas de empresa específicas que no pertenecen a ninguna otra categoría de integridad. Todas las categorías de integridad admiten la integridad definida por el usuario. Esto incluye todas las restricciones de nivel de columna y nivel de tabla en CREATE TABLE, procedimientos almacenados y desencadenadores. Exigir la integridad de los datos

SQL Server 2008 R2 Planear y crear tablas requiere identificar los valores válidos para las columnas y decidir cómo exigir la integridad de los datos en las columnas. SQL Server proporciona los siguientes mecanismos para exigir la integridad de los datos en una columna: Restricciones PRIMARY KEY Restricciones FOREIGN KEY Restricciones UNIQUE Restricciones CHECK Definiciones DEFAULT Permitir valores NULL 











CREATE PROCEDURE (Transact-SQL)

SQL Server 2012 Crea un procedimiento almacenado Transact-SQL o Common Language Runtime (CLR) en SQL Server 2012. Los procedimientos almacenados son similares a los procedimientos de otros lenguajes de programación en tanto que pueden:

91

Aceptar parámetros de entrada y devolver varios valores en forma de parámetros de salida al lote o al procedimiento que realiza la llamada. Contener instrucciones de programación que realicen operaciones en la base de datos, incluidas las llamadas a otros procedimientos. Devolver un valor de estado a un lote o a un procedimiento que realice una llamada para indicar si la operación se ha realizado correctamente o se han producido errores, y el motivo de estos. Use esta instrucción para crear un procedimiento permanente en la base de datos actual o un procedimiento temporal en la base de datos tempdb. Convenciones de sintaxis de Transact-SQL Sintaxis 





--Transact-SQL Stored Procedure Syntax CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY] ] [ ,...n ] [ WITH [ ,...n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } [;] ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ] --CLR Stored Procedure Syntax CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ = default ] [ OUT | OUTPUT ] [READONLY] ] [ ,...n ] [ WITH EXECUTE AS Clause ] AS { EXTERNAL NAME assembly_name.class_name.method_name } [;]

Argumentos schema_name

El nombre del esquema al que pertenece el procedimiento. Los procedimientos se enlazan a un esquema. Si no se especifica el nombre del 92

esquema cuando se crea el procedimiento, se asigna automáticamente el esquema predeterminado del usuario que crea este procedimiento. procedure_name

El nombre del procedimiento. Los nombres de los procedimientos deben cumplir las reglas de los identificadores y deben ser exclusivos en el esquema. Evite el uso del prefijo sp_ cuando asigne nombre a los procedimientos. SQL Server usa este prefijo para designar los procedimientos del sistema. Si usa el prefijo, puede provocar la ruptura del código de la aplicación si existe un procedimiento del sistema con el mismo nombre. Los procedimientos temporales locales o globales se pueden crear anteponiendo un signo de número (#) al parámetro procedure_name (#procedure_name) para los procedimientos temporales locales y dos signos de número (##procedure_name) para los procedimientos temporales globales. Solo la conexión que creó un procedimiento temporal local lo ve y se quita cuando se cierra esa conexión. Un procedimiento temporal global está disponible para todas las conexiones y se quita al final de la última sesión que lo use. No se pueden especificar nombres temporales para los procedimientos CLR. El nombre completo de un procedimiento o un procedimiento temporal global, incluidos los signos de número ##, no puede superar los 128 caracteres. El nombre completo de un procedimiento temporal local, incluido el signo de número #, no puede superar los 116 caracteres. ; number Entero opcional que se usa para agrupar procedimientos con el mismo nombre. Estos procedimientos agrupados se pueden quitar juntos mediante una instrucción DROP PROCEDURE. Nota

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

Los procedimientos numerados no pueden usar xml ni los tipos definidos por el usuario de CLR y no se pueden usar en una guía de plan. @ parameter Parámetro declarado en el procedimiento. Especifique un nombre de parámetro con una arroba (@) como el primer carácter. El nombre del parámetro se debe ajustar a las reglas de los identificadores. Los parámetros 93

son locales respecto al procedimiento; los mismos nombres de parámetro se pueden usar en otros procedimientos. Se pueden declarar uno o varios parámetros; el valor máximo es 2.100. El usuario debe proporcionar el valor de cada parámetro declarado cuando se llame al procedimiento, a menos que se haya definido un valor predeterminado para el parámetro o se haya establecido en el mismo valor que el de otro parámetro. Si un procedimiento contiene parámetros con valores de tabla y el parámetro no está en la llamada, se pasa una tabla vacía. Los parámetros solo pueden ocupar el lugar de expresiones constantes; no se pueden usar en lugar de nombres de tablas, nombres de columnas o nombres de otros objetos de base de datos. Para obtener más información, vea EXECUTE (Transact-SQL). No se pueden declarar los parámetros si se especifica FOR REPLICATION. [ type_schema_name. ] data_type El tipo de datos del parámetro y el esquema al que pertenece el tipo de datos. Directrices para procedimientos Transact-SQL: Todos los tipos de datos de Transact-SQL se pueden usar como parámetros. Puede usar el tipo de tabla definido por el usuario para crear parámetros con valores de tabla. Los parámetros con valores de tabla solo pueden ser parámetros INPUT y deben ir acompañados de la palabra clave READONLY. Para obtener más información, vea Usar parámetros con valores de tabla (motor de base de datos) Los tipos de datos de cursor solo pueden ser parámetros OUTPUT y deben ir acompañados de la palabra clave VARYING. Directrices para procedimientos CLR : Todos los tipos de datos de SQL Server nativos con un equivalente en código administrado se pueden usar como parámetros. Para obtener más información acerca de la correspondencia entre los tipos CLR y los tipos de datos del sistema de SQL Server, vea Asignar datos de parámetros CLR. Para obtener más información acerca de los tipos de datos del sistema de SQL Server y su sintaxis, vea Tipos de datos (Transact-SQL). Los tipos de datos con valores de tabla o de cursor no se pueden usar como parámetros. Si el tipo de datos del parámetro es un tipo definido por el usuario de CLR, se debe disponer del permiso EXECUTE en el tipo. VARYING 











94

Especifica el conjunto de resultados admitido como parámetro de salida. Este parámetro lo crea de forma dinámica el procedimiento y su contenido puede variar. Solo se aplica a los parámetros de tipo cursor.Esta opción no es válida para los procedimientos CLR. default

Valor predeterminado de un parámetro. Si se define un valor predeterminado para un parámetro, el procedimiento se puede ejecutar sin especificar ningún valor para ese parámetro. El valor predeterminado debe ser una constante o puede ser NULL. El valor constante puede tener el formato de un carácter comodín, lo que permite usar la palabra clave LIKE cuando se pase el parámetro al procedimiento. Vea el ejemplo C más adelante. Los valores predeterminados solo se registran en la columna sys.parameters.default de los procedimientos CLR. Esa columna será NULL para los parámetros de procedimientos Transact-SQL. OUT | OUTPUT Indica que se trata de un parámetro de salida. Use parámetros OUTPUT para devolver valores al autor de la llamada del procedimiento. Los parámetros text, ntext e image no se pueden usar como parámetros OUTPUT, a menos que se trate de un procedimiento CLR. Un parámetro de salida puede ser un marcador de posición de cursor, a menos que el procedimiento sea un procedimiento CLR. Un tipo de datos con valores de tabla no se puede especificar como parámetro OUTPUT de un procedimiento. READONLY Indica que el parámetro no se puede actualizar ni modificar en el cuerpo del procedimiento. Si el tipo de parámetro es un tipo con valores de tabla, se debe especificar READONLY. RECOMPILE Indica que Motor de base de datos no almacena en caché ningún plan de consulta para este procedimiento, forzándolo a ser compilado cada vez que se ejecute. Para obtener más información sobre las razones para forzar una nueva compilación, vea Volver a compilar un procedimiento almacenado. Esta opción no se puede usar cuando se especifica FOR REPLICATION ni para procedimientos CLR. Para indicar a Motor de base de datos que descarte planes de consulta para consultas individuales en un procedimiento, use la sugerencia de consulta 95

RECOMPILE en la definición de la consulta. Para obtener más información, vea Sugerencias de consulta (Transact-SQL). ENCRYPTION Indica que SQL Server convertirá el texto original de la instrucción CREATE PROCEDURE en un formato confuso. La salida de la ofuscación no se ve directamente en ninguna de las vistas de catálogo de SQL Server.Los usuarios que no dispongan de acceso a las tablas del sistema o a los archivos de base de datos no pueden recuperar el texto confuso. Sin embargo, estará disponible para los usuarios con privilegios que puedan obtener acceso a las tablas del sistema a través del puerto DAC o directamente a los archivos de base de datos. Además, los usuarios que puedan adjuntar un depurador al proceso del servidor pueden recuperar el procedimiento descifrado de la memoria en tiempo de ejecución. Para obtener más información acerca del acceso a los metadatos del sistema, vea Configuración de visibilidad de los metadatos. Esta opción no es válida para los procedimientos CLR. Los procedimientos creados mediante esta opción no se pueden publicar como parte de la replicación de SQL Server. EXECUTE AS Especifica el contexto de seguridad en el que se ejecuta el procedimiento. Para obtener más información, vea EXECUTE AS (cláusula de Transact-SQL). FOR REPLICATION Especifica que el procedimiento se crea para replicación. Por consiguiente, no se puede ejecutar en el suscriptor. Se usa un procedimiento creado con la opción FOR REPLICATION como filtro de procedimiento y solo se ejecuta durante la replicación. No se pueden declarar los parámetros si se especifica FOR REPLICATION. No se puede especificar FOR REPLICATION en los procedimientos CLR. La opción RECOMPILE se ignora en el caso de procedimientos creados con FOR REPLICATION. Un procedimiento FOR REPLICATION tendrá un tipo de objeto RF en sys.objects y sys.procedures. { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] } Una o más instrucciones Transact-SQL que comprenden el cuerpo del procedimiento. Puede usar las palabras clave BEGIN y END opcionales para incluir las instrucciones. Para obtener información, vea las secciones Prácticas recomendadas, Comentarios generales, así como Limitaciones y restricciones que aparecen más adelante. EXTERNAL NAME assembly_name.class_name.method_name 96

Especifica el método de un ensamblado de .NET Framework para un procedimiento CLR al que se va a hacer referencia. class_name debe ser un identificador válido de SQL Server y debe existir como clase en el ensamblado. Si la clase tiene un nombre completo de espacio de nombres que utiliza un punto (.) para separar las partes del espacio de nombres, el nombre de la clase debe delimitarse mediante paréntesis ([ ]) o comillas (" "). El método especificado debe ser un método estático de la clase. De manera predeterminada, SQL Server no puede ejecutar código CLR. Se pueden crear, modificar y quitar objetos de base de datos que hagan referencia a módulos de Common Language Runtime; sin embargo, estas referencias no se pueden ejecutar en SQL Server hasta que se habilite la opción clr enabled. Para habilitar esta opción, use sp_configure. Nota

Los procedimientos CLR no se admiten en las bases de datos independientes.

Prácticas recomendadas Aunque esta no es una lista de prácticas recomendadas exhaustiva, estas sugerencias pueden mejorar el rendimiento de los procedimientos. Use la instrucción SET NOCOUNT ON como la primera instrucción del cuerpo del procedimiento. Es decir, colóquela a continuación de la palabra clave AS. De esta forma, se desactivan los mensajes que devuelve SQL Server al cliente después de que se ejecute cualquier instrucción SELECT, INSERT, UPDATE, MERGE y DELETE. El rendimiento general de la base de datos y de la aplicación mejora si se elimina esta sobrecarga de red innecesaria. Para obtener información, vea SET NOCOUNT (Transact-SQL). Use nombres de esquemas cuando cree o haga referencia a los objetos de base de datos del procedimiento. El tiempo de procesamiento será menor para que Motor de base de datos resuelva los nombres de los objetos si no tiene que buscar en varios esquemas. Además, se evitarán problemas de permisos y acceso causados por el esquema predeterminado de un usuario que se asigna cuando se crean objetos sin especificar el esquema. Evite envolver con funciones las columnas especificadas en las cláusulas WHERE y JOIN . De esta forma, las columnas no son deterministas y se evita que el procesador de consultas use índices. Evite usar funciones escalares en instrucciones SELECT que devuelvan muchas filas de datos. Dado que la función escalar se debe aplicar a todas 







97













las filas, el comportamiento resultante es similar al procesamiento basado en filas y degrada el rendimiento. Evite el uso de SELECT *. En su lugar, especifique los nombres de columna necesarios. De esta forma, puede evitar algunos errores de Motor de base de datos que detengan la ejecución del procedimiento. Por ejemplo, una instrucción SELECT * que devuelve datos desde una tabla de 12 columnas y, a continuación, inserta los datos en una tabla temporal de 12 columnas funcionará correctamente hasta que cambie el número o el orden de las columnas de cualquiera de las tablas. Evite el procesamiento o la devolución de demasiados datos. Restrinja los resultados lo antes posible en el código del procedimiento para que las operaciones posteriores realizadas por él se lleven a cabo con el menor conjunto de datos posible. Envíe únicamente los datos fundamentales a la aplicación cliente. Es más eficaz que enviar datos adicionales a través de la red y forzar que dicha aplicación trabaje con conjuntos de resultados innecesariamente grandes. Use transacciones explícitas mediante el uso de BEGIN/END TRANSACTION y mantenga las transacciones lo más cortas posible. Las transacciones más largas significan bloqueos de registro más largos y mayores posibilidades de interbloqueos. Use la característica TRY…CATCH de Transact-SQL para el control de errores dentro de un procedimiento. TRY…CATCH puede encapsular todo un bloque de instrucciones Transact-SQL. Esto no solo crea una sobrecarga de rendimiento menor, sino que también hace que los informes de errores sean más precisos con mucha menos programación. Use la palabra clave DEFAULT en todas las columnas de la tabla a las que haga referencia en las instrucciones CREATE TABLE o ALTER TABLE de Transact-SQL en el cuerpo del procedimiento. De esta forma, se evita pasar el valor NULL a columnas que no admiten valores NULL. Use NULL o NOT NULL para todas las columnas de una tabla temporal. Las opciones ANSI_DFLT_ON y ANSI_DFLT_OFF controlan la forma en la que Motor de base de datos asigna los atributos NULL o NOT NULL a las columnas si no se especifican dichos atributos en una instrucción CREATE TABLE o ALTER TABLE. Si una conexión ejecuta un procedimiento con valores distintos para estas opciones a los que usó la conexión que creó el procedimiento, las columnas de la tabla creada para la segunda conexión pueden tener distinta nulabilidad y exhibir diferentes comportamientos. Si se especifica NULL o NOT NULL explícitamente para todas las columnas, las tablas temporales se crean con la misma nulabilidad para todas las conexiones que ejecuten el procedimiento almacenado. 98

Use instrucciones de modificación que conviertan valores NULL e incluya lógica que elimine las filas con valores NULL de las consultas. Tenga en cuenta que en Transact-SQL, NULL no es un valor vacío ni el valor “Nothing”. Es un marcador de posición para un valor desconocido y puede provocar un comportamiento inesperado, especialmente cuando se consultan conjuntos de resultados o se usan funciones AGGREGATE. Use el operador UNION ALL en vez de los operadores UNION u OR, a menos que exista una necesidad específica de valores distintos. El operador UNION ALL necesita menos sobrecarga de procesamiento porque no se filtran los duplicados del conjunto de resultados. Comentarios generales 



No hay ningún tamaño máximo predefinido para un procedimiento. Las variables especificadas en el procedimiento las puede definir el usuario o pueden ser variables del sistema, como @@SPID. Cuando un procedimiento se ejecuta por primera vez, se compila para determinar que dispone de un plan de acceso óptimo para recuperar los datos. En las siguientes ejecuciones del procedimiento se puede volver a usar el plan ya generado si aún permanece en la memoria caché de planes de Motor de base de datos. Cuando se inicia SQL Server, se pueden ejecutar automáticamente uno o varios procedimientos. Los procedimientos los debe crear el administrador del sistema en la base de datos maestra y ejecutarse bajo el rol fijo de servidor sysadmin como proceso en segundo plano. Los procedimientos no pueden tener ningún parámetro de entrada o salida. Para obtener más información, vea Ejecutar un procedimiento almacenado. Los procedimientos se anidan cuando un procedimiento llama a otro o ejecuta código administrado mediante una referencia a una rutina, tipo o agregado CLR. Los procedimientos y las referencias a código administrado se pueden anidar hasta 32 niveles. El nivel de anidamiento aumenta en uno cuando el procedimiento o la referencia a código administrado a los que se ha llamado empiezan a ejecutarse, y disminuye en uno cuando se completa su ejecución. Los métodos que se invocan desde el código administrado no cuentan para este límite de niveles de anidamiento. Sin embargo, cuando un procedimiento almacenado CLR realiza operaciones de acceso a datos mediante el proveedor administrado de SQL Server, se agrega un nivel de anidamiento adicional en la transición desde código administrado a SQL.

99

Si se intenta superar el nivel máximo de anidamiento, se producirá un error en toda la cadena de llamada. Puede usar la función @@NESTLEVEL para devolver el nivel de anidamiento de la ejecución del procedimiento almacenado actual. Interoperabilidad El Motor de base de datos guarda los valores de SET QUOTED_IDENTIFIER y de SET ANSI_NULLS cuando se crea o modifica un procedimiento de Transact-SQL. Estos valores originales se usan cuando se ejecuta el procedimiento. Por tanto, cualquier valor de sesión de cliente de SET QUOTED_IDENTIFIER y SET ANSI_NULLS se ignora durante la ejecución del procedimiento. Otras opciones de SET, como SET ARITHABORT, SET ANSI_WARNINGS o SET ANSI_PADDINGS, no se guardan cuando se crea o se modifica un procedimiento. Si la lógica del procedimiento depende de un valor específico, incluya una instrucción SET al inicio del procedimiento para garantizar el valor adecuado. Cuando una instrucción SET se ejecuta desde un procedimiento, el valor permanece en vigor solo hasta que se complete la ejecución del procedimiento. A continuación, el valor se restaura al que tenía cuando se llamó al procedimiento. Esto permite que clientes individuales establezcan las opciones deseadas sin afectar a la lógica del procedimiento. En un procedimiento se puede especificar cualquier instrucción SET, excepto SET SHOWPLAN_TEXT y SET SHOWPLAN_ALL. Éstas deben ser las únicas instrucciones del lote. La opción SET elegida permanece vigente durante la ejecución del procedimiento y, a continuación, revierte a su valor anterior. Nota

No se respeta SET ANSI_WARNINGS al pasar parámetros de un procedimiento almacenado, una función definida por el usuario o al declarar y establecer variables en una instrucción por lotes. Por ejemplo, si la a variable se define como char(3) y después se establece en un valor de más de tres caracteres, los datos se truncan en el tamaño definido y la instrucción INSERT o UPDATE se ejecuta correctamente.

Limitaciones y restricciones La instrucción CREATE PROCEDURE no se puede combinar con otras instrucciones Transact-SQL en un único lote. Las siguientes instrucciones no se pueden usar en ninguna parte del cuerpo de un procedimiento almacenado. CREATE AGGREGATE

CREATE SCHEMA

SET SHOWPLAN_TEXT 100

CREATE DEFAULT

CREATE o ALTER TRIGGER

SET SHOWPLAN_XML

CREATE o ALTER FUNCTION

CREATE o ALTER VIEW

USE database_name

CREATE o ALTER PROCEDURE

SET PARSEONLY

CREATE RULE

SET SHOWPLAN_ALL

Un procedimiento puede hacer referencia a tablas que aún no existan. En el momento de la creación, solo se realiza la comprobación de la sintaxis. El procedimiento no se compila hasta que se ejecute por primera vez.Solamente durante la compilación se resuelven todos los objetos a los que se haga referencia en el procedimiento. Por tanto, se puede crear un procedimiento con la sintaxis correcta que haga referencia a tablas que todavía no existan; no obstante, el procedimiento provocará un error en tiempo de ejecución si las tablas a las que hace referencia no existen. No puede especificar el nombre de una función como valor predeterminado de parámetro o como el valor pasado a un parámetro cuando se ejecute un procedimiento. Sin embargo, puede pasar una función como una variable, como se muestra en el ejemplo siguiente. -- Passing the function value as a variable. DECLARE @CheckDate datetime = GETDATE(); EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate; GO

Si el procedimiento realiza cambios en una instancia remota de SQL Server, dichos cambios no se pueden revertir. Los procedimientos remotos no intervienen en las transacciones. Para que el Motor de base de datos haga referencia al método correcto cuando está sobrecargado en .NET Framework, el método especificado en la cláusula EXTERNAL NAME debe tener las características siguientes: Ser declarado un método estático. Recibir el mismo número de parámetros que el número de parámetros del procedimiento. Usar tipos de parámetros compatibles con los tipos de datos de los parámetros correspondientes del procedimiento de SQL Server. Para obtener más información acerca de la correspondencia de tipos de datos de SQL Server y tipos de datos de .NET Framework, vea Asignar datos de parámetros CLR. Metadatos 





101

En la tabla siguiente se enumeran las vistas de catálogo y las vistas de administración dinámica que puede usar para devolver información sobre los procedimientos almacenados. Vista

Descripción

sys.sql_modules

Devuelve la definición de un procedimiento de TransactSQL. El texto de un procedimiento creado con la opción ENCRYPTION no se puede ver mediante la vista de catálogo sys.sql_modules.

sys.assembly_modules

Devuelve información sobre un procedimiento CLR.

sys.parameters

Devuelve información sobre los parámetros definidos en un procedimiento.

sys.sql_expression_depend encies sys.dm_sql_referenc ed_entitiessys.dm_sql_refe rencing_entities

Devuelve los objetos a los que hace referencia un procedimiento.

Para calcular el tamaño de un procedimiento compilado, use los siguientes contadores del Monitor de rendimiento. Nombre del objeto del Monitor de rendimiento

Nombre del contador del Monitor de rendimiento

SQLServer: Plan Cache

Frecuencia de aciertos de caché Páginas de caché Recuentos de objetos de caché*

* Estos contadores están disponibles para varias categorías de objetos de caché, incluidos Transact-SQL ad hoc, Transact-SQL preparados, procedimientos, desencadenadores, etc. Para obtener más información, veaPlan Cache (objeto de SQL Server). Seguridad Permisos Requiere el permiso CREATE PROCEDURE en la base de datos y el permiso ALTER en el esquema en el que se va a crear el procedimiento, o requiere la pertenencia al rol fijo de base de datos db_ddladmin.

102

Para los procedimientos almacenados de CLR, se necesita la propiedad del ensamblado al que se hace referencia en la cláusula EXTERNAL NAME o el permiso REFERENCES en ese ensamblado. Ejemplos

Categoría

Elementos de sintaxis ofrecidos

Sintaxis básica

CREATE PROCEDURE

Pasar parámetros

@parameter • = valor predeterminado • OUTPUT • tipo de parámetro con valores de tabla • CURSOR VARYING

Modificar datos usando un procedimiento almacenado

UPDATE

Tratamiento de errores

TRY…CATCH

Ofuscar la definición del procedimiento

WITH ENCRYPTION

Forzar la recompilación del procedimiento

WITH RECOMPILE

Establecer el contexto de seguridad

EXECUTE AS

Sintaxis básica En los ejemplos de esta sección se muestra la funcionalidad básica de la instrucción CREATE PROCEDURE con la sintaxis mínima necesaria. A.Crear un procedimiento simple Transact-SQL

En el ejemplo siguiente se crea un procedimiento almacenado que devuelve todos los empleados (nombre y apellidos), sus puestos y los nombres de sus departamentos a partir de una vista. Este procedimiento no usa ningún parámetro. En el ejemplo se muestran a continuación tres métodos de ejecutar el procedimiento. Transact-SQL USE AdventureWorks2012; GO IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetAllEmployees; GO CREATE PROCEDURE HumanResources.uspGetAllEmployees AS 103

SET NOCOUNT ON; SELECT LastName, FirstName, Department FROM HumanResources.vEmployeeDepartmentHistory; GO

El procedimiento uspGetEmployees se puede ejecutar de estas formas: Transact-SQL EXECUTE HumanResources.uspGetAllEmployees; GO -- Or EXEC HumanResources.uspGetAllEmployees; GO -- Or, if this procedure is the first statement within a batch: HumanResources.uspGetAllEmployees; B.Devolver más de un conjunto de resultados

El procedimiento siguiente devuelve dos conjuntos de resultados. Transact-SQL USE AdventureWorks2012; GO CREATE PROCEDURE dbo.uspMultipleResults AS SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person; SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer; GO C.Crear un procedimiento almacenado CLR En el ejemplo siguiente se crea el procedimiento GetPhotoFromDB que hace referencia al método GetPhotoFromDB de la clase LargeObjectBinary del ensamblado HandlingLOBUsingCLR . Antes de crear el procedimiento, el ensamblado HandlingLOBUsingCLR se registra en la base de datos local.

Transact-SQL CREATE ASSEMBLY HandlingLOBUsingCLR FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.d ll'; GO CREATE PROCEDURE dbo.GetPhotoFromDB ( @ProductPhotoID int, @CurrentDirectory nvarchar(1024), @FileName nvarchar(1024) )

104

AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB; GO

Pasar parámetros En los ejemplos de esta sección se muestra cómo usar parámetros de entrada y de salida para pasar valores a y desde un procedimiento almacenado. A.Crear un procedimiento con parámetros de entrada

En el ejemplo siguiente se crea un procedimiento almacenado que devuelve información sobre un empleado concreto pasando valores para el nombre y los apellidos del empleado. Este procedimiento solo acepta coincidencias exactas de los parámetros pasados. Transact-SQL USE AdventureWorks2012; GO IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetEmployees; GO CREATE PROCEDURE HumanResources.uspGetEmployees @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName = @FirstName AND LastName = @LastName; GO

El procedimiento uspGetEmployees se puede ejecutar de estas formas: Transact-SQL EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar'; -- Or EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar'; GO -- Or EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman'; GO -- Or, if this procedure is the first statement within a batch: HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

105

B.Usar un procedimiento con parámetros comodín

En el ejemplo siguiente se crea un procedimiento almacenado que devuelve información sobre empleados pasando valores totales o parciales para el nombre y los apellidos de los empleados. Este patrón de procedimiento coincide con los parámetros pasados o, si estos no se proporcionan, usa los valores predeterminados (apellidos que comienzan por la letra D). Transact-SQL USE AdventureWorks2012; GO IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetEmployees2; GO CREATE PROCEDURE HumanResources.uspGetEmployees2 @LastName nvarchar(50) = N'D%', @FirstName nvarchar(50) = N'%' AS SET NOCOUNT ON; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName; GO El procedimiento uspGetEmployees2 se puede ejecutar en muchas

combinaciones. Aquí se muestran solo algunas combinaciones posibles. Transact-SQL EXECUTE HumanResources.uspGetEmployees2; -- Or EXECUTE HumanResources.uspGetEmployees2 N'Wi%'; -- Or EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%'; -- Or EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n'; -- Or EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen'; -- Or EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%'; C.Usar parámetros OUTPUT En el ejemplo siguiente se crea el procedimiento uspGetList. Este procedimiento

devuelve una lista de productos cuyos precios no superan una cantidad especificada. El ejemplo se muestra con varias instruccionesSELECT y varios parámetros OUTPUT. Los parámetros OUTPUT permiten a un procedimiento

106

externo, un lote o más de una instrucción Transact-SQL tener acceso a un conjunto de valores durante la ejecución del procedimiento. Transact-SQL USE AdventureWorks2012; GO IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.uspGetList; GO CREATE PROCEDURE Production.uspGetList @Product varchar(40) , @MaxPrice money , @ComparePrice money OUTPUT , @ListPrice money OUT AS SET NOCOUNT ON; SELECT p.[Name] AS Product, p.ListPrice AS 'List Price' FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice; -- Populate the output variable @ListPprice. SET @ListPrice = (SELECT MAX(p.ListPrice) FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice); -- Populate the output variable @compareprice. SET @ComparePrice = @MaxPrice; GO Ejecute uspGetList para obtener una lista de los productos de Adventure Works (bicicletas) que cuestan menos de $700. Los parámetros OUTPUT, @Cost y @ComparePrices se utilizan con el lenguaje de

control de flujo para devolver un mensaje en la ventana Mensajes. Nota

La variable OUTPUT debe definirse al crear el procedimiento y también al utilizar la variable. Los nombres de parámetro y de variable no tienen por qué coincidir; sin embargo, el tipo de datos y la posición de los parámetros deben coincidir, a menos que se use @ListPrice = variable.

Transact-SQL DECLARE @ComparePrice money, @Cost money EXECUTE Production.uspGetList '%Bikes%', 700, @ComparePrice OUT, 107

@Cost OUTPUT IF @Cost <= @ComparePrice BEGIN PRINT 'These products can be purchased for less than $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.' END ELSE PRINT 'The prices for all products in this category exceed $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Este es el conjunto de resultados parciales: Product List Price -------------------------- ---------Road-750 Black, 58 539.99 Mountain-500 Silver, 40 564.99 Mountain-500 Silver, 42 564.99 ... Road-750 Black, 48 539.99 Road-750 Black, 52 539.99 (14 row(s) affected) These items can be purchased for less than $700.00. D.Usar un parámetro con valores de tabla

En el ejemplo siguiente se usa un tipo de parámetro con valores de tabla para insertar varias filas en una tabla. En el ejemplo se crea el tipo de parámetro, se declara una variable de tabla para hacer referencia a ella, se rellena la lista de parámetros y, a continuación, se pasan los valores a un procedimiento almacenado. El procedimiento almacenado usa los valores para insertar varias filas en una tabla. Transact-SQL USE AdventureWorks2012; GO /* Create a table type. */ CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50) , CostRate INT ); GO /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE usp_InsertProductionLocation @TVP LocationTableType READONLY AS SET NOCOUNT ON INSERT INTO [AdventureWorks2012].[Production].[Location] 108

Si la nulabilidad de la columna no se especifica explícitamente, seguirá las reglas que se muestran en la tabla siguiente. Tipo de datos de columna

Regla

Tipo de datos de alias

Motor de base de datos utiliza la nulabilidad especificada al crear el tipo d nulabilidad predeterminada del tipo de datos, utilice sp_help.


La nulabilidad se determina de acuerdo con la definición de columna.

Tipo de datos suministrado por el sistema

Si el tipo de datos suministrado por el sistema solo tiene una opción, esta datos timestamp deben ser NOT NULL. Si la configuración de sesión se establece en ON con SET: Si ANSI_NULL_DFLT_ON = ON, se asigna NULL. Si ANSI_NULL_DFLT_OFF = ON, se asigna NOT NULL. Si la base de datos se configura con ALTER DATABASE: Si ANSI_NULL_DEFAULT_ON = ON, se asigna NULL. Si ANSI_NULL_DEFAULT_OFF = ON, se asigna NOT NULL. Para ver la configuración de la base de datos para ANSI_NULL_D catálogo sys.databases.      

Cuando ninguna de las opciones ANSI_NULL_DFLT está establecida para la sesión y la base de datos tiene la configuración predeterminada (ANSI_NULL_DEFAULT es OFF), se asigna el valor predeterminado NOT NULL. En el caso de una columna calculada, el Motor de base de datos determinará automáticamente su nulabilidad. Para determinar la nulabilidad en este tipo de columna, utilice la función COLUMNPROPERTY con la propiedad AllowsNull. Nota

El controlador ODBC de SQL Server y el proveedor Microsoft OLE DB para SQL Server tienen un valor pr ANSI_NULL_DFLT_ON establecido en ON. Los usuarios de ODBC y OLE DB pueden configurar esto en l propiedades o atributos de la conexión establecidos por la aplicación.

Compresión de datos En las tablas del sistema no se puede habilitar la compresión. Cuando se crea una tabla, la compresión de datos se establece en NONE, a menos que se especifique otra cosa. Si se especifica una lista de particiones o una partición que están fuera del intervalo, se generará un error. Para obtener más información acerca de la compresión de datos, vea Compresión de datos. Para evaluar cómo afecta el cambio del estado de compresión a una tabla, un índice o una partición, utilice el procedimiento almacenado sp_estimate_data_compression_savings. Permisos 70

([Name] ,[CostRate] ,[Availability] ,[ModifiedDate]) SELECT *, 0, GETDATE() FROM @TVP; GO /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType; /* Add data to the table variable. */ INSERT INTO @LocationTVP (LocationName, CostRate) SELECT [Name], 0.00 FROM [AdventureWorks2012].[Person].[StateProvince]; /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO E.Usar un parámetro de cursor OUTPUT

En el ejemplo siguiente se usa el parámetro de cursor OUTPUT para devolver un cursor que es local en un procedimiento al lote, procedimiento o desencadenador que llama. Primero, crea el procedimiento que declara y, a continuación, abre un cursor en la tabla Currency: Transact-SQL USE AdventureWorks2012; GO IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL DROP PROCEDURE dbo.uspCurrencyCursor; GO CREATE PROCEDURE dbo.uspCurrencyCursor @CurrencyCursor CURSOR VARYING OUTPUT AS SET NOCOUNT ON; SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency; OPEN @CurrencyCursor; GO

109

A continuación, ejecuta un lote que declara una variable local de cursor, ejecuta el procedimiento para asignar el cursor a la variable local y, por último, captura las filas desde el cursor. Transact-SQL USE AdventureWorks2012; GO DECLARE @MyCursor CURSOR; EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT; WHILE (@@FETCH_STATUS = 0) BEGIN; FETCH NEXT FROM @MyCursor; END; CLOSE @MyCursor; DEALLOCATE @MyCursor; GO

Modificar datos usando un procedimiento almacenado En los ejemplos de esta sección se muestra cómo insertar o modificar datos de tablas o vistas incluyendo una instrucción del lenguaje de manipulación de datos (DML) en la definición del procedimiento. A.Usar UPDATE en un procedimiento almacenado

En el ejemplo siguiente se usa una instrucción UPDATE en un procedimiento almacenado. El procedimiento toma un parámetro de entrada @NewHours y un parámetro de salida @RowCount. El valor del parámetro@NewHours se utiliza en la instrucción UPDATE para actualizar la columna VacationHours de la tabla HumanResources.Employee. El parámetro de salida @RowCount se usa para devolver el número de filas afectadas a una variable local. Se usa una expresión CASE en la cláusula SET para determinar de forma condicional el valor establecido para VacationHours. Cuando se paga al empleado por hora (SalariedFlag = 0),VacationHours se establece en el número actual de horas más el valor especificado en @NewHours; de lo contrario, VacationHours se establece en el valor especificado en @NewHours. Transact-SQL USE AdventureWorks2012; GO CREATE PROCEDURE HumanResources.Update_VacationHours @NewHours smallint AS SET NOCOUNT ON; UPDATE HumanResources.Employee SET VacationHours = ( CASE WHEN SalariedFlag = 0 THEN VacationHours + @NewHours 110

ELSE @NewHours END ) WHERE CurrentFlag = 1; GO EXEC HumanResources.Update_VacationHours 40;

Tratamiento de errores En los ejemplos de esta sección se muestran métodos de controlar errores que pueden producirse cuando se ejecuta el procedimiento almacenado. Usar TRY…CATCH

En el ejemplo siguiente se usa la construcción TRY…CATCH para devolver

información de error capturada durante la ejecución de un procedimiento almacenado. Transact-SQL USE AdventureWorks2012; GO CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int ) AS SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION -- Delete rows from the child table, WorkOrderRouting, for the specified work order. DELETE FROM Production.WorkOrderRouting WHERE WorkOrderID = @WorkOrderID; -- Delete the rows from the parent table, WorkOrder, for the specified work order. DELETE FROM Production.WorkOrder WHERE WorkOrderID = @WorkOrderID; COMMIT END TRY BEGIN CATCH -- Determine if an error occurred. IF @@TRANCOUNT > 0 ROLLBACK -- Return the error information. DECLARE @ErrorMessage nvarchar(4000),

@ErrorSeverity int; 111

SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(); RAISERROR(@ErrorMessage, @ErrorSeverity, 1); END CATCH; GO EXEC Production.uspDeleteWorkOrder 13; /* Intentionally generate an error by reversing the order in which rows are deleted from the parent and child tables. This change does not cause an error when the procedure definition is altered, but produces an error when the procedure is executed. */ ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID int ) AS BEGIN TRY BEGIN TRANSACTION -- Delete the rows from the parent table, WorkOrder, for the specified work order. DELETE FROM Production.WorkOrder WHERE WorkOrderID = @WorkOrderID; -- Delete rows from the child table, WorkOrderRouting, for the specified work order. DELETE FROM Production.WorkOrderRouting WHERE WorkOrderID = @WorkOrderID; COMMIT TRANSACTION END TRY BEGIN CATCH -- Determine if an error occurred. IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION -- Return the error information. DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int; SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(); RAISERROR(@ErrorMessage, @ErrorSeverity, 1); END CATCH; GO -- Execute the altered procedure. 112

EXEC Production.uspDeleteWorkOrder 15; DROP PROCEDURE Production.uspDeleteWorkOrder;

Ofuscar la definición del procedimiento En los ejemplos de esta sección se muestra cómo ofuscar la definición del procedimiento almacenado. A.Usar la opción WITH ENCRYPTION

En el ejemplo siguiente se crea el procedimiento HumanResources.uspEncryptThis. Transact-SQL USE AdventureWorks2012; GO IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspEncryptThis; GO CREATE PROCEDURE HumanResources.uspEncryptThis WITH ENCRYPTION AS SET NOCOUNT ON; SELECT BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours FROM HumanResources.Employee; GO La opción WITH ENCRYPTION ofusca la definición del procedimiento al consultar el

catálogo del sistema o al usar funciones de metadatos, como se muestra en los ejemplos siguientes. Ejecute sp_helptext: Transact-SQL EXEC sp_helptext 'HumanResources.uspEncryptThis';

El conjunto de resultados es el siguiente. The text for object 'HumanResources.uspEncryptThis' is encrypted. Realice una consulta directamente a la vista de catálogo sys.sql_modules:

Transact-SQL USE AdventureWorks2012; GO SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

El conjunto de resultados es el siguiente. definition -------------------------------NULL 113

Forzar la recompilación del procedimiento En los ejemplos de esta sección se usa la cláusula WITH RECOMPILE para forzar la recompilación del procedimiento cada vez que se ejecute. A.Usar la opción WITH RECOMPILE La cláusula WITH RECOMPILE es útil cuando los parámetros suministrados al

procedimiento no son los típicos y cuando no debe almacenarse un nuevo plan de ejecución en la memoria caché o en memoria. Transact-SQL USE AdventureWorks2012; GO IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL DROP PROCEDURE dbo.uspProductByVendor; GO CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%' WITH RECOMPILE AS SET NOCOUNT ON; SELECT v.Name AS 'Vendor name', p.Name AS 'Product name' FROM Purchasing.Vendor AS v JOIN Purchasing.ProductVendor AS pv ON v.BusinessEntityID = pv.BusinessEntityID JOIN Production.Product AS p ON pv.ProductID = p.ProductID WHERE v.Name LIKE @Name; GO

Establecer el contexto de seguridad En los ejemplos de esta sección se usa la cláusula EXECUTE AS para establecer el contexto de seguridad en el que se ejecuta el procedimiento almacenado. A.Usar la cláusula EXECUTE AS

En el ejemplo siguiente se muestra el uso de la cláusula EXECUTE AS para especificar el contexto de seguridad en el que se puede ejecutar un procedimiento. En el ejemplo, la opción CALLER especifica que el procedimiento se puede ejecutar en el contexto del usuario que lo llama. Transact-SQL USE AdventureWorks2012; GO IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL DROP PROCEDURE Purchasing.uspVendorAllInfo; GO CREATE PROCEDURE Purchasing.uspVendorAllInfo WITH EXECUTE AS CALLER AS 114

SET NOCOUNT ON; SELECT v.Name AS Vendor, p.Name AS 'Product name', v.CreditRating AS 'Rating', v.ActiveFlag AS Availability FROM Purchasing.Vendor v INNER JOIN Purchasing.ProductVendor pv ON v.BusinessEntityID = pv.BusinessEntityID INNER JOIN Production.Product p ON pv.ProductID = p.ProductID ORDER BY v.Name ASC; GO B.Crear conjuntos de permisos personalizados

En el ejemplo siguiente se usa EXECUTE AS para crear permisos personalizados para una operación de base de datos. Algunas operaciones, como TRUNCATE TABLE, no tienen permisos que se puedan conceder. Si se incorpora la instrucción TRUNCATE TABLE en un procedimiento almacenado y se especifica la ejecución del procedimiento como un usuario con permisos para modificar la tabla, se pueden ampliar los permisos para truncar la tabla al usuario al que se concedan permisos EXECUTE en el procedimiento. CREATE PROCEDURE dbo.TruncateMyTable WITH EXECUTE AS SELF AS TRUNCATE TABLE MyDB..MyTable;

WHILE (Transact-SQL)

SQL Server 2012 Establece una condición para la ejecución repetida de una instrucción o bloque de instrucciones SQL. Las instrucciones se ejecutan repetidamente siempre que la condición especificada sea verdadera. Se puede controlar la ejecución de instrucciones en el bucle WHILE con las palabras clave BREAK y CONTINUE. Convenciones de sintaxis de Transact-SQL Sintaxis WHILE Boolean_expression { sql_statement | statement_block | BREAK | CONTINUE }

Argumentos

Boolean_expression 115

Es una expresión que devuelve TRUE o FALSE. Si la expresión booleana contiene una instrucción SELECT, la instrucción SELECT debe ir entre paréntesis. {sql_statement | statement_block } Se trata de cualquier instrucción o grupo de instrucciones Transact-SQL definidas con un bloque de instrucciones. Para definir un bloque de instrucciones, utilice las palabras clave de control de flujo BEGIN y END. BREAK Produce la salida del bucle WHILE más interno. Se ejecutan las instrucciones que aparecen después de la palabra clave END, que marca el final del bucle. CONTINUE Hace que se reinicie el bucle WHILE y omite las instrucciones que haya después de la palabra clave CONTINUE. Comentarios Si dos o más bucles WHILE están anidados, la instrucción BREAK interna sale al siguiente bucle más externo. Todas las instrucciones que se encuentran después del final del bucle interno deben ejecutarse primero y después se reinicia el siguiente bucle más externo. Ejemplos

A.Utilizar BREAK y CONTINUE con IF…ELSE y WHILE anidados

En el ejemplo siguiente, si el precio de venta promedio de un producto es inferior a $300, el bucle WHILE dobla los precios y, a continuación, selecciona el precio máximo. Si el precio máximo es menor o igual que $500, el bucle WHILE se reinicia y vuelve a doblar los precios. Este bucle continúa doblando los precios hasta que el precio máximo es mayor que $500, después de lo cual sale del bucle WHILE e imprime un mensaje. Transact-SQL USE AdventureWorks2012; GO WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 SELECT MAX(ListPrice) FROM Production.Product IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 116

BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear';

B.Usar WHILE en un cursor En el ejemplo siguiente se usa @@FETCH_STATUS para controlar las actividades del cursor en un bucle WHILE. DECLARE Employee_Cursor CURSOR FOR SELECT EmployeeID, Title FROM AdventureWorks2012.HumanResources.Employee WHERE JobTitle = 'Marketing Specialist'; OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Employee_Cursor; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor; GO

Operadores lógicos (Transact-SQL)

SQL Server 2012 Los operadores lógicos comprueban la veracidad de alguna condición. Éstos, como los operadores de comparación, devuelven el tipo de datos Boolean con el valor TRUE, FALSE o UNKNOWN. operador

Significado

ALL

TRUE si el conjunto completo de comparaciones es TRUE.

AND

TRUE si ambas expresiones booleanas son TRUE.

ANY

TRUE si cualquier miembro del conjunto de comparaciones es TRUE.

BETWEEN

TRUE si el operando está dentro de un intervalo.

EXISTS

TRUE si una subconsulta contiene cualquiera de las filas.

IN

TRUE si el operando es igual a uno de la lista de expresiones.

LIKE

TRUE si el operando coincide con un patrón. 117

NOT

Invierte el valor de cualquier otro operador booleano.

OR

TRUE si cualquiera de las dos expresiones booleanas es TRUE.

SOME

TRUE si alguna de las comparaciones de un conjunto es TRUE.

Operadores aritméticos (Transact-SQL)

SQL Server 2012 Los operadores aritméticos realizan operaciones matemáticas con dos expresiones de uno o más de los tipos de datos de la categoría de tipos de datos numéricos. Para obtener más información sobre las categorías de tipos de datos, vea Convenciones de sintaxis de Transact-SQL. operador

Significado

+ (sumar)

Suma

- (restar)

Resta

* (multiplicar)

Multiplicación

/ (dividir)

División

% (Módulo)

Devuelve el resto entero de una división. Por ejemplo, 12 % 5 = 2 porque el resto de 12 dividido entre 5 es 2.

También se pueden utilizar los operadores de suma (+) y resta (-) para realizar operaciones aritméticas sobre valores datetime y smalldatetime.

IF...ELSE (Transact-SQL)

SQL Server 2012 Impone condiciones en la ejecución de una instrucción Transact-SQL. La instrucción Transact-SQL que sigue a una palabra clave IF y a su condición se ejecuta si la condición se cumple: la expresión booleana devuelve TRUE.La palabra clave opcional ELSE introduce otra instrucción Transact-SQL que se ejecuta cuando la condición IF no se cumple: la expresión booleana devuelve FALSE. Convenciones de sintaxis de Transact-SQL 118

Sintaxis IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ]

Argumentos Boolean_expression

Es una expresión que devuelve TRUE o FALSE. Si la expresión booleana contiene una instrucción SELECT, la instrucción SELECT debe ir entre paréntesis. { sql_statement | statement_block } Se trata de cualquier instrucción o grupo de instrucciones Transact-SQL definidas con un bloque de instrucciones. A menos que se utilice un bloque de instrucciones, la condición IF o ELSE puede afectar al rendimiento de una sola instrucción Transact-SQL. Para definir un bloque de instrucciones, utilice las palabras clave de control de flujo BEGIN y END. Comentarios Una construcción IF...ELSE puede utilizarse en lotes, en procedimientos almacenados y en consultas ad hoc. Cuando esta construcción se utiliza en un procedimiento almacenado, se suele utilizar para probar la existencia de algún parámetro. Las pruebas IF pueden estar anidadas después de otra área IF o a continuación de un área ELSE. El límite del número de niveles anidados depende de la memoria disponible. Ejemplos En el siguiente ejemplo se utiliza IF…ELSE con la salida del procedimiento almacenado uspGetList. Este procedimiento almacenado se define Crear un procedimiento almacenado. En este ejemplo, el procedimiento almacenado devuelve una lista de bicicletas con un precio de venta inferior a 700 dólares. De este modo, se ejecuta la primera instrucción PRINT. DECLARE @compareprice money, @cost money EXECUTE Production.uspGetList '%Bikes%', 700, @compareprice OUT, @cost OUTPUT IF @cost <= @compareprice 119

image

Otros tipos de datos cursor

timestamp

hierarchyid

uniqueidentifier

sql_variant

xml

tabla

CREATE TABLE (Transact-SQL)

SQL Server 2012 Crea una nueva tabla en SQL Server 2012. Convenciones de sintaxis de Transact-SQL Sintaxis CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ AS FileTable ] ( { | | | [ ] [ ,...n ] } ) [ ON { partition_scheme_name partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] [ { TEXTIMAGE_ON { filegroup | "default" } ] [ FILESTREAM_ON { partition_scheme_name partition_scheme_name | filegroup | "default" } ] [ WITH ( [ ,...n ] ) ] [ ; ] ::= column_name [ FILESTREAM ] [ COLLATE collation_name ] [ SPARSE ] [ NULL | NOT NULL ] [ 44

[ CONSTRAINT constraint_name ] DEFAULT constant_expression ] | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] ] [ ROWGUIDCOL ] [ [ ...n ] ] ::= [ type_schema_name . ] type_name [ ( precision [ , scale ] | max | [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( < index_option > [ , ...n ] ) ] [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] | [ FOREIGN KEY ] REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } ::= column_name AS computed_column_expression computed_column_expression [ PERSISTED [ NOT NULL ] ] [ [ CONSTRAINT constraint_name ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( [ , ...n ] ) ] 45

[ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] | [ FOREIGN KEY ] REFERENCES referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE } ] [ ON UPDATE { NO ACTION } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) ] ::= column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS < table_constraint > ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,...n ] ) [ WITH FILLFACTOR = fillfactor |WITH ( [ , ...n ] ) ] [ ON { partition_scheme_name (partition_column_name) | filegroup | "default" } ] | FOREIGN KEY ( column [ ,...n ] ) REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) } ::= { [DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { | } [ , ...n ] ) ]] [ FILETABLE_DIRECTORY = ] 46

[ FILETABLE_COLLATE_FILENAME = { | database_default } ] [ FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = ] [ FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = ] [ FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = ] } ::= { 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} | DATA_COMPRESSION = { NONE | ROW | PAGE } [ ON PARTITIONS ( { | } [ , ...n ] ) ] } ::= TO

Argumentos database_name

Es el nombre de la base de datos en la que se crea la tabla. database_name debe especificar el nombre de una base de datos existente. Si no se especifica, database_name utiliza de manera predeterminada la base de datos actual. El inicio de sesión de la conexión actual debe estar asociado a un identificador de usuario existente en la base de datos especificada por database_name, y ese identificador de usuario debe tener permisos CREATE TABLE. schema_name

Es el nombre del esquema al que pertenece la nueva tabla. table_name

Es el nombre de la nueva tabla. Los nombres de tabla deben seguir las reglas que se aplican a los identificadores. table_name puede contener un máximo de 128 caracteres, excepto para los nombres de tablas temporales locales (nombres precedidos de un único signo de número (#)), que no pueden superar los 116 caracteres. 47

AS FileTable Crea la nueva tabla como un objeto FileTable. No es necesario especificar columnas porque un objeto FileTable tiene un esquema fijo. Para obtener más información sobre objetos FileTable, vea FileTables (SQL Server). column_name

Es el nombre de una columna de la tabla. Los nombres de columna deben seguir las reglas que se aplican a los identificadores y ser únicos en la tabla. column_name puede contener un máximo de 128 caracteres.column_name se puede omitir en las columnas creadas con un tipo de datos timestamp. Si no se especifica column_name, el nombre de una columna timestamp es, de manera predeterminada, timestamp. computed_column_expression

Es una expresión que define el valor de una columna calculada. Una columna calculada es una columna virtual que no está almacenada físicamente en la tabla, a menos que la columna esté marcada con PERSISTED. La columna se calcula a partir de una expresión que utiliza otras columnas de la misma tabla. Por ejemplo, una columna calculada podría tener la definición: cost AS price * qty. La expresión puede ser un nombre de columna no calculada, una constante, una función, una variable o cualquier combinación de estos elementos conectados mediante uno o más operadores. La expresión no puede ser una subconsulta ni contener tipos de datos de alias. Las columnas calculadas se pueden usar en listas de selección, cláusulas WHERE, cláusulas ORDER BY u otras ubicaciones en que se puedan emplear expresiones regulares, con las siguientes excepciones: Las columnas calculadas deben marcarse como PERSISTED para que puedan participar en restricciones FOREIGN KEY o CHECK. Es posible usar una columna calculada como columna de clave en un índice o como parte de una restricción PRIMARY KEY o UNIQUE, si el valor de la columna calculada se define mediante una expresión determinista y el tipo de datos del resultado está permitido en columnas de índice. Por ejemplo, si la tabla contiene las columnas de enteros a y b, la columna calculada a+b puede estar indizada, pero la columna calculada a+DATEPART(dd, GETDATE()) no puede indizarse porque el valor puede cambiar en las siguientes llamadas. Una columna calculada no puede ser el destino de una instrucción INSERT o UPDATE. 





Nota

48

Debido a que cada fila de una tabla puede tener distintos valores para las columnas implicadas en una calculada puede no tener el mismo valor para cada fila.

En función de las expresiones utilizadas, la nulabilidad de las columnas calculadas la determina automáticamente el Motor de base de datos. Se considera que el resultado de la mayoría de las expresiones admite valores NULL incluso si únicamente están presentes columnas que no admiten valores NULL, ya que los posibles subdesbordamientos o desbordamientos también dan como resultado valores NULL. Utilice la función COLUMNPROPERTY con la propiedad AllowsNull para determinar la nulabilidad de las columnas calculadas de la tabla. Una expresión que admita valores NULL se puede convertir en una expresión que no los admita si se especifica ISNULL con la constante check_expression, donde la constante es un valor distinto de NULL que se sustituye por cualquier resultado NULL. Requiere el permiso REFERENCES sobre el tipo para las columnas calculadas basadas en expresiones de tipo definido por el usuario de Common Language Runtime (CLR). PERSISTED Especifica que Motor de base de datos de SQL Server almacena físicamente los valores calculados en la tabla y actualiza los valores cuando se actualizan las columnas de las que depende la columna calculada. El hecho de marcar la columna calculada como PERSISTED le permite crear un índice en una columna calculada que es determinista, pero no precisa. Para obtener más información, vea Índices en columnas calculadas. Las columnas calculadas que se usan como columnas de partición de una tabla con particiones deben marcarse explícitamente como PERSISTED. computed_column_expression debe ser determinista cuando se especifique PERSISTED. ON { | filegroup | "default" } Especifica el esquema de partición o el grupo de archivos en que se almacena la tabla. Si se especifica , la tabla será una tabla con particiones cuyas particiones se almacenan en un conjunto de uno o más grupos de archivos especificados en . Si se especifica filegroup, la tabla se almacena en el grupo de archivos con nombre. El grupo de archivos debe existir en la base de datos. Si se especifica "default" o si ON no se especifica en ninguna parte, la tabla se almacena en el grupo de archivos predeterminado. El mecanismo de almacenamiento de una tabla según se especifica en CREATE TABLE no se puede modificar posteriormente. ON { | filegroup | "default"} se puede especificar también en una restricción PRIMARY KEY o UNIQUE. Estas restricciones 49

crean índices. Si se especifica filegroup, el índice se almacena en el grupo de archivos con nombre. Si se especifica "default" o si ON no se especifica en ninguna parte, el índice se almacena en el mismo grupo de archivos que la tabla. Si la restricción PRIMARY KEY o UNIQUE crea un índice clúster, las páginas de datos de la tabla se almacenan en el mismo grupo de archivos que el índice. Si se especifica CLUSTERED o la restricción crea un índice clúster, y se especifica un distinto del o filegroup de la definición de tabla, o viceversa, únicamente se respeta la definición de restricción y se omite el resto. Nota

En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo d delimitar, como en ON "default" u ON [default]. Si se especifica"default", la opción QUOTED_ID actual. Ésta es la configuración predeterminada. Para obtener más información, veaSET QUOTED_I Nota

Después de crear una tabla con particiones, considere la posibilidad de establecer la opción LOCK_E AUTO. Esto puede mejorar la simultaneidad al permitir que los bloqueos se extiendan al nivel de par obtener más información, vea ALTER TABLE (Transact-SQL).

TEXTIMAGE_ON { filegroup| "default" } Indica que las columnas text, ntext, image, xml, varchar(max), nvarchar(max), varbinar y(max) y de tipo definido por el usuario de CLR (incluyendo geometría y geografía) se almacenan en el grupo de archivos especificado. No se permite TEXTIMAGE_ON si no hay columnas de valores grandes en la tabla. No se puede especificar TEXTIMAGE_ON si se especifica . Si se especifica "default" o si TEXTIMAGE_ON no se especifica en ninguna parte, las columnas de valores grandes se almacenan en el grupo de archivos predeterminado. El almacenamiento de los datos de columna de valores grandes especificados en CREATE TABLE no se puede modificar posteriormente. Nota

En este contexto, el valor predeterminado no es una palabra clave. Es un identificador para el grupo d delimitar, como en TEXTIMAGE_ON "default" o TEXTIMAGE_ON[default]. Si se especifica "de QUOTED_IDENTIFIER debe ser ON para la sesión actual. Ésta es la configuración predeterminada. QUOTED_IDENTIFIER (Transact-SQL).

FILESTREAM_ON { partition_scheme_name | filegroup | "default" } Especifica el grupo de archivos para los datos FILESTREAM. 50

Si la tabla contiene datos FILESTREAM y se crean particiones de la tabla, debe incluirse la cláusula FILESTREAM_ON y debe especificarse un esquema de partición de grupos de archivos FILESTREAM. Este esquema de partición debe utilizar la misma función de partición y las mismas columnas de partición que el esquema de partición para la tabla; de lo contrario, se produce un error. Si la tabla no tiene particiones, no se pueden crear particiones en la columna FILESTREAM. Los datos FILESTREAM para la tabla deben almacenarse en un grupo de archivos único. Este grupo de archivos se especifica en la cláusula FILESTREAM_ON. Si la tabla no tiene particiones y no se especifica la cláusula FILESTREAM_ON, se utiliza el grupo de archivos FILESTREAM que tenga la propiedad DEFAULT establecida. Si no hay ningún grupo de archivos FILESTREAM, se produce un error. Al igual que con ON y TEXTIMAGE_ON, el valor establecido utilizando CREATE TABLE para FILESTREAM_ON no se puede cambiar, excepto en los casos siguientes: Una instrucción CREATE INDEX convierte un montón en un índice clúster. En este caso, se puede especificar un grupo de archivos FILESTREAM diferente, un esquema de partición o NULL. Una instrucción DROP INDEX convierte un índice clúster en un montón. En este caso, se puede especificar un grupo de archivos FILESTREAM diferente, un esquema de partición o "default". El grupo de archivos en la cláusula FILESTREAM_ON , o cada grupo de archivos FILESTREAM que se menciona en el esquema de partición, debe tener un archivo definido para el grupo de archivos. Este archivo se debe definir utilizando una instrucción CREATE DATABASE o ALTER DATABASE; de lo contrario, se produce un error. Para consultar temas relacionados con FILESTREAM, vea Datos de objeto binario grande (Blob) (SQL Server). [ type_schema_name. ] type_name Especifica el tipo de datos de la columna y el esquema al que pertenece. El tipo de datos puede ser uno de los siguientes: Un tipo de datos del sistema. Un tipo de alias basado en un tipo de datos del sistema de SQL Server. Los tipos de datos de alias se crean con la instrucción CREATE TYPE para poder utilizarlos en una definición de tabla. La asignación NULL o NOT NULL de un tipo de datos de alias puede invalidarse durante la instrucción CREATE TABLE. No obstante, la especificación 









51

de longitud no se puede cambiar; la longitud del tipo de datos de alias no se puede especificar en una instrucción CREATE TABLE. Un tipo definido por el usuario CLR. Los tipos definidos por el usuario CLR se crean con la instrucción CREATE TYPE para poder utilizarlos en una definición de tabla. Para crear una columna en un tipo definido por el usuario CLR, se necesita el permiso REFERENCES para el tipo. Si no se especifica el parámetro type_schema_name, el Motor de base de datos de SQL Server hace referencia a type_name en el siguiente orden: El tipo 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. 







precision

Es la precisión del tipo de datos especificado. Para obtener más información acerca de los valores de precisión válidos, vea Precisión, escala y longitud (Transact-SQL). scale

Es la escala del tipo de datos especificado. Para obtener más información acerca de los valores de escala válidos, vea Precisión, escala y longitud (Transact-SQL). max Solo se aplica a los tipos de datos varchar, nvarchar y varbinary para almacenar 2^31 bytes de datos de caracteres y binarios, y 2^30 bytes de datos Unicode. CONTENT Especifica que cada instancia del tipo de datos xml de column_name puede contener varios elementos de nivel superior. CONTENT solo se aplica al tipo de datos xml y únicamente se puede especificar si también se especifica xml_schema_collection. Si no se especifica, CONTENT es el comportamiento predeterminado. DOCUMENT Especifica que cada instancia del tipo de datos xml de column_name solo puede contener un elemento de nivel superior. DOCUMENT solo se aplica al tipo de datos xml y únicamente se puede especificar si también se especifica xml_schema_collection. xml_schema_collection

Solo se aplica al tipo de datos xml para asociar una colección de esquemas XML al tipo. Antes de escribir una columna xml en un esquema, el esquema se debe crear primero en la base de datos con CREATE XML SCHEMA COLLECTION. 52

DEFAULT Especifica el valor suministrado para la columna cuando no se ha proporcionado explícitamente un valor durante la inserción. Las definiciones DEFAULT se pueden aplicar a cualquier columna, excepto a las definidas como timestamp o a las que tengan la propiedad IDENTITY. Si se especifica un valor predeterminado para una columna de un tipo definido por el usuario, dicho tipo debe admitir la conversión implícita de constant_expression en el tipo definido por el usuario. Las definiciones DEFAULT desaparecen cuando la tabla se quita. Como valor predeterminado solo se puede utilizar un valor constante, por ejemplo una cadena de caracteres, una función escalar (función del sistema, definida por el usuario o CLR) o NULL. Para mantener la compatibilidad con versiones anteriores de SQL Server, se puede asignar un nombre de restricción a DEFAULT. constant_expression

Es una constante, NULL o una función del sistema que se utiliza como valor predeterminado de una columna. IDENTITY Indica que la nueva columna es una columna de identidad. Cuando se agrega una fila nueva a la tabla, el Motor de base de datos proporciona un valor incremental único para la columna. Las columnas de identidad se utilizan normalmente con las restricciones PRIMARY KEY como identificadores de fila únicos de 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. Las restricciones DEFAULT y los valores predeterminados enlazados no se pueden utilizar en las columnas de identidad. En este caso, deben especificarse el valor de inicialización y el incremento, o ninguno de esto valores. Si no se especifica ninguno, el valor predeterminado es (1,1). seed

Es el valor que se utiliza para la primera fila cargada en la tabla. increment

Es el valor incremental que se agrega al valor de identidad de la fila cargada anterior. NOT FOR REPLICATION En la instrucción CREATE TABLE, la cláusula NOT FOR REPLICATION se puede especificar para la propiedad IDENTITY, las restricciones FOREIGN KEY y las restricciones CHECK. Si se especifica esta cláusula para la propiedad IDENTITY, los valores no se incrementan en las columnas de identidad cuando los agentes de replicación realizan inserciones. Si se especifica esta 53

cláusula para una restricción, la restricción no se aplica cuando los agentes de replicación realizan operaciones de inserción, actualización o eliminación. ROWGUIDCOL Indica que la nueva columna es una columna de GUID de filas. Solo se puede designar una columna uniqueidentifier por tabla como columna ROWGUIDCOL. Si se aplica la propiedad ROWGUIDCOL, se puede hacer referencia a la columna mediante $ROWGUID. La propiedad ROWGUIDCOL únicamente se puede asignar a una columna uniqueidentifier. Las columnas de tipos de datos definidos por el usuario no se pueden designar con ROWGUIDCOL. La propiedad ROWGUIDCOL no aplica la unicidad de los valores almacenados en la columna. ROWGUIDCOL tampoco genera automáticamente valores para nuevas filas insertadas en la tabla. Para generar valores únicos para cada columna, utilice la función NEWID o NEWSEQUENTIALID en instrucciones INSERT o utilice estas funciones como valor predeterminado de la columna. SPARSE Indica que la columna es una columna dispersa. El almacenamiento de columnas dispersas está optimizado para los valores NULL. Las columnas dispersas no se pueden designar como NOT NULL. Para obtener restricciones adicionales y más información sobre columnas dispersas, vea Usar columnas dispersas. FILESTREAM Es válido solo para columnas varbinary(max). Especifica el almacenamiento FILESTREAM para los datos de BLOB varbinary(max). La tabla también debe tener una columna del tipo de datos uniqueidentifier que tenga el atributo ROWGUIDCOL. Esta columna no debe permitir valores nulos y debe tener una restricción de columna única UNIQUE o PRIMARY KEY. El valor de GUID de la columna lo debe proporcionar una aplicación cuando se inserten datos o una restricción DEFAULT que use la función NEWID (). No se puede quitar la columna ROWGUIDCOL ni se pueden cambiar las restricciones relacionadas si hay definida una columna FILESTREAM para la tabla. Solamente se puede quitar la columna ROWGUIDCOL después de quitarse la última columna FILESTREAM. Si se especifica el atributo de almacenamiento FILESTREAM para una columna, todos los valores para dicha columna se almacenan en un contenedor de datos FILESTREAM en el sistema de archivos. COLLATE collation_name

54

Especifica la intercalación de la columna. El nombre de intercalación puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. collation_name solo se aplica a las columnas con los tipos de datos char, varchar, text, nchar, nvarchar y ntext. Si no se especifica, se asignará a la columna la intercalación del tipo de datos definido por el usuario, si la columna es de uno de estos tipos, o la intercalación predeterminada de la base de datos. Para obtener más información acerca de los nombres de intercalación de Windows y SQL, vea Nombre de intercalación de Windows y Nombre de intercalación de SQL. Para obtener más información acerca de la cláusula COLLATE, vea COLLATE (Transact-SQL). CONSTRAINT Es una palabra clave opcional que indica el principio de la definición de una restricción PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY o CHECK. constraint_name

Es el nombre de una restricción. Los nombres de restricción deben ser únicos en el esquema al que pertenece la tabla. NULL | NOT NULL Determina si se permiten valores NULL en la columna. NULL no es estrictamente una restricción, pero se puede especificar de la misma forma que NOT NULL. NOT NULL solo puede especificarse para las columnas calculadas si también se especifica PERSISTED. PRIMARY KEY Es una restricción que exige la integridad de entidad para una o varias columnas especificadas a través de un índice único. Solo se puede crear una restricción PRIMARY KEY para cada tabla. UNIQUE Es una restricción que proporciona integridad de entidad para una o varias columnas especificadas a través de un índice único. Las tablas pueden tener múltiples restricciones UNIQUE. CLUSTERED | NONCLUSTERED Indica que se ha creado un índice clúster o no agrupado para la restricción PRIMARY KEY o UNIQUE. De forma predeterminada, el valor de las restricciones PRIMARY KEY es CLUSTERED, y el de las restricciones UNIQUE es NONCLUSTERED. En una instrucción CREATE TABLE, se puede especificar CLUSTERED tan solo para una restricción. Si se especifica CLUSTERED para una restricción UNIQUE y se especifica también una restricción PRIMARY KEY, el valor predeterminado de PRIMARY KEY es NONCLUSTERED. 55

FOREIGN KEY REFERENCES Es una restricción que proporciona integridad referencial para los datos de la columna o columnas. Las restricciones FOREIGN KEY requieren que cada valor de la columna exista en la columna o columnas de referencia correspondientes de la tabla a la que se hace referencia. Las restricciones FOREIGN KEY pueden hacer referencia solo a columnas que sean restricciones PRIMARY KEY o UNIQUE en la tabla de referencia o a columnas a las que se haga referencia en UNIQUE INDEX en la tabla de referencia. Las claves externas de las columnas calculadas también se deben marcar como PERSISTED. [ schema_name.] referenced_table_name] Es el nombre de la tabla a la que hace referencia la restricción FOREIGN KEY y el esquema al que pertenece. ( ref_column [ ,... n ] ) Es una columna o lista de columnas de la tabla a la que hace referencia la restricción FOREIGN KEY. ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Especifica la acción que tiene lugar en las filas de la tabla creada si dichas filas tienen una relación referencial y la fila a la que se hace referencia se elimina de la tabla primaria. El valor predeterminado es NO ACTION. NO ACTION El Motor de base de datos genera un error y se revierte la acción de eliminación de la fila de la tabla primaria. CASCADE Si esa fila se elimina de la tabla primaria, las filas correspondientes se eliminan de la tabla de referencia. SET NULL Todos los valores que forman la clave externa se establecen en NULL si se elimina la fila correspondiente de la tabla primaria. Para ejecutar esta restricción, las columnas de clave externa deben admitir valores NULL. SET DEFAULT Todos los valores que forman la clave externa se establecen en los valores predeterminados si se elimina la fila correspondiente de la tabla primaria. Para ejecutar esta restricción, todas las columnas de clave externa deben tener valores predeterminados. Si una columna acepta valores NULL y no se ha establecido un valor predeterminado explícito, NULL se convierte en el valor predeterminado explícito de dicha columna. No especifique CASCADE si la tabla se va a incluir en una publicación de combinación que utiliza registros lógicos. Para obtener más información

56

acerca de los registros lógicos, vea Agrupar cambios en filas relacionadas con registros lógicos. No se puede definir ON DELETE CASCADE si ya existe un desencadenador INSTEAD OF en ON DELETE en la tabla en cuestión. Por ejemplo, en la base de datos AdventureWorks2012 , la tabla ProductVendor tiene una relación referencial con la tabla Vendor. La clave externa ProductVendor.BusinessEntityID hace referencia a la clave principal Vendor.BusinessEntityID. Si se ejecuta una instrucción DELETE en una fila de la tabla Vendor y se especifica una acción ON DELETE CASCADE para ProductVendor.BusinessEntityID, Motor de base de datos comprueba si hay una o más filas dependientes de la tabla ProductVendor. Si las hay, las filas dependientes de la tabla ProductVendor se eliminan, así como la fila a la que se hace referencia en la tabla Vendor. Por el contrario, si se especifica NO ACTION, Motor de base de datos genera un error y revierte la acción de eliminación de la fila Vendor si hay al menos una fila en la tabla ProductVendor que hace referencia a ella. ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Especifica la acción que se produce en las filas de la tabla modificada cuando esas filas tienen una relación referencial y la fila a la que se hace referencia se actualiza en la tabla primaria. El valor predeterminado es NO ACTION. NO ACTION El Motor de base de datos genera un error y se revierte la acción de actualización de la fila de la tabla primaria. CASCADE Si esa fila se actualiza en la tabla primaria, las filas correspondientes se actualizan en la tabla de referencia. SET NULL Cuando se actualiza la fila correspondiente en la tabla primaria, todos los valores que componen la clave externa se establecen en NULL. Para que esta restricción se ejecute, las columnas de clave externa deben aceptar valores NULL. SET DEFAULT Cuando se actualiza la fila correspondiente en la tabla primaria, todos los valores que componen la clave externa se establecen en sus valores predeterminados. Para que esta restricción se ejecute, todas las columnas de clave externa deben tener definiciones predeterminadas. Si una columna acepta valores NULL y no se ha establecido un valor predeterminado

57

explícito, NULL se convierte en el valor predeterminado explícito de dicha columna. No especifique CASCADE si la tabla se va a incluir en una publicación de combinación que utiliza registros lógicos. Para obtener más información acerca de los registros lógicos, vea Agrupar cambios en filas relacionadas con registros lógicos. No se puede definir ON UPDATE CASCADE, SET NULL o SET DEFAULT si ya existe un desencadenador INSTEAD OF en ON UPDATE en la tabla que se va a modificar. Por ejemplo, en la base de datos AdventureWorks2012 , la tabla ProductVendor tiene una relación referencial con la tabla Vendor: la clave externa ProductVendor.BusinessEntity hace referencia a la clave principal Vendor.BusinessEntityID. Si se ejecuta una instrucción UPDATE en una fila de la tabla Vendor y se especifica una acción ON UPDATE CASCADE para ProductVendor.BusinessEntityID, Motor de base de datos comprueba si hay una o más filas dependientes de la tabla ProductVendor. Si las hay, las filas dependientes de la tabla ProductVendor se actualizan, así como la fila a la que se hace referencia en la tabla Vendor. Por el contrario, si se especifica NO ACTION, Motor de base de datos genera un error y revierte la acción de actualización de la fila Vendor si hay como mínimo una fila en la tabla ProductVendor que hace referencia a ella. CHECK Es una restricción que exige la integridad del dominio al limitar los valores posibles que se pueden escribir en una o varias columnas. Las restricciones CHECK de las columnas calculadas también se deben marcar como PERSISTED. logical_expression

Es una expresión lógica que devuelve TRUE o FALSE. Los tipos de datos de alias no pueden formar parte de la expresión. column

Es una columna o lista de columnas, entre paréntesis, que se utiliza en las restricciones de tabla para indicar las columnas que se están utilizando en la definición de la restricción. [ ASC | DESC ] Especifica cómo se ordenan la columna o las columnas que participan en las restricciones de la tabla. El valor predeterminado es ASC. partition_scheme_name

58

Es el nombre del esquema de partición que define los grupos de archivos a los que se van a asignar las particiones de una tabla con particiones. El esquema de partición debe existir en la base de datos. [ partition_column_name. ] Especifica la columna en la que se van a crear las particiones de la tabla con particiones. La columna debe coincidir con la que se especifica en la función de partición que partition_scheme_name utiliza en términos de tipo de datos, longitud y precisión. Una columna calculada que participa en una función de partición se debe marcar como PERSISTED de forma explícita. Importante

Se recomienda especificar NOT NULL en la columna de partición de las tablas con particiones y tam sean orígenes o destinos de operaciones ALTER TABLE...SWITCH. De esta forma se garantiza que de partición no tengan que comprobar si hay valores NULL.

WITH FILLFACTOR =fillfactor Especifica en qué medida el Motor de base de datos debe llenar cada página de índice que se va a utilizar para almacenar los datos de índice. Los valores de fillfactor especificados por el usuario pueden estar comprendidos entre 1 y 100. Si no se especifica un valor, el valor predeterminado es 0. Los valores del factor de relleno 0 y 100 son idénticos. Importante

La documentación de WITH FILLFACTOR = fillfactor como única opción de índice que se aplica a UNIQUE se mantiene por compatibilidad con versiones anteriores. No obstante, no se documentará d column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

Es el nombre del conjunto de columnas. Un conjunto de columnas es una representación XML sin tipo que combina todas las columnas dispersas de una tabla en una salida estructurada. Para obtener más información sobre conjuntos de columnas, vea Usar conjuntos de columnas. < table_option> ::= Especifica una o varias opciones de tabla. DATA_COMPRESSION Especifica la opción de compresión de datos para la tabla, el número de partición o el intervalo de particiones especificados. Las opciones son las siguientes: NONE No se comprimen la tabla ni las particiones especificadas. ROW

59

La tabla o las particiones especificadas se comprimen utilizando la compresión de fila. PAGE La tabla o las particiones especificadas se comprimen utilizando la compresión de página. Para obtener más información acerca de la compresión, vea Compresión de datos. ON PARTITIONS ( { | } [ ,...n ] ) Especifica las particiones a las que se aplica el valor DATA_COMPRESSION. Si la tabla no tiene particiones, el argumento ON PARTITIONS generará un error. Si no se proporciona la cláusula ON PARTITIONS, la opción DATA_COMPRESSION se aplicará a todas las particiones de una tabla con particiones. se puede especificar de las maneras siguientes: Proporcione el número de una partición, por ejemplo: ON PARTITIONS (2). Proporcione los números de partición de varias particiones separados por comas, por ejemplo: ON PARTITIONS (1, 5). Proporcione intervalos y particiones individuales, por ejemplo: ON PARTITIONS (2, 4, 6 TO 8). se puede especificar como números de partición separados por la palabra TO, por ejemplo: ON PARTITIONS (6 TO 8). Para establecer tipos de compresión de datos diferentes para particiones distintas, especifique la opción DATA_COMPRESSION más de una vez, por ejemplo: 





WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5) )

::= Especifica una o varias opciones de índice. Para obtener una descripción completa de estas opciones, vea CREATE INDEX (Transact-SQL). PAD_INDEX = { ON | OFF } Cuando es ON, el porcentaje de espacio disponible especificado por FILLFACTOR se aplica a las páginas de nivel intermedio del índice. Cuando es OFF o no se especifica ningún valor de FILLFACTOR, las páginas de nivel intermedio se rellenan casi al máximo de su capacidad dejando espacio suficiente para al menos una fila del tamaño máximo que el índice puede 60

contener teniendo en cuenta el conjunto de claves de las páginas intermedias. 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. Los valores de factor de relleno (fillfactor) 0 y 100 son idénticos. 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. La opción no tiene efecto cuando se ejecutan CREATE INDEX, ALTER INDEX o UPDATE. El valor predeterminado es OFF. ON Se producirá un mensaje de advertencia cuando se inserten valores de clave duplicados en un índice único. Solo las filas que infrinjan la restricción de unicidad darán error. OFF Se producirá un mensaje de error cuando se inserten valores de clave duplicados en un índice único. Toda la operación INSERT se revertirá. IGNORE_DUP_KEY no se puede establecer en ON para los índices creados en una vista, los índices que no sean únicos, los índices XML, los índices espaciales y los índices filtrados. Para ver IGNORE_DUP_KEY, utilice sys.indexes. En la sintaxis compatible con versiones anteriores, WITH IGNORE_DUP_KEY es equivalente a WITH IGNORE_DUP_KEY = ON. STATISTICS_NORECOMPUTE = { ON | OFF } Si es ON, las estadísticas de índices no actualizadas no se vuelven a calcular automáticamente. Si es OFF, se habilita la actualización automática de estadísticas. El valor predeterminado es OFF. ALLOW_ROW_LOCKS = { ON | OFF } Si es ON, los bloqueos de fila se permiten al tener acceso al índice. El motor de base de datos determina cuándo se utilizan los bloqueos de fila. Si es OFF, no se utilizan bloqueos de fila. El valor predeterminado es ON. ALLOW_PAGE_LOCKS = { ON | OFF } Si es ON, los bloqueos de página se permiten al tener acceso al índice. El Motor de base de datos determina el momento en que se utilizan bloqueos

61

de página. Si es OFF, no se utilizan bloqueos de página. El valor predeterminado es ON. FILETABLE_DIRECTORY = directory_name Especifica el nombre de directorio de FileTable compatible con Windows. Este nombre debe ser único entre todos los nombres de directorio de FileTable en la base de datos. La comparación de unicidad no distingue mayúsculas de minúsculas, independientemente de la configuración de intercalación. Si no se especifica este valor, se usa el nombre de la tabla de archivos. FILETABLE_COLLATE_FILENAME = { collation_name | database_default } Especifica el nombre de la collation intercalación que se va a aplicar a la columna Nombre en la tabla de archivos. La intercalación no debe distinguir mayúsculas de minúsculas para cumplir con la semántica de nomenclatura de archivos de Windows. Si no se especifica este valor, se usa la intercalación predeterminada de la base de datos. Si la intercalación predeterminada de la base de datos distingue mayúsculas de minúsculas, se genera un error en la operación CREATE TABLE. collation_name

El nombre de una intercalación sin distinción entre mayúsculas y minúsculas. database_default Especifica que se debe usar la intercalación predeterminada de la base de datos. Esta intercalación no debe distinguir mayúsculas de minúsculas. FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = constraint_name Especifica el nombre que se debe usar para la restricción de clave primaria creada automáticamente en la tabla de archivos. Si no se especifica este valor, el sistema genera un nombre para la restricción. FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = constraint_name Especifica el nombre que se debe usar para la restricción única creada automáticamente en la columna stream_id de la tabla de archivos. Si no se especifica este valor, el sistema genera un nombre para la restricción. FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = constraint_name Especifica el nombre que se debe usar para la restricción única creada automáticamente en las columnas parent_path_locator y name de la tabla de archivos. Si no se especifica este valor, el sistema genera un nombre para la restricción. Comentarios Para obtener información sobre el número de tablas, columnas, restricciones e índices permitidos, vea Especificaciones de capacidad máxima para SQL Server.

62

Normalmente, el espacio se asigna a las tablas e índices en incrementos de una extensión cada vez. Cuando se crea la tabla o el índice, se le asignan páginas de extensiones mixtas hasta que tengan páginas suficientes para llenar una extensión uniforme. Una vez que haya suficientes páginas para llenar una extensión uniforme, se asigna otra extensión cada vez que se llenan las extensiones asignadas actualmente. Para obtener un informe acerca de la cantidad de espacio asignado y utilizado por una tabla, ejecute sp_spaceused. El Motor de base de datos no exige el orden en que DEFAULT, IDENTITY, ROWGUIDCOL o las restricciones de columna se especifican en una definición de columna. Al crear una tabla, la opción QUOTED IDENTIFIER siempre se almacena como ON en los metadatos de la tabla incluso si la opción está establecida en OFF al crear la tabla. Tablas temporales Se pueden crear tablas temporales locales y globales. Las tablas temporales locales son visibles solo en la sesión actual y las tablas temporales globales son visibles para todas las sesiones. No se pueden crear particiones en las tablas temporales. Coloque un prefijo de signo de número único (#table_name) en los nombres de las tablas temporales locales y un prefijo de signo de número doble (##table_name) en los nombres de las tablas temporales globales. Las instrucciones SQL hacen referencia a la tabla temporal mediante el valor especificado para table_name en la instrucción CREATE TABLE. Por ejemplo: CREATE TABLE #MyTempTable (cola INT PRIMARY KEY); INSERT INTO #MyTempTable VALUES (1);

Si se ha creado más de una tabla temporal en un único procedimiento almacenado o lote, deben tener nombres distintos. Si se crea una tabla temporal local en un procedimiento almacenado o una aplicación que varios usuarios pueden ejecutar al mismo tiempo, el Motor de base de datos tiene que ser capaz de distinguir las tablas creadas por los distintos usuarios. El Motor de base de datos realiza esto anexando internamente un sufijo numérico a cada nombre de tabla temporal local. El nombre completo de una tabla temporal tal como se almacena en la tabla sysobjects de tempdb consta del nombre de la tabla especificado en la instrucción CREATE TABLE y el sufijo numérico generado por el sistema. Para permitir que se agregue el sufijo, el parámetro table_nameespecificado para un nombre temporal local no puede superar los 116 caracteres. Las tablas temporales se quitan automáticamente cuando están fuera de ámbito, a menos que ya se hayan quitado explícitamente mediante el uso de DROP TABLE:

63

Una tabla temporal local creada en un procedimiento almacenado se quita automáticamente cuando se completa el procedimiento almacenado. Cualquiera de los procedimientos almacenados anidados ejecutados por el procedimiento almacenado que creó la tabla puede hacer referencia a la tabla. El proceso que llamó al procedimiento almacenado que creó la tabla no puede hacer referencia a la tabla. Las demás tablas temporales se quitan automáticamente al final de la sesión actual. Las tablas temporales globales se quitan automáticamente cuando la sesión que creó la tabla finaliza y las tareas restantes han dejado de hacer referencia a ellas. La asociación entre una tarea y una tabla se mantiene solo durante la vida de una única instrucción Transact-SQL. Esto significa que la tabla temporal global se quita al finalizar la última instrucción Transact-SQL que estuviera haciendo referencia activa a la tabla cuando finalizó la sesión que la creó. Una tabla temporal local creada en un procedimiento almacenado o un desencadenador puede tener el mismo nombre que una tabla temporal creada antes de que se llame al procedimiento almacenado o al desencadenador. No obstante, si una consulta hace referencia a una tabla temporal y hay dos tablas temporales con el mismo nombre en ese momento, no está definido en cuál de las dos tablas debe resolverse la consulta. Los procedimientos almacenados anidados pueden crear también tablas temporales con el mismo nombre que la tabla temporal creada por el procedimiento almacenado que la llamó. Sin embargo, en el caso de las modificaciones que se van a resolver en la tabla creada en el procedimiento anidado, la tabla debe tener la misma estructura, con los mismos nombres de columna, que la tabla creada en el procedimiento que realiza la llamada. Esto se muestra en el ejemplo siguiente. 





CREATE PROCEDURE dbo.Test2 AS CREATE TABLE #t(x INT PRIMARY KEY); INSERT INTO #t VALUES (2); SELECT Test2Col = x FROM #t; GO CREATE PROCEDURE dbo.Test1 AS CREATE TABLE #t(x INT PRIMARY KEY); INSERT INTO #t VALUES (1); SELECT Test1Col = x FROM #t; EXEC Test2; GO

64

CREATE TABLE #t(x INT PRIMARY KEY); INSERT INTO #t VALUES (99); GO EXEC Test1; GO

El conjunto de resultados es el siguiente. (1 row(s) affected) Test1Col ----------1 (1 row(s) affected) Test2Col ----------2

Cuando se crean tablas temporales globales o locales, la sintaxis de CREATE TABLE admite la definición de restricciones, excepto restricciones FOREIGN KEY. Si se especifica una restricción FOREIGN KEY en una tabla temporal, la instrucción devuelve un mensaje de advertencia que indica que la restricción se ha omitido. La tabla se sigue creando sin las restricciones FOREIGN KEY. En las restricciones FOREIGN KEY no se puede hacer referencia a tablas temporales. Si una tabla temporal se crea con una restricción con nombre y la tabla temporal se crea dentro del ámbito de una transacción definida por el usuario, solo un usuario a la vez puede ejecutar la instrucción que crea la tabla temporal. Por ejemplo, si un procedimiento almacenado crea una tabla temporal con una restricción de clave principal con nombre, el procedimiento almacenado no puede ser ejecutado a la vez por varios usuarios. Tablas con particiones Antes de crear una tabla con particiones mediante CREATE TABLE, debe crear una función de partición para especificar cómo se van a crear las particiones en la tabla. Para crear una función de partición se usa CREATE PARTITION FUNCTION. A continuación, debe crear un esquema de partición para especificar los grupos de archivos que van a contener las particiones indicadas mediante la función de partición. Para crear un esquema de partición se usa CREATE PARTITION SCHEME. La colocación de restricciones PRIMARY KEY o UNIQUE para separar grupos de archivos no se puede especificar para las tablas con particiones. Para obtener más información, vea Tablas e índices con particiones. Restricciones PRIMARY KEY Una tabla solo puede incluir una restricción PRIMARY KEY. 

65

El índice generado por una restricción PRIMARY KEY no puede hacer que el número de índices de la tabla supere 999 índices no clúster y 1 índice clúster. Si no se especifica CLUSTERED o NONCLUSTERED para una restricción PRIMARY KEY, se utiliza CLUSTERED si no hay índices clúster especificados para las restricciones UNIQUE. Todas las columnas definidas en una restricción PRIMARY KEY se deben definir como NOT NULL. Si no se especifica nulabilidad, la nulabilidad de todas las columnas que participan en una restricción PRIMARY KEY se establece en NOT NULL. Si la clave principal se define en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario. Para obtener más información, vea Tipos definidos por el usuario de CLR. Restricciones UNIQUE Si no se especifica CLUSTERED o NONCLUSTERED para una restricción UNIQUE, de forma predeterminada se utiliza NONCLUSTERED. Cada restricción UNIQUE genera un índice. El número de restricciones UNIQUE no puede hacer que el número de índices de la tabla supere los 999 índices no clúster y 1 índice clúster. Si se define una restricción única en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario o el orden basado en el operador. Para obtener más información, vea Tipos definidos por el usuario de CLR. Restricciones FOREIGN KEY Si se especifica un valor distinto de NULL en la columna de una restricción FOREIGN KEY, el valor debe existir en la columna a que se hace referencia; de lo contrario, se devolverá un error de infracción de clave externa. Las restricciones FOREIGN KEY se aplican a la columna anterior, a menos que se especifiquen columnas de origen. Las restricciones FOREIGN KEY solo pueden hacer referencia a las tablas de la misma base de datos en el mismo servidor. La integridad referencial entre bases de datos debe implementarse a través de desencadenadores. Para obtener más información, vea CREATE TRIGGER (Transact-SQL). Las restricciones FOREIGN KEY pueden hacer referencia a otras columnas de la misma tabla. Esto recibe el nombre de autoreferencia. La cláusula REFERENCES de una restricción FOREIGN KEY de nivel de columna solo puede incluir una columna de referencia. Esta columna debe tener el mismo tipo de datos que la columna en la que se define la restricción. 























66

La cláusula REFERENCES de una restricción FOREIGN KEY de nivel de tabla debe tener el mismo número de columnas de referencia que la lista de columnas de la restricción. El tipo de datos de cada columna de referencia debe ser también el mismo que el de la columna correspondiente de la lista de columnas. No se puede especificar CASCADE, SET NULL o SET DEFAULT si una columna del tipo timestamp forma parte de la clave externa o de la clave con referencia. CASCADE, SET NULL, SET DEFAULT y NO ACTION se pueden combinar en las tablas con relaciones referenciales entre sí. Si el Motor de base de datos detecta NO ACTION, detiene y revierte las acciones CASCADE, SET NULL y SET DEFAULT relacionadas. Cuando una instrucción DELETE hace que se combinen las acciones CASCADE, SET NULL, SET DEFAULT y NO ACTION, todas las acciones CASCADE, SET NULL y SET DEFAULT se aplican antes de que el Motor de base de datos compruebe la existencia de NO ACTION. El Motor de base de datos no tiene un límite predefinido para el número de restricciones FOREIGN KEY que una tabla que hace referencia a otras tablas puede contener, o para el número de restricciones FOREIGN KEY pertenecientes a otras tablas que hacen referencia a una tabla específica. No obstante, el número real de restricciones FOREIGN KEY que se puede utilizar está limitado por la configuración del hardware y por el diseño de la base de datos y de la aplicación. Se recomienda que la tabla no contenga más de 253 restricciones FOREIGN KEY y que no más de 253 restricciones FOREIGN KEY hagan referencia a ella. El límite real en cada caso puede variar en función de la aplicación y el hardware. Debe tener en cuenta el costo que supone la exigencia de restricciones FOREIGN KEY al diseñar la base de datos y las aplicaciones. Las restricciones FOREIGN KEY no se exigen en tablas temporales. Las restricciones FOREIGN KEY solo pueden hacer referencia a columnas de restricciones PRIMARY KEY o UNIQUE de la tabla a la que se hace referencia o a columnas en UNIQUE INDEX de dicha tabla. Si la clave externa se define en una columna de tipo definido por el usuario CLR, la implementación del tipo debe admitir el orden binario. Para obtener más información, vea Tipos definidos por el usuario de CLR. Las columnas que participan en una relación de claves externas deben estar definidas con la misma longitud y escala. Definiciones DEFAULT Una tabla solo puede incluir una definición DEFAULT. Una definición DEFAULT puede contener valores constantes, funciones, funciones niládicas SQL-92 o NULL. En la siguiente tabla se muestran las 



















67

funciones niládicas y los valores que devuelven para el valor predeterminado durante la ejecución de una instrucción INSERT. Función niládica SQL-92

Valor devuelto

CURRENT_TIMESTAMP

Fecha y hora actuales.

CURRENT_USER


SESSION_USER


SYSTEM_USER


USER


En una definición DEFAULT, constant_expression no puede hacer referencia a otra columna de la tabla ni a otras tablas, vistas o procedimientos almacenados. Las definiciones DEFAULT no se pueden crear en columnas con un tipo de datos timestamp o en columnas con la propiedad IDENTITY. Las definiciones DEFAULT no se pueden crear para columnas con tipos de datos de alias si estos están enlazados a un objeto predeterminado. Restricciones CHECK Una columna puede tener cualquier número de restricciones CHECK y la condición puede incluir varias expresiones lógicas combinadas con AND y OR. Varias restricciones CHECK para una columna se validan en el orden en que se crean. La condición de búsqueda debe evaluarse como una expresión booleana y no puede hacer referencia a otra tabla. Una restricción CHECK en el nivel de columna solo puede hacer referencia a la columna restringida y una restricción CHECK en el nivel de tabla solo puede hacer referencia a columnas de la misma tabla. Las restricciones CHECK y las reglas sirven para la misma función de validación de los datos durante las instrucciones INSERT y UPDATE. Cuando hay una regla y una o más restricciones CHECK para una o varias columnas, se evalúan todas las restricciones. No se pueden definir restricciones CHECK en columnas text, ntext o image. Información adicional sobre las restricciones Un índice creado para una restricción no se puede quitar usando DROP INDEX; la restricción debe quitarse con ALTER TABLE. Un índice creado para una restricción y usado por ella se puede volver a generar mediante ALTER INDEX...REBUILD. Para obtener más información, vea Reorganizar y volver a generar índices. 

















68

Los nombres de restricción deben seguir las reglas de los identificadores, excepto que el nombre no puede empezar por un signo de número (#). Si no se proporciona el parámetro constraint_name, se asigna a la restricción un nombre generado por el sistema. El nombre de la restricción aparece en todos los mensajes de error relativos a infracciones de la restricción. Cuando se infringe una restricción en una instrucción INSERT, UPDATE o DELETE, la instrucción finaliza. Sin embargo, si SET XACT_ABORT se establece en OFF y la instrucción forma parte de una transacción explícita, continúa el procesamiento de la transacción. Si SET XACT_ABORT se establece en ON, se revierte toda la transacción. La instrucción ROLLBACK TRANSACTION también se puede utilizar con la definición de transacción al comprobar la función @@ERROR del sistema. Si ALLOW_ROW_LOCKS = ON y ALLOW_PAGE_LOCK = ON, los bloqueos de nivel de fila, página y tabla se permiten al tener acceso al índice. Motor de base de datos elige el bloqueo apropiado y puede cambiar de escala el bloqueo: de un bloqueo de fila o página a un bloqueo de tabla. Si ALLOW_ROW_LOCKS = OFF y ALLOW_PAGE_LOCK = OFF, solo se permiten los bloqueos de nivel de tabla cuando se tiene acceso al índice. Si una tabla tiene restricciones FOREIGN KEY o CHECK, y desencadenadores, las condiciones de restricción se evalúan antes de que se ejecute el desencadenador. Para obtener un informe de una tabla y sus columnas, utilice sp_help o sp_helpconstraint. Para cambiar el nombre de una tabla, utilice sp_rename. Para obtener un informe de las vistas y los procedimientos almacenados que dependen de una tabla, utilice sys.dm_sql_referenced_entities y sys.dm_sql_referencing_entities. Reglas de nulabilidad en una definición de tabla La nulabilidad de una columna determina si esa columna puede permitir un valor nulo (NULL) para sus datos. NULL no es lo mismo que cero o en blanco: NULL significa que no se ha especificado ninguna entrada o que se ha proporcionado un valor NULL explícito, y suele implicar que se desconoce el valor o que no es aplicable. Cuando cree o modifique una tabla con las instrucciones CREATE TABLE o ALTER TABLE, la configuración de la sesión y de la base de datos influirá en la nulabilidad para el tipo de datos utilizado en la definición de columna y, posiblemente, la invalidará. Se recomienda que defina siempre explícitamente una columna como NULL o NOT NULL en el caso de columnas no calculadas o, si utiliza un tipo de datos definido por el usuario, que permita que la columna utilice la nulabilidad predeterminada del tipo de datos. Las columnas dispersas siempre deben permitir valores NULL. 







69

Se necesita el permiso CREATE TABLE en la base de datos y el permiso ALTER en el esquema en que se crea la tabla. Si las columnas de la instrucción CREATE TABLE se definen como un tipo definido por el usuario CLR, se necesita la propiedad del tipo o el permiso REFERENCES. Si las columnas de la instrucción CREATE TABLE tienen asociada una colección de esquemas XML, se necesita la propiedad de la colección de esquemas XML o el permiso REFERENCES. Cualquier usuario puede crear tablas temporales en tempdb. Ejemplos A.Crear una restricción PRIMARY KEY en una columna En el ejemplo siguiente se muestra la definición de columna para una restricción PRIMARY KEY con un índice clúster en la columna EmployeeID de la tabla Employee. Como no se especifica un nombre de restricción, el sistema proporciona el nombre de la restricción. CREATE TABLE dbo.Employee (EmployeeID int PRIMARY KEY CLUSTERED);

B.Usar restricciones FOREIGN KEY Una restricción FOREIGN KEY se utiliza para hacer referencia a otra tabla. Las claves externas pueden ser claves de una sola columna o de varias columnas. En el siguiente ejemplo se muestra una restricción FOREIGN KEY de una única columna en la tabla SalesOrderHeader que hace referencia a la tabla SalesPerson. Solo se requiere la cláusula REFERENCES para una restricción FOREIGN KEY de una única columna. SalesPersonID int NULL REFERENCES SalesPerson(SalesPersonID)

También puede utilizar la cláusula FOREIGN KEY de forma explícita y volver a formular el atributo de columna. Observe que no es necesario que el nombre de la columna sea el mismo en ambas tablas. FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)

Las restricciones de claves de varias columnas se crean como restricciones de tabla. En la base de datos AdventureWorks2012 , la tabla SpecialOfferProduct incluye una restricción PRIMARY KEY de varias columnas. En el siguiente ejemplo se muestra cómo hacer referencia a esta clave desde otra tabla; el nombre explícito de restricción es opcional. CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY (ProductID, SpecialOfferID) REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)

C.Usar restricciones UNIQUE 71

Las restricciones UNIQUE se utilizan para exigir la unicidad en las columnas de clave no principal. En el siguiente ejemplo se exige la restricción de que la columna Name de la tabla Product debe ser única. Name nvarchar(100) NOT NULL UNIQUE NONCLUSTERED

D.Usar definiciones DEFAULT Los valores predeterminados suministran un valor (con las instrucciones INSERT y UPDATE) cuando no se especifica ninguno. Por ejemplo, la base de datos AdventureWorks2012 puede incluir una tabla de búsqueda con los distintos trabajos que los empleados pueden realizar en la compañía. En la columna que describe cada trabajo, el valor predeterminado de cadena de caracteres puede suministrar una descripción si no se ha escrito una descripción de forma explícita. DEFAULT 'New Position - title not formalized yet'

Además de constantes, las definiciones de DEFAULT pueden incluir funciones. Utilice el siguiente ejemplo para obtener la fecha actual de una entrada. DEFAULT (getdate())

Un recorrido de las funciones niládicas puede mejorar también la integridad de los datos. Para realizar un seguimiento del usuario que ha insertado una fila, utilice la función niládica para USER. No escriba las funciones niládicas entre paréntesis. DEFAULT USER

E.Usar restricciones CHECK En el siguiente ejemplo se muestra una restricción para los valores escritos en la columna CreditRating de la tabla Vendor. La restricción no tiene nombre. CHECK (CreditRating >= 1 and CreditRating <= 5)

En este ejemplo se muestra una restricción con nombre con una restricción de patrón en los datos de caracteres escritos en la columna de la tabla. CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')

En este ejemplo se especifica que los valores se deben incluir en una lista específica o seguir un patrón dado. CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756') OR emp_id LIKE '99[0-9][0-9]')

F.Mostrar la definición de tabla completa En el siguiente ejemplo se muestran las definiciones de tablas completas con todas las definiciones de restricciones para la tabla PurchaseOrderDetail creada en la base de datos AdventureWorks2012 . Tenga en cuenta que, para ejecutar el ejemplo, el esquema de tabla se cambia a dbo. Transact-SQL 72

CREATE TABLE dbo.PurchaseOrderDetail ( PurchaseOrderID int NOT NULL REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID), LineNumber smallint NOT NULL, ProductID int NULL REFERENCES Production.Product(ProductID), UnitPrice money NULL, OrderQty smallint NULL, ReceivedQty float NULL, RejectedQty float NULL, DueDate datetime NULL, rowguid uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT DF_PurchaseOrderDetail_rowguid DEFAULT (newid()), ModifiedDate datetime NOT NULL CONSTRAINT DF_PurchaseOrderDetail_ModifiedDate DEFAULT (getdate()), LineTotal AS ((UnitPrice*OrderQty)), StockedQty AS ((ReceivedQty-RejectedQty)), CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber PRIMARY KEY CLUSTERED (PurchaseOrderID, LineNumber) WITH (IGNORE_DUP_KEY = OFF) ) ON PRIMARY;

G.Crear una tabla con una columna xml con tipo en una colección de esquemas XML En el siguiente ejemplo se crea una tabla con una columna xml con tipo de la colección de esquemas XML HRResumeSchemaCollection. La palabra clave DOCUMENT especifica que cada instancia del tipo de datos xml decolumn_name solo puede contener un elemento de nivel superior. Transact-SQL USE AdventureWorks2012; GO CREATE TABLE HumanResources.EmployeeResumes (LName nvarchar(25), FName nvarchar(25), Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );

H.Crear una tabla con particiones En el siguiente ejemplo se crea una función de partición para crear cuatro particiones en una tabla o en un índice. A continuación, se crea un esquema de partición en el que se especifican los grupos de archivos que van a contener cada 73

una de las cuatro particiones. Finalmente, en el ejemplo se crea una tabla que utiliza el esquema de partición. En este ejemplo se supone que los grupos de archivos ya existen en la base de datos. Transact-SQL CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000) ; GO CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ; GO CREATE TABLE PartitionTable (col1 int, col2 char(10)) ON myRangePS1 (col1) ; GO

En función de los valores de la columna col1 de PartitionTable, las particiones se asignan de las maneras siguientes. Grupo de archivos

test1fg

test2fg

test3fg

Partición

1

2

3

Valores

col 1 <= 1

col1 > 1 AND col1 <= 100

col1 > 100 AND col1

I.Usar el tipo de datos uniqueidentifier en una columna En el siguiente ejemplo se crea una tabla con una columna uniqueidentifier. En el ejemplo se utiliza una restricción PRIMARY KEY para impedir que los usuarios inserten valores duplicados, y se utiliza la funciónNEWSEQUENTIALID() de la restricción DEFAULT para proporcionar valores a las nuevas filas. Se aplica la propiedad ROWGUIDCOL a la columna uniqueidentifier de modo que se pueda hacer referencia a la misma mediante la palabra clave $ROWGUID. Transact-SQL CREATE TABLE dbo.Globally_Unique_Data (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, Employee_Name varchar(60) CONSTRAINT Guid_PK PRIMARY KEY (guid) );

J.Usar una expresión para una columna calculada En el siguiente ejemplo se muestra el uso de una expresión ((low + high)/2 ) para calcular la columna calculada myavg. Transact-SQL CREATE TABLE dbo.mytable ( low int, high int, myavg AS (low + high)/2 ) ; 74

K.Crear una columna calculada en función de una columna de tipo definido por el usuario En el siguiente ejemplo se crea una tabla con una columna de tipo definido por el usuario utf8string dando por supuesto que el ensamblado del tipo y el propio tipo ya se han creado en la base de datos actual. La segunda columna se define en función de utf8string y utiliza el método ToString() de type(class)utf8string para calcular el valor de la columna. Transact-SQL CREATE TABLE UDTypeTable ( u utf8string, ustr AS u.ToString() PERSISTED ) ;

L.Usar la función USER_NAME para una columna calculada En el siguiente ejemplo se utiliza la función USER_NAME() en la columna myuser_name. Transact-SQL CREATE TABLE dbo.mylogintable ( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;

M.Crear una tabla que tenga una columna FILESTREAM En el ejemplo siguiente se crea una tabla que tiene una columna PhotoFILESTREAM. Si una tabla tiene una o varias columnas FILESTREAM, la tabla debe tener una columna ROWGUIDCOL. Transact-SQL CREATE TABLE dbo.EmployeePhoto ( EmployeeId int NOT NULL PRIMARY KEY ,Photo varbinary(max) FILESTREAM NULL ,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID() );

N.Crear una tabla que use compresión de fila En el ejemplo siguiente se crea una tabla que usa la compresión de fila. Transact-SQL CREATE TABLE dbo.T1 (c1 int, c2 nvarchar(200) ) WITH (DATA_COMPRESSION = ROW);

Para obtener más ejemplos de compresión de datos, vea Compresión de datos. O.Crear una tabla que tenga columnas dispersas y un conjunto de columnas En los ejemplos siguientes se muestra cómo crear una tabla que tenga una columna dispersa y una tabla que tenga dos columnas dispersas y un conjunto de 75

columnas. En los ejemplos se utiliza la sintaxis básica. Para obtener ejemplos más complejos, vea Usar columnas dispersas y Usar conjuntos de columnas. En este ejemplo se crea una tabla que tiene una columna dispersa. Transact-SQL CREATE TABLE dbo.T1 (c1 int PRIMARY KEY, c2 varchar(50) SPARSE NULL ) ;

En este ejemplo se crea una tabla que tiene dos columnas dispersas y un conjunto de columnas denominado CSet. Transact-SQL CREATE TABLE T1 (c1 int PRIMARY KEY, c2 varchar(50) SPARSE NULL, c3 int SPARSE NULL, CSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) ;

Crear índices (motor de base de datos)

SQL Server 2008 R2 En este tema se describen las principales tareas de creación de índices y se proporcionan las directrices de rendimiento e implementación que hay que tener en cuenta antes de crear un índice. Tareas de creación de índices Las siguientes tareas forman parte de la estrategia recomendada para crear índices: 1. Diseñar el índice. El diseño de índices es una tarea crítica. El diseño de índices incluye la determinación de las columnas que se utilizarán, la selección del tipo de índice (por ejemplo, agrupado o no agrupado), la selección de opciones de índice adecuadas y la determinación de grupos de archivos o de la ubicación de esquemas de partición. Para obtener más información, vea Diseñar índices. 2. Determinar el mejor método de creación. Los índices se crean de las siguientes maneras: Definiendo una restricción PRIMARY KEY o UNIQUE en una columna mediante CREATE TABLE o ALTER TABLE o

76

SQL Server Database Engine (Motor de base de datos de SQL Server) crea automáticamente un índice único para hacer cumplir los requisitos de unicidad de una restricción PRIMARY KEY o UNIQUE. De forma predeterminada se crea un índice clúster único para hacer cumplir una restricción PRIMARY KEY, a menos que ya exista un índice clúster en la tabla o que usted especifique un índice no clúster único. De forma predeterminada se crea un índice único no clúster para hacer cumplir una restricción UNIQUE a menos que se especifique de explícitamente un índice clúster único y no exista un índice clúster en la tabla. También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición. Un índice creado como parte de una restricción PRIMARY KEY o UNIQUE recibe automáticamente el mismo nombre que la restricción. Para obtener más información, vea Restricciones PRIMARY KEY yRestricciones UNIQUE. Creando un índice independiente de una restricción utilizando la instrucción CREATE INDEX , o el cuadro de diálogo Nuevo índice en el Explorador de objetos de SQL Server Management Studio Debe especificar el nombre del índice, de la tabla y de las columnas a las que se aplica el índice. También se pueden especificar las opciones de índice, la ubicación del índice, el grupo de archivos o el esquema de la partición. De forma predeterminada, se crea un índice que no es único y no está agrupado si no se especifican las opciones únicas o agrupadas. Para crear un índice filtrado, use la cláusula opcional WHERE. Para obtener más información, vea Directrices generales para diseñar índices filtrados. 3. Crear el índice. Un factor importante que debe tenerse en cuenta es si el índice se creará en una tabla vacía o en una tabla con datos. La creación de un índice en una tabla vacía no tiene implicaciones de rendimiento en el momento de creación del índice; sin embargo, el rendimiento se verá afectado cuando se agreguen los datos a la tabla. La creación de índices en tablas grandes debe planearse con cuidado para que el rendimiento de la base de datos no se vea afectado. La mejor manera de crear índices en tablas de gran tamaño es empezar con el índice clúster y, a continuación, generar los índices no clúster. Considere la posibilidad de establecer la opción ONLINE en ON cuando cree índices en tablas existentes. Cuando se establece en ON, los bloqueos a largo plazo no se retienen, lo que permite que continúen consultas o actualizaciones a la tabla o

77

subyacente. Para obtener más información, vea Realizar operaciones de índices en línea. Consideraciones de implementación En la siguiente tabla se enumeran los valores máximos que se aplican a los índices clúster, no clúster, espaciales, filtrados y XML. A menos que se especifique lo contrario, las limitaciones se aplican a todos los tipos de índices. Límites de índice máximos

Valor

Información adicional

Índices clúster por tabla

1

Índices no clúster por tabla

999

Incluye índices no clúster creados por restricciones PRI filtrados, pero no índices XML.

Índices XML por tabla

249

Incluye índices XML principales y secundarios en colum Índices en columnas del tipo de datos XML

Índices espaciales por tabla

249

Trabajar con índices espaciales (motor de base de datos)

Número de columnas de clave por índice

16*

El índice clúster está limitado a 15 columnas si la tabla t principal o un índice espacial. Tamaño máximo de las claves de índices.

Tamaño del registro de clave de índice

900 bytes*

No se aplica a índices XML ni a índices espaciales. Para que una tabla admita el uso de índices espaciales, el de índice es de 895 bytes. Tamaño máximo de las claves de índices.

*Puede evitar limitaciones de tamaño de registro y de columna de clave de índice de índices no clúster incluyendo columnas sin clave en el índice. Para obtener más información, vea Índice con columnas incluidas. Tipos de datos Generalmente, se puede indizar cualquier columna de una tabla o de una vista. En la siguiente tabla se muestran todos los tipos de datos que tienen una participación de índice restringida. Tipo de datos

Participación de ín


Se puede indizar si orden binario.

78

Tipos de datos de objetos grandes (LOB):image, ntext, text, varchar(max),nvarchar(max), varbinary(max) y xml

No pueden ser una c de índice. No obsta columna XML pue columna de clave en un índice XML secu principal. Pueden participar co sin clave (incluidas) clúster, excepto ima Pueden participar si expresión de colum

Columnas calculadas

No se pueden indiza columnas calculadas invocaciones de mét columna del tipo de usuario CLR, mient se marquen como de Las columnas calcul derivan de tipos de pueden indizar com clave o sin clave mi datos de columna ca permita como colu índice o columna si

Columnas de Varchar de inserción no consecutiva

La clave de índice d clúster no puede co columnas varchar c existentes en la unid ROW_OVERFLO índice clúster se cre columna varchar y existentes están en l asignación IN_RO acciones de inserció posteriores de la col constituirían inserci consecutivas produc

geometry

Se puede indizar co espaciales.

Consideraciones adicionales A continuación se ofrecen algunas consideraciones adicionales para crear un índice: 79

Puede crear un índice si tiene el permiso CONTROL o ALTER en la tabla. Cuando se crea, el índice se habilita automáticamente y está disponible para su uso. Puede quitar el acceso a un índice deshabilitándolo. Para obtener más información, vea Deshabilitar índices. Requisitos de espacio en disco 



El espacio en disco necesario para almacenar el índice depende de los siguientes factores: El tamaño de cada fila de datos de la tabla y el número de filas por página. Así se determina el número de páginas de datos que se deben leer del disco para crear el índice. Las columnas del índice y los tipos de datos utilizados. Así se determina el número de páginas de índice que se deben escribir en disco. Para obtener más información, vea Estimar el tamaño de un índice clúster yEstimar el tamaño de un índice no clúster. Espacio temporal en disco necesario durante el proceso de creación del índice. Para obtener más información, vea Determinar requisitos de espacio en disco del índice. Consideraciones de rendimiento 





El tiempo que ocupa la creación física de un índice depende en gran medida del subsistema de disco. Los factores importantes que se deben tener en cuenta son: El modelo de recuperación de la base de datos. El modelo de recuperación optimizado para cargas masivas de registros proporciona un rendimiento mucho mayor y un consumo de espacio de registro más reducido que la recuperación completa durante la operación de creación del índice. Sin embargo, la recuperación por medio de registros de operaciones masivas reduce la flexibilidad para la recuperación a un momento dado. Para obtener más información, vea Elegir un modelo de recuperación para las operaciones de índice. RAID (matriz redundante de discos económicos) utilizada para almacenar los archivos de base de datos y del registro de transacciones. Normalmente, los niveles de RAID que utilizan la creación de bandas tienen un ancho de banda de E/S mejor. Número de discos de la matriz de discos, si se utiliza RAID. Más unidades en la matriz aumentan las tasas de transferencia de datos proporcionalmente. Dónde se almacenan las ordenaciones intermedias de los datos. Si utiliza la opción SORT_IN_TEMPDB puede reducir el tiempo necesario para crear un índice cuando tempdb se encuentra en un conjunto de discos diferente que 







80

la base de datos del usuario. Para obtener más información, vea tempdb y la creación de índices. Creación del índice en línea o sin conexión. Cuando se crea un índice sin conexión (valor predeterminado), los bloqueos exclusivos se mantienen en la tabla subyacente hasta que la transacción que crea el índice se ha completado. La tabla no está accesible para los usuarios mientras se crea el índice. Excepto en el caso de los índices XML y los índices espaciales, es posible especificar que se cree el índice en línea. Cuando la opción en línea está establecida en ON, los bloqueos de la tabla a largo plazo no se conservan, lo que permite que las consultas o actualizaciones a la tabla subyacente continúen mientras se crea el índice. Aunque recomendamos operaciones de índice en línea, se debe evaluar el entorno y los requisitos específicos. Puede ser mejor ejecutar operaciones de índice sin conexión. Al hacerlo así, los usuarios tienen acceso restringido a los datos durante la operación, pero la operación acaba con mayor rapidez y utiliza menos recursos. Para obtener más información, vea Realizar operaciones de índices en línea. Para crear una restricción PRIMARY KEY o UNIQUE al crear una tabla CREATE TABLE Para crear una restricción PRIMARY KEY o UNIQUE en una tabla existente ALTER TABLE Para crear un índice CREATE INDEX CREATE SPATIAL INDEX (Transact-SQL) CREATE XML INDEX (Transact-SQL) Cómo crear un índice espacial (SQL Server Management Studio) 













Reorganizar y volver a generar índices

SQL Server 2012

Personas que lo han encontrado útil: 2 de 2 - Valorar este tema En este tema se describe cómo reorganizar o volver a generar un índice fragmentado en SQL Server 2012 mediante SQL Server Management Studio o Transact-SQL. Motor de base de datos de SQL Server mantiene los índices automáticamente cada vez que se realizan operaciones de inserción, actualización o eliminación en los datos subyacentes. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse por la base de datos (se fragmente). La fragmentación ocurre cuando los índices tienen páginas en las que 81

la ordenación lógica, basada en el valor de clave, no coincide con la ordenación física dentro del archivo de datos.Los índices muy fragmentados pueden reducir el rendimiento de la consulta y ralentizar la respuesta de la aplicación. Puede solucionar la fragmentación del índice reorganizándolo o volviéndolo a generar. Para los índices con particiones generados en un esquema de partición, puede usar cualquiera de estos métodos en un índice completo o en una sola partición de un índice. El proceso de volver a generar un índice quita y vuelve a crear el índice. Quita la fragmentación, utiliza espacio en disco al compactar las páginas según el valor de factor de relleno especificado o existente y vuelve a ordenar las filas del índice en páginas contiguas. Cuando se especifica ALL, todos los índices de la tabla se quitan y se vuelven a generar en una única transacción. La reorganización de un índice emplea muy pocos recursos del sistema. Desfragmenta el nivel hoja de los índices clúster y no clúster de las tablas y las vistas reordenando físicamente las páginas de nivel hoja para que coincidan con el orden lógico de los nodos hoja, de izquierda a derecha. La reorganización también compacta las páginas de índice. La compactación se basa en el valor de factor de relleno existente. En este tema Antes de empezar: Detectar la fragmentación Limitaciones y restricciones Seguridad Para comprobar la fragmentación de un índice, usando: SQL Server Management Studio Transact-SQL Para reorganizar o volver a generar un índice, usando: SQL Server Management Studio Transact-SQL Antes de empezar 





Detectar la fragmentación El primer paso necesario para detectar qué método de desfragmentación utilizar es analizar el índice a fin de determinar la magnitud de la fragmentación. Si utiliza la función del sistema sys.dm_db_index_physical_stats, podrá detectar la fragmentación de un índice específico, de todos los índices de una tabla o vista indizada, de todos los índices de una base de datos o de todos los índices de todas las bases de datos. Para los índices con particiones, sys.dm_db_index_physical_stats también proporciona información de la fragmentación para cada partición. 82

Material Examen DyABd

Recommend Documents