CONEXION REMOTA DESHABILITADO A SERVIDORES SSAS, SSRS, SSIS

PROBLEMA

Las opciones para conectarse a servidores remotos de ANALYSIS SERVICES, INTEGRATION SERVICES y REPORTING SERVICES, se encuentran deshabilitadas.

SOLUCION

Al momento de realizar la instalación de nuestro cliente SQL SERVER, requerimos de instalar alguna opción adicional, ejecutamos la instalación indicando que agregaremos características a una instancia( aunque en un cliente no tenemos instancia obviamente).

Como no leí las letras pequeñas, no me dí cuenta que instalando las herramientas básicas no me permitiría conectarme a los servidores de SSAS, SSRS, SSIS, ahora, activando las herramientas completas puedo conectarme.

Espero que les sirva, saludos!

Continuar...

ELECCION ENTRE TIPOS DE DATOS, AHORRANDO ESPACIO

Después de un receso, regreso con nuevos artículos y durante este tiempo, les puedo comentar que he observado diferentes BDs en las cuales usan versiones recientes de SQL SERVER y aun no conocen los diferentes y nuevos tipos de datos que ofrece, esto servirá no solo para ahorrar espacio en disco duro si no también para pasar o consumir menos ancho de banda, cache del equipo, entre muchas otras cosas, sin más les explicaré algunos nuevos tipos de datos que ofrecen las nuevas versiones.

INT o INTEGER

Como bien conocemos, guarda solo enteros positivos o negativos, pero sabías que existe más de un tipo de dato INT y que cada uno ocupa diferente espacio en disco duro? Bien, te dejo una siguiente tabla extraída de la página de MSDN

Tipo de datos	Intervalo	Almacenamiento
bigint	De -2^63 (-9.223.372.036.854.775.808) a 2^63-1 (9.223.372.036.854.775.807)	8 bytes
int	De -2^31 (-2.147.483.648) a 2^31-1 (2.147.483.647)	4 bytes
smallint	De -2^15 (-32.768) a 2^15-1 (32.767)	2 bytes
tinyint	De 0 a 255	1 byte

El tipo de dato TINYINT, solo almacena números positivos como podemos observar en la tabla y solo ocupa 1 byte de espacio, pero se preguntarán sobre algún caso en la vida real en la cual se pudiera utilizar dicho tipo de dato, pues bien, un ejemplo claro sería la edad, o no? Si comparamos un tipo de dato INT con el tipo TINYINT para alguien que tiene una edad de 50, no es lo mismo que en disco duro ocupe o que pasen por la red 4 bytes, a que sea solo 1 byte, pero que pasaría si tuviéramos una tabla con 1 millón de filas, estaríamos almacenando en disco duro y pasando por la red 4,000,000 de bytes con el tipo de dato INT, mientras que con TINYINT solo tendríamos 1,000,000 de bytes, veamos un ejemplo de esto, la primer tabla muestra los extremos de los valores permitidos en cada columna así como si almacenáramos un 1 en cada una de ellas.

DECLARE @tab TABLE(
 colBIGINT BIGINT
 , colINT INT
 , colSMALLINT SMALLINT
 , colTINYINT TINYINT
)
INSERT INTO @tab
VALUES( -9223372036854775808 , -2147483648, -32768, 0 )
, ( 1 , 1 , 1 , 1 )
, ( 9223372036854775807 , 2147483647 , 32767 , 255 )

SELECT * FROM @tab

SELECT DATALENGTH( colBIGINT ) AS lnColBIGINT
, DATALENGTH( colINT ) AS lnColINT
, DATALENGTH( colSMALLINT ) AS lnColSMALLINT
, DATALENGTH( colTINYINT ) AS lnColTINYINT
FROM @tab

DECIMAL, NUMERIC.

Por lo general estamos acostumbrados a usar el tipo de datos FLOAT, debemos de cambiar esa costumbre y usar DECIMAL o NUMERIC, porque su precisión es más exacta que FLOAT o REAL, observen el siguiente ejemplo:

DECLARE @tab TABLE ( 
 colDECIMAL DECIMAL(20,10)
 , colNUMERIC NUMERIC(20,10)
 , colFLOAT FLOAT(10)
 , colREAL REAL
)

INSERT INTO @tab VALUES( 1234567890.0123456789, 1234567890.0123456789 , 1234567890.0123456789 , 1234567890.0123456789 )

SELECT *
, CAST( colFLOAT AS DECIMAL(20,10) )
, CAST( colREAl AS DECIMAL(20,10) )
 FROM @tab

Y para el espacio en disco duro, tenemos más amplia variedad de opciones a elegir de la cantidad de dígitos que utilizaremos en DECIMAL y NUMERIC que en FLOAT y REAL, este ultimo no es posible escoger la precisión, es equivalente a un FLOAT(24).

DECIMAL, NUMERIC.

Precisión	Bytes de almacenamiento
1 - 9	5
10-19	9
20-28	13
29-38	17

FLOAT

Valor del parámetro n	Precisión	Tamaño de almacenamiento
1-24	7 dígitos	4 bytes
25-53	15 dígitos	8 bytes

REAL = FLOAT(24)

MONEY , SMALLMONEY

De la misma manera, es posible almacenar valores tipo moneda, pero debemos poner mucha atención a la longitud que utilizaremos, ya que tenemos dos opciones similares que pueden ocupar diferente espacio en disco duro.

Tipo de datos	Intervalo	Almacenamiento
money	De -922,337,203,685,477.5808 a 922,337,203,685,477.5807	8 bytes
smallmoney	De - 214.748,3648 a 214.748,3647	4 bytes

Veamos el siguiente ejemplo donde claramente podemos observar cada uno de los tipos, así como el espacio en disco duro utilizado.

DECLARE @tab TABLE(
 colMONEY MONEY
 , colSMALLMONEY SMALLMONEY
)

INSERT INTO @tab
VALUES( 123456789123456.7891 , 123456.7891 )
, ( 1.1 , 1.1 )
, ( -123456789123456.7891 , -123456.7891 )

SELECT * FROM @tab

SELECT DATALENGTH( colMONEY ) AS lnColMONEY
, DATALENGTH( colSMALLMONEY ) AS lnColSMALLMONEY
FROM @tab

TIPOS DE DATOS FECHA

En este rubro, tenemos varios tipos los cuales debemos conocer para saber cual debemos utilizar de acuerdo al rango que estemos manejando, veamos los tipos y el espacio ocupado en disco duro.

DATE

El rango es de 0001-01-01 a 9999-12-31 con un espacio en disco duro de 3 bytes, no importa la fecha que se utilice de ese rango:

DECLARE @tab TABLE( 
 colDATE DATE )

INSERT INTO @tab
VALUES( '00010101' ) 
, ( CAST( GETDATE() AS DATE ) ) 
, ( '99991231' )

SELECT *, DATALENGTH( colDATE ) AS lnColDate
 FROM @tab 

DATETIME

El rango es de 1753-01-01 00:00:00.000 a 9999-12-31 23:59:59.997 con un espacio en disco duro de 8 bytes, no importa la fecha que se utilice del rango:

DECLARE @tab TABLE( 
 colDATETIME DATETIME )

INSERT INTO @tab
VALUES( '17530101 00:00:00.000' ) 
, ( GETDATE() ) 
, ( '99991231 23:59:59.997' )

SELECT *, DATALENGTH( colDATETIME ) AS lnColDateTime
 FROM @tab

DATETIME2

El rango es un poco más amplio y además la precisión de los decimales es mayor, acepta un parámetro que define este ultimo dato que es de 0 a 7 digitos, el rango es de de 0001-01-01 00:00:00.0000000 a 9999-12-31 23:59:59.9999999 con un espacio en disco duro que varía de acuerdo a la precisión establecida, si no establecemos una precisión, el default es 7 por lo tanto ocupará 8 bytes:

Precision	Almacenamiento
0-2	6 bytes
3-4	7 bytes
5-7	8 bytes

Veamos un ejemplo de lo que es posible almacenar cada tipo de dato y los bytes que utiliza:

DECLARE @tab TABLE( 
 colDATETIME2SinPrecision DATETIME2
 ,colDATETIME20 DATETIME2(0)
 ,colDATETIME21 DATETIME2(1)
 ,colDATETIME22 DATETIME2(2) 
 ,colDATETIME23 DATETIME2(3)
 ,colDATETIME24 DATETIME2(4)
 ,colDATETIME25 DATETIME2(5)
 ,colDATETIME26 DATETIME2(6)
 ,colDATETIME27 DATETIME2(7)
)

INSERT INTO @tab
VALUES( '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 ) 
 , ( CAST( GETDATE() AS DATETIME2 )
 , CAST( GETDATE() AS DATETIME2(0) )
 , CAST( GETDATE() AS DATETIME2(1) )
 , CAST( GETDATE() AS DATETIME2(2) )
 , CAST( GETDATE() AS DATETIME2(3) )
 , CAST( GETDATE() AS DATETIME2(4) )
 , CAST( GETDATE() AS DATETIME2(5) )
 , CAST( GETDATE() AS DATETIME2(6) )
 , CAST( GETDATE() AS DATETIME2(7) )
 ) 
 , ( '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 ) 

SELECT * FROM @tab 

SELECT DATALENGTH( colDATETIME2SinPrecision ) AS lncolDATETIME2SinPrecision0
, DATALENGTH( colDATETIME20 ) AS lnColDATETIME20
, DATALENGTH( colDATETIME21 ) AS lnColDATETIME21
, DATALENGTH( colDATETIME22 ) AS lnColDATETIME22
, DATALENGTH( colDATETIME23 ) AS lnColDATETIME23
, DATALENGTH( colDATETIME24 ) AS lnColDATETIME24
, DATALENGTH( colDATETIME25 ) AS lnColDATETIME25
, DATALENGTH( colDATETIME26 ) AS lnColDATETIME26
, DATALENGTH( colDATETIME27 ) AS lnColDATETIME27
FROM @tab

DATETIMEOFFSET

Como aclaración, no explicaré el funcionamiento, solo la manera en que podemos ahorrar espacio: de la misma manera que DATETIME2, usando una menor precisión, solo que este tipo de datos ocupará un poco más de espacio porque también almacena la zona horaria proporcionada, veamos el ejemplo y podremos darnos cuenta de los bytes que almacena de acuerdo a la precisión, recuerden si no utilizan el parámetro de precisión el default será 7 y por lo tanto estarán ocupando el mayor espacio posible para el tipo de dato:

DECLARE @tab TABLE( 
 DATETIMEOFFSETSinPrecision DATETIMEOFFSET
 ,DATETIMEOFFSET0 DATETIMEOFFSET(0)
 ,DATETIMEOFFSET1 DATETIMEOFFSET(1)
 ,DATETIMEOFFSET2 DATETIMEOFFSET(2) 
 ,DATETIMEOFFSET3 DATETIMEOFFSET(3)
 ,DATETIMEOFFSET4 DATETIMEOFFSET(4)
 ,DATETIMEOFFSET5 DATETIMEOFFSET(5)
 ,DATETIMEOFFSET6 DATETIMEOFFSET(6)
 ,DATETIMEOFFSET7 DATETIMEOFFSET(7)
)


INSERT INTO @tab
VALUES( '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 ) 
 , ( CAST( GETDATE() AS DATETIMEOFFSET )
 , CAST( GETDATE() AS DATETIMEOFFSET(0) )
 , CAST( GETDATE() AS DATETIMEOFFSET(1) )
 , CAST( GETDATE() AS DATETIMEOFFSET(2) )
 , CAST( GETDATE() AS DATETIMEOFFSET(3) )
 , CAST( GETDATE() AS DATETIMEOFFSET(4) )
 , CAST( GETDATE() AS DATETIMEOFFSET(5) )
 , CAST( GETDATE() AS DATETIMEOFFSET(6) )
 , CAST( GETDATE() AS DATETIMEOFFSET(7) )
 ) 
 , (  '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 ) 

SELECT * FROM @tab 

SELECT DATALENGTH( DATETIMEOFFSETSinPrecision ) AS lnDATETIMEOFFSETSinPrecision
, DATALENGTH( DATETIMEOFFSET0 ) AS lnDATETIMEOFFSET0
, DATALENGTH( DATETIMEOFFSET1 ) AS lnDATETIMEOFFSET1
, DATALENGTH( DATETIMEOFFSET2 ) AS lnDATETIMEOFFSET2
, DATALENGTH( DATETIMEOFFSET3 ) AS lnDATETIMEOFFSET3
, DATALENGTH( DATETIMEOFFSET4 ) AS lnDATETIMEOFFSET4
, DATALENGTH( DATETIMEOFFSET5 ) AS lnDATETIMEOFFSET5
, DATALENGTH( DATETIMEOFFSET6 ) AS lnDATETIMEOFFSET6
, DATALENGTH( DATETIMEOFFSET7 ) AS lnDATETIMEOFFSET7
FROM @tab

TIME

De la misma manera, este tipo de dato usa un parámetro que es la precisión de hasta 7 decimales, si no utilizamos el parámetro, el default es 7 lo cual ocupa un espacio de 5 bytes, si no lo ocupan, en nuestro diseño de base de datos establezcamos la precisión a 0, veamos el ejemplo:

DECLARE @tab TABLE( 
 TIMESinPrecision TIME
 ,TIME0 TIME(0)
 ,TIME1 TIME(1)
 ,TIME2 TIME(2) 
 ,TIME3 TIME(3)
 ,TIME4 TIME(4)
 ,TIME5 TIME(5)
 ,TIME6 TIME(6)
 ,TIME7 TIME(7)
)


INSERT INTO @tab
VALUES( '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 , '00010101 00:00:00.1234567'
 ) 
 ,( CAST( GETDATE() AS TIME )
 , CAST( GETDATE() AS TIME(0) )
 , CAST( GETDATE() AS TIME(1) )
 , CAST( GETDATE() AS TIME(2) )
 , CAST( GETDATE() AS TIME(3) )
 , CAST( GETDATE() AS TIME(4) )
 , CAST( GETDATE() AS TIME(5) )
 , CAST( GETDATE() AS TIME(6) )
 , CAST( GETDATE() AS TIME(7) )
 ) 
 , ( '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 , '99991231 23:59:59.9999999'
 ) 

SELECT * FROM @tab 

SELECT DATALENGTH( [TIMESinPrecision] ) AS lnTIMESinPrecision
, DATALENGTH( TIME0 ) AS lnTIME0
, DATALENGTH( TIME1 ) AS lnTIME1
, DATALENGTH( TIME2 ) AS lnTIME2
, DATALENGTH( TIME3 ) AS lnTIME3
, DATALENGTH( TIME4 ) AS lnTIME4
, DATALENGTH( TIME5 ) AS lnTIME5
, DATALENGTH( TIME6 ) AS lnTIME6
, DATALENGTH( TIME7 ) AS lnTIME7
FROM @tab

A partir de las versiones más recientes de SQL SERVER ya tenemos muchos más tipos de datos que podemos utilizar para ahorrar espacio no solo en disco duro si no también la cantidad de información que se transmitirá por la red, la cantidad de información que procesará nuestro servidor etcétera, por lo que les recomiendo ampliamente que tengan especial cuidado al momento de elegir dichos tipos de dato, sobre todo nunca elijan un tipo de dato que tenga una precisión default, sabiendo que nunca utilizarán dicha precisión en la información que pueden establecer a 0 para ocupar la menor cantidad de bytes.

Espero les sirva en su próximo diseño de tablas.

SALUDOS.

Continuar...

DELETE y TRUNCATE

Como bien sabemos, ambas instrucciones sirven para eliminar filas de nuestras tablas, con algunas diferencias y características propias que a continuación indicaré.

Para los ejemplos, utilizaremos las siguientes tablas, como recomendación ejecuten el siguiente script antes de ejecutar cada consulta de ejemplo:

-- ELIMINAMOS LAS TABLAS EN CASO QUE EXISTAN.
IF OBJECT_ID( 'dbo.empleados' , 'U' ) IS NOT NULL
 DROP TABLE dbo.empleados

IF OBJECT_ID( 'dbo.paisOrigen' , 'U' ) IS NOT NULL
 DROP TABLE dbo.paisOrigen

-- CREAMOS LAS TABLAS
CREATE TABLE dbo.paisOrigen
(
 cvePaisOrigen SMALLINT IDENTITY( 1,1 )
 , nombre VARCHAR( 50 )
 , CONSTRAINT PKpaisOrigen PRIMARY KEY ( cvePaisOrigen )
)

CREATE TABLE dbo.empleados
(
 cveEmpleado SMALLINT IDENTITY(1,1)
 ,nombre VARCHAR(50) 
 , cvePaisOrigen SMALLINT
 , CONSTRAINT PKempleados PRIMARY KEY ( cveEmpleado )
 , CONSTRAINT FKpaisOrigen FOREIGN KEY ( cvePaisOrigen ) REFERENCES dbo.paisOrigen( cvePaisOrigen )
)

-- INSERTAMOS VALORES EN LAS TABLAS
INSERT INTO dbo.paisorigen( nombre )
VALUES( 'MEXICO' ) , ('ESTADOS UNIDOS') , ( 'COLOMBIA' )

INSERT INTO dbo.empleados( nombre, cvePaisOrigen )
VALUES( 'PEDRO' , 1 ) , ('LUIS' , 2 ) , ( 'EDUARDO', 3 )

-- VERIFICAMOS EL CONTENIDO DE LAS TABLAS(opcional)
--SELECT * FROM dbo.paisOrigen

--SELECT * FROM dbo.empleados

DELETE

Es posible borrar las filas de una tabla, algunos ejemplos :

-- BORRANDO TODA LA TABLA
DELETE FROM dbo.empleados;

SELECT * FROM dbo.empleados; 

-- BORRAR FILTRANDO LOS DATOS
DELETE FROM dbo.empleados
WHERE cveEmpleado = 3;

SELECT * FROM dbo.empleados;  

-- BORRAR UTILIZANDO RELACION ENTRE TABLAS
DELETE e
FROM dbo.empleados e 
 INNER JOIN dbo.paisOrigen p
 ON p.cvePaisOrigen = e.cvePaisOrigen
WHERE p.nombre LIKE '%MEX%';

SELECT * FROM dbo.empleados; 

--UTILIZANDO CTEs Y RELACION ENTRE TABLAS NO ES POSIBLE REALIZAR.
;WITH cte AS(
 SELECT e.*
 FROM dbo.empleados e 
  INNER JOIN dbo.paisOrigen p
  ON p.cvePaisOrigen = e.cvePaisOrigen
 WHERE p.nombre LIKE '%MEX%'
)

DELETE FROM cte;

-- SI ES POSIBLE CUANDO SOLO ES UNA TABLA
;WITH cte AS(
 SELECT * FROM dbo.empleados
 WHERE cveEmpleado = 3
)

DELETE FROM cte;

SELECT * FROM dbo.empleados; 

-- TAMBIEN UTILIZANDO TOP 
;WITH cte AS(
 SELECT TOP (2) * 
 FROM dbo.empleados
 ORDER BY cveEmpleado DESC 
) 

DELETE FROM cte;

SELECT * FROM dbo.empleados;

-- TAMBIEN USANDO TABLAS DERIVADAS
DELETE x FROM (
 SELECT TOP (2) * 
 FROM dbo.empleados
 ORDER BY cveEmpleado DESC 
) AS x;

SELECT * FROM dbo.empleados;

-- TAMBIEN UTILIZANDO ROW_NUMBER
;WITH cte AS(
 SELECT ROW_NUMBER() OVER( ORDER BY nombre ) AS rn, * 
 FROM dbo.empleados
) 

DELETE FROM cte
WHERE rn > 2;

SELECT * FROM dbo.empleados; 

-- EL MISMO EJEMPLO CON TABLAS DERIVADAS
DELETE x FROM (
 SELECT ROW_NUMBER() OVER( ORDER BY nombre ) AS rn, * 
 FROM dbo.empleados
) AS x
WHERE rn > 2;

SELECT * FROM dbo.empleados;

Para las tablas que estamos utilizando, no toma mucho tiempo en ejecutar las consultas pero si tenemos una tabla con gran cantidad de datos, tomaría mucho tiempo en eliminar las filas, por lo tanto pueden utilizar un script como el siguiente, suponiendo que una tabla contiene 15,000,000 de filas y queremos borrar de una consulta que nos arroja 1,001,250 filas:

WHILE 1 = 1
BEGIN
 DELETE TOP (1000) FROM dbo.nombreTabla
 WHERE cveProducto = 5;
 IF @@rowcount < 1000 BREAK;
END

Es una iteración que permite eliminar las primeras 1000 filas de la consulta, sabiendo que en realidad dicha consulta originalmente sin usar el TOP devuelve 1,001,250 filas, llegará un momento en que la consulta anterior solo arrojará como resultado 250 y es cuando la iteración habrá terminado.

TRUNCATE

No es posible filtrar las filas que deseamos borrar, por lo tanto hay que tener mucho cuidado al momento de utilizarla porque puede borrar todos los datos de una tabla.

TRUNCATE TABLE dbo.empleados;
SELECT * FROM dbo.empleados;

Algunas diferencias:

• TRUNCATE es mucho más rápido que DELETE ya que no escribe tanta información en el log de transacciones.
• TRUNCATE reinicia la numeración de una columna si está fue declarada con la propiedad IDENTITY, DELETE no.

DELETE FROM dbo.empleados 
WHERE nombre LIKE 'EDUARDO';

SELECT * FROM dbo.empleados;

INSERT INTO dbo.empleados ( nombre, cvePaisOrigen )
VALUES( 'ALBERTO' , 3 ); 

SELECT * FROM dbo.empleados;

TRUNCATE TABLE dbo.empleados;

SELECT * FROM dbo.empleados;

INSERT INTO dbo.empleados ( nombre, cvePaisOrigen )
VALUES( 'ALBERTO' , 3 ); 

SELECT * FROM dbo.empleados;

• DELETE requiere de permisos DELETE sobre la tabla, TRUNCATE requiere permisos ALTER sobre la tabla.
• TRUNCATE no activa algún disparador porque no registra eliminaciones, DELETE si lo hace.

-- AVERIGUAMOS SI EXISTE EL TRIGGER, DE SER ASI, LO ELIMINAMOS
IF OBJECT_ID('dbo.trDelete','TR') IS NOT NULL
 DROP TRIGGER dbo.trDelete
GO

-- CREAMOS DE NUEVO EL TRIGGER
CREATE TRIGGER dbo.trDelete
ON dbo.empleados
AFTER DELETE
AS
 SELECT * 
 FROM deleted;
GO


TRUNCATE TABLE dbo.empleados;

Nuevamente volvemos a ejecutar el primer script de este post y después:

-- AVERIGUAMOS SI EXISTE EL TRIGGER, DE SER ASI, LO ELIMINAMOS
IF OBJECT_ID('dbo.trDelete','TR') IS NOT NULL
 DROP TRIGGER dbo.trDelete
GO

-- CREAMOS DE NUEVO EL TRIGGER
CREATE TRIGGER dbo.trDelete
ON dbo.empleados
AFTER DELETE
AS
 SELECT * 
 FROM deleted;
GO

DELETE FROM dbo.empleados;

• TRUNCATE no se puede ejecutar en una tabla donde su llave primaria es utilizada en otra tabla como FOREIGN KEY, aun cuando no existan filas relacionadas. DELETE si lo hace.

TRUNCATE TABLE dbo.empleados

TRUNCATE TABLE dbo.paisOrigen

DELETE FROM dbo.empleados

DELETE FROM dbo.paisOrigen

Recuerden que todos los scripts requieren de la ejecución del primero que aparece en este post, si tienen algún detalle o les genera algún error, favor de avisarme y les responderé tan pronto como pueda.

SALUDOS.

Continuar...

ALGUNOS TIPOS DE INSERT

SQL SERVER es capaz de insertar datos a través de diferentes métodos, algunos de ellos:

• INSERT VALUES
• INSERT SELECT
• INSERT EXEC
• SELECT INTO

INSERT VALUES

Es posible insertar una fila o varias filas a nuestra tabla destino, la sintaxis:

INSERT INTO tablaDestino( columna1, columna2, columna3, columnaN )
VALUES( valor1 , valor2 , valor3, valorN )

Alguna de sus características:

• Es posible omitir la lista de columnas después del nombre de la tabla, sin embargo se considera una buena práctica especificarlos.
• Si la lista es omitida, la lista de columnas después de VALUES debe tener el mismo orden en el que fueron creadas en la tabla, respetando el tipo de dato, longitud entre otras propiedades.
• Si existe una columna de tipo IDENTITY, esta no debe especificarse en la lista después del nombre de la tabla y por lo tanto en la lista de valores a insertar, si se desea ingresar los valores, antes de realizar la inserción debe especificarse SET IDENTITY_INSERT <nombreTabla> ON y después de realizar la inserción nuevamente SET IDENTITY_INSERT <nombreTabla> OFF. Algo más o menos así:

SET IDENTITY_INSERT dbo.tablaDestino ON; 

INSERT INTO dbo.tablaDestino( columna1, columna2, columna3, columna4 ) 
VALUES( 5 , '1valorColumna2' , '1valorColumna3' , GETDATE() )
, ( 9 , '2valorColumna2' , '2valorColumna3' , GETDATE() ); 

SET IDENTITY_INSERT dbo.tablaDestino OFF;

• Si alguna columna contiene la restricción DEFAULT, basta con no especificarla en la lista después del nombre de la tabla; por lo contrario, si se especifica y no queremos ingresar ningún valor, en la lista después de VALUES solo es necesario escribir DEFAULT en donde corresponda su valor.

IF OBJECT_ID( 'dbo.tablaDestino' ) IS NOT NULL
 DROP TABLE dbo.tablaDestino 

CREATE TABLE dbo.tablaDestino(
 columna1 INT 
 , columna2 VARCHAR(100) 
 , columna3 VARCHAR(100) DEFAULT 'valorDefault'
 , columna4 DATETIME 
);

-- NO ESPECIFICAMOS EL NOMBRE DE LA COLUMNA
INSERT INTO dbo.tablaDestino( columna1, columna2, columna4 ) 
VALUES( 5 , '1valorColumna2' , GETDATE() );

SELECT * FROM dbo.tablaDestino;

--SI ESPECIFICAMOS EL NOMBRE DE LA COLUMNA, PODEMOS ESCRIBIR DEFAULT
INSERT INTO dbo.tablaDestino( columna1, columna2, columna3, columna4 ) 
VALUES( 9 , '2valorColumna2' , DEFAULT , GETDATE() ); 

SELECT * FROM dbo.tablaDestino;

INSERT SELECT

Inserta los valores que resultan de la ejecución de una consulta.

Características:

• Es posible omitir la lista de columnas destino al igual que tipo de INSERT anterior.
• También integra la posibilidad de ingresar valores en una columna IDENTITY si antes se especifica SET IDENTITY <nombreTabla> ON.

Un ejemplo de este tipo es el siguiente:

--ELIMINAMOS LA TABLA SI EXISTE
IF OBJECT_ID( 'dbo.tablaDestino' ) IS NOT NULL
 DROP TABLE dbo.tablaDestino ;

--CREAMOS LA TABLA
CREATE TABLE dbo.tablaDestino(
 columna1 INT
 , columna2 VARCHAR(100) 
 , columna3 VARCHAR(100)
 , columna4 DATETIME 
);

--INSERTAMOS VALORES DESDE UNA CONSULTA
INSERT INTO dbo.tablaDestino
SELECT DepartmentID , Name, GroupName, ModifiedDate
FROM AdventureWorks2012.HumanResources.Department;

--VERIFICAMOS LOS VALORES INSERTADOS
SELECT * FROM dbo.tablaDestino;

INSERT EXEC

Tenemos la posibilidad de ingresar el resultado de la ejecución de un procedimiento almacenado a una tabla, respetando la cantidad de columnas, el tipo de datos y el orden en el que fueron creados en la tabla.

--ELIMINAMOS LA TABLA SI YA EXISTE.
IF OBJECT_ID( 'dbo.tablaDestino' ) IS NOT NULL
 DROP TABLE dbo.tablaDestino 

--CREAMOS LA TABLA
CREATE TABLE dbo.tablaDestino(
 columna1 INT 
 , columna2 VARCHAR(100) 
 , columna3 VARCHAR(100) 
 , columna4 DATETIME 
);

--VERIFICAMOS QUE LA TABLA NO CONTENGA VALORES
SELECT * FROM dbo.tablaDestino; 

--ELIMINAMOS EL PROCEDIMIENTO, SI ES QUE YA EXISTE.
IF OBJECT_ID('dbo.procedimiento', 'P') IS NOT NULL
 DROP PROC dbo.procedimiento;
GO

--CREAMOS EL PROCEDIMIENTO ALMACENADO
CREATE PROC dbo.procedimiento
 @valor AS VARCHAR(50)
AS
 SELECT DepartmentID , Name, GroupName, ModifiedDate
 FROM AdventureWorks2012.HumanResources.Department
 WHERE GroupName = @valor ;
GO

--INSERTAMOS EN LA TABLA UTILIZANDO EXEC
INSERT INTO dbo.tablaDestino
EXEC dbo.procedimiento 'Research and Development'

--VERIFICAMOS LOS DATOS INSERTADOS
SELECT * FROM dbo.tablaDestino;

SELECT INTO

También utiliza una consulta para tomar los valores e insertarlos en una tabla, solo que creará una nueva tabla para este fin, si ya existe la tabla, marcará error.

Algunas de sus características son:

• Genera una nueva tabla con el nombre especificado.
• Los datos seleccionados por la consulta son copiados así como los nombres de las columnas, tipo de datos,  si la columna permite o no NULL y la propiedad IDENTITY.
• Índices, restricciones, disparadores( triggers ), y algunas otros aspectos no son copiados, por lo tanto si se requiere es necesario modificar la tabla y agregarlos.

--ELIMINAMOS LA TABLA SI YA EXISTE.
IF OBJECT_ID( 'dbo.nuevaTablaDestino' ) IS NOT NULL
 DROP TABLE dbo.nuevaTablaDestino 

--CREAMOS LA TABLA
SELECT DepartmentID , Name, GroupName, ModifiedDate
INTO dbo.nuevaTablaDestino
FROM AdventureWorks2012.HumanResources.Department
WHERE GroupName = 'Research and Development' ;
GO

--VERIFICAMOS LOS DATOS INSERTADOS
SELECT * FROM dbo.nuevaTablaDestino;

SALUDOS.

Continuar...

CONSTRAINTS O RESTRICCIONES

Para asegurar la integridad de los datos almacenados en nuestras tablas, podemos crear restricciones, algunos los hemos utilizado sin querer o simplemente desconocemos que lo que hicimos fue una restricción, por ejemplo una llave primaria. Estas restricciones las podemos implementar al momento de crear nuestras tablas o de modificarlas, también es necesario señalar que dichas restricciones son objetos propios de la base de datos y por lo tanto requieren de un nombre único compuesto del nombre del esquema al que pertenece y el nombre que lo identifica, un ejemplo sería nombreEsquema.nombreRestriccion.

Los diferentes tipos de restricción que existen son:

• PRIMARY KEY
• UNIQUE
• FOREIGN KEY
• CHECK
• DEFAULT

PRIMARY KEY

Es la más común de todas debido a que cada una de nuestras tablas debe ser completamente relacional y para lograr esto siempre debe existir una llave primaria dentro de cada tabla que identifique cada fila como única.

Para generar una llave primaria desde la creación de una tabla:

CREATE TABLE nombreEsquema.nombreTabla
(
 nombreColumna1 INT    NOT NULL,
 nombreColumna2 VARCHAR(100)  NOT NULL,
 nombreColumna3 NVARCHAR(200) NOT NULL,
 CONSTRAINT PK_nombreRestriccion PRIMARY KEY( nombreColumna1 )
);

Modificando una tabla:

ALTER TABLE nombreEsquema.nombreTabla
ADD CONSTRAINT PK_nombreRestriccion PRIMARY KEY( nombreColumna1 );

Es posible agregar más columnas como parte de una llave primaria, se recomienda como buena práctica utilizar una nomenclatura en el nombre de la restricción que ayude a identificar de que tipo es, además de tener especial cuidado en nombrar las columnas que forman parte de la llave primaria ya que estás mismas serán utilizadas como referencia en una llave foránea en otra tabla. Cada vez que generamos una llave primaria, esta crea un índice tipo de clustered automáticamente.

Existen ciertos requerimientos para la creación de una llave primaria:

• La o las columnas utilizadas en una restricción PRIMARY KEY, no pueden aceptar NULL.
• No se pueden repetir valores en la o las columnas, deben ser únicos.
• Solamente puede existir una restricción de tipo PRIMARY KEY por cada tabla.

Para verificar  las llaves primarias contenidas en nuestra base de datos podemos utilizar el siguiente código:

SELECT *
FROM sys.key_constraints
WHERE type = 'PK';

UNIQUE

Este tipo de restricción es muy parecida a PRIMARY KEY,  las diferencias son las siguientes:

• También genera un índice automáticamente pero es de tipo de NON CLUSTERED.
• La tabla puede tener más de una restricción de tipo UNIQUE.
• Si puede aceptar NULL, pero solo una fila puede contenerlo ya que como su nombre lo indica, es de tipo UNIQUE o único. 

CREATE TABLE nombreEsquema.nombreTabla
(
 nombreColumna1 INT    NULL,
 nombreColumna2 VARCHAR(100)  NOT NULL,
 nombreColumna3 NVARCHAR(200) NOT NULL,
 CONSTRAINT UQ_nombreRestriccion UNIQUE( nombreColumna1 ),
 CONSTRAINT UQ_nombreRestriccion2 UNIQUE( nombreColumna2 ),
 CONSTRAINT UQ_nombreRestriccion3 UNIQUE( nombreColumna1,nombreColumna2 )
);

Para consultar las restricciones UNIQUE se puede utilizar:

SELECT *
FROM sys.key_constraints
WHERE type = 'UQ';

FOREIGN KEY

Se forma de una columna o la combinación de varias columnas de una tabla que sirve como enlace hacia otra tabla donde en esta última, dicho enlace son la o las columnas que forman la PRIMARY KEY. En la primera tabla donde creamos la llave foránea es posible que existan valores duplicados de la/las columnas que conforman la llave primaria de la segunda tabla, además las columnas involucradas en la llave foránea deben tener el mismo tipo de datos que la llave primaria de la segunda tabla. Una llave foránea no crea un índice automáticamente, por lo que se recomienda generar uno para incrementar el rendimiento de la consulta.

CREATE TABLE nombreEsquema.nombreTabla
(
 nombreColumna1 INT    NULL,
 nombreColumna2 VARCHAR(100)  NOT NULL,
 nombreColumna3 NVARCHAR(200) NOT NULL,
 CONSTRAINT FK_nombreRestriccion FOREIGN KEY (nombreColumna1) REFERENCES nombreEsquema.otraTabla (nombreColumna1)
);

Modificando la tabla:

ALTER TABLE nombreEsquema.nombreTabla
ADD CONSTRAINT FK_nombreRestriccion FOREIGN KEY(nombreColumna1)
REFERENCES nombreEsquema.otraTabla (nombreColumna1)

Algunos requerimientos para la restricción FOREIGN KEY:

• Los valores ingresados en la o las columnas de la llave foránea, deben existir en la tabla a la que se hace referencia en la o las columnas de la llave primaria.
• Solo se pueden hacer referencia a llaves primaria de tablas que se encuentren dentro de la misma base de datos.
• Puede hacer referencia a otra columnas de la misma tabla.
• Solo puede hacer referencia a columnas de restricciones PRIMARY KEY o UNIQUE.
• No se puede utilizar en tablas temporales.

Para consultar las restricciones FOREIGN KEY, se puede utilizar:

SELECT *
FROM sys.foreign_keys
WHERE name = 'nombreEsquema.nombreTabla’;

CHECK

Con este tipo de restricción, se especifica que los valores ingresados en la columna deben cumplir la regla o formula especificada. Por ejemplo:

CREATE TABLE nombreEsquema.nombreTabla
(
 nombreColumna1 INT    NULL,
 nombreColumna2 VARCHAR(100)  NOT NULL,
 nombreColumna3 NVARCHAR(200) NOT NULL,
 --VALORES POSITIVOS
 CONSTRAINT CH_nombreRestriccion CHECK (nombreColumna1>=0),
 -- SOLO VALORES IGUALES A 10 20 30 40
 CONSTRAINT CH_nombreRestriccion2 CHECK (nombreColumna1 IN (10,20,30,40)),
 --VALORES CONTENIDOS EN UN RANGO
 CONSTRAINT CH_nombreRestriccion3 CHECK (nombreColumna1>=1 AND nombreColumna1 <=30)
);

Modificando una tabla:

ALTER TABLE nombreEsquema.nombreTabla
ADD CONSTRAINT CH_nombreRestriccion CHECK (nombreColumna1>=0);
GO

ALTER TABLE nombreEsquema.nombreTabla
ADD CONSTRAINT CH_nombreRestriccion2 CHECK (nombreColumna1 IN (10,20,30,40));
GO

ALTER TABLE nombreEsquema.nombreTabla
ADD CONSTRAINT CH_nombreRestriccion3 CHECK (nombreColumna1>=1 AND nombreColumna1 <=30);
GO

Algunos requerimientos son:

• Una columna puede tener cualquier número de restricciones CHECK.
• La condición de búsqueda debe evaluarse como una expresión booleana y no puede hacer referencia a otra tabla.
• No se pueden definir restricciones CHECK en columnas de tipo text, ntext o image.

Ventajas:

• Las expresiones utilizadas son similares a las que se usan en la clausula WHERE.
• Pueden llegar a ser una mejor alternativa que los TRIGGERS o disparadores.

Tener siempre en mente:

• Al momento de crear nuestra expresión, tomar en cuenta si la columna acepta valores NULL, por ejemplo si definimos nuestra restricción que acepte solo valores positivos ( nombreColumna1>=0), NULL es un valor desconocido por lo tanto se insertará en la columna.
• No es posible obtener el valor previo después de realizar un UPDATE, si esto es necesario se recomienda usar un TRIGGER.

Para consultar las restricciones CHECK se puede utilizar:

SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID('nombreEsquema.nombreTabla');

DEFAULT

Se puede decir que no es una restricción, ya que solo se ingresa un valor en caso de que ninguno otro sea especificado. Si una columna permite NULL y el valor a insertar no se especifica, se puede sustituir con un valor predeterminado.

CREATE TABLE nombreTabla
(
 nombreColumna1 INT    NULL CONSTRAINT DF_nombreRestriccion DEFAULT(0),
 nombreColumna2 VARCHAR(100)  NOT NULL,
 nombreColumna3 NVARCHAR(200) NOT NULL,
);

Para obtener una lista de las restricciones DEFAULT:

SELECT *
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('nombreEsquema.nombreTabla');

Espero que les sirva de ayuda.

SALUDOS!

Continuar...

FILAS A COLUMNAS Y VICEVERSA CON PIVOT UNPIVOT.

Una práctica recurrente en nuestro diario andar, es la conversión de filas a columnas o columnas a filas, antes esto lo teníamos que hacer implementando ciertos trucos con cursores o con la clausula CASE WHEN o con algunos otros, afortunadamente hoy ya contamos con la ayuda de PIVOT y UNPIVOT.

Trabajaremos sobre la base AdventureWorks2012 y con el siguiente query:

SELECT 
YEAR( OrderDate ) AS yr
, MONTH( OrderDate ) as mn
, TotalDue
FROM sales.SalesOrderHeader
WHERE OrderDate >= '20060101' AND OrderDate < '20090101'

Con la consulta anterior obtenemos las ventas realizadas entre el periodo correspondiente, aun no los agrupamos por año y mes, pero si obtenemos esos valores de cada venta para hacer la agrupación.

Para lograr un PIVOT requerimos de 3 cosas:

1. Una columna de agrupación.
2. Una columna donde están los nombres de nuestras futuras columnas.
3. Una columna donde se encuentran los valores de las intersecciones de las 2 anteriores, a la cual sea factible aplicar una función de agregado( MAX, MIN, AVG, SUM, COUNT, etc ).

Por cuestiones de buenas prácticas se recomienda una estructura como la siguiente:

WITH cte AS
(
SELECT
< 1 >,
< 2 >,
< 3 >
FROM < tablaFuente >
)
SELECT < columnas >
FROM cte
PIVOT( < funcion de agregado > ( < 3 > )
FOR < 2 > IN (< valores contenidos en 2 >)  ) AS pt;

Ahora solo aplicamos nuestro query:

;WITH cte AS(
    SELECT 
        YEAR( OrderDate ) AS yr
        , MONTH( OrderDate ) as mn
        , TotalDue
    FROM sales.SalesOrderHeader
    WHERE OrderDate >= '20060101' AND OrderDate < '20090101'
) 
SELECT *
FROM cte
PIVOT( SUM( TotalDue ) FOR mn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS pt;
GO

UNPIVOT

Se podría decir que es el proceso que revierte el PIVOT a su estado original, lo digo así porque recuerden que los datos ingresados para realizar el PIVOT del query anterior no estaban agrupados y una vez que apliquemos el UNPIVOT aquí los regresaremos como si estuvieran agrupados por año y mes.

Necesitamos identificar al menos 2 puntos importantes:

1. Un nombre de columna a lo que anteriormente eran nuestras columnas( [1],[2],[3],etc)
2. Un nombre de columna para el valor contenido dentro de las columnas anteriores.

Una vez hecho esto, aplicamos lo siguiente:

SELECT < columna fija >, < 1 >, < 2 >
FROM < tabla fuente>
UNPIVOT( < 2 > FOR < 1 > IN( < nombre de las columnas pivoteadas > ) ) AS U;

Utilizaremos la consulta que hemos trabajado, solo que ahora utilizaremos otro cte:

;WITH cte AS(
    SELECT 
        YEAR( OrderDate ) AS yr
        , MONTH( OrderDate ) as mn
        , TotalDue
    FROM sales.SalesOrderHeader
    WHERE OrderDate >= '20060101' AND OrderDate < '20090101'
) 
, ctePivot AS( -- APLICANDO PIVOT
    SELECT *
    FROM cte
    PIVOT( SUM( TotalDue ) FOR mn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS pt
)
--aplicando UNPIVOT
SELECT yr, mes, valor  
FROM ctePivot
UNPIVOT ( valor FOR mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS unpvt;

Realmente no es tan complicado utilizar ambas sentencias, es cuestión de practicar y jugar un poco con ellas para comprender correctamente su funcionamiento.

Pueden dejar su comentario, duda, sugerencia o aclaración en el apartado de abajo.

SALUDOS

Continuar...

USANDO TOP Y FILTRANDO CON OFFSET

En ocasiones requerimos filtrar cierta cantidad de filas de nuestro conjunto de datos en el cual deseamos obtener las primeras 5 , 10 o N filas de nuestro conjunto de datos o también, nos hemos preguntado como obtener un bloque de N filas después de saltar otra N cantidad de filas, bien ahora les explicaré como podremos lograr esto con TOP y con OFFSET.

TOP

Podemos extraer las primeras N cantidad de filas de un conjunto de datos, es necesario utilizar la cláusula ORDER BY. Un ejemplo es la siguiente consulta, el resultado sin la cláusula TOP es de 316 filas:

USE AdventureWorks2012
SELECT 
TOP(8) 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID

Ahora bien, también es posible extraer un porcentaje de la cantidad total de filas con la opción PERCENT, recordemos que el total de filas es de 316, si queremos extraer un 10% el resultado sería 31.6 filas, al obtener decimales el valor se redondea al entero siguiente.

SELECT 
TOP(10) PERCENT
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

Además tenemos la opción WITH TIES, con la cual es posible obtener aquellas filas que tengan el mismo valor en las columnas declaradas en la cláusula ORDER BY, pero esto solo se aplica para la última fila de nuestro conjunto de datos filtrado por el TOP. Como podemos observar en el siguiente ejemplo, la última fila se repite el valor 48.101 en 3 ocasiones.

--sin opción WITH TIES
SELECT 
TOP(9) 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

-- usando opción WITH TIES
SELECT 
TOP(9) WITH TIES
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

Es necesario subrayar que si requerimos obtener siempre los mismos resultados, necesitamos aplicar un ORDER BY donde el motor pueda identificar que son valores únicos o utilizar la opción WITH TIES, ya que si existen valores repetidos, SQL SERVER no podrá garantizar que siempre obtenga los mismos resultados cada vez que ejecutemos la consulta.

OFFSET

Es posible saltar N cantidad de filas y obtener las N filas siguientes, un ejemplo es el siguiente:

-- Sin OFFSET
SELECT 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

-- usando OFFSET
SELECT 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC
OFFSET 10 ROWS;

Y también podemos indicarle la cantidad de filas que deseamos obtener después de realizar el salto:

SELECT 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC
OFFSET 10 ROWS
FETCH NEXT 100 ROWS ONLY;

Existen también las opciones FIRST en lugar de NEXT y ROW en lugar de ROWS, aunque si usamos en la consulta previa dichas opciones, no cambiara en nada, aunque posiblemente exista una confusión al momento de leer la consulta:

SELECT 
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC
OFFSET 10 ROW
FETCH FIRST 100 ROW ONLY;

Por lo tanto el cambio de estas opciones no afecta en el resultado. Con estas opciones es posible realizar la tan solicitada paginación de un conjunto de datos dentro de SQL SERVER, lo cual podremos ver un poco más adelante.

SALUDOS!

Continuar...

CTEs RECURSIVOS (2)

Hace algunos meses les mostré como es posible la recursividad en SQL SERVER usando CTEs, esto fue lo que hicimos en aquella ocasión usando fechas y variables de tiempo:

;WITH miCTEdias AS
(
       SELECT CAST('20130101' AS DATE) as fecha
       UNION ALL
       SELECT DATEADD( d , 1 ,fecha ) as fecha
       FROM miCTEdias
       WHERE fecha < CAST('20130110' AS DATE)
)
SELECT * FROM miCTEdias;

;WITH miCTEmeses AS
(
       SELECT CAST('20120601' AS DATE) as fecha
       UNION ALL
       SELECT DATEADD( m , 1 ,fecha ) as fecha
       FROM miCTEmeses WHERE fecha < CAST('20130501' AS DATE)
)
SELECT * FROM miCTEmeses;

;WITH miCTEhrs AS
(
       SELECT CAST('00:00:00' AS TIME) as hora
       UNION ALL
       SELECT DATEADD( HH , 1 ,hora ) as hora
       FROM miCTEhrs WHERE hora < CAST('23:00:00' AS TIME)
)
SELECT * FROM miCTEhrs;

;WITH miCTEmediaHrs AS
(
       SELECT CAST('00:00:00' AS TIME) as hora
       UNION ALL
       SELECT DATEADD( MINUTE , 30 ,hora ) as hora
       FROM miCTEmediaHrs WHERE hora < CAST('23:30:00' AS TIME)
)
SELECT * FROM miCTEmediaHrs;

Ahora les dejo otros ejemplos para calcular una secuencia de números, la serie de fibonacci y el factorial:

WITH cte AS(
 SELECT 1 AS numero
 UNION ALL
 SELECT numero+1
 FROM cte 
 WHERE numero < 1000
) SELECT * FROM cte 
OPTION ( MAXRECURSION 0 )

--fibonacci
;WITH cteFib AS(
 SELECT 1 AS numero, CAST( 0 AS BIGINT ) as fibonacci, CAST( 1 AS BIGINT ) AS numero1ant
 UNION ALL
 SELECT numero+1, fibonacci + numero1ant, fibonacci
 FROM cteFib 
 WHERE numero < 50
) 
SELECT numero, fibonacci FROM cteFib 
WHERE numero < 20
OPTION ( MAXRECURSION 0 )

--factorial
;WITH cteFac AS(
 SELECT 0 AS numero, CAST( 1 AS BIGINT ) AS factorial
 UNION ALL
 SELECT numero+1, (numero+1)*factorial
 FROM cteFac 
 WHERE numero < 20
) 
SELECT * FROM cteFac 
WHERE numero < 10
OPTION ( MAXRECURSION 0 )

Espero que les sea de ayuda.

SALUDOS!

Continuar...