Eni - Sql Server 2008, Sql Y Transact Sql.pdf

SQL Server 2008 SQL y Transact SQL Este libro sobre SQL Server está dirigido tanto a estudiantes de informática que deseen aprender el lenguaje SQL con SQL Server como a informáticos que deseen actualizar sus competencias en SQL Server 2008 para comprender y controlar las funciones que complementan a esta nueva versión.El libro detalla el conjunto de instrucciones necesarias para definir tablas y manejar datos: las diferentes instrucciones SQL y Transact SQL se presentan e ilustran para comprender bien el cometido de las diferentes funciones expuestas. Se explican las ayudas de SQL Server 2008 en relación con el desarrollo, como el tipo FILESTREAM o las estructuras jerárquicas. También se detallan las principales funciones que permiten obtener una gestión de los datos más eficaz, tales como el XML y la integración del código CLR (Common Language Runtime). La base de datos que se va creando a lo largo del libro se puede descargar desde esta página (enlace Descargar). Jérôme GABILLAUD Ingeniero informático industrial y consultor, Jérôme Gabillaud también es responsable pedagógico en un gran centro de formación informática. Especialista de los sistemas de acceso a los datos de Microsoft y Oracle, posee la certificación MVP (Most Valuable Professional) en SQL Server. Jérôme Gabillaud también es autor de numerosas obras, reconocidas por sus cualidades técnicas y pedagógicas, que tratan sobre administración o puesta en marcha de bases de datos.

Preámbulo Este libro se dirige principalmente a los programadores de aplicaciones y a quienes desean serlo. En la primera parte se habla del modelo relacional y del álgebra relacional. Esta álgebra es el origen del SQL. Comprender esta álgebra permite por tanto trabajar eficazmente con el lenguaje SQL, sea cual sea el servidor de base de datos elegido. A continuación, se aborda la parte de la gestión de la estructura o SQL DDL y la gestión de los datos con SQL DML. Finalmente se explica Transact SQL, lenguaje de programación sobre SQL Server. Los capítulos siguientes se dedican a descubrir y enseñar diferentes funcionalidades que ofrece SQL Server a los programadores de aplicaciones para facilitarles el trabajo: integración de código .Net en SQL Server, gestión de datos de tipo XML… El conjunto de tareas relativas a la administración del servidor, como la gestión del espacio en disco, la gestión de la seguridad, los backup, la restauración, la replicación, etc. se detallan en otro libro de Ediciones ENI. SQL Server 2008 supone una evolución consecuente del gestor de base de datos de Microsoft.

En lo referente a la programación, SQL Server propone un conjunto de herramientas y de funcionalidades que permiten codificar con mayor rapidez. SQL Server permite asimismo reducir la distancia existente entre los aspectos de programación y de administración de la base de datos y del servidor. El objetivo es obviamente mejorar el rendimiento pero también permitir la gestión de todos los datos presentados en la empresa y con los cuales trabajan habitualmente los usuarios. SQL Server 2008 introduce por tanto nuevos tipos de datos para la gestión de datos como fecha y hora con el fin de mejorar la eficacia de los datos. Introduce un tipo de datos jerárquico para aumentar la sencillez de gestión y el tipo FILESTREAM para autorizar la gestión de datos no estructurados desde una base relacional.

Introducción La implementación de las bases de datos implica la adopción de un cierto número de responsabilidades específicas, que deben distinguirse de las de la administración. Las diferentes funciones del implementador consisten en:    

Diseño del esquema lógico de los datos. Paso al modelo físico de datos. Diseño e implementación de las restricciones de integridad. Programación del servidor de bases de datos.

Recordatorios sobre el almacenaje de datos El almacenaje de datos representa un problema tan antiguo como la informática. A medida que evolucionan las capacidades técnicas del material y del volumen de los datos manejados, también evoluciona la manera de almacenar y organizar los datos. En el marco de una aplicación de gestión, no todas las categorías de datos se ven afectadas de la misma manera por estos problemas de organización.

1. Las distintas categorías de datos En primer lugar, conviene definir la categoría de los datos. Esta categorización se realiza a partir de algunas sencillas preguntas:  

¿para qué sirven los datos? ¿cuánto tiempo hace falta conservar los datos?

a. Los datos básicos

Este tipo de datos es la base de todo sistema de información. Se trata de datos a partir de los cuales es posible trabajar. Así, en el marco de una gestión comercial, los datos básicos son los datos sobre los clientes y los productos. Los datos de este tipo son lo más voluminosos posible y evidentemente tienen una vida útil muy larga. Como son datos básicos, deberán ser accesibles fácil y rápidamente. b. Los datos de movimiento

Estos datos se generan a partir de datos básicos. Al contrario que estos últimos, su vida útil será limitada, pero su volumen será mucho más importante. Por ejemplo, siempre en el contexto de una gestión comercial, la información relativa a cada pedido se considera datos de movimiento. El volumen es importante porque la empresa cuenta con que cada cliente realice varios pedidos a lo largo de un mismo año contable. Por el contrario, la vida útil de estos datos es bastante inferior. Efectivamente, no es necesario conservar este tipo de información durante varios años en línea, sino que hace falta otro tipo de archivado más económico. c. Los datos de trabajo

Se trata de datos generados con un objetivo preciso, con un volumen en ocasiones importante pero con una duración muy corta. Desde el momento en que el trabajo ha sido realizado ya no es necesario conservar los datos. De esta forma, por ejemplo, los datos extraídos de la base que sirven para la realización de gráficos se encuadran dentro de esta categoría. Cuando se han realizado los gráficos, ya no es necesario conservar los datos extraídos de la base que han permitido obtenerlos. d. Los datos de archivo

Se trata de datos muy voluminosos y con una vida útil muy larga pero que presentan la característica de no ser accesibles directamente. Cuando lo son, lo son únicamente para su lectura. Por ejemplo, en el contexto de una aplicación de gestión comercial, puede tratarse de datos relativos a los años contables pasados.

2. La organización de los datos a. Directa

Esta organización es sin duda la más sencilla de utilizar. Los datos se graban unos tras otros en un archivo. Cada conjunto de datos posee una longitud fija y los registros se almacenan unos tras otros. De esta manera, el conocimiento de la longitud de un registro permite mediante un simple cálculo acceder directamente al 10º registro.

Este tipo de organización es costosa en términos de espacio en disco y no permite extraer fácilmente la información mediante otros criterios que no sean su posición en el orden de grabación. b. Secuencial

Con la organización secuencial los datos se graban unos tras otros. Se utiliza un carácter especial para marcar la separación entre los diferentes campos y otro para marcar el final de cada grabación. Los caracteres utilizados son generalmente la coma (,) y el retorno de carro (CR). Los archivos que contienen estos separadores se describen como archivos CSV (Comma Separated Values). Este tipo de organización permite optimizar el espacio de almacenamiento utilizado y resuelve así uno de los mayores problemas de los archivos con acceso directo. Por el contrario, igual que en la organización directa, cuando buscamos datos que responden a criterios de selección muy precisos es necesario recorrer todos los datos, lo que resulta ser más largo cuanto mayor es el volumen de datos (nombre de registros). c. Secuencial indexada

Los datos siguen siendo almacenados en formato secuencial pero para permitir un acceso más rápido a la información se pueden definir índices para cada archivo. Dentro de estos índices se clasifican los datos por orden alfanumérico. El recorrido del índice se realiza de manera secuencial y permite el acceso directo a la información almacenada en el archivo de datos. El recorrido del índice, aunque secuencial, es rápido porque el volumen de datos manejado es reducido. Además, puesto que los datos se seleccionan, no es necesario leer todo el índice. Finalmente es posible definir varios índices en un mismo archivo de datos. Por ejemplo, en un archivo que almacena información relativa a clientes, es posible definir un índice sobre los nombres y otros sobre las ciudades. Con este tipo de organización, la dificultad consiste en mantener actualizados los índices en las operaciones de inserción, de eliminación y de actualización. Además, al igual que las organizaciones directa y secuencial, los archivos no están vinculados unos a otros y no existe contexto de seguridad en el nivel de los datos. Por ejemplo, nada impide, en el nivel de los datos, la eliminación de un cliente aunque tenga pedidos en curso. De igual manera, toda persona que pueda trabajar con los datos puede acceder a la totalidad de los datos en lectura y en escritura. Estos inconvenientes plantean más problemas con la organización secuencial indexada porque se pueden gestionar grandes volúmenes de datos con muchos usuarios conectados.

Esta solución secuencial indexada ha sido adoptada de manera generalizada para aplicaciones pequeñas y medianas porque para facilitar la programación, muchos lenguajes de programación proponen un motor de gestión de este tipo de organización. d. Base de datos jerárquica

Con estas bases de datos, se resuelven los problemas de seguridad de acceso a los datos y la conexión entre los datos. En cambio, cada editor ha desarrollado su motor de manera independiente. Por lo tanto, hay que volver a aprender el funcionamiento de cada motor cada vez que se programe con uno nuevo (el lenguaje de consulta, el API de acceso a los datos). A esto hay que añadir una compleja organización de los datos. Estas soluciones de propiedad son muy costosas para la empresa que las escoja. e. Base de datos relacional

Basadas en una representación lógica de los datos que respeta el modelo relacional, las bases de datos relacionales han sabido imponerse porque todas se basan en el mismo lenguaje normalizado que es SQL.

3. La normalización del esquema relacional Cuando se define el esquema relacional para responder a todas las necesidades de los usuarios, es necesario normalizarlo para evitar redundancias de información y estructuras no conformes con el modelo relacional. Cuando se ha realizado esta operación, se puede entonces desnormalizar el esquema aunque rara vez es la mejor opción. Si el programador desnormaliza el esquema, deberá igualmente poner en marcha el conjunto de mecanismos que permiten mantener la coherencia de los datos. Efectivamente, el modelo relacional, y por tanto los SGBDR (Sistema de Gestión de Base de datos Relacional), sólo puede garantizar la coherencia de los datos en los modelos normalizados. Las formas normales permiten garantizar que el esquema es conforme con el modelo relacional. En teoría existen cinco formas normales, pero en la práctica sólo se aplican las tres primeras. La aplicación de formas normales requiere una comprensión perfecta del concepto de dependencia funcional. Un dato depende funcionalmente de otro si el conocimiento del segundo permite determinar el valor del primero. Por ejemplo, es posible decir que en una aplicación de gestión comercial, existe una dependencia funcional entre un código de IVA y el tasa de IVA o entre la referencia de un artículo y su designación. Primera forma normal: se dice que una tabla está en primera forma normal si todas las columnas contienen valores simples. Por ejemplo, si una tabla de clientes contiene un campo Teléfonos en el que se guardan los diferentes números de teléfono de un cliente, esta tabla no está en primera forma normal porque habrá que definir las columnas Trabajo y Móvil para estructurar mejor los datos.

Clientes

Número 1

Apellido Fuentes

Nombre

Teléfonos 9234548576

Pablo

Pedido

Fecha

1350

01/01/2008

Pablo

1352

15/01/2008

Rosa

683465789 9245939847 1351

02/01/2008

683465789 9234548576 1

Fuentes

2

López

La tabla anterior no respeta la primera forma normal. Clientes Número 1 1 2

Apellido Nombre Fuentes Pablo Fuentes Pablo López Rosa

Trabajo Móvil Pedido 9234548576 683465789 1350 9234548576 683465789 1352 9245939847 1351

Fecha 01/01/2008 15/01/2008 02/01/2008

Esta tabla sí respeta la primera forma normal. Segunda forma normal: se dice que una tabla está en segunda forma normal si está en primera forma normal y todas las columnas no claves dependen funcionalmente de la clave primaria. Siguiendo con el ejemplo anterior, es posible deducir en primer lugar que la clave de la tabla de los clientes está compuesta por las columnas Número y Pedido. En este caso, los valores de las columnas Apellido, Nombre, Trabajo y Móvil dependen únicamente del número, mientras que la columna Fecha está relacionada con el número del pedido. Por lo tanto, la tabla no está en segunda forma normal. Hay que definir dos tablas: clientes y pedidos. Cliente

Número 1 2

Pedido

Apellido Fuentes López

Número 1350 1352 1351

Nombre Pablo Rosa

Fecha 01/01/2008 15/01/2008 02/01/2008

Trabajo Móvil 9234548576 683465789 9245939847 Cliente

1 1 2

Las dos tablas anteriores respetan la segunda forma normal. Tercera forma normal: se dice que una tabla está en tercera forma normal si está en segunda forma normal y no existe dependencia funcional entre dos columnas no claves.

Por ejemplo, si se añaden las columnas Tratamiento y Sexo a la tabla de los clientes: Cliente Número Apellido Nombre Trabajo Móvil Tratamiento Sexo 1 Fuentes Pablo 9234548576 683465789 Sr. M 2 López Rosa 9245939847 Srta. F En este caso se puede decir que existe una dependencia funcional entre el sexo y el tratamiento. Efectivamente, el hecho de conocer el tratamiento (Sr. Sra. o Srta.) permite deducir el sexo. La tabla de los clientes no respeta por tanto la tercera forma normal. Se define la tabla de los tratamientos para obtener el esquema siguiente: Cliente Número 1 2

Apellido Fuentes López

Tratamiento

Nombre Pablo Rosa

Trabajo Móvil Tratamiento 9234548576 683465789 Sr. 9245939847 Srta.

Valor Srta. Sra. Sr.

Sexo F F M

Las dos tablas anteriores sí respetan la tercera forma normal.

El modelo relacional La organización de los datos en el Sistema de Gestión de Bases de Datos Relacionales (SGBDR o RBDMS en inglés) se basa completamente en el modelo relacional. Este modelo fue desarrollado por Edgar Franck Codd con el apoyo de IBM en los años 70. Además de este modelo, se creó también un álgebra (el álgebra relacional) para extraer los datos almacenados en este modelo. La comprensión de este modelo y de su álgebra permite abordar el aprendizaje de SQL de forma cómoda, porque se trata simplemente de trasladar los conceptos teóricos a líneas de comandos. Este trabajo permitirá además adaptarse con mucha mayor facilidad a los diferentes perfeccionamientos que puede sufrir SQL en las distintas versiones. El álgebra relacional ha conducido al desarrollo del SQL que se ha convertido en estándar en lo relativo a la administración de datos. El hecho de que los SGBDR respeten el modelo relacional lleva a trabajar con una estructura lógica de organización de los datos (tablas, vistas, índices…) que es independiente de la estructura física (ficheros…). Es tarea de cada SGBDR proporcionar una visión lógica al usuario mientras garantiza el almacenamiento físico de los datos. Esta limitación es también la ventaja de los SGBDR, porque la administración de los datos desde un punto de vista lógico es una gran facilidad de utilización. De esta forma, los

usuarios poco o nada acostumbrados a programar aplicaciones pueden iniciarse sin dificultades en el SQL.

1. Conceptos y definiciones El modelo relacional se basa en tres conceptos básicos simples (dominio, relación, atributo), a los que se aplicarán reglas precisas. La implementación de la base de datos se ve facilitada por un lenguaje declarativo (no procedural) simple, basado en una lógica de conjuntos. Dominio

Es un conjunto de valores caracterizado por un nombre. Cardinal Es el número de elementos de un dominio. Ejemplo El diccionario de los datos del análisis de una gestión comercial puede comportar, entre otras cosas, especificaciones sobre la gestión de los estados de los pedidos o la visualización de números de orden. El modelo relacional los traducirá de la manera siguiente: Estados de los pedidos = {"EC","LI", "FA","SO"};cardinal 4 Números de orden = {n | 1<=n<=9999};cardinal 9999.

Producto cartesiano

El producto cartesiano P entre varios dominios D1, D2,..., Dn, representado P = D1 X D2 X ... X Dn, es el conjunto de de los n-uplos (tuplas) (d1, d2, ..., dn) donde cada di es un elemento del dominio Di. Ejemplo Si se quiere gestionar dos dominios (codigos y tasas), se podrá obtener pares compuestos por un código y una tasa. Códigos = {1,2,3,4} Tasa de IVA = {0,7,16} Códigos X Tasa de IVA ={(1,0),(1,7),(1,16), (2,0),(2,7),(2,16),(3,0),(3,7),(3,16), (4,0),(4,7),(4,16)}

Relación

Una relación definida en los dominios D1, D2,... , Dn es un subconjunto del producto cartesiano de esos dominios caracterizado por un nombre. Atributo Es una columna de una relación caracterizada por un nombre. Grado Es el número de atributos de una relación. Ejemplo Para asociar una sola tasa por código, sólo se requieren tres pares. Relación IVA = {(1,0),(2,7),(3,16)}

Representación

Se realiza en forma de tabla (table), como una matriz:

o en detalle: IVA (CODIGO:códigos, VALOR:Tasa de IVA) o IVA (CODIGO, VALOR)

2. Reglas principales El modelo relacional gestiona un objeto principal, la relación, asociada a los conceptos de dominio y de atributo. Se aplican reglas a esta relación para respetar las restricciones vinculadas al análisis. Algunas de estas reglas son: Coherencia

Todo valor tomado por un atributo debe pertenecer al dominio en el cual está definido. Unicidad

Todos los elementos de una relación deben ser distintos. Identificador

Atributo o conjunto de atributos que permiten caracterizar de manera única cada elemento de la relación. Clave principal

Identificador mínimo de una relación. Claves secundarias

Otros identificadores de la relación. Integridad referencial

Esta regla impone que un atributo o un grupo de atributos de una tabla aparezca como clave principal en otra relación. Clave externa

Atributo o conjunto de atributos que verifican la regla de integridad referencial. Ejemplo El análisis de una gestión comercial implica gestionar clientes que tengan unas características (Nombre, Dirección) y los pedidos que efectúan dichos clientes. Podremos proponer el modelo siguiente:

CLIENTES (NUMCLI,NOMCLI,DIRCLI) NUMCLI identificador clave principal de CLIENTES NOMCLI,DIRCLI identificador clave secundaria de CLIENTES PEDIDOS (NUMPDO,FECHAPDO,NUMCLI,ESTADOPDO) NUMPDO identificador clave principal de PEDIDOS NUMCLI clave externa de PEDIDOS, que referencia NUMCLI de CLIENTES

Valor nulo

En el modelo relacional, se admite la noción de nulidad. Es un valor que representa una información desconocida o no aplicable a una columna. Se representa _ , ^ o NULL. Restricción de entidad

Todo valor que corresponde a una clave principal debe ser no NULL. Ejemplo

En la relación artículo, se puede admitir que el precio o la tasa de IVA sean desconocidos, pero debe consignarse la referencia del artículo (clave principal).

El álgebra relacional Es un método de extracción que permite la manipulación de las tablas y de las columnas. Su principio se basa en la creación de nuevas tablas (tablas resultantes) a partir de las tablas existentes; estas nuevas tablas se convierten en objetos inmediatamente utilizables. Los operadores del álgebra relacional que permiten crear las tablas resultantes se basan en la teoría de conjuntos. La sintaxis y la notación aquí empleadas son las de uso más extendido.

1. Operadores Unión

La unión entre dos relaciones de misma estructura (grado y dominios) proporciona una tabla resultante de misma estructura que tiene como elementos el conjunto de los elementos distintos de dos relaciones iniciales. Notación: Rx = R1 ∪ R2 Ejemplos Dadas las tablas CLIESTE y CLICENTRO:

Clientes de las dos regiones: CLIENTES=CLIESTE ∪ CLICENTRO

Intersección

La intersección entre dos relaciones de misma estructura (grado y dominios) da una tabla resultante de misma estructura que tiene como elementos el conjunto de los elementos comunes a ambas relaciones iniciales. Notación: Rx = R1 ∩ R2

Ejemplo Clientes comunes a ambas regiones: CLICOMUN=CLIESTE ∩ CLICENTRO

Diferencia

La diferencia entre dos relaciones de misma estructura (grado y dominios) da una tabla resultante de misma estructura que tiene como elementos el conjunto de los elementos de la primera relación que no están en la segunda. Notación: Rx = R1 - R2 Ejemplo Clientes gestionados solamente por la región ESTE: CLIESTESOLO=CLIESTE - CLICENTRO

División

La división entre dos relaciones es posible a condición de que la relación divisora esté totalmente incluida en la relación dividendo. El cociente de la división corresponde a la información que esté presente en el dividendo y no en el divisor. Otra forma de definir la división es la siguiente: Sean R1 y R2 dos relaciones tales que R2 se encuentra totalmente incluida en R1. El cociente R1†R2 está constituido por tuplas t tales que para todas las tuplas t‟ definidas en R2 existe una tupla t.t‟ definida en R1. Notación: Rx=R1÷R2 Ejemplo: Sean las relaciones persona que contienen datos relativos a individuos:

Sea la relación clienteoeste:

La división entre ambas relaciones permite aislar la información complementaria a los clientes y presente en la relación individuo:

Restricción

La restricción según una condición produce, a partir de una relación, una relación de mismo esquema que sólo contiene los elementos de la relación inicial que cumplen la condición. Notación: Rx = σ (condición) R1 La condición se expresa como: [NO] [(] atributo operador valor [)] [{Y/O}condición]

operador un operador de comparación: =, <>, >, <, >=, <= valor una constante u otro atributo Ejemplos Clientes de BARCELONA: CLI08=σ(DIRECCION="BARCELONA")CLIESTE

Artículos de la familia AB: ART1=σ(REFART>="AB" Y REFART<"AC")ARTICULOS

Alfombras cuyo precio sea inferior a 20000: ART2=σ(PRECIO<20000)ART1

Proyección

La proyección de una relación sobre un grupo de atributos da una relación resultante que tiene como esquema solamente esos atributos, y como elementos los n-uplos distintos compuestos por los valores asociados a esos atributos. Notación: Rx = π R (A1, A2... An). Ejemplo

Pedidos y estados del pedido: PDO= π PEDIDOS(NUMPDO,NUMCLI,ESTADOPDO)

Clientes con pedidos: CLIPDO1= π PEDIDOS(NUMCLI)

Clientes y estados de pedido: CLIPDO2= π PEDIDOS(NUMCLI,ESTADOPDO)

Producto cartesiano

El producto cartesiano entre dos relaciones produce una relación que tiene como esquema todos los atributos de ambas relaciones existentes y como elementos, la asociación de cada fila de la primera tabla con cada fila de la segunda. Notación: Rx = S1 X S2 Ejemplo Dadas las tablas:

INVENTARIO = ALMACEN X ART2

Combinaciones

La combinación (join) de dos relaciones según una condición se produce por la restricción sobre el producto cartesiano. Notación: Rx = S1 JOIN (condición) S2. Ejemplo Dadas las tablas:

LINPDOEC = PDOEC JOIN (PDOEC.NUMEROPDO = LINEASPDO.NUMPDO) LINEASPDO

Los diferentes tipos de combinaciones son: Theta-join (o combinación por comparación) La condición es una comparación entre dos atributos. Equi-join (o combinación equivalente) La condición se basa en la igualdad entre dos atributos. Join natural Equi-join entre los atributos que tienen el mismo nombre. Cálculos elementales

Proyección sobre una relación asociada a un cálculo sobre cada fila para crear uno o varios atributos nuevos. Notación: Rx = π S (A1...N1 = expresión calculada...) La expresión calculada puede ser:   

una operación aritmética, una función matemática, una función sobre una cadena.

Ejemplo Queremos obtener el importe de una línea de pedido (Precio * Cantidad).

LINPDOVALO = π LINPDO(NUMPDO,NUMLIN,REFART, VALOLIN=CTDADPDO*PRECIOUNIT)

Cálculo de agregados

Proyección sobre una relación asociada a uno o varios cálculos estadísticos basados en un atributo para todos los elementos de la relación o del agrupamiento vinculado a la proyección, con el fin de crear uno o varios atributos nuevos. Notación: Rx = π S (A1...N1= función estadística (Ax)...) Las funciones estadísticas son:

COUNT (*) número de filas. COUNT (atributo) número de valores no nulos. SUM (atributo) suma de los valores no nulos. AVG (atributo) promedio de los valores no nulos. MAX (atributo) valor máximo (no nulo). MIN (atributo) valor mínimo (no nulo). Ejemplo Número total de clientes en la tabla. TOTCLI=π CLIENTES(N=COUNT(*))

Total de los importes por línea de pedido: PDOVALO=π LINPDOVALO(NUMPDO,TOTPDO=SUM(VALOLIN))

Los precios más elevados, los menos elevados y promedio de los precios por categoría de artículos: STATART=π ARTICULOS(CATEGORIA,MASCARO= MAX(PRECIO),MENOSCARO=MIN(PRECIO), PROMEDIO=AVG(PRECIO))

2. Etapas de la resolución de un problema A partir de una base de datos conocida (esquemas, dominios, relaciones, elementos), es necesario: Analizar los requerimientos   

Transcribir en forma de relación resultante los requerimientos expresados en las especificaciones. Determinar los atributos y las relaciones que deben emplearse. Expresar los cálculos elementales y de agregados para crear los atributos inexistentes.

Establecer la "vista"

La vista es una relación intermedia que contiene todos los atributos que permiten realizar la extracción, con sus relaciones de origen, sus clases de utilidad y las operaciones que se deben aplicar. Clases de atributo

Clase a: atributo que participa en la relación resultante. Clase b: atributo que participa en un cálculo. Clase c: atributo que participa en una restricción. Clase d: atributo que participa en una combinación. Disponer y expresar las operaciones Caso general 1. Relaciones pertinentes. 2. Restricciones (para eliminar las filas inútiles). 3. Combinaciones, Productos cartesianos, Uniones, Intersecciones, Diferencias (para asociar las filas restantes). 4.

Cálculos elementales (para crear las nuevas columnas). 5. Cálculos de agregados (para las columnas estadísticas). 6. Join entre la tabla obtenida en Cálculos de agregados y la tabla inicial en Cálculos elementales (para añadir las columnas estadísticas a las demás). 7. Repetir las etapas del Cálculos de agregados para los otros agrupamientos. 8. Restricciones en relación a los atributos calculados. 9. Proyecciones para eliminar los duplicados. 10. Proyección final para eliminar los atributos inútiles en la tabla resultante. Ejemplo tratado en el libro

Base de datos GESCOM El análisis de la gestión comercial (CLIENTES, STOCKS, PEDIDOS) de una empresa de montaje y de venta ha proporcionado los esquemas de tablas siguientes: CLIENTES (NUMERO_CLI, APELLIDO, NOMBRE, DIRECCION, CODIGO_POSTAL, CIUDAD, TELEFONO) La tabla CLIENTES contiene una fila por cliente, con todas las características para poder contactar con el cliente o mandarle correo. Restricciones: NUMERO_CLI clave principal. APELLIDO obligatorio (no NULL). CODIGO_POSTAL en formato español.

CATEGORIAS (CODIGO_CAT, ETIQUETA_CAT) Una categoría de artículos es un agrupamiento estadístico interno codificado (01: Micros completos, 02: Programas, etc...). Restricciones: CODIGO_CAT clave principal. ARTICULOS (REFERENCIA_ART, DESIGNACION_ART, PRECIOUNIT_ART, CODIGO_CAT) Esta tabla debe contener una fila por artículo, sus características, especialmente su precio, y la categoría a la que pertenece. Restricciones: REFERENCIA_ART clave principal. CODIGO_CAT clave externa que hace referencia a CATEGORIAS. DESIGNACION_ART obligatoria (no NULL). PRECIOUNIT_ART positivo. STOCKS (REFERENCIA_ART, ALMACEN, CTDAD_STK, STOCK_MINI, STOCK_MAXI) La empresa dispone de varios almacenes en España, que pueden distribuir el material. Debe poder administrarse la cantidad de artículos por almacén, al igual que los valores límite de las cantidades almacenadas. Restricciones: El identificador clave principal es la asociación de las columnas REFERENCIA_ART y ALMACEN. REFERENCIA_ART clave externa que hace referencia a ARTICULOS. STOCK_MINI debe ser inferior o igual a STOCK_MAXI. CTDAD_STK debe estar comprendido entre -100000 y +100000. PEDIDOS (NUMERO_PDO, FECHA_PDO, TASA_DESCUENTO, NUMERO_CLI, ESTADO_PDO)

Cuando un cliente realiza un pedido, éste se identifica con un número único. Se puede aplicar un descuento global sobre el pedido y debe poder saberse si el pedido está pendiente de entrega, pendiente de facturación o liquidado, mediante un indicador (ESTADO_PDO). Restricciones: NUMERO_PDO clave principal, los números deben asignarse por orden de creación. ESTADO_PDO sólo puede tomar los valores: EC (en curso), PE (parcialmente entregado), EN (entregado), SA (saldado). NUMERO_CLI clave externa que hace referencia a CLIENTES. TASA_DESCUENTO no puede sobrepasar el 50%. LINEA_PDO (NUMERO_PDO, NUMERO_LIN, REFERENCIA_ART, CTDAD_PDO) Cada pedido tiene al menos una fila artículo con la cantidad solicitada. Restricciones: El identificador clave principal es la asociación de las columnas NUMERO_PDO y NUMERO_LIN. NUMERO_PDO clave externa que hace referencia a PEDIDOS. REFERENCIA_ART clave externa que hace referencia a ARTICULOS. Todas las zonas son obligatorias (no NULL). HISTO_FAC (NUMERO_FAC, FECHA_FAC, NUMERO_PDO, BASEIMPONIBLE, ESTADO_FAC) La interfaz con la contabilidad debe proporcionar información referida a la factura o las facturas asociadas a cada pedido, en particular el importe de la base imponible y el estado de la factura (parcial o totalmente pagada, no pagada). Un pedido cuyas facturas estén todas pagadas se considera como liquidado. Restricciones: NUMERO_FAC clave principal. NUMERO_PDO clave externa que hace referencia a PEDIDOS. ESTADO_FAC toma los valores: NP (no pagada), PP (parcialmente pagada), TP (totalmente pagada).

Ejemplo Queremos obtener la confirmación del pedido n° 1301. Modelo del documento:

Análisis del modelo: Fecha actual: información que puede colocarse en el momento de completar el documento. N° de pedido: NUMERO_PDO de PEDIDOS Fecha del pedido: FECHA_PDO de PEDIDOS Nombre del cliente: APELLIDO de CLIENTES Dirección del cliente: DIRECCION de CLIENTES Referencia: REFERENCIA_ART de LINEAS_PDO Descripción: DESIGNACION_ART de ARTICULOS Cantidad pedida: CTDAD_PDO de LINEAS_PDO Precio unitario: PRECIOUNIT_ART de ARTICULOS Importe: zona para calcular Base imponible: zona para calcular

Para establecer el documento se necesitará la tabla siguiente: CONFPDO(NUMERO_PDO, FECHA_PDO, APELLIDO, DIRECCION, REFERENCIA_ART, DESIGNACION_ART, PRECIOUNIT_ART, CTDAD_PDO, IMPORTE, BASEIMP) Donde IMPORTE= PRECIOUNIT_ART*CTDAD_PDO por línea de pedido y BASEIMP=SUM(IMPORTE) para el pedido.

Vista

Atributo

Tabla

Clase Operación Restricción sobre PEDIDOS NUMERO_PDO=1301 NUMERO_PDO a,c,d LINEAS_PDO Join natural entre PEDIDOS y LINEAS_PDO FECHA_PDO PEDIDOS a Join natural entre PEDIDOS NUMERO_CLI PEDIDOSCLIENTES d y CLIENTES APELLIDO CLIENTES a DIRECCION CLIENTES a ARTICULOS Join natural entre REFERENCIA_ART a,d LINEAS_PDO y ARTICULOS LINEAS_PDO

CTDAD_PDO LINEAS_PDO DESIGNACION_ART ARTICULOS PRECIOUNIT_ART ARTICULOS IMPORTE BASEIMP

a,b a a,b a,b a

Cálculo de IMPORTE Cálculo de IMPORTE Cálculo de BASEIMP

Operaciones

Restricción sobre el número de pedido: T1= (NUMERO_PDO=1301)PEDIDOS

T1

NUMERO_PD FECHA_PD TASA_DESCUENT NUMERO_CL ESTADO_PD O O O I O 1301 08/02/2001 0 15 EC

Join natural entre PEDIDOS y CLIENTES: T2=T1 JOIN(T1.NUMERO_CLI=CLIENTE.NUMERO_CLI) CLIENTES

T2 NUMERO_PDO FECHA_PDO NUMERO_CLI APELLIDO DIRECCION ... 1301 08/02/2001 15 FERNANDEZ MOSTOLES ... Join natural entre PEDIDOS y LINEAS_PDO: T3=T2 JOIN(T2.NUMERO_PDO=LINEAS_PDO.NUMERO_PDO) LINEAS_PDO

T NUMERO_P FECHA_P DIRECCI APELLIDO 3 DO DO ON FERNAND MOSTOL 1301 08/02/2001 EZ ES FERNAND MOSTOL 1301 08/02/2001 EZ ES

REFERENCIA_A CTDAD_P .. RT DO . .. AB03 3 . .. AB22 1 .

Join natural entre LINEAS_PDO y ARTICULOS: T4=T3 JOIN (T3.REFERENCIA_ART=ARTICULOS.REFERENCIA_ART) ARTICULOS

PRECI NUME REFEREN DESIGNAC O T RO FECHA_ APELLID DIRECC CIA_ CTDAD_ ION_ 4 PDO O ION PDO UNIT_ _PDO ART ART ART 08/02/200 FERNAN MOSTO 1301 AB03 3 BICICLETA 70000 1 DEZ LES 08/02/200 FERNAN MOSTO 1301 AB22 1 CASCO 4000 1 DEZ LES

.. . .. . .. .

Proyección de cálculo elemental de IMPORTE y eliminación de columnas inútiles: T5=π T4(NUMERO_PDO, FECHA_PDO, APELLIDO, DIRECCION, REFERENCIA_ART,CTDAD_PDO,DESIGNACION_ART, PRECIOUNIT_ART, IMPORTE=PRECIOUNIT_ART*CTDAD_PDO) PRECI NUME REFERE CTDA DESIGNAC O D_ ION_ T RO FECHA_ APELLID DIRECC NCIA 5 PDO O ION UNIT_ _PDO _ART PDO ART ART 08/02/20 FERNAN MOSTO BICICLET 1301 AB03 3 70000 01 DEZ LES A 08/02/20 FERNAN MOSTO 1301 AB22 1 CASCO 4000 01 DEZ LES

IMPO RTE

210000 4000

Proyección de cálculo de agregados para BASEIMP: T6=π T5(BASEIMP=SUM(IMPORTE))

T6

BASEIMP 214000

Producto cartesiano para tener todas las columnas en la tabla resultante: T7=T5xT6

CTD NUM FEC REFEREN AD T ERO HA_ APELLI DIREC CIA_ 7 DO CION _PD _PDO PDO ART O 08/02 FERNA MOSTO 1301 AB03 3 /01 NDEZ LES 08/02 FERNA MOSTO 1301 AB22 1 /01 NDEZ LES

PRECI DESIGNA OCION IMPO RTE UNIT_ _ART ART BICICLET 21000 70000 A 0 CASCO

4000

4000

BASEI MP 21400 0 21400 0

Administración de una base de datos La creación y el mantenimiento de una base de datos SQL Server implicará dominios de actividad diversos como:    

la administración del espacio de almacenamiento, la configuración de la base de datos, la administración de los objetos de la base de datos, la traducción de las restricciones del análisis,

 

la administración de la seguridad de acceso, las copias de seguridad.

Alguno de estos dominios implican también al administrador y se estudiarán posteriormente. La gestión y la configuración de SQL Server pueden hacerse de dos maneras: con Transact-SQL, interactivamente o por medio de un script, o con SQL Server Management Studio, con la interfaz gráfica. En SQL Server 2008 existen tres tipos de bases de datos: 





Bases de datos OLTP (OnLine Transaction Processing), es decir, bases de datos que soportan transacciones de usuarios. Este es el tipo de base de datos que se encuentra en producción. Sus principales características son que, a pesar de su gran volumen de datos y su elevado número de usuarios conectados, el tiempo de respuesta debe ser óptimo. Por suerte, los usuarios trabajan con transacciones cortas, y cada transacción manipula una cantidad de datos reducida. Bases de datos OLAP (On-Line Analytical Processing), es decir, bases de datos que permiten almacenar un número máximo de datos para atender consultas de ayuda a la toma de decisiones. Este tipo de bases de datos no se aborda en este libro. Bases de datos de tipo snapshot, que son duplicaciones más o menos completas de la base de origen destinadas a acceder rápidamente a datos remotos, por ejemplo. Este tipo de base de datos no se aborda en este libro.

Este libro solamente aborda las bases de datos de usuario. La gestión de bases de datos de sistema no entra en el ámbito de esta obra.

1. Administración del espacio de almacenamiento SQL Server utiliza una serie de archivos para almacenar el conjunto de la información relativa a una base de datos. Archivo primario

Sólo existe uno por base de datos: es el punto de entrada. Este archivo lleva la extensión *.mdf. Archivos secundarios

Pueden existir varios por base de datos y llevan la extensión *.ndf. Archivos de registro

Estos archivos (puede haber varios) contienen el registro de las transacciones y llevan la extensión *.ldf.

Los grupos de archivos

Es posible especificar un grupo de archivos en lugar de los nombres de archivo. Estos grupos presentan la ventaja de equilibrar las cargas de trabajo sobre los diferentes discos del sistema. Los datos se escriben de forma equitativa en los distintos archivos del grupo. Estructura de los archivos de datos

Los archivos de datos se dividen en páginas de 8 KB. Los datos se almacenan en el interior de las páginas; así, el tamaño máximo de una línea es de 8.060 bytes excepto los tipos text e image. Este tamaño de 8 KB permite:   

Mejores tiempos de respuesta en las operaciones de lectura/escritura. Soportar líneas de datos más largas y, por tanto, utilizar menos los tipos text e image. Una mejor gestión de las bases de datos de gran tamaño.

Estas páginas se agrupan en extensiones. Las extensiones están constituidas por ocho páginas contiguas (64 KB). Representan la unidad de asignación de espacio para las tablas y los índices. Para evitar la pérdida de espacio en disco, existen dos tipos de extensiones: 

Uniforme Reservada a un solo objeto.



Mixta Compartida por varios objetos, ocho como máximo.

Cuando se crea una tabla, las páginas se asignan en una extensión mixta. Cuando los datos representan ocho páginas se asigna una extensión uniforme a la tabla. Los archivos de datos

Los archivos de datos pueden redimensionarse de forma dinámica o manual. En el momento de crear el archivo se debe especificar:     

Nombre lógico del archivo para manipularlo con el lenguaje Transact-SQL. Nombre físico para precisar la ubicación del archivo. Un tamaño inicial. Un tamaño máximo. Un paso de incremento.

2. Administración de los objetos DATABASE Una DATABASE contiene todos los demás objetos:    

El catálogo de bases de datos. Los objetos de usuario ( tablas, valores por defecto, vistas, reglas, desencadenadores , procedimientos almacenados). Los índices , los tipos de datos, las restricciones. El registro de transacciones.

La persona que crea la base de datos debe disponer de derechos suficientes y se convierte en propietario de ella. SQL Server es capaz de administrar 32.767 bases de datos.

Los archivos que constituyen una base de datos no deben situarse en un sistema de archivos comprimido o en un directorio compartido en red.

Al crearlos, los archivos se inicializan con ceros para borrar todos los datos existentes. Esta operación conlleva una sobrecarga de trabajo pero permite optimizar los tiempos de respuesta cuando la base de datos está trabajando. Es posible asignar espacio en disco a la base sin que sea inicializada. Esta operación se identifica mediante el término inicialización instantánea. Los datos anteriormente presentados en el disco son borrados en función de las necesidades de la base. SQL Server es capaz de utilizar particiones sin procesar para la creación de los archivos de bases de datos. Sin embargo, en la gran mayoría de casos, el método preferible es el de crear los archivos en una partición NTFS o FAT. En efecto, la utilización de una partición sin procesar no permite señalar al sistema de explotación, y por tanto tampoco al administrador, de que esta partición está siendo utilizada en este momento por SQL Server. El espacio, no utilizado desde el punto de vista del sistema, puede entonces ser fácilmente utilizado para extender una partición o para crear una nueva partición. Al utilizar particiones sin procesar aumenta considerablemente el riesgo de malas manipulaciones a cambio de una ventaja que es poco significativa. Los archivos creados en particiones NTFS soportan sin problemas la compresión NTFS y eventualmente algunos grupos de archivos pueden ser posicionados en modo sólo lectura. Estas consideraciones sólo se aplican a las bases de datos de usuario y no se pueden aplicar a los archivos relativos a las bases de datos de sistema.

a. Creación de la base de datos

Para crear una base de datos, es necesario estar conectado como administrador del sistema o tener el permiso para utilizar CREATE DATABASE y situarse en la base de datos de sistema master . El objeto DATABASE debe crearse en primer lugar. Una base de datos contiene todos los demás objetos.    

El catálogo de base de datos (18 tablas de sistema). Los objetos de usuario (tablas, valores por defecto, vistas, reglas, desencadenadores, procedimientos). Los índices, los tipos de datos, las restricciones de integridad. El registro de transacciones.

El nombre de la base de datos debe ser único en una instancia SQL Server. Este nombre está limitado a 128 caracteres, respetando las reglas de construcción de identificadores. Esta longitud máxima está reducida a 123 caracteres si el nombre del registro de transacciones no se especifica en el momento de crear la base. Sintaxis CREATE DATABASE nombre [ ON [PRIMARY] [( [ NAME = nombreLógico, ] FILENAME = ’nombreFísico’ [, SIZE = tamaño] [, MAXSIZE = { tamMax | UNLIMITED } ] [, FILEGROWTH = valorIncremento] ) [,...]] [ LOG ON { archivo } ] [ COLLATE nombre_intercalación ] [ FOR ATTACH | FOR ATTACH_REBUILD_LOG ]

NAME Nombre lógico del archivo. FILENAME Ubicación y nombre físico del archivo. SIZE Tamaño inicial del archivo en megabytes (MB) o kilobytes (KB). El tamaño predeterminado es de 1 megabyte. MAXSIZE

Tamaño máximo del archivo indicado en kilobytes o megabytes (por defecto, megabytes). Si no se indica ningún valor, el tamaño del archivo estará limitado por el espacio libre en disco. UNLIMITED Sin tamaño máximo: el límite es el espacio libre en disco. FILEGROWTH Precisa la tasa de incremento para el tamaño del archivo, que no podrá sobrepasar nunca el valor máximo. Este paso puede precisarse en porcentaje o de forma estática, en kilobytes o megabytes. Las extensiones poseen un tamaño de 64 KB; éste es, pues, el valor mínimo de la tasa de incremento que se puede indicar. LOG ON Ubicación del registro de transacciones. El registro de transacciones guarda las modificaciones realizadas sobre los datos. Por cada INSERT, UPDATE o DELETE, se efectúa una escritura en el registro de transacciones antes de la escritura en la base. La validación de las transacciones se consigna también en el registro. Este registro sirve para la recuperación de los datos en caso de fallo. COLLATE Indica la intercalación predeterminada de la base de datos. El nombre de la intercalación puede ser una intercalación SQL o Windows. Si no se especifica, se utiliza la intercalación predeterminada de la instancia SQL Server. FOR ATTACH Para crear una base utilizando archivos ya creados. Esta petición es útil cuando la base se crea con más de 16 archivos. FOR ATTACH_REBUILD_LOG Con esta opción se puede crear la base de datos adjuntando a ella los archivos de datos (mdf y ndf), pero no necesariamente los de registro. Éstos últimos se reconstruirán con valor nulo. Si una base de se adjunta de esta forma, es importante efectuar lo antes posible una copia de seguridad completa y planificar todos los procesos de backup. No se puede confiar en las copias de seguridad realizadas antes de adjuntar la base de datos, ya que las secuencias de registro no corresponderán a la realidad. Ejemplo Creación de la base de datos GESCOM (6 MB) con el registro de transacciones (2 MB).

Por supuesto, esta operación puede realizarse desde la consola gráfica SQL Server Management Studio. Para hacerlo, seleccione el nodo Bases de datos en el explorador y elija Nueva base de datos en el menú contextual. Aparecerá la siguiente pantalla.

En esta ventana de diálogo podrá definir las distintas opciones de creación de la base de datos. b. Modificación de tamaño

Es posible aumentar o disminuir el tamaño de los archivos de forma automática o manual. Aumento del tamaño

Si se especifican una tasa de incremento (FILEGROWTH) y un tamaño máximo en el momento de la creación del archivo, el archivo cambiará de tamaño en función de las necesidades. Es posible modificar manualmente el tamaño, el tamaño máximo y la tasa de aumento de un archivo de datos con la instrucción ALTER DATABASE. ALTER DATABASE nombre MODIFY FILE (NAME=nombreLógico [,SIZE=tamaño] [,MAXSIZE=tamMax] [FILEGROWTH=valorIncremento])

Ejemplo Aumentar el tamaño de un archivo existente:

También es posible añadir archivos. ALTER DATABASE nombre ADD FILE ( NAME = nombreLógico, FILENAME = ’nombreFísico’ [, SIZE = tamaño] [, MAXSIZE = { tamMax | UNLIMITED } ] [, FILEGROWTH = valorIncremento] )

Ejemplo

Adición de un segundo archivo a la base GESCOM:

El comando ALTER DATABASE permite una acción mucho más importante sobre la base de datos que la simple modificación del tamaño de los archivos. También es posible añadir o eliminar archivos y grupos de archivos, cambiar el nombre de la base de datos, especificar el modo de finalización predeterminado de las transacciones en curso y cambiar la intercalación de la base de datos.

Para conocer las distintas intercalaciones disponibles en el servidor, debe ejecutar la consulta siguiente: SELECT * FROM ::fn_helpcollations () Disminución del tamaño

El tamaño de los archivos puede disminuir de forma automática si la opción autoshrink se ha activado en la base de datos. Los comandos DBCC SHRINKFILE y DBCC SHRINKDATABASE permiten efectuar manualmente la disminución de tamaño. DBCC SHRINKFILE sólo afecta a un archivo en particular mientras que DBCC SHRINKDATABASE escrutará todos los archivos de la base. El ajuste del tamaño de los archivos siempre comienza a partir del final. Por ejemplo, si la base de datos dispone de un archivo de 500 MB que se desea reducir a un tamaño de 400 MB, serán los últimos 100 MB del fichero los que serán reorganizados antes de la liberación del espacio para evitar el bloqueo de datos. La cantidad de espacio que en realidad se libera está en función del tamaño ideal fijado por el parámetro en DBCC SHRINKFILE y los datos reales. Si el archivo del ejemplo anterior contiene 450 MB utilizados, sólo se liberarán 50 MB. Sintaxis DBCC SHRINKFILE (nombre_archivo {[ ,nuevo_tamaño ] | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] }) DBCC SHRINKDATABASE ( nombre_base [ , nuevo_tamaño% ] [ , { NOTRUNCATE | TRUNCATEONLY } ] )

DBCC SHRINKFILE Reduce el tamaño del archivo de datos o del registro de transacciones para la base de datos especificada. DBCC SHRINKDATABASE

Reduce el tamaño de los archivos de datos en la base de datos especificada. nuevo_tamaño Indica el tamaño deseado del archivo después de la reducción. nuevo_tamaño% Indica el porcentaje de espacio libre que deseamos obtener en el archivo de datos después de la reducción de la base. EMPTYFILE Permite solicitar al comando DBCC_SHRINKFILE que transfiera todos los datos presentes en ese archivo de datos a otro archivo del mismo grupo. Una vez vacío, el archivo podrá eliminarse por medio de un comando ALTER DATABASE. NOTRUNCATE Reorganiza el archivo situando las páginas ocupadas en la parte superior del mismo, pero no disminuye su tamaño. TRUNCATEONLY Corta el archivo sin hacer ninguna reorganización del archivo. c. Eliminación de la base de datos

El comando DROP DATABASE permite eliminar la base de datos. Los archivos físicos también se suprimen. Si se separan de la base ciertos archivos antes de eliminarlos, éstos no se borrarán y será necesario efectuar esta operación manualmente desde el explorador de archivos. Finalmente, si hay usuarios conectados a la base no es posible eliminarla. Para forzar la desconexión de los usuarios y permitir la eliminación de la base es necesario cambiar al modo SINGLE_USER mediante la instrucción ALTER DATABASE. La instrucción DROP DATABASE sólo puede ser ejecutada si está activado el modo autocommit (está activado de forma predeterminada). No es posible eliminar las bases de datos del sistema. d. Renombrar una base de datos

Se puede renombrar una base de datos por medio de la instrucción ALTER DATABASE.

Sintaxis ALTER DATABASE nombreBase MODIFY NAME= nuevoNombreBase

El procedimiento sp_renamedb se ha mantenido por motivos de compatibilidad. Hay que tratar de no utilizarlo en SQL Server 2008. e. Configuración de una base de datos

Se puede configurar una base de datos para fijar cierto número de opciones y así lograr que el comportamiento de la base de datos cubra las necesidades de los usuarios. Para ello se debe acceder a las opciones en cuestión a través de la interfaz gráfica de SQL Server Management Studio , seleccionar la base de datos y abriendo la ventana de propiedades con la tecla [F4]; también se puede hacer a través del menú contextual asociado a la base de datos, o bien por el menú Ver - Ventana propiedades en el menú general de SQL Server Management Studio.

Hasta ahora, SQL Server utilizaba el procedimiento almacenado sp_dboption para definir las opciones de configuración de la base. Este procedimiento todavía existe en SQL Server 2008, pero solamente para conservar la compatibilidad de los scripts existentes. El procedimiento sp_dboption dejará de existir en las versiones futuras de SQL Server. Es necesario utilizar la instrucción ALTER DATABASE o bien el procedimiento almacenado sp_replicationdboption cuando las opciones están asociadas a la replicación. Sintaxis ALTER DATABASE nomBase SET opción ;

Estado de la base de datos

ONLINE Permite hacer visible de nuevo la base de datos. OFFLINE Permite hacer inaccesible la base de datos, que queda detenida y cerrada correctamente. No es posible realizar operaciones de mantenimiento en una base de datos offline. EMERGENCY La base de datos está en modo de sólo lectura, los registros están deshabilitados y su acceso queda limitado a los administradores del servidor.

Acceso

SINGLE_USER Acceso limitado a un solo usuario. RESTRICTED_USER Solo los miembros con función fija db_owner, dbcreator o sysadmin pueden conectarse a la base de datos. MULTI_USER Es el modo predeterminado, que permite que todos los usuarios con privilegios suficientes accedan a la información. Operaciones posibles

READ_ONLY La base de datos es accesible solamente para operaciones de lectura. READ_WRITE La base de datos es accesible para operaciones de lectura/escritura. Acceso

DBO use only Sólo el propietario podrá acceder a la base. Configuración

ANSI_NULL_DEFAULT Define el valor por defecto de la restricción de nulidad de columna. Según la norma ANSI, una columna puede ser NULL por defecto. RECURSIVE_TRIGGERS Permite la recursividad de los desencadenadores. TORN_PAGE_DETECTION Permite detectar las páginas incompletas.

AUTO_CLOSE La base se detiene y los recursos se liberan tras la desconexión del último usuario. AUTO_SHRINK Los archivos de la base podrán reducirse automáticamente. AUTO_CREATE_STATISTICS Todas las estadísticas que falten en la optimización de una consulta se crean. Esta opción está habilitada (en ON) de forma predeterminada. AUTO_UPDATE_STATISTICS Todas las estadísticas obsoletas para la correcta optimización de una consulta se recalculan. AUTO_UPDATE_STATISTICS_ASYNC Las estadísticas que permiten representar la pertinencia de los índices se actualizan de forma asíncrona. La consulta que provoca la actualización de las estadísticas no espera a que las estadísticas estén actualizadas para ejecutarse; serán las consultas futuras las que aprovecharán esta actualización. Esta opción está activa (en ON) de forma predeterminada. QUOTED_IDENTIFIERS Los identificadores delimitados pueden encerrarse entre comillas dobles. ANSI_NULLS Si el parámetro es verdadero (true), todas las comparaciones con un valor NULL se evalúan como NULL. Si el parámetro es falso (false), todas las comparaciones con los valores NULL y los valores no unicode se evalúan como TRUE si ambos valores son NULL. ANSI_WARNINGS Permite lanzar mensajes de error o advertencias cuando se cumplen ciertas condiciones. ARITHABORT Permite detener el procesamiento por lotes de instrucciones cuando se produce un desbordamiento de capacidad o una división por cero. CONTACT_NULL_YIELDS_NULL

El resultado es NULL si uno de los dos operandos de una operación de concatenación es NULL. CURSOR_CLOSE_ON_COMMIT Permite cerrar todos los cursores cuando se define una transacción o al finalizar una transacción. CURSOR_DEFAULT Las declaraciones de cursor tienen como valor predeterminado LOCAL. NUMERIC_ROUNDABORT Se lanza un error si se produce una pérdida de precisión durante un cálculo. RECOVERY Permite indicar la estrategia de copia de seguridad planificada a nivel de la base de datos. Este parámetro tiene una influencia directa sobre los datos que se conservan en los registros de transacciones. PAGE_VERIFY Esta opción permite validar la calidad de los datos almacenados a nivel de cada página. La opción predeterminada CHECKSUM es la recomendada por SQL Server. SUPPLEMENTAL_LOGGING Al ajustar esta opción en ON (de forma predeterminada está en OFF), se agregarán datos complementarios al registro de transacciones. Se puede conocer el estado de esta opción examinando su valor en la columna is_supplemental_logging_enabled de la vista sys.databases. PARAMETERIZATION En modo SIMPLE, de forma predeterminada las consultas se parametrizarán en función de las reglas que estén en vigor en el servidor. Utilizando el modo FORCED, SQL Server parametriza todas las consultas antes aplicar el plan de ejecución. Gestión de transacciones

ROLLBACK AFTER número La anulación de transacciones es efectiva después de número segundos de espera.

ROLLBACK IMMEDIATE La anulación de la transacción es inmediata. NO_WAIT Si la transacción no accede inmediatamente a los recursos que necesita, queda anulada. ANSI_PADDING Permite especificar si los espacios situados a la derecha sobre los datos de tipo carácter deben ser eliminados o no. COMPATIBILITY Permite fijar el nivel de compatibilidad de la base de datos: 80 para SQL Server 2000, 90 para SQL Server 2005 y 100 para SQL Server 2008. DATE_CORRELATION_OPTIMISATION Con esta opción SQL se encarga de mantener la correlación de las estadísticas entre dos tablas vinculadas por una limitación de clave externa y que poseen una columna de tipo datetime. Acceso externo

DB_CHAINING Permite administrar los contextos de seguridad en el acceso a la base de datos a partir de otra base de datos. TRUSTWORTHY Los módulos internos (procedimientos almacenados, funciones) pueden acceder a recursos externos al servidor mediante un contexto impersonal. Service Broker

ENABLE_BROKER Activa el servicio Broker. DISABLE_BROKER Desactiva el servicio Broker.

NEW_BROKER Permite precisar que la base necesita recibir un nuevo identificador Service Broker. ERROR_BROKER_CONVERSATIONS Las conversaciones en curso reciben un mensaje de error y se cierran. Snapshot (Captura instantánea)

ALLOW_SNAPSHOT_ISOLATION Cuando está activado este modo, todas las transacciones pueden trabajar con una captura instantánea (snapshot) de la base tal como estaba al comienzo de la transacción. READ_COMMITTED_SNAPSHOT Cuando está activado este modo, todas las instrucciones ven los datos tal como estaban al comienzo de la instrucción.

Gestión de las tablas y los índices 1. Identificador Todos los elementos creados en SQL Server se identifican perfectamente por su nombre, que se utiliza como identificador. Efectivamente, dos objetos del mismo tipo no pueden tener el mismo nombre si están definidos en el mismo nivel. Por ejemplo, en una instancia de SQL Server, no es posible tener dos bases de datos con el mismo nombre, pero sí es posible si las bases están definidas en dos instancias distintas de SQL Server. De igual manera, en una base de datos, no es posible tener dos tablas con el mismo nombre. El identificador es el que permite manejar los objetos a través de SQL. Por eso es importante definir correctamente estos identificadores. Los identificadores se componen de entre 1 y 128 caracteres. Comienzan siempre por una letra o uno de los caracteres siguientes: _, @, #. Los caracteres siguientes tienen que ser caracteres alfanuméricos. Obviamente no puede haber identificadores que sean palabras clave de Transact SQL. Hay dos categorías de identificadores: los regulares y los delimitados. Los identificadores regulares

Esta categoría de identificador es la más comúnmente utilizada y es a la que se debe dar preferencia. Efectivamente, este tipo de identificador está presente en todas las bases y es

muy sencillo en cuanto a la escritura de las consultas ya que no se distingue entre mayúsculas y minúsculas. Ejemplo: ENIEdiciones, RecursosInformáticos

Los identificadores delimitados

Esta categoría de identificador permite conservar caracteres especiales en los identificadores, como los caracteres acentuados, los espacios… pero también distingue entre mayúsculas y minúsculas. Estos identificadores se utilizan entre corchetes [] o comillas “”. La utilización de este tipo de identificador sólo permite ganar claridad en raras ocasiones porque la escritura de las consultas es más pesada. Por eso es preferible utilizar identificadores regulares. Ejemplo: [ENI ediciones], "Recursos Informáticos", ...

2. Los tipos de datos En el momento de definir una columna, se precisará el formato de uso del dato y el modo de almacenamiento por el tipo de la columna. a. Tipos de datos de sistema

Estos tipos están disponibles para todas las bases de datos de modo estándar. Caracteres

char[(n)] Cadena de caracteres de longitud fija, de n caracteres como máximo. Por defecto, 1; máximo, 8000. varchar(n|max) Cadena de caracteres de longitud variable, con n caracteres como máximo. Su valor predeterminado es 1 y el máximo 8.000 caracteres. Si se precisa max, la variable puede contener hasta 231 caracteres de texto. nchar[(n)] Cadena de caracteres unicode; máximo, 4.000. nvarchar (n|max)

Cadena de un máximo de 4.000 caracteres unicode. Si se precisa max, la variable puede contener hasta 231 bytes de datos de tipo texto.

El tipo sysname , que aparece cuando trabajamos con las tablas del sistema, se utiliza para referenciar los nombres de objetos. Este tipo es idéntico a un nvarchar(128) con la particularidad de que están prohibidos los valores null. Numéricos

decimal [(p[,d])] Numérico exacto de precisión p (número de cifras total), con d cifras a la derecha de la coma. p está comprendido entre 1 y 38, 18 por defecto. d está comprendido entre 1 y p, 0 por defecto. Ejemplo: para decimal (8,3) el intervalo admitido será de -99999,999 a +99999,999. Los valores son gestionados de -1038 a 1038 -1. numeric [(p[,d])] Idéntico a decimal. Para el tipo decimal, la precisión podrá ser a veces más grande que la requerida. Bigint Tipo de datos entero codificado en 8 bytes. Los valores almacenados en este tipo de datos están comprendidos entre -263(-9.223.372.036.854. 775.808) y 2631(9.223.372.036.854.775. 807). int Número entero entre -231 (-2147783648) y +231 (+2147483647). El tipo de datos int es específico de SQL Server y su sinónimo integer es compatible con ISO. smallint Número entero entre -215 (-32.768) y 215 -1 (+32.767). tinyint

Número entero positivo entre 0 y 255. float[(n)] Numérico aproximado de n cifras; n comprende de 1 a 53. real Idéntico a float(24). money Numérico en formato moneda comprendido entre - 922.337.203.685. 477,5808 y +922.337.203.685.477,5807(8 bytes). smallmoney Numérico en formato moneda comprendido entre -214.748,3648 y +214.748,3647 (4 bytes). Binarios

binary[(n)] Dato binario en n bytes (1 a 8000); la longitud es fija. varbinary (n|max) Dato binario de longitud variable de n bytes (1 a 8000). La opción max permite reservar un espacio máximo de 231 bytes. binary varying Idéntico a varbinary. Fecha

Para la administración de los datos de tipo fecha y hora SQL Server 2008 propone nuevos tipos de datos para optimizar el almacenamiento de los datos. Estos nuevos tipos de datos se introducen para mejorar la administración de los datos de los tipos fecha y hora. En primer lugar, existen tipos específicos para almacenar los datos de tipo hora y otros para almacenar los datos de tipo fecha. Esta separación es beneficiosa porque permite dar más precisión a la vez que se limita el espacio utilizado por los datos de uno u otro tipo. Por ejemplo, ¿es necesario conservar datos de tipo horas, minutos y segundos cuando sólo hace falta conservar la fecha de nacimiento de un cliente? Sin duda, no. El simple hecho de

conservar estos datos puede producir errores porque se van a realizar cálculos. Por tanto, es más razonable y más rentable adoptar para este dato un tipo que sólo conserve la fecha. Estos nuevos tipos de datos, para los datos de tipo fecha y de tipo hora, permiten igualmente una mejor compatibilidad con el resto de sistemas de gestión de datos y facilitan las operaciones de recuperación de datos. SQL Server ofrece la posibilidad con los tipos datetime2 y datetimeoffset de conservar datos de tipo fecha y hora de manera simultánea. El tipo datetimeoffset permite no sólo almacenar datos de tipo fecha y hora con una precisión de hasta 100 nanosegundos, sino que además conserva la hora en formato UTC y la diferencia (en número de horas) entre esta hora UTC y la zona horaria desde la que trabaja el usuario que introduce los datos en la base.

Los tipos datetime y smalldatetime están siempre presentes en SQL Server, pero es preferible utilizar los tipos time, date, datetime2 y datetimeoffset en las nuevas programaciones, ya que ofrecen mayor precisión y un mejor respeto de los estándares SQL.

datetime Fecha y hora almacenada en 8 bytes; 4 para un número de días respecto al 1 de enero de 1900, 4 para un número de milisegundos después de medianoche.Las fechas se gestionan del 1 de enero de 1753 al 31 de diciembre de 9999. Las horas se gestionan con una precisión de 3‟33 milisegundos. smalldatetime Fecha y hora almacenados en 4 bytes. Las fechas se gestionan del 1 de enero de 1900 al 6 de junio de 2079, con una precisión de un minuto. datetime2 Más preciso que el tipo datetime, permite almacenar un dato de tipo fecha y hora comprendido entre el 01/01/0001 y el 31/12/9999 con una precisión de 100 nanosegundos. datetimeoffset Permite almacenar un dato de tipo fecha y hora comprendido entre el 01/01/0001 y el 31/12/9999 con una precisión de 100 nanosegundos. Los datos horarios se almacenan en formato UTC y se conserva el desfase horario para recuperar la hora local indicada inicialmente. date

Permite almacenar una fecha comprendida entre el 01/01/0001 y el 31/12/9999 con una precisión de un día. time Permite almacenar un dato positivo de tipo hora inferior a 24:00 h con una precisión de 100 nanosegundos. Especiales

bit Valor binario que puede tomar los valores 0 o 1. timestamp Dato cuyo valor se actualiza automáticamente cuando la línea se modifica o inserta. uniqueidentifier Permite crear un identificador único basado en la función NEWID(). sql_variant El tipo de datos sql_variant permite almacenar cualquier tipo de datos, exceptuando datos de tipo text, ntext, timestamp y sql_variant. Si una columna utiliza este tipo de datos, las distintas filas de la tabla pueden almacenar, en dicha columna, datos de tipos diferentes. Una columna de tipo sql_variant puede poseer una longitud máxima de 8.016 bytes. Antes de utilizar un valor almacenado en formato sql_variant en una operación, es necesario convertir los datos a su formato original. Las columnas que utilizan el tipo sql_variant pueden participar en restricciones de claves primarias, de claves externas o de unicidad, pero los datos contenidos en la clave de una fila no pueden exceder los 900 bytes (límite impuesto por los índices). sql_variant no puede ser utilizado por las funciones CONTAINSTABLE y FREETEXTABLE. table Es un tipo de dato particular, que permite almacenar y reenviar un conjunto de valores con vistas a una utilización futura. El modo principal de uso de este tipo de datos es la creación de una tabla temporal.

xml Este tipo permite almacenar un documento xml en una columna de una tabla relacional.

Los tipos text, ntext e images se mantienen por motivos de compatibilidad. Es muy preferible utilizar varchar(max) y varbinary(max).

SQL Server propone asimismo un cierto número de sinónimos de sus propios tipos de base. Los sinónimos existen con frecuencia para asegurar la compatibilidad con la norma ISO. Sinónimo

Tipo SQL Server

Caracteres varchar char varying char(n) character(n) varchar(n) character varying(n) nchar(n) national character(n) nchar(n) national char(n) nvarchar(n) national character varying(n) nvarchar(n) national char varying(n) ntext national text Numéricos decimal dec float double precision int integer Binarios varbinary binary varying Otros timestamp rowversion b. Tipos de datos definidos por el usuario

Se pueden definir tipos de datos propios a través de Management Studio o del comando CREATE TYPE.

Los procedimientos almacenados sp_addtype y sp_droptype se mantienen por motivos de compatibilidad. Microsoft recomienda no utilizarlos más, ya que indudablemente no estarán presentes en las siguientes versiones de SQL Server. Sintaxis (creación) CREATE TYPE nombreTipo {FROM tipoDeBase [ ( longitud [ , precisión ] ) ] [ NULL | NOT NULL ] | EXTERNAL NAME nombreAssembly [ .nombreClase] } [ ; ]

Se puede definir un tipo de datos a partir de la definición de una clase. Esta opción está ligada a la integración de CLR en SQL Server. Veremos esta integración más adelante en este libro. Sintaxis (eliminación) DROP TYPE [ schema_name. ] type_name [ ; ]

Para definir un tipo de datos que basado en un tipo de CLR, hay que habilitar CLR por medio de sp_dboption.

Será imposible suprimir un tipo si éste se utiliza en una tabla de la base de datos en la que ha sido creado. Ejemplos Creación de un tipo para columnas como nombre cliente, nombre proveedor, etc.:

Creación de un tipo para valores entre -999 y +999:

Pedir la creación de un nuevo tipo de datos desde el SQL Server Management Studio:

Definición de un nuevo tipo de datos "Importe" mediante el SQL Server Management Studio:

Además de definir alias (como se ilustra aquí), la instrucción CREATE TYPE permite crear tipos UDT (User Defined Type), es decir, definidos mediante un CLR (este punto se explicará en el capítulo CLR). En Transact SQL, la instrucción CREATE TYPE permite asimismo crear tipos compuestos por varios campos. Estos tipos se denominan con frecuencia tipos estructurados en los lenguajes de programación. En lo referente a SQL Server, la instrucción CREATE TYPE permite crear un tipo TABLE o cuadro. Cada columna que participa en la definición de este nuevo tipo se define por el mismo principio que una columna de una tabla. De esta manera es posible definir las restricciones de integridad de clave primaria (PRIMARY KEY), de unicidad (UNIQUE), de validación (CHECK) y de no nulidad. Estas restricciones pueden ser definidas en el nivel de la columna o de la tabla. También es posible definir una columna de tipo identidad. La instrucción CREATE TYPE permite así crear tipos denominados muy característicos, porque las restricciones de integridad permiten una definición más precisa del formato posible de los datos. La introducción de este nuevo tipo permitirá definir parámetros de funciones o procedimientos de tipo cuadro. Hablamos entonces de un table value parameter. Sintaxis CREATE TYPE nombreTipo AS TABLE ( Columna tipoColumna[restinteColumna], ...)

nombreTipo Nombre del tipo así creado. columna Nombre de la columna que participa en la definición de este nuevo tipo. Obviamente es posible definir varias columnas. tipoColumna Tipo de datos Transact SQL sobre el cual está definida la columna. No todas las columnas de un mismo tipo de tabla están necesariamente definidas sobre el mismo tipo ni con la misma precisión. restinteColumna Definición de la restricción de integridad asociada a la columna. Ejemplo

En el ejemplo siguiente se define un tipo que representa a un individuo. Este tipo está compuesto por los campos estado civil, apellido y nombre. Para el campo estado civil sólo están permitidos ciertos valores.

3. Gestión de las tablas Una tabla representa una estructura lógica donde se ordenan los datos. Para permitir una buena organización de la información, cada tabla está compuesta por columnas para poder estructurar los datos. Cada columna está perfectamente identificada por su nombre, que es único dentro de cada tabla, y por su tipo de datos. Los datos se distribuyen entre varias tablas. Las limitaciones de integridad permiten garantizar la coherencia de los datos. Las tres operaciones de gestión de tabla son la creación ( CREATE TABLE), la modificación ( ALTER TABLE) y la eliminación ( DROP TABLE). Estas operaciones podrán realizarse en Transact-SQL o mediante el SQL Server Management Studio por un usuario "dbo" o que haya recibido el derecho CREATE TABLE. a. Creación de tabla

La etapa de creación de las tablas es una etapa importante del diseño de la base porque los datos se organizan en relación a las tablas. Esta operación es puntual y en general la realiza el administrador (DBA: DataBase Administrator) o por lo menos la persona encargada de administrar la base. La creación de una tabla permite definir las columnas (nombre y tipo de datos) que la componen, así como las restricciones de integridad. También es posible definir columnas calculadas, un criterio de ordenación específico y el destino de los datos de tipo text, ntext o image. Sintaxis CREATE TABLE [nombreEsquema.] nombre_tabla ( nombre_columna {tipocolumna|AS expresión_calculada} [,nombre_columna ... ][,restricciones...]) [ON grupoarchivo] [TEXTIMAGE_ON grupo_archivo]

nombreEsquema Nombre del esquema en el que se va a definir la tabla. nombre_tabla Puede ser de la forma base.propietario.tabla.

nombre_columna Nombre de la columna, que debe ser único en la tabla. Puede haber 250 columnas por tabla. tipocolumna Tipo de sistema o tipo definido por el usuario. restricciones Reglas de integridad (tratadas posteriormente en esta obra). grupoarchivo Grupo de archivos sobre el cual se creará la tabla. AS expresión_calculada Es posible definir una regla de cálculo para las columnas que contienen datos calculados. Por supuesto, únicamente podrá accederse a estas columnas en modo sólo lectura, y no es posible insertar datos o actualizar los datos de una columna de este tipo. TEXTIMAGE_ON Permite especificar el grupo de archivos de destino para los datos de tipo text, ntext e image. Se pueden crear 2 millardos de tablas por base de datos.

El número máximo de columnas por tabla es 1.024. La longitud máxima de una línea es de 8.060 bytes (sin contar los datos texto o imagen).

Ejemplos Creación de la tabla ARTICULOS:

Presentación de los datos de la tabla por medio del procedimiento almacenador sp_help:

Creación de la tabla CLIENTES (desde la interfaz gráfica):

b. Modificación de una tabla

La modificación de una tabla se realiza con un comando ALTER TABLE o mediante la interfaz gráfica del SQL Server Management Studio. Al modificar una tabla, es posible añadir y eliminar columnas y restricciones, modificar la definición de una columna (tipo de datos, intercalación y comportamiento respecto al valor NULL), activar o desactivar las restricciones de integridad y los desencadenadores. Este último punto puede resultar útil durante la importación masiva de datos a la base si deseamos conservar tiempos de proceso coherentes. Sintaxis ALTER TABLE [nombreEsquema.] nombretabla { [ ALTER COLUMN nombre_columna { nuevo_tipo_datos [ ( longitud [ , precisión ] ) ] [ COLLATE intercalación ] [ NULL | NOT NULL ] } ] | ADD nueva_columna | [ WITH CHECK | WITH NOCHECK ] ADD restricción_tabla | DROP { [ CONSTRAINT ] nombre_restricción | COLUMN nombre_columna } | { CHECK | NOCHECK } CONSTRAINT { ALL | nombre_restricción } | { ENABLE | DISABLE } TRIGGER { ALL | nombre_desencadenador } }

nombreEsquema Nombre del esquema en el que va a estar definida la tabla. WITH NOCHECK Permite establecer una restricción de integridad en la tabla sin que dicha restricción se compruebe para las filas ya existentes en la misma. COLLATE Permite definir una intercalación para la columna, distinta de la base de datos. NULL, NOT NULL Permiten definir una restricción de nulidad o de no nulidad en una columna existente en la tabla. CHECK, NOCHECK Permiten activar y desactivar restricciones de integridad. ENABLE, DISABLE

Permiten activar y desactivar la ejecución de desencadenadores asociados a la tabla. Ejemplo Agregar una columna:

c. Eliminación de una tabla

La eliminación de una base implica la eliminación de todos los datos existentes en la tabla. También se eliminan los desencadenadores y los índices asociados a la tabla. Ocurre lo mismo para los permisos de utilización de la tabla. Por el contrario, la eliminación de la tabla no afecta a las vistas, los procedimientos y las funciones que hacen referencia a la tabla. Si hacen referencia a la tabla eliminada, aparecerá un error en la próxima ejecución. Sintaxis DROP TABLE [nombreEsquema.]nombre_tabla [,nombre_tabla...]

La eliminación de una tabla eliminará los datos y los índices asociados. La eliminación no será posible si la tabla es referenciada por una clave externa . Eliminación de una tabla:

d. Nombre completo de una tabla

En función de la ubicación desde la que se hace referencia a la tabla, y en términos más generales, al objeto, habrá que utilizar un nombre más o menos preciso. El nombre completo de una tabla, y por tanto de un objeto, adopta la siguiente forma: nombreBase.nombreEsquema.nombreObjeto

Sin embargo, como habitualmente los objetos referenciados están presentes en la base actual, es posible omitir el nombre de la base. También es posible no especificar el nombre del esquema. En este caso, el motor de base de datos buscará el objeto en el esquema asociado al usuario y si esta búsqueda resulta infructuosa buscará en el esquema dbo.

Implementación de la integridad de los datos

Para asegurar la coherencia de los datos en la base de datos, es posible gestionar a nivel del servidor un conjunto de funcionalidades que permitirán centralizar los controles y las reglas de funcionamiento dictadas por el análisis. La implementación de la integridad de los datos puede hacerse de manera procedural con los valores por defecto ( DEFAULT) y los desencadenadores ( TRIGGER) o de manera declarativa por las restricciones ( CONSTRAINT) y la propiedad IDENTITY. La integridad de los datos traduce las reglas del modelo relacional, regla de coherencia (integridad de dominio), existencia de valores nulos, regla de unicidad (integridad de entidad) y claves externas (integridad referencial).

En la medida de lo posible, es preferible implementar la integridad bajo la forma de restricción porque la restricción forma entonces parte integrante de la estructura de la tabla. El respeto de la restricción es efectivo para todas las filas de datos y la verificación es mucho más rápida.

1. Los valores por defecto Desde SQL Server 2005, los objetos DEFAULT ya no tienen validez y no deben ser utilizados en nuevos desarrollos. En efecto, este tipo de objeto no cumple la norma SQL. Aunque siempre estén presentes las instrucciones CREATE DEFAULT , DROP DEFAULT , sp_bindefault y sp_unbindefault , es preferible definir el valor predeterminado en el momento de crear la tabla (CREATE TABLE) o al pasar por una instrucción de modificación de tabla (ALTER TABLE). Como de costumbre, estas operaciones pueden ejecutarse en forma de secuencia de comandos o por medio de SQL Server Management Studio.

2. Reglas Para que la gestión de los distintos elementos de la base sea más uniforme (con la generalización de las instrucciones CREATE, ALTER y DROP) y para acercarse más a la norma, SQL Server 2008 ya no ofrece gestión de reglas como objeto independiente. Las restricciones de integridad que antes podían expresarse en forma de regla deben definirse cuando se crea la tabla con la instrucción CREATE TABLE. También se pueden agregar o quitar de una tabla existente con la instrucción ALTER TABLE. Para garantizar la continuidad de las secuencias de comandos, SQL Server continúa interpretando correctamente las instrucciones CREATE RULE , DROP RULE , sp_bindrule, sp_unbindrule.

3. La propiedad Identity Esta propiedad puede ser asignada a una columna numérica entera, durante la creación o la modificación de la tabla, y permite que el sistema genere valores para esta columna. Los valores serán generados en la creación de la fila, sucesivamente y partiendo del valor inicial especificado (por defecto, 1), y aumentando o disminuyendo fila tras fila en un incremento (por defecto, 1). Sintaxis CREATE TABLE nombre (columna tipoentero IDENTITY [(inicio, incremento)], ...)

¡Sólo puede haber una columna IDENTITY por tabla!

La propiedad IDENTITY debe ser definida al mismo tiempo que la columna a la que está vinculada. La definición de una columna identity puede realizarse en un comando CREATE TABLE o también en un comando ALTER TABLE.

Ejemplo

Al crear filas ( INSERT), no se requerirá ningún valor para NUMERO_FAC. La primera inserción asignará el NUMERO_FAC 1000; la segunda, el NUMERO_FAC 1001, etc. La palabra clave IDENTITYCOL podrá utilizarse en una cláusula WHERE en lugar del nombre de columna. La variable global @@IDENTITY almacena el último valor afectado por una identidad a lo largo de la sesión actual. La función SCOPE_IDENTITY permite realizar el mismo tipo de trabajo pero limita el alcance de la visibilidad en el único lote de instrucciones actual. La función IDENT_CURRENT permite conocer el último valor identidad generado para la tabla especificada en el parámetro, sean cuales sean las sesiones. Para permitir la inserción de datos sin usar la propiedad IDENTITY y la numeración automática, hay que recurrir a la instrucción IDENTITY_INSERT de la siguiente forma: SET IDENTITY_INSERT nombre_tabla ON

El parámetro ON permite desactivar el uso de la propiedad IDENTITY, mientras que la misma instrucción con el parámetro OFF reactiva la propiedad. Ejemplo

En el siguiente ejemplo agregaremos una categoría nueva. La primera inserción se salda con un error, ya que la propiedad IDENTITY está activa. Después de su desactivación por la instrucción SET IDENTITY_INSERT categorias ON, se puede insertar la fila de datos.

Es posible definir la propiedad identity desde SQL Server Management Studio a través de las pantallas de modificación o creación de una tabla (Diseño desde el menú contextual asociado a la tabla).

Se pueden utilizar las siguientes funciones para obtener más información sobre los tipos de identidad:  

IDENT_INCR para conocer el incremento del valor identity. IDENT_SEED para conocer el valor inicial fijado en la creación del tipo identity.

Todas estas funciones tienen el objetivo de permitir al programador que controle mejor el valor generado y, así, poder recuperarlo cuando se trate de la clave principal.

4. Las restricciones de integridad Las restricciones permiten implementar la integridad declarativa definiendo controles de valor a nivel de la estructura de la propia tabla. La definición de restricciones se realiza en forma de secuencia de comandos por medio de instrucciones CREATE y ALTER TABLE. También es posible definirlas desde SQL Server Management Studio. Se recomienda emplear, siempre que sea posible, restricciones de integridad en lugar de desencadenadores de base de datos porque, al estar normalizadas, restricciones de integridad limitan las tareas de programación y reducen las posibilidades de error. Están integradas en la definición de la estructura de la tabla y su verificación es más rápida que la ejecución de un desencadenador. Sintaxis ALTER TABLE nombretabla {ADD|DROP} CONSTRAINT nombreRestricción ...[;]

Se puede obtener información sobre las restricciones de integridad definidas: hay que consultar sys.key_constraints o utilizar los procedimientos almacenados sp_help y sp_helpconstraint. Las restricciones se asociarán a la tabla o a una columna de la tabla, según el caso.

Se puede verificar la integridad de las restricciones a través de DBCC CHECKCONSTRAINT . Cuando más sentido tiene esta utilidad es en caso de que las restricciones de una tabla se hayan desactivado y vuelto a activar sin verificar los datos de la tabla. a. NOT NULL

SQL Server considera la restricción de nulidad como una propiedad de columna; la sintaxis es, pues: CREATE TABLE nombretabla (nombrecolumna tipo [{NULL | NOT NULL}] [? ...])

NOT NULL Especifica que la columna debe recibir un valor cuando se crea o se modifica. Es preferible especificar sistemáticamente NULL o NOT NULL , porque los valores por defecto de esta propiedad dependen de muchos factores:    

Para un tipo de datos definido por el usuario, es el valor especificado en la creación del tipo. Los tipos bit y timestamp sólo aceptan NOT NULL. Los parámetros de sesión ANSI_NULL_DFLT_ON o ANSI_NULL_DFLT_ OFF pueden ser activados por el comando SET . Puede colocarse parámetro de base de datos ‟ANSI NULL‟.

Desde SQL Server 2005 es posible modificar la restricción de NULL/NOT NULL con un comando ALTER TABLE para una columna ya existente. Por supuesto, los datos ya existentes en la tabla deben respetar las restricciones. b. PRIMARYKEY

Esta restricción permite definir un identificador de clave principal, es decir, una o más columnas que sólo aceptan valores únicos en la tabla (regla de unicidad o restricción de entidad). Sintaxis [CONSTRAINT nombrerestricción]PRIMARY KEY[CLUSTERED |NONCLUSTERED ] (nombrecolumna[,...])[WITH FILLFACTOR=x][ON grupo_archivos]

nombrerestricción Nombre que permite identificar la restricción en las tablas de sistema. Por defecto, SQL Server dará un nombre difícil de manipular.

Esta restricción automáticamente creará un índice único, agrupado por defecto, con el nombre de la restricción; de ahí las opciones NONCLUSTERED y FILLFACTOR. Una clave principal puede contener hasta 16 columnas. No puede haber más de una clave principal por tabla. Las columnas que la definen deben ser NOT NULL.

Ejemplos Tabla categoría, que identifica a COD_CAT:

Adición de la clave principal a la tabla LINEAS_PDO (un índice agrupado existe ya sobre NUMERO_PDO):

Gestión de la clave principal mediante el SQL Server Management Studio:

No es posible eliminar la clave principal si:  

hace referencia a ella una restricción de clave externa, se ha definido un índice xml principal sobre la tabla.

c. UNIQUE

Esta restricción permite también traducir la regla de unicidad para las otras claves únicas de la tabla o identificadores claves secundarios . Esta restricción posee las mismas características que PRIMARY KEY, con dos excepciones:  

puede haber varias restricciones UNIQUE por tabla, las columnas utilizadas pueden ser NULL (¡no recomendado!).

Cuando se agrega una restricción de unicidad a una tabla existente, SQL Server comprueba que las filas ya presentes respeten esta restricción antes de validar su agregación. La gestión de esta restricción queda garantizada por un índice de tipo UNIQUE. Este índice no puede eliminarse con el comando DROP INDEX. Hay que eliminar la restricción por medio de ALTER TABLE. Sintaxis

[CONSTRAINT nombrerestricción] UNIQUE [CLUSTERED |NONCLUSTERED] (nombrecolumna [,...]) [WITH FILLFACTOR=x] [ON grupo_archivos]

Ejemplo La asociación de las columnas DESIGNACION y PRECIOUNIT debe ser única en la tabla ARTICULOS:

Gestión de las claves secundarias mediante el SQL Server Management Studio:

d. REFERENCIAS

Esta restricción traduce la integridad referencial entre una clave externa de una tabla y una clave primaria o secundaria de otra tabla. Sintaxis CONSTRAINT nombre_restricción [FOREIGN KEY (columna[,_])] REFERENCES tabla [ ( columna [ ,... ] ) ] [ ON DELETE { CASCADE | NO ACTION | SET NULL | SET DEFAULT} ] [ ON UPDATE { CASCADE | NO ACTION | SET NULL | SET DEFAULT} ]

La cláusula FOREIGN KEY es obligatoria cuando se utiliza una sintaxis de restricción de tabla para agregar la restricción.

La opción de cascade permite especificar el comportamiento que debe adoptar SQL Server cuando el usuario actualiza o intenta eliminar una columna a la que se hace referencia. Al definir una restricción de referencia a través de las instrucciones CREATE TABLE o ALTER TABLE, es posible especificar las cláusulas ON DELETE y ON UPDATE. NO ACTION Valor por defecto de estas opciones. Permite obtener un comportamiento idéntico al de las versiones anteriores de SQL Server. ON DELETE CASCADE Permite especificar que, en caso de eliminación de una fila a cuya clave principal hagan referencia una o varias filas, también se supriman todas las filas que contengan la clave externa que hace referencia a la clave principal. Por ejemplo, con esta opción, la eliminación de una fila de datos en la tabla de pedidos provoca la eliminación de todas las filas de datos de la tabla lineas_pedido.

ON UPDATE CASCADE Permite pedir a SQL Server que actualice los valores contenidos en las columnas de claves externas cuando el valor de clave principal al que se hace referencia se actualiza. SET NULL Cuando se elimina la fila que corresponde a la clave primaria en la tabla referenciada, la clave externa toma el valor null. SET DEFAULT Cuando se elimina la fila que corresponde a la clave principal en la tabla referenciada, la clave externa toma el valor definido de forma predeterminada en la columna.

Una acción en cascada no es posible en tablas dotadas de un desencadenador instead of.

Está prohibida cualquier referencia circular en los desencadenadores en cascada. La restricción de referencia no crea ningún índice. Se recomienda crearlo a continuación. Aunque SQL Server no limite el número máximo de restricciones de clave externa que puedan definirse para cada tabla, Microsoft recomienda no sobrepasar las 253. Este límite debe respetarse en lo referente al número de claves externas definidas para la tabla y el número de claves externas que hacen referencia a la tabla. Más allá de este límite puede ser interesante revisar el diseño de la base de datos para conseguir un esquema más óptimo. Ejemplo Creación de la clave externa codigo_cat en la tabla ARTICULOS:

Gestión de las claves externas mediante el SQL Server Management Studio:

e. DEFAULT

El valor predeterminado permite precisar el valor que se va a colocar en la columna si al insertar la fila no se indica ningún dato. Los valores predeterminados son especialmente útiles si la columna no acepta valores NULL, ya que garantizan la existencia de un valor.

Siempre hay que recordar que el valor predeterminado se utiliza sólo cuando la instrucción INSERT no indica ningún valor para la columna. No es posible completar o eliminar ningún valor escrito por el usuario. Para realizar este tipo de operaciones, hay que desarrollar un desencadenador de base de datos. Se puede definir un valor predeterminado para todas las columnas con excepción de las columnas de tipo timestamp o de las que poseen un tipo identity. Sintaxis [CONSTRAINT Nombrerestricción] DEFAULT valor [FOR nombrecolumna].

Valor El valor debe ser exactamente del mismo tipo que el valor sobre el cual está definida la columna. Este valor puede ser una constante, una función escalar (como por ejemplo: USER, CURRENT_USER, SESSION_USER, SYSTEM_USER...) o bien el valor NULL. Ejemplo Valor por defecto para el APELLIDO del cliente:

Definición de un valor por defecto desde el SQL Server Management Studio:

f. CHECK

La restricción de validación o restricción CHECK permite definir reglas de validación que establezcan relaciones entre el valor de distintas columnas en una misma fila. Este tipo de restricción también permite garantizar que los datos se introducen o se actualizan en la tabla con el formato correcto. Además, con una restricción CHECK se puede garantizar que el valor presente en la columna pertenece a un intervalo de valores concreto. Sintaxis [CONSTRAINT Nombrerestricción] CHECK [NOT FOR REPLICATION] (expresión booleana)

NOT FOR REPLICATION Permite impedir a la aplicación la restricción en el momento de la duplicación .

La restricción CHECK se asocia automáticamente a la columna especificada en la expresión de la condición.

Ejemplo Implementación del control del precio positivo del artículo:

Gestión de las restricciones CHECK mediante el SQL Server Management Studio:

5. Gestión de los índices Uso de los índices y por qué indexarlos

El objetivo de los índices es permitir un acceso más rápido a la información tanto para las extracciones (SELECT) como para las actualizaciones (INSERT, UPDATE, DELETE), al reducir el tiempo necesario para localizar la fila. No obstante, los índices resultarán costosos en caso de actualización del valor contenido en la columna indexada. Una buena estrategia de indexación debe tener en cuenta estas cuestiones, de las que se pueden deducir dos reglas: 



Es preferible tener demasiado poco índice que demasiados índices. En caso de que haya múltiples índices, las ventanas de acceso a la información quedan anuladas por el tiempo necesario para actualizar los índices. Los índices deben ser lo más "grandes" posible para que puedan servir para múltiples consultas.

En definitiva, hay que asegurarse de que las consultas utilicen bien los índices que están definidos. Las consultas también deben estar escritas para manipular la cantidad mínima de datos posible de la forma más explícita posible. Por ejemplo, en caso de una proyección es preferible listar las columnas para las que deba presentarse la información en lugar del carácter genérico *. Para las restricciones es preferible hacer comparaciones entre constantes y el valor contenido en una columna. Por ejemplo, si la tabla de Artículos contiene el precio sin IVA de cada artículo, para extraer la lista de artículos cuyo precio con IVA sea inferior o igual a 100 € será preferible escribir la condición preciosiniva<= 100/1.16 en lugar de preciosiniva*1.16<=100. Con la segunda, el cálculo se efectúa para cada artículo, mientras que en el primero se realiza una sola vez para todos.

En el ejemplo anterior, todos los artículos utilizan un IVA del 16%.

También hay que tener en cuenta el hecho de que los datos se almacenan en los índices y, por tanto, van a ocupar un espacio en disco no desdeñable. El nodo hoja del índice agrupado contiene todos los datos. Para un índice no agrupado, el nodo hoja del índice contiene una referencia directa a la fila de información asociada a la clave del índice. Los demás nodos del índice se utilizan para navegar por éste y llegar de forma muy rápida a la información. Es posible agregar información a nivel de los nodos hoja del índice sin que éste tenga en cuenta estas columnas. Esta técnica resulta práctica cuando la definición del índice cubre las consultas. Pongamos, por ejemplo, que hay que extraer la lista de poblaciones y códigos postales de la tabla de clientes. Para ella se ha definido un índice no agrupado en relación con la columna de códigos postales, y la columna que representa el nombre de la población se ha agregado a nivel de nodo hoja. Así, el índice cubre la consulta capaz de producir el resultado sin necesidad de acceder a la tabla. ¿Qué es un índice?

El concepto de índice no es nada nuevo para nadie. Todos hemos utilizado el índice de un libro para llegar directamente a la página o páginas donde se encuentra la información que buscamos. Tal vez haya acudido usted al índice de este libro para localizar la explicación que está leyendo en estos momentos sobre la palabra clave "índice". Si para el índice sólo aparece un número de página detrás de cada palabra clave, se habla de índice único. Los índices que ofrece SQL Server son muy parecidos al de un libro. Se puede recorrer todo el índice para encontrar todas las informaciones, de la misma forma que se puede leer un libro a partir del índice, en lugar de seguir el orden que ofrece la tabla de contenidos. También es posible utilizar el índice para acceder directamente a una información concreta. Con el fin de garantizar un tiempo homogéneo de acceso a la información, SQL Server estructura la información en forma de árbol alrededor de la propiedad indexada. De esta forma, para encontrar un dato en el índice se localiza primero el primer carácter y después se continúa de forma secuencial hasta hallar la palabra clave buscada. Imagine ahora un libro en el que pueda definir varios índices en función de palabras clave, de temas, de tipos de manipulación que puedan desearse realizar... Esta multiplicidad de índices es la que ofrece SQL Server con la posibilidad de crear distintos índices para una misma tabla. Entre todos los índices del libro, hay uno que estructura al volumen: la tabla de contenidos, que puede verse como una especie de índice temático. De la misma forma, SQL Server

permite estructurar físicamente los datos con respecto a un índice: el índice CLUSTERED o agrupado. ¿Agrupar datos o no agruparlos?

SQL Server ofrece dos tipos de índice: los agrupados (de los que cada tabla puede tener sólo uno como máximo), que reorganizan físicamente la tabla, y los no agrupados. La definición o eliminación de un índice no agrupado carece de influencia alguna en la organización de los datos en la tabla. En cambio, la definición o eliminación de un índice agrupado tiene consecuencias en la estructura de los índices no agrupados. Tabla sin índice agrupado

Si una tabla posee únicamente este tipo de índice, todos los datos se almacenan sin seguir ningún tipo de organización. Esta elección resulta especialmente adecuada cuando:       

la tabla almacena información que espera algún tipo de particionamiento, van a truncarse los datos, se crean o eliminan índices con frecuencia, tiene lugar una carga de datos en bloque, se crean índices después de cargar los datos, y su creación puede realizarse de forma paralela, los datos se modifican (UPDATE) con muy poca frecuencia con el fin de conservar una estructura sólida, se minimiza el espacio en disco utilizado por el índice, lo que permite definir índices con un mínimo coste.

Esta solución da buenos resultados para indexar una tabla que no se encuentre presente en un servidor OLTP (por ejemplo, uno que se dedique al análisis de información). Índices agrupados

En cada tabla se puede definir un índice agrupado y sólo uno. Este tipo de índice permite agrupar físicamente los datos de la tabla según un criterio concreto. Por ejemplo, puede definirse un índice agrupado en función de la clave principal. La creación y la reconstrucción de este tipo de índice resultan costosas para el servidor en tiempo y espacio en disco. Si un índice de este tipo se define para una tabla que ya está llena de valores, su construcción costará tiempo. Además, se tardará aún más en construirlo si ya existen índices no agrupados. Idealmente, y a fin de evitar que el índice agrupado provoque un mantenimiento excesivo, el índice estará definido según una columna que contenga datos estáticos y ocupe un espacio limitado, como, por ejemplo, la clave principal.

La definición de un índice agrupado sobre una columna no estable, como el nombre de una persona o su dirección, conduce irremediablemente a una degradación significativa del rendimiento. Índices no agrupados

Dependiendo de si la tabla en la que están definidos está dotada o no de un índice agrupado, las hojas del índice no agrupado harán referencia de la diferente forma a la fila o filas de información. Si la tabla no posee índice agrupado, el RID (Row IDentifier) de la fila de información se almacena a nivel de las hojas del índice. Este RID corresponde a la dirección física (en el sentido de SQL Server) de la fila. Si, por el contrario, la tabla posee un índice agrupado, en el nivel de la hoja del índice no agrupado está almacenada la clave de la fila de información consultada. Esta clave corresponde al criterio de consulta del índice agrupado. Los índices no agrupados son la mejor opción para definir un índice que cubra una o más consultas. Con este tipo de índice, la consulta encuentra en el índice todos los datos que necesita y evita un acceso inútil a la tabla, ya que solamente manipula el índice. De esta forma, el rendimiento mejorará considerablemente porque el volumen de datos manipulados será muy inferior. Por supuesto, con esta técnica pueden optimizarse todas las consultas, pero éste no será el objetivo porque la existencia de múltiples índices elevaría mucho el mantenimiento. Índice y cálculo de agregado

Al trabajar con tablas voluminosas deben crearse índices para que las consultas se resuelvan a través del índice y no de un barrido de toda la tabla. Este punto es especialmente importante para los cálculos de agregado que requieren una operación de selección antes de efectuar el cálculo. Si un índice permite limitar la selección a efectuar, se obtiene una ganancia de rendimiento. Siguiendo esta óptica de optimización del rendimiento en el acceso a datos, se pueden definir índices sobre las columnas de una vista, aun si el resultado presente en la columna es el resultado de un cálculo de agregado. El índice definido sobre una vista está limitado a 16 columnas y 900 bytes de datos para cada entrada del índice. A diferencia de la inclusión de columnas no indexadas en las hojas del índice, los índices definidos sobre una vista pueden contener el resultado de un cálculo de agregado. a. Creación de un índice

Un índice puede crearse en cualquier momento, tenga datos la tabla o no. Sin embargo, en caso de que haya que importar los datos, es preferible importar los datos en primer lugar y luego definir los índices. En el caso contrario (se definen los índices antes de una importación masiva de datos), es necesario reconstruir los índices con el fin de garantizar una distribución equilibrada de los datos en el índice. Sintaxis

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX nom_índice ON { nombre_tabla | nombre_vista } ( columna [ ASC | DESC ] [ ,...n ]) [INCLUDE (columna[ ,...n])] [ WITH [ PAD_INDEX = {ON | OFF} ] [,FILLFACTOR = x] [,IGNORE_DUP_KEY = {ON | OFF} ] [,DROP_EXISTING = {ON | OFF} ] [,ONLINE = {ON | OFF} ] [,STATISTICS_NORECOMPUTE = {ON | OFF} ] [,SORT_IN_TEMPDB = {ON | OFF} ] [ ON grupo_archivo ]

UNIQUE Especifica que la o las columnas indexadas no podrán tener el mismo valor en diversas filas de la tabla. CLUSTERED o NONCLUSTERED Con un índice CLUSTERED (agrupado), el orden físico de las filas en las páginas de datos es idéntico al orden del índice. Sólo puede haber uno por tabla y debe crearse en primer lugar. El valor predeterminado es NONCLUSTERED. INCLUDE Con esta opción, se puede duplicar la información para incluir una copia de las columnas especificada directamente dentro del índice en forma de parámetro. Esta posibilidad está limitada a los índices no agrupados y los datos se almacenan en el nivel de hoja. En el índice se pueden incluir desde 1 hasta 1.026 columnas de cualquier tipo excepto varchar(max), varbinary(max) y nvarchar(max). La opción INCLUDE permite elaborar índices que cubran la consulta, es decir que la consulta sólo deberá recorrer el índice para encontrar todas sus necesidades. FILLFACTOR = x Especifica el porcentaje de relleno de las páginas del índice a nivel de hoja. Esto permite mejorar el rendimiento, pues evita tener valores de índice consecutivos que ya no serían físicamente contiguos. El valor predeterminado es 0 o determinado por sp_configure . Para x = 0, el nivel hoja se rellena al 100%, se reserva espacio a nivel no hoja. Para x entre 1 y 99, el porcentaje de relleno a nivel hoja es x%; se reserva espacio a nivel no hoja. Para x = 100, se rellenan los niveles hoja y no hoja. PAD_INDEX Especifica la tasa de relleno de las páginas a nivel no hoja del índice. Esta opción sólo se puede utilizar con FILLFACTOR, cuyo valor se recupera.

grupo_archivo Grupo de archivos sobre el cual se crea el índice.

En este nivel no se tienen en cuenta los índices particionados. Los elementos de sintaxis sólo conciernen a los índices definidos en su totalidad sobre un único grupo de archivos.

IGNORE_DUP_KEY Esta opción autoriza entradas duplicadas en los índices únicos. Si se activa esta opción, se generará una advertencia al insertar un duplicado y SQL Server ignorará la inserción de fila. En el caso contrario se genera un error. DROP_EXISTING Especifica que el índice ya existente debe ser eliminado. ONLINE Cuando está activada (ON) esta opción durante la construcción del índice, los datos de la tabla permanecen accesibles en lectura y en modificación durante la construcción del índice. Esta opción está disponible a partir de SQL Server 2005 y está desactivada de manera predeterminada. STATISTICS_NORECOMPUTE Las estadísticas de índices caducadas no se recalculan; será necesario utilizar el comando UPDATE STATISTICS para actualizar dichas estadísticas. Si el servidor sobre el que se ejecuta SQL Server 2005 posee varios procesadores, la creación de índices puede efectuarse de forma paralela con el fin de ganar tiempo en la construcción de índices en tablas de grandes dimensiones. La implantación de un plan de ejecución paralela para la construcción de un índice tiene en cuenta el número de procesadores del servidor, la opción de configuración max degree of parallelism (sp_configure) y el número de procesadores que no están ya demasiado cargados por threads de SQL Server. Ejemplo Creación de un índice agrupado:

Creación de un índice agrupado sobre datos ya ordenados:

b. Eliminación de un índice

Únicamente los índices definidos con la instrucción CREATE INDEX pueden ser eliminados con DROP INDEX. Un índice puede ser suprimido cuando su presencia no permita mejorar el rendimiento significativo en comparación con el coste de mantenimiento. Si la eliminación interviene en el marco de una reconstrucción del índice, entonces es preferible activar la opción DROP_EXISTING de la instrucción CREATE INDEX porque ofrece mejores prestaciones. Sintaxis DROP INDEX nombre_indice ON nombre_tabla;

La antigua sintaxis DROP INDEX nombreTabla.nombreIndice se mantiene por razones de compatibilidad pero no se debe utilizar en los nuevos desarrollos. c. Reconstruir un índice

El comando DBCC DBREINDEX sigue estando disponible por motivos de compatibilidad. Es preferible utilizar el comando ALTER INDEX para el mantenimiento de los índices. El comando ALTER INDEX permite reconstruir un índice concreto o todos los índices asociados a una tabla. En el momento de la reconstrucción, se puede especificar el factor de relleno de las hojas. Syntaxis ALTER INDEX { nombre_indice | ALL } ON nombre_tabla REBUILD [WITH]( [PAD_INDEX = { ON | OFF },] [FILLFACTOR = x ,] [SORT_IN_TEMPDB = { ON | OFF },] [IGNORE_DUP_KEY = { ON | OFF },] [STATISTICS_NORECOMPUTE = { ON | OFF }] [ONLINE = { ON | OFF },]] [;]

FILLFACTOR Permite especificar el porcentaje de relleno de las páginas a nivel de hoja del índice.

PAD_INDEX Permite aplicar a las páginas del índice a nivel de no hoja el mismo factor de relleno que se ha especificado a través de FILLFACTOR. Las otras opciones de la instrucción ALTER INDEX poseen el mismo significado que las utilizadas con la instrucción CREATE INDEX. Hay que destacar que la opción ONLINE adquiere todo su sentido en este caso concreto porque permite reconstruir el índice mientras los usuarios trabajan con los datos de la tabla subyacente. Es cierto que las operaciones serán más lentas pero no se bloquearán. Si la reconstrucción del índice se planifica para un momento de poca utilización del servidor puede incluso pasar desapercibida para los usuarios que trabajen en la base en ese momento. El siguiente ejemplo ilustra la reconstrucción de todos los índices de una tabla:

Este segundo ejemplo ilustra la reconstrucción de un índice especificando un valor para la opción FILLFACTOR, así como la aplicación del factor de relleno a nivel de no hojas.

d. Actualización de las estadísticas

SQL Server utiliza datos sobre la distribución de los valores de claves, para optimizar las consultas. Estos datos deben ser actualizados después de las modificaciones importantes de datos. Aunque se describa aquí el procedimiento manual de creación y actualización de estadísticas, es muy recomendable configurar la base de datos para que las estadísticas se creen y actualicen automáticamente. De hecho, en muchos casos la degradación del rendimiento de un servidor se debe, al menos en parte, a que sus estadísticas no están actualizadas. Sintaxis UPDATE STATISTICS nombreTabla [,nombreÍndice] [WITH {FULLSCAN|SAMPLE n {PERCENT|ROWS}|RESAMPLE}]

Si se omite el nombre de índice, se tienen en cuenta todos los índices. FULLSCAN Las estadísticas se crean a partir de un barrido completo de un 100% de las filas de la tabla. SAMPLE n{PERCENT|ROWS}

Las estadísticas se establecen a partir de una muestra representativa de los datos de la tabla. Esta muestra puede expresarse en forma de porcentaje o de número de filas. Si el tamaño de la muestra no es suficiente, SQL Server corrige por sí mismo el tamaño para garantizar que ha recorrido alrededor de mil páginas de datos. Éste es el modo predeterminado de muestreo de estadísticas. RESAMPLE Permite redefinir las estadísticas a partir de un nuevo muestreo. Las estadísticas también pueden actualizarse de forma automática. Esta opción debe ser definida en el momento de la construcción de la base, por medio del comando ALTER DATABASE, o bien utilizando el procedimiento almacenado sp_autostats.

El procedimiento sp_createstats permite definir estadísticas sobre todos los índices de datos de usuario de la base en una sola operación.

Configuración de la base para una actualización automática de las estadísticas de índice:

En modo automático, es el motor el que se encarga de calcular las estadísticas que faltan, de mantener al día las estadísticas en función de las operaciones realizadas sobre los datos y también de suprimir las estadísticas inútiles. Es posible saber si una base está configurada en generación automática de las estadísticas consultando la columna is_auto_update_stats_on de la tabla sys.databases o bien visualizando el valor de la propiedad IsAutoUpdateStatistics de la función databasepropertyex.

Creación de las estadísticas de todos los índices sobre todos los datos no de sistema de la base de datos GESCOM:

e. Información sobre los índices

Se puede obtener información correspondiente a la estructura de los índices a través de los procedimientos almacenados sp_help o sp_helpindex.

La instrucción DBCC SHOWCONTIG se ha mantenido únicamente por motivos de compatibilidad. Se recomienda no utilizarla.

Se pueden obtener datos sobre el tamaño y la fragmentación de las tablas e índices por medio de la función sys.dm_db_index_physical_stats. Syntaxis dm_db_index_physical_stats(id_base | NULL, id_Objeto | NULL, id_índice | NULL | 0, número_partición | NULL , modo | NULL | DEFAULT)

id_base Identificador de la base de datos. Es posible utilizar la función db_id() para conocer el identificador de la base de datos actual. El valor NULL toma en cuenta todas las bases de datos definidas en el servidor e implica el uso del valor NULL como identificador del objeto, el índice y la partición. id_Objeto Identificador del objeto (tabla o vista) sobre el que se desea obtener información. Este identificador puede obtenerse llamando a la función object_id(). El uso del valor NULL permite indicar que se desea información sobre todas las tablas y vistas de la base de datos actual. Ello también implica que se usará el valor NULL para la opción id_índice y número_partición. id_índice Identificador del índice a analizar. Si se especifica el valor NULL, el análisis se aplica a todos los índices de la tabla. número_partición Número de la partición en cuestión. Si se especifica el valor NULL, se tendrán en cuenta todas las particiones. modo Precisa el modo de obtención de la información: DEFAULT, NULL, LIMITED o DETAILED. El valor predeterminado (NULL) corresponde a LIMITED. El procedimiento almacenado sp_spaceused permite conocer el espacio en disco utilizado por el índice.

La función INDEXPROPERTY permite obtener las distintas informaciones relativas a los índices.

6. Seguimiento y verificación de las bases y de los objetos Tras la creación y el uso de las tablas y de los índices, en ocasiones es útil verificar la coherencia de los datos y de las páginas. La instrucción DBCC lo permite. DBCC CHECKDB [(nombrebase[,NOINDEX])]

Verifica en todas las tablas de la base el enlace entre páginas de datos y de índice, los criterios de ordenación y el puntero. También se aportan datos sobre el espacio en disco del registro de transacciones. DBCC CHECKTABLE (nombretabla[,NOINDEX|identificación índice])

Esta instrucción realiza un trabajo similar a DBCC CHECKDB pero para una sola tabla. Si se proporciona el identificador de índice, sólo se verifica éste; si se especifica NOINDEX, los índices no se verifican. DBCC CHECKFILEGROUP permite efectuar estas mismas comprobaciones sobre un grupo de archivos concreto.

Generalidades Microsoft Transact-SQL es un lenguaje de consultas mejorado respecto al SQL, en el cual se basa. El SQL (Structured Query Language) es el lenguaje estándar, creado por IBM en los años setenta para la gestión de los SGBDR (Sistemas de Gestión de Bases de Datos Relacionales). Tres categorías de instrucciones componen este lenguaje: 





El Lenguaje de Definición de Datos (Data Description Language - DDL ), que permite la creación, modificación y supresión de los objetos SQL (TABLES, INDEX, VIEWS, PROCEDURES, etc.). El Lenguaje de Manipulación de Datos (Data Manipulation Language - DML ), que proporciona las instrucciones de creación, actualización, supresión y extracción de los datos almacenados. El Lenguaje de Control de Acceso (Data Control Language - DCL) para la gestión del acceso a los datos, las transacciones y la configuración de las sesiones y de las bases.

Además, Transact-SQL se ocupa de las funcionalidades procedurales, como la gestión de las variables, las estructuras de control de flujo, los cursores y los lotes de instrucciones. Es,

pues, un lenguaje completo que cuenta con instrucciones, manipula objetos SQL, admite la programación y utiliza expresiones. Con ayuda de Transact SQL, es posible definir funciones y procedimientos que se ejecutan directamente sobre el servidor de la base de datos. Este tipo de procedimientos y funciones son particularmente interesantes cuando el tratamiento necesita de la manipulación de un volumen de datos elevado para obtener el resultado. De igual manera, el desarrollo en Transact SQL está perfectamente adaptado a un contexto de funcionalidades compartidas porque los procedimientos y las funciones alojadas en el servidor pueden ser ejecutadas desde cualquier entorno cliente (.Net, Java...). Desde SQL Server 2008 es posible, pero no obligatorio, utilizar el punto y coma como marcador de final de instrucción.

1. Expresiones En la mayoría de las sintaxis Transact-SQL, se pueden utilizar expresiones o combinaciones de expresiones para gestionar valores o para sacar partido de la capacidad de programación del lenguaje. Las expresiones pueden tomar diferentes formas. Constantes

Ejemplo Carácter

’QWERTY’, ’Escuela Nacional de Informática’ 10, -15.26, 1.235e-5

Numérico Fecha constante fecha hora ’801215’ 5 Diciembre 1980 00:00:00:000 ’15/12/1980’ idem idem ’15-12-80 8:30’ idem 8:30:00:000 ’8:30:2’ 1 Enero 1900 08:30:02:000 ’15.12.1980 8:30pm’ 15 Diciembre 1980 20:30:00:000 Binario 0x05, 0xFF, 0x5aef1b Nulo NULL

Nombres de columna

Un nombre de columna puede emplearse como expresión; el valor de la expresión es el valor "almacenado" de la columna. Funciones

Se puede utilizar como expresión cualquier función; el valor de la expresión es el resultado devuelto por la función. Ejemplo

expresión SQRT(9) substring(’ABCDEF’,2,3)

valor 3 ’BCD’

Variables

Las variables pueden emplearse como expresión o, en una expresión, con la forma @nombre_de_variable o @@nombre_de_variable. El valor de la expresión es el valor de la variable. Ejemplo

Subconsultas

Una consulta SELECT entre paréntesis puede emplearse como expresión y tener como valor el resultado de la consulta, ya sea un valor único o un conjunto de valores. Ejemplo Guardar el número de clientes en una variable:

Expresiones booleanas

Están destinadas a comprobar condiciones (IF, WHILE, WHERE, etc.). Estas expresiones se componen de la manera siguiente: expresión1 operador expresión2

2. Operadores Los operadores permitirán constituir expresiones calculadas, expresiones booleanas o combinaciones de expresiones. Operadores aritméticos

Permiten efectuar cálculos elementales y devolver un resultado. + * / % (...)

Suma Resta Multiplicación División Módulo (resto de la división entera) Paréntesis

Ejemplo

Los operadores + y - funcionan también sobre las fechas. Manipulación de cadenas de caracteres

La concatenación permite crear una sola cadena de caracteres a partir de varias expresiones de tipo carácter. El operador de concatenación es el signo más (+). Ejemplo

Operadores de bit

Permiten el cálculo entre enteros, convertidos implícitamente en valores binarios. & | ^ ~

Y O O exclusivo NO

Operadores de comparación

Permiten la creación de expresiones booleanas comparando expresiones. Estas expresiones pueden ir situadas entre paréntesis. exp1 = exp2 exp1 > exp2 exp1 >= exp2 o exp1!<exp2 exp1 < exp2 exp1 <= exp2 o exp1!>exp2 exp1 <> exp2 o exp1!=exp2 exp IN (exp1, exp2...) exp IS NULL exp LIKE

Igual. Mayor. Mayor o igual. Menor. Menor o igual. Distinto. Compara con cada expresión de la lista. Test del valor NULL. Para comprobar si una variable contiene el valor NULL es indispensable utilizar el operador IS NULL. Filtra la cadena de caracteres o la fecha según la máscara

‟máscara‟

especificada.

La máscara puede estar compuesta por: _ % [ab...] [a-z] [^ab...] ab...

Un carácter cualquiera. n caracteres cualesquiera. Un carácter en la lista ab... Un carácter en el intervalo az. Un carácter fuera de la lista o del intervalo especificado. El propio carácter.

Ejemplo máscara ’G%’ ’_X%1’ ’%[1-9]’ ’[^XW]%’ ’EL[_]%’

Cadenas correspondientes que empiece por "G" segundo carácter "X", último "1" que termina con una cifra que no empiece ni por X ni por W que empiece por "EL_"

exp BETWEEN min AND max Busca en el intervalo compuesto por los valores min y max (límites incluidos). EXISTS (subconsulta) Devuelve VERDADERO si la subconsulta envía al menos una línea. Operadores lógicos

Permiten combinar expresiones booleanas (expb) devolviendo un valor booleano. expb1 OR expb2 VERDADERO si una de las dos expresiones es verdadera. expb1 AND expb2 VERDADERO si las dos expresiones son verdaderas. NOT expb VERDADERO si expb es falsa.

3. Funciones Existen numerosas funciones para calcular columnas o efectuar tests. Se proporcionarán ejemplos con la instrucción SELECT. Las funciones estándar de SQL Server pueden agruparse por tipos: rowset, agregación, ranking, escalar. Como este último tipo contiene un gran número de funciones, debe subdividirse por categorías: matemáticas, cadena de caracteres, fecha, etc.

Ciertas funciones, sobre todo las que trabajan con datos de tipo carácter, toman en cuenta la clasificación definida a nivel del servidor. Funciones de agregación

Estas funciones devuelven un valor único resultado de un cálculo estadístico sobre una selección de líneas. Las funciones de agregado son deterministas, es decir que aplicadas a un mismo conjunto de datos devuelven siempre el mismo resultado. A excepción de la función COUNT, las funciones de agregado no tienen en cuenta los valores nulos. COUNT(*) Cuenta las líneas seleccionadas. COUNT([ALL|DISTINCT] expr) Cuenta todas las expresiones no nulas (ALL) o las expresiones no nulas únicas (DISTINCT). COUNT_BIG Su funcionamiento es idéntico al de la función COUNT, pero su resultado es de formato bigint y no int, como el de COUNT. SUM([ALL|DISTINCT] exprn) Suma de todas las expresiones no nulas (ALL) o de las expresiones no nulas únicas (DISTINCT). AVG([ALL|DISTINCT] exprn) Promedio de todas las expresiones no nulas (ALL) o las expresiones no nulas únicas (DISTINCT). MIN(exp) o MAX(exp) Valor mínimo o máximo de todas las expresiones. STDEV ([ALL|DISTINCT] exprn) Desviación típica de todos los valores de la expresión dada.

STDEVP([ALL|DISTINCT] exprn) Desviación típica de la población para todos los valores de la expresión dada. VAR([ALL|DISTINCT] exprn) Varianza de todos los valores de la expresión dada. VARP([ALL|DISTINCT] exprn) Varianza de la población para todos los valores de la expresión dada. GROUPING Se utiliza conjuntamente con ROLLUP y CUBE . Indica el valor 1 cuando la fila ha sido generada por una instrucción ROLLUP o CUBE; si no, indica el valor 0. CHECKSUM (*|exp[,...]) Permite calcular un código de control a partir de una fila de la tabla o a una lista de expresiones (por ejemplo, varias columnas). Esta función permite producir un código de hash. CHECKSUM_AGG([ALL|DISTINCT]exp) Permite calcular un valor de hash a partir de un grupo de datos. Este código de control permite saber rápidamente si un grupo de datos ha sufrido modificaciones; si las ha habido, el resultado de la función ya no será el mismo. Funciones matemáticas

Estas funciones devuelven un valor resultante de cálculos matemáticos "clásicos" (álgebra, trigonometría, logaritmos, etc.). ABS(expn) Valor absoluto de expn. CEILING(expn) Menor entero superior o igual a expn. FLOOR(expn) Mayor entero inferior o igual a expn.

SIGN(expn) Devuelve 1 si expn es positiva, -1 si es negativa y 0 si es igual a cero. SQRT(expn) raíz cuadrada de expn. POWER(expn,n) expn elevado a n. SQUARE(expn) Cálculo del cuadrado de expn. Funciones trigonométricas

PI() Valor del número PI. DEGREES (expn) Conversión a grados de expn en radianes. RADIANS(expn) Conversión a radianes de expn en grados. SIN(expn), TAN(expn), COS(expn), COT(expn) Seno, tangente, coseno y cotangente del ángulo expn en radianes. ACOS(expn), ASIN(expn), ATAN(expn) Arco coseno, arcoseno y arcotangente de expn. ATN2(expn1,expn2) Ángulo cuya tangente se encuentra en el intervalo expn1, expn2. Funciones logarítmicas

EXP(expn)

Exponencial de expn. LOG(expn) Logaritmo neperiano de expn. LOG10(expn) Logaritmo en base 10 de expn. Funciones diversas

RAND([expn]) Número aleatorio comprendido entre 0 y 1. Expn representa el valor de partida. ROUND(expn,n[,f]) Redondea expn a la precisión n. Si n es positivo, n representa el número de decimales. Si n es igual a cero, redondea al entero más cercano. Si n es negativo, redondea a la decena más cercana (-1), a la centena (-2), etc., o devuelve 0 si n es superior al número de cifras enteras de expn. Si se especifica f, su función es truncar expn. Los valores que puede tomar f se interpretan como para n. El valor de f sólo se tendrá en cuenta si n es igual a 0. Ejemplo expn 1.256 1.256 1.256 11.25 11.25 11.25 150.75 150.75

n 2 4 0 -1 -2 -3 0 0

f

1

resultado 1.260 1.256 1.000 10 .00 .00 151 150

Funciones fecha

Las funciones de fecha manipulan expresiones de tipo DATETIME y utilizan formatos que representan la parte de la fecha que se debe gestionar. Estos formatos son: Formato year quarter month day of year

Abreviatura yy qq mm dy

Significado Año (de 1753 a 9999) Trimestre (1 a 4) Mes (1 a 12) Día del año (1 a 366)

day weekday hour minute seconds millisecond

dd dw hh mi ss ms

Día del mes (1 a 31) Día de la semana (1 lunes a 7 domingo) Hora (0 a 23) Minuto (0 a 59) Segundo (0 a 59) Milisegundo (0 a 999)

GETDATE() Fecha y hora del sistema. DATENAME (formato,expd) Devuelve la parte de fecha en forma de texto. DATEPART (formato,expd) Devuelve el valor de la parte de fecha según el formato. Es posible configurar el primer día de la semana por medio de la función SET DATEFIRST (número_día). Los días se numeran desde el 1 para el lunes hasta el 7 para el domingo. Se puede conocer la configuración actual interrogando a la función @@datefirst .

DATEDIFF (formato,expd1,expd2) Diferencia según el formato entre las dos fechas. DATEADD (formato,n,expd) Añade n formato a la fecha expd. DAY(expd) Devuelve el número del día del mes. MONTH(expd) Devuelve el número del mes. YEAR(expd) Devuelve el año.

Si el parámetro que se pasa a estas tres funciones es el valor 0, SQL Server efectúa sus cálculos sobre el 1 de enero de 1900.

SWITCHOFFSET (datetimeoffset, zonaHoraria) Convierte el dato de tipo DATETIMEOFFSET del parámetro hacia la zona horaria indicada en el segundo parámetro. SYSDATETIME Devuelve la fecha y la hora actuales del servidor en formato datetime2. La diferencia en relación con la hora GMT (OFFSET) no está incluida. Esta función ofrece más precisión que el valor devuelto por getdate o getutcdate. SYSDATETIMEOFFSET Funcionamiento similar a SYSDATETIME pero el dato de tipo fecha y hora devuelto está en formato datetimeoffset e incluye por tanto la diferencia con respecto a la hora GMT. Funciones de cadena de caracteres

ASCII (expc) Valor del código ASCII del primer carácter de expc. UNICODE(expc) Valor numérico correspondiente al código de carácter unicode de expc. CHAR(expn) Carácter correspondiente al código ASCII expn. NCHAR(expn) Carácter unicode correspondiente al código numérico expn. LTRIM(expc), RTRIM(expc) Suprime los espacios no significativos a la derecha (RTRIM) o a la izquierda (LTRIM) de expc. STR(expn,[lg[,nbd]])

Convierte el número expn a cadena de longitud total lg con los nbd caracteres a la derecha de la marca decimal. SPACE(n) Devuelve n espacios. REPLICATE (expc,n) Devuelve n veces expc. CHARINDEX (‟máscara‟,expc),PATINDEX(‟%máscara%‟,expc) Devuelve la posición de partida de la primera expresión ‟máscara‟ en expc. PATINDEX permite utilizar caracteres genéricos (ver LIKE) y trabajar con los tipos text, char y varchar. LOWER(expc),UPPER(expc) Cambio entre mayúsculas y minúsculas. Convierte expc a minúsculas o mayúsculas. REVERSE(expc) Devuelve expc al revés (leído de derecha a izquierda). RIGHT(expc,n) Devuelve los n caracteres más a la derecha de expc. LEFT(expc,n) Devuelve los n caracteres más a la izquierda de expc. SUBSTRING(expc,dp,lg) Devuelve lg caracteres de expc a partir de dp. STUFF(expc1,dp,lg,expc2) Elimina lg caracteres de expc1 a partir de dp, y luego inserta expc2 en la posición dp. SOUNDEX(expc) Devuelve el código fonético de expc. Este código se compone de la primera letra de expc y de tres cifras. DIFFERENCE(expc1,expc2)

Compara los SOUNDEX de las dos expresiones. Devuelve un valor de 1 a 4; 4 significa que las dos expresiones ofrecen la mayor similitud. LEN(expc) Devuelve el número de caracteres de expc. QUOTENAME(expc[,delimitador]) Permite transformar expc en un identificador válido para SQL Server. REPLACE(expc1, expc2, expc3) Permite reemplazar en expc1 todas las ocurrencias de expc2 por expc3. Funciones de sistema

COALESCE(exp1,exp2...) Devuelve la primera exp no NULL. COL_LENGTH (‟nombre_tabla‟,‟nombre_columna‟) Longitud de la columna. COL_NAME (id_tabla,id_col) Nombre de la columna cuyo número de identificación es id_col en la tabla identificada por id_table. DATALENGTH (exp) Longitud en bytes de la expresión. DB_ID ([‟nombre_base‟]) Número de identificación de la base de datos. DB_NAME ([id_base]) Nombre de la base identificada por id_base. GETANSI NULL ([‟nombre_base‟]) Devuelve 1 si la opción "ANSI NULL DEFAULT" está activada para la base de datos.

HOST_ID() Número de identificación de la estación de trabajo. HOST_NAME() Nombre de la estación de trabajo. IDENT_INCR (‟nombre_tabla‟) Valor del incremento definido para la columna IDENTITY de la tabla o de la vista que afecta a una tabla con una columna IDENTITY. IDENT_SEED (‟nombre_tabla‟) Valor inicial definido para la columna IDENTITY de la tabla o de la vista sobre una tabla con una columna IDENTITY. IDENT_CURRENT (‟nombre_tabla‟) Devuelve el último valor de tipo identidad utilizado por esta tabla. INDEX_COL (‟nombre_tabla‟, id_índice, id_clave) Nombre de la columna indexada correspondiente al índice. ISDATE(exp) Devuelve 1 si la expresión de tipo varchar tiene un formato de fecha válido. ISNULL (exp,valor) Devuelve valor si exp es NULL. ISNUMERIC(exp) Devuelve 1 si la expresión de tipo varchar tiene un formato numérico válido. NULLIF (exp1,exp2) Devuelve NULL si exp1 = exp2. OBJECT_ID (‟nombre‟) Número de identificación del objeto ‟nombre‟.

OBJECT_NAME (id) Nombre del objeto identificado por id. STATS_DATE (id_tabla,id_índice) Fecha de la última actualización del índice. SUSER_ID([‟nombre_acceso‟]) Número de identificación correspondiente al nombre de acceso. SUSER_NAME ([id]) Nombre de acceso identificado por id. USER_NAME ([id]) Nombre del usuario identificado por id. Sólo se debe utilizar con la restricción DEFAULT (funciones niládicas). CURRENT_TIMESTAMP Fecha y hora del sistema, equivalente a GETDATE(). SYSTEM_USER Nombre de acceso. CURRENT_USER USER SESSION_USER Nombre del usuario de la sesión. OBJECT-PROPERTY(id,propiedad) Permite recuperar las propiedades de un objeto de la base de datos. ROW_NUMBER Devuelve el número secuencial de una fila de una partición de un conjunto de resultados, comenzando con 1 para la primera fila de cada partición. RANK Permite conocer el rango de una fila de una partición de un conjunto de resultados. El rango de una fila es superior en una unidad al rango de la fila de misma partición.

DENSE_RANK Funciona como RANK, pero sólo se aplica a las filas presentes en el conjunto de resultados. HAS_DBACCESS („nombreBase‟) Permite saber si, con el contexto de seguridad actual, es posible acceder a la base de datos del parámetro. Devuelve valor 1 en caso afirmativo y 0 en caso negativo. HAS_PERMS_BY_NAME Permite saber por programación si se dispone de un privilegio o no. Este tipo de función puede resultar interesante en el caso de un cambio de contexto. KILL Esta función, bien conocida por los usuarios de los sistemas Unix/Linux, permite poner fin a una sesión de usuario. Para poner fin a una conexión, es necesario pasar el parámetro del identificador de sesión (sessionID o SPID) o bien el identificador de lote actualmente en ejecución (UOW - Unit of Work). Se puede saber el UOW consultando la columna request_owner_grid de la vista sys.dm_tran_locks. El identificador de sesión se puede obtener consultando el contenido de la variable @@SPID, ejecutando el procedimiento sp_who o bien consultando la columna session_id de vistas como sys.dm_tran_locks o sys_dm_exec_sessions. Si la cancelación se realiza sobre una transacción de gran envergadura, entonces la operación puede ser relativamente larga. NEWID() Permite generar un valor de tipo UniqueIdentifier. NEWSEQUENTIALID() Esta función, destinada a ser utilizada únicamente para generar un valor predeterminado, permite generar el siguiente valor de tipo UniqueIdentifier. Puesto que el siguiente valor de tipo UniqueIdentifier es predecible, esta función sólo debe ser utilizada en las estructuras que necesiten un nivel de seguridad elevado. PARSENAME („nombreObjeto‟,parteQueExtraer) Permite extraer a partir de un nombre completo de objeto el nombre del objeto (parteQueExtraer=1), el nombre del esquema (parteQueExtraer=2), el nombre de la base de datos (parteQueExtraer=3) y el nombre del servidor (parteQueExtraer=4). PUBLISHINGSERVERNAME

Devuelve el nombre del publicador que publica una base de datos. STUFF (cadena1), dp, lg, cadena2) Elimina una cantidad especificada de caracteres (lg) de la cadena1 e inserta otro conjunto de caracteres (cadena2) en el punto inicial especificado (dp). Funciones de conversión de tipo

CAST (expresión AS tipo_dato) Permite convertir un valor al tipo especificado. CONVERT (tipo_dato,exp[,estilo]) Conversión de la expresión al tipo especificado. Puede utilizarse un estilo en las conversiones de fecha u hora. Sin el siglo 1 2 3 4 5 6 7 8 10 11 12

Con el siglo 0 o 100 101 102 103 104 105 106 107 108 9 o 109 110 111 112

defecto USA ANSI inglés/francés alemán italiano defecto USA Japón ISO

-

13 o 113

europeo

14

114

-

20 o 120

-

21 o 121

-

126 130

ODBC canónico ODBC canónico ISO8601 Hijri

Estándar

Salida (mes/mmm)dd aaaa hh:mm AM (o PM) mm/dd/aa aa.mm.dd dd/mm/aa dd.mm.aa dd-mm-aa dd mmm aa mmm dd,a hh:mm:ss (mes/mmm)dd aaaa hh:mm:ss:mmm AM mm-dd-aa aa/mm/dd aammdd dd(mes/mmm)aaaa hh:mm:ss:mmm(24h) hh:mm:ss:mmm (24h) aaaa-mm-dd hh:mi:ss(24h) yyyy-mm-dd hh:mi:ss.mmm(24h) aaaa-mm-jjThh:mi:ss.mmm dd mes aaaa hh:mi:ss:mmmAM

-

131

Hijri

dd/mm/aa hh:mi:ss:mmmAM

En caso de que se introduzca el año con dos caracteres, SQL utiliza 2049 como año de referencia, es decir, que para todo valor comprendido entre 0 y 49 SQL Server considera que se trata de un año del siglo XXI, mientras que para todo valor comprendido entre 50 y 99, SQL Server considera que se trata de un

SQL-DML El lenguaje de manipulación de datos (SQL-Data Manipulation Language) se compone de instrucciones que permiten la gestión, visualización y extracción de las filas de las tablas y de las vistas.

1. Creación de filas La creación de filas en una tabla, o en una vista según ciertas condiciones (ver más adelante), se lleva a cabo con el comando INSERT. Las restricciones serán controladas, y los triggers serán activados en el momento en que se ejecute el comando. La forma "INSERT ..... VALUES ....." crea una sola fila, mientras que "INSERT ..... SELECT ....." permite crear eventualmente varias filas. Sintaxis INSERT [INTO] nombre_objeto [(col,...)]{DEFAULT VALUES| VALUES (val,...)|consulta|EXECUTE procedimiento}

nombre_objeto Nombre válido de tabla o de vista. (col,...) Lista de las columnas a las que se asignarán valores. Las columnas no citadas tomarán el valor NULL. Si se omite la lista, todas las columnas deberán estar asociadas a un valor. DEFAULT VALUES Todas las columnas citadas tomarán sus valores por defecto o NULL si no tienen. (val,...)

Lista de valores compuesta de expresiones constantes, de las palabras clave NULL o DEFAULT, o de variables. Debe haber tantos valores como columnas a las que dar valores, del mismo tipo y en el mismo orden. consulta Instrucción SELECT que devuelve tantos valores, en el mismo orden y del mismo tipo que las columnas a las que dar valor. Esta forma de sintaxis permite insertar varias filas en una sola operación. procedimiento Nombre de un procedimiento almacenado local o remoto. Sólo los valores de las columnas devueltas por una orden SELECT contenida en el procedimiento darán valores a las columnas afectadas por el INSERT. Ejemplo Creación de un cliente (asignación de valor a todas las columnas, valor por defecto para ciudad, NULL para el teléfono:

Creación de un artículo (precio desconocido):

Creación de artículos en stock en el almacén P2 a partir de la tabla ARTICULOS:

Ejecución de un procedimiento que recupera los nombres de las tablas de un usuario identificado por su número:

Aplicación de este procedimiento para dar valores a una tabla temporal, con la información sobre las tablas cuyo propietario es "dbo":

Inserción de filas forzando el valor para una columna de tipo Identity.

Cuando la instrucción INSERT está asociada a una consulta SELECT, es posible limitar el número de filas insertadas mediante la cláusula TOP.

La instrucción INSERT permite insertar varias filas de datos de manera simultánea cuando está asociada a una cláusula SELECT, pero también es posible añadir varias filas precisando directamente los valores de los datos que se desean insertar. Para ello, se especifican entre paréntesis tras la cláusula VALUES los datos relativos a cada fila que se inserta y cada conjunto de valores se separa mediante una coma. Sintaxis INSERT INTO nombre_tabla[(col, ...] VALUES((valorFila1, ...),(valorFila2, ...);

nombre_tabla Nombre de la tabla afectada por la operación de inserción de filas. (col, ...) Nombre de las distintas columnas de la tabla para las que se han provisto valores. El orden de las columnas definido en este punto define asimismo el orden en que se deben suministrar los valores. (valorFila1, ...) Valores de los datos que se desean añadir a la tabla. Cada conjunto de valores debe suministrar un dato para cada columna especificada en la lista de columnas. Ejemplo En el ejemplo siguiente se han introducido dos valores en la tabla categorías mediante una sola instrucción INSERT:

2. Modificación de filas La modificación de los valores de las columnas de filas existentes se efectúa por la instrucción UPDATE. Esta instrucción puede actualizar varias columnas de varias filas de una tabla a partir de expresiones o de valores de otras tablas. Sintaxis UPDATE nombre_objeto SET col=exp[,...]

[FROM nombre_objeto[,...]][WHERE cond]

nombre_objeto Nombre de tabla o de vista. col Nombre de la columna que se va a actualizar. exp Cualquier tipo de expresión que devuelva un solo valor del mismo tipo que la columna. Se permiten las palabras clave DEFAULT y NULL. FROM nombre_objeto Permite actualizar cada fila a partir de los datos de otras tablas o vistas. cond Expresión booleana que permite limitar las filas que se van a actualizar. Ejemplo Modificación de los datos APELLIDO, DIRECCION y CIUDAD del Cliente 10428:

Incremento del 1% de los precios de todos los artículos de menos de 10 €:

Actualización de la cantidad en stock del artículo 000428 en el almacén N1 respecto a todas las líneas de pedido que conciernen a este artículo:

3. Eliminación de filas La instrucción DELETE permite eliminar una o más filas de una tabla o vista, en función de una condición o de los datos de otra tabla. Sintaxis DELETE [FROM] nombre_objeto [FROM nombre_objeto [,...]] [WHERE cond]

nombre_objeto Nombre de tabla o de vista. FROM nombre_objeto Permite utilizar las columnas de otras tablas o vistas en la cláusula WHERE. cond Expresión booleana que permite restringir las filas que se van a eliminar. Ejemplos Intento de eliminación de TODOS los clientes:

La opción ON DELETE, al crear la restricción de referencia, permite eludir este problema.

Eliminación de las facturas saldadas:

Eliminación de las facturas del cliente 1:

4. Extracción de filas La instrucción SELECT permite visualizar los datos almacenados en las bases de datos, efectuar cálculos o transformaciones sobre estos datos, o dar valor a las tablas a partir de otras tablas. Esta instrucción tiene una sintaxis compleja que se puede estudiar en tres partes: 

 

Sintaxis básica, que permite la visualización y las operaciones de álgebra relacional, como las restricciones, las proyecciones, los productos cartesianos, las combinaciones, los cálculos elementales y los cálculos de agregados y las uniones. Sintaxis INTO, que permite la creación de una nueva tabla a partir del resultado de SELECT. Cláusula COMPUTE, que permite la generación de filas que contienen estadísticas. Esta cláusula no es relacional.

Sintaxis básica SELECT [ALL|DISTINCT]{*|lista_expresiones}FROM lista_objeto [WHERE cond][GROUP BY lista_expresiones][HAVING cond][ORDER BY lista_expresión][UNION[ALL]SELECT.....]

ALL Permite la extracción de todas las filas (opción por defecto). DISTINCT No muestra los duplicados, es decir, las filas resultantes idénticas. * Extrae todas las columnas. lista_expresiones Lista compuesta de nombres de columnas, constantes, funciones, expresiones calculadas o de toda combinación de expresiones separadas por comas. Cada expresión podrá ser completada por un título de columna bajo la forma: TITULO = exp o exp ALIAS_COL, para modificar el título por defecto del resultado, que es o nulo o bien el nombre de la columna en la tabla. lista_objeto Lista de tablas, o de vistas separadas por comas, a partir de las cuales los datos se extraerán. Cada nombre de objeto podrá ir seguido de un nombre de alias que permite citar la tabla o la vista con otro nombre. Además, se puede orientar el funcionamiento interno de la consulta colocando directivas de consultas para el optimizador, entre paréntesis, tras cada nombre de objeto (ver anexos).

5. Operaciones de álgebra relacional a. Selección de columnas

La palabra clave SELECT permite introducir una lista de columnas que se extraerán de las tablas en el orden seleccionado o todas las columnas en el orden de creación con * (o nombre_tabla.*). En caso de ambigüedad en los nombres de columnas, se puede utilizar la forma: nombre_tabla.nombre_col. La columna resultante tendrá el mismo nombre que la columna inicial, salvo si se emplea un título o un alias de columna. Ejemplos Visualización de todas las columnas de todas las filas de Artículos:

Selección de dos columnas:

Cambio del título:

Alias de columna:

También es posible renombrar una columna utilizando la palabra clave AS entre el nombre de la columna y su alias en la cláusula SELECT. El resultado observado será el mismo que el presentado en el ejemplo anterior. b. Restricción

La restricción consiste en extraer sólo un cierto número de líneas que respondan a una o más condiciones. La cláusula WHERE permite la implementación de las restricciones. Las condiciones son expresiones booleanas compuestas de nombres de columnas, de constantes, de funciones, de operadores de comparación y de operadores lógicos. Ejemplos Clientes de Barcelona:

Clientes de la provincia de Barcelona:

Clientes de BARCELONA o de ciudad desconocida cuyo apellido empiece por "FE" y cuya cuarta letra del apellido sea "N":

Pedidos realizado en marzo en los últimos tres años:

c. Cálculos elementales

La utilización de expresiones calculadas gracias a operadores aritméticos o a funciones permite obtener nuevas columnas con el resultado de estos cálculos efectuados para cada fila resultante. Ejemplos Simulación de un aumento del 10% de las tarifas:

Visualización de los clientes con apellido y nombre concatenados y el número de la provincia:

d. Proyección

La operación de proyección permite agrupar filas respecto a columnas. El resultado de esta operación permitirá obtener líneas únicas sobre una selección de columnas. La proyección se efectúa mediante la opción DISTINCT o de la cláusula GROUP BY lista_col. La lista de columnas o de expresiones de agrupación debe ser rigurosamente idéntica a la lista de columnas seleccionadas. Aunque estas dos instrucciones permiten obtener un resultado similar, el tratamiento efectuado es completamente diferente. Con la opción DISTINCT los datos se muestran en el orden de extracción de la base y sólo se muestran los datos distintos entre sí. En el caso de la cláusula GROUP BY , se extraen todos los datos de la base y después se agrupan (clasifican) según el criterio de agrupación especificado. De esta manera se constituyen subconjuntos y sólo se muestran las etiquetas de estos subconjuntos. Ejemplo Lista de las ciudades donde residen los clientes:

Agrupación por ciudad y código postal (para ver las ciudades que tienen varios códigos postales):

e. Cálculos de agregados

Los cálculos estadísticos sobre las agrupaciones de líneas o sobre toda una selección se hacen utilizando las funciones estadísticas (COUNT, SUM, AVG, MIN, MAX) y eventualmente la cláusula GROUP BY. La cláusula HAVING permite probar una condición para limitar los grupos de resultados. Ejemplos Precio mínimo, medio y máximo de los artículos:

Cantidad de artículos en stock sumando todos los almacenes:

Número de clientes por provincia, si la provincia contiene más de 4 clientes:

f. Producto cartesiano Sintaxis

Sintaxis común SELECT lista_columna FROM lista_tabla

Sintaxis ANSI SELECT lista_columna FROM nombretabla CROSS JOIN nombretabla [ .... ]

El producto cartesiano permite extraer datos de varias tablas asociando cada fila de cada tabla citada. las tablas afectadas deben ir separadas por comas tras el FROM. Si se quiere citar el mismo nombre de columna en dos tablas distintas, éste debe ir precedido por el nombre de tabla o el nombre de alias. Esta operación puede utilizarse para simulaciones de asociación de datos o para generar un gran número de filas (el número de filas resultantes será el producto del número de filas de cada tabla). Ejemplos Asociación de cada artículo a cada categoría:

Misma consulta en sintaxis ANSI:

g. Combinación

La operación join es una combinación de un producto cartesiano y de una restricción. Permite asociar lógicamente filas de tablas diferentes. Los "join" se usan en general para establecer correspondencias entre los datos de una fila dotada de una clave externa y los datos de la fila con la clave primaria (join natural). Sintaxis

Sintaxis común SELECT lista_columna FROM lista_tabla WHERE nombretabla. nombrecolumna operador nombretabla.nombrecolumna [...]

Sintaxis ANSI SELECT lista_columna FROM nombretabla INNER JOIN nombretabla ON nombretabla.nombrecolumna operador nombretabla. nombrecolumna [...]

Ejemplos Visualización de los datos artículos y categorías:

La misma consulta en sintaxis ANSI:

Visualización del pedido:

h. Combinaciones externas

Cuando no se satisface la condición, ninguna de las filas aparece en el resultado. Las combinaciones externas permiten extraer líneas de una de las dos tablas afectadas aunque la condición sea falsa. En ese caso, los datos de la segunda tabla tienen el valor NULL. La sintaxis utilizada para la condición es: nombre_tabla1 LEFT OUTER JOIN nombre_tabla2 ON nombre_tabla1.col1= nombre_tabla2.col2 o

nombre_tabla1 RIGHT OUTER JOIN nombre_tabla2 ON nombre_tabla1.col1= nombre_tabla2.col2

O Nom_tabla1 FULL OUTER JOIN nom_tabla2 ON nom_tabla1.col1=nom_tabla2.col2

Según si se quiere ver las filas de la primera (LEFT) o de la segunda tabla (RIGHT). La combinación externa completa (FULL OUTER JOIN) permite mostrar los datos obtenidos de estas dos tablas aunque no sea posible establecer correspondencia. Sintaxis SELECT lista_columna FROM nombretabla {LEFT|RIGHT|FULL}OUTER JOIN nombretabla ON nombretabla.nombrecolumna operador nombretabla.nombrecolumna [...]

Ejemplo Lista de los pedidos por cliente. Los clientes que no tienen también aparecen:

SQL Server 2008 ya no soporta la sintaxis SQL Server *= y =* para definir las combinaciones externas, como muestra el ejemplo siguiente:

La lectura del mensaje de error permite comprender el origen del error y ofrece una posibilidad para solucionar el problema. Autocombinación

Es posible asociar filas de una tabla a otras filas de la misma tabla realizando un self-join. Es obligatorio el uso de los alias de tabla para evitar las ambigüedades de sintaxis. Se produce una autocombinación entre dos tablas cuando la condición de combinación corresponde de forma idéntica y se realiza sobre dos columnas que poseen el mismo nombre. i. Order By

La cláusula Order By permite ordenar los datos resultantes de una consulta de tipo SELECT, así como especificar qué columnas se ordenarán. Para cada criterio de ordenación, se debe especificar si se utiliza un orden ascendente (predeterminado) o descendente.

Las columnas se definen por su nombre o bien por su número de orden en la cláusula SELECT. Al manipular la columna por su número de orden, es posible ordenar columnas que presentan el resultado de un cálculo elemental o de agregado. Ejemplo Los clientes se muestran por orden alfabético de su apellido y nombre y por códigos postales.

j. Unión

El operador UNION permite obtener un conjunto de filas proveniente de varias consultas. Todas las consultas deben proporcionar el mismo número de columnas, del mismo tipo. Ejemplo Visualización de las líneas de pedido y de las cantidades en stock:

k. Except

Este operador permite poner en práctica en SQL Server 2005 el operador de diferencia definido en álgebra relacional. Permite localizar las filas de información presentes en un conjunto de resultados no en otro. Esta diferencia sólo se puede encontrar entre dos conjuntos de resultados con la misma estructura, es decir, el mismo número de columnas, definidas en el mismo orden y sobre los mismos tipos de datos para los dos conjuntos de resultados. Ejemplo Presentar la lista de clientes cuyo nombre comience por San y que no estén en la provincia de Barcelona (08).

l. Intersect

Este operador corresponde a la traducción a Transact SQL del operador de intersección. Permite identificar en una sola consulta SQL las filas que están presentes a la vez en dos conjuntos distintos de resultados, pero con la misma estructura.

Igual que con la unión y la diferencia, la intersección sólo se puede realizar entre dos conjuntos de resultados con la misma estructura. Ejemplo Presentar la lista de clientes cuyo nombre comience por San y que estén en la provincia de Barcelona (08).

Existen otros medios para llegar al mismo resultado, pero el objetivo aquí es sencillamente ilustrar el operador INTERSECT. m. Extraer solamente las primeras filas

La cláusula TOP permite extraer solamente las primeras filas de un conjunto de resultados. Esta cláusula está disponible para las operaciones SELECT, INSERT, UPDATE y DELETE. Sintaxis SELECT TOP (número)[PERCENT][WITH TIES] listaColumna FROM listaTablas...

número Representa el número de filas que se devuelven. Se trata de un número entero (bigint), o del porcentaje de filas a recuperar. Este porcentaje puede expresarse en forma de float. PERCENT Especifica que el número representa un porcentaje. WITH TIES La selección de filas a presentar se efectúa después de ordenar los datos con la cláusula ORDER BY. Sólo se puede emplear esta opción si se especifica una cláusula ORDER BY en la consulta.

En la cláusula SELECT, es opcional especificar entre paréntesis el número de filas o el porcentaje expresado por TOP, pero se recomienda encarecidamente hacerlo.

Ejemplo

Los pedidos se ordenan por cifras de negocio en sentido descendente y sólo se desean conocer los tres primeros. En este primer ejemplo no se utiliza la cláusula WITH TIES, por lo que la selección de las 3 líneas se efectúa antes de ordenar los resultados.

En este segundo ejemplo, la cláusula PERCENT permite conocer el 5% del resultado final.

6. Consulta de creación de tabla Es posible crear una nueva tabla a partir de una consulta utilizando la sintaxis: SELECT ..... INTO nombre_tabla FROM .....

La nueva tabla tendrá el esquema correspondiente a las columnas extraídas; en el caso de columnas calculadas, deberá especificarse un nombre de alias o un título. Si el nombre de la tabla va precedido de #, la tabla será temporal local; si va precedido de ##, será una tabla temporal global. Estos dos tipos de tablas se almacenan en la base de datos tempdb. Sólo se puede acceder a una tabla temporal local durante la sesión en que ha sido creada, pues desaparece con la desconexión. A una tabla temporal global se puede acceder durante todas las sesiones y se suprime al final de la última sesión que la ha utilizado. Ejemplos Creación de una nueva tabla en la base de datos actual:

Creación de una tabla temporal global:

7. Forzar el optimizador de consulta El lenguaje SQL es un lenguaje interpretado que permite describir el resultado que deseamos obtener. Por tanto, es relativamente fácil obtener una descripción válida del resultado. Por supuesto, para un mismo resultado puede haber diferentes maneras de describirlo.

A partir de esta descripción (consulta SELECT), el optimizador decide cuál es el mejor camino para mostrar el resultado. La cláusula OPTION de la consulta SELECT permite especificar al optimizador de la consulta la manera en la que debe definir el plan de ejecución de la consulta. Sin embargo, como el volumen de datos cambia sin cesar y se pueden aportar mejoras de estructura (por ejemplo, definiendo índices), esta solución ha de utilizarse con moderación. En todos los casos, es preferible dejar al optimizador de consultas la tarea de definir el plan de ejecución. Efectivamente, en la práctica totalidad de los casos, el optimizador encuentra el mejor plan de ejecución posible. Las directivas de destino del optimizador de consultas deben ser utilizadas como último recurso y puestas en práctica por un desarrollador experimentado o bien por el administrador de la base de datos, que posee una visión global de la organización de los datos en la base.

8. Tablas CTE Las tablas CTE (Common Table Expresión) tienen como objetivo simplificar la escritura y por consiguiente la comprensión de las consultas. Una tabla CTE puede ser considerada como una tabla temporal y específica de una instrucción SQL DML. Una alternativa a la utilización de tablas CTE puede ser definir una tabla temporal local (#MyTable) antes de la consulta de SQL DML y suprimir esta tabla temporal local inmediatamente después de la ejecución de la consulta. Evidentemente esta alternativa es mucho más compleja de administrar y por tanto menos limpia en cuanto a programación. Las tablas CTE permiten escribir de forma sencilla consultas complejas simplificando considerablemente la escritura de consultas anidadas. Las tablas CTE pueden ser utilizadas en el contexto de una consulta de extracción de datos (SELECT), pero también para las consultas de modificación de datos (INSERT, UPDATE y DELETE).

Las CTE son elementos de la norma ANSI SQL 99 o SQL 3. Sintaxis WITH nombreTablaCTE(nombreColumna1, nombreColumna2, ...) AS ( consultaSelect )

La tabla CTE se crea utilizando la instrucción WITH seguida del nombre de la tabla acompañado de la lista de columnas. La consulta SELECT que sirve de base para construir la tabla CTE se define a partir de la palabra clave AS. Ejemplo de definición de una tabla CTE

Una vez definida, la tabla CTE debe utilizarse inmediatamente; si no, se genera un error.

Les tablas CTE son más fáciles de crear que las tablas temporales, ya que el esfuerzo de sintaxis es menor. En cambio, el número de restricciones es mayor:  

Deben utilizarse inmediatamente después de su definición. No es posible utilizar con ellas las instrucciones COMPUTE, ORDER BY, INTO, FOR XML, FOR BROWSE.

9. Generación de filas estadísticas La cláusula COMPUTE crea nuevas filas que contienen resultados estadísticos a partir de funciones de agregación. Esta cláusula permite visualizar a la vez las filas de detalles y los cálculos de agregados, a la inversa de lo que hace GROUP BY, que sólo presenta los cálculos. Sintaxis SELECT.....COMPUTE función_estad_fila (col) [,...] [BY col[,...]

función_estad_fila(col) Funciones COUNT, SUM, AVG, MAX, MIN. BY col Muestra el resultado al cambiar de valor de columna. En ese caso, debe utilizarse una cláusula ORDER BY sobre la columna o las columnas afectadas. Ejemplo Cantidades en stock por almacén:

Operadores ROLLUP y CUBE

Los operadores ROLLUP y CUBE se utilizan en combinación con la cláusula GROUP BY y las funciones estadísticas con la finalidad de obtener filas suplementarias que contienen el cálculo de la función para agrupaciones combinadas.

La cláusula WITH ROLLUP permite crear filas con los resultados estadísticos para las agrupaciones de las columnas del GROUP BY combinadas de izquierda a derecha. Por ejemplo, si se pide la suma para una agrupación sobre las columnas A, B y C, la cláusula WITH ROLLUP proporcionará, además, la suma para una agrupación sobre A, la suma para una agrupación sobre A y B, y la suma total. La cláusula WITH CUBE permite crear líneas suplementarias para todas las combinaciones de agrupación de las columnas del GROUP BY . Para el ejemplo anterior, se tendrá, además, la suma para una agrupación sobre B, la suma para una agrupación sobre C y la suma para una agrupación sobre A y C, así como la suma para una agrupación sobre B y C.

Se pueden utilizar, como máximo, 10 expresiones de agrupación para un tamaño total de 900 bytes.

Ejemplo Cálculo de las cantidades en stock totales por almacén, categoría y artículo para dos categorías y dos almacenes:

Si se añade a la sintaxis la cláusula WITH ROLLUP, el resultado es:

Si se reemplaza WITH ROLLUP por WITH CUBE, el resultado es:

Operador OVER

Esta cláusula permite dividir los datos o bien clasificarlos antes, por ejemplo, de aplicar una función de cálculo de agregado o una función de clasificación, es decir, ROW_NUMBER, DENSE_RANK, RANK y NTILE. En el marco de una función de clasificación, la cláusula OVER puede contener una partición o una instrucción ORDER BY para realizar una clasificación. En caso de que se utilice una función de cálculo de agregado, sólo es posible la partición de los datos con la cláusula OVER. Sintaxis OVER ([PARTITION BY expresión, ...] [ORDER BY columna, ...]

Ejemplo

Operador NTILE

Esta función se utiliza conjuntamente con OVER y permite dividir cada partición en grupos de datos equilibrados. Por ejemplo, con la instrucción NTILE(4) OVER... el resultado de cada partición se dividirá en 4 grupos. Los datos serán repartidos de manera equitativa en cada grupo. Si el número de filas presentes en la partición no es un múltiplo del número de grupos que crear, entonces los primeros grupos contienen una fila más que los últimos grupos. En el caso aquí presentado, si hay que crear 4 grupos y la partición contiene 15 filas, entonces los tres primeros grupos contendrán 4 filas y el cuarto grupo contendrá 3 filas. Sintaxis NTILE(entero) OVER ...

Ejemplo

10. Subconsultas anidadas Es posible anidar una consulta SELECT en otra consulta SELECT (o en una instrucción UPDATE o DELETE) siempre que se utiliza una expresión. En general, la utilización se lleva a cabo con las cláusulas WHERE o HAVING. Podemos distinguir varios tipos de subconsultas:   

Subconsultas que devuelven un solo valor. Pueden utilizarse con los operadores =, <>, < , <= >, > =. Subconsultas que devuelven una lista de valores. Pueden utilizarse con el operador IN, EXISTS, ANY, SOME o ALL. Subconsultas correlacionadas (o subordinadas). La cláusula WHERE de la consulta interna hace referencia a una de las tablas de la consulta externa. En ese caso, la subconsulta interna se ejecuta para cada fila extraída por la consulta externa.

Ejemplos Pedidos del Cliente GUTIERREZ:

Extracción de los artículos cuya etiqueta de categoría incluye "COM":

Lista de los artículos que no forman parte de ningún pedido:

Lista de artículos con precios idénticos:

11. PIVOT y UNPIVOT Estas dos instrucciones, de Transact SQL, son muy potentes y fáciles de utilizar. El objetivo de PIVOT es transformar en columnas distintas un resultado presente en forma de filas distintas. UNPIVOT realiza la operación inversa. Para realizar la función de PIVOT había que definir una instrucción case complicada o recurrir a otros operadores como la combinación, la unión o el cálculo de agregados para presentar la información de la forma deseada. En cualquier caso, una secuencia de programación larga es trabajosa y puede dar lugar a problemas de funcionamiento. Antes de utilizar esta función, hay que definir cuáles son los datos que va a afectar y qué columna será el PIVOT. En general, el objetivo es realizar una tabla de síntesis para la que el valor presente en la celda provenga de un cálculo de agregados que dependa de la fila y la columna.

Para poder ejecutar estas instrucciones el servidor debe estar definido en nivel de compatibilidad 9.0 (sp_dbcmptlevel) o 00. En efecto, estas instrucciones han sido introducidas en Transact SQL por SQL Server 2005. ¿Cómo se utiliza PIVOT?

La instrucción PIVOT forma parte de la cláusula FROM de la instrucción SELECT. El uso de esta instrucción permitirá crear una pseudotabla interna para la consulta. Como todas las tablas manipuladas en una consulta, se puede especificar un alias para ella a través de la cláusula AS. Antes de utilizar la función PIVOT, hay que determinar el cálculo de agregados a efectuar y los valores de la columna sobre los que se puede realizar este proceso. La columna debe poseer un valor convertible en nvarchar porque los valores se transformarán en nombre de columna. Por ello no es posible realizar una pivotación alrededor de un valor de formato binario. Resumen de sintaxis

SELECT FROM [...] PIVOT (cálculoAgregado FOR columnaPivot IN (listaDeValor)) as aliasDeTabla

La lista de valor permite indicar los valores de la columna PIVOT que se transformarán en columna. Todos los valores de la lista deben especificarse entre corchetes [ ] sin apóstrofe para las cadenas de caracteres. Esta misma notación se emplea detrás de la cláusula SELECT para indicar el título de la columna y el orden de las columnas. El alias de tabla para PIVOT es indispensable.

Cuando se utiliza PIVOT con un cálculo de agregado, no se tienen en cuenta los valores nulos. Si se desea procesar estos valores, hay que utilizar la función ISNULL de Transact SQL. Ejemplo de utilización de PIVOT

En el siguiente ejemplo, la tabla de stocks contiene una fila de información por artículo y almacén. De momento existen tres almacenes (P1, P2 y N1); los dos primeros en Barcelona, y el tercero en Murcia. Ejemplo de la tabla de stocks

Para obtener una vista resumida de la disponibilidad de un artículo, hay que recorrer tres filas de datos. No resulta fácil leer la información. En cambio, si se pivotan los datos con respecto a la referencia artículos y se presentan en función de esta última, las cantidades disponibles en stock de cada uno de los almacenes, la información será más pertinente. Ejemplo de extracción utilizando PIVOT

Para clarificar el resultado se pueden realizar combinaciones con otras tablas. En el siguiente ejemplo se realiza una combinación con la tabla de artículos para conocer la designación de los artículos. Ejemplo de uso de una combinación junto con una pivotación

UNPIVOT

La función UNPIVOT realiza la función inversa a la de PIVOT. No obstante, es mucho menos frecuente que surja la necesidad de transformar las columnas en filas. Si se presenta el caso, esta funcionalidad se puede utilizar con la siguiente sintaxis: SELECT ... columnaUnpivot, AliasNuevaColumna FROM tabla UNPIVOT (AliasNuevaColumna FOR columnaUnPivot IN listaDeValor) AS aliasDeTabla

Al igual que con PIVOT, es posible realizar combinaciones con una tabla que contenga la columna UNPIVOT. Ejemplo de uso de UNPIVOT

Se utiliza una tabla CTE en la secuencia de comandos para facilitar su comprensión.

12. MERGE La instrucción MERGE permite en una sola operación Transact SQL efectuar modificaciones, inserciones e incluso eliminaciones en una misma tabla de destino. La selección de la acción se realiza mediante criterios de selección. Naturalmente, la instrucción MERGE no puede utilizar la misma tabla como origen y como destino. No obstante, es posible definir una condición de combinación entre el origen y el destino para limitar los datos manipulados. Esta instrucción permite simplificar algunos tratamientos de la información, para la construcción de síntesis, por ejemplo. Sin esta instrucción es necesario definir un lote Transact SQL con un cursor para poder tratar cada fila del origen de forma individual y decidir así la acción que se va a realizar. Sintaxis MERGE [ INTO ] tablaDestino USING origenDatos ON [ WHEN MATCHED [ AND ] THEN ] [ WHEN [TARGET] NOT MATCHED [ AND ] THEN ] [ WHEN SOURCE NOT MATCHED [ AND ] THEN ]

tablaDestino

Se trata de la tabla que contiene los datos sobre los cuales se basan las instrucciones INSERT, UPDATE y DELETE. origenDatos A partir de este origen de datos se extraen los datos que permiten seleccionar el tipo de operación que se ha de ejecutar y los datos que se han de utilizar. condiciónSelección Criterio de restricción que permite saber si es necesario seleccionar los datos o bien si debe ser ejecutada la operación que sigue. operación Instrucción INSERT, UPDATE o DELETE que se ha de ejecutar sobre la tabla de destino. Ejemplos La tabla Síntesis_Clientes contiene las referencias de todos los clientes que han realizado al menos un pedido, es decir, que poseen un volumen de negocio:

Se ha añadido una columna CA de tipo numérico(10,2) a la tabla de clientes de la siguiente manera:

Gestión de las vistas SQL Server permite la gestión de objetos asociados a las tablas: las vistas ( VIEWS). Una vista es una tabla virtual, es decir, un objeto con el mismo uso que una tabla en cuanto al lenguaje de manipulación de datos, con algunas restricciones, pero no ocupa espacio en disco para los datos. Una vista sólo "almacena" la consulta correspondiente a la extracción. Los usos de una vista son múltiples: 





Simplificación de las estructuras de las tablas. Ciertas tablas pueden comportar numerosas columnas con nombres y tipos poco prácticos de manipular. Una vista dará al usuario los mismos datos en una forma simplificada. Reutilización de consultas. Cuando las consultas se ejecutan a menudo (combinaciones, cálculos), una vista permitirá guardar la instrucción y utilizarla de forma más simple. Seguridad de acceso. Se pueden ocultar líneas y columnas a los usuarios dejando a su disposición sólo las vistas de proyección o de restricción, en lugar de las tablas iniciales.

La modificación de los datos a través de una vista sólo se permite si se modifica una sola tabla correspondiente a la vista y si la consulta de la vista no utiliza cálculos. Creación de vistas Se puede crear una vista con el lenguaje de definición de datos o con el SQL Server Management Studio . Sintaxis CREATE VIEW nombre [WITH ENCRYPTION | WITH SCHEMABINDING| WITH VIEW_METADATA] AS consulta [WITH CHECK OPTION]

nombre Nombre de objeto; debe ser único en la base de datos. consulta Instrucción SELECT sin cláusula ORDER BY, UNION, COMPUTE o INTO. WITH ENCRYPTION Permite cifrar el código en las tablas de sistema. WITH SCHEMABINDING Permite vincular la vista al esquema. Con esta opción, los objetos a los que se hace referencia en la vista deben nombrarse del siguiente modo°: nombre_esquema.nombre_objeto, y las tablas utilizadas en la vista no pueden ser eliminadas. Además si una operación ALTER TABLE afecta a la definición de la vista, ésta fallará. WITH VIEW_METADATA Permite especificar a SQL Server que devuelva la información de metadatos correspondientes a la vista, y no la de las tablas que componen la vista. Esta solicitud de información de metadatos es particularmente importante en el contexto del acceso a datos a través de una interfaz de programación, como ODBC u OLE-DB. WITH CHECK OPTION Permite no autorizar la inserción ni la modificación de datos que no respondan a los criterios de la consulta. Eliminación de vistas Sintaxis

DROP VIEW nombre

Ejemplo Simplificación de la tabla artículos:

Creación de una vista mediante el SQL Server Management Studio:

Edición de una vista mediante el SQL Server Management Studio:

Los desencadenadores de tipo INSTEAD OF están particularmente bien adaptados para las vistas, ya que permiten desviar las acciones INSERT, UPDATE y DELETE hacia las tablas que componen la vista. Estos desencadenadores ofrecen la posibilidad de obtener vistas cuyo comportamiento sea completamente transparente para el usuario de la base de datos.

El SQL procedimental SQL Server es un servidor de base de datos relacional y como tal proporciona todos los elementos para almacenar de forma estructurada los datos, pero también las herramientas necesarias para trabajar con los datos mediante SQL. Con Transact SQL también es posible definir tratamientos procedimentales directamente en la base de datos. Estos tratamientos podrán ser utilizados por todos los usuarios de la base siempre y cuando posean los privilegios necesarios. Es posible conservar la definición de estos tratamientos y convertirlos en parametrizables mediante la creación de funciones y procedimientos. También se pueden establecer tratamientos procedimentales para definir restricciones de integridad complejas. Entonces estaremos hablando de triggers o desencadenadores de base de datos.

1. Gestión de variables de usuario Una variable es una zona de memoria, caracterizada por un nombre y un tipo, que permite almacenar un valor. Las variables Transact-SQL deben declararse obligatoriamente antes de su utilización. A continuación pueden reemplazar cualquier expresión en las instrucciones SQL. Declaración de variables DECLARE @nombre_variable tipo [,...]

nombre_variable Nombre precedido del carácter @. tipo Tipo de sistema o definido por el usuario. Asignación de valores a las variables SELECT @nombre_variable = expr [,...][FROM...]

Ejemplo Modificación del nombre de los clientes que tienen el mismo apellido que el 954:

2. Variables de sistema Estas variables son definidas por el sistema y se pueden utilizar sólo en lectura. Se distinguen de las variables de usuario por la doble @. @@CONNECTIONS Número de conexiones o de intentos de conexión desde el último inicio de SQL Server. @@CPU_BUSY Tiempo dedicado por la unidad central SQL Server desde el último inicio de éste. El resultado se expresa en unidad CPU. Hay que multiplicar por @@TIMETICKS para tener el resultado en microsegundos. @@CURSOR_ROWS Número de filas afectadas en el último cursor abierto. @@DATEFIRST Devuelve el valor actual del parámetro SET DATEFIRST. @@DBTS Valor del tipo de datos timestamp actual para la base de datos. @@ERROR

Último número de error generado por el sistema para la conexión del usuario. @@FETCH_STATUS Contiene el estado de un comando de cursor FETCH. @@IDENTITY Guarda el último valor IDENTITY insertado. @@IDLE Tiempo, en milisegundos, durante el cual SQL Server ha estado inactivo desde su último inicio. @@IO_BUSY Tiempo, en milisegundos, dedicado por SQL Server para efectuar operaciones de entrada/salida desde su último inicio. @@LANGID Identificador del idioma utilizado actualmente. @@LANGUAGE Idioma utilizado actualmente. @@LOCK_TIMEOUT Timeout en milisegundos de la sesión en curso. @@MAX_CONNECTIONS Número máximo de conexiones simultáneas que es posible establecer con SQL Server. @@MAX_PRECISION Devuelve el nivel de precisión utilizado por los tipos de datos decimal y numérico. @@NESTLEVEL Nivel de anidamiento de la instrucción que se esta ejecutando. @@OPTIONS

Datos sobre los valores actuales de las opciones SET. @@PACK_RECEIVED Número de paquetes entrantes leídos por SQL Server desde su último inicio. @@PACK_SENT Número de paquetes salientes escritos por SQL Server desde su último inicio. @@PACKET_ERRORS Número de errores que se han producido mientras SQL Server enviaba o recibía paquetes desde su último inicio. @@PROCID Identificador del procedimiento almacenado Transact SQL del tratamiento en curso de ejecución. @@REMSERVER Devuelve el nombre del servidor contenido en el registro de los nombres de acceso de un servidor remoto. @@ROWCOUNT Número de filas afectadas por la última instrucción. @@SERVERNAME Nombre del servidor SQL local. @@SERVICENAME Nombre del servicio en curso de ejecución. @@SPID Número de identificación del proceso actual sobre el servidor. @@TEXTSIZE Longitud máxima, en bytes, de los datos de texto o imagen devueltos por una instrucción SELECT.

@@TIMETICKS Número de milisegundos por pulsación. @@TOTAL_ERRORS Número de errores encontrados por SQL Server al leer o escribir datos desde su último inicio. @@TOTAL_READ Número de lecturas de datos del disco efectuadas por SQL Server desde su último inicio. @@TOTAL_WRITE Número de veces que se han escrito datos en el disco efectuadas por SQL Server desde su último inicio. @@TRANCOUNT Número de transacciones actualmente activas para el usuario actual. @@VERSION Fecha, número de versión y tipo de procesador de la versión actual de SQL Server.

3. Las transacciones Gestión de las transacciones

Lo primero que hay que tener en cuenta es el bloqueo de los datos. Cuando SQL Server lee o modifica datos, bloquea las filas que está manipulando. Este bloqueo dura el mismo tiempo que la ejecución de la instrucción o la transacción. Según el tipo de bloqueo, será posible o no que otras transacciones accedan simultáneamente a la misma información. Una transacción es un conjunto de instrucciones de manipulación de datos que se ejecutan en una misma unidad de trabajo. La validación de una transacción asegura que todas las instrucciones que forman parte de ésta terminan correctamente. La anulación de la transacción asegurará la anulación del conjunto de las instrucciones. Sólo las instrucciones del DML ( SELECT, INSERT, UPDATE, DELETE) son tenidas en cuenta en una transacción. Se excluyen todas las instrucciones que manipulan objetos (CREATE, ALTER, DROP, SELECT INTO, GRANT, REVOKE, LOAD, DUMP...).

Las transacciones serán útiles para asegurar la integridad y la coherencia de los datos en modificaciones múltiples, para mejorar el rendimiento, para probar los efectos de una modificación, para gestionar los bloqueos. Una transacción se caracteriza por la palabra clave ACID (Atomicity Consistency Isolation Durability) es decir atomicidad, coherencia, aislamiento y durabilidad.  





Atomicidad: una transacción representa una unidad indivisible de trabajo para el servidor. Consistencia: al fin de la transacción, la información presente en la base de datos debe ser consistente con las reglas de estructuración de los datos que se hayan definido. Aislamiento: ya que los datos son o bien los anteriores a la transacción, o bien los resultantes de ella. Si una transacción está modificando unos datos, otra transacción no puede visualizarlos al mismo tiempo. Durabilidad: cuando se valida una transacción que ha modificado datos, las modificaciones permanecen y el gestor de bases de datos debe garantizar que, suceda lo que suceda a nivel del sistema, los cambios siempre serán visibles.

Sintaxis

Inicio de transacción BEGIN TRAN[SACTION][nombretransacción]

Validación de transacción COMMIT TRAN[SACTION][nombretransacción]

Declaración de un punto de control SAVE TRAN[SACTION][nombre_punto_control]

Anulación de transacción ROLLBACK TRAN[SACTION][{nombretransacción/nombre_punto_control}]

Ejemplo Simulación de aumento de tarifas:

Gestión de los bloqueos

En transacciones concurrentes, SQL Server gestiona automáticamente los bloqueos para garantizar la coherencia de los datos de cada transacción.

Una transacción no podrá modificar las filas a las que accede otra transacción y no podrá leer las filas en curso de modificación (lectura coherente). SQL Server activa automáticamente los bloqueos en el momento de ejecutar las transacciones. Estos bloqueos podrán activarse a diferentes niveles: fila, páginas, tablas, extensión... El tipo de bloqueo es elegido por SQL Server para minimizar los costes. Los tipos disponibles son: Bloqueos compartidos Destinados a las operaciones de lectura, impiden la modificación de datos. Varias transacciones pueden activar bloqueos compartidos sobre los mismos elementos. Bloqueos de actualización Implementados para las operaciones de modificación (UPDATE LOCK). Sólo una transacción puede adquirir un bloqueo de este tipo sobre un recurso; las otras transacciones deben esperar. Si la transacción no actualiza los datos leídos, el bloqueo se transforma en bloqueo compartido. Bloqueos exclusivos Destinados a las operaciones de escritura, no permiten ningún otro bloqueo y actúan a nivel de tabla o página. Bloqueos con intención Permiten a SQL Server señalar que una transacción quiere adquirir un bloqueo de actualización o compartido sobre el recurso. Es imposible la activación de bloqueos exclusivos. Es posible actuar sobre los bloqueos a nivel de la configuración, a nivel de las transacciones, obteniendo datos por medio de la vista dinámica sys.dm_tran_locks o seleccionando el nodo Administración - Monitor de actividad en el Explorador de objetos de SQL Server Management Studio. SET TRANSACTION

Se puede definir el sistema de bloqueo de las instrucciones SELECT para todas las transacciones de la sesión. Sintaxis SET TRANSACTION ISOLATION LEVEL opción

Options

Read UNCOMMITTED

Este nivel permite leer las modificaciones todavía no validadas por otras transacciones. Este tipo de lectura se denomina “en sucio” o "dirty read", ya que los datos visualizados no necesariamente se conservarán en caso de que la transacción se anule (ROLLBACK). En este modo de funcionamiento, las instrucciones de DML no necesitan un bloqueo compartido para la lectura de los datos porque nada impide la modificación de los datos leídos, aunque la modificación no esté aún validada. READ COMMITTED Es el modo de funcionamiento predeterminado. Sólo son visibles las modificaciones validadas (COMMIT). Las instrucciones de DML utilizan siempre un bloqueo exclusivo. No obstante, los bloqueos fijados para la lectura de los datos se anulan cuando termina la lectura, no cuando termina la transacción. Si se lee más de una vez la misma fila durante una misma transacción, no está garantizado que la lectura de la misma fila devuelva siempre los mismos datos. Puede darse este caso si los datos son modificados y luego validados por otra transacción entre las dos operaciones de lectura. REPEATABLE READ Con este modo de funcionamiento, los datos leídos o modificados por una transacción ya no son accesibles para las otras transacciones, con el fin de garantizar que la lectura repetida de una fila de datos devuelva siempre el mismo contenido. No obstante, los datos leídos siguen siendo accesibles en lectura para las demás transacciones. Los bloques se definen para la duración de la transacción. Con este tipo de bloqueo, se pueden agregar datos a las tablas entre el principio y el fin de la transacción y, por tanto, el conjunto de datos ya no es el mismo. Este tipo de problema puede tener una incidencia significativa cuando, por ejemplo, una transacción efectúa un cálculo de descuento sobre una cifra y, simultáneamente, otra transacción añade un pedido adicional. Este nuevo pedido modificará el resultado del cálculo del descuento. SERIALIZABLE Con este modo de funcionamiento, los datos leídos o modificados por una transacción serán accesibles únicamente para esta transacción. Los datos que sólo se lean seguirán siendo accesibles en modo de lectura para las demás transacciones. Se definen bloqueos para toda la duración de la transacción a nivel de las filas de datos. También se definen bloqueos de nivel más alto, en los índices, para evitar que se agreguen datos al conjunto de resultados de la transacción. Si no existe ningún índice que cubra la consulta de lectura, se impone un bloqueo adicional a nivel de la tabla. Opciones de bloqueo

Es posible especificar el bloqueo de una tabla para una instrucción SELECT particular. Es muy aconsejable dejar la gestión de bloqueos en manos del motor relacional de SQL Server para optimizar esta gestión. La gestión manual sólo permite muy raramente una

gestión óptima y puede provocar problemas de interbloqueo o retrasos para las otras transacciones. Sintaxis SELECT.....FROM nombretabla WITH (opción de bloqueo)

Opciones de bloqueo

NOLOCK Sin bloqueos. HOLDLOCK Mantiene bloqueos compartidos hasta el fin de la transacción. UPDLOCK Mantiene bloqueos de actualización de página hasta el fin de la transacción. TABLOCK Uso de bloqueos compartidos sobre la tabla. PAGLOCK Uso de bloqueos compartidos sobre las páginas. TABLOCKX Mantiene un bloqueo exclusivo. ROWLOCK Permite especificar que se debe utilizar un bloqueo de nivel de filas cuando el optimizador de consulta de la tabla tome la decisión de utilizar uno en el nivel de páginas o de tablas. Ejemplo Activación de un bloqueo compartido durante la extracción (nivel tabla):

Se puede activar el bloqueo a nivel de fila en las inserciones (IRL: Insert Row-level Locking) ejecutando el procedimiento almacenado: sp_tableoption ’nombretabla’,’insert row lock’,’true’.

4. Gestión de los lotes y de los scripts Un lote de instrucciones es un conjunto de instrucciones Transact-SQL que será compilado y ejecutado en una sola unidad. Termina con la instrucción GO . Un lote puede comportar cualquier instrucción o serie de instrucciones, así como transacciones. El interés de los lotes reside en la mejora del rendimiento y en la compilación única: en el caso de que se produzca un error de sintaxis, no se ejecuta ninguna instrucción. Sin embargo, los lotes están sometidos a ciertas restricciones: 





No se pueden combinar ciertas instrucciones en un mismo lote: CREATE PROCEDURE, CREATE RULE, CREATE DEFAULT, CREATE TRIGGER, CREATE VIEW. No se puede actuar sobre definiciones de columna y utilizar las modificaciones en un mismo lote (valores por defecto, restricción CHECK, adición de columnas a una tabla). No se puede suprimir un objeto y volver a crearlo en un mismo lote.

Los scripts son conjuntos de lotes que se ejecutarán sucesivamente a partir de un archivo de texto. Estos archivos tienen, por convención, la extensión ‟.sql‟.

5. Control de flujo Es un conjunto de funcionalidades que incluyen instrucciones (RETURN, RAISERROR, PRINT) y estructuras de control (secuencial, alternativa, repetitiva) que mejoran el uso de las instrucciones Transact-SQL dado que permiten que el usuario controle su ejecución. a. RETURN

Esta instrucción permite salir de forma incondicional de un procedimiento o función devolviendo eventualmente un valor entero. Sintaxis RETURN [exprn]

Ejemplo Envío de un valor de un procedimiento para indicar el correcto funcionamiento:

Utilización:

Ahora se podrá comprobar la variable @verif_procedure. b. PRINT

Es una instrucción de presentación de mensaje. Sintaxis PRINT {’texto’|@variable|@@variablesistema}

Ejemplo

c. CASE

Es una expresión que permite atribuir valores condicionales. Sintaxis CASE [expresión] WHEN {valor|condición} THEN valoratribuido1 [...] [ELSE valoratribuido n] END

Devuelve el valor atribuido en función del valor de la expresión o en función de una condición. Ejemplo

El siguiente ejemplo muestra la utilización de la instrucción CASE con una condición de comparación a nivel de la cláusula WHEN. Esta condición suministrará un resultado de tipo booleano.

d. BEGIN... END

Es una estructura de control que permite delimitar una serie de instrucciones (bloque). Se utiliza con los condicionales (IF) y los bucles (WHILE). Sintaxis

BEGIN {instrucción | bloque} ... END

e. IF

Es la estructura de control alternativa que permite evaluar una condición y ejecutar una instrucción o un bloque si la condición es VERDADERA. Sintaxis IF condición {instrucción|bloque} [ELSE] {instrucción|bloque}

Ejemplo

f. WHILE

Es la estructura de control repetitiva que permite ejecutar una serie de instrucciones mientras una condición sea cierta. Sintaxis WHILE condición {instrucción|bloque}

La instrucción BREAK permite la salida del bucle. La instrucción CONTINUE permite volver a la primera instrucción del bucle. Ejemplo Se aumentan las tarifas hasta que el promedio del precio sobrepase los 50 € o hasta que un artículo de la categoría ‟01‟ sea superior a 600 €.

g. OUTPUT

Esta cláusula, introducida por SQL Server 2005, permite conocer las filas afectadas por la operación de DML INSERT, UPDATE o DELETE. Con esta información, la aplicación que ha solicitado ejecutar la instrucción DML puede saber cuáles son las filas de datos afectadas.

El valor de las columnas devueltas por medio de la cláusula OUTPUT es posterior a la aplicación de restricciones de integridad y a la ejecución de la instrucción DML, pero anterior a la ejecución de los desencadenadores asociados a la tabla y a la instrucción DML.

Esta cláusula está disponible únicamente a partir de SQL Server 2005. Sintaxis OUTPUT [listaColumna] INTO @variable

listaColumna Representa la lista de columnas devueltas en la variable. Estas columnas pueden provenir directamente de la instrucción DML o corresponder a un resultado de cálculo elemental. Los datos pueden extraerse directamente de la tabla. Con los prefijos DELETED e INSERTED es posible ver los datos afectados por la eliminación/modificación y después de la modificación/inserción. @variable La variable debe ser de tipo tabla y debe declararse antes de su utilización en la cláusula OUTPUT. INSERT INTO tabla(listaColumna) OUTPUT [listaColumna] INTO @variable VALUES (listaValores) DELETE tabla OUTPUT[listaColumna] INTO @variable WHERE condición UPDATE tabla SET columna=valor OUTPUT [listaColumna] INTO @variable WHERE condición

Ejemplo Utilización de la cláusula OUTPUT para conocer el valor afectado por una columna de tipo IDENTITY al agregar datos.

6. Gestión de los cursores El uso de cursores es una técnica que permite tratar fila por fila el resultado de una consulta, contrariamente a SQL (SELECT), que trata un conjunto de filas.

Los cursores pueden ser implementados por instrucciones Transact-SQL (cursores ANSISQL) o por la API OLE-DB. Se utilizarán los cursores ANSI cuando sea necesario tratar las filas individualmente en un conjunto o cuando SQL no pueda actuar únicamente sobre las filas afectadas. Los cursores de las API serán utilizados por las aplicaciones cliente para tratar volúmenes importantes o para gestionar varios conjuntos de resultados.

Utilice los cursores sólo si no es posible el uso de SQL orientado a conjuntos. Suelen consumir mucha memoria y tiempo de tratamiento. a. DECLARE CURSOR

Esta instrucción permite la declaración y la descripción del cursor ANSI. Sintaxis DECLARE nombrecursor[INSENSITIVE][SCROLL]CURSOR FOR SELECT .... FOR {READ ONLY|UPDATE[OF lista columna]}]

INSENSITIVE Sólo se permiten las operaciones sobre la fila siguiente. SCROLL Los desplazamientos en las filas del cursor podrán hacerse en todos los sentidos. UPDATE Especifica que las actualizaciones se harán sobre la tabla de origen del cursor. Un cursor INSENSITIVE con una cláusula ORDER BY no puede actualizarse. Un cursor con ORDER BY, UNION, DISTINCT o HAVING es INSENSITIVE y READ ONLY. Además de esta sintaxis conforme con la norma ISO, Transact SQL propone una sintaxis ampliada que ofrece más posibilidades en lo relativo a los cursores: DECLARE nombrecursor CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR SELECT ... [ FOR UPDATE [ OF lista_columna [ ,...n ] ] ]

LOCAL El alcance del cursor es local en el lote, el procedimiento o la función en curso de ejecución en el que está definido el cursor. Fuera del lote, no es posible referenciar el cursor. GLOBAL El alcance del cursor es global a la conexión. La opción de base de datos default to local cursor está definida en falso (false) de manera predeterminada. Esta opción permite definir el alcance predeterminado del cursor. FORWARD_ONLY Los datos se extraen del cursor por orden de aparición (del primero al último). STATIC Se realiza una copia temporal de los datos en tempdb para que el cursor no se vea afectado por las modificaciones que puedan realizarse sobre la base. KEYSET Las filas y su orden en el cursor se fijan en el momento de la apertura del cursor. Las referencias hacia cada una de estas filas de información se conservan en una tabla temporal en tempdb. DYNAMIC El cursor refleja exactamente los datos presentes en la base. Esto significa que el número de filas, su orden y su valor pueden variar de forma dinámica. FAST_FORWARD Permite definir un cursor hacia delante y como sólo lectura (FORWARD_ONLY y READ_ONLY). SCROLL_LOCKS Permite garantizar el éxito de las instrucciones UPDATE y DELETE que pueden ser ejecutadas en relación al cursor. Con este tipo de cursor, se fija un bloqueo en la apertura del cursor para evitar que otra transacción trate de modificar los datos. OPTIMISTIC

Con esta opción, puede que una operación de actualización (UPDATE) o bien de eliminación (DELETE) realizada en el cursor no pueda efectuarse correctamente porque otra transacción haya modificado los datos en paralelo. TYPE_WARNING Se envía un mensaje de aviso (warning) a la aplicación cliente si se efectúan conversiones implícitas de tipo. b. OPEN

Esta instrucción permite hacer operativo el cursor y crear eventualmente las tablas temporales asociadas. La variable @@CURSOR_ROWS se asigna después de OPEN. Teniendo en cuenta el espacio en disco y la memoria utilizada y el bloqueo eventual de los datos en la apertura del cursor, esta operación debe ser ejecutada lo más cerca posible del tratamiento de los datos extraídos del cursor. Sintaxis OPEN [GLOBAL] nombrecursor

c. FETCH

Es la instrucción que permite extraer una fila del cursor y asignar valores a variables con su contenido. Tras fetch, la variable @@FETCH_STATUS está a 0 si fetch no ha dado errores. Sintaxis FETCH[{NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n] [FROM] [GLOBAL] nombrecursor[INTO Listavariable]

NEXT Lee la fila siguiente (única opción posible para los INSENSITIVE CURSOR). PRIOR Lee la fila anterior. FIRST Lee la primera fila. LAST

Lee la última fila. ABSOLUTE n Lee la enésima fila del conjunto. RELATIVE n Lee la enésima fila a partir de la fila actual. d. CLOSE

Cierra el cursor y libera la memoria. Esta operación debe interponerse lo antes posible con el fin de liberar los recursos cuanto antes. Sintaxis CLOSE nombrecursor

e. DEALLOCATE

Suprime el cursor y las estructuras asociadas. Sintaxis DEALLOCATE nombrecursor

Ejemplo Sólo se quieren los tres artículos más caros por categoría en una tabla de trabajo:

Si la misma secuencia de comandos Transact SQL trabaja con varios cursores, la variable @@FETCH_STATUS corresponderá al estado del último cursor utilizado. Para conocer en detalle el estado de cada cursor, hay que interrogar a la columna fetch_status de la vista dinámica sys.dm_exec_ cursors.

7. Gestión de excepciones a. Mensajes de error

Para cada error, SQL Server genera un mensaje de error que, de forma predeterminada, se presenta en pantalla. Su lectura completa suele permitir resolver el problema.

La mayoría de estos mensajes están definidos en SQL Server, pero también es posible definir mensajes propios por medio del procedimiento sp_addmessage. Estructura de los mensajes

Sea cual sea su origen, todos los mensajes de error poseen la misma estructura y los siguientes campos de información: 



 

 

número: Cada mensaje se identifica con un número único. Éste es independiente del lenguaje elegido en la instalación de SQL Server. Así, el programa desencadena un error identificado por su número. A continuación, SQL Server selecciona en la tabla sys.messages de la base de datos master el mensaje que va a presentar en función del número del error y del lenguaje elegido en la instalación del servidor. mensaje en formato texto: El mensaje es específico de cada tipo de error. Muchos mensajes poseen variables para adaptar el contenido genérico al caso concreto y ofrecer más información. gravedad: Es un indicador de la gravedad del error. Los mensajes de gravedad 1 o 2 se generan simplemente a título informativo. estado: un mismo error puede tener distintos orígenes, es decir que puede haber aparecido en distintos contextos. Cada tipo de contexto tiene asociado un estado cuyo número puede resultar útil de cara a consultar la documentación. nombre del procedimiento: Si el error se ha provocado desde un procedimiento almacenado, se presenta el nombre de éste. número de línea: Número de la línea en la que se ha originado el error, ya se encuentre en una serie de instrucciones, una función, un procedimiento almacenado o un desencadenador de la base de datos.

Gravedad de los mensajes de error

En SQL Server, cada mensaje de error posee una gravedad concreta. Esta permite clasificar los mensajes en función del riesgo potencial que comporten. Existen 25 niveles de gravedad. La gravedad se representa con un número entero comprendido entre 0 y 24. Esta gravedad se asocia a un mensaje de error cuando éste se crea, aunque se puede fijar una gravedad ligeramente diferente cuando la causa del error sea la instrucción RAISERROR. Inferior a 9 El mensaje se ofrece a título meramente informativo. No produce bloqueos. Si la gravedad es 0, el mensaje no es visible. Igual a 10 Es un mensaje informativo para describir un error provocado por el usuario con la información que acaba de escribir.

Entre 11 y 16 El usuario puede resolver el error. Igual a 17 Recursos insuficientes. Señala, por ejemplo, que SQL Server se ha quedado sin espacio en disco o que la instancia ha alcanzado ciertos límites fijados por el administrador. Igual a 18 Error interno no fatal. Se ha producido un error interno, pero la consulta se ha podido procesar correctamente y se mantiene la conexión con el servidor. Igual a 19 Error SQL en el acceso a un recurso. SQL Server ha alcanzado un límite no configurable de la instancia. A partir de este nivel, todos los mensajes deben comunicarse al administrador. Igual a 20 Error fatal en el proceso actual. El proceso en curso acaba de sufrir un error grave, pero la instancia de SQL Server no ha quedado afectada. Igual a 21 Error fatal en el proceso de la instancia, aunque es poco probable que la base de datos haya quedado afectada por el problema. Igual a 22 Error fatal en la integridad de una tabla. La integridad de los datos de la tabla no se ha respetado. El administrador puede utilizar DBCC CHECKDB para saber si han quedado afectadas otras tablas y tratará de restablecer la integridad por medio de DBCC. Igual a 23 La integridad de la base de datos ya no está garantizada. Si la utilidad DBCC no resuelve el problema, el administrador tal vez tenga que restaurar la base de datos para remediar el problema. Igual a 24 Problema de hardware, por ejemplo un error de acceso a disco.

b. Desencadenar un error

El programador puede optar por generar un mensaje de error en función del comportamiento del código. Para generar un error debe utilizar la instrucción RAISERROR . Existen dos posibilidades para trabajar con esta instrucción: generar un error definido por su número o generar un error predefinido. Sintaxis RAISERROR ( { identificador | mensaje } { ,gravedad ,estado } [ ,argumento [ ,...n ] ] ) [WITH opción, ...]

identificador Número de identificación del mensaje almacenado en la tabla sysmessages. El mensaje se selecciona en función del identificador y del idioma de la sesión. Si no hay versión del mensaje en el idioma de instalación, se utiliza la versión en inglés. mensaje Es posible definir directamente el texto del mensaje, dentro de un límite de 2.047 caractères. Si se hace, también hay que definir su gravedad y su estado. gravedad Permite especificar la gravedad del mensaje de error. En general, los mensajes de usuario tienen una gravedad estrictamente inferior a 20. Por encima de este valor, el error se considera fatal y se interrumpe la conexión con la base de datos. estado Permite rastrear el origen del error. argumento Si el mensaje (predefinido o no) posee parámetros, hay que darles valores a partir de constantes o de variables. opción Existen tres opciones posibles: LOG, NOWAIT y SETERROR. La opción LOG permite especificar que el mensaje aparecerá en el Visor de sucesos de Windows.

La opción NOWAIT garantiza que el mensaje se enviará al cliente inmediatamente. La opción SETERROR permite dar valor a @@ERROR y ERROR_NUMBER con el número del mensaje de error. Ejemplos Error definido por el usuario:

Error grave con parámetros en el mensaje:

c. Definir un mensaje de error

Es posible definir mensajes de error personalizados para comunicar a la aplicación cliente un error propio de ella sin dejar de respetar el mecanismo de gestión de excepciones de SQL Server. Si se trabaja de esta forma en el nivel de procedimientos almacenados, funciones y desencadenadores, la gestión de errores resulta muy fácil. Para definir un nuevo mensaje de error, hay que recurrir al procedimiento almacenado sp_addmessage. Los mensajes agregados con el procedimiento sp_addmessage pueden eliminarse con el procedimiento sp_dropmessage. Sintaxis sp_addmessage [ @msgnum = ] identificador , [ @severity = ] gravedad , [ @msgtext = ] ’mensaje’ [ , [ @lang = ] ’idioma’ ] [ , [ @with_log = ] TRUE|FALSE ] [ , [ @replace = ] ’replace’ ]

@msgnum Número entero (int) que permite especificar el número del mensaje. Los mensajes definidos por el usuario pueden tener un identificador comprendido entre 50.001 y 2.147.483.647. @severity Número entero (smallint) que especifica la gravedad del mensaje. Debe estar comprendido entre 1 y 25. @msgtext

Cadena de caracteres que contiene el texto presentado cuando se produce el error. @lang Cadena de caracteres que indica el idioma del mensaje. En primer lugar, el mensaje debe definirse en inglés ‟us_english‟, y después puede localizarse en español indicando ‟spanish‟. @with_log Permite precisar si el mensaje aparecerá en el registro de sucesos de Windows o no. @replace Permite solicitar la eliminación del mensaje que posea el mismo número que el que se está generando, si es que existe.

Para conocer los diferentes códigos de idioma, basta ejecutar el procedimiento sp_helplanguage sin ningún parámetro.

Es posible definir variables en el texto del mensaje para personalizar el texto del mensaje en función del contexto del que surge. Por tanto es posible, por ejemplo, saber el nombre del usuario o de la tabla afectada por la operación. Para introducir valores obtenidos de variables en el texto del mensaje de error es necesario precisar cómo se va a convertir e introducir el valor en el texto. El formato sigue siempre la forma siguiente: & [indicador] [anchura] [.precisión]tipo.

indicador Este indicador, que puede ser +, -, 0, # o espacios, tiene como objetivo la justificación de los datos de tipo numérico con relleno de ceros y determinar el comportamiento que se ha de seguir para mostrar los valores señalados. anchura Permite definir el número de caracteres que requerirá la representación textual del valor numérico.

precisión Permite definir el número de decimales que se mostrarán. tipo Para precisar el tipo de valor transmitido en el parámetro se utilizan los caracteres siguientes: Tipo doi u s o xoX

Representa Entero con signo Entero sin signo Cadena de caracteres Un número en octal (base 8) Un número en hexadecimal (base 16)

Al definir el mensaje en inglés, hay que utilizar parámetros %s para insertar un dato de tipo carácter y %d si el valor es numérico. En las versiones localizadas del mensaje, hay que utilizar %1 para hacer referencia al primer parámetro definido en el texto del mensaje en inglés, %2 para el siguiente y así sucesivamente.

Si el mensaje posee parámetros, a veces es preferible formatear el mensaje de error valorando los diferentes parámetros del mensaje durante la ejecución del programa y mostrar el error únicamente si es necesario. La función FORMATMESSAGE permite formatear el mensaje. El mensaje así formateado deberá ser mostrado por el intermediario de la función RAISERROR. Esta posibilidad de formatear así los mensajes sólo es posible para los mensajes que posean parámetros y que hayan sido definidos con sp_addmessage. Sintaxis FORMATMESSAGE (idmensaje, valorParamétrico,....)

Ejemplo En el ejemplo siguiente el mensaje se ha formateado mediante la instrucción FORMATMESSAGE:

d. Gestión de los errores

En Transact SQL, existen dos medios de gestionar los errores que pueden producirse durante la ejecución de código. 



La primera posibilidad consiste en comprobar el valor de la variable de sistema @@error después de cada instrucción para saber si se ha ejecutado correctamente o no. Esta forma clásica de gestionar los errores obliga a escribir mucho código y reduce la legibilidad de éste. La segunda posibilidad consiste en agrupar una o más instrucciones Transact SQL en un bloque TRY y centralizar la gestión de los errores en un bloque CATCH . Para un tipo de error, se escribe una sola vez el procesamiento necesario aunque puedan ser varias las instrucciones que lo hayan provocado. Esta forma de gestionar los errores es perfectamente conocida para los desarrolladores Java, VC++, C#...

En el bloque CATCH, se pueden utilizar las funciones ERROR_MESSAGE() , ERROR_NUMBER() , ERROR_SEVERITY() , ERROR_STATE() para obtener informació

Gestión de los procedimientos almacenados Los procedimientos almacenados (Stored Procedures) son objetos correspondientes a un conjunto de instrucciones del DML que pueden ejecutarse por medio de una llamada a su nombre o por la instrucción EXECUTE. Son auténticos programas que pueden recibir parámetros, devolver valores, ejecutarse de forma remota, con sus propios derechos de acceso (privilegio EXECUTE). Además, los procedimientos almacenados se guardan en el caché de memoria en forma compilada tras su primera ejecución, lo que aumenta el rendimiento (en las ejecuciones siguientes). Los procedimientos almacenados pueden ser temporales, es decir, creados para una (local) o más sesiones (global) del user. Para SQL Server un procedimiento almacenado puede definirse como una serie de instrucciones Transact SQL, almacenadas en la base de datos y perfectamente identificadas por su nombre. Para permitir que esta serie de instrucciones se adapte al mayor número de casos, algunos valores de las secuencias de comandos son parametrizables en el momento de llamar al procedimiento. Como toda serie de instrucciones Transact SQL, es posible por ejemplo encontrar una instrucción SELECT. La ejecución del procedimiento desencadenará la ejecución de la consulta y el resultado será enviado al entorno que ha solicitado la ejecución del procedimiento. Microsoft proporciona numerosos procedimientos almacenados, que se crean en el momento de instalar los servidores en la base de datos master . Estos procedimientos permiten manipular las tablas de sistema y su nombre empieza por "sp_".

Los distintos usos típicos de procedimientos almacenados son los siguientes:      

Encadenamiento de instrucciones. Mejora del rendimiento. Seguridad de ejecución. Manipulación de los datos del sistema. Implementación de las reglas de negocio. Tratamientos en cascada.

La creación o modificación de los procedimientos almacenados se lleva a cabo con instrucciones del lenguaje de definición de datos o a través del SQL Server Management Studio. Sintaxis CREATE PROC[EDURE] nombre[;número][(param1[,...])][{FOR REPLICATION|WITH RECOMPILE}][WITH ENCRYPTION]AS instrucciones.

nombre Nombre de objeto único en la base de datos. Precedido de un signo #, el procedimiento será temporal local; con dos # será temporal global. número Número de orden para procedimientos con el mismo nombre. param1,... Parámetro en la forma: @nom type [ VARYING ][ = valor ] [ OUTPUT ], puede pasarse al procedimiento. OUTPUT permite especificar un parámetro devuelto por el procedimiento. VARYING especifica el juego de resultado asumido como parámetro de salida. Se aplica únicamente a los parámetros de tipo cursor.

Un procedimiento almacenado puede contener como máximo 2100 parámetros. FOR REPLICATION Permite precisar que el procedimiento se utilizará en el momento de la duplicación. WITH RECOMPILE

El procedimiento será recompilado en cada ejecución. WITH ENCRYPTION Permite cifrar el código en las tablas de sistema. Ejemplos Código de un procedimiento almacenado de sistema mediante el SQL Server Management Studio:

Procedimiento de usuario de eliminación de un cliente (creación):

Procedimiento de usuario de eliminación de un cliente (utilización):

Las funciones definidas por el usuario SQL Server admite tres tipos de funciones de usuario: las funciones escalares, las funciones de valores de tabla en línea y las funciones de valores de tabla de múltiples instrucciones. Una función acepta entre 0 y 1.024 parámetros de entrada y devuelve un valor escalar o una tabla. Es posible definir un valor por defecto para cada uno de los parámetros de la función utilizando la palabra clave DEFAULT. Pero, al llamar a la función, se debe especificar la palabra clave DEFAULT para utilizar el valor por defecto. Este comportamiento difiere del de los procedimientos almacenados, donde el valor por defecto se tiene en cuenta de forma automática si el parámetro no se especifica en el momento de la llamada. Las funciones de tipo escalar devuelven, gracias a la palabra reservada RETURN, un valor escalar. Por supuesto, los datos de tipo timestamp, definidos por el usuario o cursor, no pueden ser devueltos. Es el mismo caso para los tipos de datos text, ntext e image. Las funciones de tablas devuelven como resultado una tabla. No contienen ningún cuerpo y la tabla es el resultado de un solo comando SELECT. Si la función está compuesta de varias instrucciones, las instrucciones están encuadradas por las palabras reservadas BEGIN y END.

Las funciones disponen de un campo de acción limitado, y en ningún caso pueden cambiar su entorno de ejecución. Desde una función no es, pues, posible cambiar el contenido de una tabla de la base de datos. Dentro de una función, las únicas acciones posibles son las que modificarán los objetos locales en dicha función. Desde el momento en que la función ha sido creada por medio de la instrucción CREATE FUNCTION , la cláusula WITH SCHEMABINDING permite vincular la función a todos los objetos a los que ésta hace referencia. Desde ese momento, cualquier modificación (ALTER) o eliminación (DROP) de esos objetos está destinada al fracaso. Esta cláusula, no obligatoria, supone que todos los objetos a los que se hace referencia pertenezcan a la misma base de datos y que el propietario de la función posea un derecho de REFERENCE sobre los objetos a los que hace referencia la función.

1. Creación de funciones Funciones escalares CREATE FUNCTION nombre_función ( [ lista_parámetros] ) RETURNS tipo_datos [ WITH ENCRYPTION| WITH SCHEMABINDING] [ AS ] BEGIN cuerpo de la función RETURN valor END

Funciones de tabla en línea CREATE FUNCTION nombre_función ( [ lista_parámetros] ) RETURNS TABLE [ WITH ENCRYPTION| WITH SCHEMABINDING] [ AS ] RETURN [ ( consulta_SELECT ) ]

Funciones de tabla de múltiples instrucciones CREATE FUNCTION nombre_función ( [ lista_parámetros] ) RETURNS @@variable_retorno TABLE (nombre_columna tipo,...) [ WITH ENCRYPTION| WITH SCHEMABINDING] [ AS ] BEGIN cuerpo de la función RETURN valor END

Ejemplos Creación de la función num_pdo que devuelve la cantidad de pedidos efectuados por un cliente que se toma como parámetro:

Creación de una función de tabla inline que permite conocer los artículos que poseen un precio inferior al tomado como parámetro:

Creación de una función que devuelve una tabla y que permite conocer, la cantidad de pedidos realizados por el cliente que se toma como parámetro y el importe medio de dichos pedidos.

Utilización de la función num_pdo:

Utilización de la función de tipo tabla:

Utilización de la función de tipo tabla de múltiples instrucciones:

Creación de una función con la opción WITH SCHEMABINDING e intento de eliminación de la tabla utilizada por la función:

2. Modificación de una función El comando ALTER FUNCTION acepta los mismos parámetros que CREATE FUNCTION. La diferencia principal estriba en el hecho de que el comando ALTER FUNCTION permite reescribir el cuerpo de una función ya existente en la base, mientras que el comando CREATE permite crear una nueva función. Con el comando ALTER no sólo se puede, por ejemplo, cambiar una simple línea de código, sino que es posible cambiar la definición de la función en su totalidad.

3. Eliminación de una función El comando DROP FUNCTION permite eliminar una función.

Los procedimientos almacenados sp_help y sp_helptext permiten obtener más información sobre las funciones definidas por el usuario.

4. CROSS APPLY y OUTER APPLY El operador Xxxxx APPLY permite relacionar los datos de una tabla/vista de la base de datos con una función de tipo tabla. Con CROSS APPLY, todas las filas de la tabla/vista están presentes en el conjunto de resultados aun si la función de tipo tabla devuelve un valor nulo. Con OUTER APPLY, sólo están presentes en el conjunto de resultados las filas de la tabla/vista para las que la función tabla devuelva un valor no nulo. Ejemplo Relacionar los datos de los clientes (nombre y apellido) con el análisis que se ha realizado de sus pedidos con la función AnalizarCli, que devuelve una tabla.

El contexto de ejecución El contexto de ejecución está directamente relacionado con la conexión y con el usuario de base de datos asociado. El contexto de ejecución permite establecer la lista de las acciones posibles y las que no lo son. Esta lista se realiza a partir de los privilegios otorgados al usuario, ya sea directamente o a través de roles. En algunos casos, puede ser necesario y deseable modificar el contexto de ejecución para beneficiarse de privilegios ampliados, pero únicamente en el marco de una secuencia de comandos, de un procedimiento o de una función. EXECUTE AS

Mediante esta instrucción es posible solicitar la conexión a la base mediante una conexión diferente de la que está en curso. Esta instrucción puede ser ejecutada de forma autónoma en una secuencia de comandos Transact SQL o bien ser utilizada como cláusula en la creación de un procedimiento, una función o un trigger. Para los procedimientos, las funciones y los trigger, la cláusula EXECUTE AS proporciona mucha flexibilidad de programación y permite a un usuario realizar acciones para las que no tiene privilegios. Para el desarrollador, el cambio de contexto de ejecución permite asimismo garantizar que la buena ejecución del código no se verá obstaculizada por un problema de derechos de acceso a los datos.

Durante la ejecución de una secuencia de comandos de Transact SQL, la instrucción EXECUTE AS permite realizar por ejemplo de manera puntual operaciones que necesitan privilegios elevados mientras que el resto de la secuencia de comandos no los necesita. SETUSER

Al contrario de la instrucción EXECUTE AS, que no modifica la conexión inicial al servidor, la instrucción SETUSER permite cambiar de conexión a lo largo de una secuencia de comandos Transact SQL. Es decir, se cierra el contexto actual de ejecución y se abre un nuevo contexto de ejecución. No es posible volver al primer contexto de ejecución. Original_Login

Esta función permite determinar el nombre exacto de la conexión utilizada inicialmente para conectarse al servidor. El conocimiento de dicho nombre no tiene interés a menos que el contexto de ejecución difiera de la conexión inicial. El contexto de ejecución puede ser modificado por el intermediario de la instrucción EXECUTE AS. REVERT

Tras el cambio de contexto de ejecución mediante la instrucción EXECUTE AS, la instrucción REVERT permite volver al contexto de ejecución presente durante el cambio de contexto con EXECUTE AS. Ejemplo En el ejemplo que se muestra a continuación, se define el procedimiento dbo. para mostrar todos los datos relativos a la conexión inicial con el servidor y la conexión utilizada para ejecutar la consulta y por último a la cuenta de usuario utilizada.

Desencadenadores (triggers) SQL Server ofrece dos tipos de desencadenadores (triggers): los DML y los DDL. Los desencadenadores DML existen desde hace tiempo en SQL Server y están presentes en muchas bases de datos. Es el tipo de desencadenador que veremos aquí. Los desencadenadores DDL se basan en el mismo principio: asociar la ejecución de un procedimiento almacenado a a la ejecución de una instrucción. La particularidad de estos desencadenadores es que están asociados a una instrucción DDL, como un comando CREATE, ALTER DROP, GRANT, DENY, REVOKE y UPDATE STATISTICS. El objetivo de estos desencadenadores es seguir la evolución de la base para realizar las tareas administrativas de la mejor forma posible.

Sintaxis CREATE TRIGGER nombre_trigger ON { tabla | vista } [ WITH ENCRYPTION ] {FOR | AFTER | INSTEAD OF } { INSERT , UPDATE ,DELETE } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ IF UPDATE ( columna ) | IF ( COLUMNS_UPDATED ( )operador_comparación_bits)] Instrucciones_SQL

WITH ENCRYPTION La definición del desencadenador está grabada de forma cifrada. Por lo tanto, no es posible conocer el código del desencadenador a posteriori. Esta acción también evita que el desencadenador se publique como parte de una duplicación. FOR Permite especificar a qué orden SQL DML está asociado el desencadenador. Por defecto, el desencadenador es de tipo AFTER. AFTER Es el modo por defecto de los desencadenadores. El código se ejecuta después de la verificación de las restricciones de integridad y después de la modificación de los datos. INSTEAD OF Se ejecuta el cuerpo del desencadenador en lugar de la orden SQL enviada a la tabla o la vista. Este tipo de desencadenador está particularmente adaptado para las vistas. INSERT,UPDATE, DELETE Un desencadenador puede reaccionar ante una o más acciones. En este caso se separan las acciones mediante comas. WITH APPEND Esta cláusula sólo es necesaria si el nivel de compatibilidad de la base es inferior o igual a 65. En ese caso, permite añadir varios desencadenadores en una misma orden SQL y un mismo objeto. Éste es el comportamiento predeterminado a partir de la versión 70. NOT FOR REPLICATION Indica que el desencadenador no debe ejecutarse cuando la modificación de los datos es el resultado de un proceso de duplicación. IF UPDATE (columna)

Sólo puede ser utilizado por los desencadenadores UPDATE o INSERT y únicamente se ejecutará si afecta a la columna o las columna(s). IF (COLUMNS_UPDATED ()operador_comparación_bits) Esta función permite conocer los índices de la columna o columnas que han sido actualizadas. Para cada columna afectada por la actualización, se activa un bit. Para saber qué columnas han sido actualizadas, basta una simple comparación binaria. Instrucciones_SQL Se podrán utilizar todas las instrucciones Transact-SQL de manipulación de datos (DML). Las instrucciones siguientes no están autorizadas:  

CREATE y DROP. ALTER TABLE y ALTER DATABASE.

    

TRUNCATE. GRANT y REVOKE. UPDATE STATISTICS. RECONFIGURE. LOAD DATABASE.

Se pueden definir varios desencadenadores para cada operación INSERT, UPDATE y DELETE. Por defecto, los desencadenadores se ejecutan después (AFTER) de la verificación de las restricciones de integridad y de la inserción de los datos en la tabla. Si la instrucción SQL falla, el desencadenador no se ejecuta.

El procedimiento almacenado sp_helptrigger permite conocer los desencadenadores definidos en una tabla. Los desencadenadores de tipo AFTER sólo pueden establecerse en las tablas; no es posible establecer tales desencadenadores en las vistas. Es posible crear varios desencadenadores after para una misma tabla y una misma orden SQL (insert, update o delete) de desencadenamiento. Si existen varios desencadenadores, el procedimiento almacenado sp_settriggerorder permite fijar el desencadenador que se ejecutará en primer lugar y el que se ejecutará el último. Los demás desencadenadores se ejecutarán siguiendo un orden aleatorio.

Si una tabla posee una acción en cascada, no es posible establecer un desencadenador INSTEAD OF. SQL Server puede autorizar la recursividad de los triggers si la opción de base de datos ‟recursive triggers‟ ha sido activada por medio de ALTER DATABASE. Esta opción permite dar mayor potencia a los desencadenadores, pero a veces puede ser peligroso utilizarla. De entre las recursiones posibles, debemos distinguir la recursión directa de la recursión indirecta. La recursión directa está gestionada por el parámetro recursive trigger. Autoriza, por ejemplo, a un desencadenador establecido sobre la tabla CLIENTES y asociado a la orden INSERT, a contener una orden INSERT sobre esta misma tabla CLIENTES. Esta segunda orden INSERT provoca la ejecución del desencadenador. La recursión indirecta está gestionada por el parámetro de servidor nested triggers del procedimiento sp_configure. Para ilustrar la recursión indirecta, debemos considerar que un desencadenador establecido sobre la tabla de PEDIDOS y asociado a la orden INSERT provoca una orden INSERT en la tabla de CLIENTES, y que el desencadenador asociado provoca a su vez una orden INSERT sobre la tabla de PEDIDOS.

Para evitar cualquier bloqueo definitivo, los desencadenadores no pueden contar con más de 32 niveles de anidamiento (o anidación). Al modificar datos, SQL Server crea filas en tablas de trabajo con la misma estructura que la tabla modificada: las tablas inserted y deleted. En una consulta INSERT, la tabla inserted contiene una copia lógica de las filas creadas. En una consulta DELETE, las filas suprimidas se colocan en la tabla deleted. En una consulta UPDATE, las filas con las modificaciones se colocan en la tabla inserted y las filas que es preciso modificar, en la tabla deleted. Puede accederse a las tablas inserted y deleted durante la ejecución del trigger. Ejemplos Creación automática de una factura al cambiar el estado del pedido:

Solicitud de creación de un desencadenador en la tabla PEDIDOS desde el SQL Server Management Studio:

Creación de un primer desencadenador en la tabla PEDIDOS:

Creación de un segundo desencadenador en la tabla PEDIDOS para calcular la tasa de descuento:

Conocer los desencadenadores establecidos en la tabla de PEDIDOS:

Fijar el primer y el último desencadenador que se ejecutan en cada operación INSERT sobre la tabla PEDIDOS:

Introducción La gestión de los datos se lleva a cabo cada vez más en entornos de red heterogéneos, es decir, con varios servidores SQL Server que deben comunicarse entre sí, o con datos no almacenados en SQL Server instalados en puestos clientes, o incluso con aplicaciones que manejan datos de varios servidores SQL y de otros gestores de datos (Access, Oracle...). Estos entornos pueden presentar muchos problemas, entre los cuales cabe destacar los siguientes:     

la disponibilidad para los usuarios de todos los datos, la concurrencia de acceso a los datos, la coherencia y la integridad de los datos, la seguridad de acceso a los datos, el rendimiento de los servidores de datos.

Las diferentes funciones del administrador y del implementador de bases de datos de SQL Server consisten en:    

asegurar la copia y la transferencia de los datos entre varias bases de datos o servidores, establecer la coherencia de los datos distribuidos en varias bases de datos, exportar e importar datos hacia gestores no SQL Server, permitir a los usuarios de un servidor el acceso a los datos de otro servidor.

Los desarrolladores de aplicaciones que utilizan SQL Server cumplirán también una función en esta gestión de datos distribuidos por medio de:  

la gestión de la integridad de los datos entre varios servidores a los que accede la misma transacción, el acceso de las aplicaciones a datos heterogéneos (SQL Server y otros sistemas de gestión de datos).

SQL Server 2008 propone toda una gama de soluciones para asegurar estas diferentes funciones: Para el administrador:     

SSIS permite la copia de datos de base a base o de servidor a servidor, la distribución de los datos sobre medios removibles, que permite instalar una base en un CD-ROM o disco removible, la duplicación asíncrona, que permite que las modificaciones realizadas en una base repercutan automáticamente en otras bases, el programa BCP, que exporta datos hacia archivos del sistema operativo o los importa, los procedimientos almacenados remotos, que permiten al usuario de un servidor ejecutar un procedimiento en otro servidor.

Para el desarrollador:  

el "commit" en dos fases, que permite asegurarse de que una transacción que utilice varios servidores se valide correctamente en todos los servidores. las API (Application Program Interface) del servidor y del cliente, que proporcionan al desarrollador las funciones para las conexiones, el acceso a los datos o las pasarelas hacia otros entornos.

SQL Server Integration Services Con SQL Server Integration Services (SSIS) , SQL Server 2005 ofrece un conjunto de utilidades y técnicas potentes y fáciles de utilizar para importar un gran volumen de datos hacia una base de datos. La extracción, la transformación y la carga de datos (ETL, Extract, Transform and Load) son los tres cometidos de Integration Services. Integration Services puede utilizarse para recuperar datos almacenados en diferentes formatos para su integración en una base de datos SQL Server. También puede servir para consolidar en una base de datos de tipo DataWareHouse informaciones procedentes de varias fuentes SQL Server, por ejemplo. Además, Integration Services facilita transferir

datos de una base a otra y aprovechar esta transferencia para reorganizar los datos de cara a las necesidades del futuro. La herramienta DTS de las anteriores versiones de SQL respondía en parte a esta necesidad, pero las dificultades que surgían en la programación de bucles y la delicada gestión de grandes volúmenes justificaban el desarrollo desde Visual Studio por ejemplo. Con SSIS, es mucho más fácil llevar a cabo estas tareas y es mucho menos necesario recurrir a una utilidad de desarrollo propio. SQL Server Integration Services se puede utilizar también para realizar tareas administrativas como la salvaguarda de las tablas, de las vistas, de los procedimientos... De esta manera es posible automatizar dichas tareas y planificar su ejecución. SSIS también puede ser utilizado por el administrador para incorporar datos existentes en archivos planos sobre la actual base de datos.

1. Principios de funcionamiento El servicio de integración de SQL Server 2005 (SSIS) se basa en estos 4 elementos principales: 



 

El servicio SSIS propiamente dicho, con el que SQL Server Management Studio puede seguir la ejecución de paquetes en curso y gestionar el almacenamiento de paquetes. El modelo de objetos (Object Model) de SSIS en forma de API permite acceder a las herramientas SSIS, trabajar con utilidades de línea de comandos y personalizar las aplicaciones. El runtime SSIS permite la ejecución de paquetes y gestiona todos los elementos asociados: registros, configuración de conexiones, transacciones... El flujo de datos. Este componente integra el motor de gestión de datos que lee la información desde la fuente y la almacena en búferes de memoria antes de escribirla en su destino. Las transformaciones se aplican a los datos almacenados en búferes antes de escribirlos en su destino.

El siguiente esquema ilustra la integración y las relaciones que pueden existir entre estos 4 componentes en la construcción de SSIS:

SSIS soporta igual de bien el código nativo que el generado por el Framework. Por ejemplo, se pueden escribir tareas de transformación personalizadas en C++ y producir con ellas un ejecutable, o bien en C# y producir código gestionado por el Framework .NET.

2. Herramientas gráficas Ya que el servicio de integración de datos puede utilizarse tanto en bases OLAP como OLTP, puede trabajarse con SSIS perfectamente desde SQL Server Management Studio para bases OLTP y desde Business Intelligence Development Studio para las soluciones de reporting. Ambas herramientas gráficas se reparten las operaciones realizables a nivel de SSIS. a. Business Intelligence Development Studio

Esta utilidad permite definir paquetes y personalizarlos. Desde esta consola se crean e implementan los paquetes SSIS. En consecuencia, se pueden efectuar las operaciones siguientes:      

Ejecutar el asistente de importación y de exportación para diseñar paquetes simples. Crear paquetes complejos que integren operadores de control de flujo, lógica de negocios... Probar y poner a punto los paquetes. Definir la configuración del paquete que permitirá actualizar las propiedades de la solución empaquetada. Empaquetar la solución para instalar el paquete y los elementos que dependen de los otros puestos. Guardar el paquete en la base msdb y el sistema de archivos.

b. SQL Server Management Studio

En el nivel de SSIS, SQL Server Management Studio representa una consola de gestión de paquetes y de puesta en producción. Permite realizar las siguientes operaciones:    

Crear carpetas para organizar lógicamente los distintos paquetes SSIS. Ejecutar paquetes almacenados localmente por medio de la utilidad Ejecutar Paquete. Ejecutar la utilidad Ejecutar Paquete para generar la línea de comandos para la utilidad dtexec. Importar y exportar paquetes desde y hacia la base msdb y el sistema de archivos.

3. Asistente de importación y exportación El asistente de importación y exportación permite definir rápida y simplemente paquetes SSIS que no realicen transformaciones complejas. El asistente permite responder rápidamente a los problemas más sencillos y suministra una primera plataforma de trabajo para diseñar paquetes más complejos con SSIS Designer. Para definir un paquete SSIS, el asistente deberá definir los siguientes elementos:





La fuente y el destino de los datos. Después del tipo de fuente y de destino, puede pedir opciones de configuración como el delimitador de campo para la exportación en un archivo plano. Validar y eventualmente modificar las correspondencias entre la fuente y el destino.

Sólo queda guardar el paquete SSIS en la base de datos MSDB o en forma de archivo. Si el asistente se ejecuta desde SQL Server Management Studio, se puede ejecutar de inmediato. Para ejecutar el asistente desde SQL Server Management Studio, hay que realizar las siguientes manipulaciones: 1. Desde el explorador, seleccione la base de datos de destino de la importación o de la exportación. 2. Desde el menú contextual asociado a la base de datos, seleccione Tareas - Importar datos o bien Tareas - Exportar datos.

Para ejecutar el asistente desde Business Intelligence Development Studio, hay que realizar las manipulaciones siguientes: 1. Desde el menú Archivo - Abrir, seleccione el proyecto Integration Services en el que va a trabajar el asistente. Ello presupone que el proyecto ya existe. Si la operación es puntual, no es necesario crear un proyecto, sino que es preferible pasar por SQL Server Management Studio. 2. Seleccione la carpeta Paquetes SSIS y seleccione la opción Asistente para importación y exportación de SSIS en el menú contextual. Ejemplo El ejemplo siguiente ilustra el uso del asistente para importar datos desde un archivo de texto. De entrada, aparece la pantalla de bienvenida:

El origen de datos seleccionado es de tipo archivo plano; se especifican los elementos relacionados con la estructura de los datos: el separador de filas, el de columnas... se puede configurar y ver cómo el origen se interpreta en función de los parámetros escritos seleccionando las vistas Columnas, Avanzadas y Vista previa.

Desde la vista Avanzadas, el botón Sugerir tipos permite analizar las primeras filas del archivo origen para determinar con la mayor exactitud posible los tipos de datos de la tabla de destino. De manera predeterminada, solamente se analizan las 100 primeras filas, pero es posible precisar el número de filas que analizar.

El destino se selecciona en el paso siguiente. Después de seleccionar la instancia SQL Server a la que hay que conectarse, se puede crear una nueva base de destino por medio del botón Nueva.

A continuación el asistente ofrece la lista de asociaciones realizadas entre origen y destino. La complejidad de esta pantalla depende del tipo de origen seleccionado (OLE-DB, SQL Server...).

Pulsando el botón Editar asignaciones, se puede ver en detalle la correspondencia en el nivel de cada columna. Por ejemplo, las columnas se renombran para darles un nombre significativo.

A continuación, el asistente pregunta si se desea ejecutar el paquete de inmediato. También se puede guardar su definición en la base de datos msdb o en forma de archivo. En caso de que se grabe el paquete, es necesario especificar el nivel de seguridad deseado.

Después hay que dar un nombre y una descripción al paquete, además de indicar la instancia de SQL Server en la que se grabará.

Antes de salir del asistente, se presenta un resumen de las opciones elegidas y las tareas que se van a realizar.

Durante la ejecución del paquete se puede seguir el avance de las operaciones.

4. SSIS Designer Con este diseñador de paquetes SSIS, se pueden definir paquetes complejos. Aunque se pueda diseñar todo el paquete desde cero, parece razonable partir cuando sea posible de uno creado con el asistente y personalizarlo. SSIS Designer está disponible desde Business Intelligence Development Studio cuando se trabaja en un proyecto de tipo Integration Services. Para crear un proyecto de este tipo en Business Intelligence Development Studio, hay que seleccionar Archivo - Nuevo - Proyecto en el menú. En la ventana de creación de nuevo proyecto, hay que seleccionar un proyecto de tipo Integration Services y escribir su nombre.

Una vez creado, el proyecto aparece directamente SSIS Designer con un paquete predeterminado. SSIS Designer siempre se compone de las cuatro fichas siguientes:    

Flujo de control Flujo de datos Controladores de eventos Explorador de paquetes

Durante la ejecución de un paquete aparece una quinta ficha para seguir el avance de la ejecución y visualizar su resultado. SSIS Designer dispone también de una ventana para gestionar las conexiones con los distintos orígenes de datos a los que accede el paquete. La siguiente pantalla ilustra la estructura de SSIS Designer:

En la parte izquierda de la pantalla, Business Intelligence Development Studio ofrece un cuadro de herramientas para definir paquetes SSIS. Este cuadro de herramientas permite desarrollar fácil y rápidamente un paquete. Después de seleccionar un elemento, hay que arrastrarlo hacia Control de flujo o la hoja activa (Flujo de datos, por ejemplo). Hay que configurar cada elemento editando sus propiedades por medio de la opción Editar del menú contextual. Para asignar una conexión a un componente, basta seleccionar la conexión desde la ventana Administradores de conexión y arrastrarla sobre el componente.

También es fácil desarrollar un paquete como el que ilustra el ejemplo siguiente:

El componente de envío de email está configurado de esta forma:

La tarea de transferencia de datos se detalla en la ficha Flujo de datos. En este caso, la barra de herramientas de SSIS también presenta las opciones estándar para este nivel.

SSIS también ofrece los siguientes asistentes:  



Asistente de instalación de paquetes: para guiar durante las etapas de despliegue y configuración de los paquetes. Asistente de configuración de paquetes: para crear una configuración que permita actualizar las propiedades de un paquete y de los objetos en el momento de ejecutarlo. Asistente para migración de paquetes: ayuda a convertir los paquetes de formato DTS en paquetes de formato SSIS.

5. Otras utilidades a. Ejecutar Paquete

Esta utilidad permite configurar y ejecutar paquetes en el ordenador, así como generar la línea de comandos para una utilización con dtexec . Para acceder a esta utilidad hay que conectarse desde SQL Server Management Studio a un servidor de tipo Integration Services, como se indica en el ejemplo siguiente.

Se puede abrir una conexión nueva a través del menú Archivo - Conectar con Explorador de objetos.

A continuación, la utilidad pedirá la ejecución del paquete a partir del menú contextual, tal y como ilustra el ejemplo siguiente:

Entonces se pueden definir las opciones de ejecución del paquete.

Se puede acudir a esta utilidad desde la línea de comandos escribiendo dtexecui. b. Utilidades de línea de comandos dtexec

Esta utilidad permite ejecutar un paquete SSIS desde la línea de comandos. Se puede configurar el uso del paquete por medio de las diferentes opciones de dtexec. dtutil

Esta utilidad de línea de comandos permite gestionar los paquetes existentes que estén almacenados en la base de datos msdb o en forma de archivos. También permite copiar, eliminar, renombrar o sencillamente verificar la existencia de un paquete. Aunque sea relativamente implícito para una utilidad de línea de comandos, es preferible gestionar los paquetes SSIS desde SQL Server Management Studio siempre que sea posible. Esta utilidad no soporta el uso de un archivo de comandos ni la redirección a un archivo.

Modificar una base de datos Los archivos de datos y de registro de transacciones se pueden adjuntar y separar de la base de datos SQL Server. Esta operación permite desplazar rápida y simplemente una base de datos desde una instancia SQL Server a otra situada en el mismo servidor o en otro. También se puede aprovechar el momento en que la base de datos está separada para copiar los archivos y adjuntarla inmediatamente al servidor de origen y adjuntar una copia a un servidor de pruebas o redundante. De hecho, este procedimiento es mucho más rápido que el de crear una copia de seguridad y restaurarla.

Los procedimientos sp_create_removable, sp_attach_db se mantienen por motivos de compatibilidad.

1. Separar una base de datos Al separar una base de datos de su servidor, los archivos de datos y registro de transacciones se conservan en el mismo estado. La base de datos puede volver a adjuntarse a un servidor SQL Server, que, naturalmente, puede ser el mismo del que anteriormente se ha separado.

Esta operación se puede hacer para cualquier base de datos de usuario siempre que se encuentre en un estado correcto (es decir, que no esté, por ejemplo, en pleno proceso de restauración) y que no esté involucrada en operaciones administrativas a nivel de la base de datos, como un proceso de duplicación por ejemplo. a. Desde SQL Server Management Studio

Es la solución más sencilla para separar una base de datos. Debe seleccionar la opción Tareas - Separar desde el menú contextual asociado a la base de datos.

Entonces aparece el cuadro de diálogo para separar la base de datos. Se pueden habilitar tres opciones en otras tantas casillas:   

Quitar conexiones: si hay usuarios conectados, habilitando esta casilla se pondrá fin a su conexión. Actualizar estadísticas: se recomienda actualizar las estadísticas de las tablas antes de utilizarlas. Mantener catálogos de texto: esta opción, que está habilitada de forma predeterminada, permite conservar los catálogos de texto integral en la base de datos separada.

En el siguiente ejemplo, las casillas Quitar conexiones y Actualizar estadísticas están habilitadas para cerrar las conexiones existentes y actualizar las estadísticas antes de separar la base de datos.

Después del proceso, la base de datos habrá quedado separada. Ya sólo faltará, si es necesario, desplazar los archivos por medio de instrucciones de manipulación de archivos. b. Desde Transact SQL

En Transact SQL, hay que utilizar el procedimiento sp_detach_db . sp_detach_db [ @dbname= ] ’base’ [ , [ @skipchecks= ] {’true’|’false’}] [ , [ @KeepFulltextIndexFile= ] {’true’|’false’} ]

@dbname Nombre de la base de datos a separar. @skipchecks

¿Hay que actualizar las estadísticas o no? La opción predeterminada es NULL. Si se especifica false, se ejecutará el comando UPDATE STATISTICS; si se selecciona true, las estadísticas no se actualizarán. De forma predeterminada, se actualizan las estadísticas de tablas e índices. Esta opción es especialmente importante para las bases de datos. @KeepFullTextIndexFile ¿Hay que conservar los catálogos de texto o no? Ejemplo Separamos la base de datos Gescom:

2. Adjuntar una base de datos Esta operación puede realizarse con una base de datos que se haya separado correctamente a través del procedimiento que ofrece SQL Server Management Studio o mediante sp_detach_db. a. Desde SQL Server Management Studio

La primera operación consiste en seleccionar la opción Adjuntar desde el menú contextual asociado a la carpeta Bases de datos.

Desde la ventana de transacciones Adjuntar bases de datos, hay que localizar los archivos de datos y registro de transacciones de la base de datos a adjuntar. A partir del archivo MDF, el asistente localiza el archivo de registro de transacciones y los demás archivos de datos.

Después de ejecutar la operación, la base de datos vuelve a ser accesible para los usuarios. b. Desde Transact SQL

Hay que utilizar la instrucción CREATE DATABASE con la opción FOR ATTACH. CREATE DATABASE nombreBaseDeDatos ON <descripciónArchivo> [ ,...n ] FOR { ATTACH | ATTACH_REBUILD_LOG }

nombreBaseDeDatos

Nombre de la base de datos a adjuntar. descripciónArchivo Descripción del archivo a adjuntar: se especifican el nombre lógico y el nombre físico a través de las opciones NAME y FILENAME. FOR ATTACH La base de datos se adjunta en este estado. FOR ATTACH_REBUILD_LOG Si falta un archivo de registro o es erróneo, la base de datos lo reconstruye en vacío. Ejemplo La base de datos GESCOM se adjunta a partir de los archivos presentes:

3. Mover una base de datos o archivos Por medio de operaciones de separar y adjuntar, se pueden desplazar físicamente los archivos de la base de datos y reorganizar su estructura física.

El programa BCP BCP (Bulk Copy Program) es una utilidad de importación de datos desde un archivo del sistema operativo hacia una tabla, existente o no, y de exportación de los datos desde una tabla o una vista hacia un archivo del sistema operativo. BCP se utilizará principalmente en los casos siguientes:   

Interfaz con sistemas de gestión de archivos distintos de SQL Server. Volúmenes de transferencia importantes entre dos servidores SQL Server. Creación de archivos que contengan datos para otras aplicaciones (administrador de informes, programas que no utilicen SQL...).

Las características de las transferencias son las siguientes: 

La importación de los datos es dinámica: otros usuarios pueden acceder a la base durante la ejecución de bcp.





 

La exportación es estática; al utilizar BCP una imagen fija de la base de datos, es preferible, para mantener la coherencia de los datos, que los usuarios no accedan a la base durante la transferencia. Se efectúa un BCP "rápido" si la tabla receptora no contiene índices ni restricciones y si la opción select into/Bulkcopy está en ON. Si ninguna de estas condiciones se cumple, se lleva a cabo un BCP "lento". Los valores por defecto y los tipos de datos se tienen en cuenta. Los desencadenadores y las restricciones se ignoran.

Es interesante combinar osql y bcp en un proceso por lotes. Osql puede eliminar y volver a crear posteriormente los índices y las restricciones y validar los datos; bcp asegura la transferencia. Aunque BCP es una herramienta con un buen rendimiento, las soluciones de importación y exportación en línea de comandos pueden realizarse definiendo un paquete SSIS guardado en un archivo e iniciado por medio de la utilidad dtsrun . Sintaxis Desde la línea de comandos. bcp {nombretabla | "consulta"} {in | out | queryout | format} archivo_datos [-mnúmero_max_errores] [-fformato] [ -x] [ -earchivo_error] [-Fprimera_fila] [-Lúltima_fila] [-btamaño_batch] [-n] [-c] [-w] [-N] [-V (60 | 65 | 70| 80 | 90)] [-6] [-q] [-C { ACP | OEM | RAW | página_código }] [-tfin_campo] [-rfin_fila] [-iarchivo_entrada] [-oarchivo_salida] [-atamaño_paquetes] [-Snombre_servidor[\nombre_instancia]] [-Unombre_usuario] [-Pcontraseña] [-T] [-v] [-R] [-k] [-E] [-h "opción [,...n]"]

nombretabla |"consulta" Nombre de la tabla, de la vista o de la consulta SQL utilizada. in | out |queryout | format ¿Se trabaja en importación, en exportación, desde una consulta de tipo SELECT o utilizando un archivo de formato? archivos_datos Nombre completo del archivo que va a utilizar bcp. -m num_max_errores

Número máximo de errores posibles antes de que la copia de datos sea cancelada. -f formato Nombre completo del archivo que contiene las respuestas guardadas de una utilización anterior de bcp. -x Utilizado conjuntamente con las opciones format y f, permite trabajar con un archivo de datos de formato XML. -e archivo_error Nombre completo del archivo que contiene los errores. -F primera_fila Por defecto, se utiliza la primera fila del archivo. -L última_fila Número de la última fila del archivo que se debe tener en cuenta al importar datos desde dicho archivo. Por defecto, se trata del valor 0, que corresponde a la última fila del archivo. -b tamaño_batch Número de filas por paquete de datos copiado. -n Copia por bloque utilizando los tipos de datos por defecto. -c Copia por bloque utilizando el tipo carácter. -w Copia por bloque utilizando los caracteres unicode. -N Copia por bloque utilizando los tipos de datos por defecto para los datos que no son de tipo carácter y el tipo unicode para los datos de tipo carácter.

-V (60 | 65 | 70 | 80 | 90) Copia en bloque utilizando los tipos de datos de las versiones anteriores de SQL Server. -6 Parámetro existente sólo por motivos de compatibilidad. Es preferible utilizar la opción V. -q Interpretación de las comillas. -C { ACP | OEM | RAW | página_código } Este parámetro permite precisar el tipo de codificación utilizado para los caracteres. ACP para utilizar la página ANSI/Windows n°1252. OEM permite utilizar la página predeterminada del cliente. Es la opción predeterminada si no se especifica la opción -C. RAW permite no realizar ninguna conversión entre la página de códigos del servidor y la de destino. página_códigos representa el número de la página de códigos a utilizar. -t fin_campo Marca de fin de campo. -r fin_fila Marca de fin de fila. -i archivo_entrada Nombre del archivo que contiene las respuestas a las preguntas para cada campo, en el caso de una copia por bloque en modo interactivo. -o archivo_salida Nombre del archivo que recibe las salidas bcp. -a tamaño_paquetes Número de bytes por paquete de red enviado. -S nombre_servidor [\nombre_instancia] Nombre del servidor y de la instancia. -U nombre_usuario

Nombre que el usuario de SQL Server ha utilizado para conectarse. -P contraseña Contraseña del usuario SQL Server. -T Utilizar una conexión de confianza. -v Conocer el número de versión bcp. -R Utilizar el formato regional para los datos de tipo fecha y hora y los datos monetarios. -k No utilizar los valores por defecto si la columna insertada contiene NULL. -E Tomar en cuenta una columna de tipo identidad en el archivo de datos de importación. -h "opción [,...n]" Opciones que es preciso especificar con vistas a una exportación hacia una versión 6.5 o anterior de SQL Server. Ejemplo Exportación hacia un archivo ASCII cuyo separador de campos es la coma y cuyo carácter separador de registros es un retorno de carro:

Los servidores remotos Un servidor remoto es un servidor que forma parte de una red y al que los usuarios pueden acceder por medio de su servidor local. Su interés radica en que gestiona las conexiones en modo local y al tiempo que permite a los usuarios ejecutar procedimientos almacenados en el servidor remoto.

Estos usuarios se denominan "usuarios remotos" y deben contar con un nombre de acceso en el servidor remoto. Antes de poder utilizar esta funcionalidad, habrá que añadir los servidores remotos, configurarlos y gestionar los usuarios remotos. Esta configuración podrá hacerse en el SQL Server Management Studio, visualizando las propiedades de un servidor (ficha Conexiones, área Conexiones a servidores remotos).

1. Gestión de servidores remotos Para trabajar con servidores y datos remotos, el método más sencillo en SQL Server consiste en establecer un vínculo con el servidor remoto. Esta conexión permite identificar con exactitud al servidor remoto y cómo se abre la sesión de usuario. La gestión de estos servidores puede realizarse tanto desde Transact SQL como desde SQL Server Management Studio. Todas las informaciones relativas a estas inscripciones se guardan en la tabla sys.servers. Se puede interrogar al procedimiento sp_linkedservers para obtener todos los datos relativos a los servidores vinculados. a. Agregar un servidor vinculado

Para agregar un servidor vinculado a través de SQL Server Management Studio, seleccione la opción Nuevo - Servidor vinculado en el menú contextual asociado a la carpeta Objetos de servidor.

En Transact SQL, hay que utilizar el procedimiento almacenado sp_addlinkedserver. sp_addlinkedserver [ @server= ] ’nombreDeServidor’ , [ @srvproduct= ] ’SQL Server’ ]

@server Nombre del servidor SQL Server con el que se establece el vínculo. Los datos ofrecidos aquí conciernen únicamente al vínculo entre servidores SQL Server.

b. Eliminar un servidor vinculado

Esta operación puede realizarse por medio del procedimiento almacenado sp_dropserver o desde SQL Server Management Studio seleccionando la opción Eliminar en el menú contextual asociado al servidor vinculado.

En transact SQL, hay que utilizar el procedimiento almacenado sp_dropserver. sp_dropserver [ @server = ] ’nombreDeServidor’ [ , [ @droplogins = ] { ’droplogins’ | NULL} ]

@server Nombre del servidor SQL Server con el que se ha establecido un vínculo. @droplogins Permite especificar que también se eliminen las conexiones asociadas al vínculo.

2. Gestión de usuarios remotos Cuando es posible, y en la medida en que la solución reposa únicamente en instancias SQL Server, es preferible utilizar la autenticación de Windows. Así, los derechos asignados directa o indirectamente al usuario permiten controlar su trabajo. Si ello no es posible, hay que definir cuentas de seguridad SQL Server para la correspondencia entre conexiones locales y una cuenta de conexión remota. Esta tarea puede llevarse a cabo desde SQL Server Management Studio o a través de los procedimientos Transact SQL sp_addlinkedsrvlogin y sp_droplinkedsrvlogin. Todas las operaciones realizadas en el servidor remoto se ejecutan respetando los privilegios asignados al usuario definido localmente para este servidor remoto con cuyo nombre se ha abierto una sesión. Se pueden definir correspondencias de seguridad desde la ventana Nuevo servidor vinculado de SQL Server Management Studio.

3. Uso de recursos remotos Para trabajar con una tabla o un objeto remotos, hay que utilizar el nombre completo del objeto (nombreBase.nombreEsquema.nombreObjeto) precedido por el nombre del servidor remoto.

4. Los sinónimos La manipulación de los objetos remotos no es algo sencillo y los nombres relativamente largos sólo dificultan la comprensión de las consultas que hacen referencia a estos objetos. Para simplificar esta sintaxis y trabajar de forma más sencilla con los objetos remotos, SQL Server propone la noción de sinónimo . Como en francés, un sinónimo en el sentido de SQL Server consiste en atribuir un segundo identificador para referenciar al mismo objeto. En el caso del ejemplo anterior, sería posible definir el sinónimo clientesRemotos para referenciar la tabla SERV01.gescom.dbo.clientes. Los sinónimos SQL Server no se limitan a los objetos remotos, sino que también permiten definir términos alternativos para las tablas, vistas, procedimientos, funciones... para adaptar los objetos de la base de datos al vocabulario de los usuarios. Por ejemplo, hay quienes hablan de artículos y otros de productos, igual que se habla de asalariados, empleados y colaboradores. Al ofrecer la posibilidad de tener varios términos para hacer referencia al mismo objeto, la estructura de la base de datos se vuelve totalmente adaptable. Sintaxis CREATE SYNONYM nombreSinónimo FOR object;

Ejemplo El sinónimo para la tabla clientes alojada en el servidor SERV01 se define de la siguiente manera.

Por supuesto, los sinónimos son eliminados por la instrucción DROP SYNONYM nombreSinónimo. Cuando se trabaja con un sinónimo, a veces es interesante saber el tipo de base del sinónimo: es decir, identificar el objeto del cual es sinónimo. El primer reflejo para obtener este tipo de información es sin ninguna duda recorrer las tablas del sistema y en particular la vista sys.synonyms. Pero también es posible recurrir a la función OBJECTPROPERTYEX , que permite identificar rápidamente el tipo de base para el sinónimo indicado en el parámetro.

La administración de las transacciones distribuidas Una transacción distribuida es un conjunto de instrucciones de una aplicación que actualizan los datos en, al menos, dos servidores. Para prevenir la aparición de problemas de integridad y de coherencia de datos en caso de validación incompleta, deben implementarse medios de prevención y corrección de errores. Con SQL Server, estos medios son, por una parte, el servicio MS DTC (Distributed Transaction Coordinator), en los servidores o los clientes WINDOWS, y por otra, el Transact-SQL y los procedimientos almacenados remotos. Por otro lado, se pueden distinguir tres tipos de participantes en la implementación de esta funcionalidad, a saber: 





la aplicación, que desencadena la transacción y pide las actualizaciones a los diferentes servidores. Son programas que utilizan las API, Embedded SQL, OLE DB u ODBC, o scripts en Transact-SQL, el Coordinador de transacciones, que coordina los diferentes servidores. Esta función es asegurada por MS DTC, según el protocolo de validación de dos fases (Two Phase Commit), el administrador o los administradores de recursos, que son los servidores en los cuales se instalan las bases de datos en cuestión.

1. MS DTC El servicio Distributed Transaction Coordinator puede ser iniciado por el administrador de servicios de WINDOWS 2000 o por el Administrador de servicios SQL Server en los servidores que deben poder asumir la función de coordinador de transacciones.

2. Implementación en Transact-SQL Una transacción distribuida en Transact-SQL quedará determinada por el uso de BEGIN DISTRIBUTED TRANSACTION y la ejecución de procedimientos almacenados remotos. Hay que observar que si una instrucción ROLLBACK TRAN interviene antes que COMMIT TRAN , toda la transacción, local y remota, se anula. La instrucción SAVE TRANSACTION no se permite en estas transacciones. Ejemplo Ejecución de una transacción en el servidor BRUNO, que actualiza un cliente en una base local y efectúa inmediatamente la misma actualización en una base de SRV01 (el

procedimiento DIRECCLI recibe el número del cliente que se debe modificar y su nueva dirección).

Se puede configurar el servidor o la sesión para llevar a cabo una ejecución sistemática de los procedimientos almacenados remotos en transacción distribuida mediante sp_configure remote proc trans o set remote_ proc_transactions.

Introducción Los datos utilizados en el contexto de las aplicaciones no cesan de evolucionar. Por tanto es normal que los servidores de bases de datos también evolucionen proponiendo tipos adaptados a estos nuevos formatos. Es lo que hace SQL Server al ofrecer la posibilidad de almacenar datos en formato xml y datos geográficos, además de una mejor gestión de los documentos anexos (imagen, vídeo, sonido, documento digitalizado…) para no recargar el proceso de administración de la base pero vinculando los datos relacionales a estas informaciones almacenadas directamente en el sistema de archivos.

Trabajar con el formato XML Los datos en formato XML están cada vez más presentes en el entorno de trabajo. Por tanto es normal que una base de datos se adapte para estar en situación de almacenar y administrar de manera óptima los datos definidos en este formato. Esto es lo que hace SQL Server al ofrecer la posibilidad de trabajar directamente con datos en formato XML y almacenarlos en la estructura relacional de una tabla. Puesto que XML representa ante todo un formato de intercambio de datos, SQL Server propone igualmente las herramientas necesarias para producir un documento XML a partir de datos relacionales o viceversa, integrar en las tablas relacionales datos extraídos de un documento XML. Se pueden almacenar datos en formato relacional o en formato XML. Cada uno de estos dos formatos tiene sus ventajas y sus inconvenientes. SQL Server alberga un motor relacional para almacenar y trabajar con datos guardados con este formato. Sin embargo, SQL Server también ofrece la posibilidad de gestionar los datos en formato XML. De esta forma, sea cual sea el modo de almacenamiento utilizado, SQL Server puede guardar los datos en formato nativo. El objetivo de SQL Server es adaptarse al modo de almacenamiento de los datos en función del formato con el que trabaje la aplicación cliente.

Microsoft Office 2007 permite que los usuarios de Word, Excel Visio e Infopath guarden sus documentos en formato XML, mediante el formato OpenXML. El siguiente esquema ilustra el hecho de que las aplicaciones trabajan tanto con datos de formato relacional como con formato XML.

Para responder correctamente a distintas necesidades, SQL Server ha mejorado considerablemente su gestión del formato XML. Elección de un formato

Los dos formatos no compiten entre sí, sino que se complementan. El motor de base de datos debe tener la capacidad de gestionar los datos de forma óptima con independencia de su formato de almacenamiento. XML se adapta especialmente bien al intercambio de datos entre aplicaciones, a la gestión de documentos, al envío de mensajes (SOAP)... XML presenta la ventaja de ser autodescriptivo. También ofrece la posibilidad de transferir estructuras complejas de datos. Esta representación de datos se realiza en forma de árbol. No obstante, el formato relacional permite garantizar una mejor homogeneidad de los datos, ya que las tablas están muy estructuradas. La estructura en tablas de datos permite almacenar un gran volumen de información de forma fiable, y las consultas permiten extraer datos de forma rápida y eficaz. Sin ninguna duda, éste es el mejor formato para almacenar un gran volumen de información y trabajar con él. La siguiente tabla indica cuál de los dos formatos es mejor en función de la estructura inicial de los datos: Formato de datos Archivo plano Estructura jerárquica Datos semiestructurados Lenguaje de descripción Conservar el orden Recursividad

XML Bien adaptado Bien adaptado Bien adaptado Bien adaptado Bien adaptado Bien adaptado

Relacional Bien adaptado Posible Posible Posible Posible Posible

1. El tipo XML SQL Server ofrece un tipo de datos XML para almacenar los datos en formato nativo XML. Este tipo no es un campo de texto de grandes dimensiones, como nvarchar(max) por ejemplo. Si no existen motivos puramente relacionados con el almacenamiento que lo

impidan, el tipo XML permite realizar consultas de información precisas. También se pueden definir índices en las columnas XML para acelerar el procesamiento de las consultas. Se pueden crear tablas relacionales que, además de las columnas de los tipos habituales, contengan una o más columnas de tipo XML. Las columnas de tipo XML utilizan formato binario (blob) para almacenar la información en la base de datos relacional, de forma que el documento XML se conserva en el estado correcto. De hecho, el espacio para cada dato XML está limitado a 2 GB. Además, el documento XML no debe estar estructurado con una jerarquía de más de 128 niveles.

Los datos XML están definidos en UTF-16 por SQL Server 2005.

Para almacenar datos directamente en formato XML, SQL Server 2005 evita tener que hacer un trabajo largo y pesado para definir las correspondencias entre el formato XML y la estructura relacional con la que están organizada la información en la base de datos. Este tipo dispone de los métodos query(), exist(), value(), nodes() y modify() para trabajar con los datos. Estos métodos se basan en XQuery, un subconjunto de XML específico para consultas. Para satisfacer las exigencias del consorcio W3C, se puede asociar una colección de esquemas a una columna de tipo XML. Los datos almacenados en la columna deberán respetar las restricciones del esquema. Esta columna se denominará entonces XML con tipo; si no, se tratará de una columna XML sin tipo. XML sin tipo

El tipo XML tal y como está definido en SQL Server 2005 respeta la definición normalizada por el estándar ISO SQL-2003, es decir que trabaja con documentos XML 1.0 bien formados y con fragmentos XML. Sin embargo, la columna que utilice un tipo XML sin tipo, es decir no basado en un esquema XML, sólo contendrá datos conformes con un documento XML 1.0 bien formado o un fragmento XML. Este método de funcionamiento es el más flexible, pero cuando se dispone de un esquema XML es preferible recurrir a XML con tipo. Debe utilizarse cuando no se disponga de esquema XML, o cuando haya varios esquemas XML asociados a orígenes externos de datos. Ejemplo

La tabla catálogo se crea con una columna XML sin tipo:

XML con tipo

Si los datos que irán en una columna XML están descritos en una colección de esquemas XML, se puede asociar esta colección de esquemas XML a la columna. Entablando esta asociación en el nivel de la definición de la columna todos los datos escritos en ella deben respetar un esquema XML asociado. Entonces se dice que la columna está definida en XML con tipo. Los esquemas XML actúan como una restricción de integridad potente que garantiza una estructura claramente identificada para todos los datos presentes en esta columna. Las actualizaciones de datos XML se controlan mejor y más rápido en el proceso de ejecución de las consultas. Al definir la columna se pueden usar las palabras clave DOCUMENT y CONTENT para especificar que contendrá documentos XML bien formados, es decir con un solo elemento en el nivel superior. En el caso de CONTENT, la columna contendrá datos de formato XML. Si no se especifica nada más en la definición de la columna, se aplica de forma predeterminada la opción CONTENT. La colección de esquemas XML debe estar creada antes de que se pueda hacer referencia a ella en una columna XML. La gestión de colecciones de esquemas se realiza con las instrucciones CREATE XML SCHEMA COLLECTION , ALTER XML SCHEMA COLLECTION y DROP XML SCHEMA COLLECTION. Cada colección puede contener uno o más esquemas XML. Esta gestión de colecciones hace mucho más flexible la gestión de columnas XML con tipo, ya que siempre se puede agregar un esquema XML a la colección para responder a nuevas restricciones, funcionalidades o formatos de gestión de datos. Sin embargo, durante la definición del esquema, no siempre es posible definir el conjunto de posibilidades. SQL Server soporta las declaraciones any, anyAttribute y anytype en la definición de esquemas. Sintaxis CREATE XML SCHEMA COLLECTION nombreEsquema AS definiciónDelEsquema;

Ejemplo Definición de un esquema para definir un cliente:

Ahora es posible definir una tabla con una columna que se basa en esta clasificación XML. Ejemplo Definición de la tabla Vendedores:

Sin embargo, la definición no siempre es tan sencilla y los datos obligatorios pueden ser completados por datos específicos en casos bien concretos. Por ejemplo, en el caso aquí mostrado, puede ser interesante completar la información general de un cliente con información puntual cuyos criterios no necesariamente están definidos de antemano (un número de teléfono adicional, detalles sobre la entrega, gustos particulares…). Mediante la etiqueta any es posible adaptar así el esquema XML, con lo que en este caso obtenemos:

Los atributos de la etiqueta any son: minOccurs: el número mínimo de elementos de este tipo. maxOccurs: el número máximo de datos opcionales. namespace: el espacio de nombre utilizado para la validación de los elementos opcionales. processContents: este parámetro permite indicar a SQL Server cómo validar los elementos opcionales en relación a un esquema. Los valores posibles para el atributo processContents son:   

skip: los elementos no son validados en relación a un esquema. strict: los elementos son necesariamente validados en relación a un esquema. lax: los elementos son validados en relación a un esquema únicamente si éste existe en la base.

Unión y lista de tipo

Los esquemas XML permiten definir los tipos para los datos XML. Permiten también definir los valores posibles para ciertos elementos. A veces los valores posibles para un mismo criterio (por ejemplo la distancia) pueden ser expresados de varias maneras (sistema métrico o sistema anglosajón). El esquema XML que tendrá en cuenta la definición de las distancias deberá incluir la unión de los dos tipos para permitir la introducción de todos los valores posibles. Para hacer esta unión se utilizará la etiqueta union. El ejemplo siguiente ilustra este propósito:

2. Trabajar con una columna de tipo XML a. Operaciones de DML

Tanto si la columna es con tipo como si no, se pueden manipular los datos por medio de las instrucciones DML INSERT, UPDATE, DELETE y SELECT. No obstante, estas instrucciones se comportan con los datos XML como columnas basadas en un tipo relacional clásico, es decir que manipulan toda la información presente en la columna. Es posible trabajar con datos de tipo fecha y hora (datetime, date y time) en documentos XML de manera sencilla. Para ello, los datos de tipo fecha y hora se escriben mediante el formato YYYY-MM-DDThh:mm:ss:nnnZ para expresar una hora GMT o bien YYYYMM-DDThh:mm:ss:nnn±hh:mm para expresar la díferencia horaria respecto a la hora GMT. Los elementos del formato son:       

YYYY: año con 4 cifras. MM: mes con 2 cifras. DD: número del día del mes con 2 cifras. hh: hora con formato 24 h. mm: minutos. ss: segundos. nnn: fracciones de segundo.

Este formato de fecha y hora cumple la forma ISO 8601. La utilización de esta norma permite transferir fácilmente datos de tipo fecha y hora de un entorno a otro. Ejemplo Fila agregada a una columna XML sin tipo:

Fila agregada a una columna XML sin tipo, con importación de datos desde un archivo.

b. Métodos específicos

Al buscar datos, el usuario puede desear recuperar únicamente los datos pertinentes a partir de la fuente XML. Para hacer una analogía, el problema es similar al de los datos de texto cuando el usuario sólo desea conocer un subconjunto de la cadena de caracteres almacenada en la base de datos. Para cubrir esta posibilidad, SQL Server ofrece numerosas funciones de manipulación de cadenas de caracteres. Para extraer partes de un documento XML, SQL Server ofrece los métodos query(), value(), exist() y nodes(). SQL Server también dispone del método modify() para modificar datos XML.

La presentación predeterminada de los datos es textual, aunque haciendo clic en la columna de resultados de la ventana de SQL Server Management Studio se puede abrir el editor XML para obtener una mejor visualización del resultado. Query()

Este método acepta como parámetro una consulta de formato XQuery que haga referencia a la lista de nodos del documento. Devuelve un elemento XML sin tipo, extraído de la columna de tipo XML. Ejemplo

value()

Este método devuelve un valor escalar extraído del documento XML. Acepta como parámetro una consulta XQuery y el nombre del tipo de datos del valor devuelto. Ejemplo El ejemplo siguiente permite conocer la referencia y el precio del primer artículo presente en el documento XML que tiene el número 1.

exist()

Este método, que acepta como parámetro una consulta de tipo XQuery, devuelve un valor escalar de tipo bit:  

1 si la consulta XQuery permite localizar la información. 0 si la consulta XQuery no devuelve información.



NULL si la columna XML de la función contiene el valor NULL.

Resulta útil trabajar con este método en conjunto con el método value para no generar un error cuando la consulta XQuery no ofrece resultados. Ejemplo

nodes()

Este método es útil cuando se desea obtener las correspondencias entre el tipo XML y un almacenamiento de datos en formato relacional. A partir de una consulta XQuery pasada como parámetro, este método permite obtener tanto filas como nodos definidos en este nivel. Cada fila del conjunto de resultados está en formato XML, pero así resulta fácil dividir en partes correctamente un documento XML. Ejemplo A partir de un dato de tipo XML almacenado en una variable, se dividen datos en función de la consulta XQuery que se pasa como parámetro al método nodes. El conjunto de resultados se presenta a continuación, aunque también puede almacenarse en una columna de tipo XML de una tabla de la base de datos.

modify()

El método modify permite modificar una parte de los datos almacenados en un documento xml. Al contrario que la instrucción update, que permite actualizar el contenido global de una columna, el método modify permite modificar valores directamente en la columna de tipo xml. El método modify permite añadir valores, modificar valores existentes y suprimir valores. Para realizar estas tres operaciones, el método modify utiliza las expresiones XML DML: insert, replace value of y delete. Ejemplo En el ejemplo siguiente, el documento xml inicial se modifica añadiendo una fila de información:

3. Indexar una columna de tipo XML SQL Server ofrece la posibilidad de definir índices sobre las columnas de tipo XML. Cuando tiene lugar una consulta, los datos XML se procesan en el nivel de cada fila, lo que

puede provocar que los procesamientos sean largos y costosos, sobre todo si la consulta afecta a una gran cantidad de filas o cuando los datos de formato XML son voluminosos. El mecanismo utilizado habitualmente para definir un índice de datos relacionales se basa en la estructura de un árbol equilibrado, es decir que, en la construcción del índice, se realiza siempre un mismo número de preguntas para llegar al nivel de hoja del índice, sea cual sea el valor que se busque. Este tipo de estructuración también se adopta para definir el índice denominado principal sobre la columna de tipo XML. Este índice se compone, entre otros elementos, de marcas y valores contenidos en la columna de tipo XML. A partir de este índice principal, se pueden definir índices secundarios para acelerar el tratamiento de las consultas. Estos índices secundarios se definen en función de consultas frecuentes:   

el índice PATH para consultas sobre la ruta de acceso; el índice PROPERTY para consultas sobre propiedades; el índice VALUE para consultas sobre valores.

También es posible definir un índice de texto integral sobre las columnas de tipo XML, con el fin de indexar únicamente el contenido e ignorar las marcas XML. a. Índice principal

El índice principal para una columna de tipo XML necesita que la tabla posea un índice organizado en función de la clave principal. Sintaxis CREATE PRIMARY XML INDEX nombreÍndice ON tabla(columnaXML)

Ejemplo Se define un índice principal sobre la columna página de la tabla catálogo.

b. Índice secundario

Sólo se puede crear un índice secundario si se ha definido un índice principal para la columna. El documento XML sólo puede contener 128 niveles como máximo. Los documentos que posean una jerarquía más compleja se rechazan en el momento de la inserción o la modificación de las columnas. De la misma forma, la indexación se realiza sobre los primeros 128 bytes del nodo; los valores más largos no se tienen en cuenta de cara al índice.

Sintaxis CREATE XML INDEX nombreÍndice ON tabla(columnaXML) USING XML INDEX nombreÍndicePrincipal FOR {PATH|PROPERTY|VALUE}

PATH Permite construir un índice sobre las columnas path y value (ruta y valor) del índice XML principal. Este índice permite mejorar claramente las respuestas cuando se utilice el método exist(), en una clausula where por ejemplo. PROPERTY Permite construir un índice sobre las columnas PK, path y value del índice XML principal. El símbolo PK corresponde a la clave principal de la tabla. Este tipo de índice resulta útil para utilizar el método value() en consultas SQL de manipulación de datos. VALUE Permite construir un índice sobre las columnas value y path del índice XML principal. Este índice resulta útil principalmente para las consultas en las que se conozca el valor de nodo independientemente de la ruta de acceso, cosa que puede suceder, por ejemplo, cuando se utiliza el método exist(). Ejemplo En el ejemplo siguiente, se crean los tres tipos de índices a partir del índice XML principal definido para la columna página de tipo XML de la tabla Catálogo.

4. XQuery y Xpath Los documentos de formato XML son cada vez más populares, por lo que cada vez son más frecuentes las consultas de este tipo de datos. El lenguaje XQuery tiene el objetivo de ser un lenguaje tan sencillo y eficaz para consultar datos de formato XML como lo es el lenguaje SQL para consultar datos almacenados en formato relacional. Se puede consultar la documentación exacta sobre las reglas que rigen el lenguaje de interrogación XQuery en la página http://www.w3.org/TR/xquery. El lenguaje Transact SQL de SQL Server 2005 implementa solamente un subconjunto de XQuery tal y como éste está definido por el W3C. Este subconjunto permite realizar sin ningún problema la mayoría de las consultas. La implementación XQuery de SQL Server 2005 ofrece, entre otras, la posibilidad de construir bucles for, de hacer restricciones (where), de devolver un valor (return) y de ordenar el conjunto de resultados (order by).

SQL Server también propone la función let, que permite valorar variables en el contexto de una consulta XQuery. Dentro de XQuery está integrado el lenguaje de navegación XPath 2.0, que corresponde a consultas XQuery definidas única y enteramente con una ruta. La expresión de esta ruta permite localizar uno o más nodos en el documento XML de origen. Se puede limitar el número de nodos seleccionados definiendo filtros. Éstos, a su vez, pueden utilizar los valores contenidos en los atributos de los nodos para evaluar una condición booleana. Las expresiones XQuery siempre están estructuradas de la misma forma: un encabezado (prologue) y una expresión (body). A menudo se omite el encabezado para las consultas definidas en SQL Server. La expresión XQuery representa la consulta propiamente dicha. El lenguaje XQuery tiene un tipo muy rígido y distingue entre mayúsculas y minúsculas, lo que es coherente con XML pero no se da en SQL. Las palabras clave siempre se escriben en minúscula. La consulta XQuery más simple corresponde sencillamente a una ruta relativa al documento XML. Este tipo de consultas puede, por ejemplo, utilizarse como parámetro del método query(). La ruta se expresa con respecto a la raíz de la estructura XML. El resultado de la consulta corresponde a todos los elementos que poseen el nivel de jerarquía expresado en la consulta. Ejemplo Sea la siguiente variable de tipo XML:

La consulta XQuery biblioteca/autor ofrecerá como resultado el conjunto de autores y sus libros. La consulta XQuery biblioteca/autor/* ofrecerá como resultado solamente los libros, ya que la ruta de elementos seleccionados debe ser biblioteca/autor/algo. Por último, la consulta biblioteca/autor/libro/* no ofrecerá ningún resultado, ya que el nodo libro está compuesto por un solo nodo.

La consulta XQuery también puede permitir la localización de un valor concreto. En este caso, la expresión de la ruta hacia el elemento se acompañará de filtros para definir

condiciones adicionales para la selección de filas. Este filtro corresponderá al número de la instancia del nodo que debe seleccionarse, y se expresa entre corchetes [ ]. Ejemplo La consulta XQuery biblioteca/autor[1]/* suministrará la lista de libros cuyo autor es el primero al que se hace referencia en el documento XML (en este ejemplo, Julio Verne). La consulta XQuery biblioteca/autor[2]/* suministrará la lista de libros cuyo autor sea el segundo al que se hace referencia en el documento XML (en este ejemplo, Douglas Hofstadter).

Se puede trabajar con los atributos de los nodos XML en la consulta XQuery. Para hacer referencia a un atributo, hay que anteponer el símbolo @ a su nombre. Ejemplo Para presentar el nombre de pila de los autores, hay que utilizar la siguiente consulta XQuery: biblioteca/autor/@nombre. Para tener la seguridad de que la consulta sólo producirá un valor único, hay que precisar el número de cada nodo presente en la consulta. El ejemplo permite conocer el nombre del primer autor y el apellido del segundo autor.

Se puede trabajar con los parámetros para imponer condiciones de selección de nodos. El filtro expresado entre corchetes permite definir una condición que devolverá un valor booleano que indicará si el nodo está seleccionado o no. Ejemplo La consulta XQuery biblioteca/autor[@nombre=‟Julio‟]/* permitirá conocer la lista de libros escritos por un autor cuyo nombre de pila es Julio.

Para escribir consultas de extracción de datos aún más flexibles, XQuery ofrece las instrucciones for, where y return. La construcción de la consulta puede ser más sofisticada y permitir la extracción de informaciones de forma propia. La consulta XQuery contiene todos los elementos necesarios para extraer la información del documento en formato XML. Ejemplo

En el ejemplo siguiente, se recorre gracias a un bucle for el documento XML en busca de los autores cuyo nombre es Julio. Cada subconjunto del documento que corresponde a un paso del recorrido se conserva en la variable $element. Para cada elemento del bucle for, se aplica una condición suplementaria con el operador where. En el ejemplo aquí presentado, sólo se conservan los libros identificados como clásicos. Finalmente, mediante la instrucción return se obtiene la información relativa a los libros como elemento del nodo clásico:

Durante el recorrido del documento xml, a veces es necesario asignar valores a variables. Esta operación es posible gracias a la instrucción let, que permite la valoración de una variable en una expresión XQuery. La consulta XQuery está entonces en situación de realizar operaciones más complejas, como el recuento. Ejemplo En el ejemplo siguiente, se recorre el documento xml en el nivel de autores. El recorrido se realiza con el bucle for. Para cada elemento del recorrido, y, por tanto, para cada autor, se cuenta el número de libros mediante la instrucción count y este valor se asigna a la variable $número. La instrucción return permite devolver una expresión xml bien formateada:

5. FOR XML El uso de FOR XML representa un buen medio para gestionar el intercambio en formato XML con una aplicación cliente. Esta cláusula permite convertir el resultado de una consulta SELECT en formato XML. Ejemplo Esta consulta establece la lista de pedidos por clientes en formato XML.

El resultado en formato XML de esta consulta es, así pues:

Con la aparición del tipo XML y la creciente demanda de presentar los datos en este formato, se ha revisado la cláusula FOR XML en SQL Server con el objetivo de permitir la extracción de información de la base relacional en formato XML sin necesidad de recurrir a utilidades externas de manipulación y conversión de datos. Por cuestiones de compatibilidad, los resultados de la cláusula FOR XML se ofrecen de forma predeterminada en formato texto. No obstante, la opción TYPE permite obtener el mismo resultado, aunque expresado en formato XML. Esta opción resulta especialmente interesante cuando, por ejemplo, el resultado de la consulta debe almacenarse en una columna de tipo XML. Ejemplo La consulta anterior se modifica para obtener el resultado en formato XML.

Esta directiva TYPE también permite simplificar la escritura de consultas SQL para generar un documento XML más o menos complejo. Se pueden utilizar todas las funcionalidades de SQL para generar de forma rápida y simple un documento de formato XML. Ejemplo Anidación de consultas que devuelven un valor en formato XML.

El resultado visible a través del editor XML es:

También existe la posibilidad de definir una ruta (PATH) a nivel de la cláusula FOR XML, o bien directamente en el nivel del alias de columna en la consulta Select. Ejemplo El caso anterior se ha revisado indicando las rutas para obtener exactamente el documento XML deseado.

El resultado en formato XML es, entonces:

6. OpenXML Este método permite tratar un documento XML en forma de conjunto de resultados. Con OPEN XML se puede integrar el tratamiento de un documento XML en una consulta SQL de tipo SELECT, INSERT, UPDATE y DELETE.

Con la aparición del tipo XML, este método se utiliza cada vez menos. Sintaxis OPENXML(idoc int ,consultaXPath nvarchar,[flags byte]) [WITH (estructuraDatos)]

idoc Identificador del documento XML. Lo suministra el procedimiento sp_XML_ preparedocument. consultaXPath Consulta en formato XPath para localizar los datos en el documento XML. flags Indicadores de parámetros OPENXML. estructuraDatos Nombre y tipos de columnas a presentar en el conjunto de resultados. El método sp_xml_preparedocument permite preparar en formato XML un documento de formato texto. El procedimiento almacenado devuelve el identificador del resultado de esta conversión, con el que OPENXML podrá localizar los datos en formato XML. Tras su utilización, hay que acordarse de ejecutar sp_xml_removedocument para liberar la memoria ocupada por el documento de formato XML. Ejemplo Implementación del método OPENXML.

7. OPENROWSET Este método permite acceder de forma fácil a recursos externos al servidor para trabajar tanto con datos como con datos presentes en forma de tabla en la base de trabajo actual. La fuente de datos puede ser el sistema de archivos Windows o una fuente OLEDB. Tal vez sea más conveniente usar este método que el programa de importación bcp. El método OPENROWSET presenta una alternativa a OPENXML para integrar fácilmente los datos XML externos al servidor. Sintaxis OPENROWSET(nombreProveedor,cadenaConexión,consulta)

nombreProveedor Nombre del proveedor OLEDB a utilizar. cadenaConexión Cadena de conexión para conectar con la fuente de datos. Los parámetros de esta cadena dependen del proveedor OLEDB seleccionado. consulta Consulta SQL a ejecutar para extraer la información. Ejemplo Carga de datos almacenados en un archivo plano en el sistema de archivos de Windows:

Servicios Web XML SQL Server ofrece servicios Web XML para trabajar con el servidor desde una aplicación remota. Presentando una identificación adecuada ante el servidor, esta aplicación puede solicitar libremente la ejecución de procedimientos almacenados y paquetes de instrucciones Transact SQL. Estas solicitudes deben formularse al servidor por medio de un mensaje en formato SOAP. Esta funcionalidad necesita que el servicio IIS esté instalado y configurado en el servidor en el que funciona el motor SQL Server. Por ello, la verificación de la disponibilidad del servidor IIS se realiza en el momento de instalar SQL Server.

En SQL Server es el middleware orientado a mensajería, Service Broker, el principal usuario de los extremos así definidos. Este servicio se basa en los extremos durante la puesta en práctica mediante varias instancias de SQL Server.

1. Principios de funcionamiento Los servicios Web XML nativos de SQL Server tienen el objetivo de facilitar el acceso a la información para aplicaciones que tengan una necesidad puntual de hacerlo. Esta solución no es apropiada si se da alguna de las siguientes condiciones:   

El tiempo de respuesta del servidor de base de datos debe ser óptimo. La carga de trabajo de la aplicación va a ser muy importante. Se trata de reemplazar un servidor de aplicaciones.

Al ofrecer esta solución a través de SOAP , las aplicaciones no Windows pueden acceder fácilmente a los datos almacenados en una base de datos SQL Server. Los clientes Windows pueden utilizar tanto MDAC (Microsoft Data Access Component), como SQLXML 3.0 para trabajar con SQL Server. Para poder usar los servicios Web XML, las aplicaciones deben disponer de un extremo en el servidor SQL Server. Este extremo está disponible a través del protocolo HTTP. A cada extremo definido se asocian procedimientos almacenados y funciones propias de la aplicación, es decir que para el usuario que acceda por el extremo HTTP sólo estarán disponibles estos elementos. Las funciones y procedimientos se clasifican como métodos Web (web methods). Una colección de métodos web que funcionan entre sí constituye un servicio Web. El servicio Web se describe con un archivo WSDL que SQL Server puede generar automáticamente y hacer accesible para el punto de entrada HTTP.

Esta solución presenta numerosas ventajas, algunas de las cuales son: 



Todas las aplicaciones capaces de trabajar con un servicio Web pueden acceder a la instancia SQL Server. Como las tecnologías que rigen los servicios Web están normalizadas y son estables, se puede trabajar con servicios Web por medio de muchos lenguajes de programación. Por ejemplo, una aplicación escrita en Java puede acceder fácilmente a datos de una base SQL Server, sin que sea necesario instalar un controlador JDBC. Una excelente integración con las herramientas de desarrollo de aplicaciones Web. De hecho, los resultados están en formato XML y las herramientas de desarrollo de aplicaciones Web pueden tratar estos datos de forma fácil y eficaz si, por ejemplo, aplican una hoja de estilo XSL.



Una integración más fácil con los clientes móviles que se conectan al servidor con frecuencia irregular. Además, el formato XML normalizado permite adaptarse a todo tipo de aplicaciones, como las desarrolladas para terminales móviles.

Para que sea posible utilizar estos servicios Web, hay que configurar el servidor. Las etapas de este proceso de configuración son:    

administrar los extremos; definir las autorizaciones de acceso para el extremo; definir funciones y procedimientos almacenados; configurar el entorno de ejecución del lado servidor.

Luego habrá que generar el archivo WDSL relativo al servicio Web y, por último, integrar el uso de estos servicios Web directamente en una aplicación cliente.

2. Punto de entrada HTTP Para que SQL Server pueda responder a las solicitudes realizadas a través de un mensaje SOAP por el protocolo HTTP, hace falta que el servidor pueda escuchar este protocolo. Debe crearse un directorio virtual o punto de entrada HTTP (http endpoint) que corresponda a una URL válida y accesible desde el exterior. Para administrar los puntos de entrada HTTP, SQL Server dispone de las instrucciones CREATE, ALTER y DROP ENDPOINT . a. CREATE ENDPOINT

Este método permite crear el extremo HTTP. Se puede definir simultáneamente las funciones que estarán disponibles en él. La instrucción se divide en dos grandes partes: primero el protocolo de red permite acceder al punto de entrada, y luego se exponen los métodos ofrecidos en el servicio Web.

Dado el gran número de opciones que posee este comando, sólo se detallan a continuación los más significativos. Sintaxis CREATE ENDPOINT nombreExtremo [ AUTHORIZATION conexión ] STATE = { STARTED | STOPPED | DISABLED } AS { HTTP | TCP } (parámetrosEspecíficosDelProtocolo ) FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING} (parámetrosEspecíficosDelProtocolo)

nombreExtremo

Nombre lógico del extremo (httpEndPoint). Este nombre permitirá identificar al extremo al que hagan referencia las instrucciones ALTER ENDPOINT y DROP ENDPOINT. AUTHORIZATION conexión Esta opción permite crear un extremo y otorgarle su propiedad a una conexión válida definida en SQL Server. De forma predeterminada, también es su propietario el creador de httpEndPoint. STATE Permite precisar el estado del extremo, que de forma predeterminada está detenido (no accesible). AS {HTTP|TCP} Permite especificar el protocolo utilizado para acceder al servicio Web. Según el protocolo definido, las opciones de configuración serán distintas: por ejemplo, LISTERNER_PORT y LISTERNER_IP si el protocolo es TCP, o PATH, AUTHENTICATION, PORTS, SITE... si el protocolo es HTTP. FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING} Permite definir la "carga útil" del extremo, es decir las funciones y procedimientos almacenados que participarán en la definición del servicio Web. Los argumentos que siguen son propios del tipo de contenido del punto de entrada. Por ejemplo, si el contenido es de tipo SOAP los argumentos permitirán definir la correspondencia entre el nombre de procedimientos y funciones y el nombre con el que los identificará el servicio Web. También se indica el método de construcción del archivo WDSL. Ejemplo El ejemplo siguiente permite crear un extremo en el servidor SQL Server y exponer la función ContadorClientes que devuelve el número total de clientes. El extremo está definido para HTTP y el método es accesible por medio de SOAP.

b. ALTER ENDPOINT

El comando ALTER ENDPOINT permite, principalmente, modificar el estado de un extremo. También permite agregar otros métodos y procedimientos disponibles en el servicio Web, así como modificar los métodos existentes o suprimirlos del servicio Web. Sintaxis

ALTER ENDPOINT nombreExtremo [ AFFINITY = { NONE | <64bit_integer> | ADMIN } ] [ STATE = { STARTED | STOPPED | DISABLED } ] AS { HTTP | TCP } (parámetrosEspecíficosDelProtocolo ) FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING} (argumentosEspecíficosDelTipoDeContenido)

Ejemplo En el ejemplo siguiente, se detiene el extremo.

c. DROP ENDPOINT

Esta instrucción permite suprimir un extremo HTTP. Sintaxis DROP ENDPOINT nombreExtremo

Ejemplo Se suprime el extremo libro.

Parámetros con valor de tabla Se trata de un tipo de parámetro nuevo introducido con SQL Server 2008. Con este tipo de parámetro es posible enviar un conjunto de datos directamente a un procedimiento o a una función Transact SQL. Para entender mejor el interés de un “table value parameter", es posible acercar este tipo de parámetro a la noción de cuadro. Cada fila del cuadro se define en relación a un tipo de datos de usuario. Estos tipos son creados mediante la instrucción CREATE TYPE, que permite ahora definir tipos TABLE. Cada campo está muy diferenciado gracias a la utilización, entre otras cosas, de restricciones de integridad en el momento de la definición del tipo. Con los parámetros con valor de tabla es posible administrar un conjunto estructurado de datos sin necesidad de crear una tabla, ni siquiera temporal. En este sentido, la utilización de estos tipos permite ganar en flexibilidad de utilización y a veces incluso en rendimiento. Sin embargo, los parámetros con valor de tabla son siempre parámetros de sólo lectura. Por tanto, el procedimiento o la función que posee un parámetro de este tipo no puede modificar los datos presentes en este parámetro. La utilización de un parámetro con valor de tabla puede dividirse en tres etapas:

  

definición del tipo de cada fila mediante la instrucción CREATE TYPE; utilización del parámetro con valor de tabla con datos mediante las instrucciones del DML: INSERT, UPDATE y DELETE; llamada a la función o al procedimiento utilizando como parámetro el parámetro con valor de tabla.

En el procedimiento o en la función que poseen un parámetro de este tipo, los datos son extraídos mediante consultas SELECT o mediante la utilización de un cursor. Ejemplo En el ejemplo siguiente se ha definido el tipo tcliente para recibir un número de cliente y su nombre. El procedimiento mostrar permite mostrar los datos contenidos en un parámetro de tipo tcliente. Finalmente, una secuencia de comandos Transact SQL permite almacenar los números y los nombres de los clientes que viven en Barcelona en una variable de tipo tcliente.

Las estructuras jerárquicas La noción de organización jerárquica se encuentra en varios ámbitos de la vida cotidiana y su modelización no siempre es fácil. Es el caso por ejemplo de un organigrama de empresa. SQL Server 2008 propone un tipo de datos (hierarchyId) y métodos para almacenar esta jerarquía de forma estructurada. También es posible optimizar el recorrido de esta jerarquía mediante índices que permiten recorrer rápidamente el árbol de directorios. Además, SQL Server ofrece a través de Transact SQL métodos específicos a este recorrido de árbol de directorios para facilitar la extracción de datos.

1. HierarchyId Se trata de un tipo de datos específico de SQL Server que puede ser utilizado para crear una estructura jerárquica en una tabla relacional. Se pueden extraer los datos de esta tabla mediante consultas jerárquicas. Esta noción de jerarquía en ningún caso es comparable a una restricción de integridad; es posible encontrar elementos huérfanos, es decir, que no están vinculados al árbol de directorios definido. Los elementos huérfanos pueden aparecer tras la introducción de un valor erróneo o tras la eliminación del elemento que era la jerarquía superior. El tipo hierarchyId ofrece todo el soporte necesario para crear adecuadamente una jerarquía en las tablas. No obstante, el simple hecho de definir una columna de este tipo en una tabla

no garantiza en ningún caso que los datos se clasifiquen de manera jerárquica. Es el desarrollador o el usuario final quien tiene que valorar esta columna. De la misma manera, si se ha de administrar la noción de unicidad de los valores, es necesario definir una restricción de unicidad.

2. Los índices Para acelerar el recorrido de los directorios jerárquicos, es posible definir índices. Sin embargo, los índices sólo serán útiles si respetan dicho árbol de directorios. Puesto que hay dos maneras de recorrer un árbol, existen dos maneras de definir estos índices. El primer método es tratar de llegar lo más rápido posible al nivel hoja y a continuación explorar el conjunto de hojas de una misma rama antes de pasar a la siguiente rama.

El segundo método consiste en explorar un nivel completamente antes de pasar a recorrer elementos del nivel inferior. Por supuesto, es posible definir los dos índices en la misma tabla.

3. Los métodos GetAncestor(nivel) Este método permite identificar a la jerarquía superior de un nodo. El valor entero pasado como parámetro permite precisar el número de niveles que hay que subir. Este valor es por tanto estrictamente positivo e inferior al valor devuelto por GetLevel. GetDescendant ([nodoHermano1 [, nodoHermano2]]) Este método permite saber qué nodo es el descendiente directo del nodo identificado como padre y en relación al cual se ejecuta el método. En caso de que haya varios nodos hijos es posible precisar 1 ó 2 hermanos del descendiente para localizar de forma precisa la ubicación del nodo descendiente. Este tipo de posicionamiento es especialmente útil en el momento de insertar datos en un árbol seleccionado. GetLevel() Este método es necesario durante la definición de un índice BreadthFirst. Permite conocer la profundidad de un nodo en el árbol de directorios. GetRoot() Este método permite identificar de forma inmediata el nodo raíz del árbol de directorios.

IsDescendant (nodoPorComprobar) Este método permite determinar rápidamente si el nodo indicado en el parámetro pertenece a la descendencia del nodo respecto al cual se ejecuta este método. Parse (cadenaCaracteres) Este método permite convertir la cadena de caracteres indicada en el parámetro a un valor de tipo hierarchyId. Read (representaciónBinaria) Este método sólo es utilizable en el contexto del CLR y permite realizar una lectura en binario del nodo respecto al cual se ejecuta este método. Reparent (antiguaRaíz, nuevaRaíz) Este método permite modificar el nodo raíz y definir la nueva ruta de acceso en relación a la nueva raíz indicada en el parámetro. ToString() Este método es el método inverso de Parse y permite obtener una representación textual del valor hierarchyId del nodo. Write (flujoBinario) Este método reservado al código CLR permite definir a partir de un flujo binario el valor de tipo hierarchyId del nodo. El método Write permite realizar la operación inversa del trabajo realizado con el método Read. Ejemplo La primera etapa consiste en crear una tabla con una columna de tipo jerárquico. En el ejemplo presentado a continuación, se ha definido una tabla que representa a los empleados de una empresa con su puesto y su posición jerárquica respectiva.

A continuación se definen los índices en esta tabla. El primer índice se crea mediante la inserción de una restricción de clave primaria. El segundo índice se refiere a la columna de tipo hierarchyId. Finalmente, para permitir recorrer rápidamente la lista de empleados que se encuentran en un mismo nivel de la jerarquía, se inserta una nueva columna en la definición de la tabla. Esta columna contiene un dato calculado a partir de la posición del empleado en la jerarquía:

Ahora que está definida la estructura, es posible insertar datos en esta tabla:

Ahora es posible extraer los datos como deseemos. En este primer ejemplo, se han extraído todos los empleados que tienen como superior jerárquico directo al jefe:

Los datos no estructurados Hoy en día, las bases de datos deben estar en situación de almacenar datos no estructurados y esto es lo que propone SQL Server con su tipo filestream. En efecto, los documentos digitales están cada vez más presentes en nuestra vida cotidiana y ahora es habitual trabajar con fotos, archivos de Word, Excel, documentos escaneados... Ahora bien, todos estos documentos difícilmente pueden encontrar su lugar en una base de datos relacional estructurada con tipos de datos simples. Además, estos documentos representan con mucha frecuencia un volumen importante. Esa es la razón por la que generalmente se realiza una de las siguientes opciones:   

los datos estructurados se almacenan en la base de datos y los datos no estructurados (archivos) se almacenan directamente en el sistema de archivos, los datos estructurados se almacenan en una base de datos y los datos no estructurados se almacenan en otra base, todos los datos, estructurados o no, se almacenan en la base de datos.

Las dos primeras soluciones plantean el problema de la vinculación entre los diferentes datos. Por ejemplo, cómo asociar correctamente la imagen de un producto a su referencia, su denominación… y en particular cómo garantizar que si se elimina una imagen deje de existir su artículo asociado. La tercera solución evita estos problemas pero plantea el delicado problema de la administración del espacio en disco en la base de datos. Además, los archivos de datos muy voluminosos tienen tendencia a deteriorar el rendimiento del motor de base de datos. Para tratar de aprovechar las diferentes opciones, SQL Server propone dos soluciones diferentes. La primera consiste en utilizar una segunda base para almacenar los datos de tipo blob (Binary Large OBject) pero conservando un vínculo entre los datos locales y los datos remotos. Este servidor de archivos BLOB evidentemente puede ser de Microsoft, pero todos los grandes fabricantes del mercado son compatibles con esta solución. En efecto, este tipo de solución puede ser interesante cuando los datos de tipo BLOB deben ser

almacenados en un servidor distinto del servidor SQL, o cuando ya existen aplicaciones que alimentan este almacén de BLOB. La segunda posibilidad es definir una columna de tipo varbinary utilizando el atributo FILESTREAM, que permite almacenar el archivo en el sistema de archivos a la vez que lo vincula con los datos relacionales. Este modo de almacenamiento permite utilizar las ventajas de todas las soluciones sin sus inconvenientes. Los datos almacenados en una columna FILESTREAM son detectados por SQL como datos de tipo BLOB y pueden ser manipulados en consultas como cualquier dato relacional almacenado en la base. Además, esta columna recibe un tratamiento idéntico al resto de columnas BLOB durante las operaciones de mantenimiento, de salvaguarda y de restauración. Este tipo de columnas impone no obstante algunas limitaciones, como el almacenamiento de los datos en un disco local, que no soporta el cifrado automático de los datos ni la configuración en espejo. Tampoco es posible definir una columna de tipo FILESTREAM en un parámetro con valor de tabla. Sea cual sea el modelo de almacenamiento escogido, la búsqueda de estos datos sigue siendo complicada. Para realizar esta tarea, SQL Server utiliza el servicio de texto integral incluido en SQL Server. Esta integración permite reducir la complejidad de administración y por tanto el coste durante el despliegue de soluciones SQL Server que utilizan esta funcionalidad. Además, esta integración permite obtener mejores resultados. Para su puesta en marcha es necesario activar la administración del tipo FILESTREAM en el nivel del servidor. Esta activación se realiza mediante el procedimiento almacenado sp_filestream_configure. Este procedimiento permitirá compartir una carpeta en el servidor y permitir a los clientes acceder a una parte del disco duro del servidor. Sintaxis Sp_filestream_configure @enable_level= nivel ,@share_name= nombreRecursoCompartido

nivel El nivel corresponde a un valor comprendido entre 0 y 3. El valor 0 corresponde a ningún recurso compartido activo y el valor 3 significa que la carpeta está accesible localmente y de manera remota. nombreRecursoCompartido Permite precisar el nombre del recurso compartido asignado al tipo Filestream. Ejemplo En el ejemplo siguiente el recurso compartido se llama GescomImages y está accesible a nivel local y a través de la red:

Es posible comprobar el resultado de este comando ejecutando la instrucción net share desde la ventana del símbolo de sistema de Windows:

Para administrar los archivos físicos SQL Server utiliza los grupos de archivos. La administración de los datos FILESTREAM no escapa a la regla y por eso es necesario definir un grupo de archivos. La particularidad de este grupo de archivos proviene del hecho de que no está compuesto de archivos, sino que corresponde a una carpeta creada anteriormente en el sistema de archivos. Ejemplo En el ejemplo siguiente se ha definido el grupo de archivos FilestreamGroup y se ha asociado la carpeta C:\gescom\images al grupo de archivos. La carpeta C:\gescom debe ser creada en el sistema de archivos y la instrucción Transact SQL se encarga de definir la subcarpeta images de la misma manera que se encarga de definir el archivo de datos en un grupo de archivos clásico.

Entonces se puede definir una columna de tipo FILESTREAM, pero sólo si las tablas poseen una columna de tipo uniqueidentifier rowguidcol, que no acepten los valores null y con una restricción de unicidad o bien de clave primaria. Ejemplo Con este fin se define la tabla catálogo:

Ahora es posible insertar nuevas filas de datos en la tabla. Como Transact SQL no es el lenguaje mejor adaptado para importar imágenes (es mejor utilizar .Net), se trata como una cadena de caracteres por lo que se considerará como un dato binario.

Se puede consultar el contenido de la carpeta de destino y comprobar que ahora contiene datos.

El archivo filestream.hdr no debe ser eliminado bajo ningún concepto.

Los datos espaciales Son numerosas las aplicaciones que trabajan con datos geográficos y permiten una identificación más rápida de la información, a menudo con el objetivo de trazar un itinerario. Pero las aplicaciones también pueden utilizar datos geográficos para obtener una representación visual de los datos o para realizar un análisis geográfico de los datos (¿dónde se encuentran nuestros principales clientes?, etc.). No obstante, no es posible administrar de la misma manera los datos relativos a un esquema de una ciudad o de un barrio y los relativos a un país. Efectivamente el itinerario para ir desde la Plaza de Cibeles hasta la Plaza de Colón en Madrid es infinitamente más corto que para ir de Madrid a Santa Cruz de Tenerife. En el primer caso se puede considerar que la Tierra es plana, mientras que esta aproximación no es posible para el segundo caso. Para responder a estas limitaciones, SQL Server propone los tipos geometry y geography. El tipo geometry trabaja sobre un mapa en dos dimensiones y permite representar datos a escala local. El tipo geography utiliza la latitud y la longitud para almacenar los distintos datos. Para estar en situación de trabajar con las principales herramientas de representación cartográfica del mercado, SQL Server respeta las especificaciones emitidas por el Consorcio Geoespacial Abierto (OGC, por sus siglas en inglés). Propone los métodos y las propiedades del tipo geometry de conformidad con las recomendaciones del OGC. Estos tipos de datos (geometry y geography ) se definen como tipos CLR. Se puede definir un índice sobre las columnas que alojan datos geográficos mediante un cuadro de varios niveles. Ejemplo Se modifica la tabla clientes para insertar una columna de tipo geography:

Los datos de tipo geography se manipulan con los métodos asociados a este tipo. Los métodos se denominan de la siguiente manera: geography::nombreMétodo. Los datos de tipo geography pueden ser un punto, una fila o un polígono. Ejemplo En el ejemplo siguiente se actualiza el posicionamiento de cada cliente mediante una instrucción UPDATE. Los datos de latitud y longitud representan un punto y se expresan en forma de cadena de caracteres. El método STGeomFromText permite transformar esta cadena de caracteres en información de tipo geography.

El principio es exactamente el mismo con los datos de tipo geometry.

SQL Server y el CLR SQL Server está en situación de administrar datos cada vez más complejos pero también debe ofrecer la posibilidad de definir en el servidor tratamientos cada vez más precisos y a veces complicados para poder ofrecer siempre más funcionalidades. En algunos casos, el lenguaje Transact SQL no permite definir el tratamiento deseado. Gracias a la integración de CLR (Common Language Runtime) en .Net directamente en el motor de base de datos, es posible programar tratamientos mediante C# o VB.Net. La programación de estos tratamientos se puede hacer por tanto directamente desde Visual Studio. Para que los métodos y procedimientos así definidos puedan ser utilizados desde las instrucciones SQL se realiza un mapeo Transact SQL con el código MSIL. El hecho de pasar por el CLR integrado en SQL Server es mucho más seguro (y sencillo) que llamar a procedimientos almacenados ampliados. Efectivamente, el CLR presente en SQL Server es únicamente parcial y no cubre todas las funcionalidades ofrecidas por Framework .Net. Por ejemplo, todos los objetos relativos a la administración de la interfaz gráfica no están. Limitando el número de funcionalidades ofrecidas por el CLR es posible prevenir la actuación de determinado código malintencionados.

1. ¿Transact SQL o CLR? Al introducir el código CLR en SQL Server, se plantea un nuevo problema para el desarrollador. ¿Cuándo escoger trabajar con un lenguaje de .Net Framework y cuándo dar prioridad a Transact SQL? Contrariamente lo que se podría pensar tras una primera aproximación rápida a SQL Server, Transact SQL no se ve amenazado en ningún caso por el código CLR. De hecho, ambos tienen sus ventajas y sus inconvenientes. En función del trabajo que se haya de realizar se escogerá de manera natural uno u otro. A continuación se enumeran algunas de las principales ventajas de cada lenguaje. Se debe utilizar preferentemente Transact SQL para:  

obtener un acceso intensivo a los datos. Es inconcebible definir procedimientos CLR para ocultar consultas SELECT; manipular columnas definidas sobre tipos pesados, es decir, cuyo "peso" supere los 8 Kb;



disfrutar de mayor sencillez cuando el desarrollo está realizado en SQL Server aunque algunos despliegues se hayan realizado en una versión anterior de SQL Server.

Se utilizará preferentemente el código CLR para:        

validar los datos y reducir el tráfico de red; definir funciones de alcance general; pasar los datos como parámetros; realizar las operaciones aunque haya poco (o no haya) acceso a la información presente en la base de datos; realizar cálculos complejos sobre los datos presentes en una fila de información; realizar cálculos de agregado o trabajar con tipos escalares; aprovechar la potencia de los lenguajes .Net en términos de funcionalidades ya disponibles; sustituir los procedimientos almacenados ampliados (xp_...), o más exactamente para escribir nuevos procedimientos porque CLR ofrece flexibilidad de escritura gracias al código controlado.

Es posible completar estas características precisando que aunque Transact SQL no posee una gestión optimizada para las cadenas de caracteres, dispone de numerosas funciones de manejo de datos. CLR permite producir un código más optimizado y por tanto más productivo. También permite aprovechar la riqueza de los lenguajes C# y VB.Net. El código CLR está especialmente bien adaptado para definir tratamientos complejos. Permite asimismo administrar flujos XML complejos. Finalmente, CLR permite crear sus propios tipos de datos estructurados: los UDT (o User Defined Types). El código CLR también puede imponerse de forma lógica, por ejemplo durante la escritura de un procedimiento relativamente complejo. Es entonces cuando se nota la riqueza de un lenguaje orientado a objetos y del Framework .Net. Por ejemplo, para detectar un formato en una cadena de caracteres es mucho más sencillo utilizar CLR y las expresiones regulares en lugar de escribir un código complicado y no optimizado en Transact SQL. a. Las funciones escalares

El establecimiento de tratamientos en Transact SQL permite ya un importante ahorro de tiempo en la ejecución en comparación con la ejecución del mismo tratamiento en una aplicación cliente. Pero se pueden mejorar aún más estos resultados para las funciones complejas mediante CLR. Esta mejora de prestaciones es especialmente significativa cuando el paso al código CLR tiene como resultado la escritura de una función de cálculo de agregado. En efecto, dicha

función es ejecutada para cada fila de datos respondiendo a los criterios de restricción de la consulta. Durante la escritura de la función mediante un lenguaje .Net, es necesario precisar si esta función será determinante o no. El buen posicionamiento de esta opción es importante para el resultado. En efecto, el motor utilizará esta opción para mantener o no una caché de respuestas en función de los parámetros de llamadas de la función. En el caso del tratamiento de las cadenas de caracteres por una función del CLR, conviene saber que los lenguajes como C# y VB.Net trabajan sobre datos de tipo carácter en formato unicode, por lo que es necesario utilizar los tipos nchar y nvarchar en Transact SQL. b. Las funciones que devuelven una tabla

El código CLR no es necesariamente el mejor lenguaje para definir estas funciones. CLR propone funcionalidades interesantes para manipular y devolver un conjunto de informaciones, en particular mediante interfaces IEnumerator o IEnumerable, que permiten realizar de manera simple el recorrido completo de la tabla de resultados.

2. Trabajar con el código CLR El código CLR se puede cargar en la base de dos formas: directamente desde Visual Studio o desde el ensamblador de MSIL mediante la instrucción Transact SQL CREATE ASSEMBLY. Si se escriben las funciones SQL CLR desde Visual Studio, la implementación sobre SQL Server es automática. No obstante, a partir de la segunda compilación de una función, hay que eliminar el ensamblador del servidor SQL porque las funciones ya presentes en el servidor SQL no se eliminarán automáticamente. Al proceder de esta forma hay pocas probabilidades de sobrescribir una función que ya existe. Para estar en situación de trabajar con funciones presentes en el ensamblador, es decir, definidas en la plataforma .Net desde Transact SQL, se ha de realizar un mapeo para asegurar la compatibilidad de los diferentes modos de llamada. Las funciones siempre deben tener como prefijo el nombre del esquema sobre el cual están definidas (dbo en general), durante su utilización. a. Acceder a recursos externos

Para acceder a recursos externos al servidor de base de datos, como el registro de eventos, el registro de Windows o bien la invocación de un servicio Web, es necesario tener acceso al código CLR. Ante todo, no se deben utilizar procedimientos no documentados sp_xxxx o

xp_xxxx, porque no necesariamente estarán presentes en las versiones futuras de SQL Server. b. Las instrucciones SQL

Al utilizar Transact SQL, la mayor parte del tiempo las instrucciones SQL están codificadas de manera permanente o estática. El compilador puede así detectar los errores de sintaxis desde la creación del procedimiento o de la función. Durante la integración de instrucciones SQL en código CLR, las instrucciones SQL sólo son compiladas en el momento de la ejecución del código que transmite las instrucciones SQL al motor de base de datos. El comportamiento es similar al que encontramos durante la utilización del procedimiento almacenado sp_executesql que permite reconstruir de forma dinámica instrucciones SQL en Transact SQL. c. Los tipos de datos

En el CLR, no existe equivalente de la palabra clave null presente en el nivel de Transact SQL para señalar la ausencia de valor. La asignación de este valor a una variable de tipo entero, por ejemplo, provoca una excepción de asignación. Para no experimentar este tipo de problemas, es necesario utilizar los indicadores RETURNS NULL, o NULL INPUT durante la definición de la función. El código CLR tampoco está perfectamente adaptado al tratamiento de las cadenas de caracteres de tipo char o varchar. De hecho, en el código CLR las cadenas de caracteres siempre están en formato unicode. Por esto, entre otros motivos, es preferible trabajar con los tipos de datos nchar y nvarchar, que trabajan directamente en el formato unicode. De igual manera, el código CLR considera como un flujo binario los datos de tipo TIMESTAMP. d. El acceso a los datos

El CLR de SQL Server permite el acceso a los datos a través de ADO. Esta capa puede, ahora, ser considerada como un estándar de acceso a los datos desde .Net y permite encontrar rápidamente los automatismos adquiridos durante el diseño de programas clientes. Además, SQL Server propone para el código que se ejecuta sobre el servidor clases específicas en el espacio de nombre Microsoft.SqlServer.Server. e. Localización del código

La integración de código CLR en SQL Server permite escribir procedimientos complejos en VB.Net o C#, por ejemplo, y especificar que la ejecución de este código tenga lugar en

el servidor de base de datos. El objetivo de esta nueva funcionalidad es proporcionar más flexibilidad a los desarrolladores y permitir realizar de la forma más sencilla posible tratamientos complejos gracias, entre otras cosas, a la riqueza de las funcionalidades ofrecidas por CLR. Sin embargo SQL Server no tiene vocación de transformarse en servidor de aplicaciones. Las ejecuciones realizadas sobre el servidor de base de datos consumen recursos que de esa manera no están disponibles para el tratamiento de las consultas. Debido a esto, es necesario dar prioridad a la ejecución del código en el aplicativo cliente (desde el punto de vista del servidor de base de datos) para aligerar la carga de trabajo del servidor.

La clase SQLContext propone el método IsAvailable, que devuelve un valor booleano true (verdadero) si el código se ejecuta sobre el servidor y false (falso) si no es así.

Definir código CLR administrado en SQL Server 1. Activar la aceptación de código CLR Antes de comenzar a escribir código CLR para SQL Server hay que activar la aceptación de este tipo de código por parte de SQL Server. No se trata de una opción ni de un componente que haya que instalar, sino de activar una opción de configuración. Por razones de seguridad, esta opción está desactivada de forma predeterminada. Puede ser activada desde la superficie de exposición o directamente desde Transact SQL mediante el procedimiento almacenado sp_configure. Se trata entonces de modificar el valor de la opción de configuración CLR_ENABLED . Esta configuración a través de Transact SQL puede ser realizada desde SQL Server Management Studio o bien desde SQLCMD, tal y como se ilustra a continuación.

2. Trabajar con Visual Studio Aunque en teoría sea posible escribir el código fuente desde cualquier editor, incluso desde el Bloc de notas de Windows, se recomienda encarecidamente emplear Visual Studio para definir elementos en código CLR administrados por SQL Server. Desde Visual Studio , el despliegue sobre la instancia de SQL Server y la correspondencia entre CLR y Transact SQL se establecen de forma automática.

Para garantizar al máximo la seguridad de la aplicación, SQL Server nunca suprimirá de forma implícita un procedimiento o una función. Deben suprimirse junto con el assembly antes de lanzar una nueva generación desde Visual Studio. Después de lanzar Visual Studio, hay que crear un proyecto de tipo SQL Server.

A continuación, Visual Studio solicita agregar una referencia al servidor que sirve de base para el proyecto.

En el caso aquí ilustrado, hay que seleccionar como proveedor de datos .Net Framework Data Provider for SQL Server. A continuación hay que identificar completamente al servidor indicando su ubicación (local) porque el servidor SQL Server y las herramientas de desarrollo Visual Studio están presentes en el puesto.

Los diferentes tipos de desarrollo ofrecidos por el CLR se exponen y se ilustran con un ejemplo. Los ejemplos están indicados en C#, pero también es posible realizar el mismo trabajo en Visual Basic.

3. Tipos de datos personalizados Los tipos de datos personalizados o UDT (User Defined Type) ofrecen al desarrollador la posibilidad de complementar los tipos propuestos por SQL Server para almacenar información en el formato que se desee. Sin embargo, los tipos UDT pueden resultar ser una trampa si se utilizan de forma distinta a su propósito, como por ejemplo para transformar SQL Server en una base orientada a objetos. Cuando se define una columna mediante un tipo UDT, se actualizan la totalidad de los campos constitutivos de este tipo, incluso aunque sólo se haya movilizado un valor. De igual manera, la manipulación de este tipo será más complicada y menos productiva que si se utiliza una estructura relacional clásica. Los tipos UDT, por el contrario, están bien adaptados cuando se trata de almacenar valores cuya estructura es compleja y en los que los diferentes campos están vinculados entre sí. Por ejemplo, un número de seguridad social: la estructura es compleja, pero está perfectamente definida y los datos están vinculados entre sí para componer un número único. Si se define un tipo UDT es posible definir funciones y procedimientos que manipulen este tipo. Debido a que la estructura es compleja, la verificación de los datos será más fácil en C# y en VB.Net que en Transact SQL. Para definir su propio tipo de dato es necesario, desde la ventana Solution Explorer, seleccionar Add - User-Defined Type desde el menú contextual asociado al proyecto.

A continuación aparecerá el cuadro de diálogo que permite insertar un nuevo componente en el proyecto. En el ejemplo siguiente, se crea el tipo TCliente.

Ya sólo queda definir la clase de forma completa en Visual Studio.

El despliegue está garantizado automáticamente en el momento de la ejecución del proyecto. Entonces es posible definir una tabla que posea una columna de tipo TCliente.

Cuando el proyecto se ejecute la próxima vez, se generará un error porque el tipo no se ha suprimido de forma automática. Hay que suprimir el tipo TCliente desde SQL Server antes de tratar de ejecutar el proyecto de nuevo.

4. Procedimiento almacenado El procedimiento almacenado se agrega al proyecto a través del menú contextual asociado al proyecto desde la ventana Explorador de soluciones o del menú Proyecto de Visual Studio.

Se ha creado el procedimiento InfoSysFich.cs. El objetivo de este procedimiento es obtener datos del sistema de archivos del servidor. Estos datos podrán resultar útiles al agregar un nuevo archivo a una base de datos, por ejemplo. Sin la integración del CLR en SQL Server, sólo se puede realizar esta tarea programando un procedimiento almacenado, con todos los riesgos que ello conlleva. using using using using using using

System; System.Data; System.Data.Sql; System.Data.SqlTypes; System.IO; Microsoft.SqlServer.Server;

public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void infoSysFich() {

try { //Obtener una referencia en SQL Pipe para comunicar // con la instancia SQL Server SqlPipe sqlpipe; sqlpipe = SqlContext.Pipe; // Crear una tabla de MetaData relativa a columnas de datos SqlMetaData[] metadata = new SqlMetaData[2]; metadata[0] = new SqlMetaData("unidad", SqlDbType.Char, 1); metadata[1] = new SqlMetaData("Mo libres", SqlDbType.BigInt); SqlDataRecord guardar = new SqlDataRecord(metadata); sqlpipe.SendResultsStart(guardar); //Recuperar los datos relativos a unidades DriveInfo[] losDatos=DriveInfo.GetDrives(); for (int i = 0; i < losDatos.Length; i++) { if ((losDatos[i].DriveType == DriveType.Fixed) && (losDatos[i].IsReady) ) { //Recuperar el apellido y el espacio libre guardar.SetString(0,losDatos[i].Name. Substring(0,1)); guardar.SetInt64(1,losDatos[i]. TotalFreeSpace/1048576); //Enviar los datos a SQL Server sqlpipe.SendResultsRow(guardar); } } . sqlpipe.SendResultsEnd(); } catch (Exception e) { throw new Exception("Error de ejecución",e); } } };

Antes de compilar, hay que comprobar que el nivel de permisos sea No seguro (Unsafe) en el nivel de propiedades del proyecto, ficha Base de datos.

La ejecución del procedimiento almacenado ofrece el siguiente resultado:

5. Desencadenador de base de datos El código CLR también puede aprovecharse para definir triggers de base de datos más complejos que los que se pueden definir fácilmente en Transact SQL. Al igual que con los elementos anteriores, hay que agregar un trigger de base de datos con el menú Proyecto - AñadirDesencadenador.

El siguiente ejemplo sólo será posible si la tabla Clientes posee una columna mail de tipo nvarchar(80). using using using using using using using

System; System.Data; System.Data.Sql; System.Data.SqlTypes; System.Data.SqlClient; System.Text.RegularExpressions; Microsoft.SqlServer.Server;

public partial class Triggers { // Enter existing table or view for the target and uncomment the attribute line [Microsoft.SqlServer.Server.SqlTrigger (Name="valideMail", Target="Clientes", Event="FOR INSERT, UPDATE")] public static void valideMail() { SqlConnection cnx= new SqlConnection("Context Connection=True"); cnx.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cnx; cmd.CommandText = "Select mail from inserted"; SqlDataReader resultado; resultado = cmd.ExecuteReader(); if (resultado.HasRows) resultado.Read(); if (Regex.IsMatch(resultado.GetSqlString(0).ToString(), "^([\\w-]+\\.)*?[\\w-]+@+\\.([\\w-]+\\.)*?[\\w]+$")){ return; }else{ //hay que generar un error... } return; } }

6. Función de cálculo de agregados Se pueden definir funciones propias de cálculo de agregado o UDA (User Defined Aggregate) para cubrir una necesidad concreta. El primer paso a seguir consiste en definir la función en el proyecto mediante el menú Proyecto Agregar - Agregado definido por el usuario.

El siguiente ejemplo muestra una función de agregado escrita en C#: using System;

using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using System.Text; using System.IO; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)] public struct reunir { public void Init() { i=1; } public void Accumulate(SqlString Value) { if (Value.IsNull) return; i = i + Value.ToSqlInt32().Value; } public void Merge(reunir Group) { i = i + Group.i; } public SqlInt32 Terminate() { return i; ; } // This is a place-holder member field private int i; }

SQLEsquema de la base de datos Sitios de Internet Artículos SQL Server 2008: http://www.devjoker.com/html/SQL-Server-2008.html MSDN: http://msdn2.microsoft.com/en-us/sqlserver/default.aspx Technet: http://technet.microsoft.com/es-es/sqlserver/default.aspx Página de SQL Server: http://www.microsoft.com/sql

Páginas españolas de SQL Server: http://www.microsoft.com/spain/sql Grupo de SQL Server: http://grupos.emagister.com/sql_server/1082 Blog de SQL Server en español: http://www.todosql.com/

Secuencias de comandos de la base de datos USE GESCOM GO CREATE TABLE stocks( referencia_art nvarchar(16) NOT NULL, almacen nchar(2) NOT NULL, ctdad_stk int NULL, limite_mini int NULL, limite_maxi int NULL, CONSTRAINT pk_stocks PRIMARY KEY(referencia_art,almacen) ); GO CREATE TABLE clientes( numero_cli int NOT NULL, apellido nvarchar(30) NOT NULL, nombre nvarchar(30) NOT NULL, direccion nvarchar(80) NULL, codigo_postal int NULL, ciudad nvarchar(30) NULL,

telefono char(14) NULL, CODEREP char(2) NULL, CONSTRAINT pk_clientes PRIMARY KEY (numero_cli) ); GO CREATE TABLE categorias( codigo_cat int IDENTITY(1,1) NOT NULL, etiqueta_cat varchar(30) NULL, CONSTRAINT pk_categorias PRIMARY KEY (codigo_cat) ); GO CREATE TABLE articulos( referencia_art nvarchar(16) NOT NULL, designacion_art nvarchar(200) NULL, preciounit_art decimal(10, 2) NULL, codigo_cat int NULL, CONSTRAINT pk_articulos PRIMARY KEY (referencia_art) ); GO CREATE TABLE pedidos( numero_pdo int IDENTITY(1350,1) NOT NULL, fecha_pdo datetime2(7) NULL, tasa_descuento numeric(2, 0) NULL,

numero_cli int NULL, estado_pdo char(2) NULL, CONSTRAINT pk_pedidos PRIMARY KEY (numero) ); GO CREATE TABLE lineas_pdo( numero_pdo int NOT NULL, linea int NOT NULL, articulo nvarchar(16) NULL, ctdad_pdo smallint NULL, CONSTRAINT pk_lineas_pdo PRIMARY KEY (numero_pdo,linea) ); GO CREATE TABLE histo_fac( numero_fac int IDENTITY(1000,1) NOT NULL, fecha_fac datetime NULL, numero_pdo int NULL, importe smallmoney NULL, estado_fac char(2) NULL, CONSTRAINT pk_histo_fac PRIMARY KEY(numero_fac) ); GO ALTER TABLE clientes ADD CONSTRAINT df_nom DEFAULT (‟anonimo‟) FOR apellido;

GO ALTER TABLE articulos ADD CONSTRAINT ck_articulos_prec CHECK (preciounit_art>=0); GO ALTER TABLE clientes ADD CONSTRAINT ck_cpo CHECK (codigo_postal between 1000 AND 52999); GO ALTER TABLE articulos ADD CONSTRAINT fk_articulos_categorias FOREIGN KEY(codigo_cat) REFERENCES categorias (codigo_cat); GO ALTER TABLE pedidos ADD CONSTRAINT fk_pedidos_clientes FOREIGN KEY(numero_cli) REFERENCES clients (numero_cli); GO ALTER TABLE histo_fac ADD CONSTRAINT fk_histo_fac_pedidos FOREIGN KEY(numero_pdo) REFERENCES pedidos (numero_pdo); GO ALTER TABLE lineas_pdo ADD CONSTRAINT fk_lineas_pdo_pedidos FOREIGN KEY(numero_pdo) REFERENCES pedidos (numero_pdo); GO ALTER TABLE lineas_pdo ADD CONSTRAINT fk_lineas_pdo_articulos FOREIGN KEY(articulo) REFERENCES articulos (referencia_art); GO

ALTER TABLE articulos ADD CONSTRAINT fk_articulos_categorias FOREIGN KEY(codigo_cat) REFERENCES categorias (codigo_cat); GO ALTER TABLE stocks ADD CONSTRAINT fk_stocks_articulos FOREIGN KEY(referencia_art) REFERENCES articulos (referencia_art);

Glosario ADO API CLR DMV ETL GC GAC ODS OLTP OLAP RID SOAP SQL SSIS TSQL TVF UDF

ActiveX Data Objectpal Application Programming Interface Common Language Runtime Dynamic Management View Extraction Transformation and Load Garbage Collector Global Assembly Cache Open Data Services On Line Transaction Processing OnLine Analytical Processing Row Identifier Simple Object Access Protocol Structure Query Language SQL Server Integration Services Transact SQL Table Valued Functions User Defined aggregate Function