Download Introduccion SQL Server

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