UNIDAD 5

SQL SERVER 2008

DATABASE
Introducción
Las bases de datos son el corazón de SQL
server 2008, desde guardar información del
usuario para usarla después hasta actuar
como una manera de almacenamiento
temporal para operaciones de SQL server.
Base de datos del sistema
Cuando se instala SQL son creadas cinco bases
de datos del sistema para almacenar información
y las operaciones de base de datos de soporte.
Cuatro de ellas ( “master”,”model”,”msdb” y
“tempdb”) son visibles durante las operaciones
de SQL, pero la quinta, que es la base de datos
“resource” no está visible.
También se pueden crear Bases de datos de
distribución si la instancia de SQL Server está
configurada como un servidor de replicación.
Base de datos de usuario
Las Bases de datos de usuario son aquellas
que son creadas por cualquier usuario
registrado que posea los permisos
adecuados.
En versiones anteriores de SQL Server, tenía
la opción de instalar las bases de datos de
ejemplo de AdventureWorks2008, pero esta
habilidad ha sido removido del producto.
Planear una base de datos
Una de las responsabilidades clave del
administrador de base de datos es la gestión de
la creación de bases de datos.
Con frecuencia, una compañía comprara una
aplicación de un proveedor que requiere un SQL
Server back-end, sin la planeación completa para
el soporte de la capa de datos.
Muchas veces, el vendedor estará más que feliz
de instalar la instancia de SQL Server, y crear las
bases de datos necesarias para apoyar la
aplicación de soporte.
El mejor momento para empezar a gestionar una
base de datos es antes de que sea creada.
Es imperativo que el administrador de base de
datos deba estar íntimamente involucrado en la
planificación y creación de base de datos que
soporte.
Capacidad de planeación
Una de las primeras cosas que deben ser
determinados en la planificación de una nueva
base de datos es la cantidad de espacio en
disco necesario para la base de datos de
soporte. La idea es tanto para asegurar que hay
suficiente espacio en disco disponible para la
expansión de datos y para reducir la cantidad de
datos y el crecimiento del archivo de
transacciones que es realizado para el acomodo
en la expansión de datos para mejorar la
eficiencia de la base de datos.
Si la base de datos está siendo construida para
soportar una aplicación comprada a un
proveedor, la capacidad de planificación para la
base de datos debe ser muy fácil. Sin embargo,
la simplicidad depende de la documentación
detallada que proporcione el proveedor del
software. La documentación debe describir el
tamaño promedio dela base de datos.
Si la documentación es proporcionada, usted
tendrá una buena idea de qué esperar dela base
de datos y en consecuencia puede ser
configurada.
Si la base de datos está siendo diseñada y
construida internamente, tienen que establecerse
técnicas para determinar la magnitud de los
archivos de datos que se necesitan. Estas
técnicas funcionan para conocer cuantos datos
son agregados en cada transacción, mientras
que en una base de datos proporcionada por el
proveedor, esta información puede no estar
disponible.
Hay un método efectivo para determinar qué tan
grande será un archivo de datos:
La idea es tomar el prototipo de base de datos (la
versión de prueba o desarrollo de la base de datos) y
llenarlo con una cantidad adecuada de datos de
prueba. Después de la base de datos de prueba ha
sido poblada, compruebe el tamaño del archivo de
datos en el disco, y luego se multiplica por 1,5. El
tamaño del archivo resultante debería ser suficiente
para acomodarla carga de datos inicial de la nueva
base de datos con un poco de espacio de sobra.
Una vez que la base de datos este trabajando, es muy
importante monitorear el tamaño del archivo de los
datos para analizar el factor de crecimiento del archivo.
Creando base de datos
Las bases de datos son creadas usualmente por
escritura o por ejecución de código T-SQL o por
medio de la interface grafica del usuario. En
cualquiera de los casos, el único requerimiento
de información durante el proceso de la creación
de la base de datos es el nombre de la nueva
base de datos.

Practica 1
Al ejecutar el anterior ejemplo solo creara 2
archivos en la dirección específica para los
archivos de bases de datos, que se configuro
durante la instalación de SQL Server.
La direccion de los archivos es la siguiente:
C:\Program Files\Microsoft SQLServer\
MSSQL10.MSSQLSERVER\MSSQL\DATA\SampleDB.mdf
C:\Program Files\Microsoft SQLServer\
MSSQL10.MSSQLSERVER\MSSQL\DATA\SampleDB_log.ldf
El primer archivo es el de los datos de base de
datos, y el segundo archivo es el archivo de
registro de transacción de la base de datos.
Primeros pasos
Es importante conocer todas las características y
las opciones que están disponibles, en esta
sección se explicaran el proceso de crear una
nueva base de datos en modo gráfico con todas
las opciones
 Creando una nueva base de datos
Crear una base de datos
gráficamente con SQL Server
Management Studio es muy fácil e
intuitivo.

El primer paso es abrir SQL Server

Management Studio desde el menú
Inicio y conectarse al motor de base
de datos de SQL Server.

Una vez dentro, en el explorador de

objetos, dar clic derecho en el nodo
de “Databases” y dar clic en la
opción “New Database”. La
aparecerá la ventana de nueva base
de datos.
Campo: Database name
El nombre de la base de datos. Se tienen que
tener en cuenta las siguientes recomendaciones:
Debemos de recordar que el nombre solo puede
tener un máximo de 128 caracteres.
Puede que las aplicaciones no se conecten si el
nombre no tiene estándares aceptados
Como buena práctica, los nombres deben de ser lo
más descriptivos pero a la vez, lo más corto
posibles.
Los espacios en blanco pueden ser un problema
cuando la base de datos es accedida con frecuencia.
Campo: Owner
El campo Owner por lo general debe especificar SA, que es la
cuenta integrada de SQL Server Administrador del sistema.

Cuando se crea una nueva base de datos en la interfaz

gráfica de usuario, este campo será por defecto el valor de
<default>, que es la cuenta de inicio de sesión que está
llevando a cabo la creación de bases de datos.

El propietario de la base de datos obtiene un control completo

de la base de datos. La propiedad de base de datos pueden
ser modificados por uso de la autorización ALTER T-SQL y
especificar cualquier nombre de usuario válido.

Practica 2
Hay dos maneras de recuperar información acerca
de bases de datos.

El procedimiento almacenado sp_helpdb se puede

utilizar para recuperar información acerca de todas
las bases de datos o una base de datos específica y
es mucho más fácil de usar para una mirada rápida.

Para todas las bases de datos, el procedimiento

almacenado se ejecuta sin parámetros. Para una
base de datos específica, el nombre dela base de
datos se pasa al procedimiento almacenado.

Practica 3
sp_helpdb resultados sin especificar un nombre
de base de datos
sp_helpdb resultados con un nombre de base de datos
Otra forma de ver la información de base de
datos es mediante el uso de las vistas de
catálogo, las cuales fueron introducidas en SQL
Server 2005.

Estas ofrecen más información que sus

contrapartes de procedimientos almacenados y
permite el uso de comandos estándares T-SQL
como WHERE y GROUP BY.

Practica 4
Usando vistas de catálogo para recuperar información de
bases de datos.
Archivos de Base de Datos
 En los archivos de base de datos en la sección del
cuadro de diálogo Nueva base de datos, se muestra
el nombre lógico del primer archivo de datos, así
como el nombre lógico para el primer archivo de
registro se han dado nombres de forma automática.
 El primer archivo de datos tiene el mismo nombre
que la base de datos y el archivo de transacciones se
le da el nombre de la base de datos con _log añadido
al final. Los nombres lógicos se utilizan para referirse
a los archivos de programación en el script T-SQL.
Ejemplo
 Haga clic en el botón Agregar en la parte inferior del
cuadro de diálogo Nueva base de datos. Una nueva
fila de un archivo adicional se agrega en la sección
archivos de base de datos. Los valores
predeterminados de archivo nuevos para el tipo de
archivo de los datos de filas, se pueden cambiar a
cualquiera de registro o de datos FILESTREAM
seleccionándolo en la lista desplegable. Una vez que
la base de datos se crea, el tipo del archivo no puede
ser cambiado.
Para este ejemplo, dejar el tipo de archivo
como Rows Data. Escriba un nombre lógico
para el nuevo archivo de datos y luego en la
columna de grupo de archivos, haga clic en la
lista desplegable y elija <new filegroup>.>. El
nuevo grupo de archivos muestra el cuadro
de diálogo, como se observa en la Figura.
FILEGROUPS
Las Bases de datos son creadas sobre archivos que se organizan
en grupos de archivos.

Los grupos de archivos son una agrupación lógica de archivos de

datos que contiene todos los datos y objetos definidos de la base
de datos.

Los datos están fragmentados a través de grupos de archivos

mediante una estrategia de llenado proporcional. Esto permite que
todos los archivos de datos puedan ser llenados al mismo tiempo.

El grupo de archivos que sólo se requiere es el llamado principal.

El grupo de archivos principal está formado por el archivo de datos
principal y cualquier archivo de datos definido por el usuario.
El propósito del archivo de datos principal es
almacenar todas las referencias del sistema para la
base de datos, incluyendo los punteros a objetos
definidos en la base de datos de recursos.

El grupo de archivos principal contendrá todas las

definiciones de los objetos determinados por el usuario
si se permite que el grupo de archivos por defecto así
como todos los objetos sean creados por el sistema.
En adición al grupo de archivos primario, los grupos de
archivos pueden ser creados por el usuario según sea
necesario.

Una de las mayores ventajas de la utilización de los

grupos de archivos definidos por el usuario se reduce a
una sola palabra: control.
Con grupos de archivos definidos por el usuario, el
administrador de base de datos tiene el control
completo sobre qué datos se almacenan y en qué
ubicación.

Una característica interesante de la utilización de

grupos de archivos es la capacidad de marcar todos
los datos contenidos dentro de ese grupo de archivos
como de sólo lectura.
Esto se puede hacer seleccionando el'' solo lectura'',
en la casilla de diálogo Nuevo grupo de archivos. Esto
puede ser muy ventajoso cuando hay una organización
diferente de los objetos en una base de datos.
¿Mantenimiento o Rendimiento?
Los grupos de archivos ofrecen la posibilidad de
mejorar tanto el rendimiento y la viabilidad de mantener
una base de datos mediante la separación de datos a
través de varios archivos físicos en grupos de tablas.
La ventaja del mantenimiento proviene de la capacidad
de copia de seguridad y restaurar archivos individuales
y grupos de archivos en lugar de copias de seguridad
de bases de datos enteras.
Esta habilidad es útil con grandes bases de datos
separadas en varios grupos de archivos, e incluso más
útil cuando algunos de los grupos de archivos están
marcados como de sólo lectura.
Esta separación y la habilitación de los datos
de solo escritura y solo lectura le permite al
administrador de base de datos crear copias
de seguridad únicamente de los datos que
estén sujetos a modificaciones, esto puede
reducir el tiempo de respaldo o recuperación
de las bases de datos grandes, sin embargo
esta capacidad no viene sin un costo.
Las Ventajas de rendimiento que se entregan
con grupos de archivos se divide
principalmente en tres áreas:
1.-La primera está en paralelo las operaciones de lectura y
escritura que son posibles mediante la separación de los
archivos de datos a través de múltiples dispositivos físicos.
Sin embargo, la ganancia de rendimiento mismo se puede
lograr en un solo grupo de archivos con muchos archivos
físicos.
2- El segundo es la capacidad de mover los índices no
agrupados y de grandes objetos de datos fuera del grupo de
archivos reservados para el espacio de datos regular. La
separación de los índices no agrupados de los datos
permite que el motor de base de datos busque ubicaciones
de fila del índice y recupere las filas de las tablas de manera
simultánea utilizando hilos separados.
3.- La tercera (y más significativa) ventaja de los grupos de
archivos es que permiten la capacidad de partición de
tablas físicamente grandes a través de varios grupos de
archivos.
La mejor manera de mejorar el acceso a los
datos del disco es la implementación de un RAID.
Las principales razones para el uso de grupos de
archivos para los administradores de la mayoría
de bases de datos son el control que ofrece en el
almacenamiento de los datos y la capacidad para
separar los datos del usuario de sistema, lo que
equivale a las preocupaciones del
mantenimiento.
 Tamaño del Archivo
En la columna ''tamaño inicial(MB)”, se debe
asignar un valor en función del tamaño del archivo
que se espera dentro de las primeras semanas(y
quizás incluso meses)de operación.
Como practica recomendada, las modificaciones de
tamaño de archivo deben mantenerse al mínimo.
Asignar suficiente espacio en un disco contiguo
para dar cabida a todos los datos que se esperan
más un porcentaje de espacio para el crecimiento.
Crecimiento automático
Los cambios muestran el dialogo de crecimiento
automático, como se muestra en la Figura.
El cuadro de diálogo Cambiar crecimiento automático
permite la configuración del tamaño máximo de
archivos y la configuración de crecimiento para cada
archivo individual.
Asegúrese de que la casilla crecimiento automático
está marcada. La desactivación de esta casilla
establece la propiedad FILEGROWTH a cero. Para
este ejemplo, usaremos los valores por defecto en el
cuadro de diálogo Cambiar crecimiento automático.
Como práctica recomendada, la opción de
crecimiento automático debe ajustarse a un
incremento lo suficientemente grande como para
reducir al mínimo el número de offile-growths
necesarios para acomodar el crecimiento de datos.
Los archivos con crecimiento en pequeños
incrementos resultan en la fragmentación física de
los archivos, lo cual es perjudicial tanto para los
datos y el rendimiento del archivo de registro.
El tamaño de los datos y archivos de registro puede
ser restringido, lo que permite una forma más de
controlar el tamaño de los archivos. Esto se puede
hacer seleccionando la opción crecimiento del
archivo restringido (MB), botón de opción y la
especificación de un tamaño máximo.
Este tamaño no puede ser superado por el
crecimiento automático o manual de archivos de
operaciones. En general, es una buena práctica
para establecer un tamaño máximo de archivo para
proteger contra cualquier proceso errático que
puede intentar para insertar millones de filas(en
lugar de sólo unos pocos)y también para mantener
el control del crecimiento de la base de datos.
Ruta
Para cambiar la ruta donde los datos y los
archivos de registro se encuentran, haga clic en
el botón de puntos suspensivos a la derecha de
la columna Ruta de acceso en el cuadro de
diálogo Nueva base de datos para cada archivo
de datos y seleccionar una carpeta de destino
para cada archivo individual.
Ahora que todos los ajustes generales de su
nueva base de datos están completos, es el
momento de configurar las opciones de base de
datos.
Opciones de Bases de Datos
Haga click en la
Opción “Select a
page” en la parte
superior izquierda
del cuadro de
dialogo “New
Database”, como se
muestra en la
Figura. La ventana
de Opciones
muestra varias
opciones de
configuración de la
base de datos.
 Collation
Click en la lista
desplegable de
“Collation” y
revise las
diferentes
configuraciones
disponibles, pero
deje el valor
establecido en
<server default>
 Una instancia de SQL Server se le asigna una
intercalación de servidor predeterminada que
determina qué caracteres son compatibles con el
servidor por defecto, y como estos caracteres se
buscan y se ordenan. La configuración de intercalación
también puede ser asignada a la base de datos. Como
resultado, sólo porque una instancia de SQL Server se
ha configurado para utilizar el juego de caracteres
latinos no significa que una base de datos creada para
soportar caracteres coreanos no se puede crear en la
misma instancia. Sin embargo, las incompatibilidades
intercalación en la base de datos tempdb se puede
producir si la configuración de intercalación de base de
datos es diferente de los valores de intercalación de
SQL Server de la instancia.
Modo de Recuperacion
Los modelos disponibles que pueden ser
establecidos son :
“Full”
“Bulk-Logged”
 “Simple”.

Si la base de datos model no ha sido establecida, el

modelo de recuperación por default será “full”.
El modelo “Full” se entiende sólo como un accesorio
para el modelo de recuperación completo para su uso
durante las operaciones masivas. Esto porque el
modelo ”Full” registra todas las modificaciones en la
base de datos. Debido a que todas las modificaciones
de una base de datos se registran por completo, el
registro de transacciones puede crecer muy
rápidamente en gran tamaño durante ciertas
operaciones
El modelo “Bulk-Logged” fue desarrollado para pueda
hacer varias cargas masivas durante algunas
operaciones que podría causar que el archivo de
transacciones también crezca de manera rápida.
En el modelo “Simple”, el archivo de transacciones es
limpiado de todo el contenido inactivo cada vez que es
emitido.
Nivel de Compatibilidad
Haga clic en la lista desplegable “nivel de
compatibilidad” y revisarlas opciones posibles. A
menos que tenga razones específicas para
cambiar el nivel de compatibilidad, debe
establecerá SQL Server2008 (100).
La opción de nivel de compatibilidad cambia el
comportamiento de algunas operaciones de base
de datos y sólo es necesario si una instancia de
SQL Server 2008compartelas funciones de base
de datos con una versión anterior de SQL Server.
SQL Server 2008 sólo permite la selección de los
niveles de compatibilidad de 80, 90 y 100,que
como se indica en la lista desplegable, se
correlaciona con SQL Server 2000, SQL Server
2005 y SQL Server 2008, respectivamente.
El siguiente código establece el nivel de
compatibilidad de la base de datos
AdventureWorks2008deSQL Server 2000:

Practica 5
Otras Opciones
Por defecto la sección “otras opciones” en la
pantalla de una nueva base de datos organiza las
opciones de manera categórica. Para efectos de
este debate, vamos a ordenarlas opciones en
orden alfabético. Cuando este sea el caso, los
comandos para configurar la opción de nivel de
conexión y la opción de configuración de base de
datos se mostraran a la vez.

Es importante conocer las opciones de nivel de

conexión, si se especifica, se remplazaran las
opciones de nivel de base de datos. Cuando no se
especifica, la opción de base de datos estará
activada.
Haga clic en el botón de ordenación alfabético, que
puede ser identificado por una A y Z uno con una
flecha vertical hacia abajo. Las opciones disponibles
están ahora en la lista por orden alfabético.
ANSI NULL default
La configuración ANSI NULL DEFAULT específica
si el valor predeterminado para las columnas
agregadas a una tabla durante una operación
CREATE TABLE o ALTER TABLE permite nulos o
no. Cuando ANSI NULL está establecida en False,
las columnas agregadas no permite nulos a menos
que explícitamente se especifique para hacerlo.
Cuando se conecta a SQL Server con SQL Server
Management Studio, la configuración por defecto
de conexión para nuevas consultas es ANSI NULLS
ON, lo que anula la configuración de base de datos.
Para su instalación en el nivel de conexión o el
nivel de base de datos, los comandos se utilizan
los siguientes…

Practica 6
ANSI NULLS Enabled
 La configuración ‘‘ANSI NULLS Enabled’’ controla el
comportamiento de las comparaciones con valores
NULL.
Cuando se establece en TRUE, cualquier
comparación para un valor NULL tiene como
resultado desconocido.
Cuando se establece en FALSE, las
comparaciones con NULL devolverá True si los
valores son nulos.
Para su instalación en el nivel de conexión o el
nivel de base de datos, se utilizan los siguientes
comandos…
Practica 7
ANSI Padding Enabled
Cuando se configure en TRUE, “ANSI Padding
Enabled” dicta que los espacios finales de los datos
de caracteres y los ceros finales para los datos
binarios se anexan al final de las columnas de
caracteres y valores binarios que son de longitud fija.
Las columnas de caracteres y binarios que son de
longitud variable no se rellenan, y los espacios finales
o ceros finales no se recortan. Cuando se establece
en FALSE, las columnas de caracteres y binarios que
son de longitud fija y un conjunto NOT NULL se
comportan de la misma forma que cuando ANSI
Padding Enabled es true.
Sin embargo, columnas con valores nulos de
caracteres y binarios que son de longitud fija no
se rellenan, y los espacios finales o ceros finales
se recortan.
Las Columnas de longitud variable se comportan
como columnas de longitud fija que aceptan
valores NULL cuando “ANSI Padding Enabled” es
FALSE.
Para su instalación en el nivel de conexión o el
nivel de base de datos, se utilizan los siguientes
comandos…
Practica 8
ANSI Warnings Enabled
Cuando “ANSI Warnings Enabled” se establece
en TRUE, las advertencias incrementaran en el
motor de la base de datos cada vez que una
función de agregado se encuentre con un valor
NULL. Cuando se establece en False, no se
emiten advertencias.
Para su instalación en el nivel de conexión o el
nivel de base de datos, se utilizan los siguientes
comandos…
Practica 9
Arithmetic Abort Eneabled
Cualquier declaración o transacción que se
encuentra con un desbordamiento aritmético o error
de división por cero terminará cuando la Arithmetic
Abort Enabled se establece en True.
Cuando se establece en False, una advertencia se
muestra, pero la declaración o la transacción no se
dará por terminado. Para que esta opción tenga el
efecto deseado,Arithmetic Abort Enabled se debe
establecer en False.
Para su instalación en el nivel de conexión o el nivel
de base de datos, se utilizan los siguientes
comandos…
Practica 10
Auto Close
Cuando una base de datos es accedida por primera vez,
SQL Server abre y cierra todos los archivos que están
asociados con la base de datos.
Cuando “Auto Close” es TRUE, la base de datos será
cerrada, liberando a todos los archivos de los bloqueos,
cuando el último usuario conectado cierra la conexión.
Esta opción está desactivada por defecto porque el acto
de apertura y cierre de la base de datos en una
plataforma de servidores innecesaria y produce gastos
innecesarios.
La propiedad se establece en OFF por default por el
hecho de abrir y cerrar una base de datos en una
plataforma de servidores es innecesario y produce
sobrecarga.
Para su instalación en el nivel de base de datos,
se utilizan los siguientes comandos…
Practica 11
Auto Create Statics
Cuando Auto Create Statistics está configurado en TRUE, el
motor de base de datos va a generar las estadísticas de las
columnas sin índices, y cuando las columnas hagan referencia a
una clausula WHERE, o la clausula ON a una operación JOIN.
Las estadísticas utilizan el motor de la base de datos para
determinar la selectividad y la distribución de datos en una
columna. Si se establece en FALSE, el administrador de la base
de datos deberá crear estadísticas manuales cada que las
necesite.
Para su instalación en el nivel de base de datos, se utilizan los
siguientes comandos…

Practica 12
Auto Shrink
Cuando Auto Shrink está configurado en TRUE, el motor de
la base de datos examinara periódicamente el tamaño de
todos los archivos de la base de datos y comparara la
cantidad de datos almacenados.
Si hay más de 25% total libre en el espacio restante, el motor
de la base de datos realizara operaciones para reducir los
ficheros en la base de datos para reducir a un total del 25 %
del espacio total, el motor, esta opción está establecida en
FALSE de manera predeterminada, a excepción de SQL
Server Express Edición.

Se utilizan los siguientes comandos…

Practica 13
Auto Update Statistics
Cuando Auto Update Statistics está configurado en
TRUE, el motor de la base de datos actualiza
automáticamente la información estadística sobre las
columnas para mantener los planes de consulta lo
más eficientes posibles.
Esto normalmente ocurre cuando una consulta es
ejecutada y el procesador de consultas descubre
estadísticas fuera de fecha.
Si se establece en False, el administrador de la base
de datos tendrá que mantener las estadísticas de las
columnas de manera manual, se utilizan los
siguientes comandos…
Practica 14
Auto Update Statistics
Asynchronously
Cuando Auto Update Statistics Asynchronously se
establece en TRUE, las estadísticas que se descubren
fuera de fecha serán actualizadas durante la consulta,
pero la consulta que se estaba ejecutando cuando se
descubrió no va a esperar las nuevas estadísticas.
Las consultas posteriores tomaran ventaja de las
nuevas estadísticas. Cuando se establece en FALSE,
la compilación de la consulta no se producirá hasta
después de que las estadísticas se actualicen.
Se utilizan los siguientes comandos…
Practica 15
Broker Enabled
Cuando Broker Enabled está establecido es
TRUE, la base de datos se configura para la
participación en un Sistema de Mensajería
Service Broker*.
Cuando esta opción es habilitada en una nueva
base de datos, un identificador de Service Broker
es creado y se conserva en la base de datos.
Si Service Broker está deshabilitado y luego se
vuelve a habilitar, el identificador original será
utilizado.
Practica 16
Close Cursor on Commit Enabled
Cuando Close Cursor on Commit Enabled está establecido en:
 TRUE, los cursores contenidos en una transacción se cerraran
después de que la transacción sea confirmada o revertida.
 False, los cursores permanecen abiertos una vez que se confirma
la transacción.

Sin embargo, si se revierte una transacción se cerrará todos

los cursores excepto los definidos como INSENSITIVE o
STATIC.

Para su instalación en el nivel de conexión o el nivel de base

de datos se utilizan los siguientes Comandos:

Practica 17
Concatenate Null Yields Null
Cuando una cadena de caracteres se vincula con un
NULL, este devolverá NULL cuando el
“Concatenate Null Yields Null” se encuentre en
TRUE. Cuando se encuentra en FALSE, una
cadena de caracteres se vincula con un NULL
devolverá la cadena de caracteres.

Para su instalación en el nivel de conexión o nivel

de base de datos, se utilizan los siguientes
comandos:

Practica 18
Cross-database Ownership Chaining
Enabled
El “Cross-database Ownership Chaining Enabled” la
opción no está bien definida en el dialogo de
opciones solo indica el valor que se establece.
Cuando se encuentra en True, indica que la base de
datos puede contribuir en Cross-database
Ownership Chaining Enabled.
Esta opción solo se reconoce si la opción del nivel
de servidor esta desactivado.
Para su instalación en el nivel de conexión o nivel de
base de datos, se utilizan los siguientes comandos…
Practica 19
DataBase Read-Only
La base de datos de sólo lectura es una opción que
especifica que no se admiten modificaciones a la
base de datos cuando este se encuentra en TRUE.
Un acceso exclusivo a la base de datos es
necesario para establecer esta opción, a excepción
de la base de datos master.
 Para configurar en nivel de base de datos se usan
los siguientes comandos:

Practica 20
DataBase State
Los estados de la base de datos, de esta opción
no se puede configurar en la interfaz gráfica, y,
en su mayor parte, no es directamente
configurable en absoluto.
La alteración es el ONLINE, OFFLINE, y estados
de emergencia. Los estados de la base de datos
indican valores diferentes en función de lo que
está ocurriendo en la base de datos.
En la siguiente tabla se describen los diferentes
estados en los que puede estar la base de datos:
Estado Descripción
ONLINE La base de datos está en línea y disponible, también puede
mostrar estado NORMAL.
OFFLINE La base de datos no está disponible. La base de datos es
establecida OFFLINE cuando ejecutamos el comando ALTER
DATABASE <DBName> SET OFFLINE. Esto se puede dar
cuando el DBA necesite mover un archivo de la base de datos de
un lugar a otro.
RESTORING Uno o más archivos están siendo restablecidos. La base de datos
no está disponible.
RECOVERING La base de datos está siendo recuperada. Una excepción se da
cuando la base de datos esta espejeada.
RECOVERY Una base de datos estará en este estado si SQL encuentra algún
PENDING error durante el proceso de recuperación. La base de datos no
estará disponible hasta que el DBA resuelva el error.
SUSPECT Uno o más archivos de la base de datos son marcados como
sospechoso por acceso a datos o un error de lectura. Cuando la
base de datos esta en este estado, no estará disponible hasta que
se corrija el problema
EMERGENCY La base de datos estará en este estado cuando el DBA establezca
este estatus. En este estado, la base de datos está en modo usuario
único, y pueda ser reparada o restaurada, si la base de datos está
marcada como sospechosa, este es el primer paso para corregir el
problema.
Date Correlation Optimized
Enabled
Cuando “Date Correlation Optimization Enabled”
se encuentra en modo TRUE, indica que el motor
de la base de datos mantiene actualizadas sus
estadísticas entre dos tablas que se unen en la
columna datetime por una restricción de clave
externa para optimizar consultas entre las dos
tablas donde el campo datetime es un filtro.
Para configurar en nivel de base de datos, usar
los siguientes comandos…

Practica 21
Default Cursor
A diferencia de variables locales y globales, cuyo
alcance se basa en las conexiones, los cursores son
siempre locales con respeto a la conexión en la que se
declaran. Cuando “Default Cursor” se establece en
global, se especifica que un cursor declarado puede
hacer referencia a cualquier proceso por lotes,
procedimiento almacenado o desencadenador que se
ejecute en la misma conexión. Si se establece en modo
Local, el cursor sólo puede hacer referencia a un lugar
dentro del lote, de procedimiento almacenado, o
desencadenador en que se declaró el cursor.
Para su instalación en el nivel de base de datos, se
utilizan los siguientes comandos…
Practica 22
Encryption Enabled
Cuando “Encryption Enabled” se encuentra en
modo True, todos los datos y archivos de registro
serán encriptados. Si una clave de cifrado de
base aún no ha sido creada, al tratar de
establecer esta opción se producirá un error.
Para fijarlo en el nivel de base, se utilizan los
comandos siguientes…

Practica 23
Honor Broker Priority
La opción “Honor Broker Priority” no se puede
configurar en SQL Server Management Studio y
debe ser cambiado a través de script T-SQL.
Cuando esta opción está activada.
Para su instalación en el nivel de base de datos,
los comandos que se utilizan son los siguientes…

Practica 24
Numeric Roud-Abort
Cuando “Numeric Roud-Abort” se encuentra en
True, significa que por cualquier redondeo
numérico que se produce se generará un error.
Por ejemplo, si “Numeric Roud-Abort” se
establece en True, se genera un error como se
muestra en el siguiente código…

Practica 24.1
El error se produce porque la variable decimal
fue declarada con una escala de 3. Recuerde
que la escala especifica cuántos dígitos se
encuentran a la derecha del punto decimal. Para
realizar este cálculo, SQL Server debe redondear
el número.
Cuando“Numeric Roud-Abort” se encuentra en
False, este código tendrá éxito…

Practica 24.2
Para su instalación en el nivel de conexión o el
nivel de base de datos, los comandos se utilizan
los siguientes…

Practica 25
Page Verify
La opción “Page Verify” permite al administrador
de base de datos establecer diferentes opciones
para verificación de una página.
Las opciones disponibles son:
Checksum
TORN_PAGE_DETECTION
Ninguno
En cuanto al rendimiento, la mejor opción es
Ninguno. Sin embargo, con Ninguno, las páginas
pueden salir dañadas durante las operaciones de
escritura en disco y no podrán ser descubiertos.
Con la opción de comprobación, SQL Server
calcula un valor de suma de comprobación y lo
almacenan en el encabezado de página. Este
valor de la suma es muy parecida a la
comprobación de redundancia cíclica (CRC) los
valores que se crean cuando los archivos
se escriben en el disco por el sistema operativo.
Cuando una página lee los datos desde el disco,
SQL Server vuelve a calcular la suma de
comprobación y compararla con la almacenada en
el encabezado de página.
 Si los valores coinciden, la página está bien.
 Si los valores no coinciden, la página se considera
dañada, un error 823 se incrementará, y el estado de
la base de datos se cambia de LÍNEA a sospechar
La opción configura TORN_PAGE_DETECTION SQL
Server sirve para escribir un bit de error en el
encabezado de página al final de cada ciclo de
escritura. Si el error está ausente cuando la página se
lee más adelante, un error 823 se eleva, y el estado
de la base de datos se cambia de LÍNEA a sospechar.
Cuando SQL Server genera un error 823, un registro
se añadirá a la tabla suspect_pages en msdb base de
datos.
El registro incluye la base de datos en donde se
produjo el error en el ID de la página, ID de archivo, y
varios otros piezas de información que será útil para
restaurar la página de una copia de seguridad. Esta
tabla se actualizará cuando la página se restaura,
pero los registros no se eliminará.
Page Verify - Checksum
Con la opción “Cheksum”, SQL server calculara
el valor de la suma de verificación y lo guardara
en el encabezado de la página. Cuando la página
de datos es leída, SQL server recalculara la
suma de verificación y la comparara contra la que
se encuentra en el encabezado de la página.
Si los valores concuerdan, la página está bien. Si
los valores no concuerdan, la pagina es
considerada corrupta, un error 823 será lanzado,
y la base de datos pasara del estatus ONLINe a
SUSPECT.
Torn Page Detection
La opción “Torn_Page_Detection” configura SQL
server para escribir un bit de error en el
encabezado de la página al final de cada ciclo. Si
el bit de error está ausente cuando la página es
leída después, lanzara el error 823, y la base de
datos cambiara su estatus de ONLINE a
SUSPECT.

Para su instalación en el nivel de base de datos,

los comandos que se utilizan son los siguientes…
Practica 26
Parameterization
Parameterization es una opción muy interesante,
pero avanzó desde que se introdujo en SQL
Server 2005. De manera predeterminada, el
motor de base de datos auto-parametriza
algunas consultas para los planes de consulta
que se crean y compilado puede ser reutilizado,
incluso cuando los diferentes valores se definen
en la cláusula WHERE. Por ejemplo, considerar
el siguiente código…
Practica 27
Para su instalación en el nivel de base de datos,
los comandos se utilizan los siguientes…
Practica 28
Quoted Identifiers Enabled
De forma predeterminada, SQL Server utiliza
corchetes (''[]'') para delimitar los objetos.
La Delimitación de Objetos sólo es necesario si el
nombre del objeto contiene un espacio incrustado
o una palabra reservada. El delimitador estándar
ANSI es las comillas dobles.
Los siguientes ejemplos muestran cómo crear y
hacer referencia a un objeto con un espacio
integrado con las dos entre corchetes y comillas
dobles:
El siguiente es un ejemplo de un delimitador
ANSI:
Practica 28.1

El siguiente es un ejemplo de un delimitador por

default:

Practica 28.2
Cuando la opción “Quoted Identifiers” está
establecida en:
 True los corches y comillas son aceptados;
 False solo los delimitadores de corchetes, serán
aceptados.

Para establecer esta opción en el nivel de la

conexión o el nivel de la base de datos, se
utilizan los siguientes comandos…
Practica 29
Recursive Triggers Enabled
Los Triggers recursivos se consideran una
técnica de programación avanzada que permite
que el mismo disparador; disparar más de una
vez en secuencia, en la misma transacción.
Cuando se establece en False, esta acción no
está permitida y es la configuración
predeterminada.
Por lo general, es una buena idea para salir de
este conjunto en False. La lógica recursiva es
difícil en el mejor de depurar y puede llevar a
muchos dolores de cabeza.
Casi todo el tiempo, la lógica recursiva puede
reescribirse como no-recursivo lógica. Para su
instalación en el nivel de base de datos, se
utilizan los siguientes los comandos:

Practica 30
Restrict Access
La opción “Restrict Access” opción permite al
administrador de base de datos restringir el acceso a
una base de datos o a un conjunto definido de inicios
de sesión. El valor predeterminado de esta opción es
MULTI_USER, que permite a múltiples usuarios sin
privilegios para acceder a la base de datos. Las
otras dos opciones existen para restringir el acceso:
SINGLE_USER and RESTRICTED_USER.
Cuando la opción “SINGLE USER” esta activada,
sólo una cuenta de usuario se permite el acceso a la
base de datos a la vez.
Cuando la Opción “RESTRICTED USER” está
habilitada, sólo los miembros de las funciones
db_owner,dbcreator o el administrador del
sistema, puede conectarse a la base de datos.
Para su instalación en el nivel de base de datos,
se utilizan los siguientes comandos:
Practica 31
Service Broker Identifier
La Opción “Service Broker Identifier” no es
configurable en el SQL Server Management
studio y no se puede establecer directamente.
El identificador de Service Broker se crea la
primera vez que se habilita la base de datos para
utilizar Service Broker y se utiliza para identificar
de forma únical a base de datos en una
infraestructura de mensajería.
Trustworthy
“Trustworthy” no puede ser establecido a través de SQL
Server Management Studio.
La opción “TRUSTWORTHY” indica si la instancia de SQL
Server confía en la base de datos para acceder a recursos
externos o de la red. Si se establece en False, no se les
permite el acceso a cualquier recurso externo a la base de
datos a los componentes de base de datos de
programación creado con código administrado, o los
componentes de base de datos que necesitan para
ejecutar en el contexto de un usuario con muchos
privilegios,
Cuando una de estas dos situaciones es requerida, la
opción “Trustworthy” puede ser establecida en True.
Para su configuración a nivel de base de datos,
se utilizan los siguientes comandos:

Practica 32
VarDecimal Storage Format Enabled
Esta característica fue introducida por primera
vez en el Service Pack 2 para SQL Server2005 y
ahora está en desuso en SQL Server 2008
Para SQL Server 2008, se enceuntra encendido
y no se puede apagar.
Generando Scripts de
Creación de Base de datos
Ahora que ha pasado por todos los pasos y las
opciones de creación de una base de datos,
vamos a echar un vistazo a cómo puede crear un
script.
En el borde superior de la pantalla se encuentra
un botón llamado “script”.
Al hacer clic en la flecha que se encuentra a un
lado, desplegara todas las opciones disponibles
para crear scripts. Este script puede ser usado
para para crear una nueva base de datos con las
mismas opciones simplemente cambiando el
nombre lógico y físico de la base de datos y los
archivos asociados.
El siguiente ejemplo muestra cómo utilizar SQL
dinámico para crear una nueva base de datos
con un grupo de archivos definido por el usuario
marcado como por defecto…
Practica 33
Schema(Esquema)
Un esquema es simplemente una manera de
organizar tus objetos de la base de datos y
asignar permisos a los objetos que contiene.
 El esquema mismo puede ser propiedad de
cualquier entidad de la base de datos,
incluyendo funciones de la base de datos y
funciones de aplicación mientras se contienen
muchos objetos pertenecientes a varios
usuarios.
 Dentro de un esquema, los objetos no pueden
tener nombres duplicados, pero pueden tener
el mismo nombre si pertenecen a un esquema
distinto.
 Por ejemplo, si una tabla llamada “Inventory” es
creada en el esquema “Sales” en el servidor
“AughtEight” su nombre se volvería:

AughtEight.Sales.Inventory

 Una tabla adicional llamada “Inventory” puede seguir

siendo creada en el esquema “Marketing”, y su
nombre sería:

AughtEight.Marketing.Inventory
Aunque esto sea posible no es recomendable
hacerlo ya que puede llevar a la confusión para
alguien nuevo a la base de datos y puede
producir resultados inesperados en las consultas
más adelante.
Donde los esquemas realmente se vuelven útiles
es en la capacidad de formar ámbitos de
seguridad que pueden ser usados por el DBA
para controlar el acceso a todos los objetos del
esquema.
En SQL Server 2008, una entidad de la base de
datos es asignada como propiedad de un
esquema, y dicho esquema posee los objetos que
la componen, tales como tablas, vistas,
procedimientos almacenados y funciones.
Si un usuario que posee un esquema
necesita ser eliminado, la propiedad de ese
esquema necesita ser asignado a otro usuario
primero.
La manera más fácil es hacer que el usuario
dbo posea todos los esquemas.
El usuario dbo es un usuario incluido que se
le asigna a cualquier miembro del papel de
servidor fijo sysadmin.
El usuario dbo siempre existe y no puede ser
eliminado, así que es el candidato para
asignarle la propiedad del esquema.
Esquemas y resolución de nombres
Como los esquemas solamente son
contenedores para objetos, es importante
establecer el contexto de las referencias de
objeto cuando llaman a objetos de la base de
datos.
Cada usuario tiene un esquema
predeterminado.
Cuando el usuario se loguea en el servidor y
manda llamar objetos de la base de datos, el
esquema por defecto jugará un papel distinto
en cómo los objetos deben ser referenciados.
Por ejemplo, suponga que el usuario FredF
es creado en la base de datos
AdventureWorks2008 y se le asigna como
esquema por defecto de Sales.

*Si el usuario FredF ejecuta la consulta

SELECT * FROM CreditCard

la tabla CreditCard será resuelta como

AdventureWorks2008.Sales.CreditCard
Si el mismo usuario ejecuta la consulta

SELECT * FROM Person

La tabla Person se resolverá como

AdventureWorks2008.Sales.Person (no existe)

Como SQL Server 2008 es incapaz de encontrar la tabla

Person en el esquema por defecto de FredF, cambiará
el esquema por defecto a dbo y buscará la tabla

AdventureWorks2008.dbo.Person (tampoco existe)

SQL Server regresará el error: "Invalid object name".

Creación de un esquema
Para crear un esquema la única información requerida es el
nombre del esquema.

La propiedad del esquema se asigna por defecto al usuario que

ejecutó el script de creación, pero cualquier usuario válido de
la base de datos puede ser asignado como propietario.

CREATE SCHEMA Schema_Name [ AUTHORIZATION owner ]

Ejemplo:
USE AdventureWorks2008
GO
CREATE SCHEMA Operations AUTHORIZATION dbo

Practica 33.2
Cualquier declaración en el ámbito del
esquema que siga después de la declaración
CREATE SCHEMA caerá dentro del alcance
del esquema recién creado, como en el
siguiente ejemplo…

Practica 33.3
Incluso aunque el esquema no fue especificado en la
declaración CREATE TABLE, el script coloca la tabla
DeliveryDriver dentro del esquema Operations.

Incluso si la declaración GRANT SELECT se ejecuta

con éxito, a pesar de que el esquema no fue
designado en la sentencia, se le asigna por defecto el
esquema Operations, porque la sentencia CREATE
SCHEMA establece el alcance del esquema para
todas las declaraciones restantes en el lote.

Si el script se cambia ligeramente para que la

sentencia GRANT SELECT esté en un lote diferente,
la sentencia fallará.
Practica 33.4
 La palabra clave GO coloca la sentencia GRANT
SELECT fuera del lote creado por el esquema, y por
lo tanto el contexto de ejecución se revierte al del
usuario que ejecuta el script.

 Como buena práctica, el esquema de un objeto

debería especificarse siempre para evitar resultados
inesperados.

GRANT SELECT ON Operations.DeliveryDriver TO

FredF
Mantenimiento de Esquemas
Como precaución, si tratas de eliminar un esquema que
contiene objetos, un error se generará y se mostrará
como en el siguiente ejemplo:

DROP SCHEMA Operations

----------------------------------------------------
Msg 3729, Level 16, State 1, Line 1
Cannot drop schema ‘Operations’ because it is being
referenced by object ’DeliveryDriver’.
ALTER SCHEMA Production TRANSFER Operations.DeliveryDriver

con este transfiere el control del objeto para poder eliminar el esquema
con la consulta de arriba
Si el objeto de un esquema sigue siendo requerido, puede
ser transferido a un esquema diferente con la sentencia
ALTER SCHEMA:

ALTER SCHEMA Production TRANSFER Operations.DeliveryDriver

En este ejemplo se modifica el esquema Production

moviendo la tabla DeliveryDriver del esquema Operations al
esquema Production.

Como DeliveryDriver era el último objeto en el esquema,

ahora puede ser eliminado. Hay que tener en cuenta sin
embargo, que al transferir un objeto de un esquema a otro
limpia cualquier permiso establecido en el objeto.

Un usuario que posee un esquema no puede ser eliminado

de la BD, la cual es una de las razones por la que puedas
decidir hacer que el usuario dbo posea todos los esquemas.
Para cambiar la propiedad de un esquema, la
propiedad AUTHORIZATION del esquema es
alterada.

El siguiente ejemplo cambia la propiedad del

esquema Operations al usuario FredF…

Practica 34
Tablas
SQL Server 2008, como todos los sistemas
administradores de bases de datos relacionales,
almacena la información en objetos llamados tablas.

Se asume que ya estamos familiarizados con los

conceptos de las bases de datos relacionales, así
que no se gastará mucho tiempo explicando como
se crean las tablas.

Lo importante para un administrador de bases de

datos de SQL Server 2008 es como mantener y
proteger las tablas para optimizar el rendimiento y la
seguridad de la base de datos.
Cotejamietno de tablas (Table
collation)
Cuando se crea una base de datos, el soporte de
cotejamiento puede ser configurado para que sea
diferente al del servidor. Esto también es cierto para las
columnas de las tablas que contienen datos caracteres.

Cada columna puede ser definida con una configuración

diferente de cotejamiento .

Por ejemplo, la compañía Cycles de AdventureWorks

quiere habilitar a clientes de todo el mundo para que
naveguen y busquen el catálogo de productos en sus
propios idiomas.
Para habilitar esta funcionalidad, una tabla Global
Product Description es construida con el
siguiente script:

Practica 35
Cada columna está ahora ordenada y se puede
consultar utilizando la configuración de cotejamiento
del idioma nativo como se definió en los
requerimientos del negocio.

Sin embargo no hay que malentender esta parte:

 SQL Server 2008 no es un traductor universal.
 SQL Server solamente provee el framework para
almacenar múltiples lenguajes.
Se tendrá que hacer la traducción correcta de las
descripciones y colocarlas en las columnas
apropiadas, y manejar cualquier incompatibilidad de
cotejamiento que surja a causa del cotejamiento de
tempdb.
Arquitectura de las tablas
Como se discutió en el capítulo 4;
 *SQL Server utiliza páginas de datos de 8 KB
para almacenar información.
Toda la información dentro de la tabla es
almacenada dentro de estas páginas, pero cómo la
información en las páginas es organizada diferirá
dependiendo de cómo crees la tabla y lo que hagas
con ella después de crearla.

Por defecto, toda la información se almacenará de

un modo desorganizado, formalmente llamado
montón (heap).
SQL Server 2008 no hace el intento de mantener
la información organizada u ordenada en ninguna
forma y no mantiene enlaces entre las páginas.
El siguiente código crea una tabla que se
almacena de tal forma…

Practica 36
Particiones de tablas
SQL Server físicamente almacena todas las páginas de
datos en unidades lógicas llamadas particiones.
A menos que se separen específicamente, las tablas
son almacenadas en una sola partición definida en un
solo grupo de archivos (filegroup).
Sin embargo, SQL Server proporciona la posibilidad
para separar tablas grandes en unidades más
pequeñas y manejables mediante la partición horizontal
de las tablas a través de múltiples archivos gestionados
por las definiciones de grupos de archivos.
La característica de particionar las tablas está
disponible solamente en las ediciones Enterprise y
Developer de SQL Server 2008.
Por ejemplo, una tabla de transacciones con
millones de filas puede ser físicamente
particionadas de manera que todas las
transacciones para el año en curso son
separadas de aquellas de año anteriores. De
esta forma, solo un subconjunto de la tabla
necesitará ser escaneada para consultar, insertar
o actualizar las transacciones para el año
presente.

Practica 37
 Para poblar la tabla recién creada, inserta todas las
filas de las tablas TransactionHistory y
TransactionHistoryArchive usando el operador
UNION:
Practica 38
Ahora que ya tenemos una tabla de buen tamaño,
ejecuta la siguiente consulta para ver el rendimiento
antes de la partición. La tabla contiene un total de
202,696 filas. De las filas de transacciones en la tabla,
12,711 tomaron lugar en el año 2001, 38,300 en 2002,
81,086 en 2003 y 70,599 tomaron lugar en 2004.
Practica 39
El script utiliza el comando DBCC DROPCLEANBUFFERS
para limpiar todas las páginas del buffer caché. Esto nos
permitirá ver cuántas lecturas son requeridas físicamente
para traer toda la información necesaria a la memoria.
También activa el reporteo de estadísticas con la opción
SET STATISTICS IO ON y luego consulta la tabla
dbo.Transaction para regresar el total del monto de ventas
y la cantidad de productos vendidos en 2002.

El resultado de la consulta es el siguiente:

TotalQuantity TotalCost
------------- ---------------------
1472494 16427929.3028

(1 row(s) affected)
Table ‘Transactions’. Scan count 1, logical reads 1408, physical reads 26, readahead
reads 1407, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Como se puede apreciar, para satisfacer la consulta,
SQL Server tuvo que escanear la tabla. Para
encontrar las 38,800 filas que satisfacen el criterio de
la cláusula WHERE, SQL Server tuvo que buscar a
través de 202,696 filas. Esta búsqueda resultó en
1,408 lecturas físicas.
Ahora vamos a ver qué pasa cuando dividimos
físicamente la tabla en múltiples archivos partiendo la
tabla de tal manera que todas las transacciones sean
divididas por año.
En este caso se ha decidido perticionar físicamente la
tabla Transaction después de haber sido construida.
Desde que la información es almacenada en el
montón, estamos forzados a crear una nueva tabla
particionada y mover la información a ella, y luego
eliminar la tabla original.
El primer paso en la partición de la tabla es la creación de los
grupos de archivos que mantendrán los archivos de datos para ser
usados para almacenar las particiones de la tabla.

Hay que recordar que las tablas no pueden ser asignadas a un

archivo en particular, únicamente a un grupo de archivos. En este
ejemplo, cada grupo de archivos contendrá solamente un archivo,
pero esto no es un requisito, ya que las particiones pueden ser
definidas para existir en un solo archivo o en múltiples archivos.

El siguiente script cuatro nuevos grupos de archivos con un archivo

por grupo de archivos para contener la tabla de transacciones
particionada. La tabla Transaction será particionada por fecha:

Nota: este script asume la presencia de una unidad “E” y una

carpeta SQLData, para que funcione, tienes que cambiar la letra de
la unidad de asignación
Practica 40
El siguiente paso es crear una función de
partición. Las funciones de partición determinan
los límites para cada partición. Se debe
especificar con qué tipo de datos trabajará la
función durante su creación. Todos los tipos de
datos son válidos, con la excepción de los tipos
de datos alias, tipos CLR, o cualquiera de los
siguientes: text, ntext, image, xml, timestamp,
varchar(max), nvarchar(max) o varbinary(max).
Por ejemplo, la función de partición especificará lo que
son los rangos de valores. (como 1 hasta 100,000,
100,001 hasta 1,000,000, etc.). Hay que tener en
cuenta que cuando se específica una función de
partición, solamente se puede particionar sobre un solo
valor.
En este ejemplo, toda la información es particionada
por fecha a fin de agrupar la información de acuerdo a
las consultas más frecuentes que se ejecutan.
La siguiente función de partición particionará la tabla en
cuatro grupos de registros fechados. El primer grupo va
desde NULL hasta 12/31/2001. el segundo de 1/1/2002
hasta 12/31/2002. el tercero va desde 1/1/2003 hasta
12/31/2003. y el cuatro de 1/1/2004 hasta el infinito.
Practica 41
Cuando se define el esquema de partición, debes
especificar el mismo número de grupos de archivos como
particiones fueron definidas por la función de partición.
Practica 42
Si se quisiera particionar la tabla pero almacenar todas
las particiones en el mismo grupo de archivos, hay dos
alternativas: se puede repetir el nombre del grupo de
archivos para cada partición o usar la opción ALL TO con
un solo grupo de archivos, por ejemplo ALL TO
([PRIMARY]).
Lo único que falta hacer ahora es crear la tabla
particionada y mover la información desde la tabla
original Transactions en ella:
Practica 43
Cuando se crean funciones de partición y esquemas de
partición, hay que recordar que pueden ser usados
para particionar tantas tablas como se necesite.
YearFunction y YearScheme pueden ser usados
para particionar cualquier tabla en la base de datos
AdventureWorks2008 que tenga una columna de tipo
datetime en ella.

Para ver si el resultado de la consulta se ha

mejorado, hay que ejecutar la misma consulta:
Practica 44
Ahora que la tabla está particionada físicamente, las
lecturas físicas requeridas para obtener el resultado han
ido de 1,408 a 266. el decremento del costo de entrada-
salida causará también el decremento de uso de CPU
resultando en una consulta mucho más eficiente.
 La característica de partición de tablas está presente
desde SQL Server 2005, pero solamente se podía hacer
a través de código (como acabamos de hacer).
 Las ventajas de SQL Server 2008 es que se puede
usar la GUI de SQL Server Management Studio para
crear tablas particionadas y además de crear el script
para ello, y así poder ejecutarlo en cualquier servidor.
Otra ventaja es que se puede realizar la operación en
lugar de crear una nueva tabla y mover los datos en ella.
El siguiente script nos ayudará borrar todo lo que
hicimos anteriormente:

IF EXISTS (SELECT * FROM sys.tables WHERE object_id =

OBJECT_ID(’dbo.PartitionedTransactions’))
DROP TABLE dbo.PartitionedTransactions

IF EXISTS(SELECT * FROM sys.partition_schemes WHERE Name = ‘YearScheme’)

DROP PARTITION SCHEME YearScheme

IF EXISTS (SELECT * FROM sys.partition_functions WHERE Name = ‘YearFunction’)

DROP PARTITION FUNCTION YearFunction
Ejemplo:

Vamos a ensayar el proceso para particionar la tabla

Production.TransactionHistory de la base de datos
AdventureWorks2008 mediante el SQL Management Studio.

1.- Dar clic derecho en la tabla que se desea particionar,

elegir Storage, y luego “Create Partition”.

2.- Use la página “Select Partitioning Column” para identificar la

columna sobre la cual se va a particionar la tabla. El asistente
solamente mostrará las columnas válidas según el tipo de dato. En
este caso, elegiremos la columna TransactionDate.
3.- El siguiente paso es seleccionar la función de partición, se puede
seleccionar una función existente pero solo será posible
seleccionarla si es compatible con la columna que se seleccionó
anteriormente. Si no hay funciones en la base de datos, se tendrá
que crear una nueva
4.- Ahora se necesita elegir un esquema de partición para la
tabla. Si existe un esquema de partición disponible y coincide
con el número de grupo de archivos definidos por la función
seleccionada entonces se puede reutilizar, de lo contrario se
tendrá que crear una nueva.
5.- La página de mapa de particiones se utiliza para definir los limites de la
función de partición, y la asignación que existen entre las divisiones y los
grupos de archivos. Si la columna de la partición es date, datetime,
smalldatetime, datetime2 o datetimeoffset, el botón “Set boundaries” se
habilitará. Una vez que las particiones han sido definidas se deben de
asignar a los grupos de archivos que deseamos, estos deben de existir ya y
no pueden ser creados en el momento.
6.- Ahora tienes la opción de crear un script que particionará
la tabla, hacer la partición inmediatamente, o programarla
para que se ejecute más tarde.
 Compresión de datos
SQL Server 2008 introduce la capacidad de
comprimir tablas, índices o particiones. Esto puede
ahorrar peticiones de entrada/salida, ya que más
información en cada página equivale a menos
páginas para leer. Adicionalmente, a causa de que
más información es almacenada en cada página de
datos, más información puede ser almacenada en
la misma cantidad de memoria.
La compresión de datos se habilita usando uno de
dos diferentes modos : compresión de filas (row
ompression) o compresión de págnas (page
compression).
 Compresión de filas
La compresión de filas es un descendiente del formato de
almacenamiento vardecimal introducido en SQL Server 2005 SP2.
Anteriormente al formato de almacenamiento vardecimal, los
valores decimal eran almacenados en una cantidad fija de espacio.
La cantidad de espacio que un decimal usaba estaba basada en la
escala definida por la columna y tomaba un espacio de entre 5 y 17
bytes. Esto contribuía frecuentemente a un gran desperdicio de
espacio dependiendo del valor.
Por ejemplo, si se definía una columna como decimal (15,15), ésta
tomaría 9 bytes de almacenamiento sin importar el valor. Con el
formato de almacenamiento vardecimal activado, solo se usa el
espacio absolutamente requerido para representar un número. Esto
ha sido extendido para todos los tipos de datos de longitud fija en
SQL Server 2008.
 Indices
Como ya vimos las tablas de SQL son almacenadas en
cantidades grandes y de forma predeterminada. Para que SQL
pueda recuperar cualquier registro debe de escanear la tabla por
completo, dicho en otras palabras, debe examinar todos los
registros para poder devolver los solicitados.

Muchas tablas trabajan bien para almacenar los datos, y son

muy eficientes en el manejo de nuevos registros, pero no son
muy buenas cuando se trata de buscar los datos específicos en
una tabla. Es aquí cuando entra la necesidad de utilizar indices.

Ahora veremos los dos tipos de índices básicos que admite

SQL: clustered (agrupados) y non-clustered (no agrupados).
Aunque estos tipos son el soporte para otros tipos de
índices tal como lo son, índices XML e índices spatial
(espacial), estos últimos son diferentes de los índices
normales que son utilizados para la localización de los
datos en las tablas de bases de datos.

Una de las principales diferencias que existe entre los

índices agrupados y no agrupados, se encuentra en el
nivel de hoja.

En los no agrupados el nivel de hoja contiene

punteros a los datos, y los agrupados contienen los
datos actuales.
 Clustered indexes (índices agrupados)

Todos los datos que pertenecen a una tabla pueden ser

almacenados en uno o muchos índices agrupados. Y
cuando existen muchos índices agrupados son excluidos
mutuamente.

Los índices agrupados son creados para ordenar datos

por: un atributo, una fila o una columna en particular. La
clave que es agrupada debe de ser única en el índice, pero
no quiere decir que la columna se debe marcar como
única. Cuando los índices se crean en las columnas que
no están marcadas como únicas, SQL genera una columna
que cuenta con un número interno de 4 bytes esto para
identificar si existen duplicados agrupados en las claves de
índices.
 Non-clustered (no agrupados)
Los índices no agrupados suelen parecerse mucho
a los índices que se encuentran en la parte posterior
de un libro. Cuando el valor del índice es
encontrado, no contamos con la fila de datos reales,
sino que tenemos un puntero que nos dice la
ubicación de la fila de datos actual.
El tipo de puntero que es incluido en los niveles de
hoja de las paginas, dependera en si el índice no
agrupado es construido en la parte superior de una
pila o en un índice agrupado.
Indices No Agrupados en Heaps
Cuando un índice no agrupado es construido en una
tabla organizada como una pila, las columnas indexadas
son ordenadas junto con un puntero a la ubicación física
de los datos.

El puntero consta del identificador de archivo,

identificador de página y por el número de ranuras que
se encuentran en los datos.
Esto permite a SQL acceder a los datos de forma más
rápida después de que se encontró el índice. La
desventaja que puede tener es que si existen registros
similares, puede que sean ubicados en lugares diferentes
Índices No Agrupados en Indices Agrupados

Cuando un índice no agrupado es construido en un índice

agrupado, el valor del apuntador en el indice para esa fila es el
indice es la clave del índice agrupado. Una vez que el valor se
encuentra indexado, SQL utiliza una clave agrupada para
navegar por el índice para recuperar las columnas necesarias.

Ya sea para crear un índice agrupado o para dejar los

registros en un pila esto es una decisión de diseño que suele
ser impulsado por cómo acceder a los datos.

Cuando los datos de una tabla son muy visitados por un

atributo de predicción o de columna, puede llegar a ser útil
para poder agrupar las filas de la tabla en la columna.
Columnas Incluidas
La funcionalidad de los índices no agrupados
puede llegar a mejorar mediante los valores que
no son clave para los nodos u hojas de índice.

Esto nos va a permitir que el índice cubra más

consultas, que reduzca el número de veces que
tiene que ser atravesado el índice agrupado para
recuperar los valores adicionales.
 Por ejemplo:
Tenemos una tabla llamada contactos que tiene definido
un índice agrupado en la columna ContactID y un índice
no agrupado en la columna LastName.

Cuando una consulta requiere de la columna ContactID,

FirstName y LastName esta se ejecuta utilizando el
apellido, en el índice no agrupado, el LastName es utilizado
para localizar los registros pero solo tendrá las necesarias
que son apellido y ContactID.
SQL hace como un recorrido al índice agrupado para poder
recuperar el valor del nombre de cada registro que
encontró. Tantos recorridos se hagan a los índices
agrupados se pueden eliminar mediante el diseño como un
índice de cobertura.
Un índice de cobertura es un índice el cual contiene todos los datos
necesarios para satisfacer una consulta.

Las columnas incluidas nos permiten aumentar una cobertura de

consulta sin que pueda existir la necesidad de incurrir en la
sobrecarga de las claves. Las columnas que se encuentran
marcadas como included solo aparecen en los nodos de hojas de
índice y no son consideradas en el orden de las filas.

Para poder incluir las columnas en los nodos de las hojas,

utilizaremos la opción INCLUDE del comando CREATE INDEX. El
siguiente comando crea un índice en la columna LastName e incluye
la columna FirstName de la tabla Person.Person de
AdventureWorks2008:

P45.txt
 
Indices Filtrados

es una optimizacion del índice no agrupado. Que permite la creación

de un indice sobre un subconjunto de los datos manteniendo la
estructura del índice más pequeño, esto resulta en una disminución
de la cantidad de tiempo necesario para generar el índice y también
para reducir los costos de mantenimiento.

Los índices filtrados se utilizan para crear índices en columnas que

tienen un alto porcentaje de valores nulos o columnas que contiene
rangos de datos como son los montos en dólares. Para crear un
índice filtrado solo basta incluir la cláusula WHERE con la
instrucción CREATE INDEX.
El siguiente ejemplo crea un índice de
los productos que cuestan más de
$800.00.

P46.txt
Índices Jerárquicos
Como vimos en el capítulo 4, HierarchiID es un tipo
de dato nuevo que fue introducido en SQL
Server2008. Los índices pueden ser construidos en
las columnas de este nuevo tipo de dato utilizando
dos enfoques diferentes: breadth-first and depth-
first.
Índices Breadth-First
Mantiene agrupados todos los registros que
se encuentran dentro del mismo nivel. Esto
le permite a SQL responder de forma rápida
a las consultas.
En el código siguiente se crea un índice de
prioridad a la amplitud de una tabla de
empleados:

P47.txt
Indices Depth-First
Mantienen todos los registros agrupados juntos para una
cadena. Esto permite que SQL Server responda a las
consultas que están en busca de una jerarquía de una
forma rápida.
Para poder crear un índice de profundidad, lo que
tenemos que hacer es crear un índice en la columna
HierarchyID.
El siguiente código crea in índice de profundidad en la
tabla de empleados:

P48.txt
 Indices Espaciales
(Spatial Indexes)
SQL Server tiene incluido un soporte para los datos
especiales a través de dos nuevos tipos de datos CLR:
geometry y geography.

El tipo de datos geometry fue diseñado para planear el

espacio mientras que el tipo de datos geography usa la
geodésica de espacio
En una operación común de datos espaciales es la de
encontrar todos los registros que se cruzan con una
determinada área.

La creación de un índice espacial pasa por dos fases: la

descomposición y la teselacion.
En la fase de descomposición SQL rompe un área finita en una
estructura de red. Y cada una de las células de la red es asignada a
otra estructura de red, formando así un nivel más detallado.

La red de cada nivel puede ser configurada para ser una 4*4, 4*8, 8*16
o 16*16 red de celdas .

Como podemos ver, el número de células pueden ir en aumento

rápidamente.

Durante la fase de teselacion cada valor espacial en la tabla es

mapeado a cada uno de los niveles de la cuadricula resultante. El
índice espacial puede ser utilizado para localizar objetos en el espacio
que están relacionados con otros objetos que se encuentran
almacenados en el índice, los índices espaciales también pueden ser
construidos en una sola columna de datos, cada una de estas cubre
una área independiente del espacio.
Indices XML
Otro de los tipos de índice que es compatible con SQL
Server2008 es el llamado índice XML. Los datos de tipo
XML se almacenan como un objeto grande en binario, en la
base de datos de SQL.

Para poder buscar ciertos elementos, atributos o valores en

los documentos XML, primero debemos de abrir el binary
large object (BLOB) y después desplegar su contenido.

El acto de trituración es lo que hace que SQL pueda crear

un colección de objetos XML en la cual se puede navegar,
en realidad los extractos de la estructura de datos de XML
almacenan temporalmente las estructuras relacionales.
 Los índices de XML vienen con una sobrecarga pero el índice de
arriba es más importante que los índices regulares, es por esto que
los índices de XML deben ser reservados para las columnas en los
que rara vez son modificados.
 
Almacenar y recuperar archivos XML suele ser mucho más eficiente
que las aplicaciones de bases de datos. Sin embargo hay empresas
que requieren de este tipo de funcionalidad, por lo que la capacidad
de crear índices se incluyó para evitarnos destruir completamente
documentos.
 
Existen cuatro tipos de índices de XML, el primero debe ser un
índice principal este a su vez tiene tres índices secundarios. Cada
tipo de índice adicional mejora el rendimiento de la consulta XML
para algunos ciertos tipos de consultas, para otros no puede ser
muy eficiente ya que afectan la modificación de datos SQL.
 Indices Primarios XML
El índice XML principal no se basa en la columna XML sino
que además es un índice agrupado que se basa en una
tabla interna, y esta tabla es creada durante el proceso de
creación del índice. A esta tabla interna se le conoce como
tabla de nodo.
 
La tabla de nodo está relacionada directamente con el
índice agrupado de la tabla donde fue creado el índice
XML, y es utilizada para apoyar al índice principal XML pero
esta no es directamente accesible. Para poder crear un
índice XML, la tabla y la columna deben de tener un índice
agrupado en la clave principal.
 
El índice de XML almacena una representación relacional del
campo XML y nos ayuda a optimizar consultas en la creación
de planes eficaces de la consulta esto para obtener datos de un
campo XML. El siguiente ejemplo crea un índice XML:
P49.txt

Los índices primarios de XML se pueden crear gráficamente en

el Management Studio. Para crearlo primero se debe de crear
una tabla. Para poder copiar y usar la tabla Person.Person la
cual contiene una columna XML ejecutamos el siguiente
código el cual crea la tabla MyContact y después crea un índice
agrupado en la clave principal:
P50.txt
 Ahora que tenemos una tabla, expandimos el nodo de la base de
datos AdventureWorks2008R2 en el explorador de objetos,
expandimos tablas y después la tabla dbo.MyPerson.

Es posible que se tenga que actualizar el nodo de tablas para poder

obtener la tabla MyPerson.
 
Hacemos click derecho sobre la tabla MyPerson y después
seleccionamos diseño. La estructura de la tabla ira apareciendo en
la parte derecha del explorador de objetos.
Hacemos click en la columna AdditionalContactInfo, después en el
botón administrar índices XML de la barra de herramientas, el
diseño de tablas (fig. 5-18) si la barra de herramientas no es visible
seleccionamos en el menú ver barra de herramientas, diseño de
tablas.
En el cuadro de dialogo índices XML (fig.) hacemos click en agregar
y después cambiamos el nombre del nuevo índice principal el cual
es PXML_MyPerson_AdditionalContactInfo y podemos dar una
breve descripción de que es el índice primario de XML.
Debemos de tener en cuenta que la propiedad primaria
se establece en sí y no se puede cambiar, esto se hace
porque es el primer índice XML y el primero en ser
creado en una columna y debe ser un índice principal.

Los índices primarios también pueden ser creados

haciendo click derecho en el nodo de índices de la tabla
en el explorador de objetos, después en nuevo índice, y
elegimos primary XML en la lista en el tipo de índice del
menú desplegable (fig.).
En cambio los índices secundarios no pueden ser
creados de esta manera.
Indices Secundarios XML
Los índices XML Values son diseñados para
permitir las consultas XML donde la ruta no está
totalmente especificada o cuando un valor esta
siendo buscado por un comodín, por ejemplo si se
trata de recuperar los nodos que están en
Lineltem se identifica el producto 9834, la factura
pertenece a la utilización del XQuery
como/Invoice/LineItem[@ProductID="9834"],
entonces el índice de valor puede ser mejorado
en el rendimiento, el siguiente código muestra la
sintaxis para crear un índice de valor secundario:
P51.txt
Para la creación de índices secundarios gráficamente es
prácticamente lo mismo que el principal, excepto que el
tipo de índices secundario puede ser ahora seleccionado
de la lista desplegable tipo secundario, para crear un
índice XML secundario hacemos click en el botón añadir
en la ventana de configuración de los índices XML.
Ahora que se ha agregado predeterminamos los valores
siguientes en el índice de tipo secundario, la propiedad
primaria se establece en no, y nos da una nueva lista
desplegable de tipo secundario (fig).
Indices Secundarios
XML PROPERTY
Los índices secundarios XML PROPERTY pueden ser
utilizados para la optimización de consultas que
recuperan el valor de los nodos especificando las rutas
completas de los nodos. Por ejemplo si deseamos
devolver el identificador del producto para el primer nodo
Lineltem en el documento utilizado XQuery como
(/Invoice/LineItem/@ProductID) y después un índice
PROPERTY se puede mejorar el rendimiento.
A continuación un ejemplo de la sintaxis para crear un
índice de propiedad secundaria:
P52.txt
 Mantenimiento de las Tablas

Ahora que se tiene una mejor idea de cómo se

organizan o se guardan los datos en las tablas, y de
las diferentes formar que existen para optimizar la
recuperación de los datos, el mantenimiento de la
tabla se puede clasificar en dos categorías:

Mantenimiento de los índices.

La creación y mantenimiento de estadísticas
de los índices.
 
 
Fragmentación de Índice

Una de las principales causas de bajo rendimiento

de las consultas es que no están bien mantenidos
los índices, como estos se actualizan también se
pueden fragmentar.
Esto suele pasar por que los índices son un conjunto
contiguo de datos ordenados.
Para mantener los índices ordenados SQL Server
divide las páginas de todos los datos para hacer
espacio para más datos
Por ejemplo, el Extent 72 (fig.) tiene un índice
agrupado definido en la columna LastName de la
tabla Slate.Employee. Cada página de datos está
totalmente lleno en el Extent.
El siguiente código se ejecuta
para insertar una nueva fila en la
tabla Slate.Employee:

P53.txt
Una división de página se produce inmediatamente,
esto es porque no existe espacio en la página para
un nuevo registro. Para mantener un orden en las
filas lo que hace SQL es dividir en 113 páginas y
mueve aproximadamente el 50% de las filas a una
nueva página no asignada. Fig.
 Como resultado de esta página dividida, cuando
SQL lee la paginas de datos para recuperar el
contenido de la tabla Slate.Employee este cambia
de grado 72 en la grado 119, y después vuelve a
cambiar al grado 72 para así poder continuar con la
exploración de fila.
 
Las divisiones de páginas causan fragmentación. La
fragmentación de los índices puede llegar a causar
que SQL realice un número excesivo de lecturas
para la recuperación de los datos, y esto resulta un
bajo rendimiento de consultas.
 
 Para poder comprobar la fragmentación en todos los
índices de una tabla o en indices específicos,
podemos utilizar la función
sys.dm_bd_index_physcal_stats.
 
Esta función nos puede devolver gran cantidad de
información índices que existen en una tabla,
incluyendo la cantidad de datos en cada página, la
fragmentación, el tamaño medio de los registros de
un índice.
El nivel de fragmentación nos permite conocer los
índices que deben ser reconstruidos y que el
porcentaje medio del espacio de cada pagina y
nos dice que tan pronto es posible realizar más
divisiones de páginas. Para poder consultar la
vista de administración dinámica
sys.dm_db_index_physcal_stats usamos la
siguiente sintaxis:

P54.txt
La función sys.dm_db_index_physcal_stats requiere
de cinco parámetros que se le pasan cuando se
recupera la información del índice, en la siguiente
tabla se describen los parámetros:
Para practicar el mantenimiento de índices, ejecutaremos
el siguiente comando para crear la tabla MyPersons, la
cual usaremos en los siguientes ejemplos:

P55.txt
Para poder consultar la vista
sys.dm_db_index_physical_stats y devolver todos los
datos posibles en relación de la tabla de MyPersons
ejecutamos la siguiente consulta:

P56.txt
La ejecución de esta consulta devuelve más
información que la generalmente es necesitada, ya
que lo que realmente nos interesa es la
fragmentación del nivel de hoja del índice y el
porcentaje ocupado de las páginas de datos. Se
puede limitar la cantidad de datos devuelta

Para poder reducir la información devuelta por la

consulta sys.dm_db_index_physical_stats, se
limita a solo las columnas de interés y el nivel de
hoja del índice, de la siguiente forma:
P57.txt
 
Esta consulta nos devuelve el nivel de
fragmentación y el espacio de página utilizado
para el nivel de hoja de todos los índices, aquí es
donde la fragmentación (en cuanto a nivel se
refiere) se producirá.
Una definición precisa de fragmentación es, el
porcentaje de las páginas donde la
siguiente pagina física no es la siguiente pagina
logica, como se muestra en la figura.
El siguiente script inserta 3.994 registros adicionales,
lo que constituye un aumento del 20% en las filas:

P58.txt
 
La consulta de la vista de administración dinámica
sys.dm_db_index_physical_stats ahora nos
devuelve datos interesantes

P59.txt
Casi es 97% del tiempo cuando SQL estaba leyendo las
páginas de datos, la siguiente página física no fue la
siguiente pagina lógica, esto debido a que se añadieron filas
a la tabla MyPersons, aparte de la fragmentación las páginas
de datos tiene ahora solo el 59% de su capacidad.

La combinación de los índices de fragmentación y las

páginas de datos parcialmente llenas causa que SQL lea 274
extensiones lógicas, cuando solo 40 deberían de ser leídas.

Esta información la podemos consultar a través de un

comando: DBCC SHOWCONTIG.DBCC SHOWCONTING,
podremos ver lo que contiene la tabla MyPersons:

P60.txt
Muchos de los comandos DBCC van más allá de la
comprobación de coherencia de datos, es por eso
que DBCC se puede utilizar como un acrónimo para
la consola de comandos de la base de datos.

El comando DBCC SHOWCONTING nos muestra

que SQL escanea 38 extensiones para la
recuperación de datos de la tabla MyPersons, pero
para ello tuvo que pasar entre 274 veces
SQL utiliza los índices para encontrar rápidamente
las filas en las páginas de datos para la lectura,
actualización o eliminación. Sin embargo si
solamente insertamos datos en las tablas no
necesitamos un índice. La regla general es que los
índices mejoran el rendimiento de la lectura y
perjudican el rendimiento de inserción.
Mitigación de la Fragmentación con
Fill-Factor

Para mitigar la fragmentación que es producida por

las divisiones de página, el administrador de bases
de datos puede diseñar o reconstruir índices para
que las páginas de datos no estén completamente
llenas.
A esta opción se le conoce como Fill-Factor, al
construir o reconstruir el índice, un porcentaje del
Fill-Factor puede ser especificado. Si una página de
índice solo se llena el 90% se necesita hacer mas
inserciones en el indice para ocasionar que la
pagina se dividas y por lo tanto la fragmentacion
pueda ocurrir.
El Fill-Factor solo puede ser eficaz cuando los índices
son construidos o reconstruidos. Después de ciertas
inserciones, los índices se volverán a llenar y las
divisiones de página se van a producir, sin embargo
las divisiones de página no se producirán de inmediato
ya la cantidad de tiempo entre la reconstruccion del
índice puede ser alargada.

Llenar las páginas de índice tiene sus desventajas,

primeramente las paginas no están completamente
llenas, la cantidad de espacio necesaria en disco para
almacenar el índice aumentara.
Además, dado que hay menos datos en cada página,
el número de lecturas necesarias para recuperar los
datos aumentara. Y como resultado tenemos un punto
definido de rendimientos decrecientes cuando se
establece un Fill-Factor.

El Fill-Factor de los índices no debe de ser inferior al

90%. En las tablas actualizadas y consultadas este
porcentaje podría bajar hasta un 85% pero hay que
tomar en cuenta que SQL tendría que realizar el 15%
más de lecturas para obtener el 100% de los registros.
Como resultado un 10% de nivel de fragmentacion
puede tener el mismo efecto que un fill-factor de 90%
La eliminación de la
fragmentación
Hay tres maneras de eliminar la fragmentación:
Los índices se pueden quitar y volver a crear,
reconstruir en su lugar o reorganizar. Cada
método tiene sus ventajas y desventajas.

La opción de descartar y recrear se usa con el

comando CREATE INDEX. La reconstrucción y
reorganización de las opciones se utilizan con el
comando ALTER INDEX. Vamos a echar un
vistazo a cómo utilizar cada uno de estos
enfoques.
Creación de Índices con
Drop Existing
La principal ventaja de la eliminación y recreacion de
un índice es que casi todo lo relacionado con el índice
puede ser cambiado.

Por ejemplo, las columnas que se definen en el índice

se puede cambiar, el fill-factor del índice puede ser
modificado, o el índice se puede cambiar de un índice
no agrupado en un índice agrupado, siempre y
cuando un índice agrupado no exista. Sin embargo, al
utilizar la opción DROP_EXISTING con el comando
CREATE INDEX, un índice específico debe ser
indicado.
Cuando se utiliza la reconstrucción o la reorganización de
las opciones del comando ALTER INDEX, todos los índices
en una tabla se pueden procesar a la vez.

La reconstrucción de un índice con la opción

DROP_EXISTING elimina la fragmentación del índice
mediante la reconstrucción de todas las páginas de índice
en orden indexado.

También compacta las páginas de índice para que el

espacio vacío creado por las divisiones de página se llene.
Tanto el nivel de hoja y el nivel no hoja de los índices se
reconstruyen.
El siguiente es un ejemplo de la sintaxis para
borrar y volver a crear un índice con el comando
CREATE INDEX

 P61.txt
Reconstruyendo índices
Cuando un índice es reconstruido con el comando
ALTER INDEX, SQL Server en realidad descarta y
vuelve a crear el índice al igual que el comando
CREATE INDEX.

La diferencia es que las columnas del índice

existente no se pueden cambiar, ni el tipo de índice.
Sin embargo, el factor de llenado se puede modificar.
También es posible ejecutar el comando solo una vez
y tienen que reconstruirse todos los índices en la
tabla.
Otra característica muy útil es la opción en línea. Si
está en línea, SQL Server no pondrá ningún bloqueo
a largo plazo en la tabla que está siendo indexada, lo
que resulta en un impacto mucho menor en el
rendimiento del usuario.

Con el fin de hacer esto, SQL Server aprovecha la

base de datos tempdb para la creación del índice y el
mantenimiento. Los índices se crean o se regeneran
en la base de datos tempdb y luego se trasladan a la
base de datos adecuada o correspondiente
Esto disminuye el impacto de los usuarios en la
base de datos, pero puede causar un crecimiento
inesperado de la base de datos tempdb.

La opción de índice en línea sólo está disponible

con las ediciones Enterprise y Developer de SQL
Server.

Al igual que la opción DROP_EXISTING, la opción

REBUILD de ALTER INDEX reconstruye tanto la
hoja y no hoja de los niveles del índice.
El siguiente es un ejemplo de la reconstrucción
de un índice individual y luego todos los índices
en una tabla con un FILLFACTOR de 90% y la
opción ONLINE activada:

P62.txt
 
Reorganización de índices
La reorganización de índices consume la menor
cantidad de recursos del sistema, pero no como un
trabajo a fondo sino como una reconstrucción del
índice.

Cuando SQL Server reorganiza un índice, este

reorganiza y compacta las páginas de datos de
modo que coincida su orden lógico con su orden
físico.

La reorganización de índices sólo afecta el nivel de

hoja del índice y se realiza siempre de forma online.
La guía sobre el momento de realizar una reorganización
de índice en comparación con el momento de realizar una
regeneración tiene el nivel de fragmentación de 30 por
ciento.

Si el nivel de fragmentación es menor o igual al 30 por

ciento, la reorganización se llevará en menos tiempo que
una reconstrucción de índices y consume mucho menos
recursos del sistema.

Si la fragmentación es mayor que el 30 por ciento, lo más

probable es que la reorganización de índices llevara más
tiempo que una reconstrucción, pero todavía se consumen
menos recursos.
En general, si los índices se reconstruyen
periódicamente con un adecuado factor de
llenado, la necesidad de una reorganización del
índice entre los períodos se reduce. Sin embargo,
los intervalos de la actividad de transacción
pueden requerir una reorganización para evitar la
fragmentación de más de 30 % y que puede
causar problemas de rendimiento
Estadísticas

Las estadísticas son utilizadas por SQL Server

para encontrar los medios más eficaces de
recuperación de datos de las tablas de la base de
datos mediante el almacenamiento de la
información acerca de la selectividad de los datos
en una columna, así como la distribución de los
datos de una columna.
Se pueden crear de forma manual y automática. El
capítulo 10 describe las estadísticas con mayor
detalle.
Exigir la integridad de datos
Como se ha mencionado en capítulos anteriores, el
objetivo de este libro es que usted quede
familiarizado con la teoría de bases de datos, por lo
que no se exponen como propósito las limitaciones
para mantener la integridad de los datos.

En su lugar, lo que está cubierto en esta sección es

cómo crear estas limitaciones, así como otros
objetos de base de datos que se utilizan para
mantener la integridad y consistencia de los datos.
 
Restricciones de clave principal

Una tabla puede tener una y sólo una restricción de clave

principal. Este valor es el que se utiliza para identificar de
forma única cada fila de la tabla.

Una restricción de clave principal se puede definir de dos

formas en una sola columna o una combinación de las
columnas, si se necesita más de una columna para identificar
de forma única cada fila.

Es muy importante que usted entienda cómo SQL Server

exige la unicidad de los valores clave especificados en la
definición de una clave principal. Lo hace mediante la
creación de un índice único en la columna o columnas que
participan en la clave
Sería muy ineficiente para tratar de forzar la
unicidad sin ordenar los datos. El problema con
SQL Server a lo que esto respecta es que el valor
por defecto es un índice agrupado único, si un
indice agrupado no existe.

Las decisiones sobre qué columna o columnas

participan en una clave principal y cuales en la
estructuración física de los datos de la tabla son
completamente diferentes
No se debe suponer que la clave principal también
debe ser la clave agrupada de la tabla. Recuerde
que todos los índices no agrupados de la tabla se
incluiran en la clave del índice agrupado como el
puntero de vuelta a la fila de datos.

Si la longitud de la clave principal es grande,

usando un índice agrupado para apoyar la clave
primaria podría ser muy perjudicial para un indice
no agrupado de almacenamiento y recuperación.
Las claves principales se pueden crear mediante la
selección de la columna o columnas en la ventana
Diseñador de tablas y luego hacer clic en el boton de
''Establecer clave principal', o mediante el uso de
Transact-SQL en un comando CREATE TABLE o
ALTER TABLE. Los siguientes son ejemplos de cómo
establecer una clave principal en las tablas durante y
después de su creacion.

Cuando se utiliza la sentencia CREATE TABLE, puede

definir la restricción como parte de la definición de la
columna o al final de todas las definiciones de columna
como parte de la definición de la tabla.
El primer ejemplo muestra cómo crear la clave
principal como parte de la definición de la columna
del comando CREATE TABLE:

P63.txt
El siguiente ejemplo también se crea una
restricción de clave principal en el comando
CREATE TABLE, pero lo hace como parte de la
definición de la tabla al final de todas las
definiciones de columna:

P64.txt
En ambos casos, la palabra clave CONSTRAINT y
el nombre de la restricción son opcionales. Si el
nombre se omite, SQL Server asigna un nombre
generado por el sistema.
Se recomienda proporcionar un nombre para
todas las restricciones ya que es lo que se
mostrará dentro de SQL Server Management
Studio y en cualquier mensaje de error generado
por SQL Server, y un nombre más amistoso podría
ser útil.
El nombre que se elija debe ser único dentro del
esquema que contiene la tabla.
El último ejemplo muestra cómo agregar una
restricción de clave principal a una tabla ya
existente mediante el comando ALTER TABLE:

P65.txt
Además, recuerde que si la palabra clave
NONCLUSTERED se omite, SQL Server crea un
índice agrupado para exigir la clave si no está
definido.

Asegurarse de que ésta clave es la que estaba

destinada, ya que es un error común
 
Restricciones únicas

Mientras sólo una restricción de clave principal se

permite en una tabla, muchas restricciones únicas
se pueden especificar.

Por ejemplo, una empresa de mensajería que

emplea conductores podria querer registrar la
información sobre sus conductores en una tabla
como en el siguiente ejemplo:
P66.txt
En este ejemplo, el empleador probablemente
quiere asegurarse de que tanto el número de
Seguro Social y el número de licencias de
conducir son únicos, además de la clave primaria.

Usted puede estar pensando, ''¿Por qué no sólo

se utiliza el número de Seguro Social o el número
de licencia como la clave principal?'' Hay muchas
razones del porque estas columnas no son
buenos candidatos para una clave principal.
Cuando se trata del número de Seguro Social, la seguridad
puede ser un gran problema. Dado que las claves primarias
se utilizan como claves externas (foreign key), el número
de Seguro Social seria duplicado en varios lugares.

Dada la sensibilidad colocada en la información privada,

esto se convertiría en una pesadilla de gestión. Otra razón
se aplica tanto al número de Seguro Social y el número de
la licencia de conducir.

Debido a que estas cifras no son números en absoluto, sino

más bien cadenas de caracteres, no son los mejores
valores a utilizar para exigir la integridad referencial, ya que
los criterios de unión serían muy grandes en lugar de un
valor entero más eficiente.
Otra cosa a considerar es que los valores son
únicos, después de todo. Los números de Seguro
Social pueden ser reutilizados, y usted puede
terminar con problemas.

Esto ocurre generalmente si se planea mantener los

datos durante un tiempo muy largo.
Para crear una restricción única, tiene dos
opciones: Crear un índice único o crear una
restricción única en la tabla. Un índice único se
comporta como una restricción unica, y SQL Server
crea un índice único para hacer cumplir la
restricción única.

Sin embargo, hay una pequeña diferencia. Si se

crea una restricción unica, la única manera de quitar
el índice único es quitar la restricción. No será
posible utilizar el comando DROP INDEX.
Para crear índices únicos o restricciones
gráficamente, en primer lugar se abre la tabla
para la modificación haciendo clic derecho sobre
el nombre de la tabla y haga clic en Diseño.

En la barra de herramientas Diseñador de tablas,

haga clic en el botón “Administrar índices y
claves'' (ver Figura).
Figure: ‘‘Manage Indexes and Keys’’button.
En los Índices / Claves de diálogo (ver Figura),
haga clic en Agregar y especifique las propiedades
de la clave indexor nuevo.

Observe en la propiedad Type que, pueden ser

elegidos indice o clave unica. Si la''propiedad''Es
único se establece en True, entonces o clave de
índice o único tendrá el mismo efecto.
Figure 5-26: Indexes/Keys dialog.
Para exigir la exclusividad en la columna
LicenseNum, uno de los siguientes comandos se
puede utilizar ya que ambos tienen el mismo
resultado:

P67.txt
 
Restricciones foreign key (Restricciones de llaves foraneas)

Restricciones de claves foráneas se crean para

garantizar la integridad referencial entre las tablas.

Para crear una restricción de clave externa en una

tabla, las columnas definidas en la clave externa
deben asignarse a una columna o columnas de
una tabla de clave principal, donde las columnas
se designan como la clave principal o tiene una
restricción única (ambas restricciones e índices
deben ser únicos).
Los siguientes ejemplos se basan en la tabla
dbo.Driver creado anteriormente y la tabla
dbo.DriverRecord, que se pueden crear con el
siguiente script:

P68.txt
Para crear una restricción de clave externa con las
herramientas de gráficos, expanda la tabla
DriverRecord en el Explorador de objetos.

Haga clic derecho en el nodo de teclas y haga clic

en ‘‘New Foreign Key.’ El cuadro de diálogo
Relaciones de clave aparecerá en la pantalla (vea
la Figura).
Figure: Foreign Key Relationships dialog.
Oprima en los puntos suspensivos a la derecha de los
cuadros ‘‘Tables And Columns Specification’’ en las
propiedades para seleccionar la clave principal y las
columnas clave.

En las tablas resultantes y el diálogo de las columnas (ver

Figura), seleccione Driver como la tabla de clave principal y
DriverID como la columna, tanto en la clave principal y en
las tablas de claves externas, como se muestra en la Figura.

Una vez que cierre el asistente y la página del Diseñador de

tablas, debe ser capaz de ver la clave externa recién creada
en la carpeta de claves en la vista de árbol. Si no, haga clic
derecho en la tabla DriverRecord y seleccione Actualizar.
Figure: Tables and Columns dialog.
 
Opciones de restricción de
clave foránea

Restricciones de claves foráneas tienen muchas

opciones avanzadas que cambiar, su forma de
comportarse durante la creación y después de la
creación que se describen en las secciones
siguientes. Estas opciones se pueden establecer en
las secciones de diseño general y la tabla del
cuadro de diálogo Relaciones de clave externa, o a
través de Transact-SQL.

Ejemplos del código necesario para crear las claves

externas y establecer sus opciones se dan con
cada descripción.
Los siguientes ejemplos utilizan el nombre de la
restricción misma. Para ejecutar los ejemplos de la
sucesión, será necesario quitar la restricción
existente antes de volver a ejecutarla.

Las restricciones pueden ser eliminados mediante

SQL Server Managements Studio’s Object Explorer
o mediante la ejecución de la secuencia de
comandos ALTER TABLE dbo. DriverRecord
RESTRICCIÓN FK_DriverRecord_Driver.
 
WITH CHECK

WITH CHECK es la configuración predeterminada

cuando se agrega una restricción de clave foránea.
Esta opción especifica que los datos existentes en
la tabla de clave foránea debe ser validados para
cumplir con la restricción:

P69.txt
WITH NOCHECK

El ajuste WITH NOCHECK específica que los

datos existentes no se ha validado para cumplir
con la nueva restricción.
Esta opción puede hacer el proceso de creación
más eficiente cuando se sabe que todos los datos
existentes ya se ajustaron a la restricción, pero es
importante saber que los registros no conformes
se tendrán en cuenta durante la creación.
Sin embargo, durante las actualizaciones
posteriores de la fila de los registros de no
conformes, la restricción se aplica, lo que resulta
en un error.

P70.txt
 
Restricciones en cascada

Las claves externas evitan la actualización o

eliminación de valores de los padres (valores
primarios o únicos) por default.

Sin embargo, hay ocasiones en que esto no es

deseable. SQL Server ofrece la opción de
especificar qué acción se toma en los registros
secundarios si un registro primario se elimina o
actualiza.
ON DELETE NO ACTION y ON UPDATE NO
ACTION son las configuraciones por defecto de
las claves externas.

Este tipo de configuración específica para

cualquier intento de eliminar una fila o actualizar
una clave de referencia claves externas de filas
existentes en otras tablas fallará.
Además de la configuración de NO ACTION por
default, las opciones CASCADE, SET NULL y SET
DEFAULT son posibles, estas permiten la supresión
o actualización de los valores clave en cascada de
una manera definida de las tablas definidas para
tener relaciones de clave externa.
ON DELETE CASCADE
Esta opción especifica que todos los registros
secundarios se eliminarán cuando la fila primaria
se elimina. Si el registro del hijo también tiene
registros secundarios, las opciones de clave
externa en las tablas se hará cumplir y ya sea en
cascada o no.

P71.txt
Aquí se borra todo lo que tiene “llave primaria”
 
ON UPDATE CASCADE

Cuando una clave principal se actualiza, la

actualización en cascada de los registros de los
hijos que hacen referencia a las claves de los
padres.

P72.txt
Aquí en vez de borrar se actualiza
 
ON DELETE SET NULL

Con este ajuste, la clave externa de cualquier

registro del hijo se establece en NULL si la fila
primaria se elimina. La columna de clave externa
debe permitir valores nulos para que funcione esta
opción.

P73.txt
Borra las columnas que tengan en referencia la
llave foranea
 
ON UPDATE SET NULL

Cualquier registro de la clave externa del hijo se

establece en NULL si la clave primaria
correspondiente se actualiza. La columna de clave
externa debe permitir valores nulos para que
funcione esta opción.

P74.txt
No actualiza el dato lo pone null
ON DELETE SET DEFAULT
Cuando un registro padre es eliminado, el valor
correspondiente al hijo clave se fijará en el valor
especificado por cualquier restricción por defecto definida
en esa columna.

Si no existe restricción DEFAULT, el valor se establece en

NULL, siempre y cuando la columna de clave externa sea
anulada.
El valor especificado en la restricción DEFAULT debe tener
una fila correspondiente en la tabla padre.

P75.txt
 
ON UPDATE SET DEFAULT

Cuando un valor de clave principal se actualiza todos los

registros secundarios correspondientes se actualizará con
el valor especificado en la restricción por defecto definido
en la columna de clave externa.

Al igual que la opción anterior, el valor por defecto debe

existir en la tabla padre.
Si no hay ningún valor predeterminado definido y la
columna de clave externa es anulable, el valor del hijo se
establece en NULL.
P76.txt
Se actualiza en uno en otro pero aquí pone el valor de default
 
ON UPDATE SET DEFAULT

La configuración en cascada varios de éstos

pueden ser combinados y mezclados.

Por ejemplo, la opción de cascada para un

DELETE se puede configurar para CASCADE,
pero ninguna acción para una actualización.
Check Constraints

Restricciones de comprobación se utilizan para

asegurar que los datos en un campo se ajusten a
una expresión definida. Las restricciones de
comprobación se pueden crear gráficamente
mediante los siguientes pasos en la tabla
dbo.Driver que fue creado anteriormente:
1. Expanda la tabla dbo.Driver en el Explorador
de objetos.
2. Haga clic derecho en el nodo Restricciones y
haga clic en''NEW CONSTRAINT''. Esto abrirá el
cuadro de diálogo CHECK CONSTRAINTS.
 3. En el cuadro de diálogo CHECK
CONSTRAINTS (ver Figura), cambiar el nombre de
la restricción a CK_DriverSocialSecurityNumber en
la sección de identidad y cambiar la descripción de
Cumplir los valores numéricos de SSN.
4. Modificar la expresión de la restricción,
simplemente introduciendo la siguiente expresión:

(SocSecNum LIKE ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-

9]’)
Figure: Check Constraints dialog.
Esta expresión se asegura de que los números de
Seguro Social a la tabla será de nueve enteros
contiguos sin guiones. La validación de un número
de Seguro Social (SSN) es mucho más complicada
que esto.

Por ejemplo, los tres primeros números de un

rango de número de Seguro Social sólo están
entre 001 y 772, pero esto sería muy complicado
con un operador LIKE simple.
Observe que cuando usted está trabajando dentro de
cualquiera de la restricción de clave externa o casillas de
verificación de restricciones de diálogo (ver Figura), no
hay botones Aceptar o Cancelar.

Si abre el cuadro de diálogo, una restricción se agrega

automáticamente para usted, así que si usted decide no
agregar la restricción, en realidad tendría que eliminar
antes de cerrar el diálogo.

Aquí está el comando Transact-SQL para crear la misma

restricción:
P77.txt
Restricciones por default.
Las restricciones por defecto especifican un
valor para ser insertado en una tabla si no se
especifica un valor durante la inserción. Se
pueden aplicar a una tabla cuando se crea o
añadirlos posteriormente.
Para crear una restricción predeterminada con las
herramientas gráficas, primero seleccione la
columna que desea aplicar a la opción
predeterminada y, a continuación especificar un
valor predeterminado o el enlace en la ventana de
propiedades de la columna del Diseñador de
tablas, como se muestra en la Figura.
Los enlaces son enlaces a una base de datos
predeterminada o a la regla y se analizan más
adelante en este capítulo.

Para este ejemplo, se especifica la cadena de

'000000000 'como el valor predeterminado de la
columna SocSecNum.
El comando de Transact-SQL lleva a cabo esta
misma tarea, es lo siguiente:

P78.txt
Diagramas de Base de Datos
Una vez que la base de datos y sus objetos han sido creados, a
menudo es conveniente ser capaz de crear diagramas entidad
relación que están vinculados a las estructuras subyacentes.

De esta manera, los cambios que deben hacerse (en especial la

creación de restricciones de claves foráneas) se puede hacer y
se aplica a la base de datos en un entorno gráfico conveniente.

La función de diagrama de base de datos en SQL Server

Management Studio proporciona esta funcionalidad. La función
de diagrama de base de datos, sin embargo, no es un sustituto
de pleno derecho las herramientas de base de datos de diseño.
Es más frecuente en la fase de prueba y desarrollo de la
implementación de bases de datos.
La función de diagrama de base de datos se
accede en el Explorador de objetos de SQL Server
Management Studio en el nodo de base de datos
individual.

Antes de poder crear el diagrama de base de datos

en primer lugar, tendremos que instalar los objetos
de soporte del diagrama.
Esto se puede hacer haciendo clic derecho en el
nodo de Diagramas de Base de Datos y
seleccionar ''Instalar el soporte del diagrama.

''Si usted no hace esto, entonces la primera vez

que trate de crear un diagrama de base de datos,
un mensaje informativo que se mostrará notificará
que ''uno o varios objetos de apoyo ''faltan, y le
pregunta si desea o no que instalarlos
O bien la instalación de los objetos de apoyo o
seleccionar YES hará que SQL Server elabore
una tabla del sistema de propiedad llamado
dbo.sysdiagrams que contendrá las definiciones
de todos los diagramas creados.

Los siguientes pasos le guiarán a través de la

creación y modificación de un diagrama de base
de datos:
1. Expanda el nodo Bases de datos y el nodo de
base de datos AdventureWorks2008. Haga clic
derecho en el nodo diagramas de base de datos
de AdventureWorks2008 y haga clic en “Nuevo
diagrama de base de datos”.

El panel Diagrama de base de datos aparece, así

como un cuadro de diálogo Agregar tabla en orden
alfabético que muestra todas las tablas de usuario
en la base de datos.
 2. Seleccione la Dirección (Persona) en la tabla.
Haga clic en Agregar para agregar la tabla
Person.Address en el diagrama y haga clic en
Cerrar en el cuadro de diálogo Agregar tabla.
(También puede hacer doble clic sobre la tabla en la
lista para agregar al diagrama.)

3. Haga clic derecho en la Dirección (Persona) de

la tabla y haga clic en Agregar tablas relacionadas''
(‘‘Add Related Tables.’’ ) Esto hace que todas las
tablas que tienen una relación definida con la tabla
Person.Address se añadan a la figura. Esta
característica es muy útil cuando no se está
familiarizado con la estructura de la base de datos.
Tenga en cuenta que todas las tablas son apiladas
una encima de la otra en el diagrama.
Manualmente, puede cambiar el orden, o
simplemente haga clic derecho en un espacio
vacío en el diagrama y haga clic en Organizar
tablas' '(‘‘Arrange Tables.’’).

'SQL Server se encarga de las tablas que tenía en

el panel de diagrama de manera que las tablas y
sus relaciones se ven fácilmente
Porque no hay espacio limitado en el diagrama,
puede crear varios diagramas que dividan a la base
de datos en áreas funcionales, o puede mostrar los
saltos de página en el diagrama y dividir un
diagrama de gran tamaño en muchas páginas.

Para mostrar los saltos de página, haga clic

derecho en un espacio vacío en el diagrama y haga
clic en ''Ver saltos de página.'' (‘‘View Page Breaks.
’’)
Al hacer clic derecho cualquier tabla proporciona la
opción de cambiar la forma en que se muestra la
tabla en el diagrama, eliminar la tabla de la base de
datos, remover la tabla del diagrama, así como
varias opciones para modificar la tabla
normalmente disponible en la barra de
herramientas Diseñador de tablas.
Vistas (views)
SQL Server 2008 vistas es simplemente guardar las consultas
que se nombran y se pueden administrar de forma independiente
de las tablas que hacen referencia.

Ellos son muy similares a las tablas que hacen referencia, excepto
que son, por default, los objetos lógicos y los objetos no físicos.

La única excepción a esto es que cuando un índice agrupado

único se crea en una vista, provoca que la vista sea materializada.

''Las vistas son típicamente creadas para diseño abstracto

complejo de base de datos, para simplificar los permisos de
concesión de acceso a una vista en lugar de múltiples tablas, y
para organizar los datos para la exportación a otros almacenes de
datos
Vistas del sistema
Vistas del sistema, como se señaló en el capítulo
4, las vistas de los objetos del sistema se ven en el
administrador de base de datos.

Hay demasiadas vistas del sistema para

describirlas aquí, y la mayoría se encuentran
documentados en SQL Server 2008. Las vistas del
sistema se pueden dividir en cuatro categorías:
Vistas del esquema de
información
Las vistas del esquema de información están
predefinidas son las vistas que pertenecen a un
esquema especial llamado
INFORMATION_SCHEMA.

SQL Server 2008 implementa la definición estándar

ISO para el INFORMATION_SCHEMA y
proporciona una vista consistente de metadatos de
SQL Server que es generalmente estable desde
una versión a otra.
Vistas de catálogo
Las vistas de catálogo son otro método para recuperar
los metadatos de SQL Server.

Debido a que las vistas de catálogo representan la

interfaz más general en los metadatos de SQL Server,
192 se recomienda utilizar estas vistas sobre la
información del esquema.

Estas proporcionan una gran cantidad de información útil

que puede ser utilizada en la solución de problemas y
mantenimiento de SQL Server 2008. Si se usa en scripts
permanente, asegúrese de especificar las columnas por
su nombre.
Microsoft se reserva el derecho de agregar
columnas adicionales al final de las vistas de
catálogo, lo que podría romper el código existente.
De hecho, esto ocurrió en las vistas del catálogo
elegido entre SQL Server 2005 y SQL Server
2008.
Vistas de administración
dinámica
La gestión dinámica de cambio de vistas del
servidor de información de estado pueden ser
usados para monitorear los procesos de SQL
Server, diagnosticar problemas y ajustar el
rendimiento.

Se describen brevemente en el capítulo 4.

Vistas de compatibilidad
de catálogo
Debido a las tablas del sistema de SQL Server 2000 que ya no
están disponibles, SQL Server 2008 proporciona muchas vistas
que llevan el mismo nombre que las tablas del sistema anterior.

Estas vistas mantienen sólo las características de SQL Server

2008 que son compatibles con SQL Server 2000 y proporcionan
exclusivamente para su uso objetos y scripts diseñados en SQL
Server 2000.

El trabajo de desarrollo futuro debe utilizar las nuevas vistas de

catálogo y mantener solo la información específica de SQL
Server 2008, ya que estos se eliminarán en una versión futura.
Sinónimos
Los sinónimos son un medio para dar un nombre a un
servidor SQL Server con ámbito de esquema de base
de datos de objetos que pueden ser utilizados por las
aplicaciones de base de datos en lugar de su definida
en dos partes, tres partes, o nombres de cuatro partes.

Por ejemplo, una aplicación de base de datos que hace

referencia a una tabla en otro servidor normalmente
tendría que usar un nombre de cuatro partes.

La definición de un sinónimo esencialmente presenta un

alias que se asigna directamente a la tabla sin tener
que especificar el nombre completo de la tabla.
El siguiente código creará un sinónimo
llamado productos en la base de datos
AdventureWorks2008 que hace referencia
a la tabla de dbo.DimProduct en la base de
datos AdventureWorksDW2008:

P79.txt
Observe que la consulta devuelve 606 filas de la
base de datos AdventureWorksDW sin tener que
calificar el nombre del objeto, como el siguiente
ejemplo:
P80.txt

Los sinónimos pueden hacer referencia a vistas,

tablas, procedimientos almacenados y funciones
en cualquier base de datos o un servidor vinculado
a simplificar el acceso a las aplicaciones de datos.
 
Los objetos de programación

Como se señaló anteriormente, la creación y la

lógica detrás de los objetos de programación están
fuera del alcance de este libro, pero el propósito de
los objetos y su uso básico es pertinente.

El administrador de la base de datos tiene que

entender cómo los objetos de programación pueden
afectar al comportamiento de la base de datos.

El aspecto más importante suele ser la seguridad,

que se aborda en el capítulo 6.
 Procedimientos almacenados
Un procedimiento almacenado es una colección de
Transact-SQL o un código administrado que se
almacena en el servidor en una base de datos.

Los procedimientos almacenados de SQL Server son

muy similares a los procedimientos de otros
lenguajes de programación que se utilizan para
encapsular tareas repetitivas.

Apoyan al usuario declara variables, la ejecución

condicional, y muchas otras funciones de
programación.
Los procedimientos almacenados pueden ser
escritos en el tradicional Transact-SQL o en
cualquier otro.

NET administrado, como C # o VB.NET. El capítulo

14 discute las ventajas de utilizar un código
administrado para crear complejos procedimientos
almacenados que empujan los límites de Transact-
SQL.
El principal objetivo de los procedimientos
almacenados es encapsular la funcionalidad
empresarial y crear una lógica de aplicación
reutilizables.

Debido a que los procedimientos almacenados se

almacenan en el servidor, los cambios en la lógica
de negocio se pueden realizar en un solo lugar.
Los procedimientos almacenados también proporcionan
modificaciones controladas de los datos en la base de
datos.

Darle a los usuarios permiso para modificar los datos en

tablas de bases de datos suele ser una muy mala idea.

En cambio, los procedimientos almacenados pueden ser

creados que sólo realicen las modificaciones que requiera
la aplicación. Los usuarios pueden recibir el permiso para
ejecutar el procedimiento almacenado para realizar la
modificación de los datos requeridos.
Los procedimientos almacenados creados por el
usuario pueden ser más eficientes que ad hoc
Transact-SQL, y mucho más seguros.

También reducen drásticamente el número de

paquetes de red necesarios para consultar y
modificar bases de datos y se compilan y
almacenan en caché durante largos periodos de
tiempo para su reutilización eficiente.
Además de los procedimientos almacenados creados por el
usuario, SQL Server proporciona cientos de Procedimientos
almacenados del sistema.

Estos procedimientos almacenados del sistema se utilizan para

recuperar información del sistema, así como para realizar
cambios en los objetos del sistema subyacente.

Van desde simples procedimientos almacenados que devuelven

una lista de todos los usuarios registrados, a los complejos
procedimientos almacenados que crean puestos de trabajo de
base de datos de mantenimiento.

Algunos de estos procedimientos almacenados se tratan en los

capítulos siguientes que se aplican al tema en cuestión.
Funciones
SQL Server 2008 ofrece soporte para tres tipos de
funciones definidas por el usuario: las funciones
escalares, funciones con valores de tabla, y funciones
de agregado.

Las funciones de SQL Server son muy similares a las

funciones en otros lenguajes de programación. Ellas
aceptan parámetros, realizan alguna acción sobre la base
de los parámetros de entrada, y devuelve un valor.

El valor de la tabla de funciones siempre devuelve un tipo

de tabla de datos. Escalares y funciones de agregado
puede devolver cualquier tipo de datos excepto text, ntext
e image.
Las funciones definidas por el usuario se pueden crear con
Transact-SQL o con un código administrado, con la
excepción de las funciones de agregado, que siempre se
crean con un código administrado.

Las funciones definidas por el usuario ofrecen muchos de

los mismos beneficios que los procedimientos
almacenados en cuanto a eficiencia y seguridad se refiere.

Una de las áreas en las que se diferencian es que las

funciones no se les permiten ejecutar cualquier código que
modifica el estado de la base de datos, mientras que a los
procedimientos almacenados si.
Las funciones del sistema se dividen en categorías
en el Explorador de objetos de SQL Server
Management Studio.

Algunas funciones se utilizan para manipular los

datos del usuario (por ejemplo, agregados y las
funciones de cadenas), mientras que otros se
utilizan para recuperar información del sistema
(como la seguridad y las funciones de los
metadatos).
Disparadores (Triggers)
Los factores disparadores son almacenados
Transact-SQL o código administrado de los objetos
que se ejecutan, porque de alguna otra acción
dentro del sistema y no se puede ejecutar
directamente.

Dos tipos de disparadores existen en SQL Server

2008:
*Los disparadores DML y
*Los disparadores DDL
(Se ejecuantan cuando se crea, se modifica o se
elimina una tabla).
Disparadores DML
El lenguaje de manipulación de datos (DML) son
los disparadores ejecutados como resultado de un
comando DML (INSERT, UPDATE, DELETE) que
se produce.
Hay dos tipos de disparadores DML en SQL
Server 2008:
*Después de los disparadores y
*‘‘En lugar de” disparadores.
 Después de los disparadores
(“After Triggers”)

Los disparadores tradicionales se conocen como

factores desencadenantes, ya que después de
ejecutarse ''después de'' la instrucción DML se
ejecuta en la tabla con el disparador (trigger)
definido.

El código del disparador es parte implícita de la

operación que causó la activación de ejecutar.
Cualquier comando ROLLBACK en el cuerpo del
disparador hará que el disparador y el de
transacción asociados sean revertidos.
‘Instead Of’’ Triggers ‘
En lugar de disparadores (‘Instead Of’’ Triggers )

“En lugar de los disparadores” son llamados así porque los

comandos en el disparador se ejecutan en lugar de la
transacción que hizo que el disparador para ser ejecutado.

''En lugar de los disparadores” fueron creados principalmente

como un método de envío de actualizaciones a las tablas de
referencia de las vistas que contienen un operador UNION, ya
que estas vistas no pueden ser directamente actualizadas.

Para obtener información sobre ''en lugar de los disparadores”, y

estas vistas con particiones, echa un vistazo a partir de T-SQL
con Microsoft SQL Server 2005 y 2008 por Paul Turley y Dan
Wood (Wiley, 2008).
Disparadores DDL
Data Definition Language (DDL) (Lenguaje de
definición de datos) se ejecuta como resultado de
un comando DDL (CREATE, DROP, ALTER) que es
ejecutado y puede estar al alcance, ya sea en la
base de datos o el ámbito del servidor.

Los disparadores DDL proporcionan la capacidad

de auditoría o de evitar modificaciones de base de
datos y el servidor.
El siguiente ejemplo muestra cómo crear un
disparador de base de datos a nivel de DDL para
las modificaciones de auditoría realizadas a la
base de datos.

En primer lugar, se crea una tabla para registrar

todos los eventos DDL que se producen en la
base de datos. Para ello, ejecute el siguiente
script:
P81.txt
A continuación, se crea un disparador que ejecuta cada
vez que cualquier evento de nivel DDL se ejecuta.

Este disparador usa una función del sistema llamada

EVENTDATA que devuelve un conjunto de resultados
XML que contienen toda la información sobre el evento
DDL.

El disparador utiliza comandos XQUERY para triturar

los datos XML en un conjunto de resultados
relacionales que se insertan en la tabla de auditoría.
P82.txt
Ahora se prueba el gatillo de crear y eliminar una
tabla llamada TriggerTest y luego se consulta la
tabla de auditoría para ver si ha capturado la
información que deseaba:

P83.txt
 Usted debe obtener dos registros que se asemejan
a la figura (por supuesto, su LoginName y
EventTime pueden variar).

Figure 5-31: DDL Trigger Audit results

Para asegurarse de que este disparador no
interfiera con otros ejercicios más adelante en el
libro, es posible que desee ejecutar el siguiente
comando:

DROP TRIGGER DatabaseAudit ON DATABASE.

 Los ensamblados (assemblies)
Los ensamblados son archivos que contienen objetos de la base de la
programación y se crean utilizando Visual Studio.

Estos pueden incluir procedimientos almacenados, funciones,

disparadores, agregados y tipos de datos por escrito en cualquier
lenguaje administrado, como C # o Visual Basic.NET.

Se puede acceder directamente a ellas en el motor de base de datos a

través de la integración de Common Language Runtime (CLR).

Usando un código administrado ofrece una ventaja significativa sobre las

tradicionales Transact-SQL en ciertas situaciones, tales como aquellas
que requieren operaciones matemáticas intensivas y recurrentes o la
manipulación de cadenas complejas.

El capítulo 14 describe los objetos CLR y la integración con más detalle.

Como se desglosa en el capítulo 14, hay una
tensión definida entre los administradores de bases
de datos y developers.Often, esta tensión se agrava
por la falta del administrador de la base de
conocimientos de programación.

Con la integración de CLR y el motor de base de

datos, es más importante que nunca que los
administradores de bases de datos de programación
se den a entender y se comuniquen con los
desarrolladores que interactúan con sus sistemas.
Los ensamblados CLR se pueden importar de la
base de datos con Visual Studio, Transact-SQL, o
con SQL Server Management Studio. Esta
discusión se centra sólo en Transact-SQL y SQL
Server Management Studio.

Con el fin de que sigas conmigo aquí, se necesita

un archivo para cargar. Más tarde, en el capítulo 14
nos ocuparemos de la creación de este archivo,
pero por ahora, sólo tiene que utilizar su
imaginación.
Para añadir un nuevo ensamble con SQL Server
Management Studio, expanda Bases de datos, elija
AdventureWorks2008, elija Programación, haga clic
en “Ensamble”, y haga clic en ''nuevo ensamble”.

En el cuadro de diálogo de nuevo ensamble (ver

Figura), vaya a ensamble, especifique un
propietario del ensamble, y establezca los permisos
para el montaje.
Figure : New Assembly dialog
El conjunto de permisos de acceso define la cantidad
de ensambles que se deben llevar a cabo para las
acciones contenidas. ''Seguro'', son los límites del
ensamble para la base de datos actual y la conexión.

El “acceso externo” permite que el conjunto interactúe

con el sistema operativo, red y sistema de archivos.
''Sin restricciones'', permite el montaje de todos los
privilegios de acceso externo, así como la posibilidad
de realizar llamadas a un código no administrado.

El permiso del ensamble se discuten en mayor detalle

en los capítulos 6 y 14 años.
Ahora que el ensamble ha sido añadido a la base
de datos, el procedimiento almacenado, una función
de disparo, el tipo, o agregado se puede agregar a
la base de datos que conecta al ensamble.
(Para este proceso exacto, ver el capítulo 14.)
Tipos (Types)
Los tipos son una colección de datos del sistema,
son tipos definidos por el usuario y tipos de tabla de
datos definidos por el usuario, así como todas las
colecciones de esquemas XML utilizados en la base
de datos.

Los datos del sistema de tipos se tratan en el

capítulo 4, así que vamos a mirar los demás tipos.
Tipos de datos definido
por el usuario
Los tipos de datos definidos por el usuario son alias
de tipos de sistemas. Estos alias existen sólo dentro
de la base de datos que se crean.

Los tipos de datos definidos por el usuario son los

más utilizados para proporcionar un nombre de
datos de tipo intuitivo y mantener la consistencia de
los datos a través de tablas de tipo diferente.
Por ejemplo, si le preguntaran a cinco diferentes
desarrolladores de bases de datos como crear una tabla que
almacena información sobre una persona, lo más probable es
que se obtendran cinco soluciones diferentes.

Probablemente la tabla contendrá columnas para el apellido de

la persona, nombre, dirección y número de teléfono, pero lo
más probable es que los cinco diferentes desarrolladores de
base de datos proporcionarán al menos tres diferentes tipos de
datos para almacenar cualquiera de los campos especificados.

Por ejemplo, un desarrollador puede usar un varchar (13) para

representar un número de teléfono, pensando que los números
de teléfono serían representados como (111) 111-1111.
Otro desarrollador puede decidir pensar
globalmente y proporcionar los códigos
internacionales, y así especificar un número de
teléfono de tipo varchar (25). Para evitar posibles
conflictos de tipo posterior, se puede especificar
que el usuario define los tipos de datos que
utilizará.
Para crear un usuario con tipo de datos definido
gráficamente, expanda Bases de datos en el
Explorador de objetos, ampliar
AdventureWorks2008, expanda Programación,
expanda Tipos, haga clic en User-defined data
types’’ y haga clic en ‘‘New User-defined data type.’’
La figura ilustra la creación de un tipo de datos
ZipCode en el esquema dbo que se basa en el
sistema de tipo char (5).

Los tipos de datos Definidos por el usuario también

se puede enlazar a los valores de base de datos y
reglas, especificando en los cuadros de texto
adecuados.

Los valores predeterminados y reglas se describen

más adelante en este capítulo
Figure: Creation of a ZipCode data type.
Hay algunas desventajas de los tipos de datos
definidos por el usuario. Por un lado, no son
transparentes para las aplicaciones de base de
datos.

Por ejemplo, un programador de aplicaciones no

sería capaz de crear una instancia de una variable
en el.
La capa de aplicación que utiliza el tipo de datos ZipCode. El
programador tiene que saber que el tipo de base era un char (5).

Además de la visibilidad de la capa de aplicación, los tipos de

datos definidos por el usuario sólo existen en la base de datos
en la que se crean.

Por ejemplo, un tipo de datos ZipCode en la base de datos

AdventureWorks2008 no puede ser el mismo como un tipo de
datos en la base de datos ZipCode AdventureWorksDW2008.

Además, una vez creado, no puede ser alterado. En otras

palabras, si más adelante se desea cambiar el tipo de datos
ZipCode es un char (9) para mantener zip 4, usted tendría que
quitar y volver a crearlo. Desafortunadamente, con el fin de
quitarlo, no puede ser utilizado en cualquier lugar.
Tipos de tablas definidas
por el usuario
SQL Server 2008 proporciona la capacidad de crear tipos
definidos por el usuario que representan a las definiciones
de tabla.

Usted podría utilizar tipos de tabla definidas por el usuario

para declarar variables o parámetros de procedimientos
almacenados y funciones, lo que es mucho más fácil
trabajar con conjuntos de información.

Para crear un tipo de tabla definida por el usuario, se utiliza

la sentencia CREATE TYPE que proporciona la definición
de la tabla.
El siguiente código crea una estructura
de tabla que se utiliza para representar
un conjunto de clientes y la utiliza como
parámetro de entrada a un
procedimiento almacenado:

P84.txt
El código anterior es únicamente con fines
demostrativos y no funcionará ya que no existe un
procedimiento almacenado llamado
usp_AddCustomers en la base de datos
AdventureWorks.

Definidos por el usuario (User-Defined Types)

Los Definidos por el usuario (UDTs) son muy similares a los
tipos de datos definidos por el usuario, salvo que se crean
mediante un código administrado y se define en una asamblea
que se importa a una base de datos de SQL Server.

Los UDTs pueden ser muy complejos y pueden definir los

tipos de datos personalizados que no tienen ningún tipo de
sistema paralelo. Por ejemplo, un UDT puede ser creado para
definir un cierto número de Seguro Social de tipos de datos
que realmente se almacena como un número, pero no truncar
ceros a la izquierda.

Asimismo, sería capaz de tomar ventaja de las expresiones

regulares en el código administrado para validar el número de
Seguro Social mucho más fácil y más preciso.
La otra ventaja de los UDTs, es que son visibles desde la capa de
aplicación.
Porque se definen en una asamblea, el ensamble puede hacer
referencia en una aplicación de base de datos para que los
parámetros puedan crear una instancia mediante el UDT de
origen.

Sin embargo, los definidos por el usuario no son perfectos, y

puede ser problemático cuando se trata de aplicaciones de bases
de datos cruzadas debido a que la UDT es la base de datos
específica.

Sin embargo, si el mismo ensamble hace referencia en la creación

de la Coordinación en cada base de datos, esta limitación se
reduce. Como se señaló anteriormente, el Capítulo 14 contiene
más información acerca de los ensamblados CLR y los objetos de
base de datos que se pueden crear con ellos, incluyendo los UDT.
Valores Predeterminados (Defaults)
En lugar de crear una restricción predeterminada en
una columna en una tabla, un valor predeterminado
independiente se puede crear en el nivel de base de
datos y ligado a cualquier columna de la tabla en la
base de datos.

Defaults se han marcado para depreciación, y se

recomienda que no los utilicen en cualquier trabajo
de desarrollo. Se encuentran en el nodo de
programación de bases de datos en el Explorador
de objetos, deben ser creados con Transact-SQL.
El siguiente ejemplo muestra cómo crear un
número predeterminado del Seguro Social y
luego se unen a la columna SocSecNum
sobre la tabla dbo.Driver:

P85.txt
Reglas
Las reglas, como por default, han quedado en
des uso. Una regla es como una restricción de
comprobación. Sin embargo, se creará una vez en
el nivel de base de datos y ligado a cualquier
columna que coincida con el tipo de datos
especificado.

El siguiente ejemplo muestra cómo crear una regla

que aplica los datos numéricos en una columna
basada en caracteres y cómo enlazar la regla a la
columna SocSecNum:

P86.txt
Resumen
En este capítulo se ha cubierto una gran cantidad de información, y
apenas hemos arañado la superficie. Un libro entero se podría escribir
con tan sólo la base de datos de SQL Server y todas las características
que incluye, sin embargo, esto no es ese libro.

El propósito de este capítulo era exponerse a muchos de los objetos que

se pueden encontrar en una base de datos de SQL Server y cómo crear y
gestionarlos.

En el capítulo 6, que se verá a continuación vemos cómo asegurar un

servidor de SQL Server 2008, una base de datos, y todos los objetos
asociados que forman parte de SQL Server.

Muchas nuevas características (tales como certificados de servidor SQL,

las credenciales y cifrado) se describen a detalle, y también cubre las
características de seguridad básicas para que pueda asegurarse de que
su servidor es tan seguro como lo pueda ser.