• Author / Uploaded
• Fabian Ramirez

Citation preview

Celaya, Gto. a 2 de Agosto del 2001

I.S.C. Alejandro Guzmán Zazueta

Implementación de una Base de Datos con SQL Server 2000 Introducción Este curso provee al estudiante las habilidades técnicas requeridas para implementar una base de datos en SQL Server 2000. Este manejador de base de datos es uno de los líderes en el mercado de las pequeñas y medianas empresa.

Al terminar el Curso Al final del curso, el estudiante tendrá las habilidades para describir los elementos de SQL Server 2000; describir los conceptos básicos de la programación con Transact-SQL; crear y manipular bases de datos y sus componentes relacionados; implementar los cuatro tipos de integridad (integridad de entidad, integridad de dominio, integridad referencial e integridad definida por el usuario); plan para el uso de índices; crear y mantener índices; crear y mantener vistas de datos; implementar funciones definidas por el usuario; diseñar, crear y usar procedimientos almacenados; crear e implementar triggers; programar a través de múltiples servidores usando consultas distribuidas, transacciones distribuidas, y vistas particionadas; optimización de consultas; manejo de transacciones y candados para asegurar la concurrencia.

Contenido del Curso Módulo 1: Vistazo a SQL Server !"Qué es SQL Server !"Integración de SQL Server !"Bases de Datos de SQL Server !"Seguridad de SQL Server

Módulo 2: Vistazo a la Programación !"Herramientas para Programar en SQL Server !"El Lenguaje de Programación Transact-SQL !"Elementos del Transact-SQL !"Elementos adicionales al Lenguaje

Módulo 3: Crear Tipos de Datos y Tablas !"Creación de Tipos de Dato !"Creación de Tablas !"Generación de Scripts de Bases de Datos

Módulo 4: Implementación de Integridad de Datos !"Tipos de Integridad de Datos !"Forzar la Integridad de Datos !"Aplicación de restricciones Constraints !"Usar Defaults, Checks y Reglas !"Decisión de qué método emplear para forzar la integridad

Módulo 5: Planear Indices !"Introducción a Indices !"Arquitectura de los Indices !"Cómo mantiene SQL Server un Indice y su Estructura !"Decisión de cuáles columnas indexar !"Mantenimiento a Indices !"Introducción a Estadísticas !"Consultas a la tabla de Sysindexes

Módulo 6: Consulta de Información !"Consultas de Múltiples Tablas !"Consultas con JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN !"Consultas Avanzadas !"Funciones Agregadas !"Recomendaciones para Optimizar Consultas

Módulo 7: Manejando Transacciones y Bloqueos !"Introducción a Transacciones y Bloqueos !"Manejo de Transacciones !"Bloqueos en SQL Server Manejo de Bloqueos !"

Módulo 8: Implementación de Vistas !"Introducción a las Vistas !"Ventajas de las Vistas !"Definición de Vistas !"Modificación de Datos a través de Vistas !"Optimizar el Rendimiento Usando Vistas

Módulo 9: Implementación de Procedimientos Almacenados !"Introducción a los Procedimientos Almacenados !"Crear, Ejecutar, Modificar y Borrar Procedimientos Almacenados !"Usar Parámetros en los Procedimientos Almacenados !"Ejecución de los Procedimientos Almacenados !"Manejo de Mensajes de Error !"Definición de Desencadenadores (Triggers) !"Cómo Trabajar con Desencadenadores (Triggers) !"Consideraciones para el Rendimiento !"Manejo de Cursores

Módulo 10: Implementación de Funciones Definidas por el Usuario !"¿Qué es una Función Definida por el Usuario? !"Funciones escalares !"Funciones de valores de tabla de múltiples instrucciones !"Funciones de valores de tabla en línea

Módulo 11: Programación a través de Múltiples Servidores !"Introducción a Consultas Distribuidas !"Ligar Servidores !"Ejecutar Consultas en Servidores Ligados !"Manejar Transacciones Distribuidas !"Modificación de Datos en Servidores Vinculados !"Uso de Vistas Particionadas

SQL Server 2000 Base de Datos Facturación Los ejemplos del presente manual se basarán en la siguiente base de datos, la cual almacena la facturación de varios negocios, así como el control de su catálogo de productos y clientes.

1

SQL Server 2000

Módulo 1: Vistazo a SQL Server Introducción a SQL Server Microsoft SQL es una base de datos relacional cliente – servidor basada en el lenguaje de consultas (SQL, Structured Query Language). Base de Datos Es un conjunto de información que tiene un significado y que se encuentra organizada en entidades de manera que se minimice la redundancia, mantenga la integridad y seguridad de la información. Algunas de estas funciones se logran a través de un buen diseño de base de datos y otras con el apoyo de un manejador de base de datos (como lo es SQL Server 2000). Hay varias formas de organizar los datos en las bases de datos, pero las bases de datos relacionales son una de las formas más efectivas. Los sistemas de bases de datos relacionales son una aplicación de la teoría matemática de los conjuntos al problema de la organización de datos. En una base de datos relacional, los datos están organizados en tablas. La Arquitectura Cliente/Servidor • SQL utiliza la arquitectura Cliente/Servidor para separar las cargas de trabajo de tareas que se ejecutan en un servidor de aquellas que se ejecutan en el lado del cliente. • El Servidor es el responsable de la manipulación de la información y el Cliente de la presentación de datos al usuario. El cliente típicamente corre sobre una o más computadoras clientes, pero también puede correr sobre la computadora que hace las veces de servidor (donde se encuentra SQL Server). SQL Server maneja las bases de datos y controla los recursos disponibles en el Servidor, tales como memoria, ancho de banda y operaciones de disco entre múltiples solicitudes. La arquitectura Cliente/Servidor permite diseñar y distribuir aplicaciones óptimamente para una variedad de ambientes, la programación de interfases clientes permite a la aplicación correr en diferentes computadoras y comunicarse al servidor sobre la red. El RDBMS (Sistema de Bases de Datos Relacional) es el responsable de forzar la estructura de la base de datos, incluyendo:

2

SQL Server 2000 •

Mantener las relaciones entre los datos en la base de datos.

•

Asegurar que los datos sean almacenados correctamente – que no sean violadas las reglas que definen las relaciones entre los datos.

•

Recuperar todos los datos a un punto de consistencia en el caso de que el sistema llegue a fallar.

3

SQL Server 2000

CARACTERÍSTICAS DE SQL SERVER 2000 •

Escalabilidad y disponibilidad. El mismo motor de base de datos se puede utilizar en un intervalo de plataformas desde equipos portátiles que ejecutan Microsoft Windows 98 o por medio de grandes servidores con varios procesadores que ejecutan Microsoft Windows 2000. SQL Server 2000 Enterprise admite características como servidores federados, vistas indexadas y soporte para memorias grandes, que le permiten ampliarse a los niveles de rendimiento requeridos por los mayores sitios Web. Un grupo de servidores autónomos que cooperan para compartir el trabajo se denomina federación. Para obtener servidores de bases de datos federados, se crean bases de datos en cada servidor y después se dividen las tablas entre estas bases de datos.

•

Características de base de datos corporativas. El motor de base de datos relacional de SQL Server 2000 admite las características necesarias para satisfacer los exigentes entornos de procesamiento de datos. El motor de base de datos protege la integridad de los datos a la vez que minimiza la carga de trabajo que supone la administración de miles de usuarios modificando la base de datos simultáneamente. Las consultas distribuidas de SQL Server 2000 permiten hacer referencia a datos de varios orígenes como si fuesen parte de una base de datos de SQL Server 2000. Al mismo tiempo, el soporte para transacciones distribuidas protege la integridad de las actualizaciones de los datos distribuidos. La duplicación permite también mantener varias copias de datos a la vez que garantiza que las distintas copias permanezcan sincronizadas.

•

Facilidad de instalación, distribución y utilización. SQL Server 2000 incluye un conjunto de herramientas administrativas y de desarrollo que mejora el proceso de instalación, distribución, administración y uso de SQL Server en varios sitios. Estas características permiten entregar con rapidez aplicaciones de SQL Server que los clientes pueden implementar con un trabajo de instalación y administración mínimo.

•

Almacenamiento de datos. SQL Server 2000 incluye herramientas para extraer y analizar datos de resumen para el procesamiento analítico en línea. SQL Server incluye también herramientas para diseñar gráficamente las bases de datos y analizar los datos mediante preguntas en inglés.

4

SQL Server 2000

Servicio SQL Server El motor de base de datos de Microsoft SQL Server 2000 se ejecuta como un servicio en los sistemas operativos Microsoft Windows NT o Microsoft Windows 2000. No se ejecuta como servicio en Microsoft Windows 98 porque este sistema operativo no admite servicios. SQL Server puede ejecutarse también como un archivo ejecutable en Windows NT y Windows 2000, aunque se suele ejecutar como un servicio. El servicio SQL Server administra todos los archivos que componen las bases de datos que pertenecen a una instancia de SQL Server. Es el componente que procesa todas las instrucciones Transact-SQL enviadas desde las aplicaciones cliente de SQL Server. SQL Server admite también consultas distribuidas que recuperan datos de varios orígenes, no sólo SQL Server. El servicio SQL Server asigna recursos del equipo de un modo eficaz entre varios usuarios simultáneos. También implementa reglas de empresa definidas en procedimientos almacenados y desencadenadores, asegura la coherencia de los datos y evita problemas lógicos como el tener a dos personas intentando actualizar los mismos datos a la vez.

Servicio Agente SQL Server El Agente SQL Server admite características que permiten programar actividades periódicas en Microsoft SQL Server 2000 o notificar a los administradores de sistemas acerca de los problemas que han tenido lugar con el servidor. Los componentes del Agente SQL Server que implementan dichas funciones son: •

Trabajos Objetos definidos que constan de uno o más pasos para llevarse a cabo. Los pasos son las instrucciones Transact-SQL que se van a ejecutar. Por ejemplo, se pueden programar trabajos para que se ejecuten a horas específicas o a intervalos regulares.

•

Alertas Acciones que se realizan cuando se producen sucesos específicos, como un error concreto o errores de cierta gravedad, o cuando se alcanza el límite de espacio libre disponible definido en una base de datos. Se puede definir que la alerta realice acciones como el envío de mensajes de correo electrónico, mensajes de localización a un operador o la ejecución de un trabajo que resuelva el problema.

5

SQL Server 2000

•

Operadores Personas identificadas mediante sus cuentas de red o sus identificadores de correo electrónico, quienes pueden resolver los problemas del servidor. Pueden ser destinos de las alertas, a través de correo electrónico, un localizador o comandos de red.

Servicio Microsoft Search El servicio Microsoft Search es un motor de indexación y de búsqueda de texto. El estándar SQL-92 define únicamente capacidades de búsqueda de caracteres básicos: •

Buscar un valor de caracteres igual que, menor que o mayor que una constante de caracteres.

•

Buscar un valor de caracteres que contenga un patrón de cadena de caracteres.

El uso del servicio Microsoft Search permite a Microsoft SQL Server 2000 y SQL Server 7.0 admitir búsquedas más sofisticadas en columnas de cadenas de caracteres. El servicio Microsoft Search tiene dos funciones: • Indexación Implementa los catálogos e índices de texto que se definen en la base de datos. Acepta las definiciones de catálogos de texto, y las tablas y columnas que comprenden los índices en cada catálogo. Implementa las peticiones para llenar los índices de texto. •

Consultas Procesa consultas de búsqueda de texto. Determina qué entradas del índice cumplen los criterios de selección de texto. Por cada entrada que cumpla los criterios de selección, devuelve la identidad de la fila más un valor de clasificación al servicio de SQL Server, que utiliza esa información para construir el conjunto de resultados de la consulta. Los tipos de consultas compatibles incluyen búsquedas de: • •

Palabras o frases. Palabras próximas entre sí.

El motor de texto se ejecuta como un servicio llamado Microsoft Search en Microsoft Windows NT o Microsoft Windows 2000. Se instala cuando se selecciona la función de búsqueda de texto en la instalación personalizada. El Servicio de búsqueda de Microsoft no se instala en Microsoft Windows 95 o 6

SQL Server 2000 Microsoft Windows 98, aunque los clientes Windows 95 y Windows 98 pueden utilizar el servicio cuando se conectan a una instalación de SQL Server en equipos con Windows NT o Windows 2000. Los catálogos e índices de texto no se almacenan en una base de datos de SQL Server. Se almacenan en archivos separados que administra el servicio Microsoft Search. A los archivos de catálogo de texto pueden tener acceso únicamente el servicio Microsoft Search y el administrador del sistema de Windows NT o Windows 2000.

Servicio MS DTC El Coordinador de transacciones distribuidas de Microsoft (MS DTC) es un administrador de transacciones que permite que aplicaciones cliente incluyan varios orígenes de datos en una transacción. MS DTC coordina la confirmación de la transacción distribuida entre todos los servidores dados de alta en dicha transacción. Para que una instalación de Microsoft SQL Server pueda participar en una transacción distribuida deberá: •

Llamar a procedimientos almacenados de servidores remotos que ejecuten SQL Server.

7

SQL Server 2000 •

Promover automática o explícitamente una transacción local a una transacción distribuida y dar de alta los servidores remotos en la transacción.

•

Hacer actualizaciones distribuidas que actualicen datos de varios orígenes de datos OLE DB. Si dichos orígenes de datos OLE DB admiten la interfaz de transacciones distribuidas de OLE DB, SQL Server también puede darlos de alta en la transacción distribuida.

El servicio MS DTC coordina la apropiada terminación de las transacciones distribuidas para asegurar que, si todas las actualizaciones de todos los servidores son correctas, queden como definitivas o que, en caso de errores, se eliminen todas.

8

SQL Server 2000 Bases de Datos y Datos del Sistema Los sistemas Microsoft SQL Server 2000 tienen cuatro bases de datos del sistema: •

master La base de datos master registra toda la información del sistema de SQL Server. Registra todas las cuentas de inicio de sesión y todas las opciones de configuración del sistema. La base de datos master registra la existencia del resto de bases de datos, incluida la ubicación de los archivos de base de datos. La base de datos master registra la información de inicialización de SQL Server. Se recomienda que haga una copia de seguridad de la base de datos master cuando se haya realizado una actividad de mantenimiento de la base de datos en la base de datos de publicación, la base de datos de distribución o la base de datos de suscripciones que afecte a la configuración de la duplicación (especialmente cambios a Publicadores o Suscriptores habilitados).

•

tempdb La base de datos tempdb almacena todas las tablas y todos los procedimientos almacenados temporales. También satisface otras necesidades de almacenamiento temporal, como las tablas de trabajo generadas por SQL Server. La base de datos tempdb es un recurso global; las tablas y los procedimientos almacenados temporales de todos los usuarios conectados al sistema se almacenan en ella; tempdb se vuelve a crear cada vez que se inicia SQL Server, de forma que el sistema se inicia con una copia limpia de la base de datos. Como las tablas y los procedimientos almacenados temporales se eliminan automáticamente al desconectar y cuando se cierra el sistema no hay conexiones activas, en tempdb nunca hay nada que se tenga que guardar de una sesión de SQL Server a otra. De forma predeterminada, tempdb crece automáticamente según sea necesario al mismo tiempo que se ejecuta SQL Server. Sin embargo, de forma distinta a otras bases de datos, se reinicia a su estado original cada vez que se inicia el motor de base de datos. Si el tamaño definido en tempdb es pequeño, parte de su carga de proceso del sistema puede llevarse con el crecimiento automático de tempdb hasta el tamaño necesario para admitir la carga de trabajo cada vez que se inicia SQL Server. Puede evitar esta sobrecarga si utiliza ALTER DATABASE para aumentar el tamaño de tempdb.

9

SQL Server 2000 •

model La base de datos model se utiliza como plantilla para todas las bases de datos creadas en un sistema. Cuando se emite una instrucción CREATE DATABASE, la primera parte de la base de datos se crea copiando el contenido de la base de datos model, el resto de la nueva base de datos se llena con páginas vacías. Como tempdb se crea de nuevo cada vez que se inicia SQL Server, la base de datos model siempre tiene que existir en un sistema SQL Server.

•

msdb La base de datos msdb la utiliza el Agente SQL Server para programar alertas y trabajos, y para registrar operadores. La base de datos msdb de cada Suscriptor contiene información de trabajo similar para todos los agentes de suscripciones de extracción. La base de datos msdb del Distribuidor también contiene información de perfil del agente para todos los agentes de duplicación. Para proporcionar mejores opciones de recuperación si necesita restaurar una o varias bases de datos duplicadas, debe hacerse una copia de seguridad periódica de la base de datos msdb


En SQL Server 2000 y SQL Server 7.0, cada base de datos, incluidas las bases de datos del sistema, tiene su propio conjunto de archivos y no los comparte con otras bases de datos. Archivo de base de datos

Nombre del archivo Tamaño predeterminado, físico. configuración típica

Datos principales de master

MASTER.MDF

11,0 MB

Registro de master

MASTLOG.LDF

1,25 MB

Datos principales de tempdb TEMPDB.MDF

8,0 MB

Registro de tempdb

TEMPLOG.LDF

0,5 MB

Datos principales de model

MODEL.MDF

0,75 MB

Registro de model

MODELLOG.LDF

0,75 MB

Datos principales de msdb

MSDBDATA.MDF

12,0 MB

Registro de msdb

MSDBLOG.LDF

2,25 MB

Los tamaños de estos archivos pueden variar ligeramente en las distintas ediciones de SQL Server 2000. Cada base de datos de SQL Server 2000 contiene tablas del sistema que registran los datos que necesitan los componentes de SQL Server. El correcto

10

SQL Server 2000 funcionamiento de SQL Server depende de la integridad de la información de las tablas del sistema, por lo que Microsoft no admite que los usuarios actualicen directamente la información de las tablas del sistema. Microsoft proporciona un completo conjunto de herramientas administrativas que permite que los usuarios administren totalmente su sistema y administren todos los usuarios y los objetos de una base de datos. Los usuarios pueden utilizar las herramientas de administración, como el Administrador corporativo de SQL Server, para administrar directamente el sistema. Los programadores que generan secuencias de comandos Transact-SQL y procedimientos almacenados. Una importante función de SQL-DMO, los procedimientos almacenados del sistema y las instrucciones del lenguaje de definición de datos (DDL), es la de aislar las aplicaciones de los cambios de las tablas del sistema. En ocasiones, Microsoft tiene que cambiar las tablas del sistema en nuevas versiones de SQL Server para admitir las nuevas funciones que se agregan en dichas nuevas versiones. Las aplicaciones que utilicen instrucciones SELECT que hacen referencia directa a tablas del sistema suelen depender del formato anterior de las tablas del sistema. Puede que los sitios no puedan actualizar una versión de SQL a otra nueva hasta que todas las aplicaciones que seleccionen las tablas del sistema se hayan escrito otra vez. Microsoft no admite la definición de desencadenadores en las tablas del sistema; pueden alterar el funcionamiento del sistema.

API SQL-DMO Los Objetos de administración distribuida SQL (SQL-DMO) encapsulan los objetos que se encuentran en las bases de datos de Microsoft® SQL Server™ 2000. SQLDMO permite que las aplicaciones escritas en lenguajes que admiten Automatización o COM administren todas las partes de una instalación de SQL Server. SQL-DMO es la interfaz de programación de aplicaciones (API) que utiliza el Administrador corporativo de SQL Server 2000. Por tanto, las aplicaciones que utilicen SQL-DMO pueden realizar todas las funciones del Administrador corporativo de SQL Server.

11

SQL Server 2000 Arquitectura de seguridad La arquitectura de un sistema de seguridad se basa en usuarios y grupos de usuarios. Esta ilustración muestra cómo los usuarios y los grupos locales y globales de Microsoft® Windows NT® 4.0 y Windows® 2000 pueden conectarse a las cuentas de seguridad de Microsoft SQL Server™, y cómo maneja SQL Server las cuentas de seguridad, independientemente de las cuentas de Windows NT 4.0 y Windows 2000.


El grupo local CORPUSERS contiene dos usuarios y un grupo global, Mktg, que también contiene dos usuarios. SQL Server permite utilizar directamente los grupos locales y globales de Windows NT 4.0 y Windows 2000 para organizar sus cuentas de usuario. Además, los usuarios de Windows NT 4.0 Fred y Jerry, que no pertenecen a un grupo de Windows NT 4.0, pueden agregarse a una instancia de SQL Server directamente como usuarios de Windows NT 4.0 (por ejemplo, Fred) o como usuarios de SQL Server (Jerry). SQL Server amplía aún más este modelo con el uso de las funciones. Las funciones son grupos de usuarios organizados con fines administrativos, como los grupos de Windows NT 4.0 o Windows 2000, pero se crean en SQL Server cuando no existe un grupo equivalente en Windows NT 4.0 o Windows 2000. Por ejemplo, la función Managers contiene el grupo global Mktg de Windows NT 4.0 y los usuarios de Windows NT Frank y Fred. SQL Server ofrece también seguridad en las aplicaciones, mediante el uso de funciones de aplicación de base de datos individuales.

12

SQL Server 2000

Módulo 2: Vistazo a la Programación Lenguaje Transact – SQL SQL Server utiliza el lenguaje Transact SQL, una versión del Lenguaje Estructurado de Consultas (SQL), como su lenguaje de consultas y lenguaje de programación. SQL es un conjunto de comandos que permiten especificar la información que se desea recuperar o modificar. Con Transact SQL se pueden acceder y consultar datos, actualizar y administrar sistemas de bases de datos relaciónales. El Instituto Americano de Estándares Nacionales (ANSI) y la Organización Internacional de Estándares (ISO) ha definido estándares para SQL. Transact SQL soporta los últimos estándares publicados en 1992, llamados ANSI SQL 92, más extensiones que se proveen para incrementar su funcionalidad. Utilizar identificadores El nombre de un objeto de base de datos se conoce como su identificador. Todo en Microsoft SQL Server puede tener un identificador. Servidores, bases de datos y objetos de bases de datos tales como tablas, vistas, columnas, índices, desencadenadores, procedimientos, restricciones, reglas, etc. pueden tener identificadores. Se requiere que la mayor parte de los objetos tengan identificadores; pero para ciertos objetos, como las restricciones, son opcionales. El identificador de un objeto se crea cuando se define el objeto. A continuación, el identificador se utiliza para hacer referencia al objeto. Por ejemplo, la siguiente instrucción crea una tabla con el identificador TableX y dos columnas con los identificadores Llave y Descripción: CREATE TABLE TableX (Llave INT PRIMARY KEY, Descripción VARCHAR(80) ) Esta tabla tiene también una restricción sin nombre. La restricción PRIMARY KEY no tiene identificador. Hay dos clases de identificadores: identificadores normales Siguen las reglas de formato de los identificadores. Los identificadores normales no están delimitados cuando se usan en instrucciones de Transact-SQL. SELECT * FROM TableX WHERE Llave = 124

13

SQL Server 2000 identificadores delimitados Se incluyen entre comillas dobles (") o corchetes ([ ]). Los identificadores que siguen las reglas de formato de los identificadores pueden estar delimitados o no. SELECT * FROM [TableX] --Delimiter is optional. WHERE [KeyCol] = 124 --Delimiter is optional. Los identificadores que no cumplen alguna de las reglas de los identificadores deben estar delimitados en las instrucciones Transact-SQL. SELECT * FROM [My Table] --El identificador un espacio y usa una palabra reservada. WHERE [order] = 10 --El identificador es una palabra reservada. Ambos identificadores, normales y delimitados, deben contener entre 1 y 128 caracteres. En el caso de las tablas temporales locales, el identificador puede tener un máximo de 116 caracteres. Reglas de los identificadores normales 1. El primer carácter debe ser alguno de los siguientes: • Una letra, como aparece definida por el estándar Unicode 2.0. La definición Unicode de letras incluye los caracteres latinos de la "a" a la "z" y de la "A" a la "Z", además de los caracteres de letras de otros idiomas. •

El signo de subrayado (_), arroba (@) o número (#). Ciertos símbolos al principio de un identificador tienen un significado especial en SQL Server. Un identificador que empieza con el signo arroba indica un parámetro o una variable local. Un identificador que empieza con un signo número indica una tabla o procedimiento temporal. Un identificador que empieza con un signo de número doble (##) indica un objeto temporal global. Algunas funciones de SQL Server tienen nombres que empiezan con un doble signo de arroba (@@). Para evitar confusiones con estas funciones, se recomienda que no utilice nombres que empiecen con @@.

2. Los caracteres subsiguientes pueden ser: • Letras, tal como se definen en el estándar Unicode 2.0. •

Números decimales de tipo latino básico o demás secuencias de comandos nacionales.

•

El signo de arroba, dólar ($), número o subrayado.

14

SQL Server 2000 3. El identificador no debe ser una palabra reservada de Transact-SQL. SQL Server reserva las versiones en mayúsculas y minúsculas de las palabras reservadas. 4. No se permiten los caracteres especiales o los espacios incrustados. Cuando se utilicen en instrucciones Transact-SQL, los identificadores que no cumplan estas reglas deben aparecer delimitados por comillas dobles o corchetes.

Lenguaje de Manipulación de Datos El propósito de una base de datos en SQL Server es almacenar datos y entonces hacerlos disponibles a usuarios y aplicaciones autorizadas. Mientras administradores de base de datos crean y mantienen la base de datos, usuarios trabaja con los contenidos de la base de datos. • Accesando o recuperando datos existentes. • Cambiando o actualizando datos existentes. • Adicionando o insertando datos nuevos. • Borrando datos existentes. Esto se efectúa a través de las cuatro sentencias del lenguaje Transact SQL: • La sentencia SELECT es usada para recuperar datos. • La sentencia UPDATE es usada para modificar datos existentes. • La sentencia INSERT es usada para agregar nuevos registros a las tablas. • La sentencia DELETE es usada para remover registros que ya no necesite de una tabla. El funcionamiento de estas cuatro sentencias se cubrirá ampliamente más adelante.

15

SQL Server 2000

Variables Locales y Globales Variables Locales DECLARE @local_variable

tipoDato

Las variables se declaran en el cuerpo de un proceso por lotes o procedimiento con la instrucción DECLARE, y se les asignan valores con una instrucción SET o SELECT. Las variables de cursor pueden declararse con esta instrucción y utilizarse con otras instrucciones relacionadas con los cursores. Después de su declaración, todas las variables se inicializan con NULL (nulo). DECLARE @ClientePk SamllInt DECLARE @NegocioPk SmallInt Set @ClientePk = 2 Set @NegocioPk = 2 Select NomCliente, DomCliente From Cliente Where NegocioPk = @NegocioPk And ClientePk = @ClientePk

Variables Globales Las variables globales de Transact-SQL constituyen un tipo de función en SQL Server 2000 y ahora reciben el nombre de funciones, a continuación se muestran algunas de las más usadas:

@@FETCH_STATUS Devuelve el estado de la última instrucción FETCH de cursor ejecutada sobre cualquier cursor que la conexión haya abierto. Valor de retorno Descripción 0

La instrucción FETCH se ejecutó correctamente.

-1

La instrucción FETCH ha finalizado con error o la fila estaba más allá del conjunto de resultados.

-2

Falta la fila recuperada.

Sintaxis @@FETCH_STATUS 16

SQL Server 2000 Observaciones Al ser @@FETCH_STATUS global para todos los cursores de una conexión, debe usarse con cuidado. Después de ejecutar una instrucción FETCH, la comprobación de @@FETCH_STATUS se debe realizar antes de que se ejecute otra instrucción FETCH sobre otro cursor. El valor de @@FETCH_STATUS no está definido antes de producirse las recuperaciones en la conexión.

@@ERROR Devuelve el número de error de la última instrucción Transact-SQL ejecutada. Sintaxis @@ERROR Observaciones Cuando Microsoft® SQL Server completa con éxito la ejecución de una instrucción Transact-SQL, en @@ERROR se establece el valor 0. Si se produce un error, se devuelve un mensaje de error. @@ERROR devuelve el número del mensaje de error, hasta que se ejecute otra instrucción Transact-SQL. Puede ver el texto asociado a un número de error @@ERROR en la tabla de sistema sysmessages. Al restablecerse @@ERROR con cada instrucción ejecutada, debe comprobarlo inmediatamente después de la instrucción que desea validar o guardarlo en una variable local para examinarlo posteriormente.

@@ROWCOUNT Devuelve el número de filas afectadas por la última instrucción. Sintaxis @@ROWCOUNT Observaciones En esta variable se establece 0 al ejecutarse una instrucción que no devuelva filas, como por ejemplo la instrucción IF.

17

SQL Server 2000 @@TRANCOUNT Devuelve el número de transacciones activas en la conexión actual. Sintaxis @@TRANCOUNT Observaciones La instrucción BEGIN TRANSACTION incrementa @@TRANCOUNT en 1. ROLLBACK TRANSACTION reduce @@TRANCOUNT a 0. Cada instrucción COMMIT TRANSACTION o COMMIT WORK disminuye @@TRANCOUNT en uno.

18

SQL Server 2000

Tipos de Datos En Microsoft SQL Server, cada columna, variable local, expresión y parámetro dispone de un tipo de datos relacionado, que es un atributo que especifica el tipo de datos (integer, character, money, etc) que el objeto puede contener. SQL Server suministra un conjunto de tipos de datos del sistema que define todos los tipos de datos que pueden utilizarse con SQL Server. El conjunto de tipos de datos suministrados por el sistema se muestra debajo.

Numéricos exactos Integers Bigint

Datos enteros (números enteros) comprendidos entre -2^63 (9223372036854775808) y 2^63 -1 (9223372036854775807).

Int

Datos enteros (números enteros) comprendidos entre -2^31 (2.147.483.648) y 2^31 - 1 (2.147.483.647).

Smallint

Datos enteros comprendidos entre 215 (-32.768) y 215 - 1 (32.767).

Tinyint

Datos enteros comprendidos 0 y 255.

Bit

Datos enteros con valor 1 ó 0.

Decimal y Numeric 





Números de precisión y escala fijas. Cuando se utiliza la precisión máxima, los valores permitidos están comprendidos entre - 10^38 +1 y 10^38 - 1.

Numeric

Funcionalmente equivalente a decimal.

Money y Smallmoney money

Valores de moneda comprendidos entre -263 (922.337.203.685.477,5808) y 263 - 1 (+922.337.203.685.477,5807), con una precisión de una diezmilésima de la unidad monetaria.

Smallmoney Valores de moneda comprendidos entre -214.748,3648 y +214.748,3647, con una precisión de una diezmilésima de la unidad monetaria. 19

SQL Server 2000 Numéricos con aproximación Flota

Números con precisión de coma flotante comprendidos entre -1,79E + 308 y 1,79E + 308.

Real

Números con precisión de coma flotante comprendidos entre -3,40E + 38 y 3,40E + 38.

Datetime

Datetime y Smalldatetime Datos de fecha y hora comprendidos entre el 1 de enero de 1753 y el 31 de diciembre de 9999, con una precisión de 3,33 milisegundos.

Smalldatetime

Datos de fecha y hora comprendidos entre el 1 de enero de 1900 y el 6 de junio de 2079, con una precisión de un minuto.

Timestamp Un número único para toda la base de datos que se actualiza cada vez que se actualiza una fila.

Char

Cadenas de Caracteres Datos de caracteres no Unicode de longitud fija con una longitud máxima de 8.000 caracteres.

Varchar

Datos no Unicode de longitud variable con un máximo de 8.000 caracteres.

Text

Datos no Unicode de longitud variable con una longitud máxima de 231 - 1 (2.147.483.647) caracteres.

Binary

Cadenas binarias Datos binarios de longitud fija con una longitud máxima de 8.000 bytes.

Varbinary

Datos binarios de longitud variable con una longitud máxima de 8.000 bytes.

Image

Datos binarios de longitud variable con una longitud máxima de 2.147.483.647 bytes.

20

SQL Server 2000

Operadores Un operador es un símbolo que indica una acción que va a ser usada en una o más expresiones. Operadores de Comparación Operador = > < >=

!
, =, , !< (operadores de comparación)

•

NOT

•

AND

•

ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

•

= (asignación)

Cuando en una expresión dos operadores tengan el mismo nivel de precedencia de operador, se evalúan de izquierda a derecha en función de su posición dentro de la expresión.

22

SQL Server 2000 Lenguaje de Control de Flujo La siguiente tabla muestra las palabras clave de control de flujo de Transact-SQL. Palabra clave

Descripción

BEGIN...END

Define un bloque de instrucciones.

BREAK

Sale del ciclo WHILE más interno.

CONTINUE

Reinicia un ciclo WHILE.

GOTO label

Continúa el proceso en la instrucción que sigue a la etiqueta definida por label.

IF...ELSE

Define una ejecución condicional y, opcionalmente, una ejecución alternativa si la condición es FALSE.

RETURN

Sale incondicionalmente.

WAITFOR

Establece una demora en la ejecución de instrucciones.

WHILE

Repite instrucciones mientras una condición específica sea TRUE.

BEGIN...END Encierra un conjunto de instrucciones Transact-SQL de forma que se pueda ejecutar. BEGIN y END son palabras clave del lenguaje de control de flujo. Sintaxis BEGIN { sql_statement | statement_block } END Argumentos {sql_statement | statement_block} Es cualquier instrucción o grupo de instrucciones Transact-SQL válidos definidos como bloque de instrucciones. Observaciones Los bloques BEGIN...END pueden anidarse.

23

SQL Server 2000

BREAK Hace que se salga del ciclo WHILE más interno. Se ejecutan las instrucciones que aparecen después de la palabra clave END, que marca el final del ciclo.

CONTINUE Reinicia un ciclo WHILE. Las instrucciones que se encuentren a continuación de la palabra clave CONTINUE se omiten. CONTINUE se suele activar, aunque no siempre, con una comprobación IF. En este ejemplo, si el promedio de precio es menor de 30, el bucle WHILE dobla los precios y, a continuación, selecciona el precio máximo. Si el precio máximo es menor o igual que 50, el ciclo WHILE se reinicia y dobla los precios de nuevo. Este bucle continúa la duplicación de precios hasta que el precio máximo sea mayor que 50 y, a continuación, sale del ciclo WHILE e imprime un mensaje. WHILE (SELECT AVG(PrecioVta) FROM Producto) < 30 BEGIN UPDATE Producto SET PrecioVta = PrecioVta * 2 SELECT MAX(PrecioVta) FROM Producto IF (SELECT MAX(PrecioVta) FROM Producto) > 50 BREAK ELSE CONTINUE END

GOTO Altera el flujo de ejecución y lo dirige a una etiqueta. Las instrucciones TransactSQL que siguen a una instrucción GOTO se pasan por alto y el procesamiento continúa en el punto que marca la etiqueta. Las instrucciones GOTO y las etiquetas se pueden utilizar en cualquier punto de un procedimiento, lote o bloque de instrucciones. Las instrucciones GOTO se pueden anidar. Sintaxis Definición de la etiqueta: label :

Alteración de la ejecución: GOTO label

24

SQL Server 2000

Argumentos label Es el punto a continuación del cual comienza el procesamiento cuando una instrucción GOTO especifica esa etiqueta. Las etiquetas se deben ajustar a las normas de los identificadores. Las etiquetas se pueden utilizar como comentarios, tanto si se usa GOTO como si no. Observaciones GOTO puede aparecer dentro de las instrucciones de control de flujo condicional, en bloques de instrucciones o en procedimientos, pero no se puede dirigir a una etiqueta externa al lote. La ramificación con GOTO se puede dirigir a una etiqueta definida antes o después de la instrucción GOTO.

DECLARE @iContador Int SET @iContador = 1 Ciclo: Select @iContador SET @iContador = @iContador + 1 IF (@iContador 50 BREAK ELSE CONTINUE END

29

SQL Server 2000 Utilizar Comentarios Los comentarios son cadenas de texto que no se ejecutan incluidas en el código de un programa; también se conocen como observaciones. Los comentarios se pueden usar para documentar código o partes deshabilitadas temporalmente de instrucciones y lotes de Transact-SQL que se están diagnosticando. La utilización de comentarios hace más fácil el mantenimiento futuro del código del programa. Los comentarios se usan, a menudo, para guardar el nombre de un programa, el nombre del autor y las fechas de los cambios importantes del código. Los comentarios se pueden usar para describir cálculos complicados o para explicar determinado método de programación. Microsoft SQL Server admite dos tipos de caracteres para indicar comentarios: •

-- (doble guión). Estos caracteres para los comentarios se pueden usar en la misma línea que el código que se va a ejecutar o en una línea aparte. Todo lo que se encuentre entre los dos guiones y el final de la línea es parte del comentario. En el caso de que un comentario ocupe varias líneas, los guiones dobles deben aparecer al principio de cada línea de comentarios. Para obtener más información acerca de cómo utilizar los caracteres para los comentarios, consulte -- (Comment).

•

/* ... */ (pares de caracteres de barra diagonal y asterisco). Estos caracteres para los comentarios se pueden usar en la misma línea que el código que se va a ejecutar, en líneas separadas o, incluso, en el código ejecutable. Todo lo incluido entre el par de apertura de comentario (/*) y el par de cierre de comentario (*/) se considera parte del comentario. En un comentario de varias líneas, el par de caracteres de apertura de comentario (/*) debe iniciar el comentario y el par de caracteres de cierre de comentario (*/) debe finalizarlo. Ningún otro carácter de comentario debe aparecer en ninguna línea del comentario. Para obtener más información acerca del uso de los caracteres de comentario /* ... */ , consulte /* ... */ (Comment).

Nota: No usar comentarios dentro de las instrucciones de SQL. Por ejemplo entre el SELECT y el FROM

30

SQL Server 2000

Módulo 3: Creando Tipos de Datos y Tablas Crear Tipos de Datos Definidos por el Usuario Los tipos de datos definidos por el usuario se basan en los tipos de datos del sistema de Microsoft SQL Server 2000. Se pueden utilizar cuando varias tablas deben almacenar el mismo tipo de datos en una columna y desea asegurarse de que dichas columnas tienen exactamente el mismo tipo de datos, longitud y condición que la aceptación de valores NULL. Por ejemplo, es posible crear un tipo de datos definido por el usuario con el nombre CodigoPostal a partir del tipo de datos char. Cuando cree un tipo de datos definido por el usuario, deberá suministrar estos tres parámetros: •

Nombre

•

Tipo de datos del sistema en el que se basa el nuevo tipo de datos

•

Aceptación de valores NULL (si el tipo de datos permite valores NULL) Cuando no está definida explícitamente la aceptación de valores NULL, se asignará en función de la configuración de los valores NULL ANSI predeterminada para la base de datos o para la conexión.

Nota Si se crea un tipo de datos definido por el usuario en la base de datos model, existirá en todas las nuevas bases de datos definidas por el usuario. Sin embargo, si el tipo de datos se crea en una base de datos definida por el usuario, sólo existirá en esa base de datos. Para adicionar un tipo de dato definido por el usuario, se deberá ejecutar el procedimiento almacenado sp_addtype, este lo adiciona a la tabla del sistema systypes. Una vez que el tipo de dato es creado, ya lo puede usar dentro de las instrucciones CREATE TABLE y ALTER TABLE. Sintaxis: sp_addtype [@typename =] utTipoDatoFisico, [@phystype =] tipoDatoFisico [, [@nulltype =] 'tipoNull'] ut

Estas dos primeras letras identificarán a los tipos de datos definidos por el usuario, seguidos por el nombre que recibirá éste.

tipoDatoFisico

Tipo de Dato de SQL server

31

SQL Server 2000

TipoNull

Este parámetro puede tener dos valores NULL o NOT NULL. El default, en caso de que no se especifique, es el valor NULL.

Nota : En caso de que alguno de los parámetros tengan espacio o paréntesis, este se deberá poner entre comillas simples. Ejemplo: Exec Sp_addtype utNombre , ‘varchar(20)’, ‘NOT NULL’ Exec Sp_addtype utFecFactura, datetime, NULL Borrar Tipos de Dato Definidos por el Usuario Sintaxis: Sp_droptype ‘NomTipoDato’ Ejemplo: Exec sp_droptype ‘utNombre’

32

SQL Server 2000

Creación de Tablas Cuando se crea una tabla, se deberá especificar el nombre de la tabla, nombre de las columnas y el tipo de datos de las columnas. Los nombres de las columnas deberán ser únicos para una tabla en específico. No deberá de existir un campo que se llame igual en dos tablas y tenga significado diferente. Algunos nombres recomendados para campos de uso común son los siguientes. Pk Llave primaria Id Identificador Mto Monto Fecha Fecha Cve Clave No Número Nom Nombre Ejemplo: EmpleadoPk OperacionPk Considere las siguientes características cuando crea tablas: • Dos mil millones de tablas por cada base de datos y 1.024 columnas por tabla. • 8092 bytes por registro (esto no aplica sobre los tipos de dato image y text). Sintaxis CREATE TABLE nombre table (NombreColumna TipoDato | NULL | NOT NULL | | ... n) Ejemplo: CREATE TABLE Negocio ( NegocioPk SmallInt NOT NULL, NomNegocio VarChar(40) NOT NULL, DomNegocio VarChar(60) NULL, RFCNegocio VarChar(15) NULL ) Borrando una Tabla Remueve la definición de una tabla y todos sus datos, índices, triggers, constraints y permisos. Las vistas o procedimientos almacenados que hagan referencia a la tabla eliminada se deben eliminar con la instrucción DROP VIEW o DROP PROCEDURE.

33

SQL Server 2000

Sintaxis: DROP TABLE NombreTabla Ejemplo: DROP TABLE Negocio Adicionando y Borrando Columnas en una Tabla Modifica la definición de una tabla , vía adicionando o borrando columnas y constraints, o deshabilitando o habilitando constraints. ALTER TABLE NomTabla ADD NomColumna Varchar(20) | NULL | NOT NULL Ejemplo: ALTER TABLE Negocio ADD TelNegocio Varchar(24) NULL ALTER TABLE NomTabla DROP COLUMN NomColumna Ejemplo: ALTER TABLE Negocio DROP COLUMN TelNegocio

34

Módulo 4: Implementando Integridad de Datos Integridad de los Datos La Integridad de los Datos garantiza la calidad de los datos de la base de datos. Por ejemplo, si se especifica para un Cliente el valor de su columna NegocioPk = 1, la base de datos no debe permitir que ningún otro Negocio tenga el mismo valor de identificador. Si tiene una columna RFCNegocio para la que se prevea que en las primeras 4 posiciones sean letras el siguiente un guión, enseguida 6 números, seguidos de un guión y por ultimo 2 letras y un número, la base de datos no debe aceptar que se tecleen valores que no se correspondan con esa definición. En la tabla de Cliente hay una columna NegocioPk en la que se almacene el número de Negocio al que pertenece ese cliente, la base de datos sólo debe permitir valores que correspondan a los números de Negocio de la tabla Negocio. Dos pasos importantes en el diseño de las tablas son la identificación de valores válidos para una columna y la determinación de cómo forzar la integridad de los datos en la columna. Hay cuatro categorías de integridad de datos: !"Integridad de entidad. !"Integridad de dominio. !"Integridad referencial. !"Integridad definida por el usuario. Hay varias maneras de forzar cada tipo de integridad. Tipo de integridad Entidad

Dominio

Referencial Definida por el Usuario

Opciones recomendadas Restricción PRIMARY KEY Restricción UNIQUE Propiedad IDENTITY Definición predeterminada (DEFAULT) Restricción FOREIGN KEY Restricción CHECK, RULE NOT NULL Restricción FOREIGN KEY Restricción CHECK Todas las restricciones en columnas y tablas de CREATE TABLE Procedimientos almacenados Triggers

Integridad de Entidad La integridad de entidad define una fila como entidad única para una tabla determinada. La integridad de entidad fuerza la integridad de la columna o columnas de los identificadores o la clave principal de una tabla (mediante índices, restricciones UNIQUE, restricciones PRIMARY KEY o propiedades IDENTITY).

35

Integridad de Dominio La integridad de dominio viene dada por la validez de las entradas para una columna determinada. Puede forzar la integridad de dominio si restringe el tipo (mediante tipos de datos), el formato (mediante las reglas RULE y las restricciones CHECK), o el intervalo de valores posibles (mediante restricciones FOREIGN KEY, restricciones CHECK, definiciones DEFAULT, definiciones NOT NULL y reglas). Integridad Referencial La integridad referencial protege las relaciones definidas entre las tablas cuando se crean o se eliminan registros. En Microsoft SQL Server 2000, la integridad referencial se basa en las relaciones entre las llaves foráneas y las llaves primarias o entre las claves externas y las claves únicas. La integridad referencial garantiza que los valores clave son coherentes en las distintas tablas. Para conseguir esa coherencia, es preciso que no haya referencias a valores inexistentes y que, si cambia el valor de una clave, todas las referencias a ella se cambien en consecuencia en toda la base de datos. Cuando se fuerza la integridad referencial, SQL Server impide a los usuarios: • • •

Agregar registros a una tabla relacionada si no hay ningún registro asociado en la tabla principal. Cambiar valores en una tabla principal de manera que queden registros huérfanos en una tabla relacionada. Eliminar registros de una tabla principal cuando hay registros relacionados coincidentes.

Por ejemplo, con las tablas Negocio y Producto de la base de datos Facturacion, la integridad referencial se basa en la relación entre la clave externa (NegocioPk) de la tabla Producto y la clave principal (NegocioPK) de la tabla Negocio.

36

Integridad Definida por el Usuario La integridad definida por el usuario le permite definir reglas de la compañía específicas que no pertenecen a ninguna otra categoría de integridad. Todas las categorías de integridad son compatibles con la integridad definida por el usuario. Constraints (Restricciones) Los constraints son restricciones que se le adicionan a la base de datos con el objeto de mantener la integridad referencial de manera automática (PRIMARY KEY Y FOREIGN KEY), así como la integridad de Dominio a través de Defaults y Checks. Creando una restricción de Índice UNIQUE Crea un índice único (uno en el que no se permite que dos filas tengan el mismo valor de índice). Microsoft SQL Server comprueba si hay valores duplicados cuando se crea el índice (si ya existen datos) y realiza la comprobación cada vez que se agregan datos con una instrucción INSERT o UPDATE. Si existen valores de clave duplicados, se cancela la instrucción CREATE INDEX y se devuelve un mensaje de error con el primer duplicado. Un índice único no se puede crear en una columna individual ni en columnas múltiples (índice compuesto) en las que la clave completa (todas las columnas de esa clave) sea NULL en más de una fila; para la indexación, éstas se tratan como valores duplicados. Cuando existe un índice único, las instrucciones UPDATE o INSERT que generen valores de clave duplicada se deshacen y SQL Server muestra un mensaje de error. Esto se cumple incluso si las instrucciones UPDATE o INSERT cambian muchas filas pero crean un único duplicado. Si se realiza un intento de introducir datos donde existe un índice único y se ha especificado la cláusula IGNORE_DUP_KEY, sólo fallarán las filas que infrinjan el índice UNIQUE. Cuando se procesa una instrucción UPDATE, IGNORE_DUP_KEY no tiene efecto. SQL Server no permite la creación de un índice único sobre columnas que ya contengan valores duplicados, ya esté establecido o no IGNORE_DUP_KEY. Si se intenta, SQL Server muestra un mensaje de error; los duplicados deben eliminarse antes de que se pueda crear un índice único sobre la columna o columnas. En el siguiente ejemplo se crea un índice único sobre la columna RFCCliente al momento de crear la tabla de Cliente. CONSTRAINT unRFCCliente UNIQUE NONCLUSTERED (RFCCliente) Para eliminar un CONSTRAINT ALTER TABLE Cliente DROP CONSTRAINT unRFCCliente

37

El siguiente ejemplo crea un índice único ya estando creada la tabla. CREATE UNIQUE NONCLUSTERED INDEX unRFCCliente ON Cliente (RFCCliente) Para eliminar un índice DROP INDEX Cliente.unRFCCliente El siguiente ejemplo crea un índice único ya estando creada la tabla. ALTER TABLE Cliente ADD CONSTRAINT un RFCCliente UNIQUE NONCLUSTERED (RFCCliente) Para eliminar un Constraint con ALTER TABLE ALTER TABLE Cliente DROP CONSTRAINT unRFCCliente

Creando un Constraint de Tipo CHECK Las restricciones CHECK se pueden: • Crear cuando se crea la tabla, durante el proceso de definición de la misma. • Agregar a una tabla ya existente. Las tablas y las columnas pueden contener varias restricciones CHECK. • Modificar o eliminar si ya existen. Por ejemplo, puede modificar la expresión utilizada por la restricción CHECK en una columna de la tabla. Nota: Para modificar una restricción CHECK mediante Transact-SQL, antes deberá eliminar la restricción CHECK existente y, a continuación, volver a crearla con la nueva definición. Cuando se agrega una restricción CHECK a una tabla ya existente, puede aplicarse a los nuevos datos o bien ampliarse también a los datos ya existentes. De forma predeterminada, la restricción CHECK se aplica a los datos existentes y a los nuevos datos. Puede resultar útil aplicar la restricción sólo a los datos nuevos cuando los datos existentes ya cumplen la nueva restricción CHECK o cuando una regla de empresa requiere que se exija la restricción únicamente a partir de ahora. Por ejemplo, una restricción anterior podía requerir que los códigos postales estuvieran limitados a cinco cifras, mientras que una nueva restricción requiere que los códigos postales sean de nueve cifras. Los datos que figuran en los antiguos códigos postales de cinco cifras siguen siendo válidos y coexisten con los nuevos datos de los nuevos códigos postales, que tienen nueve cifras. Por lo tanto, la nueva restricción sólo debe comprobarse con los nuevos datos.

38

No obstante, conviene actuar con precaución cuando se agrega una restricción sin comprobar los datos existentes ya que, de ese modo, se omiten los controles de Microsoft SQL Server que exigen las reglas de integridad para la tabla. Este ejemplo muestra una restricción con una restricción de modelo sobre los datos de caracteres introducidos en la columna RFCNegocio de la tabla Negocio al momento de su creación. CONSTRAINT ckRFCNegocio CHECK (RFCNegocio LIKE '[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z]' OR RFCNegocio LIKE '[A-Z][A-Z][A-Z][A-Z]-[0-9][0-9][0-9][0-9][0-9][0-9]-[A-Z][A-Z][AZ]') Y enseguida ya estando creada la tabla de negocio ALTER TABLE Negocio WITH NOCHECK ADD CONSTRAINT ckRFC CHECK (RFCNegocio LIKE '[A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z]' OR RFCNegocio LIKE '[A-Z][A-Z][A-Z][A-Z]-[0-9][0-9][0-9][0-9][0-9][0-9]-[A-Z][A-Z][AZ]') El ejemplo siguiente agrega una restricción a una columna existente de la tabla. La columna tiene un valor que infringe la restricción; por tanto, se utiliza WITH NOCHECK para impedir que la restricción se valide contra las filas existentes y para permitir que se agregue la restricción. ALTER TABLE Producto WITH NOCHECK ADD CONSTRAINT ckMinimo CHECK (Minimo > 0) Para eliminar este CONSTRAINT ALTER TABLE Producto DROP CONSTRAINT ckMinimo

Restricciones de Tipo PRIMARY KEY (clave principal) Clave Principal (PRIMARY KEY) Columna o combinación de columnas que identifican exclusivamente a una fila de cualquier otra fila de una tabla. La clave principal (PK) no debe ser NULL y debe tener un índice exclusivo. Se utiliza normalmente una clave principal para combinaciones con claves externas (claves no principales coincidentes) de otras tablas. Creando Restricciones de Tipo Llave Primaria (PRIMARY KEY) Una restricción PRIMARY KEY única se puede: • Crear cuando se crea la tabla, durante el proceso de definición de la misma. 39

• •

Agregar a una tabla existente, siempre que no exista ya otra restricción PRIMARY KEY (una tabla sólo puede tener una restricción PRIMARY KEY). Modificar o eliminar, si ya existe. Por ejemplo, es posible que desee que la restricción PRIMARY KEY de la tabla haga referencia a otras columnas o bien modificar el orden de la columna, el nombre del índice, la opción de agrupamiento o el factor de relleno de la restricción PRIMARY KEY. No se puede cambiar la longitud de una columna definida con una restricción PRIMARY KEY.

Nota Para modificar una restricción PRIMARY KEY mediante Transact-SQL, antes deberá eliminar la restricción PRIMARY KEY existente y, a continuación, volver a crearla con la nueva definición. Cuando se agrega una restricción PRIMARY KEY a una o a varias columnas de la tabla, SQL Server comprueba los datos que hay en las columnas para asegurarse de que sigan las reglas de las claves principales: • Que no haya ningún valor Null. • Que no haya ningún valor duplicado. Si se agrega una restricción PRIMARY KEY a una columna con valores duplicados o valores NULL, SQL Server devuelve un error y no agrega la restricción. No se puede agregar una restricción PRIMARY KEY que no cumpla estas reglas. SQL Server crea automáticamente un índice único para exigir que la restricción PRIMARY KEY sea única. Si aún no existe un índice agrupado en la tabla o no se ha especificado explícitamente, se crea un índice agrupado único para exigir la restricción PRIMARY KEY. Importante No se puede eliminar una restricción PRIMARY KEY si una restricción FOREIGN KEY de otra tabla está haciendo referencia a ella; antes, es preciso eliminar la restricción FOREIGN KEY. Este ejemplo muestra cómo se puede suministrar un nombre para la restricción PRIMARY KEY al momento de la creación de la tabla. Esta restricción se utiliza en la columna NegocioPk de la tabla Negocio. NegocioPk Int Not Null CONSTRAINT PKNegocio PRIMARY KEY CLUSTERED

El siguiente ejemplo agrega un CONSTRAINT de tipo PRIMARY KEY a una tabla que ya existe pero que no cuenta con llave primaria: ALTER TABLE Negocio ADD CONSTRAINT PkNegocio PRIMARY KEY CLUSTERED (NegocioPk)

40

Para borrar una restricción de tipo primary Key: ALTER TABLE Negocio DROP CONSTRAINT PkNegocio

Restricciones de tipo FOREIGN KEY (clave externa) Clave externa (FK, Foreign Key) Una clave externa (FK) es una columna o combinación de columnas que se utiliza para establecer y exigir un vínculo entre los datos de dos tablas. Se crea un vínculo entre dos tablas al agregar en una tabla la columna o columnas que contienen los valores de la clave principal de la otra tabla. Esta columna se convierte en una clave externa para la segunda tabla. Los valores de claves externas deben ser copias de los valores principales de clave; no debe existir un valor en la clave externa excepto NULL a menos que el mismo valor exista en la clave principal. Una clave externa puede ser NULL; si cualquier parte de una clave externa compuesta es NULL, toda la clave externa debe ser NULL. Puede crear una clave externa mediante la definición de una restricción FOREIGN KEY cuando cree o modifique una tabla. Por ejemplo, la tabla DetalleFactura de la base de datos Factura tiene un vínculo con la tabla Producto porque hay una relación lógica entre los detalles facturados. Las columnas NegocioPk y ProductoPk de la tabla DetalleFactura coinciden con las columnas de la clave principal de la tabla Producto. Las columnas NegocioPk y ProductoPk de la tabla DetalleFactura es la clave externa para la tabla Producto.

No es necesario que una restricción FOREIGN KEY esté vinculada únicamente a una restricción PRIMARY KEY de otra tabla; también puede definirse para que haga referencia a las columnas de una restricción UNIQUE de otra tabla. Una restricción FOREIGN KEY puede contener valores NULL, pero si alguna columna de una restricción FOREIGN KEY compuesta contiene valores NULL, se omitirá la

41

comprobación de la restricción FOREIGN KEY. Cuando se agrega una nueva fila de datos a una tabla o se cambian los datos de una fila existente, los valores de la columna o columnas de la clave externa deberán encontrarse en la columna o columnas de la clave principal de la otra tabla o bien deberán ser NULL. Nota Una restricción FOREIGN KEY puede hacer referencia a columnas de tablas de la misma base de datos o a columnas de una misma tabla (tablas con referencia a sí mismas). Un ejemplo de tabla con referencia a sí misma sería una tabla de empleados que contuviera tres columnas: númeroEmpleado, nombreEmpleado númeroEmpleadoResponsable. y Dado que los responsables también son empleados, hay una relación de clave externa desde la columna númeroEmpleadoResponsable a la columna númeroEmpleado. Aunque el propósito principal de una restricción FOREIGN KEY es el de controlar los datos que pueden almacenarse en la tabla de la clave externa, también controla los cambios realizados en los datos de la tabla de la clave principal. Por ejemplo, si se elimina la fila en la tabla Producto y el identificador de ese producto se utiliza para productos que se facturaron y que figuran en la tabla DetalleFactura, se rompe la integridad relacional entre las dos tablas: los detalles facturados del producto eliminado quedarán sin correspondencia en la tabla DetalleFactura, al perderse los vínculos con los datos de la tabla Producto. Con una restricción FOREIGN KEY se evita esta situación. Exige la integridad referencial al asegurar que no se puedan realizar cambios en los datos de la tabla de la clave principal si esos cambios anulan el vínculo con los datos de la tabla de la clave externa. Si se intenta eliminar la fila de una tabla de la clave principal o cambiar un valor de la clave principal, la acción no progresará si el valor de la clave principal cambiado o eliminado corresponde a un valor de la restricción FOREIGN KEY de otra tabla. Para cambiar o eliminar una fila de una restricción FOREIGN KEY, antes debe eliminar los datos de la clave principal en la tabla de la clave externa o bien cambiar los datos de la clave externa en la tabla de la clave externa y vincular, de ese modo, la clave externa con otros datos de clave principal. Una restricción FOREIGN KEY puede ser un índice adecuado por dos motivos: • Los cambios en las restricciones PRIMARY KEY se comprueban con restricciones FOREIGN KEY en las tablas relacionadas. • Las columnas de la clave externa suelen utilizarse en los criterios de combinación cuando los datos de las tablas relacionadas se combinan en consultas mediante la correspondencia de la columna o columnas de la restricción FOREIGN KEY de una tabla y la columna o columnas de la clave única o principal de la otra tabla. Un índice permite que Microsoft SQL Server busque con rapidez los datos relacionados en la tabla de la clave externa. No obstante, no es necesario crear este índice. Pueden combinarse los datos de dos tablas relacionadas aunque no se hayan definido restricciones PRIMARY KEY o FOREIGN KEY entre ellas, pero una relación de clave externa entre dos tablas indica que éstas han sido

42

optimizadas para su combinación utilice las claves como criterio.

en

una

consulta

que

Crear y modificar restricciones FOREIGN KEY Las restricciones FOREIGN KEY se pueden: • Crear cuando se crea la tabla, durante el proceso de definición de la misma. • Agregar a una tabla ya existente, siempre que la restricción FOREIGN KEY esté vinculada a una restricción PRIMARY KEY o UNIQUE de otra o de la misma tabla. Una tabla puede contener varias restricciones FOREIGN KEY. • Modificar o eliminar si ya existen restricciones FOREIGN KEY. Por ejemplo, es posible que desee que la restricción FOREIGN KEY de la tabla haga referencia a otras columnas. No se puede cambiar la longitud de una columna definida con una restricción FOREIGN KEY. Nota Para modificar una restricción FOREIGN KEY mediante Transact-SQL, antes deberá eliminar la restricción FOREIGN KEY existente y, a continuación, volver a crearla con la nueva definición. Cuando se agrega una restricción FOREIGN KEY a una o a varias columnas de la tabla, Microsoft SQL Server comprueba de forma predeterminada los datos que hay en las columnas para asegurarse de que estén todos los valores, excepto NULL, en las columnas de la restricción PRIMARY KEY o UNIQUE a la que se hace referencia. Sin embargo, se puede impedir que SQL Server compruebe los datos de la columna con la nueva restricción y exigirle que agregue la nueva restricción, independientemente de los datos que haya en la columna. Esta opción resulta de utilidad cuando los datos existentes ya cumplen con la nueva restricción FOREIGN KEY o cuando una regla de empresa requiere que se exija la restricción sólo a partir de ese punto. No obstante, debe actuar con precaución cuando agregue una restricción sin comprobar los datos existentes porque así se omiten los controles de SQL Server que garantizan la integridad de los datos de la tabla. Para crear una restricción FOREIGN KEY durante la creación de una tabla: Las restricciones de claves de varias columnas se crean como restricciones de tabla. En la base de datos Facturacion, la tabla Producto incluye una restricción PRIMARY KEY multicolumna. Este ejemplo muestra cómo hacer referencia a esta clave desde la tabla DetalleFactura(un nombre explícito de restricción es opcional).

ALTER TABLE table

43

ADD [ CONSTRAINT constraint_name ] [ FOREIGN KEY ] REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]

Ejemplo: A continuación se muestra cómo se crea una llave foránea con borrado en cascada (el valor predeterminado es NO ACTION), en la definición de la tabla. CONSTRAINT FKProducto1 FOREIGN KEY (NegocioPk,ProductoPk) REFERENCES Producto (NegocioPk,ProductoPk) On DELETE CASCADE Ejemplo Para crear una restricción FOREIGN KEY en una tabla existente con actualización en cascada (el valor predeterminado es NO ACTION) ALTER TABLE DetalleFactura ADD CONSTRAINT FKProducto1 FOREIGN KEY (NegocioPk,ProductoPk) REFERENCES Producto (NegocioPk,ProductoPk) ON UPDATE CASCADE Para eliminar una restricción de tipo FOREIGN KEY ALTER TABLE DetalleFactura DROP CONSTRAINT FkProducto1

Nota: Los nombres de las restricciones se consideran objetos de la base de datos por lo que deben ser únicos, como se observa en la restricción del párrafo anterior el nombre de la llave foránea tiene un número consecutivo.

44

Regla (RULE) Objeto de base de datos enlazado a una columna o tipo de datos definido por el usuario que especifica los datos que se pueden escribir en dicha columna. Cada vez que un usuario escribe o modifica un valor (con una instrucción INSERT o UPDATE), SQL Server lo comprueba contra la regla más reciente enlazada a la columna especificada, por ejemplo, para comprobar los límites o listas. Los datos escritos antes de la creación y enlace de una regla no se comprueban. Cuando se crea un objeto llamado Regla. Cuando se enlaza una regla a una columna o un tipo de datos definido por el usuario, especifica los valores aceptables que se pueden insertar en esa columna. Las reglas, que son una característica de compatibilidad con versiones anteriores, realizan algunas de las mismas funciones que las restricciones de comprobación. Las restricciones CHECK, creadas mediante la palabra clave CHECK de ALTER o CREATE TABLE, son la forma preferida y estándar de restringir los valores de una columna (se pueden definir restricciones múltiples en una o múltiples columnas). Una columna o tipo de datos definido por el usuario sólo puede tener una regla enlazada. Sin embargo, una columna puede tener una regla y una o más restricciones de comprobación asociadas con ella. Cuando esto es así, se evalúan todas las restricciones. Sintaxis CREATE RULE regla AS expresiónCondición Argumentos regla Es el nombre de la nueva regla. Los nombres de las reglas deben cumplir las reglas de los identificadores. Especificar el nombre del propietario de la regla es opcional. expresiónCondición Son las condiciones que definen la regla. Una regla puede ser cualquier expresión que sea válida en una cláusula WHERE y puede incluir elementos como operadores aritméticos, operadores relacionales y predicados (por ejemplo, IN, LIKE, BETWEEN). Una regla no puede hacer referencia a columnas u otros objetos de base de datos. Se pueden incluir funciones integradas que no hagan referencia a objetos de base de datos. expresiónCondición incluye una variable. El signo (@) precede a cada variable local. La expresión hace referencia al valor que se introduce con la instrucción UPDATE o INSERT. Se puede utilizar cualquier nombre o símbolo para representar el valor cuando se crea la regla, pero el primer carácter debe ser @. Observaciones La instrucción CREATE RULE no puede combinarse con otras instrucciones Transact-SQL en un único proceso por lotes. Las reglas no se aplican a los datos ya existentes en la base de datos en el momento en que se crearon las reglas y no 45

se pueden enlazar a los tipos de datos del sistema. Una regla sólo se puede crear en la base de datos actual. Una vez creada la regla, ejecute sp_bindrule para enlazarla a una columna o a un tipo de datos definido por el usuario. La regla debe ser compatible con el tipo de datos de la columna. Una regla no se puede enlazar a una columna text, image o timestamp. Asegúrese de incluir las constantes de fecha y de caracteres entre comillas simples (‘) y de preceder las constantes binarias con 0x. Por ejemplo, “@valor LIKE A%” no se puede utilizar como regla para una columna numérica. Si la regla no es compatible con la columna a la que se ha enlazado, Microsoft SQL Server devuelve un mensaje de error cuando se inserte un valor (no cuando se enlaza la regla). Una regla enlazada a un tipo de datos definido por el usuario sólo se activa cuando se intenta insertar un valor o actualizar una columna de la base de datos del tipo de datos definido por el usuario. Como las reglas no prueban las variables, no debe asignar un valor a una variable de tipo de datos definido por el usuario que sería rechazada por una regla enlazada a una columna del mismo tipo de datos. Para obtener un informe de una regla, utilice sp_help. Para mostrar el texto de una regla, ejecute sp_helptext con el nombre de la regla como parámetro. Para cambiar el nombre de una regla, utilice sp_rename. Una regla debe quitarse (mediante DROP RULE) antes de crear otra con el mismo nombre y debe cancelarse el enlace (mediante sp_unbindrule) antes de quitarla. Utilice sp_unbindrule para cancelar el enlace de una regla a una columna. Una nueva regla se puede enlazar a una columna o tipo de datos sin cancelar el enlace de la anterior; la nueva regla anula la anterior. Las reglas enlazadas a columnas tienen siempre preferencia sobre las reglas enlazadas a tipos de datos definidos por el usuario. Enlazar una regla a una columna substituye una regla ya enlazada al tipo de datos definido por el usuario de esa columna. Pero el enlace de una regla a un tipo de datos no substituye una regla enlazada a una columna de ese tipo de datos definido por el usuario. Si una columna tiene un valor predeterminado y una regla asociada a ella, el valor predeterminado debe encontrarse en el dominio definido por la regla. Un valor predeterminado que esté en conflicto con una regla no se inserta nunca. SQL Server genera un mensaje de error cada vez que intenta insertar tal valor predeterminado. Ejemplos Regla con una lista. Este ejemplo crea una regla que restringe los valores actuales que se escriben en las columnas a las que la regla está enlazada, a sólo aquellos enumerados en la regla. CREATE RULE ruLista AS @list IN ('1389', '0736', '0877')

46

Regla con un modelo. Este ejemplo crea una regla que sigue un modelo de dos caracteres cualquiera con un guión a continuación, cualquier número de caracteres (o no caracteres) y un entero entre 0 y 9 al final. CREATE RULE ruPatron AS @value LIKE '_ _-%[0-9]' Regla con un intervalo. Este ejemplo crea una regla que restringe el intervalo de enteros que se insertan en las columnas a las que la regla está enlazada. CREATE RULE ruRango AS @range >= 1000 AND @range < 5000

Para ligar una regla a una columna de la base de datos: EXEC sp_bindrule 'ruRango', 'Producto.Maximo'

Para des ligar una regla: EXEC sp_unbindrule 'Producto.Maximo'

Para Borrar una Regla: DROP RULE ruRango

47

Módulo 5: Planeando Índices Índices Los índices de una base de datos son similares a los índices que hay en los libros. En un libro, un índice le permite encontrar información rápidamente sin necesidad de leer todo el libro. En una base de datos, un índice permite que el programa de la base de datos busque datos en una tabla sin necesidad de examinar toda la tabla. El índice de un libro es una lista de palabras con los números de las páginas en las que se encuentra cada palabra. Un índice de una base de datos es una lista de los valores de una tabla con las posiciones de almacenamiento de las filas de la tabla donde se encuentra cada valor. Se puede crear índices en una sola columna o en una combinación de columnas de una tabla; los índices se implementan en forma de árboles B. Un índice contiene una entrada con una o varias columnas (la clave de búsqueda) de cada fila de una tabla. Un árbol B se ordena con la clave de búsqueda y se puede buscar de forma eficiente en cualquier subconjunto principal de la clave de búsqueda. Por ejemplo, un índice en las columnas A, B y C puede buscarse de forma eficiente en A, en A y B, y en A, B y C. Mientras que la mayor parte de los libros contienen un índice general de palabras, nombres, lugares, etc., las bases de datos contienen índices individuales para tipos o columnas de datos seleccionados. Es como un libro con un índice para los nombres de las personas y otro índice para los lugares. Cuando cree una base de datos y la optimice para mejorar el rendimiento, es recomendable que cree índices para las columnas que se utilizan en las consultas con el fin de buscar datos. En la base de datos de ejemplo Ferretera suministrada por este manual, la tabla Negocio tiene un índice en la columna NegocioPk. En la siguiente ilustración se muestra cómo almacena el índice para cada valor NegocioPk y señala a las filas de datos de la tabla con cada valor.

48

Cuando SQL Server ejecuta una instrucción para buscar datos en la tabla Negocio a partir de un valor de NegocioPk específico, reconoce el índice para la columna NegocioPk y lo utiliza para buscar los datos. Si no hay un índice, empieza desde el principio de la tabla y va buscando, fila por fila, el valor de NegocioPk especificado. SQL Server crea automáticamente índices para determinados tipos de restricciones (por ejemplo, restricciones de clave principal (PRIMARY KEY) y de no duplicados (UNIQUE). También puede personalizar las definiciones de la tabla mediante la creación de índices independientes de las restricciones. No obstante, las ventajas que ofrecen los índices por lo que respecta al rendimiento también tienen un costo. Las tablas con índices necesitan más espacio de almacenamiento en la base de datos. Asimismo, es posible que los comandos de inserción, actualización o eliminación de datos sean más lentos y precisen más tiempo de proceso para mantener los índices. Cuando diseñe y cree índices, deberá asegurarse de que las ventajas en el rendimiento compensan suficientemente el costo adicional en cuanto a espacio de almacenamiento y recursos de proceso. Un índice de SQL Server es una estructura asociada con una tabla que aumenta la rapidez de la obtención de las filas de la tabla. Un índice contiene claves generadas a partir de una o varias columnas de la tabla. Dichas claves están almacenadas en una estructura que permite que SQL Server busque rápida y eficientemente la fila o filas asociadas con los valores de cada clave.

Consideraciones al Diseñar un Índice Cuando Microsoft SQL Server ejecuta una consulta, el optimizado de consultas evalúa los costos de los métodos disponibles para recuperar los datos y utiliza el método más eficiente. Puede explorar una tabla o bien utilizar un índice, en el caso de que exista. Cuando SQL Server explora una tabla, empieza desde el principio y examina todas las filas de la tabla, una por una, para extraer las que cumplen los criterios de la consulta. Cuando SQL Server utiliza un índice, busca la ubicación de almacenamiento de las filas necesarias para la consulta y extrae únicamente esas filas. Cuando se plantee la posibilidad de crear un índice en una columna, tenga en cuenta si se utilizarán columnas indexadas en las consultas y de qué modo. Los índices son útiles cuando una consulta: • Busca filas que coincidan con un valor de clave de búsqueda específico (consulta de coincidencia exacta). En una comparación de coincidencia exacta, la consulta utiliza la instrucción WHERE para especificar una entrada de una columna con un valor determinado. Por ejemplo: WHERE emp_id = 'VPA30890F'

49

•

Busca filas con valores de clave de búsqueda en un intervalo de valores (consulta de intervalo). En una consulta de intervalo, la consulta especifica cualquier entrada con un valor que se encuentre entre otros dos valores. Por ejemplo: WHERE job_lvl BETWEEN 9 and 12 o bien, WHERE job_lvl >= 9 and job_lvl 10 AND NomNegocio LIKE 'F%' GO

58

La selección devuelve la información de los productos y proveedores de cualquier combinación de partes suministrada por una empresa cuyo nombre empieza con la letra F y el precio del producto es superior a 10 pesos. Cuando en una consulta simple se hace referencia a múltiples tablas, ninguna de las referencias a las columnas debe ser ambigua. En el ejemplo anterior, ambas tablas, Producto y Negocio, tienen una columna llamada NegocioPk. Cualquier nombre de columna que esté duplicado en varias tablas a las que se hace referencia en la consulta debe ser calificado con el nombre de la tabla. Todas las referencias a las columnas NegocioPk del ejemplo se califican. Cuando el nombre de una columna no está duplicado en varias de las tablas usadas en la consulta, las referencias al mismo no tienen que calificarse con el nombre de la tabla. Esto se muestra en el ejemplo anterior. Algunas veces, una instrucción SELECT es difícil de entender porque no hay nada que indique qué tabla ha suministrado cada columna. Se puede mejorar la comprensión de la consulta si todas las columnas se califican con sus nombres de tabla. La comprensión puede aumentarse más si se usan alias de tablas, especialmente cuando los propios nombres de las tablas se deben calificar con los nombres de las bases de datos y de los propietarios. Éste es el mismo ejemplo, con la salvedad de que se han asignado alias a las tablas y las columnas se han calificado con los alias de las tablas para mejorar su comprensión: SELECT A.NomNegocio, B.NomProducto, B.Existencia FROM Negocio as A JOIN Producto AS B ON (A.NegocioPk = B.NegocioPk) WHERE B.PrecioVenta > 10 AND A.NomNegocio LIKE 'F%' GO Los ejemplos anteriores han especificado las condiciones de combinación en la cláusula FROM, lo que constituye el método recomendado. Esta consulta contiene la misma condición de combinación especificada en la cláusula WHERE: SELECT A.NomNegocio, B.NomProducto, B.Existencia FROM Negocio as A, Producto AS B WHERE A.NegocioPk = B.NegocioPk AND B.PrecioVenta > 10 AND A.NomNegocio LIKE 'F%' GO La lista de selección de una combinación puede hacer referencia a todas las columnas de las tablas combinadas o a cualquier subconjunto de las columnas. No es necesario que la lista de selección contenga columnas de cada tabla de la combinación. Por ejemplo, en una combinación de tres tablas, sólo se puede usar una tabla para pasar de una de las dos tablas a la tercera y no es necesario que se haga referencia a ninguna columna de la tabla intermedia en la lista de selección.

59

Aunque las condiciones de combinación suelen tener comparaciones de igualdad (=), también se puede especificar otros operadores de comparación o relacionales, así como otros predicados. Cuando SQL Server procesa Joins, el motor de consultas elige el método más eficiente entre varias posibilidades para procesar la combinación. Aunque la ejecución física de varias combinaciones utiliza muchas optimizaciones distintas, la secuencia lógica es: • Se aplican las condiciones de combinación de la cláusula FROM. • Las condiciones de combinación y de búsqueda de la cláusula WHERE. • Se aplican las condiciones de búsqueda de la cláusula HAVING. La secuencia lógica puede, en ocasiones, influir en los resultados de la consulta si se cambian las condiciones entre las cláusulas FROM y WHERE. La mayor parte de las consultas que usan un Join (combinación) se pueden volver a escribir con una subconsulta (una consulta anidada dentro de otra consulta), y la mayor parte de las subconsultas se pueden escribir como Joins. Nota Las tablas no se pueden combinar directamente en columnas ntext, text o image. Sin embargo, las tablas se pueden combinar indirectamente en columnas ntext, text o image mediante la utilización de SUBSTRING. Por ejemplo, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.columnaTexto, 1, 20) = SUBSTRING(t2.columnaTexto, 1, 20) realiza una combinación interna de dos tablas en los primeros 20 caracteres de cada columna de texto de las tablas t1 y t2. Utilizar combinaciones internas (INNER JOIN) Una combinación interna es aquella en la que los valores de las columnas que se están combinando se comparan mediante un operador de comparación. En el estándar SQL-92, las combinaciones internas se pueden especificar en las cláusulas FROM o WHERE. Éste es el único tipo de combinación que SQL-92 admite en la cláusula WHERE. Las combinaciones internas especificadas en la cláusula WHERE se conocen como combinaciones internas al estilo antiguo. Esta consulta de Transact-SQL es un ejemplo de una combinación interna. SELECT A.NomNegocio, P.NomProducto, P.PrecioVenta FROM Negocio AS a INNER JOIN Producto AS p ON a.NegocioPk = p.NegocioPk ORDER BY a.NomProducto DESC Esta combinación interna se conoce como una combinación equivalente. Devuelve todas las columnas seleccionadas de ambas tablas y sólo devuelve las filas en las que haya un valor igual en la columna de la combinación.

60

A.NomNegocio --------------------Ferretera Papalo Ferretera Papalo Ferretera Abraham Ferretera Abraham

P.NomProducto P.Precio ------------------------ -------------Tornillo 0.50 Taladros 300 Arena 200 Alambron 550

Utilizar combinaciones externas(OUTER JOIN) Las combinaciones internas sólo devuelven filas cuando hay una fila de ambas tablas, como mínimo, que coincide con la condición de la combinación. Las combinaciones internas eliminan las filas que no coinciden con alguna fila de la otra tabla. Sin embargo, las combinaciones externas devuelven todas las filas de una de las tablas o vistas mencionadas en la cláusula FROM, como mínimo, siempre que tales filas cumplan con alguna de las condiciones de búsqueda de WHERE o HAVING. Todas las filas se recuperarán de la tabla izquierda a la que se haya hecho referencia con una combinación externa izquierda, y de la tabla derecha a la que se haya hecho referencia con una combinación externa derecha. En una combinación externa completa, se devuelven todas las filas de ambas tablas. Microsoft SQL Server usa las siguientes palabras clave de SQL-92 para las combinaciones externas especificadas en una cláusula FROM: • LEFT OUTER JOIN o LEFT JOIN • RIGHT OUTER JOIN o RIGHT JOIN • FULL OUTER JOIN o FULL JOIN SQL Server admite tanto la sintaxis de combinaciones externas de SQL-92 como la sintaxis heredada para especificar las combinaciones externas que se basan en la utilización de los operadores *= y =* en la cláusula WHERE. Se recomienda utilizar la sintaxis de SQL-92 porque no está sujeta a las ambigüedades que se producen, en ocasiones, en las combinaciones externas heredadas de TransactSQL. Utilizar combinaciones exteriores izquierdas Imagine una combinación de la tabla Negocio y de la tabla Clientes en sus columnas ciudad. Los resultados sólo muestran los Clientes que viven en ciudades donde haya algún Negocio. Para incluir todos los Clientes en el resultado, con independencia de que un Negocio se encuentre o no en la misma ciudad, use una combinación exterior izquierda de SQL-92. A continuación se muestra la consulta y los resultados de la combinación externa izquierda de Transact-SQL: SELECT a.NomCliente, p.NomNegocio FROM Cliente a LEFT OUTER JOIN Negocio p ON a.ciudad = p.ciudad and a.NegocioPk = p.NegocioPk ORDER BY p.NomNegocio ASC, a.NomCliente ASC

61

Este es el conjunto de resultados: NomCliente NomNegocio ------------------------------------------------------- ----------------------------------------Erick Guzmán NULL Luis Eduardo Medina NULL Ivan Cruz Guzmán Ferretera Abraham Ios Cruz Ferretera Papalo LEFT OUTER JOIN incluye en el resultado todas las filas de la tabla Cliente, tanto si hay una coincidencia en la columna ciudad de la tabla Negocio como si no la hay. Observe que, en los resultados, no hay datos coincidentes con la mayor parte de los Clientes enumerados, con lo que estas filas contienen valores NULL en la columna NomNegocio. Utilizar combinaciones exteriores derechas Imagine una combinación de la tabla Cliente y de la tabla Negocio en sus columnas ciudad. Los resultados sólo muestran los Clientes que viven en ciudades donde haya algún Negocio. El operador de combinación exterior derecha de SQL-92, RIGHT OUTER JOIN, indica que todas las filas de la segunda tabla se deben incluir en los resultados, con independencia de si hay datos coincidentes en la primera tabla. Para incluir todos los Negocios en los resultados, sin tener en cuenta si hay un Negocio en la ciudad, use una combinación externa derecha de SQL-92. A continuación se muestra la consulta de Transact-SQL y los resultados de la combinación externa derecha: SELECT a.NomCliente, p.NomNegocio FROM Cliente AS a RIGHT OUTER JOIN Negocio AS p ON a.ciudad = p.ciudad and a.NegocioPk = p.NegocioPk ORDER BY p.NomNegocio ASC, a.NomCliente ASC NomCliente NomNegocio ----------------------------------------------- -----------------------------------------------------------Ivan Cruz Guzmán Ferretera Abraham NULL Ferretera la Nueva Ios Cruz Ferretera Papalo Para retener la información que no coincida al incluir las filas no coincidentes en los resultados de una combinación, utilice una combinación externa completa. Microsoft SQL Server proporciona el operador de combinación externa completa, FULL OUTER JOIN, que incluye todas las filas de ambas tablas, con independencia de que la otra tabla tenga un valor coincidente o no. Imagine una combinación de la tabla Cliente y de la tabla Negocio en sus columnas ciudad. Los resultados sólo muestran los Clientes que viven en ciudades donde hay algún Negocio. El operador de combinación externa completa de SQL-92, FULL OUTER

62

JOIN, indica que todas las filas de ambas tablas se van a incluir en los resultados, con independencia de que haya datos coincidentes en las tablas. Para incluir todos los Negocios y todos los Clientes en el resultado, sin tener en cuenta si un Cliente se encuentra en la misma ciudad, use una combinación externa completa. A continuación se muestran la consulta y los resultados de la combinación externa completa de Transact-SQL: SELECT a.NomCliente, p.NomCiudad FROM Cliente a FULL OUTER JOIN Negocio p ON a.ciudad = p.ciudad ORDER BY p.NomNegocio ASC, a.NomCliente ASC NomCliente NomNegocio -------------------------------------------------- ---------------------------------------------------Erick Guzmán NULL Luis Eduardo Medina NULL Ivan Cruz Guzmán Ferretera Abraham NULL Ferretera la Nueva Ios Cruz Ferretera Papalo Utilizar Combinaciones Cruzadas(Cross Join) Una combinación cruzada que no tenga una cláusula WHERE da como resultado el producto cartesiano de las tablas involucradas en la combinación. El tamaño del conjunto de resultados de un producto cartesiano es igual al número de filas de la primera tabla multiplicado por el número de filas de la segunda tabla. A continuación se muestra un ejemplo de una combinación cruzada de TransactSQL: SELECT a.NomCliente, p.NomNegocio FROM Cliente a CROSS JOIN Negocio p ORDER BY a.NomCliente DESC El conjunto de resultados contiene 12 filas (Cliente tiene 4 filas y Negocio tiene 3; 4 multiplicado por 3 es igual a 12). NomCliente NomNegocio -------------------------------------------------------- ---------------------------------------------------Luis Eduardo Medina Ferretera Abraham Luis Eduardo Medina Ferretera Papalo Luis Eduardo Medina Ferretera la Nueva Ivan Cruz Guzmán Ferretera la Nueva Ivan Cruz Guzmán Ferretera Papalo Ivan Cruz Guzmán Ferretera Abraham Ios Cruz Ferretera Abraham Ios Cruz Ferretera Papalo Ios Cruz Ferretera la Nueva Erick Guzmán Ferretera la Nueva Erick Guzmán Ferretera Papalo Erick Guzmán Ferretera Abraham 63

Sin embargo, si se agrega una cláusula WHERE, la combinación cruzada se comporta como una combinación interna. Por ejemplo, las siguientes consultas de Transact-SQL producen el mismo conjunto de resultados: SELECT a.NomCliente, p.NomNegocio FROM Cliente a CROSS JOIN Negocio p WHERE a.ciudad = p.ciudad and A.NegocioPk = p.NegocioPk ORDER BY a.NomCliente DESC -- o SELECT a.NomCliente, p.NomNegocio FROM Cliente a INNER JOIN Negocio p ON A.ciudad = p.ciudad and A.NegocioPk = p.NegocioPk ORDER BY a.NomCliente DESC

Combinar Tres Tablas o Más Si bien cada especificación de combinación sólo combina dos tablas, las cláusulas FROM pueden contener múltiples especificaciones de combinaciones. Esto permite que se combinen muchas tablas en una consulta única. La tabla DetalleFactura de la base de datos Facturacion ofrece un buen ejemplo de una situación en la que resulta útil combinar más de dos tablas. En la consulta siguiente de Transact-SQL se buscan los nombres de todos los productos que se vendieron en la Factura 100 del Negocio 1 USE Facturacion SELECT A.FolFactura, A.FecFactura, C.NomProducto, B.Cantidad, B.Importe FROM (Factura A Inner Join DetalleFactura B on (A.ConsecPk = B.ConsecPk)) Inner Join Producto C On (B.NegocioPk = C.NegocioPk And B.ProductoPk = C.ProductoPk) Where A.NegocioPk = 1 and A.FolFactura = 100 Order By A.FolFactura Éste es el conjunto de resultados: FolFactura FecFactura NomProducto Cantidad Importe --------------- --------------- --------------------------------- ------------------ -------------------100 2001-01-01 Escoba 2 13 100 2001-01-01 Mechudo 2 20 Cuando hay más de un operador de combinación en la misma instrucción, bien para combinar más de dos tablas o bien para combinar más de dos pares de columnas, las expresiones de combinación se pueden conectar con AND o con OR.

64

Consultas Avanzadas Conceptos Básicos de Subconsultas Una subconsulta es una consulta SELECT que devuelve un valor único y está anidada en una instrucción SELECT, INSERT, UPDATE o DELETE, o dentro de otra subconsulta. Una subconsulta se puede utilizar en cualquier parte en la que se permita una expresión. En este ejemplo se utiliza una subconsulta como una expresión de columna llamada MaxImporte en una instrucción SELECT. SELECT Factura.FolFactura, Factura.FecFactura, (SELECT MAX(A.Importe) FROM DetalleFactura A WHERE Factura.ConsecPk = A.ConsecPk) AS MaxImporte FROM Factura Se llama también subconsulta a una consulta o selección interna, mientras que la instrucción que contiene una subconsulta también es conocida como consulta o selección externa. Muchas de las instrucciones de Transact-SQL que incluyen subconsultas se pueden formular también como combinaciones. Otras preguntas se pueden formular sólo con subconsultas. En Transact-SQL, normalmente no hay diferencias de rendimiento entre una instrucción que incluya una subconsulta y una versión semánticamente equivalente que no lo haga. Sin embargo, en algunos casos en los que se debe comprobar la existencia de algo, una combinación produce mejores resultados. De lo contrario, la consulta anidada debe ser procesada para cada resultado de la consulta externa con el fin de asegurar la eliminación de los duplicados. En tales casos, la utilización de combinaciones produciría los mejores resultados. Esto es un ejemplo que muestra una subconsulta SELECT y una combinación SELECT que devuelve el mismo conjunto de resultados: SELECT Producto.NomProducto FROM Producto WHERE Producto.ProductoPk = (SELECT Distinct A.ProductoPk FROM DetalleFactura A Where Producto.NegocioPk = A.NegocioPk and Producto.ProductoPk = A.ProductoPk )

Select NomProducto From Producto A Inner Join DetalleFactura B On A.NegocioPk = B.NegocioPk And A.ProductoPk = B.ProductoPk

65

Una subconsulta anidada en la instrucción externa SELECT tiene los siguientes componentes: • Una consulta SELECT normal, que incluye los componentes normales de la lista de selección. • Una cláusula normal FROM que incluye uno o más nombres de tablas o vistas. • Una cláusula opcional WHERE. • Una cláusula opcional GROUP BY. • Una cláusula opcional HAVING. La consulta SELECT de una subconsulta se incluye siempre entre paréntesis. Una subconsulta puede anidarse dentro de la cláusula WHERE o HAVING de una instrucción externa SELECT, INSERT, UPDATE o DELETE, o dentro de otra subconsulta. Se puede disponer de hasta 32 niveles de anidamiento, aunque el límite varía dependiendo de la memoria disponible y de la complejidad del resto de las expresiones de la consulta. Las consultas individuales puede que no admitan anidamientos por encima de 32 niveles. Una subconsulta puede aparecer en cualquier parte en la que se pueda usar una expresión, si devuelve un valor individual. Si una tabla aparece sólo en una subconsulta y no en la consulta externa, las columnas de esa tabla no se pueden incluir en el resultado (la lista de selección de la consulta externa). Las instrucciones que incluyen una subconsulta normalmente tienen uno de estos formatos: • WHERE expresión [NOT] IN (subconsulta) • WHERE expresión operadorComparación [ANY | ALL] (subconsulta) • WHERE [NOT] EXISTS (subconsulta) En algunas instrucciones de Transact-SQL, la subconsulta se puede evaluar como si fuera una consulta independiente. Conceptualmente, los resultados de la subconsulta se sustituyen en la consulta externa (aunque, en realidad, ésta no es la forma en que Microsoft SQL Server procesa las instrucciones de Transact-SQL con subconsultas). Hay tres tipos básicos de subconsultas, que son las que: • Operan en listas, introducidas con IN o aquellas que modificó un operador de comparación mediante ANY o ALL. • Se introducen con un operador de comparación sin modificar y deben devolver un valor individual. • Son pruebas de existencia que se introducen con EXISTS.

66

Subconsultas con IN El resultado de una subconsulta presentada con IN (o con NOT IN) es una lista de cero o más valores. Una vez que la consulta devuelve los resultados, la consulta externa hace uso de ellos. En esta consulta se buscan los nombres de los Clientes que tienen alguna factura: SELECT A.NomCliente FROM Cliente A WHERE ClientePk IN (SELECT Distinct B.ClientePk FROM Factura B WHERE A.NegocioPk = B.NegocioPk) Este es el conjunto de resultados: NomCliente -----------------------------------------------------------Ivan Cruz Guzmán Erick Guzmán Esta instrucción se evalúa en dos pasos. Primero, la consulta interna devuelve los números de identificación de los clientes en el que el negocio donde efectuó la compra coincida. Segundo, estos valores se sustituyen en la consulta externa, que busca los nombres de los clientes asociados con los números de identificación en la tabla de Clientes.

Subconsultas con NOT IN Una subconsulta utilizando NOT IN regresa todas las ocurrencias que no se correspondan con el resultado de la subConsulta: Para este ejemplo serian todos los clientes que no tienen ninguna factura o remisión. Este caso se da cuando se presenta el proceso de depuración de las tablas de factura de detallefactura: SELECT A.NomCliente FROM Cliente A WHERE ClientePk NOT IN (SELECT Distinct B.ClientePk FROM Factura B WHERE A.NegocioPk = B.NegocioPk) Este es el conjunto de resultados: NomCliente -----------------------------------------------------------Luis Eduardo Medina Ios Cruz

67

Subconsultas en las instrucciones UPDATE, DELETE e INSERT Las subconsultas se pueden anidar en las instrucciones UPDATE, DELETE e INSERT, así como en las instrucciones SELECT. La siguiente consulta dobla el PrecioVenta de todos los Productos del Negocio Ferretera Papalo. La consulta actualiza la tabla Producto; su subconsulta hace referencia a la tabla Negocio. UPDATE Producto SET PrecioVenta = PrecioVenta * 2 WHERE ProductoPk IN (SELECT ProductoPk FROM Negocio WHERE NomNegocio = 'Ferretera Papalo' AND Negocio.NegocioPk = Producto.NegocioPk) Puede quitar todos los registros de ventas del 05/Abril/2001 con esta consulta anidada: DELETE DetalleFactura WHERE ConsecPk IN (SELECT ConsecPk FROM Factura WHERE FecFactura = '2001/04/05') Esta es una instrucción DELETE equivalente que usa una combinación: DELETE DetalleFactura FROM DetalleFactura INNER JOIN Factura ON DetalleFactura.ConsecPk = Factura.ConsecPk AND Factura.FecFactura = '2001/02/02'

Para crear una tabla alterna con el resultado de una consulta: 1er Paso. Crear la Tabla Vacía SELECT A.NomNegocio, b.NomProducto INTO TablaAux FROM Negocio A INNER JOIN Producto B ON A.NegocioPk = B.NegocioPk AND B.ProductoPk = NULL

2o Paso. Llenar la Tabla Insert Into TablaAux SELECT A.NomNegocio, b.NomProducto FROM Negocio A INNER JOIN Producto B ON A.NegocioPk = B.NegocioPk

68

Se preguntarán el por qué en dos pasos y no en uno solo, la causa es que si se efectúa en un solo paso ocurre un bloqueo en tablas del TempDb de SQL Server y genera que ninguna otra base de datos pueda trabajar de manera concurrente.

Subconsultas con Operadores de Comparación Las subconsultas se pueden presentar con uno de los operadores de comparación (=, < >, >, > =, , ! < o < =). Una subconsulta presentada con un operador de comparación sin modificar (un operador de comparación no seguido de ANY o ALL) debe devolver un valor individual en lugar de una lista de valores, como las subconsultas presentadas con IN. Si tal subconsulta devuelve más de un valor, Microsoft SQL Server muestra un mensaje de error. Para usar una subconsulta presentada con un operador de comparación sin modificar, debe estar suficientemente familiarizado con los datos y con la naturaleza del problema para saber que la subconsulta devolverá exactamente un valor. Por ejemplo, quiere determinar qué producto se facturó a un precio de venta más alto que el especificado en la tabla de producto: SELECT ProductoPk,ConsecPk, Cantidad, Importe FROM DetalleFactura WHERE Importe/Cantidad > (SELECT PrecioVenta FROM Producto WHERE DetalleFactura.NegocioPk = Producto.NegocioPk AND DetalleFactura.ProductoPk = Producto.ProductoPk) Este es el conjunto de resultados: ProductoPk ConsecPk Cantidad ---------------------------------------3 2 10

Importe -------------------252

Subconsultas con EXISTS Cuando se presenta una subconsulta con la palabra clave EXISTS, funciona como una prueba de existencia. La cláusula WHERE de la consulta externa comprueba la existencia de las filas devueltas por la subconsulta. La subconsulta en realidad no produce ningún dato, devuelve el valor TRUE o FALSE. Una subconsulta presentada con EXISTS tiene la siguiente sintaxis: WHERE [NOT] EXISTS (subconsulta)

69

En esta consulta se busca el nombre de los Clientes a quienes se les facturó. SELECT NomCliente FROM Cliente WHERE EXISTS (SELECT * FROM Factura WHERE Factura.ClientePk = Cliente.ClientePk AND Factura.NegocioPk = Cliente.NegocioPk AND Factura.FolFactura 0) Este es el conjunto de resultados: NomCliente -----------------------------------------------------------Ivan Cruz Guzmán Erick Guzmán

Subconsultas con NOT EXISTS NOT EXISTS funciona igual que EXISTS, salvo por que la cláusula WHERE en la que se utiliza se cumple si la subconsulta no devuelve ninguna fila. En el ejemplo siguiente se buscan nombres de Clientes que no tengan factura. SELECT NomCliente FROM Cliente WHERE NOT EXISTS (SELECT * FROM Factura WHERE Factura.ClientePk = Cliente.ClientePk AND Factura.NegocioPk = Cliente.NegocioPk AND Factura.FolFactura 0) Este es el conjunto de resultados: NomCliente -----------------------------------------------------------Luis Eduardo Medina Ios Cruz

70

Sumando Datos Usar funciones de agregado en la lista de selección Las funciones de agregado (SUM, AVG, COUNT, COUNT(*), MAX y MIN) generan valores de resumen en los conjuntos de resultados de las consultas. Una función de agregado (con la excepción de COUNT(*)) procesa todos los valores seleccionados en una única columna para generar un único resultado. Las funciones de agregado se pueden aplicar a todas las filas de una tabla, a un subconjunto de la tabla especificado por una cláusula WHERE o a uno o varios grupos de filas de la tabla. Cuando se aplica una función de agregado, se genera un valor individual por cada conjunto de filas. En este ejemplo se calcula la suma el valor de las existencias de todos los Productos de un negocio en particular. SELECT SUM(Existencia * Costo) FROM Producto WHERE NegocioPk = 1 ----------------------------------------------------4071.0000 Con esta consulta, puede averiguar el precio promedio de todos los productos si se doblaran los precios. SELECT avg(PrecioVenta * 2) ‘Precio de Promedio’ FROM Producto WHERE NegocioPk = 1 Éste es el conjunto de resultados: ‘Precio de Venta’ ----------------------------------------------------32.750000 En la tabla se muestra la sintaxis de las funciones de agregado y sus resultados (expresión es casi siempre el nombre de una columna). Funciones de agregado SUM([ALL | DISTINCT] expresión) AVG([ALL | DISTINCT] expresión) COUNT([ALL | DISTINCT] expresión) COUNT(*) MAX(expresión) MIN(expresión)

Resultado Total de los valores de la expresión numérica. Promedio de los valores de la expresión numérica. Número de valores en la expresión. Número de filas seleccionadas. Valor mayor de la expresión. Valor menor de la expresión.

SUM, AVG, COUNT, MAX y MIN pasan por alto los valores NULL; COUNT(*) no lo hace.

71

La palabra clave opcional DISTINCT se puede usar con SUM, AVG y COUNT para eliminar los valores duplicados antes de que se establezca la función de agregado (el valor predeterminado es ALL). SUM y AVG se pueden usar sólo con columnas numéricas, como, por ejemplo, las de los tipos de datos int, smallint, tinyint, decimal, numeric, float, real, money y smallmoney. MIN y MAX no se pueden usar con tipos de datos bit. El resto de las funciones de agregado que no sean COUNT(*) no se puede usar con los tipos de datos text e image. Con estas excepciones, las funciones de agregado se pueden usar con cualquier tipo de columna. Por ejemplo, en una columna del tipo de datos character, use MIN (mínimo) para buscar el valor inferior, el más cercano al principio del alfabeto: SELECT MIN(NomProducto) FROM Producto Éste es el conjunto de resultados: -------------------Cable No 12 El tipo de resultado que devuelve una función de agregado puede tener una precisión mayor que las entradas, siempre que sea suficientemente grande para contener el valor del resultado de agregado. Por ejemplo, las funciones SUM o AVG devuelven un valor int cuando el tipo de datos de entrada es smallint o tinyint. IMPORTANTE. Cuando en una lista de selección se usan funciones de agregado, la lista de selección sólo puede contener: • Funciones de agregado • Columnas de agrupamiento de una cláusula GROUP BY • Una expresión que devuelve el mismo valor para cada fila del conjunto de resultados, como, por ejemplo, una constante Las funciones de agregado no se pueden usar en una cláusula WHERE. Sin embargo, una instrucción SELECT con funciones de agregado en su lista de selección incluye, a menudo, una cláusula WHERE que restringe las filas a las que se aplica la función de agregado. Si una instrucción SELECT incluye una cláusula WHERE (aunque no una cláusula GROUP BY), una función de agregado produce un valor individual para el subconjunto de filas especificado por la cláusula WHERE. Esto es cierto tanto si está operando en todas las filas de una tabla o sólo en un subconjunto de filas definido por una cláusula WHERE.

72

Agrupar filas con GROUP BY Cuando se usan sin una cláusula GROUP BY, las funciones de agregado sólo devuelven un valor de agregado para la instrucción SELECT. La cláusula GROUP BY se usa para producir valores de agregado para cada fila del conjunto de resultados. En este ejemplo se devuelve el costo de las existencias por negocio: SELECT NomNegocio as ‘Nombre del Negocio’, SUM(Costo * Existencia) AS ‘Valor de las Existencias’ FROM Negocio INNER JOIN Producto ON Negocio.NegocioPk = Producto.NegocioPk AND Negocio.NegocioPk = 1 GROUP BY NomNegocio Este es el conjunto de resultados: Nombre del Negocio Valor de las Exsitencias ------------------------------------------------------------ ---------------------------------------Ferretera Abraham 4071.0000 A continuación de las palabras clave GROUP BY se coloca una lista de columnas, conocidas como las columnas de agrupamiento. La cláusula GROUP BY restringe las filas del conjunto de resultados y sólo hay una fila por cada valor distinto de las columnas o columna de agrupamiento. Cada fila del conjunto de resultados contiene los datos de resumen relacionados con el valor específico de sus columnas de agrupamiento. Hay restricciones en los elementos que se pueden especificar en la lista de selección cuando una instrucción SELECT contiene una cláusula GROUP BY. Los únicos elementos permitidos en la lista de selección son: • •

Las columnas de agrupamiento. Expresiones que devuelven un solo valor por cada valor de las columnas de agrupamiento, como por ejemplo, las funciones de agregado que tienen un nombre de columna como uno de sus parámetros. Se conocen como agregados vectoriales.

73

Por ejemplo, TableX contiene: ColumnA 1 1 1 2 2

ColumnB Abc Def Ghi Jkl Mno

ColumnC 5 4 9 8 3

Si ColumnA se usa como la columna de agrupamiento, habrá dos filas en el conjunto de resultados, una que resuma la información del valor 1 y la otra que resuma la información del valor 2. Cuando la ColumnA es la columna de agrupamiento, la única forma en la que se podría hacer referencia a la ColumnB o ColumnC es en el caso de que fueran parámetros de una función de agregado que pudiera devolver un valor único por cada valor de ColumnA. La lista de selección puede incluir expresiones como, por ejemplo, MAX(ColumnB), SUM(ColumnC) o AVG(ColumnC): SELECT ColumnA, MAX(ColumnB) AS MaxB, SUM(ColumnC) AS SumC FROM TableX GROUP BY ColumnA Esta selección devuelve dos filas, una por cada valor exclusivo de ColumnA: ColumnA MaxB SumC -------------- ----------1 ghi 18 2 mno 11 (2 row(s) affected) Sin embargo, no se permite tener sólo la expresión ColumnB en la lista de selección: SELECT ColumnA, ColumnB, SUM(ColumnC) AS SumC FROM TableX GROUP BY ColumnA Puesto que GROUP BY sólo puede devolver una fila con el valor 1 en ColumnA, no hay forma de devolver los tres valores de ColumnB (abc, def y ghi) asociados con el valor 1 de ColumnA.

74

Nota: No es posible utilizar GROUP BY o HAVING en columnas ntext, text, image o bit a menos que se encuentren en una función que devuelva un valor que tiene otro tipo de datos, como SUBSTRING o CAST. Utilizar DISTINCT con funciones de agregado La palabra clave DISTINCT es opcional con SUM, AVG y COUNT. Cuando se usa DISTINCT, los valores duplicados se eliminan antes de que se calcule la suma, promedio o recuento. Si usa DISTINCT, la expresión sólo debe constar del nombre de una columna. No puede incluir una expresión aritmética. En la consulta siguiente se devuelven los precios promedio de los libros del negocio especificado (sin los valores duplicados). SELECT AVG(DISTINCT PrecioVenta) FROM Producto WHERE NegocioPk = 10 ----------------------------------------------------150.25 Sin DISTINCT, la función AVG busca el precio promedio de todos los títulos del negocio especificado. SELECT AVG(PrecioVenta) FROM Producto WHERE NegocioPk = 10 Este es el conjunto de resultados: ------------------------------------------100.33333333333333 Nota: Los valores NULL de una columna se pasan por alto mientras opera una función de agregado.

Utilizar COUNT(*) COUNT(*) no requiere un parámetro expresión porque, por definición, no usa información de ninguna columna en particular. Cuenta el número total de filas que cumplen las calificaciones de la consulta. COUNT(*) devuelve el número de filas que coinciden con las condiciones de búsqueda especificadas en la consulta, sin eliminar los duplicados. Cuenta cada fila por separado, incluidas las filas que contienen valores NULL.

75

En esta consulta se busca el número total de productos de la tabla Producto: SELECT COUNT(*) FROM Producto Este es el conjunto de resultados: ----------5 (1 row(s) affected) COUNT(*) se puede combinar con otras funciones de agregado. Esta consulta muestra COUNT(*) combinado con una función AVG en la que ambas funciones de agregado sólo agregan los datos de las filas que cumplen la condición de búsqueda de la cláusula WHERE. SELECT COUNT(*), AVG(PrecioVenta) FROM Producto WHERE PrecioVenta > 100 Este es el conjunto de resultados: --------------------------------------------------------------3 350.0 (1 row(s) affected)

Elegir filas con la cláusula HAVING La cláusula HAVING establece las condiciones de la cláusula GROUP BY de la misma forma que WHERE interactúa con SELECT. Mientras que las condiciones de búsqueda de WHERE se aplican antes de que se produzca la operación de agrupamiento, las condiciones de búsqueda de HAVING se aplican después. La sintaxis de la cláusula HAVING es exactamente igual que la sintaxis de la cláusula WHERE, salvo que en HAVING puede contener funciones de agregado. Las cláusulas HAVING pueden hacer referencia a cualquiera de los elementos que aparecen en la lista de selección. En esta consulta se buscan los productos cuyas ventas sean superiores a $200 pesos: SELECT ProductoPk, SUM(Importe) Total FROM DetalleFactura GROUP BY ProductoPk HAVING SUM(Importe) > 200

76

Este es el conjunto de resultados: ProductoPk Total ---------------- ----------------------------------------------------2 300.0 (1 row(s) affected) •

• •

La comprensión de la secuencia correcta en la que se aplican las cláusulas WHERE, GROUP BY y HAVING ayuda a codificar consultas que sean eficientes: La cláusula WHERE se utiliza para filtrar las filas que resultan de las operaciones especificadas en la cláusula FROM. La cláusula GROUP BY se usa para agrupar el resultado de la cláusula WHERE. La cláusula HAVING se usa para filtrar las filas del resultado agrupado.

Es más eficiente especificar en la cláusula WHERE las condiciones de búsqueda que se pueden establecer antes o después de la operación de agrupamiento. Esto reduce el número de filas que tienen que agruparse. Las únicas condiciones de búsqueda que se deben especificar en la cláusula HAVING son aquellas que se deben aplicar una vez que se hayan realizado las operaciones de agrupamiento. A continuación se muestra un ejemplo de una cláusula HAVING sin funciones de agregado. Agrupa por Unidad de Medida las filas de la tabla Producto y elimina los tipos que no empiezan con la letra "p". SELECT UnidadMedida FROM Producto GROUP BY UnidadMedida HAVING UnidadMedida LIKE 'p%' Este es el conjunto de resultados: UnidadMedida -------------------Pieza Cuando en HAVING se incluyen varias condiciones, se combinan mediante AND, OR o NOT.

77

Resumir Datos con COMPUTE y COMPUTE BY Genera totales que aparecen como columnas de resumen adicionales al final del conjunto de resultados. Cuando se utiliza con BY, la cláusula COMPUTE genera interrupciones de control y subtotales en el conjunto de resultados. Puede especificar COMPUTE BY y COMPUTE en la misma consulta. Los valores de resumen generados por COMPUTE aparecen como conjuntos separados de resultados en los resultados de la consulta. Los resultados de una consulta que incluya una cláusula COMPUTE son como un informe de ruptura de control, que es un informe cuyos valores de resumen se controlan mediante las agrupaciones, o rupturas, que se especifique. Puede producir valores de resumen para grupos, y puede calcular más de una función de agregado para el mismo grupo. Cuando se especifica COMPUTE con la cláusula opcional BY, hay dos conjuntos de resultados por cada grupo que califica la instrucción SELECT: •

El primer conjunto de resultados de cada grupo tiene el conjunto de filas de detalles que contienen la información de la lista de selección para ese grupo.

•

El segundo conjunto de resultados de cada grupo tiene una fila que contiene los subtotales de las funciones de agregado especificadas en la cláusula COMPUTE para ese grupo.

Cuando se especifica COMPUTE sin la cláusula opcional BY, hay dos conjuntos de resultados para la instrucción SELECT: •

El primer conjunto de resultados de cada grupo tiene todas las filas de detalles que contienen información de la lista de selección.

•

El segundo conjunto de resultados tiene una fila que contiene los totales de las funciones de agregado especificadas en la cláusula COMPUTE.

Sintaxis [ COMPUTE { { AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM } ( expression ) } [ ,...n ] [ BY expression [ ,...n ] ] ]

78

Argumentos AVG | COUNT | MAX | MIN | STDEV | STDEVP | VAR | VARP | SUM Especifica la función de agregado que se va a realizar. Con la cláusula COMPUTE se utilizan estas funciones de agregado de filas. Función de agregado de Resultado filas AVG

Promedio de los valores de la expresión numérica.

COUNT

Número de filas seleccionadas.

MAX

Valor mayor de la expresión.

MIN

Valor menor de la expresión.

STDEV

Desviación típica estadística de todos los valores de la expresión.

STDEVP

Desviación típica estadística del llenado para todos los valores de la expresión.

SUM

Total de los valores de la expresión numérica.

VAR

Varianza estadística de todos los valores de la expresión.

VARP

Varianza estadística del llenado para todos los valores de la expresión.

No existe ninguna función equivalente a COUNT(*). Para localizar la información de resumen que producen GROUP BY y COUNT(*), utilice una cláusula COMPUTE sin BY. Estas funciones omiten los valores NULL. No se permite utilizar la palabra clave DISTINCT en funciones de agregado de filas cuando se especifican con la cláusula COMPUTE. ( expression ) Una expresión, por ejemplo, el nombre de una columna en la que se realiza el cálculo. El argumento expression debe aparecer en la lista de selección y se debe especificar exactamente igual que una de las expresiones de la lista de selección. En expression no se puede utilizar un alias de columna especificado en la lista de selección. Nota No se puede especificar los tipos de datos ntext, text o image en una cláusula COMPUTE o en una cláusula COMPUTE BY.

79

BY expression Genera interrupciones de control y subtotales en el conjunto de resultados. expression es una copia exacta de una order_by_expression de la cláusula ORDER BY asociada. Normalmente se trata de un nombre de columna o de un alias de columna. Se pueden especificar varias expresiones. Si se enumeran varias expresiones después de BY, se divide un grupo en subgrupos y se aplica la función de agregado en cada nivel del agrupamiento. Si utiliza COMPUTE BY, también debe utilizar una cláusula ORDER BY. Las expresiones deben ser idénticas a las enumeradas después de ORDER BY o un subconjunto de las mismas, y deben estar en la misma secuencia. Ejemplo: La instrucción SELECT siguiente usa una cláusula COMPUTE simple para producir el total de la suma del PrecioVenta y las Existencias de la tabla de Productos: SELECT FamiliaPk, PrecioVenta, Existencia FROM Producto ORDER BY FamiliaPk COMPUTE SUM(PrecioVenta), SUM(Existencia) En esta consulta se agrega la palabra clave opcional BY a la cláusula COMPUTE para producir subtotales para cada grupo:

SELECT FamiliaPk, PrecioVenta, Existencia FROM Producto ORDER BY FamiliaPk COMPUTE SUM(PrecioVenta), SUM(Existencia) BY FamiliaPk

80

Recomendaciones para Optimizar Consultas Algunas consultas utilizan intrínsecamente recursos de forma intensiva. Esto está relacionado con aspectos fundamentales de bases de datos e índices. Estas consultas no son ineficaces, debido a que el optimizador implementará las consultas de la forma más eficaz posible. Sin embargo, utilizan recursos de forma intensiva y la naturaleza orientada a conjuntos de Transact-SQL puede hacer que parezcan ineficaces. Ningún grado de inteligencia del optimizador de consultas puede eliminar el costo intrínseco de recursos de estas construcciones. Son costosas en comparación con una consulta menos compleja. Aunque Microsoft SQL Server utilizará un plan de acceso óptimo, está limitado por lo que es fundamentalmente posible. Por ejemplo, los siguientes tipos de consultas pueden utilizar recursos de forma intensiva: • Consultas que devuelven conjuntos de resultados de gran tamaño. • Cláusulas WHERE no únicas Sin embargo, las recomendaciones para optimizar consultas y mejorar su rendimiento incluyen: • Agregar más memoria (especialmente si el servidor ejecuta muchas consultas complejas y varias consultas se ejecutan lentamente). • Ejecutar SQL Server en un equipo con más de un procesador. Varios procesadores permiten que SQL Server utilice consultas en paralelo. • Considere la posibilidad de volver a escribir la consulta. o Si la consulta utiliza cursores, determine si se puede escribir la consulta de cursor de forma más eficaz con un tipo de cursor más eficaz, como un cursor de desplazamiento rápido hacia delante, o una única consulta. Las consultas únicas normalmente mejoran las operaciones de cursor. Debido a que un conjunto de instrucciones de cursor suele constituir una operación de ciclo externo, en la que cada fila del ciclo externo se procesa una vez con una instrucción interna, considere la posibilidad de utilizar en su lugar una instrucción GROUP BY o CASE, o una subconsulta. o Si una aplicación utiliza un ciclo, considere la posibilidad de colocar el ciclo en la consulta. A menudo, una aplicación contendrá un ciclo que, a su vez, contiene una consulta parametrizada que se ejecuta muchas veces y será necesario realizar un viaje de ida y vuelta en la red entre el equipo que ejecuta la aplicación y SQL Server. En su lugar, cree una sola consulta más compleja con una tabla temporal. Sólo necesita un viaje de ida y vuelta en la red, y el optimizador de consultas puede optimizar mejor la consulta única. o No utilice varios alias para una sola tabla en la misma consulta para simular la intersección de índices. Ya no es necesario debido a que SQL Server tiene en cuenta automáticamente la intersección de índices y puede utilizar varios índices en la misma tabla de la misma consulta. Por ejemplo, dada la consulta:

81

SELECT * FROM Factura WHERE ClientePk BETWEEN 1 AND 250 AND FecFactura BETWEEN '2001/01/01' AND '2001/01/02' SQL Server puede utilizar índices sobre las columnas ClientePk y FecFactura, y después, realizar una coincidencia hash entre los dos subconjuntos para obtener la intersección de índices.

82

Módulo 7: Manejando Transacciones y Bloqueos Transacciones Una transacción es una secuencia de operaciones realizadas como una sola unidad lógica de trabajo. Una unidad lógica de trabajo debe exhibir cuatro propiedades, conocidas como propiedades ACID (atomicidad, coherencia, aislamiento y durabilidad), para ser calificada como transacción: Atomicidad Una transacción debe ser una unidad atómica de trabajo, tanto si se realizan todas sus modificaciones en los datos, como si no se realiza ninguna de ellas. Coherencia Cuando finaliza, una transacción debe dejar todos los datos en un estado coherente. En una base de datos relacional, se deben aplicar todas las reglas a las modificaciones de la transacción para mantener la integridad de todos los datos. Todas las estructuras internas de datos, como índices de árbol B o listas doblemente vinculadas, deben estar correctas al final de la transacción. Aislamiento Las modificaciones realizadas por transacciones simultáneas se deben aislar de las modificaciones llevadas a cabo por otras transacciones simultáneas. Una transacción ve los datos en el estado en que estaban antes de que otra transacción simultánea los modificara o después de que la segunda transacción se haya concluido, pero no ve un estado intermedio. Esto se conoce como seriabilidad debido a que su resultado es la capacidad de volver a cargar los datos iniciales y reproducir una serie de transacciones para finalizar con los datos en el mismo estado en que estaban después de realizar las transacciones originales. Durabilidad Una vez concluida una transacción, sus efectos son permanentes en el sistema. Las modificaciones persisten aún en el caso de producirse un error del sistema.

Especificar y exigir transacciones Los programadores de SQL son los responsables de iniciar y finalizar las transacciones en puntos que exijan la coherencia lógica de los datos. El programador debe definir la secuencia de modificaciones de datos que los dejan en un estado coherente en relación con las reglas corporativas de la organización. A continuación, el programador incluye estas instrucciones de modificación en una sola transacción de forma que Microsoft SQL Server puede exigir la integridad física de la misma. Es responsabilidad de un sistema de base de datos corporativo como SQL Server proporcionar los mecanismos que aseguren la integridad física de cada transacción. SQL Server proporciona: •

Servicios de bloqueo que preservan el aislamiento de la transacción.

83

•

Servicios de registro que aseguran la durabilidad de la transacción. Aún en el caso de que falle el hardware del servidor, el sistema operativo o el propio SQL Server, SQL Server utiliza registros de transacciones, al reinicio, para deshacer automáticamente las transacciones incompletas en el momento en que se produjo el error en el sistema.

•

Características de administración de transacciones que exigen la atomicidad y coherencia de la transacción. Una vez iniciada una transacción, debe concluirse correctamente o SQL Server deshará todas las modificaciones de datos realizadas desde que se inició la transacción.

Controlar Transacciones Una transacción es una unidad única de trabajo. Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o deshacerse, se borran todas las modificaciones de los datos. SQL Server funciona en tres modos de transacción: Transacciones de confirmación automática Cada instrucción individual es una transacción. Transacciones explícitas Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK. Transacciones implícitas Se inicia implícitamente una nueva transacción cuando se ha completado la anterior, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK

Finalizar transacciones Puede finalizar las transacciones con la instrucción COMMIT o la instrucción ROLLBACK. COMMIT Si una transacción es correcta, confírmela. La instrucción COMMIT garantiza que todas las modificaciones de la transacción se convierten en una parte permanente de la base de datos. La instrucción COMMIT también libera recursos que utiliza la transacción como, por ejemplo, los bloqueos. 84

ROLLBACK Si se produce un error en una transacción o el usuario decide cancelar la transacción, deshaga la transacción. La instrucción ROLLBACK deshace todas las modificaciones realizadas en la transacción al devolver los datos al estado en que estaban al inicio de la transacción. La instrucción ROLLBACK también libera los recursos que mantiene la transacción. Errores al procesar la transacción Si un error grave impide la terminación correcta de una transacción, SQL Server deshace automáticamente la transacción y libera todos los recursos que mantiene la transacción. Si se interrumpe la conexión de red del cliente con SQL Server, las transacciones pendientes de la conexión se deshacen cuando la red notifica a SQL Server la interrupción. Si la aplicación cliente falla o si el equipo cliente se bloquea o se reinicia, también se interrumpe la conexión y SQL Server deshace las conexiones pendientes cuando la red le notifica la interrupción. Si el cliente cierra la aplicación, se deshacen las transacciones pendientes. Es responsabilidad del programador codificar la aplicación para especificar la acción correcta (COMMIT o ROLLBACK) si se produce un error de compilación o en tiempo de ejecución.

85

Bloqueo SQL Server 2000 utiliza el bloqueo para asegurar la integridad de las transacciones y la coherencia de las bases de datos. El bloqueo impide que los usuarios lean los datos que otros están modificando y que varios usuarios modifiquen los mismos datos de forma simultánea. Si no se utilizan bloqueos, los datos de la base de datos pueden presentar errores lógicos y las consultas que en ellos se realicen pueden producir resultados inesperados. Aunque SQL Server impone el bloqueo de forma automática, se pueden diseñar aplicaciones más eficaces si se comprende y se personaliza en éstas el bloqueo.
Interbloqueos (DeadLock) Un interbloqueo se produce cuando hay una dependencia cíclica entre dos o más subprocesos para algún conjunto de resultados. Un interbloqueo es una circunstancia que se puede dar en cualquier sistema con varios subprocesos, no sólo en un sistema de administración de bases de datos relaciónales. Un subproceso de un sistema que contiene varios subprocesos puede adquirir uno o más recursos, por ejemplo, bloqueos. Si el recurso que se desea adquirir pertenece actualmente a otro subproceso, puede que el primer subproceso deba esperar a que el otro libere el recurso de destino. En consecuencia, se dice que el subproceso que está en espera depende del subproceso que posee ese recurso concreto. Si el subproceso "propietario" desea adquirir otro recurso que actualmente tiene ocupado el subproceso en espera, la situación que se genera es un interbloqueo: ninguno de los subprocesos puede liberar el recurso que tiene ocupado hasta que sus transacciones se confirmen o se anulen, y éstas no pueden confirmarse ni anularse porque están a la espera de recursos que el otro subproceso tiene ocupados. Por ejemplo, el subproceso T1 que ejecuta la transacción 1 tiene un bloqueo exclusivo en la tabla Proveedor. El subproceso T2 que ejecuta la transacción 2 obtiene un bloqueo exclusivo en la tabla Partes y, a continuación, desea un bloqueo en la tabla Proveedor. La transacción 2 no puede obtener el bloqueo debido a que lo tiene la transacción 1. La transacción 2 está bloqueada, esperando a la transacción 1. La transacción 1, a continuación, desea un bloqueo en la tabla Partes, pero no puede obtenerlo debido a que la transacción 2 la tiene bloqueada. Las transacciones no pueden liberar los bloqueos que mantienen hasta que se confirmen o se anulen. Las transacciones no se pueden confirmar ni cancelar porque para continuar necesitan un bloqueo que tiene la otra transacción. Nota A menudo se confunden los interbloqueos con los bloqueos normales. Cuando una transacción tiene un bloqueo en un recurso que desea otra transacción, la segunda transacción espera a que se libere el bloqueo. De forma predeterminada, las transacciones de SQL Server no tienen tiempo de espera (a menos que se establezca LOCK_TIMEOUT). La segunda transacción está bloqueada, no en situación de interbloqueo. Para minimizar los bloqueos se recomienda hacer uso de los índices CLUSTERED, para así obtener la característica de bloqueo por registro, aumentando con esto la concurrencia.

86

Módulo 8: Implementando Vistas Vistas de SQL Una vista se puede considerar una tabla virtual o una consulta almacenada. Los datos accesibles a través de una vista no están almacenados en un objeto distinto de la base de datos. Lo que está almacenado en la base de datos es una instrucción SELECT. El resultado de la instrucción SELECT forma la tabla virtual que la vista devuelve. El usuario puede utilizar dicha tabla virtual haciendo referencia al nombre de la vista en instrucciones Transact-SQL, de la misma forma en que se hace referencia a las tablas. Las vistas se utilizan para alguna de estas funciones, o para todas: •

Restringir el acceso del usuario a filas concretas de una tabla. Por ejemplo, permitir que un empleado sólo vea las filas que guardan su trabajo en una tabla de seguimiento de actividad laboral.

•

Restringir el acceso del usuario a columnas específicas. Por ejemplo, permitir que los empleados que no trabajen en el departamento de nóminas vean las columnas de nombre, oficina, teléfono y departamento de la tabla de empleados, pero no permitir que vean las columnas con los datos de salario u otra información personal.

•

Combinar columnas de varias tablas de forma que parezcan una sola tabla.

•

Agregar información en lugar de presentar los detalles. Por ejemplo, presentar la suma de una columna o el valor máximo o mínimo de una columna.

Las vistas se crean definiendo la instrucción SELECT que recupera los datos presentados por la vista. Las tablas de datos a las que hace referencia la instrucción SELECT se conocen como las tablas base para la vista. En este ejemplo, vwCliente de la base de datos Facturación es una vista que selecciona datos de dos tablas base para presentar una tabla virtual de datos frecuentemente utilizados: CREATE VIEW vwCliente AS SELECT A.ClientePk, A.NomCliente,B.FolioFactura, B.FechaFactura FROM Cliente AS A Inner JOIN Factura AS B ON (a.ClientePk = b.ClientePk) Una vez creada, se puede hacer referencia a vwCliente en las instrucciones, de la misma forma que se hace referencia a una tabla:

87

SELECT * FROM vwCliente Las vistas en todas las versiones de SQL Server son actualizables (pueden ser objetivo de instrucciones UPDATE, DELETE o INSERT) mientras la modificación afecte sólo a una de las tablas base de la vista, por ejemplo: -- Incrementar el precio de los productos un 10%. UPDATE vwCliente SET NomCliente = ‘Ma. Teresa Cardenas Moreno’ WHERE ClientePk = 2 AND NegocioPk = 1 GO SQL Server 2000 admite tipos más complejos de instrucciones INSERT, UPDATE y DELETE que hagan referencia a vistas. Las vistas indexadas son una característica de SQL Server 2000 que mejora considerablemente el rendimiento de vistas complejas del tipo que se encuentra normalmente en almacenes de datos u otros sistemas de ayuda a la toma de decisiones. Las vistas se denominan tablas virtuales porque el conjunto de resultados de una vista normalmente no se guarda en la base de datos. El conjunto de resultados de una vista se incorpora dinámicamente a la lógica de la instrucción y el conjunto de resultados se genera dinámicamente en tiempo de ejecución. Las consultas complejas, como las de los sistemas de ayuda a la toma de decisiones, pueden hacer referencia a un gran número de filas de tablas base y agregar mucha información en agregados relativamente concisos como sumas o promedios. SQL Server 2000 admite la creación de un índice agrupado en una vista que implemente una consulta tan compleja. Cuando se ejecuta la instrucción CREATE INDEX, el conjunto de resultados de la vista SELECT se almacena en la base de datos de forma permanente. Las futuras instrucciones SQL que hagan referencia a la vista tendrán unos tiempos de respuesta sustancialmente mejores. En la vista se reflejan automáticamente las modificaciones a los datos base. Crear un índice de una vista Este ejemplo creará una vista y un índice de esa vista. A continuación, se incluyen dos consultas que utilizan la vista indexada.

88

--Activar las siguientes opciones para soportar índices en las vistas SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABO RT,QUOTED_IDENTIFIER,ANSI_NULLS ON GO --Crea la Vista CREATE VIEW vwProductos WITH SCHEMABINDING AS SELECT B.ProductoPk, A.ConsecPK, B.Existencia,count_big(*) as Cuenta FROM dbo.DetalleFactura A, dbo.Producto B WHERE A.ProductoPk = B.ProductoPk And A.NegocioPk = B.NegocioPk GROUP BY B.Productopk, A.ConsecPK, B.Existencia GO

--Crea índice sobre una vista CREATE UNIQUE CLUSTERED INDEX PkProducto3 ON vwProductos (Productopk, ConsecPK) GO La instrucción CREATE VIEW de SQL Server 2000 admite una opción SCHEMABINDING que evita que se modifiquen las tablas a las que hace referencia la vista, sin necesidad de ajustar la vista. Debe especificar SCHEMABINDING para cualquier vista en la que cree un índice. SCHEMABINDING Enlaza la vista al esquema. Cuando se especifica SCHEMABINDING, select_statement debe incluir los nombres con dos partes (propietario.objeto) de las tablas, vistas o funciones definidas por el usuario a las que se hace referencia. Las vistas o las tablas que participan en una vista creada con la cláusula de enlace de esquema no se pueden quitar ni alterar, de forma que deja de tener un enlace de esquema. De lo contrario, SQL Server genera un error. Además, las instrucciones ALTER TABLE sobre tablas que participan en vistas que tienen enlaces de esquemas provocarán un error si estas instrucciones afectan a la definición de la vista. Las funciones de agregado AVG, MAX, MIN, STDEV, STDEVP, VAR o VARP. Si se especifican AVG, MAX, MIN, STDEV, STDEVP, VAR o VARP en consultas que hacen referencia a la vista indexada, el optimizador puede calcular el resultado 89

necesario si la lista de selección de la vista contiene estas funciones de sustitución: Función de agregado compleja

Funciones de agregado sencillas de sustitución

AVG(X)

SUM(X), COUNT_BIG(X)

STDEV(X)

SUM(X), COUNT_BIG(X), SUM(X**2)

STDEVP(X)

SUM(X), COUNT_BIG(X), SUM(X**2)

VAR(X)

SUM(X), COUNT_BIG(X), SUM(X**2)

VARP(X)

SUM(X), COUNT_BIG(X), SUM(X**2)

Por ejemplo, una lista de selección de vista indexada no puede contener la expresión AVG(SomeColumn). Si la lista de selección de la vista contiene las expresiones SUM(SomeColumn) y COUNT_BIG(SomeColumn), SQL Server puede calcular el promedio de una consulta que hace referencia a la vista y especifica AVG(SomeColumn).

Borrando una Vista Quita una o más vistas de la base de datos actual. Si se elimina una tabla que tiene vistas, éstas las deberá eliminar con DROP VIEW. Sintaxis DROP VIEW vista [,…n] IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vwProductos’) DROP VIEW vwProductos GO

90

Módulo 9: Implementando Procedimientos Almacenados Procedimientos Almacenados Los procedimientos almacenados pueden facilitar en gran medida la administración de la base de datos y la visualización de información sobre dicha base de datos y sus usuarios. Los procedimientos almacenados son una colección precompilada de instrucciones SQL e instrucciones de control de flujo opcionales almacenadas bajo un solo nombre y procesadas como una unidad. Los procedimientos almacenados se guardan en una base de datos; se pueden ejecutar desde una aplicación y permiten variables declaradas por el usuario, ejecución condicional y otras funciones eficaces de programación. Los procedimientos almacenados pueden contener flujo de programas, lógica y consultas a la base de datos. Pueden aceptar parámetros, proporcionar resultados de parámetros, devolver conjuntos de resultados individuales o múltiples y devolver valores. Las ventajas de utilizar procedimientos almacenados en SQL Server en vez de programas Transact-SQL almacenados localmente en equipos clientes consisten en que: • Permiten una programación modular. Puede crear el procedimiento una vez, almacenarlo en la base de datos, y llamarlo desde el programa el número de veces que desee. Un especialista en programación de bases de datos puede crear procedimientos almacenados, que luego será posible modificar independientemente del código fuente del programa. Facilitan el mantenimiento. • Permiten una ejecución más rápida. En situaciones en las que se necesita una gran cantidad de código Transact-SQL, o si las operaciones se realizan varias veces, los procedimientos almacenados pueden ser más rápidos que los lotes de código Transact-SQL. Los procedimientos son analizados y optimizados en el momento de su creación, y es posible utilizar una versión del procedimiento que se encuentra en la memoria después de que se ejecute por primera vez. Las instrucciones de Transact-SQL que se envían varias veces desde el cliente cada vez que deben ejecutarse tienen que ser compiladas y optimizadas siempre que SQL Server las ejecuta. • Pueden reducir el tráfico de red. Una operación que necesite centenares de líneas de código Transact-SQL puede realizarse mediante una sola instrucción que ejecute el código en un procedimiento, en vez de enviar cientos de líneas de código por la red. •

Pueden utilizarse como mecanismo de seguridad.

91

Es posible conceder permisos a los usuarios para ejecutar un procedimiento almacenado, incluso si no cuentan con permiso para ejecutar directamente las instrucciones del procedimiento.

Ejemplo. El siguiente procedimiento almacenado regresa las ventas de un producto determinado y el número de ventas. CREATE PROCEDURE spProductoVentas @NegocioPk SmallInt, @ProductoPk SmallInt, @Ventas Float OUTPUT AS SELECT @Ventas = SUM(Importe) FROM DetalleFactura WHERE NegocioPk = @NegocioPk AND ProductoPk = @ProductoPk RETURN (Select count(*) from DetalleFactura WHERE NegocioPk = @NegocioPk AND ProductoPk = @ProductoPk) Para ejecutar este procedimiento almacenado: declare @VentaSal Float, @Cuenta Int EXEC @Cuenta = spProductoVentas 1,2, @Ventas = @VentaSal OUTPUT Select @Cuenta as Cuenta, @VentaSal as 'Ventas de Salida' Como se puede observar, este procedimiento almacenado contiene dos parámetros de entrada y uno de salida. Ejemplo. Especificando un valor por default. Este procedimiento incrementa el precio de los artículos en un determinado porcentaje. CREATE PROCEDURE spIncrementa @Porcentaje SmallInt = Null AS BEGIN TRANSACTION IF @Porcentaje IS NULL BEGIN PRINT ‘ERROR: Usted deberá especificar el valor del porcentaje.’ ROLLBACK TRANSACTION RETURN 1 END IF @Porcentaje >0 AND 0 Begin RAISERROR ('No Puede Borrar el Negocio por tener Clientes', 16, 1) RollBack Transaction End Los desencadenadores contienen instrucciones Transact-SQL, como los procedimientos almacenados. Los desencadenadores, como los procedimientos almacenados, devuelven el conjunto de resultados generado por las instrucciones SELECT del desencadenador. No se recomienda que incluya instrucciones SELECT en los desencadenadores, excepto en las instrucciones que sólo rellenen

95

parámetros. Esto se debe a que los usuarios no esperan que las instrucciones UPDATE, INSERT o DELETE devuelvan ningún conjunto de resultados. Puede utilizar la cláusula FOR para especificar cuándo se tiene que ejecutar un desencadenador: •

AFTER (después de) Los desencadenadores se ejecutan tras la finalización de la instrucción que los desencadena. Si la instrucción termina con un error, por ejemplo, la infracción de una restricción o un error de sintaxis, el desencadenador no se ejecuta. No es posible especificar desencadenadores AFTER para vistas, sólo se pueden especificar para tablas. Puede especificar varios desencadenadores AFTER para cada acción de desencadenamiento (INSERT, UPDATE o DELETE). Si tiene varios desencadenadores AFTER para una tabla, puede utilizar sp_settriggerorder para definir qué desencadenador AFTER se activa primero y cuál al final. El resto de los desencadenadores AFTER, excepto el primero y el último, se activan en un orden indefinido que no puede controlar. AFTER es el valor predeterminado en SQL Server 2000. No se puede especificar AFTER o INSTEAD OF en SQL Server versión 7.0 o anterior; todos los desencadenadores de esas versiones funcionan como desencadenadores AFTER.

•

INSTEAD OF (en lugar de) Se ejecuta el desencadenador en lugar de la acción desencadenadora. Es posible especificar desencadenadores INSTEAD OF en tablas y en vistas. Puede especificar sólo un desencadenador INSTEAD OF para cada acción desencadenadora (INSERT, UPDATE o DELETE). Es posible utilizar desencadenadores INSTEAD OF para realizar comprobaciones de integridad en los valores de datos especificados en instrucciones INSERT y UPDATE. Los desencadenadores INSTEAD OF también le permiten especificar acciones que posibilitan la actualización de las vistas, que normalmente no aceptan actualizaciones.

Exigir reglas de empresa con desencadenadores Los dos principales mecanismos que ofrece Microsoft SQL Server 2000 para exigir las reglas de empresa y la integridad de datos son: las restricciones y los desencadenadores. Un desencadenador es un procedimiento almacenado de tipo especial que actúa automáticamente cuando se modifican los datos de la tabla. Los desencadenadores se invocan en respuesta a las instrucciones INSERT, UPDATE y DELETE. Un desencadenador puede consultar otras tablas e incluir instrucciones de Transact-SQL complejas. El desencadenador y la instrucción que lo activa se tratan como una sola transacción que puede deshacerse desde el desencadenador. Si se detecta un error grave (por ejemplo, no hay suficiente espacio en disco), se deshace automáticamente toda la transacción. 96

Los desencadenadores tienen varias utilidades: •

Los desencadenadores pueden realizar cambios en cascada por medio de tablas relacionadas de la base de datos; sin embargo, estos cambios pueden ejecutarse de manera más eficaz mediante restricciones de integridad referencial en cascada.

•

Los desencadenadores pueden exigir restricciones más complejas que las restricciones CHECK. A diferencia de éstas, los desencadenadores pueden hacer referencia a columnas de otras tablas. Por ejemplo, un desencadenador puede utilizar una instrucción SELECT de otra tabla para comparar con los datos insertados o actualizados y para realizar acciones adicionales, como modificar los datos o mostrar un mensaje de error definido por el usuario.

•

Los desencadenadores también pueden evaluar el estado de una tabla antes y después de realizar una modificación de datos y actuar en función de la diferencia.

•

Varios desencadenadores del mismo tipo (INSERT, UPDATE o DELETE) en una tabla permiten realizar distintas acciones en respuesta a una misma instrucción de modificación.

Comparación de los desencadenadores y las restricciones Tanto las restricciones como los desencadenadores ofrecen ventajas específicas que resultan útiles en determinadas situaciones. La principal ventaja de los desencadenadores consiste en que pueden contener una lógica de proceso compleja que utilice código de Transact-SQL. Por tanto, los desencadenadores permiten toda la funcionalidad de las restricciones; sin embargo, no son siempre el mejor método para realizar una determinada función. La integridad de entidad debe ser siempre exigida en el nivel más bajo por índices que formen parte de restricciones PRIMARY KEY y UNIQUE, o que se creen independientemente de las restricciones. La integridad de dominio debe ser exigida mediante restricciones CHECK, y la integridad referencial (RI) mediante restricciones FOREIGN KEY, siempre que sus características satisfagan las necesidades funcionales de la aplicación. Los desencadenadores resultan de especial utilidad allí donde las características de las restricciones no cubren las necesidades funcionales de la aplicación. Por ejemplo: •

Las restricciones FOREIGN KEY pueden validar un valor de columna sólo si coinciden exactamente con un valor de otra columna, a menos que la cláusula REFERENCES defina una acción referencial en cascada.

97

•

Una restricción CHECK puede validar el valor de una columna únicamente con relación a una expresión lógica o a otra columna de la misma tabla. Si la aplicación necesita que se valide el valor de una columna con relación a una columna de otra tabla, deberá utilizar un desencadenador.

•

Las restricciones sólo pueden comunicar la existencia de errores mediante mensajes de error estándar del sistema. Si la aplicación necesita (o puede aprovechar) mensajes personalizados y un tratamiento de errores más complejo, deberá utilizar un desencadenador.

Los desencadenadores pueden realizar cambios en cascada por medio de tablas relacionadas de la base de datos; sin embargo, estos cambios pueden ejecutarse de manera más eficiente a través de restricciones de integridad referencial en cascada. •

Los desencadenadores pueden no permitir o deshacer los cambios que infrinjan la integridad referencial y cancelar, de ese modo, cualquier intento de modificación de los datos. Ese tipo de desencadenador puede actuar cuando se cambia una clave externa y el nuevo valor no coincide con su clave principal. Por ejemplo, puede crear un desencadenador de inserción en DetalleFactura.ProductoPk que deshaga la inserción si el nuevo valor no coincide con ningún valor de Producto.ProductoPk. (No obstante, para estos casos suelen utilizarse restricciones FOREIGN KEY.)

•

Si hay restricciones en la tabla del desencadenador, se comprobarán después de la ejecución del desencadenador INSTEAD OF pero antes de la ejecución del desencadenador AFTER. Si se infringen las restricciones, se desharán las acciones del desencadenador INSTEAD OF y no se ejecutará el desencadenador AFTER.

CREATE TRIGGER Crea un desencadenador, que es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando un usuario intenta la instrucción especificada de modificación de datos en la tabla indicada. Microsoft SQL Server permite crear varios desencadenadores para cualquier instrucción INSERT, UPDATE o DELETE. Sintaxis CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE] } AS instrucciónSQL [...n] 98

Argumentos trigger_name Es el nombre del desencadenador. Un nombre de desencadenador debe cumplir las reglas de los identificadores y debe ser único en la base de datos. Especificar el propietario del desencadenador es opcional. Table | view Es la tabla o vista en que se ejecuta el desencadenador; algunas veces se denomina tabla del desencadenador o vista del desencadenador. Especificar el nombre del propietario de la tabla o vista es opcional. WITH ENCRYPTION Codifica las entradas syscomments que contienen el texto de CREATE TRIGGER. El uso de WITH ENCRYPTION impide que el desencadenador se publique como parte de la duplicación de SQL Server. AFTER Especifica que el desencadenador sólo se activa cuando todas las operaciones especificadas en la instrucción SQL desencadenadora se han ejecutado correctamente. Además, todas las acciones referenciales en cascada y las comprobaciones de restricciones deben ser correctas para que este desencadenador se ejecute. AFTER es el valor predeterminado, si sólo se especifica la palabra clave FOR. Los desencadenadores AFTER no se pueden definir en las vistas. INSTEAD OF Especifica que se ejecuta el desencadenador en vez de la instrucción SQL desencadenadora, por lo que se suplantan las acciones de las instrucciones desencadenadoras. Como máximo, se puede definir un desencadenador INSTEAD OF por cada instrucción INSERT, UPDATE o DELETE en cada tabla o vista. No obstante, en las vistas es posible definir otras vistas que tengan su propio desencadenador INSTEAD OF. Los desencadenadores INSTEAD OF no se permiten en las vistas actualizables WITH CHECK OPTION. SQL Server emitirá un error si se agrega un desencadenador INSTEAD OF a una vista actualizable donde se ha especificado WITH CHECK OPTION. El usuario debe quitar esta opción mediante ALTER VIEW antes de definir el desencadenador INSTEAD OF. { [DELETE] [,] [INSERT] [,] [UPDATE] } Son palabras clave que especifican qué instrucciones de modificación de datos activan el desencadenador cuando se intentan en esta tabla o vista. Se debe especificar al menos una opción. En la definición del desencadenador se permite cualquier combinación de éstas, en cualquier orden. Si especifica más de una opción, sepárelas con comas. Para los desencadenadores INSTEAD OF, no se permite la opción DELETE en tablas que tengan una relación de integridad referencial que especifica una acción 99

ON DELETE en cascada. Igualmente, no se permite la opción UPDATE en tablas que tengan una relación de integridad referencial que especifica una acción ON UPDATE en cascada. AS Son las acciones que va a llevar a cabo el desencadenador. sql_statement Son las condiciones y acciones del desencadenador. Las condiciones del desencadenador especifican los criterios adicionales que determinan si los intentos de las instrucciones DELETE, INSERT o UPDATE hacen que se lleven a cabo las acciones del desencadenador. Las acciones del desencadenador especificadas en las instrucciones TransactSQL entran en efecto cuando se intenta la operación DELETE, INSERT o UPDATE. Los desencadenadores pueden incluir cualquier número y clase de instrucciones Transact-SQL. Un desencadenador está diseñado para comprobar o cambiar los datos en base a una instrucción de modificación de datos; no debe devolver datos al usuario. Las instrucciones Transact-SQL de un desencadenador incluyen a menudo lenguaje de control de flujo. En las instrucciones CREATE TRIGGER se utilizan unas cuantas tablas especiales: •

deleted e inserted son tablas lógicas (conceptuales). Son de estructura similar a la tabla en que se define el desencadenador, es decir, la tabla en que se intenta la acción del usuario, y guarda los valores antiguos o nuevos de las filas que la acción del usuario puede cambiar. Por ejemplo, para recuperar todos los valores de la tabla deleted, utilice: SELECT * FROM deleted

Ejemplo: Manda borrar en cascada todo el detalle de una factura al eliminar esta CREATE TRIGGER trDelFactura ON Factura FOR DELETE AS DELETE FROM Detallefactura WHERE ConsecPk In (SELECT ConsecPk FROM DELETED WHERE DELETED.ConsecPk = DetalleFactura.ConsecPk)

100

Ejemplo. Manda un mensaje cuando se agrega o modifica un producto. El mensaje 50009 es un mensaje definido por el usuario en sysmessages.

CREATE TRIGGER InsUpdProducto ON Producto FOR INSERT, UPDATE AS RAISERROR (50009, 16, 10)

Ejemplo. Por medio de este trigger se hace un respaldo del producto modificado en la tabla ProductoAux CREATE TRIGGER updProducto ON Producto FOR UPDATE AS SAVE TRANSACTION MiTransacc INSERT INTO ProductoAux SELECT * FROM Deleted IF (@@error 0) BEGIN ROLLBACK TRANSACTION MiTransacc END

Ver los triggers que tiene una determinada tabla Devuelve el tipo o los tipos de desencadenadores definidos en la tabla especificada de la base de datos actual. sp_helptrigger 'Factura' Para borrar un trigger DROP TRIGGER trDelFactura Para ver el código de un trigger Sp_helptext trDelFactura

101

Cursores Las operaciones de una base de datos relacional actúan en un conjunto completo de filas. El conjunto de filas que devuelve una instrucción SELECT está compuesto de todas las filas que satisfacen las condiciones de la cláusula WHERE de la instrucción. Este conjunto completo de filas que devuelve la instrucción se conoce como el conjunto de resultados. Las aplicaciones, especialmente las aplicaciones interactivas en línea, no siempre pueden trabajar de forma efectiva con el conjunto de resultados completo si lo toman como una unidad. Estas aplicaciones necesitan un mecanismo que trabaje con una fila o un pequeño bloque de filas cada vez. Los cursores son una extensión de los conjuntos de resultados que proporcionan dicho mecanismo. Los cursores amplían el procesamiento de los resultados porque: •

Permiten situarse en filas específicas del conjunto de resultados.

•

Recuperan una fila o bloque de filas de la posición actual en el conjunto de resultados.

•

Aceptan modificaciones de los datos de las filas en la posición actual del conjunto de resultados

•

Aceptan diferentes grados de visibilidad para los cambios que realizan otros usuarios en la información de la base de datos que se presenta en el conjunto de resultados.

•

Proporcionan instrucciones de Transact-SQL en secuencias de comandos, procedimientos almacenados y acceso de desencadenadores a los datos de un conjunto de resultados.

Proceso de cursores Los cursores de Transact-SQL utilizan el siguiente proceso general con todos los cursores de SQL Server: 1. Asigne un cursor al conjunto de resultados de una instrucción Transact-SQL y defina las características del cursor como, por ejemplo, si sus filas se pueden actualizar. 2. Ejecute la instrucción de Transact-SQL para llenar el cursor. 3. Recupere las filas del cursor que desea ver. La operación de recuperar una fila o un bloque de filas de un cursor recibe el nombre de recopilación. Realizar series de recopilaciones para recuperar filas, ya sea hacia adelante o hacia atrás, recibe el nombre de desplazamiento.

102

4. Existe la opción de realizar operaciones de modificación (actualización o eliminación) en la fila de la posición actual del cursor. 5. Cierre el cursor.

Cursores de Transact-SQL. Los cursores de Transact-SQL se utilizan principalmente en procedimientos almacenados, desencadenadores y secuencias de comandos de Transact-SQL a fin de dejar disponible el contenido de un conjunto de resultados para otras instrucciones de Transact-SQL. El proceso típico para utilizar un cursor de Transact-SQL en un procedimiento almacenado o desencadenador es: 1. Indicar las variables de Transact-SQL que contendrán los datos que ha devuelto el cursor. Indicar una variable para cada columna del conjunto de resultados. Establecer variables lo suficientemente grandes para que puedan contener los valores que devuelve la columna y con un tipo de datos que se pueda convertir implícitamente desde el tipo de datos de la columna. 2. Asocie un cursor de Transact-SQL con una instrucción SELECT mediante la instrucción DECLARE CURSOR. La instrucción DECLARE CURSOR también define las características del cursor como, por ejemplo, el nombre y si es de sólo lectura o de desplazamiento sólo hacia delante. 3. Utilice la instrucción OPEN para ejecutar la instrucción SELECT y llenar el cursor. 4. Utilice la instrucción FETCH INTO para recopilar filas individuales y mover los datos de cada columna a la variable especificada. Otras instrucciones de Transact-SQL pueden hacer referencia a estas variables para tener acceso a los valores de los datos recopilados. Los cursores de TransactSQL no admiten la recopilación de bloques de filas. 5. Cuando haya terminado con el cursor, utilice la instrucción CLOSE. Al cerrar un cursor se liberan algunos recursos, como el conjunto de resultados del cursor y sus bloqueos en la fila actual, pero la estructura del cursor sigue lista para procesar en caso de volver a emitir una instrucción OPEN. Dado que el cursor sigue presente, no puede volver a utilizar su nombre en este momento. La instrucción DEALLOCATE libera completamente todos los recursos asignados al cursor, incluido su nombre. Una vez cancelada la asignación de un cursor, se debe emitir una instrucción DECLARE para volver a generarlo.

103

Ejemplo. En el siguiente ejemplo se incrementa el precio de venta en un 10% si la existencia es menor a 1000 y en caso contrario un 5% DECLARE @Existencia Numeric(18,2) DECLARE @NomProducto varchar(60) DECLARE curEjemplo CURSOR FOR SELECT NomProducto, Existencia FROM Producto OPEN curEjemplo BEGIN TRANSACTION FETCH NEXT FROM curEjemplo INTO @NomProducto,@Existencia WHILE @@FETCH_STATUS = 0 BEGIN If @Existencia < 1000 Update Producto Set PrecioVenta = PrecioVenta * 1.10 Else Update Producto Set PrecioVenta = PrecioVenta * 1.05 FETCH NEXT FROM curEjemplo INTO @NomProducto,@Existencia END COMMIT TRANSACTION CLOSE curEjemplo DEALLOCATE curEjemplo

104

Módulo 10: Implementando Funciones Definidas por el Usuario Funciones definidas por el usuario Las funciones son subrutinas formadas por una o varias instrucciones TransactSQL que se pueden utilizar para encapsular un código con el fin de utilizarlo de nuevo posteriormente. Microsoft SQL Server 2000 no limita a los usuarios a las funciones integradas que vienen definidas como parte del lenguaje Transact-SQL, sino que les permite crear sus propias funciones definidas por el usuario. Las funciones definidas por el usuario se crean con la instrucción CREATE FUNCTION, se modifican con la instrucción ALTER FUNCTION y se quitan con la instrucción DROP FUNCTION. Todos los nombres de funciones completos (database_name.owner_name.function_name) definidos por el usuario deben ser únicos. Para crear, modificar o quitar funciones definidas por el usuario, debe tener permisos de CREATE FUNCTION. Los usuarios distintos del propietario deben tener permiso EXECUTE para una función, y solo así podrán utilizarla en una instrucción de Transact-SQL. Para crear o modificar tablas con referencias a funciones definidas por el usuario en la restricción CHECK, la cláusula DEFAULT o la definición de una columna calculada, también debe tener permiso REFERENCES para las funciones. Los errores de Transact-SQL que producen la cancelación de una instrucción y continúan con la siguiente instrucción del módulo, como desencadenadores o procedimientos almacenados, se tratan de forma distinta dentro de una función. En las funciones, estos errores hacen que se detenga la ejecución de la función. Esto hace que se cancele la función que invocó la instrucción. Tipos de funciones definidas por el usuario SQL Server 2000 admite tres tipos de funciones definidas por el usuario: •

Funciones escalares

•

Funciones de valores de tabla de múltiples instrucciones

•

Funciones de valores de tabla en línea

Una función definida por el usuario no tiene ninguno o tiene varios parámetros de entrada y devuelve un valor escalar o una tabla. Una función puede tener un máximo de 1024 parámetros de entrada. Cuando un parámetro de la función toma un valor predeterminado, debe especificarse la palabra clave DEFAULT al llamar a la función para poder obtener el valor predeterminado. Este comportamiento es diferente del de los parámetros con valores predeterminados de los 105

procedimientos almacenados, para los cuales omitir el parámetro implica especificar el valor predeterminado. Las funciones definidas por el usuario no admiten parámetros de salida. Las funciones escalares devuelven un único valor de datos del tipo definido en la cláusula RETURNS. Se pueden utilizar todos los tipos de datos escalares, incluidos bigint y sql_variant. No se admiten el tipo de datos timestamp, los tipos de datos definidos por el usuario y los tipos de datos no escalares, como table o cursor. El cuerpo de la función, definido en un bloque BEGIN...END, contiene el conjunto de instrucciones Transact-SQL que devuelven el valor. El tipo devuelto puede ser cualquier tipo de datos excepto text, ntext, image, cursor y timestamp. Las funciones de valores de tabla devuelven un tipo de datos table. Las funciones de valores de tabla en línea no tienen cuerpo; la tabla es el conjunto de resultados de una sola instrucción SELECT. Para una función de valores de tabla de varias instrucciones, el cuerpo de la función, definido en un bloque BEGIN...END, contiene las instrucciones TRANSACT-SQL que generan e insertan filas en la tabla que se va a devolver.

Observaciones Las funciones definidas por el usuario son de valores de tabla o de valores escalares. Son funciones de valores escalares si la cláusula RETURNS especificó uno de los tipos de datos escalares. Las funciones de valores escalares se pueden definir utilizando varias instrucciones Transact-SQL. Son funciones de valores de tabla si la cláusula RETURNS especificó TABLE. Según cómo se haya definido el cuerpo de la función, las funciones de valores de tabla se pueden clasificar como en funciones en línea o de múltiples instrucciones. Si la cláusula RETURNS especifica TABLE sin una lista de columnas, la función es en línea. Las funciones en línea son funciones de valores de tabla definidas con una única instrucción SELECT como parte del cuerpo de la función. Las columnas, incluidos los tipos de datos, de la tabla que devuelve la función, proceden de la lista SELECT de la instrucción SELECT que define la función. Si la cláusula RETURNS especifica un tipo TABLE con columnas y sus tipos de datos, se trata de una función de valores de tabla de múltiples instrucciones. El cuerpo de una función de múltiples instrucciones permite las siguientes instrucciones. Las instrucciones no incluidas en la lista no se permiten en el cuerpo de una función: •

Instrucciones de asignación.

•

Las instrucciones de control de flujo.

•

Instrucciones DECLARE que definen variables de datos y cursores que son locales a la función.

106

•

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

•

Operaciones de cursor que hacen referencia a cursores locales que se declaran, abren, cierran y cuya asignación se cancela en la función. Sólo se permiten las instrucciones FETCH que asignan valores a las variables locales mediante la cláusula INTO; no se permiten las instrucciones FETCH que devuelven los datos al cliente.

•

Instrucciones INSERT, UPDATE y DELETE que modifican las variables table locales para la función.

•

Instrucciones EXECUTE que llaman a procedimientos almacenados extendidos.

Invocación a funciones Las funciones de valores escalares se pueden llamar en aquellos lugares donde se utilizan expresiones escalares, incluidas las columnas calculadas y las definiciones de restricciones CHECK. Al llamar a funciones de valores escalares, utilice como mínimo el nombre de dos partes de la función. [database_name.]owner_name.function_name ([argument_expr][,...]) Si utiliza una función definida por el usuario para definir una columna calculada, la calidad determinista de la función también define si se creará un índice en esa columna calculada. En una columna calculada, se puede crear un índice que utiliza una función sólo si la función es determinista. Una función es determinista si siempre devuelve el mismo valor con los mismos datos de entrada. Las funciones de valores de tabla se pueden llamar utilizando un nombre en una sola parte. [database_name.][owner_name.]function_name ([argument_expr][,...])

Funciones Escalares CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ WITH < function_option> [ [,] ...n] ] [ AS ] BEGIN function_body RETURN scalar_expression END

107

En este ejemplo, una función definida por el usuario de tipo escalar, PuntoMedio, toma dos argumentos, el NegocioPk y el ProductoPk y calcula el punto medio de las existencias en base a su Máximo y Mínimo. CREATE FUNCTION PuntoMedio (@NegocioPk SmallInt, @ProductoPk Int) RETURNS Numeric (18,2) AS BEGIN DECLARE @Medio Numeric (18,2) SELECT @Medio = (Maximo – Minimo) / 2 + Minimo From Producto Where NegocioPk = @NegocioPk and ProductoPk = @ProductoPk RETURN(@Medio) END Llamada a la Función: SELECT Facturacion.dbo.PuntoMedio(1,2) as 'Punto Medio’

Funciones de Valores de Tabla en Línea CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ WITH < function_option > [ [,] ...n ] ] [ AS ] RETURN [ ( ] select-stmt [ ) ]

Este ejemplo devuelve una función de valores de tabla en línea. Esta nos arroja como resultado el nombre del producto y las cantidades vendidas de éste: CREATE FUNCTION VentasProducto (@NegocioPk SmallInt, @ProductoPk Int) RETURNS TABLE AS RETURN (SELECT NomProducto, Cantidad FROM Producto A Inner Join DetalleFactura B On (A.NegocioPk = B.NegocioPk And A.ProductoPk = B.ProductoPk) WHERE A.NegocioPk = @NegocioPk And A.ProductoPk = @ProductoPk)

Llamada a la Función: select * from Ventasproducto(1,2)

108

Funciones de Valores de Tabla de Múltiples Instrucciones CREATE FUNCTION [ owner_name. ] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE < table_type_definition > [ WITH < function_option > [ [,] ...n ] ] [ AS ] BEGIN function_body RETURN END Este ejemplo devuelve una función de valores de tabla de múltiples instrucciones. Nos arroja como resultado las facturas que tienen un importe superior al parámetro de entrada: CREATE FUNCTION FacturadoMayor ( @Importe Numeric(18,2)) RETURNS @Facturado TABLE ( FolFactura char(10), FecFactura SmallDateTime, NomProducto Varchar(60), Cantidad Numeric(18,2), Importe Numeric(18,2) ) AS BEGIN INSERT @Facturado SELECT A.FolFactura, A.Fecfactura,C.NomProducto,B.Cantidad,B.Importe FROM (Factura A Inner Join DetalleFactura B On (A.ConsecPk = B.ConsecPk)) Inner Join Producto C ON (B.NegocioPk = C.NegocioPk And B.ProductoPk = C.ProductoPk) WHERE B.Importe > @Importe RETURN END

Select * From FacturadoMayor(10)

109

Módulo 12: Programando a través de Múltiples Servidores Consultas Distribuidas Las consultas distribuidas tienen acceso a datos de varios orígenes, que pueden estar almacenados en un equipo o en equipos distintos. Microsoft SQL Server 2000 admite las consultas distribuidas a través de OLE DB

Las consultas distribuidas proporcionan a los usuarios de SQL Server acceso a: •

Datos distribuidos almacenados en múltiples instancias SQL Server.

•

Datos heterogéneos almacenados en varios orígenes de datos relacionales y no relacionales a los que se tiene acceso mediante un proveedor OLE DB.

Los proveedores OLE DB exponen datos en objetos tabulares llamados conjuntos de filas. En las instrucciones Transact-SQL, SQL Server 2000 permite que se haga referencia a los conjuntos de filas de los proveedores OLE DB como si fueran una tabla de SQL Server. En las instrucciones SELECT, INSERT, UPDATE y DELETE de Transact-SQL, se puede hacer referencia directa a las tablas y vistas de orígenes de datos externos. Puesto que las consultas distribuidas usan OLE DB como interfaz subyacente, éstas tienen acceso a los sistemas DBMS relacionales tradicionales con procesadores de consultas SQL, así como a los datos administrados por orígenes de datos de capacidad y sofisticación diversas. Siempre que el software propietario de los datos los expone en un conjunto de filas tabular a través del proveedor OLE DB, los datos se podrán usar en las consultas distribuidas. Nota El uso de las consultas distribuidas en SQL Server es similar a la funcionalidad de las tablas vinculadas mediante ODBC, que anteriormente admitía Microsoft Access. Esta funcionalidad se encuentra ahora integrada en SQL Server con OLE DB como interfaz para los datos externos.


110

Configurar servidores vinculados Una configuración con servidores vinculados permite a Microsoft SQL Server ejecutar comandos en orígenes de datos OLE DB situados en servidores diferentes. Los servidores vinculados ofrecen estas ventajas: •

El acceso al servidor remoto.

•

La capacidad de ejecutar consultas distribuidas, actualizaciones, comandos y transacciones en orígenes de datos heterogéneos en toda la organización.

•

La capacidad de tratar diferentes orígenes de datos de manera similar.

Componentes de servidores vinculados En una definición de servidor vinculado se especifica un proveedor OLE DB y un origen de datos OLE DB. Los proveedores OLE DB son bibliotecas de vínculos dinámicos (DLL) que administran e interactúan con orígenes de datos específicos. Los orígenes de datos OLE DB identifican la base datos específica a la que se puede tener acceso mediante OLE DB. Aunque los orígenes de datos en los que se realizan consultas mediante definiciones de servidores vinculados son normalmente bases de datos,

111

hay proveedores OLE DB para una amplia variedad de archivos y formatos de archivos, incluidos archivos de texto, datos de hojas de cálculo y los resultados de búsquedas de contenido de texto. Vinculación de un Servidor sp_addlinkedserver Crea un servidor vinculado, que permite obtener acceso a consultas heterogéneas distribuidas en orígenes de datos OLE DB. Tras crear un servidor vinculado con sp_addlinkedserver, este servidor puede ejecutar consultas distribuidas. Si el servidor vinculado se define como Microsoft SQL Server, se pueden ejecutar procedimientos almacenados remotos. En este ejemplo se crea un servidor vinculado denominado SVRTEC que utiliza el Proveedor Microsoft OLE DB para SQL Server. Para Vincular una Base de Datos de Access a SQL Server 1. Ejecute sp_addlinkedserver para crear el servidor vinculado, y especifique Microsoft.Jet.OLEDB.4.0 como provider_name y el nombre de ruta de acceso completo del archivo de base de datos .mdb de Access como data_source. El archivo .mdb de base de datos debe residir en el servidor. data_source se evalúa en el servidor, no en el cliente, y la ruta de acceso debe ser válida en el servidor. Por ejemplo, para crear un servidor vinculado llamado BD que funcione en la base de datos llamada BD.mdb en el directorio C:\Sistema, ejecute: sp_addlinkedserver 'c:\Sistema\BD.mdb'

'BD',

'Access

97',

'Microsoft.Jet.OLEDB.4.0',

2. Para tener acceso a una base de datos de Access que no sea segura, los inicios de sesión de SQL Server que intentan tener acceso a la base de datos de Access deben definir una asignación de inicio de sesión para el nombre de usuario Admin sin contraseña. En este ejemplo se habilita el acceso del usuario local sa al servidor vinculado llamado BD. sp_addlinkedsrvlogin 'BD', false, 'sa', 'Admin', NULL Las bases de datos Access no tienen nombres de esquema ni de catálogo. Por tanto, con un nombre de cuatro partes de la forma linked_server...table_name se puede hacer referencia a las tablas de un servidor vinculado basado en Access en consultas distribuidas. SELECT * FROM BD...Menu

112

Para crear un servidor vinculado que obtenga acceso a una base de datos SQL Server 1. Ejecute sp_addlinkedserver para crear el servidor vinculado y especifique SQLOLEDB como provider_name y el nombre de red del servidor que ejecuta la instancia remota de SQL Server como data_source. Por ejemplo, para crear un servidor vinculado denominado SVRTEC que funcione en la instancia de SQL Server, que se ejecuta en el servidor cuyo nombre de red es Lab06, ejecute: sp_addlinkedserver N'SVRTEC', ' ', N'SQLOLEDB', N'Lab06' 2. Asigne cada inicio de sesión de SQL Server local que necesite tener acceso al servidor vinculado a un inicio de sesión con Autenticación de SQL Server del servidor vinculado. En este ejemplo se asigna el acceso del inicio de sesión local Joe al inicio de sesión con Autenticación de SQL Server Visitor en el servidor vinculado llamado Lab06. sp_addlinkedsrvlogin N'LinkSQLSrvr', false, N'sa', N'sa', N'hola'




Para consultar la tabla de Cliente en la base de datos de Facturación: SELECT * FROM SVRTEC.Facturacion..Cliente

113

Transacciones Distribuidas BEGIN DISTRIBUTED TRANSACTION Especifica el inicio de una transacción distribuida de Transact-SQL administrada mediante el Coordinador de transacciones distribuidas de Microsoft (MS DTC, Microsoft Distributed Transaction Coordinator). Sintaxis BEGIN DISTRIBUTED TRAN [ SACTION ] [ transaction_name | @tran_name_variable ] Argumentos transaction_name Se trata de un nombre de transacción definida por el usuario que se utiliza para realizar el seguimiento de la transacción distribuida en los programas de MS DTC. El argumento transaction_name debe seguir las reglas de los identificadores, pero sólo se utilizan los 32 primeros caracteres. @tran_name_variable Se trata del nombre de una variable definida por el usuario que contiene el nombre de una transacción utilizada para realizar el seguimiento de la transacción distribuida en las herramientas de MS DTC. La variable se debe declarar con un tipo de datos char, varchar, nchar o nvarchar. Observaciones El servidor que ejecuta la instrucción BEGIN DISTRIBUTED TRANSACTION es el originador de la transacción y controla su realización. Posteriormente, cuando se ejecuta en la conexión una instrucción COMMIT TRANSACTION o ROLLBACK TRANSACTION, el servidor que controla la transacción solicita a MS DTC que administre la realización de la transacción entre los servidores involucrados. Hay dos formas de dar de alta servidores SQL remotos en una transacción distribuida: •

Una conexión que ya se ha dado de alta en la transacción distribuida realiza una llamada a un procedimiento almacenado remoto que hace referencia a un servidor remoto.

•

Una conexión que ya se ha dado de alta en la transacción distribuida ejecuta una consulta distribuida que hace referencia a un servidor remoto.

Por ejemplo, si se emite BEGIN DISTRIBUTED TRANSACTION en el ServidorA, la conexión llama a un procedimiento almacenado en el ServidorB y a otro 114

procedimiento almacenado en el ServidorC, y el procedimiento almacenado del ServidorC ejecuta una consulta distribuida contra el ServidorD, los cuatro servidores SQL están involucrados en la transacción distribuida. El ServidorA es el que origina y controla la transacción. Las conexiones involucradas en transacciones Transact-SQL distribuidas no obtienen un objeto de transacción que puedan pasar a otra conexión para que se dé de alta explícitamente en la transacción distribuida. La única forma en que un servidor remoto puede darse de alta en la transacción consiste en que constituya el destino de una llamada a un procedimiento almacenado remoto o a una consulta distribuida. Este ejemplo actualiza el nombre del producto en las bases de datos local y remota. Ambas bases de datos, local y remota, confirmarán o desharán la transacción. Nota A menos que MS DTC esté instalado actualmente en el equipo que ejecuta Microsoft SQL Server, este ejemplo produce un mensaje de error. Para obtener más información acerca de MS DTC, consulte la documentación del Coordinador de transacciones distribuidas de Microsoft. USE Facturacion GO BEGIN DISTRIBUTED TRANSACTION UPDATE Producto SET NomProducto = 'Tornillos' WHERE ProductoPk = 3 and NegocioPk = 2 EXECUTE remote.Facturacion.dbo.spModNombreProd 2,3, ‘Tornillos’ COMMIT TRAN GO Nota: Para obtener la funcionalidad de transacciones distribuidas, se requiere tener los servidores remotos vinculados y tener corriendo el servicio de MSDTC

115

Crear una Vista Dividida Una vista dividida combina los datos divididos procedentes de un conjunto de tablas miembro en uno o más servidores, y hace que los datos parezcan proceder todos de una sola tabla. Microsoft SQL Server 2000 distingue entre vistas con particiones locales y distribuidas. En una vista local con particiones, todas las tablas que participan y la vista residen en la misma instancia de SQL Server. En una vista con particiones distribuida, al menos una de las tablas participantes reside en un servidor diferente (remoto). Además, SQL Server 2000 diferencia entre vistas con particiones que son actualizables y vistas que son copias de solo lectura de las tablas subyacentes. Para poder implementar una vista dividida, primero debe realizar una partición horizontal de la tabla. La tabla original se reemplaza con varias tablas miembro más pequeñas. Cada tabla miembro tiene el mismo número de columnas que la tabla original, y cada columna tiene los mismos atributos (como tipos de datos, tamaño, intercalación) que la columna correspondiente de la tabla original. Si va a crear una vista dividida distribuida, cada tabla miembro estará en un servidor miembro diferente. Para obtener la mayor transparencia de ubicación, el nombre de las bases de datos miembro debe ser el mismo en cada servidor miembro, aunque esto no es necesario. Por ejemplo: Server1.CustomerDB, Server2.CustomerDB, Server3.CustomerDB. Diseñe las tablas miembro de manera que cada tabla almacene una rebanada horizontal de la tabla original basada en un intervalo de valores de clave. Los intervalos se basan en los valores de datos de una columna de partición. El intervalo de valores de cada tabla miembro se exige mediante una restricción CHECK de la columna de partición; los intervalos no se pueden superponer. Por ejemplo, no puede haber una tabla con un intervalo de 1 a 200000 y otra con un intervalo de 150000 a 300000, ya que no quedaría claro qué tabla contiene los valores que van de 150000 a 200000. Por ejemplo, imagine que divide una tabla Customer en tres tablas. La restricciones CHECK de estas tablas son: -- On Server1: CREATE TABLE Customer_33 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999), ... -- Additional column definitions) -- On Server2: CREATE TABLE Customer_66 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 33000 AND 65999), ... -- Additional column definitions) -- On Server3: CREATE TABLE Customer_99 116

(CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 66000 AND 99999), ... -- Additional column definitions) Después de crear las tablas miembro, defina una vista con particiones distribuida en cada servidor miembro, con el mismo nombre para cada vista. Esto permite que las consultas que hacen referencia al nombre de vista dividida distribuida se ejecuten en los servidores miembro. El sistema funciona como si hubiera una copia de la tabla original en cada servidor miembro, aunque cada servidor sólo tiene una tabla miembro y una vista dividida distribuida. La aplicación detecta la ubicación de los datos. Puede generar las vistas divididas distribuidas, si: •

Agrega definiciones de servidor vinculado a cada servidor miembro que contenga la información de la conexión necesaria para ejecutar consultas distribuidas en los demás servidores miembro. Esto proporciona a la vista dividida distribuida el acceso a los datos de los demás servidores miembro.

•

Crea una vista dividida distribuida en cada servidor miembro. Las vistas utilizan las instrucciones SELECT distribuidas para obtener acceso a los datos de los servidores miembro vinculados y mezclan las filas distribuidas con las filas de la tabla miembro local.

Para crear vistas divididas distribuidas en el ejemplo anterior, debe hacer lo siguiente: •

Agregar una definición de servidor vinculado denominada Server2 con la información de conexión de Server2, y una definición de servidor vinculado denominada Server3 para tener acceso a Server3.

Crear la siguiente vista dividida distribuida: CREATE VIEW Customers AS SELECT * FROM CompanyDatabase.TableOwner.Customers_33 UNION ALL SELECT * FROM Server2.CompanyDatabase.TableOwner.Customers_66 UNION ALL SELECT * FROM Server3.CompanyDatabase.TableOwner.Customers_99

117