Examen 70-433 - Chapter 1 Data Retrieval
Chapter 1 - Data Retrieval
ANSI_NULLS
Sólo con SET ANSI_NULLS OFF; se puede hacer comparaciones con NULL tales como:
SELECT Nombre
FROM Cliente
WHERE Telefono <> NULL
En caso contrario se debe reemplazar "IS NOT" con "<>"
ORDEN DE OPERACIONES
En caso de no existir parentesis, SQL evalua en el siguiente orden:
1-NOT
2-AND
3-OR
CLAUSULA LIKE
La Cláusula Like se utiliza para "matchear" columnas especificas en la cláusula WHERE
(%)
Reemplaza cualquier cantidad de caracteres (incluyendo 0), por ejemplo:
"%sa" matchea "casa", "masa", "sa"
(_)
Reemplaza exactamente con un caracter, por ejemplo:
"_so" matchea "oso", pero no "so" ni "manioso"
([])
Reemplaza un caracter con un conjunto o un rango de caracteres
por ejemplo: [aeiou], que tambièn se puede respresentar separado por comas como [a,e,i,o,u], el mismo matchea todas las vocales
Para el caso de conjuntos, se separa por "-" por ejemplo: [a-z] matchea cualquier caracter que sea letra.
(^)
Indica el opuesto al conjunto, por ejemplo [^a-z] indicaría que el caracter no sea una letra.
Cláusula BETWEEN: Es importante saber que el rango incluye valores de la siguiente manera, si por ejemplo se tiene BETWEEN 0 AND 5, este rango incluye 0, 0.1, 0.00001, 2, ... 5.0
Ademas en rangos como A AND Z se incluyen A, ASAS, ... Z, pero no Zebras.
INNER JOIN
Se utiliza para matchear 2 o más tablas, las rows retornadas son las que cumplen con la condicion referencial en el operador ON del INNER JOIN
OUTER JOIN
Es utilizado para retornar todas las rows de una tabla y sólo información de las tablas en común de otra tabla o puede retornar todas las rows de todas las tablas con JOIN.
Se puede omitir la palabra OUTER, pero se debe especificar LEFT, RIGHT o FULL
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOINSELECT Nombre, Apellido, Telefono
FROM Cliente C RIGHT OUTER JOIN Facturas F ON
C.Id = F.ClienteId
RIGHT OUTER JOIN añade al resultado del INNER JOIN las filas de la tabla de la derecha que no tienen correspondencia en la tabla Cliente, y rellena en esas filas los campos de la tabla de la izquierda con valores NULL.
FULL OUTER JOIN
Retorna todas las rows de todas las tablas de la cláusula JOINFUNCIONES DE AGREGADO
AVG
CHECKSUM_AGG
COUNT
COUNT_BIG
GROUPPING
MAX
MIN
SUM
STDEV
STDEVP
VAR
VARP
NULL es ignorado para todas las funciones de Agregado
OPERADORES: WITH ROLLUP y WITH CUBE
WITH ROLLUP se utiliza para obtener un "SUBTOTAL" en las querys que utilizan GROUP BY,
Ejemplo:
Supongamos que tenemos 2 tablas, una tabla representa a los jugares de los equipos y otra tabla tiene el detalle de los goles por partido de los jugadores
Entonces queremos tener un detalle por jugador y por equipo de:
- Promedio de Goles
- Cantidad de Goles
SELECT EquipoID
, JugadorID
, AVG(Goles)
, SUM(Goles)
FROM Equipo E
JOIN Jugador J
ON J.EquipoID = E.ID
JOIN DetallePartido D
ON D.JugadorID = J.ID
GROUP BY EquipoID,JugadorID
WITH ROLLUPEl resultado de la query anterior mostrará los valores como si fuera un GROUP BY, pero al tener un WITH ROLLUP agregará una ROW por cada EQUIPO, el cual indicará el promedio y la suma de goles del equipo, esta ROW tendrá el valor NULL en JugadorID
>1, NULL, 1, 20
Además al final del set retornado se mostrará una ROW con valor NULL en Equipo y Jugador que indicará el Promedio y suma de goles de todos los jugadores de todos los equipos.
>NULL, NULL, 2, 250
GROUPPING
GROUPPING se agrega a la clausula SELECT, retorna 1 ó 0.
1 : Indica que el campo especificado es NULL porque se está utilizando para el detalle de un WITH ROLLUP
0: Caso contrario
SELECT EquipoID
, GROUPPING (EquipoID)
, JugadorID
, GROUPPING (JugadorID)
, AVG(Goles)
, SUM(Goles)
FROM Equipo E
JOIN Jugador J
ON J.EquipoID = E.ID
JOIN DetallePartido D
ON D.JugadorID = J.ID
GROUP BY EquipoID,JugadorID
WITH ROLLUPEn el ejemplo anterior retornará 1 en las rows cuyo campo sea NULL por ser un SUBTOTAL
GROUPING SETS
Groupping sets es una novedad en SQL SERVER 2008, siempre se puede reemplazar por una operación ROLLUP o CUBE, o por un GROUP BY con UNION ALL, o subconjuntos retornados por ROLLUP o CUBE
Ejemplo:
SELECT EquipoID
, JugadorID
, AVG(Goles)
, SUM(Goles)
FROM Equipo E
JOIN Jugador J
ON J.EquipoID = E.ID
JOIN DetallePartido D
ON D.JugadorID = J.ID
GROUP BY GROUPING SETS ((EquipoID),(JugadorID))
Es equivalente a usar la misma query del ejemplo de ROLLUP, pero usando UNION ALL y NULL en los campos a agrupar, sería algo como:
SELECT NULL, Jugador, AVG...
FROM ...
...
UNION ALL
SELECT Equipo, NULL, AVG...
FROM ...
...
UNION, UNION ALL
Las consultas que utilizan UNION retornan los ALIAS de la primera consulta y ordenan según la segunda consulta.
Las consultas unidas por UNION deben tener la misma cantidad de campos y del mismo tipo.
UNION ALL incluye los rows repetidos.
Performance: En caso de saber de antemano que las consultas a unir son disjuntas, se debe utilizar UNION ALL ya que une las rows sin comparar entre las rows las repetidas y de esta manera la consulta es más rápida
EXCEPT, INTERSECT
Estas cláusulas son muy faciles de usar.
SELECT nombre
FROM Cliente
EXCEPT
SELECT nombre
FROM Empleados
Retorna los nombres de los clientes, tal que esos nombres no son nombres de empleados.
SELECT nombre
FROM Cliente
INTERSECTSELECT nombre
FROM Empleados
Retorna los nombres de los clientes que también son nombres de empleados.
CROSS APPLY, OUTER APPLY
Simplemente mencionar que CROSS APPLY retorna el producto cartesiano entre la tabla de la izquierda y los valores de la derecha que no son NULL, y OUTER APPLY retorna lo mismo pero incluye los valores de la derecha que son NULL
UDFs
Date and Time Functions
En SQL Server 2008 se agregan los nuevos tipos:
SYSDATETIME
SYSDATETIMEOFFSET
SYSUTCDATETIMEPara menos precisión podemos utilizar:
GETDATE
GETUTCDATE
CURRENT_TIMESTAMP@@ERROR retorna el número del error más reciente en la sessión actual
@@TRANCOUNT retorna el número de transaciones activas en la sessión actual
EN CONSTRUCCION . . .
Resumen Examen 70-433 - Chapter 2 Modifying Data (en Español)
Chapter 2 - Modifying Data
OUTPUT es agregado en SQL Server 2005, en SQL Server 2008 se agrega MERGE el cual sirve para realizar merge sobre las tablas como se verá más adelante
Omitiré los comentarios acerca de INSERT, UPDATE y DELETE.
IDENTITY es una propiedad que es usada para crear una columna identity, esta es una columna que asigna valores incrementales por cada fila insertada.
De esta manera podemos insertar un específico identity, para ello debemos especificar los campos de la tabla y además el id a insertar no puede ser repetido, ej:
TRUNCATE TABLE vs DELETE TABLE
1- TRUNCATE es más rápido
2- TRUNCATE reinicia las columnas identity (si existen en la tabla)
3-TRUNCATE no tiene WHERE, borra todo.
4-DELETE logea informacion de cada fila borrada, TRUNCATE sòlo crea entradas de las páginas.
OUTPUT
OUTPUT puede retornar información de las filas afectadas por INSERT, UPDATE o DELETE
OUTPUT se utiliza para auditar, por ejemplo de la siguiente manera:
La query anterior, inserta en la tabla clientes y además en OUTPUT se indica que se guarda en la tabla audit la fecha y el ID del cliente insertado.
MERGE
MERGE INTO TARGET_TABLE
USING SOURCE_TABLE
ON TARGET_TABLE.ID = SOURCE_TABLE.ID
WHEN NOT MATCHED BY TARGET THEN
INSERT (.....)
VALUES (....)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET TARGET_TABLE.CAMPO = ....
TRANSACCIONES
Las transacciones se inician con BEGIN TRANSACTION, se confirman con COMMIT y se deshacen con ROLLBACK
Por ejemplo:
BEGIN TRANSACTION
INSERT INTO...
...
IF @@ERROR= 0
COMMIT TRANSACTION;
ELSE
ROLLBACK;
Aclaracion: ROLLBACK no resetea los campos identity
Se puede utilizar la variable global @@trancount para saber la cantidad de transacciones abiertas en la session actual
OUTPUT es agregado en SQL Server 2005, en SQL Server 2008 se agrega MERGE el cual sirve para realizar merge sobre las tablas como se verá más adelante
Omitiré los comentarios acerca de INSERT, UPDATE y DELETE.
IDENTITY es una propiedad que es usada para crear una columna identity, esta es una columna que asigna valores incrementales por cada fila insertada.
SET IDENTITY_INSERT NombreTabla ON;
De esta manera podemos insertar un específico identity, para ello debemos especificar los campos de la tabla y además el id a insertar no puede ser repetido, ej:
SET IDENTITY_INSERT Clientes ON;
insert into Clientes (idcliente,nombre) values
(100,'nuevo')
insert into Clientes (idcliente,nombre) values
(100,'nuevo')
TRUNCATE TABLE vs DELETE TABLE
1- TRUNCATE es más rápido
2- TRUNCATE reinicia las columnas identity (si existen en la tabla)
3-TRUNCATE no tiene WHERE, borra todo.
4-DELETE logea informacion de cada fila borrada, TRUNCATE sòlo crea entradas de las páginas.
OUTPUT
OUTPUT puede retornar información de las filas afectadas por INSERT, UPDATE o DELETE
OUTPUT se utiliza para auditar, por ejemplo de la siguiente manera:
INSERT INTO Clientes
(Nombre,Domicilio)
OUTPUT getdate(),inserted.ClienteID INTO Audit
VALUES ('Juan','Centro');
La query anterior, inserta en la tabla clientes y además en OUTPUT se indica que se guarda en la tabla audit la fecha y el ID del cliente insertado.
MERGE
MERGE INTO TARGET_TABLE
USING SOURCE_TABLE
ON TARGET_TABLE.ID = SOURCE_TABLE.ID
WHEN NOT MATCHED BY TARGET THEN
INSERT (.....)
VALUES (....)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET TARGET_TABLE.CAMPO = ....
TRANSACCIONES
Las transacciones se inician con BEGIN TRANSACTION, se confirman con COMMIT y se deshacen con ROLLBACK
Por ejemplo:
BEGIN TRANSACTION
INSERT INTO...
...
IF @@ERROR= 0
COMMIT TRANSACTION;
ELSE
ROLLBACK;
Aclaracion: ROLLBACK no resetea los campos identity
Se puede utilizar la variable global @@trancount para saber la cantidad de transacciones abiertas en la session actual
TRANSACTION ISOLATION LEVELS
(EN CONSTRUCCION)Chapter 3 - Tables, Data Types, and Declarative Data Integrity
Chapter 3 - Tables, Data Types, and Declarative Data Integrity
Tipos de Caracteres
Los tipos de string en SQL Server son: char, varchar, nchar, nvarchar, text y ntext.
Se recomienda evitar el uso de text y ntext, ya que son obsoletos, deben ser reemplazados por varchar(max) y nvarchar(max)
La diferencia entre char y varchar es que Char tiene largo fijo, mientras que varchar es de largo variable
Osea que char siempre ocupa en memoria el largo definido, mientras que varchar sólo utiliza la memoria que necesita para el largo del valor que se introduce en la variable.
COLLATION
El Collation es usado para ordenar y comparar la información guardada en las columnas, hay 2397 variaciones de collations en SQL Server 2008, por ej. Latin1_General_CI_AI
CI significa Case insensitive (opuesto CS)
AI significa Acent insensitive (opuesto AS)
Si se desea usar una Collation diferente en una comparación de una columa, se debe especificar, por ejemplo:
Select...
From...
Where Direccion = 'öslo" COLLATE Finnish_Swedish_CI_AS
Aclaración: En estos casos no se hace uso del índice
TIPOS DE DATOS
Tipos de Datos
Tipo Bytes Rango
tinyint (2^8) 0 255
smallint (2^16) -32768 32767
int (2^32) -2147483648 2147483647
bigint (2^64) -2 exp63 2 exp 63 -1
decimal 2 a 17 bytes depende de la precision.
Tipos de Datos Fecha
Tipo Bytes Rango
datetime (2^8) 1/1/1753 - 31/12/9999 (Tratar de evitarlo)
smalldatetime (2^4) 1/1/1900 - 6/6/2079 (Tratar de evitarlo)
datetime2 (2^6 - 2^8) 1/1/0001 - 31/12/9999
datetimeoffset (2^8 - 2^10) 1/1/0001 - 31/12/9999 (cuando time zone offset es requerido)
date (2^3) 1/1/0001 - 31/12/9999
time (2^3 - 2^5) 00:00:00 - 23:59:59
Identificadores
El primer caracter debe ser una letra o un guión bajo (_), no puede ser un digito
El caracter @ se utiliza para definir variables o parametros.
El caracter # se utiliza para definir objetos temporales
El caracter ## se utiliza para definir objetos temporales globales
El identificador no debe ser una palabra reservada de T-SQL
No se pueden ingresar espacios ni caracteres especiales.
Todo Identificador que no se adhiera al standar, debe ser delimitado usando (") o ([])
Usando (") se cumple con ANSI SQL standard
De todas maneras se debe poner SET QUOTED_IDENTIFIER en ON
COMPRESSION
En SQL Server 2008 EE.
Se implementa en 2 niveles: Row y Page.
La siguiente instrucción se utiliza con page-level compression:
ALTER TABLE Clientes
REBUILD
WITH (DATA_COMPRESSION = PAGE);
ROW-LEVEL COMPRESSION
Cambia el formato usada para almacenar las rows, convierte todos los data types en data types de largo variable. (en general se benefician todos los tipo s de largo fijo)
PAGE LEVEL COMPRESSION
Introduce punteros a las rows en la misma página para evitar ordenar con redundancia de datos
Ejemplo:
Fila 1: SQL
Fila 2: Oracle
Fila 3: SQL
usando el diccionario de la página quedaria:
Fila 1:SQL
Fila2: Oracle
Fila3: Fila1.
CONSTRAINTS
Existen 5 tipos de contraints:
1- PRIMARY KEY
2- UNIQUE
3- FOREIGN KEY
4- CHECK
5- DEFAULT
PRIMARY KEY y UNIQUE
Ambas identifican una columna o una combinación de columnas en una tabla.
No pueden contener más de 16 columnas o 900 bytes de información.
Si no se especifica PRIMARY KEY crea un CLUSTERED INDEX y UNIQUE crea un NON -CLUSTERED INDEX
Se puede encontrar información en:
sys.key_constraints
sys.indexes
FOREIGN KEYS
Se usa para columna/s cuyo valor debe existir en otra columna/s en la mimsa tabla o en otra tabla de la misma base de datos.
Maneja Integridad Referencial entre tablas
Condiciones:
1-Las columnas referenciadas deben ser del mismo tipo
2-La columa referenciada debe tener una UNIQUE INDEX (usan tipicamente a PRIMARY KEY o UNIQUE)
3-Ya que la FOREIGN KEY debe referenciar a una UNIQUE INDEX, la FOREIGN KEY debe temner el mismo tamaño que la PRIMARY KEY y UNIQUE
ACCIONES SOBRE FOREIGN KEY:
-NO ACTION (por defecto)
-SET NULL
-SET DEFAULT
-CASCADE
Ejemplo:
CREATE TABLE Clientes
(
ClienteId INT PRIMARY KEY
)
CREATE TABLE Facturas
(
FacturaId INT PRIMARY KEY
, CLIENTEID INT NULL
REFERENCES Clientes
ON DELETE SET NULL
ON UPDATE CASCADE)
Si se utiliza una FOREIGN KEY con CASCADA, todos los AFTER triggers pero se ejecutan luego de que se ejecuta la acción de CASCADA.
CHECK CONSTRAINTS
Son un conjunto de reglas que se deben validar antes de permitir realizar cambios en una tabñla
1-Son fáciles de usar (similar a WHERE)
2-Se chequean automaticamente
3-Pueden mejorar el rendimiento.
Ejemplo:
ALTER TABLE Clientes
ADD CHECK (Edad>=18);
Desventajas:
1-Los mensajes de error son los generados por el sistema y no se pueden reemplazar por mensajes mas user-friendly
2-No toman en cuenta el valor anterior de la columna, por ejemplo no se puede crear una constraint que valide que el nuevo valor no sea mayor al doble del valor actual.
Se debe tomar en cuenta que las CONSTRAINTS como las del ejemlo deben considerar el caso en que el valor sea NULL, por ej AND Edad IS NOT NULL, ya que los valores null no se evaluan con True or False.
Las CONSTRAINTS pueden contener NOT, AND, OR, UDF's o CLR UDF's, por ejemplo se podría programar en c# una expression regular para validar cierto campo.
Cuando se crea una FOREIGN KEY, esta se considera como "trusted", pero si se ejecuta una FOREIGN KEY, y luego se ejecuta algo como:
ALTER TABLE Clientes NOCHECK CONSTRAINT FKClientesFacturas
y luego:
ALTER TABLE Clientes CHECK CONSTRAINT FKClientesFacturas
Si se realiza un plan a una query que tenga un EXISTS que valide la CONSTRAINT se toma como si la tabla clientes no tuviese CONSTRAINT, ya que se pudieron haber insertado valores en las tablas que no cumpliesen con la CONSTRAINT, esto se puede ver en :
SELECT name, is_not_trusted FROM sys.foreign_keys WHERE name = 'FKClientesFacturas', si devuelve 1 indica que no es "trusted" (segura)
Para evitar estos casos, se debe volver a setear la CONSTRAINT con WITH CHECK
ALTER TABLE Clientes
WITH CHECK
CHECK CONSTRAINT FKClientesFacturas
Chapter 4 - Using Additional Query Techniques
Chapter 4 - Using Additional Query Techniques
COMMON TABLE EXPRESSIONS
CTE se define en 2 partes:
1-Una clausula WITH que contiene un SELECT que genera la tabla
2-Un SELECT que hace referencia a la expression de la tabla definida con WITH
Ejemplo:
WITH FacturasCTE AS
(SELECT idCliente, COUNT(*) Cant
FROM Facturas
GROUP BY idCliente)
SELECT c.Nombre,c.Apellido,....a.Cant
FROM FacturasCTE a INNER JOIN Clientes c ON c.Id=a.IdCliente
GO
Se puede utilizar MAXRECURSION para hacer una recursión sobre el CTE, utilizando:
SELECT...
...
OPTION (MAXRECURSION 100);
EN CONSTRUCCION (RECURSION). . .
OMITO SUBQUERYS...
RANKING FUNCTIONS
Existen 4 funciones para rankear la información:
1- ROW_NUMBER
2-RANK
3-DENSE_RANK
4-NTILE
ROW_NUMBER
ROW_NUMBER es una función que se usa para asignar números de filas de acuerdo a una "clausula", por ejemplo:
SELECT nombre, ROW_NUMBER() OVER(ORDER BY EDAD DESC) AS 'Row'
FROM...
WHERE...
También se puede utilizar en un funciones de agregacion para asignar numeracion a los conjuntos agrupados
SELECT nombre, ROW_NUMBER() OVER (PARTITION BY idCli ORDER BY Nombre DESC) as 'Row'
FROM...
WHERE....
Esta consulta tendrá una numeración distinta para cada cliente, 1.2.3...->nuevo cliente 1.2.3...etc...
RANK
Rank es una función similar a ROW_NUMBER sólo que asocia el mismo valor para las filas que estàn relacionadas (INNER JOIN ON...) y saltea las repetidas de la misma para asignar el próximo número
DENSE_RANK()
Similar a RANK, sólo que no hace "gap", no saltea los números que se repiten en las filas, sino que lo hace correlativo
NTILE
Se usa para dividir el resultado en conjuntos de apróx. los mismos grupos, por ejemplo si se quiere separar en 10 GRUPOS se usa NTILE(10)
SELECT .... , NTILE(10) OVER (ORDER BY ClienteID DESC) 'IdGrupo'
FROM...
WHERE...
Chapter 5 - SPS, UDF, Triggers and Views
Chapter 5 - Programming Microsoft SQL Server with T-SQL User-Defined Stored Procedures, Functions, Triggers, and Views
STORED PROCEDURES
La primera vez que un SP es accedido SQL Server genera un compilado y un plan de ejecución en query cache y lo reusa para las execuciones siguientes. (lo cual es un beneficio de performance)
De todas maneras el principal propósito de los SPs es proveer seguridad en la capa y una API para aislar aplicaciones de cambios en la base de datos.
La clausula ENCRYPTION para los SPs no encripta el SPs, sino que hace inlegible el texto para cualquiera que no tenga VIEW DEFINITION sobre el objeto.
El delimitador es GO, cuando se encuentra un GO otra subsecuencia es considerada en la query.
GLOBAL VARIABLES
@@ERROR error de la ultima sentencia ejecutada
@@IDENTITY valor del ultimo valor identity insertado con conexión
@@ROWCOUNT cantidad de rows affectadas en la ultima sentencia
@@TRANCOUNT cantidad de transacciones abiertas en la conexión
@@VERSION version de SQL
Importante: datos de text, ntext e image han quedado en desuso y no se debe utilizar
Asignaciones en SQL SERVER 2008
SET @a = 1
SET @a +=1
SET @a *=1
SET @a /=1
CONTROLES DE FLUJO
-RETURN (Se usa para devolver un valor, el código siguiente de RETURN no es ejecutado)
-IF...ELSE
-BEGIN...END
-WHILE
-BREAK/CONTINUE (Evitar esto)
-WAITFOR (WAITFOR DELAY '00:00:02', WAITFOR TIME, WAITFOR RECEIVE)
-GOTO (Evitar esto)
MENSAJES DE ERROR
1-Numero (1-49999)
2-Nivel de Severidad (0 - 25)
3-Mensaje (hasta 255 caracteres unicode y permite 2 parametros)
Niveles de Seguridad
=>16 son logeados en el Error log de SQL y Windows Application Event Log
19 a 25 pueden ser especificados sólo por miembros de sysadmin server
20 a 25 son considerados fatales y causan que la conexion se termine y todas las operaciones abiertas de transaccion se hagan rolled back
Mensajes
Se pueden crear mensajes de error customizados, desde el número 50001 (50000 es reservado para designar mensajes cuyo numero no es especificado)
Se pueden localizar en cada lenguaje, pero se debe crear una versión en ingles antes de crear una version no-ingles.
EXEC sp_addmessage
50001 --@msgnum
,16 --@severity
,N'The customer %s must be owner in %d' --@msgtext
,'us_english'; -- @lang
para otros lenguajes los parametros %s y %d se deben especificar con %1! y %2!
Para enviar un mensaje de error ejecutando el comando RAISEERROR se debe usar la siguiente sintaxis:
RAISEERROR msg_id | msg_str | @localvariable , severidad, etc....
Se pueden ver los mensajes disponibles en:
sys.messages
TRY...CATCH
CONTINUAR.A . . .
