SQL

SERVER
Astay Systems 1
 ¿Qué es una base de datos?

Una base de datos es una herramienta que recopila datos, los organiza y los
relaciona para que se pueda hacer una rápida búsqueda y recuperar con
ayuda de un ordenador. Hoy en día, las bases de datos también sirven para
desarrollar análisis. Las bases de datos más modernas tienen motores
específicos para sacar informes de datos complejos.

Astay Systems 2
 Tipos de bases de datos

Los diferentes tipos de bases de datos pueden ser clasificados según su

utilidad, el área de aplicación, entre otras. A continuación se presentan los
principales tipos de bases de datos.

Por la variabilidad

Bases de datos estáticas: son aquellas que solo se emplean para la

lectura o consulta de información, la cual no puede ser alterada.
Generalmente, se trata de datos históricos que se emplean para realizar
análisis de información en específico, por ello son típicas de la
inteligencia empresarial.
Bases de datos dinámicas: son bases de datos que pueden ser
consultados y actualizados según las necesidades que se presenten.
Astay Systems 3
 BASES DE DATOS RELACIONALES
Comencemos por la que seguramente ya conoces, las bases de datos
relacionales, este es el modelo que por lo regular enseñan en la universidad
y en el trabajo. Este tipo de bases de datos surgieron en los años 70 como
una solución para almacenar información de acuerdo a un esquema que
permite mostrar la información en forma de tablas, con columnas y filas.
Estos son los gestores de bases de datos relacionales más conocidos y
utilizados:

Oracle
MySQL
Microsoft SQL Server
PostgreSQL
Astay Systems 4
 La integridad de los datos es un factor sumamente importante al momento
de diseñar una base de datos de tipo relacional.

Los sistemas RDBMS (Relational Data Base Management System) se rigen

por el principio ACID.

Atomicity: Cuando una operación se realiza sobre los datos, esta debe
ser absoluta, esto quiere decir que todos los pasos deben ejecutarse
sobre una sola operación, o bien no debe ejecutarse ninguno.
Consistency: Cualquier operación realizada en la base de datos debe
llevarla de un estado válido a otro, todos los datos deben ser
consistentes, por ejemplo en los tipos (string, numérico, boolean)

Astay Systems 5
 Isolation: Ninguna operación puede o debe afectar a otras.
Durability: Cualquier operación realizada en una base de datos debe ser
permanente una vez es ejecutada, incluso si ocurre un fallo inesperado
en el sistema, los datos deben ser almacenados en su último estado
conocido.

Astay Systems 6
 VENTAJAS
Al ser una tecnología bastante madura, cuenta con una documentación
muy extensa y una comunidad bastante activa. Cualquier duda puede ser
resuelta con un poco de investigación.
Los estándares SQL se encuentran bien definidos y son ampliamente
aceptados.
Una gran cantidad de desarrolladores cuentan con amplia experiencia en
esta tecnología.
Toda base de datos relacional debe cumplir con los principios ACID, por
lo cual los datos son confiables.

Astay Systems 7
 DESVENTAJAS
Hasta hace un tiempo, las bases de datos relacionales eran la primera
opción al momento de desarrollar casi cualquier aplicación, eran robustas,
confiables y ampliamente conocidas por los desarrolladores, pero pronto
surgió un problema, las bases de datos relacionales tienen muy poca
escalabilidad, esto quiere decir que si nosotros queremos agregar una nueva
funcionalidad a nuestra aplicación probablemente será necesario un
rediseño del modelo de nuestra base de datos, lo cual requiere tiempo y
recursos por parte del equipo de desarrollo.

Astay Systems 8
 Las siglas de SQL representan Structured Query Language (lenguaje de
consulta estructurada). Este código de base de datos se popularizo gracias
a IBM y Oracle en los años 70, donde se convirtió por excelencia en el
lenguaje designado de los sistemas de gestión de base de datos. En la
actualidad, los más grandes servicios de SQL son SQL Server, de Microsoft,
y MySQL, desarrollado por Oracle.

El lenguaje de desarrollo utilizado es Transact-SQL (TSQL), una

implementación del estándar ANSI del lenguaje SQL.

Astay Systems 9
 Microsoft SQL Server
Microsoft SQL Server es un sistema de gestión de base de datos relacional,
desarrollado por la empresa Microsoft.

El lenguaje de desarrollo utilizado (por línea de comandos o mediante la

interfaz gráfica de Management Studio) es Transact-SQL (TSQL), una
implementación del estándar ANSI del lenguaje SQL, utilizado para
manipular y recuperar datos (DML), crear tablas y definir relaciones entre
ellas (DDL).

SQL Server ha estado tradicionalmente disponible solo para sistemas

operativos Windows de Microsoft, pero desde 2016 está disponible para
GNU/Linux, y a partir de 2017 para Docker también.
Astay Systems 10
 Cuales son sus características:

Soporte de transacciones.
Soporta procedimientos almacenados.
Incluye un entorno gráfico.
Permite trabajar en modo Cliente-Servidor.

Astay Systems 11
 PROGRAMACIÓN
T-SQL:

Primer medio de interacción con el Servidor, el cual permite realizar las

operaciones claves en SQL Server, incluyendo la creación y modificación de
esquemas de base de datos, inserción y modificación

Astay Systems 12
 Características de SQL

El Lenguage de Definición de Datos (LDD):

Proporciona comandos para la creación, borrado y modificación de
esquemas relacionales.
El Lenguaje de Manipulación de Datos (LMD):
Basado en el álgebra relacional y el cálculo relacional permite realizar
consultas y adicionalmente insertar, borrar y actualizar de tuplas.
Ejecutado en una consola interactiva.
Embebido dentro de un lenguaje de programación de propósito
general.
Autorización, definición de usuarios y privilegios.
Control de Transacciones
Astay Systems 13
 Comandos
CREAR UNA BASE DATOS

CREATE DATABASE DbSSTT

ON PRIMARY
(NAME = 'DbSSTT', --! Nombre logico del archivo
FILENAME = 'C:\[Link]'
SIZE =10, -- Por defecto esta en MB, GB Son entros
MAXSIZE =50, -- Tama;o max de la DB
FILEGROWTH =5) --Crecimiento del archivo, tambien

LOG ON --! Archivo de transacciones, mantiene la integridad de los datos

(NAME = 'DbSSTTLog',
FILENAME = '[Link]',
SIZE = 5,
MAXSIZE =25,
FILEGROWTH = 5);

Astay Systems 14
 -
-
IMPORTANCIA DE LA SEGURIRDAD
(!)
-
Astay Systems 15
 Introducción Teórica

La protección de la información (controlar el acceso a los datos de una

organización) se parece mucho a la protección de una estructura física. Por
ejemplo, imagine que tiene su propio negocio y el edificio que lo alberga
también es de su propiedad no querrá que el público en general pueda
acceder al edificio; solo deberían tener acceso los empleados. Sin embargo,
también necesita restricciones para las zonas a las que los empleados
pueden acceder, porque solo los contables deberían tener acceso al
departamento de contabilidad y casi nadie debería tener acceso a su
despacho; debe instalar diversos sistemas de seguridad.

Astay Systems 16
 La protección de SQL Server (su “edificio”) se basa en este concepto; nadie
puede entrar a menos que se le
conceda acceso y, una vez que los usuarios están dentro, los diferentes
sistemas de seguridad mantienen las
aéreas confidenciales a salvo de miradas indiscretas.

Astay Systems 17
 SQL Server define 4 conceptos básicos:

1. Login de SQL Server

2. Usuario de la Base de datos
3. Role de la BD
4. Role de una aplicación

Inicios de sesión (Login):

Un login es la habilidad de utilizar una instancia del Servidor SQL, está

asociado con un usuario de Windows o con un usuario de SQL. Son
autenticados contra SQL Server por lo tanto son los accesos al servidor,
pero esto no quiere decir que puedan acceder a las bases de datos o a otros
objetos. Para poder acceder a cada una de las bases de datos se necesita
de un usuario (user).
Astay Systems 18
 Usuario de la base de datos (User):

El usuario de la base de datos es la identidad del inicio de sesión cuando

está conectado a una base de datos.
El usuario de la base de datos puede utilizar el mismo nombre que el inicio
de sesión, pero no es necesario.

Los Logins son asignados a los usuarios

Los grants se les asignan a los usuarios.
A los usuarios se le asignan sus propios Esquemas(schemas)

Astay Systems 19
 Usuarios por defecto en una BD

dbo: Propietario. No puede ser borrado de la BD.

Guest: Permite a usuarios que no tienen cuenta en la BD, que accedan a
ella, pero hay que hacerle permiso explícitamente.
Information_schema: Permite ver los metadatos de SQL Server
sys: Permite consultar las tablas y vistas del sistema, procedimientos
extendidos y otros objetos del catálogo del sistema.

Astay Systems 20
 Los usuarios pueden pertenecer a Roles.

Todos los usuarios son miembros del Role “Public”

El login “sa” está asignado al usuario dbo en todas las base de datos.
Da acceso a la base de datos, pero esto tampoco quiere decir que pueda
hacer cualquier operación sobre la base de datos, en principio no puede
hacer casi nada, salvo que se le vaya asignando roles y otros privilegios
para hacerle permisos de acceso a los objetos de esa base de datos.

Roles:
Los Roles pueden existir a nivel de instancia o base de datos.
A nivel de Instancia:

Los logins pueden ser otorgados roles llamados “server roles”.

No se pueden crear Roles nuevos.
Astay Systems 21
 A nivel de Base de Datos

Los usuarios de base de datos pueden ser otorgados roles.

Se pueden crear roles nuevos.

Role de una Aplicación

Un role de aplicación sirve para asignarle permisos a una aplicación:

Tiene un password
No contiene usuarios

Astay Systems 22
 Jerarquía de permisos

El Motor de base de datos administra un conjunto jerárquico de entidades

que se pueden proteger mediante permisos. Estas entidades se conocen
como elementos protegibles. Los protegibles más prominentes son los
servidores y las bases de datos, pero los permisos discretos se pueden
establecer en un nivel mucho más específico.
En la siguiente figura se muestra las relaciones entre las jerarquías de
permisos del Motor de base de datos

Astay Systems 23
 Usuarios de BD y esquemas

Colección de objetos de la BBDD cuyo propietario es un único principal y

forma un único espacio de nombres ( conjunto de objetos que no pueden
tener nombres duplicados)
[Link]
Los objetos ahora pertenecen al esquema de forma independiente al usuario
Beneficios:

El borrado de un usuario no requiere que tengamos que renombrar los

objetos.
Resolución de nombres uniforme.
Gestión de permisos a nivel de esquema.

Astay Systems 24
 Una BBDD puede contener múltiples esquemas
Cada esquema tiene un propietario (principal): usuario o rol
Cada usuario tiene un default schema para resolución de nombres
La mayoría de los objetos de la BBDD residen en esquemas
Creación de objetos dentro de un esquema requiere permisos
CREATE y ALTER o CONTROL sobre el esquema

Astay Systems 25
 Modificando la DB

Pero que pasa cuando llegamos al tope del dise;o, se deja de registrar y la
DB pasa ha ser de solo lectura.
La modificamos:

ALTER DATABASE DbSSTT

MODIFY FILE
(NAME = Geotec,
MAXSIZE = 100MB);

Astay Systems 26
 Comandos
DELETE

Nos sirve para eliminar registros registros y total.

USE DbSSTT
DELETE FROM Users ---! Peligro
WHERE id = 2

Al cargar la informacion nuevamente revisamos que el autonumerico sigue

contando.

Astay Systems 27
 TRUNCATE

Al igual que DELETE este borra los registros pero a la vez reinicia el
autonumerico.

TRUNCATE TABLE Users

UPDATE

Nos siver para actualizar el campo selecionado con el argumento deseado

donde debe cumplir un criterio.

UPDATE Productos SET NOMBRE = 'Cable HDMI'

WHERE Id = 5

Astay Systems 28
 Crear y eliminar tablas
Create table

CREATE DATABASE Mibase

USE MiBase
CREATE TABLE MiTabla
(
Id_User INT IDENTITY PRIMARY KEY,
Name_User VARCHAR(20),
PASSWORD VARCHAR(15),
)

Drop Table

DROP TABLE MiTable

Astay Systems 29
 Tipos de datos
Tipos de datos de cadena

Tipo de Tamaño
Descripción Almacenamiento
datos máximo
Cadena de caracteres de 8,000
char (n) Ancho definido
ancho fijo caracteres
varchar Cadena de caracteres de 8,000 2 bytes + número
(n) ancho variable caracteres de caracteres
varchar Cadena de caracteres de 1,073,741,824 2 bytes + número
(max) ancho variable caracteres de caracteres
Cadena de caracteres de 2 GB de datos 4 bytes + número
text 30
Astay Systems ancho variable de texto de caracteres
 Tipo de
Descripción Tamaño máximo Almacenamiento
datos
Cadena Unicode de Ancho definido x
nchar 4.000 caracteres
ancho fijo 2
Ancho de cadena
nvarchar 4.000 caracteres
Unicode
nvarchar Ancho de cadena 536,870,912
(max) Unicode caracteres
Ancho de cadena 2 GB de datos de
ntext
Unicode texto
Cadena binaria de
binary (n) 8,000 bytes
ancho fijo

Astay Systems 31
 Tipos de datos numéricos

Tipo de
Descripción Almacenamiento
datos
bit Entero que puede ser 0, 1 o NULL
tinyint Permite números enteros de 0 a 255 1 byte
Permite números enteros entre -32,768 y
smallint 2 bytes
32,767
Permite números enteros entre
int 4 bytes
-2,147,483,648 y 2,147,483,647

Astay Systems 32
 Tipo de
Descripción Almacenamiento
datos
Permite números enteros entre
bigint -9,223,372,036,854,775,808 y 8 bytes
9,223,372,036,854,775,807
Datos monetarios de -214,748.3648 a
smallmoney 4 bytes
214,748.3647
Datos monetarios de
money -922,337,203,685,477.5808 a 8 bytes
922,337,203,685,477.5807

Astay Systems 33
 Tipo
de Descripción Almacenamiento
datos
Números de escala y precisión fijos. Permite
números de -10 ^ 38 +1 a 10 ^ 38 -1. El
parámetro p indica el número total máximo de
dígitos que se pueden almacenar (tanto a la
izquierda como a la derecha del punto
decimal
decimal). p debe ser un valor de 1 a 38. El 5-17 bytes
(p, s)
valor predeterminado es 18. El parámetro s
indica la cantidad máxima de dígitos
almacenados a la derecha del punto decimal.
s debe ser un valor de 0 a p. El valor
predeterminado es 0
Astay Systems 34
 Tipo
de Descripción Almacenamiento
datos
Datos del número de precisión flotante desde
-1.79E + 308 a 1.79E + 308. El parámetro n
float indica si el campo debe contener 4 u 8 bytes.
4 u 8 bytes
(n) float (24) contiene un campo de 4 bytes y float
(53) contiene un campo de 8 bytes. El valor
predeterminado de n es 53.
Datos numéricos de precisión flotante desde
real 4 bytes
-3.40E + 38 a 3.40E + 38

Astay Systems 35
 Tipos de datos de fecha

Tipo de
Descripción Almacenamiento
datos
Del 1 de enero de 1753 al 31 de
datetime diciembre de 1999, con una precisión 8 bytes
de 3,33 milisegundos
Desde el 1 de enero de 0001 hasta el
datetime2 31 de diciembre de 1999, con una 6-8 bytes
precisión de 100 nanosegundos
Del 1 de enero de 1900 al 6 de junio de
smalldatetime 4 bytes
2079 con una precisión de 1 minuto

Astay Systems 36
 Tipo de
Descripción Almacenamiento
datos
Almacenar una fecha solamente. Del 1
date de enero de 0001 al 31 de diciembre de 3 bytes
9999
Almacenar un tiempo solo con una
time 3-5 bytes
precisión de 100 nanosegundos
Lo mismo que datetime2 con la adición
datetimeoffset 8-10 bytes
de un desplazamiento de zona horaria
Almacena un número único que se
timestamp actualiza cada vez que se crea o
modifica una fila.

Astay Systems 37
 INSERTAR DATOS
INSERT TO

INSERT INTO miTabla values('JuanMansilla','12345'); -- Cada linea termina con punto y coma

INSERT INTO miTabla values('MiguelIglesias','12345'); -- Cada linea termina con punto y coma

INSERT INTO miTabla values('PedroInfantes','12345'); -- Cada linea termina con punto y coma

Revisamos lo que hemos agregado:

SELECT * FROM miTabla

Astay Systems 38
 Esquemas en SQL
Se utilizan para organizar o agrupar los conjuntos de objetos de una base de
datos. Permitiendo una mejor administración de permisos al momento de
asignarlos a los usuarios.

Entre los objetos que utilizan esquemas podemos encontrar.

Tablas
Diagramas
Procedimientos almacenados
Funciones
Vistas
Astay Systems 39
 Que esquemas por defecto tenemos?
Una base de datos crea por defecto algunos esquemas que se utilizan al
crear objetos, los principales son.

sys
guest
INFORMATION_SCHEMA
dbo --> Predeterminado

No podemos eliminar estos esquemas por defecto.

Astay Systems 40
 Crear esquemas con T-SQL
Como se había mencionado se pueden crear esquemas para clasificar los
objetos permitiendo mejorar la administración.

Utilizando el siguiente código se puede crear un esquema personalizado.

CREATE SCHEMA gtc AUTHORIZATION dbo;

Con la instrucción creamos un esquema de nombre gtc.

AUTHORIZATION solicita los permisos a dbo para que el nuevo esquema
sea por defecto, si al crear un objeto no se especifica el esquema.

Astay Systems 41
 Asignar esquema
La asignación de esquemas es tan sencilla como agregar el nombre a un
objeto nuevo para la base de datos.

Vamos a crear una nueva tabla y la asignaremos al esquema creado

anteriormente.

CREATE TABLE [Link](

Id INT,
Nombre VARCHAR(20)
);

En la posición del nombre de la tabla se antepone el esquema gtc asignando

mediante el punto a la nueva tabla de nombre Persona.
Astay Systems 42
 Otra ventaja de trabajar con esquemas es que podemos crear un usuario al
final lo podemos borrar y no afectar a la base de datos ya que el propietario
del esquema es el usuario.

Ejercicio

Astay Systems 43
 COMANDO GO
El comando GO no es una instrucción de Transact-SQL, sino un comando
especial reconocido por varias utilidades de MS, incluido el editor de código
de SQL Server Management Studio.

El comando GO se usa para agrupar comandos SQL en lotes que se envían

al servidor. Los comandos incluidos en el lote, es decir, el conjunto de
comandos desde el último comando GO o el inicio de la sesión, deben ser
lógicamente consistentes.

Nota: No pueden existir mas comandos en la misma linea donde se

encuentra GO.

Astay Systems 44
 COMANDO IDENTITY
IDENTITY permite indicar el valor de inicio de la secuencia y el incremento
Un campo definido como IDENTITY generalmente se establece como clave
primaria.
Un campo IDENTITY no es editable, es decir, no se puede ingresar un
valor ni actualizarlo.

Y si queremos desactivar esta caracteristica de nuestra tabla?

SET INDETITY_INSERT Product ON --> Cambiamos por Off

Astay Systems 45
 RELACIONES ENTRE TABLAS
CREATE TABLE Productos
(
Cod VARCHAR(4) PRIMARY KEY NOT NULL,
Nom_Prod VARCHAR(50) NOT NULL,
)
GO
CREATE TABLE Facturar
(
Fecha DATE NOT NULL,
nFactura INT PRIMARY KEY NOT NULL,
Doc_Indetidad INT NOT NULL,
NOM_cLIENTE VARCHAR(50) NOT NULL,
)

Astay Systems 46
 CREATE TABLE Detalle
(
Cod VARCHAR(4) NOT NULL,
nFactura INT NOT NULL,
Cantidad INT NOT NULL,
Precio MONEY NOT NULL,
Importe MONEY NOT NULL,

CONSTRAINT FK_Detalle_Productos --Nombre de la restriccion no es necesario

FOREIGN KEY (Cod) REFERENCES Productos(Cod)
ON UPDATE CASCADE -- Reglas de actualizacion
ON DELETE CASCADE -- Reglas de Eliminacion

CONSTRAINT Fk_Detalle_Factura
FOREIGN KEY (nFACTURA) REFERENCES FACTURAR(nFACTURA)
ON UPDATE CASCADE
ON DELETE CASCADE
)

Vemos el diagrama de la base de datos

Astay Systems 47
 Que pasa si ya tengo las tablas?

USE Ventas
ALTER TABLE Detalle
ADD CONSTRAINT Fk_Detalle_Productos
FOREIGN KEY (Cod) REFENCES Productos(Cod)
ON UPDATE CASCADE
ON DELETE CASCADE,

CONSTRAINT Fk_Detalle_Facturar
FOREIGN KEY(nFactura) REFERENCES Facturar(nFactura)
ON UPDATE CASCADE
ON DELETE CASCADE

Astay Systems 48
 Indices
CREATE INDEX se utiliza para crear índices en una tabla.

Un índice sirve para buscar datos rápidamente, y no tener que recorrer toda
la tabla secuencialmente en busca alguna fila concreta.

Si una columna es índice de una tabla, al buscar por un valor de esa

columna, iremos directamente a la fila correspondiente. La búsqueda así es
mucho más óptima en recursos y más rápida en tiempo.
Si esa columna de búsqueda no fuese índice, entonces tendríamos que
recorrer de forma secuencial la tabla en busca de algún dato. Por eso, es
importante crear un índice por cada tipo de búsqueda que queramos hacer
en la tabla.
Astay Systems 49
 Actualizar una tabla con índices tarda más tiempo porque también hay que
actualizar los índices, así que solo se deben poner índices en las columnas
por las que buscamos frecuentemente.

Se pueden crear índices ÚNICOS, es decir, índices que no admiten valores

duplicados.
Hay dos tipos de Índices en SQL Server:

Índices Agrupados
Índices No Agrupados

Astay Systems 50
 Índices Agrupados

Un índice agrupado define el orden en el cual los datos son físicamente

almacenados en una tabla. Los datos de las tablas pueden ser ordenados
sólo en una forma, por lo tanto, sólo puede haber un índice agrupado por
tabla.

CREATE DATABASE schooldb

CREATE TABLE student

(
id INT PRIMARY KEY, -- Es es el indice agrupado
Names VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
DOB datetime NOT NULL,
total_score INT NOT NULL,
city VARCHAR(50) NOT NULL
)
Astay Systems 51
 USE schooldb
INSERT INTO student
VALUES
(6, 'Kate', 'Female', '03-JAN-1985', 500, 'Liverpool'),
(2, 'Jon', 'Male', '02-FEB-1974', 545, 'Manchester'),
(9, 'Wise', 'Male', '11-NOV-1987', 499, 'Manchester'),
(3, 'Sara', 'Female', '07-MAR-1988', 600, 'Leeds'),
(1, 'Jolly', 'Female', '12-JUN-1989', 500, 'London'),
(4, 'Laura', 'Female', '22-DEC-1981', 400, 'Liverpool'),
(7, 'Joseph', 'Male', '09-APR-1982', 643, 'London'),
(5, 'Alan', 'Male', '29-JUL-1993', 500, 'London'),
(8, 'Mice', 'Male', '16-AUG-1974', 543, 'Liverpool'),
(10, 'Elis', 'Female', '28-OCT-1990', 400, 'Leeds');

-- Para ver que Index tenemoas almacenados

USE SchoolDB
EXECUTE Sp_helpIndex Student

Astay Systems 52
 Índices agrupados personalizados

Primero eliminamos el indice que tengamos, despues:

use schooldb

CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score

ON student(gender ASC, total_score DESC)

Un índice que es creado en más que una columna es llamado “índice

compuesto”, en este caso primero se ordena primero por Gender y luego por
Total_Score.

Astay Systems 53
 Índices no grupados

Un índice no agrupado no ordena los datos físicos dentro de la tabla. De

hecho, un índice no agrupado es agrupado en un solo lugar y los datos de la
tabla son almacenados en otro lugar. Esto es similar a un libro de texto
donde el contenido del libro está localizado en un lugar y el índice está
localizado en otro. Esto permite tener más de un índice no agrupado por
tabla.

Es importante mencionar que dentro de la tabla los datos serán ordenados

por un índice agrupado. De todos modos, por dentro los datos del índice no
agrupado son almacenados en un orden específico. El índice contiene
valores de columna en los cuales el índice es creado y la dirección del
registro a la que el valor de la columna pertenece.

Astay Systems 54
 Creando un Índice no agrupado

La sintaxis para crear un Índice no agrupado es similar a la del índice

agrupado. De todos modos, en el caso de los índices no agrupados, la
palabra reservada NONCLUSTERED es usada en lugar de CLUSTERED .

USE schooldb

CREATE NONCLUSTERED INDEX IX_tblStudent_Name

ON student(name ASC)

El siguiente script crea un índice no agrupado en la columna “name” de la

tabla student. El índice ordena por name en orden ascendente. Como
dijimos anteriormente, los datos de la tabla e índice serán almacenados en
lugares diferentes.
Los indices no agrupados se crean para mejorar la velocidad de las
Astay Systems
consultas. 55
 Conclusión
De la discusión encontramos las siguientes diferencias entre índices
agrupados y no agrupados.
Puede haber sólo un índice agrupado por tabla. De todos modos, usted
puede crear múltiples índices no agrupados en una sola tabla.
Los índices agrupados sólo ordenan tablas. Por lo tanto, no consumen
almacenaje extra. Los índices no agrupados son almacenados en un lugar
separado de la tabla real. Reclamando más espacio de almacenamiento.
Los índices agrupados son más rápidos que los índices no agrupados, ya
que no involucran ningún paso extra de búsqueda.

Astay Systems 56
 Procedimiento almacenados
Un procedimiento almacenado es un conjunto de instrucciones de T-SQL
que SQL Server compila, en un único plan de ejecución, los llamados "store
procedures" se encuentran almacenados en la base de datos, los cuales
pueden ser ejecutados en cualquier momento.

Tipos de Procedimientos Almacenados:

Procedimientos Almacenados del sistema, se utilizan para administrar el

SQL Server y para mostrar información sobre base de datos y sobre
usuarios.
Procedimientos Almacenados sencillos definidos por el usuario, son los
procedimientos creados por los usuarios y están personalizados para
llevar a cabo la tarea deseada por el usuario.
Astay Systems 57
 Los procedimientos almacenados ofrecen ventajas importantes:

Rendimiento: al ser ejecutados por el motor de base de datos ofrecen un

rendimiento inmejorable ya que no es necesario transportar datos a
ninguna parte. Cualquier proceso externo tiene una penalidad de tiempo
adicional dada por el transporte de datos.
Potencia: el lenguaje para procedimientos almacenados es muy potente.
Permiten ejecutar operaciones complejas en pocos pasos ya que poseen
un conjunto de instrucciones avanzado.
Centralización: al formar parte de la base de datos los procedimientos
almacenados están en un lugar centralizado y pueden ser ejecutados por
cualquier aplicación que tenga acceso a la misma.

Astay Systems 58
 CODIGO DE EJEMPLO (SIN PARAMETROS)

CREATE PROCEDURE SP_PRUEBA1

AS --Bajo que contexto se va ha crear
PRINT 'HOLA MUNDO'

-- COMO LO EJECUTAMOS
EXECUTE SP_PRUEBA1

CREATE PROC SP_CONSULT

AS
SELECT * FROM Productos
WHERE COD_PROD = 'A005'

EXECUTE SP_CONSULT

Astay Systems 59
 HACEMOS USO DE PARAMETROS

CREATE PROC SP_RestarExistencia

@CodProd AS VARCHAR(4),
@Cantidad AS INT
AS
UPDATE PRODUCTOS SET EXISTENCIA = EXISTENCIA-@Cantidad
WHERE CodProd = @CodProd

SELECT * FROM Productos

-- Executamos el procedimiento
EXEC SP_RestarExistencia 'A003',45

Astay Systems 60
 VISTAS

Es como una tabla virtual que almacena una consulta. Los datos accesibles
a través de la vista no están almacenados en la base de datos como un
objeto.
Una vista suele llamarse también tabla virtual porque los resultados que
retorna y la manera de referenciarlas es la misma que para una tabla.

Las vistas permiten:

ocultar información: permitiendo el acceso a algunos datos y manteniendo

oculto el resto de la información que no se incluye en la vista. El usuario
opera con los datos de una vista como si se tratara de una tabla,
pudiendo modificar tales datos.

Astay Systems 61
 Simplificar la administración de los permisos de usuario: se pueden dar al
usuario permisos para que solamente pueda acceder a los datos a través
de vistas, en lugar de concederle permisos para acceder a ciertos
campos, así se protegen las tablas base de cambios en su estructura.
Mejorar el rendimiento: se puede evitar tipear instrucciones repetidamente
almacenando en una vista el resultado de una consulta compleja que
incluya información de varias tablas.

CREATE VIEW Listado1

AS
SELECT Id_Cod, Cod_prod, Nombre FROM PRODUCTOS

-- Ejecutamos
SELECT * FROM LISTADOS

Astay Systems 62
 MODIFICAMOS UNA VISTA

ALTER VIEW Listado1 --Llamamos a la vista

AS
SELECT Id_Cod, Cod_prod, Nombre FROM PRODUCTOS

-- Ejecutamos
SELECT * FROM LISTADOS

-- Vemos que tienen nuestra vista

sp_helptext Listado1 --!Peligro, se ve el origen de la vista, la tabla

ALTER VIEW Listado1 WITH ENCRYPTION --Llamamos a la vista

AS
SELECT Id_Cod, Cod_prod, Nombre FROM PRODUCTOS

BORRAMOS LA VISTA

DROP VIEW Listado1

Astay Systems 63
 -- Eliminamos la vista "vista_empleados" si existe:
if object_id('vista_empleados') is not null
drop view vista_empleados;
go

-- Creamos la vista "vista_empleados", que es resultado de una combinación

-- en la cual se muestran 5 campos:
create view vista_empleados as
select (apellido+' '+[Link]) as nombre,sexo,
[Link] as seccion, cantidadhijos
from empleados as e
join secciones as s
on codigo=seccion;
go

-- Vemos la información de la vista:

select * from vista_empleados;

-- Realizamos una consulta a la vista como si se tratara de una tabla:

select seccion,count(*) as cantidad
from vista_empleados
group by seccion;

-- Eliminamos la vista "vista_empleados_ingreso" si existe:

if object_id('vista_empleados_ingreso') is not null
drop view vista_empleados_ingreso;
go

-- Creamos otra vista de "empleados" denominada "vista_empleados_ingreso"

-- que almacena la cantidad de empleados por año:
create view vista_empleados_ingreso (fecha,cantidad)
as
select datepart(year,fechaingreso),count(*)
from empleados
group by datepart(year,fechaingreso);
go

-- Vemos la información de la vista creada:

select * from vista_empleados_ingreso;

Astay Systems 64
 SQL JOIN
OPERADORES

Un operador es un símbolo o una palabra clave que define una acción que
se realiza en una o más expresiones en la instrucción Select.
Establecer operador

Veamos los detalles de los los operadores de conjuntos en SQL Server y

cómo usarlos.
Hay cuatro operadores básicos de conjuntos en SQL Server:

JOIN & JOIN All

EXCEPT
INTERSECT
Astay Systems 65
 JOIN'S

El operador de JOIN combina los resultados de dos o más consultas dando

lugar a la creación de un único conjunto de resultados que incluye todas las
filas que pertenecen a todas las consultas en la JOIN . En esta operación,
combina dos consultas más y elimina los duplicados.

Astay Systems 66
 SELECT
product_name,
category_name,
list_price
FROM
[Link] p
INNER JOIN [Link] c
-- c y p son los alias
ON c.category_id = p.category_id
ORDER BY
product_name DESC;

Astay Systems 67
 SQL Server INNER JOIN syntax

SELECT
select_list
FROM
T1
INNER JOIN T2 ON join_predicate;

En esta sintaxism la consulta obtiene informacion de ambas tablas T1 y T2.

Primero, especifica la tabla principal (T1) con la clausual FROM .

Segundo, la segunda tabla especifica en el INNER JOIN con la clausula
(T2) y un Join predicado, solo las filas que hacen el Join predicate se
incluyan en el conjunto de resultados.

Astay Systems 68
 Un ejemplo mas

SELECT
product_name,
category_name,
brand_name,
list_price
FROM
[Link] p
INNER JOIN [Link] c ON c.category_id = p.category_id
INNER JOIN [Link] b ON b.brand_id = p.brand_id
ORDER BY
product_name DESC;
Astay Systems 69
 LEFT JOIN

SELECT
product_name, order_id
FROM
[Link] p
LEFT JOIN sales.order_items o ON o.product_id = p.product_id
ORDER BY
order_id;
Astay Systems 70
 Un ejemplo mas

SELECT
p.product_name, o.order_id,
i.item_id,
o.order_date
FROM
[Link] p
LEFT JOIN sales.order_items i
ON i.product_id = p.product_id
LEFT JOIN [Link] o
ON o.order_id = i.order_id
ORDER BY
order_id;
Astay Systems 71
 RIGHT JOIN
Devuelve todos los registros de la tabla derecha y los registros coincidentes
de la tabla izquierda.

SELECT product_name, order_id

FROM
sales.order_items o
RIGHT JOIN [Link] p ON o.product_id = p.product_id
ORDER BY
Astay Systems order_id; 72
 Otro ejemplo mas

SELECT
product_name,
order_id
FROM
sales.order_items o
RIGHT JOIN [Link] p
ON o.product_id = p.product_id
WHERE
order_id IS NULL
ORDER BY
product_name;

Astay Systems 73
 FULL JOIN
Devuelve todos los registros cuando hay una coincidencia en la tabla
izquierda o [Link] no existen filas coincidentes para la fila en la
tabla izquierda, las columnas de la tabla derecha tendrán valores nulos. De
manera similar, cuando no existen filas coincidentes para la fila en la tabla
derecha, la columna de la tabla izquierda tendrá valores nulos.
A continuación se muestra la sintaxis de FULL OUTER JOIN al unir dos tablas
T1 y T2:

SELECT
select_list
FROM
T1
FULL OUTER JOIN T2 ON join_predicate;
Astay Systems 74
 La palabra clave OUTER es opcional, por lo que puede omitirla como se
muestra en la siguiente consulta:

SELECT
select_list
FROM
T1
FULL JOIN T2 ON join_predicate;

En esta sintaxis:
En esta sintaxis:

Primero, especifique la tabla izquierda T1 en la cláusula FROM.

En segundo lugar, especifique la tabla derecha T2 y un predicado de unión.

Astay Systems 75
 El siguiente diagrama de Venn ilustra la FULL OUTER JOIN de dos conjuntos
de resultados:

Example

Vamos a configurar una tabla de muestra para demostrar full outer join .
Primero, creamos un nuevo esquema llamado pm que represente la gestión
de proyectos.

CREATE SCHEMA pm;

Astay Systems
GO 76
 A continuación, creamos nuevas tablas llamadas Projects y miembros en el
esquema pm :

CREATE TABLE [Link](

id INT PRIMARY KEY IDENTITY,
title VARCHAR(255) NOT NULL
);

CREATE TABLE [Link](

id INT PRIMARY KEY IDENTITY,
name VARCHAR(120) NOT NULL,
project_id INT,
FOREIGN KEY (project_id)
REFERENCES [Link](id)
);

Supongamos que cada miembro solo puede participar en un proyecto y cada

proyecto tiene cero o más miembros. Si un proyecto está en la fase de idea,
Astaypor lo tanto, no hay ningún miembro asignado.
Systems 77
 Luego, inserte algunas filas en los Projects y las tablas Members :

INSERT INTO
[Link](title)
VALUES
('New CRM for Project Sales'),
('ERP Implementation'),
('Develop Mobile Sales Platform');

INSERT INTO
[Link](name, project_id)
VALUES
('John Doe', 1),
('Lily Bush', 1),
('Jane Doe', 2),
('Jack Daniel', null);

Astay Systems 78
 Después de eso, consulta los datos de los Projects y las tabla Members :

SELECT * FROM [Link];

SELECT * FROM [Link];

SELECT
[Link] member,
[Link] project
FROM
[Link] m
FULL OUTER JOIN [Link] p
ON [Link] = m.project_id;

En este ejemplo, la consulta devolvió miembros que participan en proyectos,

miembros que no participan en ningún proyecto y proyectos que no tienen
ningún miembro.

Astay Systems 79
 Para encontrar los miembros que no participan en ningún proyecto y
proyectos que no tienen miembros, agregue una cláusula WHERE a la
consulta anterior:

SELECT
[Link] member,
[Link] project
FROM
[Link] m
FULL OUTER JOIN [Link] p
ON [Link] = m.project_id
WHERE
[Link] IS NULL OR
[Link] IS NULL;

Astay Systems 80
 CROSS JOIN
Sirve para unir dos tablas que no estan relacionadas
A continuación se ilustra la sintaxis de SQL Server CROSS JOIN de dos
tablas:

SELECT
select_list
FROM
T1
CROSS JOIN T2;

Astay Systems 81
 CROSS JOIN unió cada fila de la primera tabla (T1) con cada fila de la
segunda tabla (T2). En otras palabras, la unión cruzada devuelve un
producto cartesiano de filas de ambas tablas.

A diferencia de INNER JOIN o LEFT JOIN, la combinación cruzada no

establece una relación entre las tablas unidas.

Suponga que la tabla T1 contiene tres filas 1, 2 y 3 y que la tabla T2

contiene tres filas A, B y C.

CROSS JOIN obtiene una fila de la primera tabla (T1) y luego crea una
nueva fila para cada fila de la segunda tabla (T2). Luego hace lo mismo para
la siguiente fila de la primera tabla (T1) y así sucesivamente.

Astay Systems 82
 Ejemplos CROSS JOIN

La siguiente declaración devuelve las combinaciones de todos los productos

y tiendas. El conjunto de resultados se puede utilizar para el procedimiento
de inventario durante los cierres de fin de mes y de fin de año:

SELECT
product_id,
product_name,
store_id,
0 AS quantity
FROM
[Link]
CROSS JOIN [Link]
ORDER BY
product_name,
store_id;

Astay Systems 83
 La siguiente declaración encuentra los productos que no tienen ventas en
las tiendas:

SELECT
s.store_id,
p.product_id,
ISNULL(sales, 0) sales
FROM
[Link] s
CROSS JOIN [Link] p
LEFT JOIN (

Astay Systems 84
 SELECT
s.store_id,
p.product_id,
SUM (quantity * i.list_price) sales
FROM
[Link] o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN [Link] s ON s.store_id = o.store_id
INNER JOIN [Link] p ON p.product_id = i.product_id
GROUP BY
s.store_id,
p.product_id
) c ON c.store_id = s.store_id
AND c.product_id = p.product_id
WHERE
sales IS NULL
ORDER BY
product_id,
store_id;

Astay Systems 85
 SELF JOIN
Una autounión le permite unir una tabla a sí misma. Es útil para consultar
datos jerárquicos o comparar filas dentro de la misma tabla.
Una autounión utiliza la cláusula de unión interna o izquierda. Debido a que
la consulta que utiliza la unión automática hace referencia a la misma tabla,
el alias de la tabla se usa para asignar diferentes nombres a la misma tabla
dentro de la consulta.

SELECT
select_list
FROM
T t1
[INNER | LEFT] JOIN T t2 ON
join_predicate;

Astay Systems 86
 EJEMPLOS

1- Uso de SELF JOIN para consultar datos jerárquicos.

SELECT
e.first_name + ' ' + e.last_name employee,
m.first_name + ' ' + m.last_name manager
FROM
[Link] e
INNER JOIN [Link] m ON m.staff_id = e.manager_id
ORDER BY
manager;

Astay Systems 87
 GROUP BY
La cláusula GROUP BY le permite organizar las filas de una consulta en
grupos. Los grupos están determinados por las columnas que especifique en
la cláusula GROUP BY .

A continuación se ilustra la sintaxis de la cláusula GROUP BY :

SELECT
select_list
FROM
table_name
GROUP BY
column_name1,
column_name2 ,...;

Astay Systems 88
 En esta consulta, la cláusula GROUP BY produjo un grupo para cada
combinación de los valores en las columnas enumeradas en la cláusula
GROUP BY .

Considere el siguiente ejemplo:

SELECT
customer_id,
YEAR (order_date) order_year
FROM
[Link]
WHERE
customer_id IN (1, 2)
ORDER BY
customer_id;

Astay Systems 89
 En este ejemplo, recuperamos la identificación del cliente y el año ordenado
de los clientes con la identificación del cliente uno y dos.
Como puede ver claramente en la salida, el cliente con la identificación uno
realizó un pedido en 2016 y dos pedidos en 2018. El cliente con la
identificación dos realizó dos pedidos en 2017 y un pedido en 2018.
Agreguemos una cláusula GROUP BY a la consulta para ver el efecto:

SELECT
customer_id,
YEAR (order_date) order_year
FROM
[Link]
WHERE
customer_id IN (1, 2)
GROUP BY
customer_id,
YEAR (order_date)
ORDER BY
customer_id;
Astay Systems 90
 La cláusula GROUP BY organizó las primeras tres filas en dos grupos y las
siguientes tres filas en los otros dos grupos con las combinaciones únicas
de la identificación del cliente y el año del pedido.

Hablando funcionalmente, la cláusula GROUP BY en la consulta anterior

produjo el mismo resultado que la siguiente consulta que utiliza la cláusula
DISTINCT :

SELECT DISTINCT
customer_id,
YEAR (order_date) order_year
FROM
[Link]
WHERE
customer_id IN (1, 2)
ORDER BY
customer_id;

Astay Systems 91
 GROUP BY clause and aggregate functions

En la práctica, la cláusula GROUP BY a menudo se usa con funciones

agregadas para generar informes resumidos.

Una función agregada realiza un cálculo en un grupo y devuelve un valor

único por grupo. Por ejemplo, COUNT () devuelve el número de filas en cada
grupo. Otras funciones agregadas comúnmente utilizadas son SUM () ,
AVG () (promedio), MIN () (mínimo), MAX () (máximo).

La cláusula GROUP BY organiza las filas en grupos y una función agregada

devuelve el resumen (cuenta, mínimo, máximo, promedio, suma, etc.) para
cada grupo.

Por ejemplo, la siguiente consulta devuelve el número de pedidos realizados

por el cliente por año:
Astay Systems 92
 SELECT
customer_id,
YEAR (order_date) order_year,
COUNT (order_id) order_placed
FROM
[Link]
WHERE
customer_id IN (1, 2)
GROUP BY
customer_id,
YEAR (order_date)
ORDER BY
customer_id;

Si desea hacer referencia a cualquier columna o expresión que no figura en

la cláusula GROUP BY , debe usar esa columna como entrada de una función
agregada. De lo contrario, obtendrá un error porque no hay garantía de que
la columna o expresión devolverá un solo valor por grupo. Por ejemplo, la
Astaysiguiente
Systems consulta fallará: 93
 SELECT
customer_id,
YEAR (order_date) order_year,
order_status
FROM
[Link]
WHERE
customer_id IN (1, 2)
GROUP BY
customer_id,
YEAR (order_date)
ORDER BY
customer_id;

Astay Systems 94
 HAVING
La cláusula HAVING a menudo se usa con la cláusula GROUP BY para filtrar
grupos según una lista específica de condiciones. A continuación se ilustra
la sintaxis de la cláusula HAVING :

SELECT
select_list
FROM
table_name
GROUP BY
group_list
HAVING
conditions;

Astay Systems 95
 En esta sintaxis, la cláusula GROUP BY resume las filas en grupos y la
cláusula HAVING aplica una o más condiciones a estos grupos. Solo los
grupos que hacen que las condiciones se evalúen como TRUE se incluyen en
el resultado. En otras palabras, los grupos para los cuales la condición se
evalúa como FALSE o UNKNOWN se filtran. Debido a que SQL Server procesa
la cláusula HAVING después de la cláusula GROUP BY , no puede hacer
referencia a la función de agregado especificada en la lista de selección
utilizando el alias de columna. La siguiente consulta fallará:

SELECT
column_name1, column_name2,
aggregate_function (column_name3) column_alias
FROM
table_name
GROUP BY
column_name1, column_name2
HAVING
column_alias > value;
Astay Systems 96
 HAVING con el ejemplo de función COUNT

La siguiente declaración utiliza la cláusula HAVING para encontrar a los

clientes que hicieron al menos dos pedidos por año:

SELECT
customer_id,
YEAR (order_date),
COUNT (order_id) order_count
FROM
[Link]
GROUP BY
customer_id,
YEAR (order_date)
HAVING
COUNT (order_id) >= 2
ORDER BY
customer_id;

Astay Systems 97
 En este ejemplo:

Primero, la cláusula GROUP BY agrupa el pedido de cliente por cliente y

año de pedido. La función COUNT () devuelve el número de pedidos que
cada cliente realizó cada año.
En segundo lugar, la cláusula HAVING excluyó a todos los clientes cuyo
número de pedidos es inferior a dos.

Astay Systems 98
 HAVING cláusula con el ejemplo de función SUM () .

SELECT
order_id,
SUM (
quantity * list_price * (1 - discount)
) net_value
FROM
sales.order_items
GROUP BY
order_id
HAVING
SUM (
quantity * list_price * (1 - discount)
) > 20000
ORDER BY
net_value;

Astay Systems 99
 En este ejemplo:

Primero, la función SUMA () devuelve los valores netos de los pedidos de

ventas.
Segundo, la cláusula HAVING filtra los pedidos de ventas cuyos valores netos
son menores o iguales a 20,000.

Astay Systems 100

 Cláusula HAVING con ejemplo de funciones MAX y MIN

La siguiente declaración primero encuentra los precios de lista máximos y

mínimos en cada categoría de producto. Luego, filtra la categoría que tiene
el precio de lista máximo mayor que 4,000 o el precio de lista mínimo menor
que 500:

SELECT
category_id,
MAX (list_price) max_list_price,
MIN (list_price) min_list_price
FROM
[Link]
GROUP BY
category_id
HAVING
MAX (list_price) > 4000 OR MIN (list_price) < 500;

Astay Systems 101

 SUBQUERY
Una subconsulta es una consulta anidada dentro de otra instrucción como
SELECT , INSERT , UPDATE o DELETE .
La siguiente declaración muestra cómo usar una subconsulta en la cláusula
WHERE de una declaración SELECT para encontrar los pedidos de ventas de
los clientes que se encuentran en Nueva York:

Astay Systems 102

 SELECT
order_id, order_date, customer_id
FROM
[Link]
WHERE
customer_id IN (
SELECT
customer_id
FROM
[Link]
WHERE
city = 'New York'
)
ORDER BY
order_date DESC;

Astay Systems 103

 SUBQUEY ANINADOS
Una subconsulta puede anidarse dentro de otra subconsulta. SQL Server
admite hasta 32 niveles de anidamiento.

Astay Systems 104

 SELECT
product_name, list_price
FROM
[Link]
WHERE
list_price > (
SELECT
AVG (list_price)
FROM
[Link]
WHERE
brand_id IN (
SELECT
brand_id
FROM
[Link]
WHERE
brand_name = 'Strider'
OR brand_name = 'Trek'
)
)
ORDER BY
list_price;

Astay Systems 105

 TIPOS SUBQUERY

Puede usar una subconsulta en muchos lugares:

En lugar de una expresión

Con IN o NOT IN
Con ANY o ALL
Con EXISTS o NO EXISTS
En la instrucción UPDATE , DELETE , INSERT
En la cláusula FROM

Astay Systems 106

 La SUBQUERY se usa en lugar de una expresión

En el siguiente ejemplo, se utiliza una subconsulta como una expresión de

columna denominada max_list_price en una instrucción SELECT .

SELECT
order_id,
order_date,
(
SELECT
MAX (list_price)
FROM
sales.order_items i
WHERE
i.order_id = o.order_id
) AS max_list_price
FROM
[Link] o
order by order_date desc;
Astay Systems 107
 La SUBQUERY se utiliza con el operador IN

Una subconsulta que se usa con el operador IN devuelve un conjunto de

cero o más valores. Después de que la subconsulta devuelve valores, la
consulta externa los utiliza.

La siguiente consulta encuentra los nombres de todas las bicicletas de

montaña y productos de bicicletas de carretera que venden las tiendas de
bicicletas.

Astay Systems 108

 SELECT
product_id,
product_name
FROM
[Link]
WHERE
category_id IN (
SELECT
category_id
FROM
[Link]
WHERE
category_name = 'Mountain Bikes'
OR category_name = 'Road Bikes'
);

Astay Systems 109

 Esta consulta se evalúa en dos pasos:

Primero, la consulta interna devuelve una lista de números de

identificación de categoría que coinciden con los nombres Mountain Bikes
y el código Road Bikes.
En segundo lugar, estos valores se sustituyen en la consulta externa que
encuentra los nombres de productos que tienen el número de
identificación de categoría coincidente con uno de los valores de la lista.

Astay Systems 110

 CREATE TRIGGER
La instrucción CREATE TRIGGER le permite crear un nuevo disparador que se
dispara automáticamente cada vez que ocurre un evento como INSERT ,
DELETE o UPDATE en una tabla.

A continuación se ilustra la sintaxis de la instrucción CREATE TRIGGER :

CREATE TRIGGER [schema_name.]trigger_name

ON table_name
AFTER {[INSERT],[UPDATE],[DELETE]}
[NOT FOR REPLICATION]
AS
{sql_statements}

Astay Systems 111

 En esta sintaxis:

Schema_name es el nombre del esquema al que pertenece el nuevo

activador. El nombre del esquema es opcional.
Trigger_name es el nombre definido por el usuario para el nuevo activador.
Table_name es la tabla a la que se aplica el desencadenador.
El evento aparece en la cláusula AFTER . El evento podría ser INSERT ,
UPDATE o DELETE . Un disparador único puede dispararse en respuesta a
una o más acciones contra la mesa.
La opción NOT FOR REPLICATION indica a SQL Server que no active el
desencadenador cuando se realiza una modificación de datos como parte
de un proceso de replicación.
Sql_statements es uno o más Transact-SQL utilizados para llevar a cabo
acciones una vez que se produce un evento.
Astay Systems 112
 SQL Server proporciona dos tablas virtuales que están disponibles
específicamente para los desencadenantes llamadas tablas INSERTED y
DELETED . SQL Server usa estas tablas para capturar los datos de la fila
modificada antes y después de que ocurra el evento.

La siguiente tabla muestra el contenido de las tablas INSERTED y DELETED

antes y después de cada evento:

Evento
INSERTED Tabla tiene DELETED table holds
DML
INSERT filas para insertar Vacio
Nuevas filas modificadas por filas existentes modificadas por
UPDATE
la actualización la actualización
DELETE Vacio Filas que se eliminarán
Astay Systems 113
 1- Crear una tabla para registrar los cambios.

La siguiente instrucción crea una tabla llamada production.product_audits

para registrar información cuando se produce un evento INSERT o DELETE en
la tabla [Link]:

CREATE TABLE production.product_audits(

change_id INT IDENTITY PRIMARY KEY,
product_id INT NOT NULL,
product_name VARCHAR(255) NOT NULL,
brand_id INT NOT NULL,
category_id INT NOT NULL,
model_year SMALLINT NOT NULL,
list_price DEC(10,2) NOT NULL,
updated_at DATETIME NOT NULL,
operation CHAR(3) NOT NULL,
CHECK(operation = 'INS' or operation='DEL')
);

Astay Systems 114

 2- Creando un después DML trigger

Primero, para crear un nuevo (trigger) disparador, especifique el nombre del

disparador y el esquema al que pertenece el disparador en la cláusula
CREATE TRIGGER :

CREATE TRIGGER production.trg_product_audit

A continuación, especifique el nombre de la tabla, que se activará cuando se

produzca un evento, en la cláusula ON :

ON [Link]

Astay Systems 115

 El cuerpo del disparador comienza con la palabra clave AS :

AS
BEGIN

Después de eso, dentro del cuerpo del desencadenador, establece

SET NOCOUNT en ON para evitar que se devuelva el número de filas de
mensajes afectados cuando se dispara el desencadenador.

SET NOCOUNT ON;

El activador insertará una fila en la tabla production.product_audits cada vez

que se inserte o se elimine una fila de la tabla [Link]. Los
datos para insertar se alimentan de las tablas INSERTED y DELETED a través
del operador UNION ALL :
Astay Systems 116
 INSERT INTO
production.product_audits
(
product_id,
product_name,
brand_id,
category_id,
model_year,
list_price,
updated_at,
operation
)
SELECT
i.product_id,
product_name,
brand_id,
category_id,
model_year,
i.list_price,
GETDATE(),
'INS'
FROM
inserted AS i
UNION ALL
SELECT
d.product_id,
product_name,
brand_id,
category_id,
model_year,
d.list_price,
getdate(),
'DEL'
FROM
deleted AS d;

Astay Systems 117

 Lo siguiente pone todas las partes juntas:

CREATE TRIGGER production.trg_product_audit

ON [Link]
AFTER INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO production.product_audits(
product_id,
product_name,
brand_id,
category_id,
model_year,
list_price,
updated_at,
operation
)
SELECT
i.product_id,
product_name,
brand_id,
category_id,
model_year,
i.list_price,
GETDATE(),
'INS'
FROM
Astay Systemsinserted i 118
UNION ALL
 SELECT
d.product_id,
product_name,
brand_id,
category_id,
model_year,
d.list_price,
GETDATE(),
'DEL'
FROM
deleted d;
END

Astay Systems 119

 Finalmente, ejecuta la declaración completa para crear el desencadenante.
Una vez que se crea el activador, puede encontrarlo en la carpeta de
activadores de la tabla como se muestra en la siguiente imagen:

Astay Systems 120

 Tablas temporales
Las tablas temporales son visibles solamente en la sesión actual.

Las tablas temporales se eliminan automáticamente al acabar la sesión o la

función o procedimiento almacenado en el cual fueron definidas. Se pueden
eliminar con "drop table".

Pueden ser locales (son visibles sólo en la sesión actual) o globales (visibles
por todas las sesiones).

Para crear tablas temporales locales se emplea la misma sintaxis que para
crear cualquier tabla, excepto que se coloca un signo numeral (#)
precediendo el nombre.

Astay Systems 121

 Las tablas temporales se crean en tempdb, y al crearlas se producen
varios bloqueos sobre esta base de datos como por ejemplo en las tablas
sysobjects y sysindex. Los bloqueos sobre tempdb afectan a todo el
servidor.
Al crearlas es necesario que se realicen accesos de escritura al disco ( no
siempre si las tablas son pequeñas)
Al introducir datos en las tablas temporales de nuevo se produce
actividad en el disco, y ya sabemos que el acceso a disco suele ser el
"cuello de botella" de nuestro sistema.
Al leer datos de la tabla temporal hay que recurrir de nuevo al disco.
Además estos datos leídos de la tabla suelen combinarse con otros.
Al borrar la tabla de nuevo hay que adquirir bloqueos sobre la base de
datos tempdb y realizar operaciones en disco.
Astay Systems 122
 Las tablas temporales son de dos tipos en cuanto al alcance la tabla.
Tenemos tablas temporales locales y tablas temporales globales.

#locales: Las tablas temporales locales tienen una # como primer

carácter en su nombre y sólo se pueden utilizar en la conexión en la que
el usuario las crea. Cuando la conexión termina la tabla temporal
desaparece.
##globales: Las tablas temporales globales comienzan con ## y son
visibles por cualquier usuario conectado al SQL Server. Y una cosa más,
estás tablas desaparecen cuando ningún usuario está haciendo
referencias a ellas, no cuado se desconecta el usuario que la creo.

Astay Systems 123

 Temp Realmente hay un tipo más de tablas temporales. Si creamos una
tabla dentro de la base de datos temp es una tabla real en cuanto a que
podemos utilizarla como cualquier otra tabla en cualquier base de datos, y
es temporal en cuanto a que desaparece en cuanto apagamos el servidor.

Funcionamiento de tablas temporales

Crear una tabla temporal es igual que crear una tabla normal. Veámoslo con
un ejemplo:

CREATE TABLE #TablaTemporal (Campo1 int, Campo2 varchar(50))

Y se usan de manera habitual.

INSERT INTO #TalbaTemporal VALUES (1,'Primer campo')

INSERT INTO #TalbaTemporal VALUES (2,'Segundo campo')
SELECT * FROM #TablaTemporal
Astay Systems 124
 Como vemos no hay prácticamente limitaciones a la hora de trabajar con
tablas temporales (una limitación es que no pueden tener restricciones
FOREING KEY). Optimizar el uso de tablas temporales

El uso que les podemos dar a este tipo de tablas es infinito, pero siempre
teniendo en cuenta unas cuantas directivas que debemos seguir para que
ralenticen nuestro trabajo lo menos posible.

Astay Systems 125