Lenguaje SQL

Es un lenguaje estándar internacional, comúnmente aceptado por los fabricantes de generadores de bases
de datos.

SQL (Structured Query Language) es un lenguaje de consulta para los sistemas de bases de datos
relacionales, pero que no posee la potencia de los lenguajes de programación.

Las sentencias de SQL se clasifican según su finalidad dando origen a 3 sublenguajes:

–DCL (Data Control Language): Lenguaje que incluye órdenes para manejar la seguridad de los datos y de la
base de datos. Permite crear roles y establecer permisos. Sentencias: GRANT, REVOKE

–DDL (Data Definition Language): Lenguaje que incluye ordenes para definir, modificar o borrar objetos de
la base de datos. Sentencias: CREATE, DROP, ALTER

–DML (Data Manipulation Language): Lenguaje de manipulación de datos que permite actualizar y
recuperar los datos almacenados en la base de datos. Este tipo de instrucciones son las que se
desarrollaran en este capítulo. Sentencias: SELECT, INSERT, UPDATE, DELETE

Transact-SQL

Es el lenguaje de programación que proporciona SQL Server para ampliar el lenguaje SQL con los
elementos característicos de los lenguajes de programación: variables, sentencias de control de flujo,
bucles, etc.

Transact SQL es el lenguaje de programación que proporciona SQL Server para extender el SQL estándar
con otro tipo de instrucciones.

Instrucción INSERT
INSERT [ INTO ] objeto
{ [ ( lista_columnas ) ]
{ VALUES ( ( { DEFAULT | NULL | expresión } [ ,...n ] )
[ ,...n ] ) | tabla_derivada | sentencia_ejecutable | DEFAULT VALUES
}
}
Instrucción UPDATE
UPDATE objeto
SET
{ nombre columna = { expresión | DEFAULT | NULL }
| @variable = expresión
| @variable = columna = expresión } [ ,...n ]
[ FROM{ <tabla> } [ ,...n ] ]
[ WHERE { <condición> ]

Instrucción DELETE
DELETE [ FROM ] <objeto>
[ FROM <tabla> [ ,...n ] ]
[ WHERE { <condición> ]
Bases de Datos

Las bases de datos de SQL Server 2008 están formadas por un conjunto de tablas. Estas tablas contienen
datos y otros objetos, como vistas, índices, procedimientos almacenados, funciones definidas por el
usuario y desencadenadores, que se definen para permitir realizar actividades con los datos.

Diseño
–Al diseñar la base de datos, independientemente de su tamaño y complejidad, se llevan a cabo los
siguientes pasos básicos:

>Recopilar información.
>Identificar los objetos.
>Crear el modelo de objetos.
>Identificar los tipos de información para cada objeto.
>Identificar las relaciones entre los objetos.

Consideraciones del diseño de una base de datos.

–Proceso de transacciones en línea (OLTP, Online Transaction Processing): Datos que cambian con
frecuencia. Estas aplicaciones cuentan normalmente con muchos usuarios que realizan transacciones al
mismo tiempo que cambian datos en tiempo real. Consideraciones a tener en cuenta en este tipo de base
de datos: alto grado de normalización, dosificación de índices, ubicación correcta de los datos y pocos
datos históricos.
–Ayuda a la toma de decisiones (OLAP, OnLine Analytical Processing): son óptimas para las consultas de
datos que no impliquen cambios frecuentes en los mismos. Consideraciones a tener en cuenta en este tipo
de base de datos: poca normalización, muchos índices y datos preprocesados,.

Bases de Datos de Sistema

Master: La base de datos master se compone de las tablas de sistema que realizan el seguimiento de la
instalación del servidor y de todas las bases de datos que se creen posteriormente. Asimismo controla las
asignaciones de archivos, los parámetros de configuración que afectan al sistema, las cuentas de inicio de
sesión. Esta base de datos es crítica para el sistema, así que es bueno tener siempre una copia de
seguridad actualizada.

Tempdb: Es una base de datos temporal, fundamentalmente un espacio de trabajo, es diferente a las
demás bases de datos, puesto que se regenera cada vez que arranca SQL Server. Se emplea para las tablas
temporales creadas explícitamente por los usuarios, para las tablas de trabajo intermedias de SQL Server
durante el procesamiento y la ordenación de las consultas.

Model: Se utiliza como plantilla para todas las bases de datos creadas en un sistema. Cuando se ejecuta
una instrucción CREATE DATABASE, la primera parte de la base de datos se crea copiando el contenido de
la base de datos model, el resto de la nueva base de datos se llena con páginas vacías.

Msdb: Es empleada por los servicios SQL Server Agent, Database Mail, Service Broker, log shipping, etc.
para guardar información con respecto a tareas de automatización como por ejemplo copias de seguridad
y tareas de duplicación, asimismo solución a problemas

Para modificar o borrar una tabla se puede usar la herramienta SQL Server Management Studio o las
instrucciones ALTER DATABASE / DROP DATABASE. Al modificar se puede agregar o sacar archivos o grupos
de archivos, modificar atributos, cambiar el nombre o el tamaño de una base de datos. Tambiéb permite
establecer o modificar valores de las opciones. Al borrar una base de datos ésta se borra físicamente del
disco.
Modificación y borrado de BD
Para modificar o borrar una tabla se puede usar la herramienta SQL Server Management Studio o las
instrucciones ALTER DATABASE / DROP DATABASE. Al modificar se puede agregar o sacar archivos o grupos
de archivos, modificar atributos, cambiar el nombre o el tamaño de una base de datos. Tambiéb permite
establecer o modificar valores de las opciones. Al borrar una base de datos ésta se borra físicamente del
disco.

Opciones de BD

Una vez creada la base de datos, se pueden establecer distintos valores para sus opciones usando SQL
Server Management Studio o la sentencia Transact-SQL ALTER DATABASE.

>El cambio de opciones se hace para cada base de datos por separado.
>Si se desea modificar varias simultáneamente podría modificarse la base de datos de sistema Model y
toda base de datos creada a continuación contendrá las modificaciones.

AUTO_CREATE_STATISTICS: Crea estadísticas en forma automática necesarias para la optimización de

consultas. El valor predeterminado es ON.

AUTO_UPDATE_STATISTICS: Actualiza automáticamente las estadísticas que están desactualizadas. El valor

predeterminado es ON.

AUTO_CLOSE; Si está en ON cierra la base de datos automáticamente cuando el último usuario cierra su
sesión. El valor predeterminado es OFF (excepto para la edición Express)

AUTO_SHRINK: Si está en ON la base de datos se encoge automáticamente en forma periódica. El valor

predeterminado es OFF.

READ_ONLY / READ_WRITE: Controla si los usuarios pueden modificar los datos. El valor predeterminado
es READ_WRITE .

SINGLE_USER / RESTRICTED_USER / MULTI_USER: Controla que usuarios pueden conectarse a la base de

datos. El valor predeterminado es MULTI_USER. Cuando se especifica SINGLE_USER, sólo se puede
conectar un usuario a la base de datos en un momento dado. Todas las demás conexiones de usuario se
desconectan. Cuando se especifica RESTRICTED_USER, sólo pueden conectarse a la base de datos los
miembros de la función fija de base de datos db_owner y los de las funciones fijas de servidor dbcreator y
sysadmin, pero no se limita la cantidad de miembros. Cuando se especifica MULTI_USER, se permite el
acceso de todos los usuarios que cuenten con los permisos adecuados para conectarse a la base de datos.

RECOVERY MODEL: FULL / SIMPLE / BULK_LOGGED: El valor predeterminado es FULL. Provee un modelo
de recuperación completo ante fallas. BULK_LOGGED no usa el registro de transacciones para ese tipo de
movimiento. SIMPLE recupera la base de datos solo desde el último backup completo o diferencial.

PAGE_VERIFY: Permite detectar entradas de E/S incompletas. CHECKSUM: guarda un valor calculado en la
cabecera de la página basado en su contenido. Este valor es recalculado y comparado con los datos de la
página para controlarlas. Es el valor predeterminado. TORN_PAGE_DETECTION: guarda un bit específico
por cada sector de 512 bytes dentro de la cabecera de la página. Este bit se usa para el control.

SQL ANSI_NULL_DEFAULT: Permite al usuario controlar el uso predeterminado del valor nulo de una
columna al crear o modificar una tabla. El valor predeterminado es OFF o sea NOT NULL.
ANSI_NULLS; Cuando está en ON, todas las comparaciones con nulos devuelven nulos. Si está en OFF solo
devuelve nulo si ambos valores son nulos. El valor predeterminado es OFF

QUOTED_IDENTIFIER: Cuando se especifica ON, se pueden utilizar comillas dobles para encerrar los
identificadores delimitados. Cuando se especifica OFF, los identificadores no pueden ir entre comillas y
deben adaptarse a todas las reglas de Transact-SQL que se aplican a los identificadores (Usar [] para
delimitar identificadores).

Registro de transacciones

El registro de transacciones se utiliza para garantizar la integridad de los datos de la base de datos y para la
recuperación de datos. Comprender la arquitectura física puede mejorar la eficacia en la administración de
registros de transacciones.

SQL Server guarda cada transacción usando el registro de transacciones para mantener la consistencia de
la base de datos y ayudar a recuperarlos en el caso de fallas de la misma. Las transacciones son grabadas
primero en el registro de transacciones y automáticamente pasadas a los datos.

El proceso es el siguiente:

–La aplicación envía los datos

–Cuando la modificación es ejecutada, las páginas afectadas son cargadas en memoria, si las mismas no
hubieran sido cargadas anteriormente.

–Cada modificación es guardada en el registro de transacciones.

–El proceso de punto de comprobación (CheckPoint) graba toda la transacción en la base de datos.

Puntos de comprobación (CheckPoint)

–El Motor de base de datos de SQL Server genera puntos de comprobación automáticos. El intervalo entre
puntos de comprobación automáticos se basa en el espacio del registro utilizado y en el tiempo
transcurrido desde el último punto de comprobación. El intervalo de tiempo entre los puntos de
comprobación automáticos puede ser muy variable y largo si se realizan pocas modificaciones en la base
de datos. Los puntos de comprobación automáticos también se pueden producir con frecuencia si se
modifican muchos datos.

Información de BD

Se puede consultar información sobre una base de datos usando SQL Server Management Studio o
consultando las vistas de sistemas, funciones de sistema o procedimientos almacenados de sistema que
provee SQL Server.

Vista del Catálogo

–Las vistas de catálogo devuelven información utilizada por el Motor de base de datos de SQL Server.

Vistas de catálogo de archivos y bases de datos:

–[Link] que devuelve un registro por cada base de datos.

–sys.database_files que devuelve un registro por cada archivo de una base de datos.

Objetos:

–[Link] que devuelve un registro por cada tabla de una base de datos.

–[Link] que devuelve un registro por cada vista de una base de datos.

–[Link] que devuelve un registro por cada columna de un objeto.

Seguridad:

–sys.database_permissions que devuelve un registro por cada permiso definido en una base de datos.

–sys.database_role_members que devuelve un registro por cada miembro de un rol de una base de datos.

Funciones del Sistema

–DB_ID: Devuelve el número de identificación (Id.) de esa base de datos.

–DB_NAME: Devuelve el nombre de la base de datos.

–FILE_ID: Devuelve el número de identificación del archivo (Id.) del nombre de archivo lógico dado de la
base de datos actual.

–FILE_NAME: Devuelve el nombre del archivo lógico dado de la base de datos actual.

Procedimientos Almacenados de Sistema

–Sp_Databases: Lista las bases de datos disponibles de un Server

–Sp_HelpDB: Información sobre las bases de datos de un servidor

–Sp_Help: Presenta información acerca de un objeto de base de datos (cualquier objeto de la vista de
compatibilidad [Link]), un tipo de datos definido por el usuario o un tipo de datos. Puede
ejecutarse sin parámetros, entonces muestra la información sobre todos los objetos o puede pasarse como
parámetro el nombre del objeto a consultar.

Archivos de base de datos

Las bases de datos de SQL Server utilizan tres tipos de archivos:

–Archivos de datos principales: El archivo de datos principal es el punto de partida de la base de datos y
apunta a los otros archivos de la base de datos. Cada base de datos tiene un archivo de datos principal. La
extensión recomendada para los nombres de archivos de datos principales es .mdf.
–Archivos de datos secundarios: Los archivos de datos secundarios son todos los archivos de datos menos
el archivo de datos principal. Puede que algunas bases de datos no tengan archivos de datos secundarios,
mientras que otras pueden tener varios archivos de datos secundarios. La extensión de nombre de archivo
recomendada para los archivos de datos secundarios es .ndf.

–Archivos de registro: Los archivos de registro almacenan toda la información de registro que se utiliza
para recuperar la base de datos. Como mínimo, tiene que haber un archivo de registro por cada base de
datos, aunque puede haber varios. La extensión de nombre de archivo recomendada para los archivos de
registro es .ldf.

SQL Server no exige las extensiones de nombre de archivo .mdf, .ndf y .ldf, pero estas extensiones ayudan
a identificar las distintas clases de archivos y su uso.

Los objetos y archivos de una base de datos se pueden agrupar en grupos de archivos con fines de
asignación y administración. Hay dos tipos de grupos de archivos:

–Principal: El grupo de archivos principal contiene el archivo de datos principal y los demás archivos
asignados específicamente a otro grupo de archivos. Todas las páginas de las tablas del sistema están
asignadas al grupo de archivos principal. (PRIMARY)

–Definidos por el usuario: Los grupos de archivos definidos por el usuario son los grupos de archivos
especificados mediante la palabra clave FILEGROUP en la instrucción CREATE DATABASE o ALTER
DATABASE. Los archivos de registro nunca forman parte de un grupo de archivos. Ningún archivo puede
pertenecer a más de un grupo de archivos. Las tablas, los índices y los datos de objetos grandes se pueden
asociar a un grupo de archivos específico.

Mejoras en el rendimiento

–El uso de archivos y grupos de archivos permite mejorar el rendimiento de la base de datos al permitir
crear la base de datos en varios discos, varios controladores de disco o sistemas RAID (matriz redundante
de discos independientes).

Consideraciones para la creación y organización de grupos de archivos

–Separar datos de solo lectura de los modificables

–Separar índices de sus tablas, mejora la performance

–Hacer copias de seguridad por archivo en vez de hacer copias de toda la base de datos

–Tablas e índices con el mismo tipo de mantenimiento deberían estar en el mismo grupo de archivos

–Cambiar siempre el grupo de archivos predeterminado de manera de no incluir automáticamente las

tablas de usuario en Primary

–Organizar tablas con particiones en múltiples grupos de archivos. Es una buena manera de separar datos
con distintos niveles de acceso y no trabajar con tablas excesivamente grandes.

Esquemas (Schemas)
El comportamiento de los esquemas cambió en SQL Server 2008. Los esquemas ya no son equivalentes a
los usuarios de la base de datos; cada esquema ahora es un espacio de nombres (namespace) distinto que
existe de forma independientemente del usuario de base de datos que lo creó.

Todas las bases de datos contienen un esquema llamado dbo. Es el esquema predeterminado para todos
los usuarios cuando no se define explícitamente el esquema.

Ventajas

–Mayor flexibilidad para organizar la base de datos usando nombres de espacio, ya que de esta manera los
objetos no dependen del usuario que lo creo.

–Manejo de permisos mejorada, ya que los permisos pueden ser asignados a los esquemas y no
directamente a cada objeto.

–Al dar de baja un usuario no es necesario renombrar los objetos que le pertenecían.

Una instantánea de base de datos es una vista estática de sólo lectura de una base de datos denominada
base de datos de origen.

Una instantánea de base de datos es coherente en cuanto a las transacciones con la base de datos de
origen tal como existía en el momento de la creación de la instantánea.

Las instantáneas de la base de datos dependen de la base de datos de origen. Deben residir en la misma
instancia de servidor que la base de datos.

Las instantáneas se pueden utilizar para crear informes. Además, en el caso de que se produzca un error de
usuario en una base de datos de origen, ésta se puede revertir al estado en que se encontraba cuando se
creó la instantánea.

Una instantánea de la base de datos puede ser útil inmediatamente antes de realizar un cambio
importante en una base de datos, como cambiar el esquema o la estructura de una tabla.

Instantáneas de BD (Snapshot)

Creación:

CREATE DATABASE nombreSnapshot ON

(NAME= nombreSnapshot,
FILENAME = nombreArchivoSnapshot )
AS SNAPSHOT OF baseDatosOrigen

Lectura sobre una instantánea de la base de datos.

Tamaño de la instantánea de la base de datos.

Sentencias TSQL

Variables
–Declare
–Set
–Select
Operadores
–Aritméticos
–Lógicos
–Comparativos
–De Concatenación

Sentencias TSQL

Comentarios
–De Línea: --
–De Bloque: /* */

Control de Flujo
–Begin / End
–If / Else
–Return
–While
–Case

Sentencias TSQL

Novedades de SQL Server 2008

–Operadores de asignación
–+=, -=, *=, /=, %=