1. INTRODUCCIÓN CURSO SQL SERVER 2000 ¿Qué es SQL Server

RESULTADOS

DATABASE

Cliente

TRANSACT SQL

Server

Servidor

¿Qué es SQL Server? SQL Server es un manejador de bases de datos relacionales (RDBMS), bajo la arquitectura Cliente/Servidor. Utiliza el lenguaje Transact-SQL para enviar peticiones entre el Cliente y SQL Server. Arquitectura Cliente/Servidor SQL Server utiliza la arquitectura Cliente/Servidor para dividir la carga de trabajo entre dos tareas que corren unas en el Servidor y otras en el Cliente.: ♣

El Cliente es el responsable de la lógica del modelo de negocios y de la presentación de los datos a los usuarios. El Cliente típicamente corre en una o más computadoras clientes.

♣

SQL Server administra la base de datos y permite la disponibilidad de los recursos del Servidor -tales como memoria, recursos de red, operaciones en disco- entre otras múltiples peticiones.

La arquitectura Cliente/Servidor nos permite diseñar y desarrollar aplicaciones para mejorar una gran variedad de ambientes. Las interfaces de programación del Cliente proporcionan las facilidades a las aplicaciones para que puedan correr en computadoras cliente separadas y puedan comunicarse con el Servidor a través de una red. Sistemas de Administración de Base de Datos Relacionales (RDBMS) Un RDBMS tiene la responsabilidad de: ♣

Mantener las relaciones entre los datos de la base de datos (modelo entidad-relación)

♣

Asegurar que los datos son almacenados correctamente, esto es, que las reglas que definen las relaciones entre datos no sean violadas (integridad).

♣

Recuperación de todos los datos para mantener su consistencia en caso de falla del sistema. (ROLLBACK 1

Transact SQL SQL Server utiliza Transact SQL, una versión del Lenguaje de Consulta Estructurado (SQL), como medio para accesar la información de la base de datos (database query), así como un lenguaje de programación. SQL es un conjunto de comandos que permiten especificar información que se quiere recuperar o modificar. Con Transact-SQL se puede consultar, actualizar y administrar la información de la base de datos. Transact-SQL es fundamental para trabajar con SQL Server. Todas las aplicaciones que se comunican con SQL Server lo hacen enviando instrucciones Transact-SQL al servidor, independientemente de la interfaz de usuario de la aplicación. Transact-SQL se genera desde muchas clases de aplicaciones, como son las siguientes: ♣ ♣ ♣

Aplicaciones creadas con sistemas de desarrollo tales como Microsoft Visual C++, Microsoft Visual Basic o Microsoft Visual J++, Delphi, CBuilder, etc. y que utilizan interfaces de programación de aplicaciones (API) de base de datos tales como ADO, OLE DB y ODBC. Páginas Web que extraen datos de bases de datos SQL Server. Sistemas de bases de datos distribuidos desde los que duplican datos SQL Server en varias bases de datos o se ejecutan consultas distribuidas.

2

Plataformas que soporta SQL Server MS DOS Windows 3.x

Windows NT

Windows 9x

Terceras Partes

Browsers

Cliente

Servidor

WINDOWS NT

WINDOWS 9X

Plataformas SQL Server corre en los sistemas operativos mostrados en la figura. Puede utilizar alguno o todos los sistemas operativos para crear y ejecutar aplicaciones. Plataforma Windows 9x Windows 2000 Windows NT Workstations Windows NT Windows NT Enterprise Ed. Windows 3.x MS-DOS Terceras Partes (Third Party) Internet Browser

Server Software Sí, corre como una aplicación Sí, corre como un servidor Sí, corre como un servidor Sí, corre como un servidor Sí, corre como un servidor

Cliente OS Sí Sí Sí Sí Sí Sí Sí Sí, Unix, Macintosh Sí

3

Servicios de SQL Server Administración de Bases de Datos Procesamiento de Transacciones y Consultas (Querys) Integridad de Datos

SQL Server

Agente de SQL Server (SQL Server Agent)

Coordinador de Transacciones Distribuidas (Microsoft Distributed Transaction Coordinator) Servidor

Rastreador (Microsoft Search)

Trabajos (Jobs) Avisos (Alerts) Operadores Administración de Transacciones Distribuidas

Catálogos de Texto Completo (Full-Text Catalogs) Indices de Texto Completo (Full-Text Indexes)

SQL Server incluye los siguientes servicios: SQL Server, SQL ServerAgent, Microsoft Distributed Transaction Coordinator (DTC) y Microsoft Search. Aunque estos servicios corren usualmente como servicios en Windows NT, también pueden correrse como aplicaciones.

Servicio SQL Server El Servicio SQL Server es la máquina de la base de datos. Es el componente que procesa todas las instrucciones de Transact SQL y maneja todos los archivos contenidos en las bases de datos del servidor. El Servicio SQL Server: Proporciona los recursos de la computadora entre múltiples usuarios concurrentes. Previene problemas lógicos, tales como solicitudes de usuarios que quieren actualizar los mismos datos al mismo tiempo. Asegura la consistencia e integridad de los datos. Servicio Agente de SQL Server (SQL ServerAgent Service) El Agente de SQL Server es un servicio que trabaja en conjunción con SQL Server para crear y administrar trabajos (jobs) locales o multiserver, avisos (alerts) y operadores. Servicio Coordinador de Transacciones Distribuidas (Microsoft Distributed Transaction Coordinator Service)

Este servicio permite a los clientes incluir diferentes orígenes de datos en una transacción. DTC coordina la correcta aplicación de transacciones distribuidas para asegurar que todas las actualizaciones en todos los servidores sean permanentes o que, en caso de errores, todas las modificaciones sean canceladas.

4

Servicio de Rastreador(Microsoft Search Service) Este servicio es una máquina de texto-completo (full-text engine) que corre como un servicio de Windows NT. El soporte de texto completo involucra la capacidad de editar consultas (query) contra datos carácter y la creación y mantenimiento de los índices que facilitan ésos querys.

5

2. ALMACENAMIENTO INTERNO. DETALLES Al crear una tabla, se insertan una o varias filas en varios catálogos del sistema para administrar dicha tabla. Como mínimo, se añaden a los catálogos (tablas) del sistema SYSOBJECT, SYSINDEXES Y SYSCOLUMNS. Cuando la nueva tabla se define con una o varias restricciones, las filas se añaden a las tablas del sistema SYSREFERENCES Y SYSCONSTRAINTS. Para cada tabla creada se añade a la tabla SYSOBJECTS una única fila que contiene, entre otras cosas, el nombre, el ID del objeto y e propietario de la nueva tabla. La tabla SYSINDEXES recibirá una sola fila que contiene punteros a las páginas de datos primera y última utilizadas por la nueva tabla e información relativa al tamaño de la tabla, incluido el número de extensiones, páginas y filas utilizadas actualmente. La tabla SYSCOLUMNS recibirá una fila por cada columna de la nueva tabla, y cada fila contendrá información como el nombre de la columna, el tipo de datos y la longitud. A cada columna se le asigna un número de ID de columna que corresponde directamente con el orden en el que se han especificado las columnas al crear la tabla.

PAGINAS DE DATOS Las páginas de datos son las estructuras que contienen todos los datos que no sean de tipo TEXT/IMAGE de una tabla. Como ocurre con todos los demás tipos de páginas en SQL Server, las páginas de datos tienen un tamaño fijo de 2 Kb. Las páginas de datos constan de tres componentes principales: la cabecera de la página, las filas de datos y la tabla de desplazamientos de las filas, como se muestra en la figura a continuación. ESTRUCTURA DE UNA PAGINA DE DATOS

Cabecera de la página 32 bytes

Filas de datos

Tabla de desplazamientos de las filas

INDICES 6

Los índices son la otra estructura importante de datos en disco definidos por el usuario. Un índice proporciona un acceso rápido a los datos cuando se puede buscar en los datos por el valor que es la clave del índice. Para entender mejor este concepto, piense en los índices en su vida cotidiana. Está leyendo un libro sobre SQL Server y desea encontrar las veces que aparece la palabra SELECT. Tiene Dos opciones para hacerlo: puede abrir el libro y examinarlo en su totalidad página por página, o puede mirar en el índice que hay en la página final, buscar la palabra SELECT y después ir a los números de página indicados. Así es conceptualmente como funciona un índice en SQL Server. SQL Server admite índices agrupados y no agrupados. Ambos utilizan árboles B estándar, como se muestra en la figura a continuación. Indice del nivel 3 (Raíz)

Indice del nivel 2 (Nivel Intermedio)

Indice del nivel 1 (Nivel Intermedio)

Indice del nivel 0 (Nivel Hoja) Contiene punteros a páginas de datos

Páginas de datos

Un árbol B proporciona acceso rápido a los datos mediante la búsqueda de un valor clave del índice. Los árboles B agrupan registros con claves similares. La B viene de balanced (equilibrado), y equilibrar el árbol es una característica fundamental de la utilidad de un árbol B. Se gestionan los árboles y se injertan ramas según sea necesario, de forma que la navegación hacia la parte inferior del árbol para buscar un valor y localizar un registro específico siempre necesite sólo unos pocos accesos a las páginas. Como los árboles están equilibrados, la localización de cualquier registro requiere la misma cantidad de recursos, y la velocidad de recuperación será constante porque el índice tiene la misma profundidad por todas partes. Un índice consta de un árbol con una raíz en la que empieza la navegación, posibles niveles de índice intermedios y páginas hoja de nivel inferior. El índice se utiliza para localizar la página hoja correcta. El número de niveles de un índice variará en función del número de filas de la tabla y del tamaño de la columna o columnas clave que componen el índice. Si se crea un índice con una clave grande, habrá espacio para menos entradas en una página, así que harán falta más páginas (y posiblemente más niveles) para el índice. En una recuperación o eliminación cualificada, la

7

página hoja correcta será la página inferior del árbol en la que residen una o varias filas con la clave o claves especificadas. En un índice no agrupado, el nivel inferior del árbol (la página hoja) apunta a la página que tiene una fila de datos que contiene el valor de la clave del índice. En un índice agrupado, la página hoja es la página de datos (

Indices agrupados El nivel hoja de un índice agrupado contiene las páginas de datos, no sólo las claves del índice. Un índice agrupado mantiene los datos en una tabla ordenados físicamente alrededor de la clave. Decidir sobre qué clave realizar la agrupación es una consideración importante de rendimiento. Cuando se recorre el índice hasta el nivel hoja, se habrán recuperado los propios datos, no simplemente los punteros a ellos. En la Figura siguiente se muestra la estructura de un índice agrupado.

Como los datos sólo pueden ordenarse físicamente de una única forma, una tabla sólo puede tener un índice agrupado. El optimizador de consultas favorece en gran medida un índice agrupado porque permite localizar los datos directamente al nivel de hoja. Como define el orden real de los datos, un índice agrupado permite un acceso especialmente rápido para las consultas que buscan un rango de valores. El optimizador de consultas sabe que sólo es necesario examinar un determinado rango de páginas de datos. La mayor parte de las tablas deben tener un índice agrupado. Si su tabla va a tener sólo un índice, en general deberá ser agrupado. Utilizar índices agrupados es importante para las cuestiones de administración del espacio, como dónde aparecerán las filas nuevas en la tabla. Recuerde que si una tabla no tiene un índice agrupado, todas las nuevas filas se añaden al final de la tabla, aunque existan ranuras abiertas disponibles para filas debido a eliminaciones anteriores.

8

Indices no agrupados El nivel hoja del árbol en un índice no agrupado apunta a la página que tiene una fila de datos que contiene el valor de la clave del índice, como se muestra en la Figura a continuación

La presencia o ausencia de un índice no agrupado no afecta a la organización de las páginas de datos, así que no existe la restricción de tener sólo un índice no agrupados para cada tabla, como ocurre con los índices agrupados. Cada tabla puede incluir hasta 254 índices no agrupados, pero generalmente deberá ser más juicioso y tener sólo una pocos por tabla (a menos que tenga una situación real de sólo lectura, en cuyo caso cuantos más índices mejor, excepto por lo que respecta al espacio de almacenamiento) La indexación puede acelerar las consultas (si el índice resulta útil para la consulta) pero puede ralentizar las actualizaciones además de utilizar espacio de almacenamiento adicional. En general, deberá declarar índices no agrupados adicionales cuando espere realizar consultas sobre ciertos campos o utilizar campos en uniones, o cuando quiera asegurar la exclusividad de campos distintos (o además) de los utilizados en su índice agrupado. Nota. Si está utilizando un índice para imponer la exclusividad, existe una forma mejor. Las restricciones PRIMARY KEY y UNIQUE utilizan la indexación para imponerla. Para buscar datos utilizando un índice no agrupado es necesario primero recorrer el índice y después recuperar la página de datos a la que apunta. Por ejemplo, para llegar a una página de datos utilizando un índice con una profundidad de tres (una página raíz, una página intermedia y la página hoja) habrá que recorrer las tres páginas de índice. Seguirá siendo necesario recuperar la página de datos, aunque ya se ha identificado exactamente para que no sea necesario examinar. A pesar de todo, han sido necesarias cuatro operaciones de E/S lógicas para acceder a una página de datos. Puede ver que un índice no agrupado sólo tiene utilidad si es altamente selectivo. Puede resultar útil recordar la idea de buscar un tema en un libro. Si el índice cita muchas páginas para el tema que está buscando, puede decidir hojear el libro desde la primera página citada, en lugar de alternar entre el índice y las páginas citadas. Si el libro es corto, puede empezar desde el

9

principio y examinarlo entero. En cualquier caso, el índice no le habrá resultado demasiado útil. Este tipo de información es la misma que considera el optimizador de SQL Server cuando decide si utilizar un índice no agrupado. Los índices no agrupados son especialmente útiles para consultas de tipo correspondencia exacta, cuando el predicado de la cláusula WHERE se espera que elimine de la consideración la mayoría de las páginas (típicamente del 80 al 85 por 100). Cuando no es así, SQL Server generalmente decide utilizar algún otro índice, más útil, o simplemente explorar toda la tabla, porque entonces incurrirá en el coste de realizar operaciones de E/S sólo para las páginas de datos, en lugar de incurrir en el coste de las operaciones de E/S tanto para las páginas de datos como para las páginas de índice. SQL Server mira en la página de distribución del índice para decidir cómo de selectivo es un índice y decidir qué índice utilizar. La página de distribución es una única página dedicada a servir como histograma (un muestreo de los valores clave del índice). Las páginas de distribución proporcionan una relación que compara el número de filas para las que se va a cumplir la cláusula de selección con respecto al número de filas de una tabla. Una página de distribución se crea al ejecutar UPDATE STATISTICS en una tabla que contenga uno o varios índices, o al crear un índice sobre datos ya existentes. Si la tabla no contiene datos al crear inicialmente el índice, puede que no haya datos que muestrear, así que la página de distribución estará vacía. Tenga en cuenta que esto siempre es así cuando se crea un índice para que lo utilice una restricción PRIMARY KEY o UNIQUE en la instrucción CRÉATE TABLE. Así que asegúrese de ejecutar UPDATE STATISTICS después de cargar esas tablas para que puedan utilizarse los índices de manera inteligente. Ejecute UPDATE STATISTICS periódicamente cuando los datos cambien de manera Crear índices

La sintaxis típica para la creación de un índice es directa: CRÉATE [UNIQUE] [CLUSTERED I NONCLUSTERED] ÍNDEX nombre_índice ON [[basedatos.]propietario.]nombre_tabla (nombre_columna [ nombre_columna]...)

CRÉATE ÍNDEX dispone de algunas opciones adicionales para propósitos especializados: [WITH [FILLFACTOR = X] [[,] PAD_INDEX] [[,] IGNORE_DUP_KEY] [[,] (SORTED_DATA I SORTED_DATA_REORG}] [[,] {IGNORE_RUP_ROW I ALLOW_DUP_ROW) ] ] En la práctica, ninguna de estas opciones se utiliza demasiado, aunque FILLFACTOR es probablemente la más utilizada. FILLFACTOR le permite reservar cierto espacio en cada página hoja de un índice (en un índice agrupado esto es igual a la página de datos). Al reservar algo de espacio libre con FILLFACTOR, más tarde puede evitar la necesidad de dividir páginas para hacer espacio para una entrada. Pero recuerde que FILLFACTOR no está mantenido; sólo indica cuánto espacio está reservado con los datos existentes. Si lo necesita, puede utilizar el comando DBCC DBREINDEX para reconstruir el índice y restablecer el FILLFACTOR original especificado. Consejo. Sf va a reconstruir todos los índices de una tabla, especifique simplemente el índice agrupado con DBCC DBREINDEX. Al hacerlo así se reconstruye internamente toda la tabla y todos los índices no agrupados. FILLFACTOR no suele especificarse índice por índice, pero puede especificarlo así para ajuste fino. Si no se especifica FILLFACTOR, se utiliza el valor predeterminado de todo el servidor. El valor se define para el servidor por medio de sp_configure, fillfactor. Este valor es 0 de manera predeterminada, lo que significa que las páginas hoja de los índices se llenarán todo lo posible.

10

FILLFACTOR generalmente sólo se aplica a la página hoja del índice (la página de datos para un índice agrupado). En situaciones especializadas y de alta utilización, será recomendable reservar espacio en las páginas de índice intermedias para evitar divisiones de página ahí también. Puede hacerlo mediante la opción PAD_INDEX, que utiliza el mismo valor que FILLFACTOR.

Las opciones SORTED_DATA y SORTED_DATA_REORG le indican a SQL Server que los datos ya están ordenados para que pueda omitir ese paso como optimización del rendimiento al crear el índice. Aunque las dos opciones son similares entre sí, SORTED_DATA_REORG puede ser especialmente útil al reconstruir un índice agrupado a afectos de reorganizar la tabla (por ejemplo, para hacer que las páginas sean más contiguas). En ese caso, SORTED_DATA_REORG le indica a SQL Server que reconstruya la tabla pero omita el paso de la ordenación. Puede asegurar la exclusividad de una clave utilizando restricciones PRIMARY KEY y UNIQUE,. Estas restricciones funcionan haciendo un índice exclusivo sobre el valor o valores de la clave (por supuesto que no es necesario que los índice sean exclusivos). Si una instrucción UPDATE o INSERT va a afectar a varias filas, y si se encuentra una única fila que no sería exclusiva, se cancela toda la instrucción y no se ve afectada ninguna fila. Con un índice exclusivo, puede utilizar IGNORE_DUP_KEY para que un error de no exclusividad en una operación UPDATE o INSERT de varias filas no provoque la anulación de toda la instrucción. Se descartará la fila no exclusiva y todas las demás filas se verán afectadas. IGNORE_DUP_KEY no permite que se viole la exclusividad del índice; en su lugar, hace que una violación en una modificación de datos que afecte a varias filas no sea fatal para todas las filas correctas. Tendrá que tener mucho cuidado al utilizar IGNORE_DUP_KEY, porque puede provocar que «pierda» algunas filas. Con esta opción, si intenta actualizar una fila de forma que se cree una clave duplicada, esa fila se descartará; no se devolverá a su valor anterior. En la tabla actualizada no estará ni el nuevo valor ni el valor original de la fila que podría producir el duplicado. Por ejemplo, si intenta actualizar «Smith» a «Jones» y «Jones» ya existe, acabará con un «Jones» y sin «Smith». Francamente, es mejor «limpiar» antes los datos. Puede utilizar una instrucción SELECT con un COUNT(*) agrupado según la clave del índice en la tabla, con una cláusula WHERE similar a la que ejecutaría su actualización de varias filas, para asegurar que el valor de la clave no tenga ninguna fila con un cómputo superior a 1. Si se encuentran filas que tengan un cómputo mayor de 1, podrá corregir los datos o la cláusula WHERE.

Las opciones IGNORE_DUP_ROW y ALLOW_DUP_ROW se utilizan muy poco. Sirven para determinar cómo deben tratarse las filas para los índices agrupados no exclusivos cuando toda una fila puede duplicar a otra. No se explicarán más estas situaciones, porque le recomiendo encarecidamente que siempre tenga un identifícador exclusivo (una clave primaria) en tocias las tablas, en cuyo caso nunca tendrá duplicada toda una fila. IGNORE_DUP_ROW y ALLOW_DUP_ROW pueden resultar útiles al crear una tabla nueva basada en datos ya existentes de alguna otra fuente. Puede que tenga que limpiar los datos para eliminar los duplicados antes de dar el paso correcto de identificar o fabricar una clave primaria. En este caso especializado, IGNORE_DUP_ROW puede ayudarle a limpiar sus datos.

11