TRANSACT-SQL

SQL Server

Alberto Echeverry A.
Ingeniero de Sistemas
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Índices:

¿Por qué utilizar Índices?

 Aumento en el desempeño.

Mejora la Velocidad de Acceso a Datos.

Menor Consumo de Recursos.
 Integridad de Datos.

Unicidad de Registros.
¿Por qué NO Utilizar Índices?
 Consumen Espacio en Disco.

 Producen Sobre Carga de Trabajo.

DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
¿Cuándo Usar Índices?

 Columnas a Indexar:
Claves Primarias y Foráneas.
Frecuentemente Búsquedas en Rangos.
Frecuentemente Consultas en Orden.
Utilizadas en JOIN.

 Columnas que NO se Deben Indexar:

Pocas Veces Referenciadas en Consultas.
Contiene Pocos Valores Únicos.
Definidas como bit, text o image.
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Ejemplo: Crear un índice para los Nombres y Apellidos de los Empleados.

CREATE INDEX IX_Empleados_Nombre

ON [Link] (Nombres, Apellidos);
GO

 Eliminar el índice creado :

DROP INDEX IX_Empleados_Nombre

ON [Link];
GO
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Actualización de Datos:

Hasta ahora hemos estudiado el cómo recuperar datos

almacenados en las tablas de nuestra base de datos. En
este tema vamos a tratar el de la actualización de esos
datos, es decir, insertar nuevas filas, borrar filas o
cambiar el contenido de las filas de una tabla. Estas
operaciones modifican los datos almacenados en las
tablas pero no su estructura, ni su definición.
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Insertar una fila INSERT INTO...VALUES
La inserción de nuevos datos en una tabla se realiza añadiendo
filas enteras a la tabla, la sentencia SQL que lo permite es la
orden INSERT INTO.
La inserción se puede realizar de una fila o de varias filas de
golpe, veremos las dos opciones por separado y empezaremos
por la inserción de una fila.
La sintaxis es la siguiente:
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Esta sintaxis se utiliza para insertar una sola fila cuyos valores
indicamos después de la palabra reservada VALUES.
 Los registros se agregan siempre al final de la tabla.
Al nombre de la tabla se le puede añadir la cláusula IN si la tabla
se encuentra en otra base de datos (en una base de datos externa).
 Cuando la tabla tiene una columna de tipo contador
(AutoNumber), lo normal es no asignar valor a esa columna para
que el sistema le asigne el valor que le toque según el contador, si
por el contrario queremos que la columna tenga un valor
concreto, lo indicamos en la lista de valores.
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Cuando no se indica ninguna lista de columnas después del
destino, se asume por defecto todas las columnas de la tabla, en
este caso, los valores se tienen que especificar en el mismo orden
en que aparecen las columnas en la ventana de diseño de dicha
tabla, y se tiene que utilizar el valor NULL para rellenar las
columnas de las cuales no tenemos valores.

Ejemplo:
INSERT INTO [Link]
VALUES (3030, '2009-11-08', 71100, NULL, 1);
GO
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Cuando indicamos nombres de columnas, estos corresponden a nombres de
columna de la tabla, pero no tienen por qué estar en el orden en que aparecen
en la ventana diseño de la tabla, también se pueden omitir algunas columnas,
la columnas que no se nombran tendrán por defecto el valor NULL o el valor
predeterminado indicado en la ventana de diseño de tabla.

El ejemplo anterior se podría escribir de la siguiente forma:

INSERT INTO [Link]

(Nro_Pedido, Fecha, Id_Cliente, Id_Empleado, Id_Forma_Pago)
VALUES (3030, '2009-11-08', 71100, 74000, 1);
GO
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Insertar varias filas INSERT INTO...SELECT
Podemos insertar en una tabla varias filas con una sola sentencia
SELECT INTO si los valores a insertar se pueden obtener como
resultado de una consulta, en este caso sustituimos la cláusula
VALUES lista de valores por una sentencia SELECT como las
que hemos visto hasta ahora. Cada fila resultado de la SELECT
forma una lista de valores que son los que se insertan en una
nueva fila de la tabla destino. Es como si tuviesemos una
INSERT...VALUES por cada fila resultado de la sentencia
SELECT.
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
La sintaxis es la siguiente:

El SELECT debe devolver el mismo número de columnas que las de la tabla

destino y en el mismo orden, o el mismo número de columnas que indicamos en
la lista de columnas después de destino.

El siguiente ejemplo inserta en la tabla OthersPedidos que es igual a la tabla

Pedidos aquellas tuplas donde el Id_Forma_Pago = 3.

INSERT INTO [Link]

SELECT * FROM [Link] WHERE Id_Forma_Pago = 3;
GO
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
El siguiente ejemplo, Inserta varias tuplas a la tabla OthersPedidos
que es igual a la tabla Pedidos; siempre que el Id_Forma_Pago =
1. Solo Inserta los datos de las Columnas especificadas y las
demás columnas quedan con NULL.

INSERT INTO [Link]

(Nro_Pedido, Fecha)
SELECT Nro_Pedido, Fecha
FROM [Link]
WHERE Id_Forma_Pago = 1;
GO
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Insertar filas en una nueva tabla SELECT ... INTO
Esta sentencia inserta filas creando en ese momento la tabla
donde se insertan las filas. Se suele utilizar para guardar en una
tabla el resultado de una SELECT.
Sintaxis:

Las columnas de la nueva tabla tendrán el mismo tipo y tamaño

que las columnas origen, y se llamarán con el nombre de alias de la
columna origen o en su defecto con el nombre de la columna
origen, pero no se transfiere ninguna otra propiedad del campo o
de la tabla como por ejemplo las claves e índices.
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
El siguiente ejemplo crea la tabla EmpleadosResultante, a partir de la tabla
Empleados.

SELECT * INTO EmpleadosResultante FROM [Link];

GO

La sentencia SELECT INTO se suele utilizar para crear tablas de trabajo, o

tablas intermedias, las creamos para una determinada tarea y cuando hemos
terminado esa tarea las borramos. También puede ser útil para sacar datos en
una tabla para enviarlos a alguien.
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
Este Ejemplo Crea la Tabla EmpleadosResultante con base al criterio del
WHERE.

SELECT Id_Empleado AS CC_Empleado, Nombre, Id_Cargo

INTO [Link]
FROM [Link]
WHERE Id_Cargo > 0 AND Id_Cargo < 5;
GO
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Modificar el contenido de las filas ( UPDATE )
La sentencia UPDATE modifica los valores de una o más
columnas en las filas seleccionadas de una o varias tablas.
La sintaxis es la siguiente:

También se puede incluir la cláusula IN si la tabla a modificar

se encuentra en una base de datos externa.
La cláusula SET especifica qué columnas van a modificarse y
qué valores asignar a esas columnas.
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
El siguiente ejemplo cambia el Id_Cargo a 5 para todos los
empleados de la tabla [Link].

UPDATE [Link] SET Id_Cargo = 5

WHERE Id_Empleado IN
(SELECT Id_Empleado
FROM [Link]
WHERE Id_Cargo = 9);
GO
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Borrar filas (DELETE)

La sentencia DELETE elimina filas de una tabla.

La sintaxis es la siguiente:

Podemos incluir la cláusula IN si la tabla se encuentra en una base de datos

externa, también podemos escribir una composición de tablas.
La opción tabla.* se utiliza cuando el origen está basado en varias tablas, y
sirve para indicar en qué tabla vamos a borrar.
La opción * es opcional y es la que se asume por defecto y se puede poner
unicamente cuando el origen es una sola tabla.
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
Si no se indica la cláusula WHERE, se borran TODAS las filas de la tabla.
Una vez borrados, los registros no se pueden recuperar.
Si la tabla donde borramos está relacionada con otras tablas se podrán borrar
o no los registros siguiendo las reglas de integridad referencial definidas en
las relaciones.

Este ejemplo borra las tuplas de [Link] donde el Id_Cargo

es 5.

DELETE FROM [Link]

WHERE Id_Empleado IN
(SELECT Id_Empleado
FROM [Link]
WHERE Id_Cargo = 5);
GO
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Truncate Table
Para borrar datos de forma masiva disponemos de la instrucción
TRUNCATE TABLE, que borra todos los datos de una tabla.

Este ejemplo elimina todos los datos de la tabla

EmpleadosResultante:

TRUNCATE TABLE [Link];

GO
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Vistas
las vistas, calculadas a partir de otras tablas. Son virtuales en el
sentido que no ocupan espacio en el disco, pero son el resultado
de interrogaciones sobre otras tablas y, por lo tanto, siempre están
alineadas con los valores contenidos en dichas tablas.

La sintaxis SQL para definir una vista es la siguiente:

CREATE VIEW nombre_vista [( lista_nombres_columnas )]

AS expresión_tabla

 NOTA:
Una Vista se consulta como si fuese una Tabla.
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
Típicamente, expresión_tabla es una instrucción SELECT que
producirá la tabla que interesa. La lista_nombres_columnas se
puede usar para asignar nombres a las columnas de la vista.

Ejemplo: Obtener una Vista del ProductID y el Name de la

tabla [Link]. Donde el ProductID este entre 350
y 400

CREATE VIEW ResultadoProduct

AS SELECT ProductID, Name
FROM [Link]
WHERE ProductID BETWEEN 350 AND 400;
GO
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
Ejemplo: Obtener una Vista de: ProductSubcategoryID, ProductID, Name,
SUM(ListPrice) AS TOTAL de la tabla [Link].

CREATE VIEW TotPreProdSubCat

AS SELECT ProductSubcategoryID, ProductID, Name,
SUM(ListPrice) AS TOTAL
FROM [Link]
WHERE ProductID IN (SELECT ProductID
FROM [Link]
WHERE ListPrice > 0 AND
(ProductSubcategoryID BETWEEN
15 AND 20))
GROUP BY ProductSubcategoryID,
ProductID, Name;
GO
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Restricciones al Crear Vistas:

 Máximo 1024 Columnas.

 No puede incluir las Clausulas COMPUTE o COMPUTE BY.
 No puede incluir la Clausula ORDER BY, a menos que sea
usado en conjunto con un TOP.
 No puede incluir la Clausula INTO.
 No puede Referenciar una Tabla Temporal.
 Debe ser expresada como un Batch simple Transact-SQL.
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
Modificar Datos con Vistas
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
Modificar Datos con Vistas
 DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Alterar un Vista
La sintaxis es la siguiente:
ALTER VIEW nombre_vista [( lista_nombres_columnas )]
AS expresión_tabla

Ejemplo: Alterar la Vista ResultadoProduct creada en la diapositiva

91. Note que se le añaden nuevos atributos.

ALTER VIEW ResultadoProduct

AS SELECT ProductID, Name, Size, Color, ListPrice
FROM [Link]
WHERE ProductID BETWEEN 350 AND 400;
GO
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Eliminar una Vista

La sintaxis es la siguiente:
DROP VIEW nombre_vista

El siguiente ejemplo elimina la vista ResultadoProduct, alterada

en la diapositiva anterior.

DROP VIEW ResultadoProduct;

GO
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Procedimientos Almacenados

Un procedimiento es un programa dentro de la base de datos que

ejecuta una acción o conjunto de acciones especificas.
Un procedimiento tiene un nombre, un conjunto de parámetros
(opcional) y un bloque de código.
En Transact SQL los procedimientos almacenados pueden
devolver valores (numérico entero) o conjuntos de resultados.
Para crear un procedimiento almacenado debemos emplear la
sentencia CREATE PROCEDURE.
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
Sintaxis:

Para modificar un procedimiento almacenado debemos emplear

la sentencia ALTER PROCEDURE.
Sintaxis:
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 El siguiente Ejemplo crea un PA llamado
“SPU_Pedidos_Fechas” que retorna las tuplas de la tabla
[Link] cuyas DueDate (Fecha Pedido) sea
menor a la fecha Actual.

CREATE PROCEDURE SPU_Pedidos_Fechas

AS
SELECT SalesOrderID, OrderDate
FROM [Link]
WHERE DueDate < GETDATE();
GO
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Ejecución de un PA por sí mismo.
Debemos utilizar la instrucción EXEC seguida del nombre del
PA. Así:

EXEC SPU_Pedidos_Fechas;
GO

Ejemplo: Crear un PA que inserte en la tabla

[Link] las tuplas cuyo Id_Cliente este entre
40000 y 50000 (estas tuplas se encuentran en la tabla
[Link] de la Base de Datos Pedidos).
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
CREATE PROCEDURE SPU_Insert_ClientesResultante
AS
SELECT *
FROM [Link]
WHERE Id_Cliente BETWEEN 40000 AND 50000;
GO

El PA se Ejecuta dentro de una instrucción INSERT INTO así:

INSERT INTO [Link] (Id_Cliente, Nombre)

EXEC SPU_Insert_ClientesResultante;
GO
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Alterar o Modificar un PA.
Esto se logra con la intrucción ALTER PROCEDURE así:
Altere el procedimiento SPU_Insert_ClientesResultante para
que inserte los cuatro primeros caracteres del nombre de los
empleados (para lograrlo se debe utilizar la función
SUBSTRING):

ALTER PROCEDURE SPU_Insert_ClientesResultante

AS
SELECT Id_Cliente, SUBSTRING (Nombre, 1, 4) Nombre
FROM [Link]
WHERE Id_Cliente BETWEEN 40000 AND 50000;
GO
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
 Eliminar un PA.
Esto se logra con la instrucción DROP PROCEDURE así:

Eliminar el PA SPU_Insert_ClientesResultante

DROP PROCEDURE SPU_Insert_ClientesResultante;

GO

 También se pueden hacer Operaciones Matemáticas así:

Multiplique dos números y diga si el Resultado es Menor o
Mayor a 50.
DML (LENGUAJE DE MANIPULACIÓN DE
DATOS)
CREATE PROCEDURE SPU_Multiplicar
/* Declaración de Variable */
@Nro1 SmallInt,
@Nro2 SmallInt,
@Rest SmallInt OUTPUT
AS
SET @Rest = @Nro1 * @Nro2;
GO

Ejecución del Procedimiento: (ver la diapositiva siguiente).