T-SQL expande el estándar de SQL para incluir programación procedural, variables locales, varias funciones de soporte para procesamiento de strings, procesamiento de fechas, matemáticas, etc, y cambios a los estatutos DELETE y UPDATE

Funciones Analíticas con SQL Server

En los siguientes artículos que iré creando resumiremos el uso de las siguientes funciones analíticas incorporadas a partir de SQL Server 2012

 

Estas son:

CUME_DIST (Transact-SQL) LEAD (Transact-SQL)
FIRST_VALUE (Transact-SQL) PERCENTILE_CONT (Transact-SQL)
LAG (Transact-SQL) PERCENTILE_DISC (Transact-SQL)
LAST_VALUE (Transact-SQL) PERCENT_RANK (Transact-SQL)

Uso de LAG en SQL Server

Dos nuevas funciones analíticas: LAG y LEAD

A partir de la versión 2012 de SQL Server se incorporaron entre otras cosas dos funciones nuevas, que permiten acceder a la fila anterior, o posterior de una consulta.

Algo que anteriormente era imposible, y tocaba solucionar con Tablas Pivot, o usando Self Join

LAG y LEAD

Primero entendamos que hace cada una:

LAG: devuelve el valor (de una columna especificada) que estuvo en la fila anterior.

LEAD: devuelve el valor (de una columna especificada) que estuvo en la fila siguiente.

Mas info:

https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql

 

 

 

 

Realizar backup con TSQL

Realizar backup de tus bases con TSQL en MS SQL Server

Hoy veremos una manera simple de realizar un respaldo de todas nuestras bases de datos mediante un pequeño script.

Script para Backup

--------------------------------------------------
--Variables
DECLARE @Base_Datos VARCHAR(50) -- Nombre de la Base de Datos  
DECLARE @Ruta_Archivos VARCHAR(256) -- Ruta para los archivos 
DECLARE @Nombre_A VARCHAR(256) -- Nombre del Backup  (1era Parte)
DECLARE @Nombre_B VARCHAR(20) --  Nombre del Backup (2da Parte)
 
-- 1-Ubicación de los backups
SET @Ruta_Archivos = 'C:\Temp\'  
 
-- Nombre del Archivo (2da Parte) _YYYYMMDD
SELECT @Nombre_B = (SELECT '_'+CONVERT(VARCHAR(20),GETDATE(),112) +'.BAK') 
 
DECLARE Cursor_Backup CURSOR READ_ONLY FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
 
OPEN Cursor_Backup   
FETCH NEXT FROM Cursor_Backup INTO @Base_Datos   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
   SET @Nombre_A = @Ruta_Archivos + @Base_Datos +  @Nombre_B   
   BACKUP DATABASE @Base_Datos TO DISK = @Nombre_A  
 
   FETCH NEXT FROM Cursor_Backup INTO @Base_Datos   
END   

 
CLOSE Cursor_Backup   
DEALLOCATE Cursor_Backup

Resultado de correr el script

realizar backup con tsqlQue sigue…

Se puede optimizar el script para agregar nuevas opciones de backups, como así también programar el mismo.

Crear indices faltantes

Como buscar y crear índices faltantes en nuestra base de datos

Navegando por la web me tope con esta conjunto de scripts recomendado por Microsoft que aseguran ser útil para cualquier DBA

https://gallery.technet.microsoft.com/Some-random-collection-of-b607bf1b

Como siempre son para MS SQL Server.

Entre ellos encontré uno muy útil que analiza las estadísticas de una base en producción y trate de generarte el TSQL para crear los índices recomendados faltantes

Veamos el código

/**********************************************
--Missing Index Script
**********************************************/

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Resultado de correr el script

Luego de unos segundos veremos algo de este estilo:

buscando indices faltantes con tsqlPodremos ver el tiempo promedio estimado de mejora al realizar el índice, como el TSQL para crearlo.

(*) Un error que tiene el script es el nombre del índice que genera, que no debe superar los 128 caracteres, y en el mismo no lo contempla.

Por lo demás promete cumplir lo que ofrece….

Actualmente lo estoy probando en mis bases de producción como alternativa a la herramienta de análisis de rendimiento de MS SQL Server

Obtener el ultimo día del mes con SQL Server

Como obtener el último día del mes con TSQL en SQL Server

Anteriormente habíamos visto como podíamos obtener:

Primer y último día de Mes Anterior, Actual y Siguiente.

Si te interesa ( y trabajas con una versión anterior a MS SQL Server 2012), les dejo el tutorial anterior:

https://maurobernal.com.ar/blog/t-sql/primer-y-ultimo-dia-del-mes-con-sql-server-y-otros/

Pero en esta oportunidad veremos otra variante para obtener el último día del mes: actual, mes anterior y mes siguiente.

Usando la funtión EOMONTH()

A partir de Microsoft SQL Server 2012 se agregó la función EOMONTH()

https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql

La misma permite obtener el último día del mes

Veamos un ejemplo:

--GetDate() -> 14 Nov 2017

--Último día del mes Actual
select EOMONTH(getdate()) as UltDiaMesActual

--Último día del mes Anterior
select EOMONTH(getdate(),-1) as UltDiaMesAnterior

--Último día del mes Próximo
select EOMONTH(getdate(),1) as UltDiaMesProximo

Esto nos dará como resultado:

usando_eomonth_ejemploCon un poco de ingenio podemos obtener un poco más

A partir del código anterior, con un poco de ingenio, y con ayuda de la función DateAdd()

https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql

Si le agregamos un día podemos obtener el primer día del mes anterior, mes actual y mes próximo

--GetDate() -> 14 Nov 2017

--Primer día del Mes Anterior
select dateadd(d,1,EOMONTH(getdate(),-2)) as PrimerDiaMesAnterior

--Primer día del Mes Actual
select dateadd(d,1,EOMONTH(getdate(),-1)) as PrimerDiaMesActual

--Primer Dia del Mes Siguiente
select dateadd(d,1,EOMONTH(getdate())) as PrimerDiaMesSiguiente

El resultado sería el siguiente:

eomoth para primer dia del mes

 

No se olviden de comentar si les sirvió, !!! Y cualquier cosa que necesiten hacer es posible con un poco de paciencia…

Tutorial Rank y Dense_Rank

Aprende a usar Rank() y Dense_Rank()

Vamos primero con la documentación de estas dos funciones:

Rank:

https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql

Dense_Rank

https://docs.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql

 

Entendiendo con un ejemplo

Primero crearemos un pequeña base de datos, que incluye un listado de vendedores, con sus ventas

IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('myTable'))
BEGIN;
    DROP TABLE [myTable];
END;
GO

CREATE TABLE [myTable] (
    [myTableID] INTEGER NOT NULL IDENTITY(1, 1),
    [Vendedor] VARCHAR(255) NULL,
    [Ventas] VARCHAR(100) NULL,
    PRIMARY KEY ([myTableID])
);
GO

INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Bowen','$1.60'),('Becker','$31.71'),('Stanton','$95.81'),('Weaver','$60.26'),('Park','$65.37'),('Cook','$80.55'),('Pruitt','$60.42'),('Roberson','$20.23'),('Gilbert','$81.98'),('Emerson','$44.32');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Harper','$1.60'),('Patrick','$98.08'),('Andrews','$35.71'),('Steele','$92.19'),('Moody','$1.90'),('Strong','$68.97'),('Rowland','$23.67'),('Barron','$79.18'),('Schultz','$80.94'),('Andrews','$1.92');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Cleveland','$58.03'),('Head','$46.60'),('Hickman','$25.67'),('Cooke','$7.19'),('Farrell','$25.46'),('Sutton','$29.80'),('Dominguez','$85.95'),('Meadows','$70.26'),('Horne','$32.67'),('Cantu','$49.44');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Mosley','$95.29'),('Gomez','$8.53'),('Noble','$81.25'),('Shaw','$47.67'),('Harper','$23.01'),('Reese','$2.46'),('Brown','$36.73'),('Hardin','$0.65'),('Marshall','$7.98'),('Combs','$60.59');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Hicks','$2.78'),('Cote','$15.58'),('Knight','$91.91'),('Gilliam','$75.10'),('Leblanc','$11.16'),('Franklin','$13.92'),('Powers','$1.45'),('Clark','$36.26'),('Rose','$22.70'),('Hoover','$78.61');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Hines','$45.97'),('Lowery','$29.76'),('Le','$63.35'),('Allison','$96.55'),('Fulton','$34.28'),('Cleveland','$46.02'),('Brewer','$1.60'),('Howe','$1.60'),('Sims','$26.47'),('Fox','$55.71');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Bentley','$28.30'),('Hahn','$81.10'),('Colon','$62.66'),('Le','$3.42'),('House','$90.24'),('Kemp','$93.13'),('Cooper','$85.59'),('Mosley','$46.40'),('Jacobs','$1.58'),('Dickson','$76.56');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Jacobs','$46.47'),('Moon','$77.90'),('Palmer','$52.45'),('Bray','$99.29'),('Franco','$50.64'),('Erickson','$21.77'),('Joyce','$32.19'),('Kline','$4.15'),('Barrera','$71.70'),('Jacobs','$58.56');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Mercer','$95.18'),('Horn','$43.12'),('Mendoza','$42.59'),('Mays','$59.19'),('Shaw','$33.63'),('Sawyer','$58.80'),('Chen','$43.88'),('Dorsey','$32.79'),('Howard','$50.02'),('Marshall','$81.84');
INSERT INTO myTable([Vendedor],[Ventas]) VALUES('Casey','$2.67'),('Boyer','$39.07'),('Richards','$31.92'),('Larson','$64.99'),('Drake','$42.46'),('Holmes','$28.37'),('Hicks','$21.46'),('Richardson','$89.33'),('Evans','$8.90'),('Crawford','$11.28');

 

Contenido de esta tabla

Esta tabla desordenada contiene el listado de vendedores, con sus respectivas Ventas

select * from myTable

Base para ejemplo de Dense Rank

Uso de Rank y Dense_Rank para armar ranking

La idea de estas dos funciones es armar un ranking.

Semejanza: Ambas, en el caso de que empaten le ponen el mismo número de ranking

Diferencia: Al caso siguiente luego del empate Rank no le pone el inmediato superior, si no que mantiene la cuenta de la cantidad de elementos que lleva hasta el momento y le pone el siguiente. A diferencia de Dense_Rank que continúa con el siguiente correlativo al del empate

 

Con Rank

select *, RANK() over (Order by Ventas) as Ranking from myTable

Uso de Rank

Con Dense_Rank

select *, DENSE_RANK() over ( Order by Ventas) as Ranking from myTable

Uso de dense_rankts

Eliminar Repetidos con TSQL

Eliminar Repetidos con TSQL y la función Row_Number()

Hoy vamos usar row_number(), una subconsulta, comando IN para buscar y eliminar registros repetidos.

En esta nueva etapa me he propuesto compartir cada uno de los scripts que siempre son de utilidad cuando se trabaja con bases de datos.

Como siempre son con TSQL para MS SQL Server.

Esta vez veremos como eliminar registros repetidos de una tabla, solo dejando 1 registro.

El secreto está en el uso del comando ROW_NUMBER

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

 

Cargaremos una tabla de ejemplo MYTABLA

IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('myTable'))
BEGIN;
    DROP TABLE [myTable];
END;
GO

CREATE TABLE [myTable] (
    [myTableID] INTEGER NOT NULL IDENTITY(1, 1),
    [Documento] VARCHAR(13) NULL,
    [Nombre] VARCHAR(255) NULL,
    PRIMARY KEY ([myTableID])
);
GO


INSERT INTO myTable([Documento],[Nombre]) VALUES('1695071197799','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630121141299','Harlan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1637061077799','Armand');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1653081722499','Chadwick');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695020909099','Fletcher');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614102485499','Garth');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1624071487999','Cedric');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1678010341799','Prescott');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695071197799','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630121141299','Harlan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1637061077799','Armand');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1653081722499','Chadwick');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691081716399','Harding');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1641101376799','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630020717199','Carlos');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627101503999','Edan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636051993199','Samson');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627041338699','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661032884599','Brennan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625120186899','Brett');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642100764499','Ira');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1624052909599','Kyle');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656062403199','Jordan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656020101399','Alden');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1657080142799','Tanek');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1678122145299','Fulton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1684010635799','Reese');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1613011065899','Grant');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630012756499','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1618071462499','Flynn');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615052518299','Benjamin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691052832399','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1669013054199','Tucker');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1612110409199','Laith');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614110752199','Tad');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1699041337299','Chaney');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1650112954899','Jeremy');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1647052552299','Adam');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643041374799','Denton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642081947099','Knox');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643020793199','Vance');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1632020546699','Thaddeus');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670032188999','Keegan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615102660699','Clark');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1693053059399','Hedley');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661082842999','Beau');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636081862699','August');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1608070833799','Oscar');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1687121074299','Leo');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1655122056599','Griffith');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614010191199','Andrew');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668121357799','Abdul');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1691081716399','Harding');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1641101376799','Jakeem');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630020717199','Carlos');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627101503999','Edan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636051993199','Samson');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1627041338699','Wylie');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1661032884599','Brennan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625120186899','Brett');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1642100764499','Ira');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1602091000299','Jordan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1602060613699','Guy');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1656041722899','Keane');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1630122181399','Cain');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1634062813199','Stone');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1615111655399','Elton');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609092165099','Theodore');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682111461299','Noble');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649101947099','Bradley');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692080607499','Basil');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1645013071199','Bruce');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1677121204299','Chancellor');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668110667099','Price');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1644072576599','Driscoll');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1616101911399','August');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1680021349199','Wallace');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1652052495999','Emery');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692050724999','Talon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1614111114799','Avram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670010342799','Hiram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1636022785999','Brody');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609033047099','Stuart');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1669122235799','Moses');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1626080826599','Leo');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1670010510999','Micah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676043021599','Jonah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1621032365499','Len');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1658020481699','Amir');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1611032912899','Len');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1638071718599','Ray');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676120168199','Kenyon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1688050847099','Sawyer');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1692051561099','Malcolm');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1664101012499','Nolan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1682121754399','Lucius');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1633112425699','Alvin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1606040846399','Ralph');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1610061348699','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649120964999','Malachi');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1609121778799','Kermit');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1649111157999','Wade');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1625051934199','Simon');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1698060420799','Donovan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1607101359699','Isaiah');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1648100677099','Avram');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1668081567999','Grant');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1613042285999','Keefe');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1626042455399','Joshua');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1676081014999','Amir');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1643042339599','Conan');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1695051340499','Jin');
INSERT INTO myTable([Documento],[Nombre]) VALUES('1610012015699','Akeem');

 

Esta pequeña tabla de ejemplo tiene la siguiente estructura:

eliminar repetidos con tsql

Buscando repetidos

Lo primero que haremos es buscar los que tienen el campo DOCUMENTO repetido usando la función Row_Number(). Esta nos devolverá un nuevo campo que llamaremos ORDEN, e indicara las veces que se repite un campo

select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden 
from mytable

eliminar repetidos con tsql y row_number()

PARTITION BY Documento le indicamos que la partición sea el campo documento

ORDER BY myTableID le indicamos el criterio para enumerar. Es decir en orden ascendente por ID del campo. Si tuviésemos un campo de tipo fecha (Datetime) podríamos ordenar por mas nuevo, o viceversa

 

Buscando los repetidos

Lo primero que haremos es buscar los repetidos usando una subconsulta

Select myTableID from
(select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden 
from mytable) SubConsulta1
where Orden>1

Eliminiar repetidos TSQL 31 repetidosEsta consulta devuelve los ID de los repetidos: 31 registros para ser mas especificos

Eliminado los repetidos

Ahora los eliminamos usando el comando IN

begin transaction eliminacion1
delete from mytable where 
mytableID in 
(

Select myTableID from
(select *,ROW_NUMBER() over ( PARTITION BY Documento ORDER BY myTableID) as Orden 
from mytable) SubConsulta1
where Orden>1
)

commit transaction eliminacion1

 

 

 

Espero que les haya sido útil.

 

El comando Row_Number() permite trabajar con los registros de forma masiva pudiendo identificar la N aparición.

Mas adelante veremos como eliminar / actualizar:

-El último registro de una serie de registros

-El primero, etc…

 

 

 

 

Validar CBU Argentina

Validar – CBU Número de Cuenta – Argentina

Siguiendo con estas funciones de TSQL para MS SQL Server, en esta ocasión es para validar los 22 dígitos del CBU.

La Clave Bancaria Uniforme (CBU) se compone de 22 dígitos

https://es.wikipedia.org/wiki/Clave_Bancaria_Uniforme

CBU Clave Bancaria Uniforme

 

CREATE FUNCTION [dbo].[ValidarCBUdigito] 
( 
-- Add the parameters for the function here 
@block VARCHAR(100) 
) 
RETURNS TINYINT 
AS 
BEGIN 
DECLARE @subblock VARCHAR(100) 
DECLARE @posicion INTEGER 
DECLARE @digito CHAR(1) 
DECLARE @Pond CHAR(4) 
DECLARE @largo INTEGER 
DECLARE @suma INTEGER 

SELECT @suma = 0 
SELECT @largo = LEN ( @block ) 
SELECT @digito = SUBSTRING ( @block , @largo , 1 ) 
SELECT @subblock = SUBSTRING ( @block , 1 , @largo - 1 ) 
SELECT @Pond = '9713' 
SELECT @posicion = 1 

WHILE @posicion <= @largo - 1 
BEGIN 
SELECT @suma = @suma + CONVERT( INTEGER , SUBSTRING ( @subblock , @largo - @posicion , 1 )) * CONVERT( INTEGER , SUBSTRING ( @pond ,CASE (@posicion % 4) WHEN 1 THEN 4 WHEN 2 THEN 3 WHEN 3 THEN 2 WHEN 0 THEN 1 END , 1 ) ) 
SELECT @posicion = @posicion + 1 
END 


if @digito = RIGHT ( convert( varchar, 10 - ( @suma % 10 )) , 1 ) 
RETURN 1 

RETURN 0 
END

 

Validar Tarjeta de Crédito con TSQL

Validar Tarjeta de crédito

Verificar y validar el número de una Tarjeta de Crédito o Débito
La mayoría de los sellos de tarjetas de crédito (Visa, Master, Dinners, etc) usan el algoritmo de Luhn, el cual mediante un digito verificador corrobora si el resto de los números son correctos.
https://es.wikipedia.org/wiki/Algoritmo_de_Luhn

Existen diferentes formas de implementar el algoritmo. En esta ocasión les comparto el script de Derek Colley

Veamos la función para validar con TSQL

CREATE FUNCTION dbo.usp_LuhnsAlgorithm_New ( @inputString VARCHAR(20) )
RETURNS TINYINT 
AS BEGIN 
-------------------------------------------------------------------------------
-- Function to calculate whether a number is valid according to the 'MOD 10'
-- check, a.k.a. Luhn's Algorithm.
-- Author:  Derek Colley, August 2014
-- Parameters:  @inputString VARCHAR(20) 
-- Outputs:  TINYINT:    2 = an error occurred, validity undetermined
--       0 = number is not valid 
--       1 = number is valid
-------------------------------------------------------------------------------

-- first a quick check to ensure we have at least 3 numbers in the string - 
-- you can change this to any arbitrary amount, i.e. if you are just 
-- checking credit card numbers, make it 13 digits

DECLARE @result TINYINT

IF @inputString NOT LIKE ('%[0-9]%[0-9]%[0-9]%') 
 RETURN 2

-- set up our table for algorithm calculation

DECLARE @charTable TABLE ( 
 Position INT NOT NULL, 
 ThisChar CHAR(1) NOT NULL, 
 Doubled TINYINT, 
 Summed TINYINT ) 

-- convert the @inputString to a fixed width char datatype 
-- we can then process the string as a set with a known number of elements
-- this avoids RBAR substringing each char to a table in a cursor

SET @inputString = CAST(@inputString AS CHAR(20))
INSERT INTO @charTable(Position, ThisChar) 
 SELECT 1, SUBSTRING(@inputString, 1, 1) UNION ALL 
 SELECT 2, SUBSTRING(@inputString, 2, 1) UNION ALL 
 SELECT 3, SUBSTRING(@inputString, 3, 1) UNION ALL 
 SELECT 4, SUBSTRING(@inputString, 4, 1) UNION ALL 
 SELECT 5, SUBSTRING(@inputString, 5, 1) UNION ALL 
 SELECT 6, SUBSTRING(@inputString, 6, 1) UNION ALL 
 SELECT 7, SUBSTRING(@inputString, 7, 1) UNION ALL 
 SELECT 8, SUBSTRING(@inputString, 8, 1) UNION ALL 
 SELECT 9, SUBSTRING(@inputString, 9, 1) UNION ALL 
 SELECT 10, SUBSTRING(@inputString, 10, 1) UNION ALL 
 SELECT 11, SUBSTRING(@inputString, 11, 1) UNION ALL 
 SELECT 12, SUBSTRING(@inputString, 12, 1) UNION ALL 
 SELECT 13, SUBSTRING(@inputString, 13, 1) UNION ALL 
 SELECT 14, SUBSTRING(@inputString, 14, 1) UNION ALL 
 SELECT 15, SUBSTRING(@inputString, 15, 1) UNION ALL 
 SELECT 16, SUBSTRING(@inputString, 16, 1) UNION ALL 
 SELECT 17, SUBSTRING(@inputString, 17, 1) UNION ALL 
 SELECT 18, SUBSTRING(@inputString, 18, 1) UNION ALL 
 SELECT 19, SUBSTRING(@inputString, 19, 1) UNION ALL 
 SELECT 20, SUBSTRING(@inputString, 20, 1)


-- remove non-numerics inc. whitespace from the string 
DELETE FROM @charTable
WHERE  ThisChar NOT LIKE('[0-9]') 


-- unfortunately this messes up the Position indicator, 
-- so let's 'reset' this like so... 
DECLARE @tempTable TABLE ( 
 NewPosition INT IDENTITY(1,1), 
 OldPosition INT ) 
INSERT INTO @tempTable (OldPosition)
 SELECT Position 
 FROM @charTable 
 ORDER BY Position ASC 

UPDATE  @charTable
SET   Position = t2.NewPosition 
FROM  @charTable t1 
INNER JOIN  @tempTable t2 ON t1.Position = t2.OldPosition 

-- now for every 2nd digit from the right of the numeric, 
-- double it and store the result in the Doubled column 

IF ( SELECT MAX(Position) % 2 FROM @charTable ) = 0 -- evens 
BEGIN 
 UPDATE @charTable
 SET  Doubled = CAST(ThisChar AS TINYINT) * 2 
 WHERE Position % 2 <> 0 
END
ELSE BEGIN -- odds
 UPDATE @charTable 
 SET  Doubled = CAST(ThisChar AS TINYINT) * 2 
 WHERE Position % 2 = 0 
END 


-- now if the doubled digit is > 9, sum the digits, else carry forward
-- to the Summed column.  This goes for non-doubled digits too.
UPDATE @charTable
SET  Summed = 
   CASE WHEN Doubled IS NULL 
     THEN CAST(ThisChar AS TINYINT) 
     WHEN Doubled IS NOT NULL AND Doubled <= 9 
     THEN Doubled 
     WHEN Doubled IS NOT NULL AND Doubled >= 10 
     -- sum the digits.  Luckily SQL Server butchers int division...
     THEN (Doubled / 10) + (Doubled - 10) 
   END      


-- finally, sum the Summed column and if the result % 10 = 0, it's valid 
IF ( SELECT SUM(Summed) % 10 FROM @charTable ) = 0
 SET @result = 1
ELSE 
 SET @result = 0

RETURN @result 
END

Para probar:

SELECT '371449635398431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('371449635398431') [Valid Card?] UNION ALL
SELECT '3714 4963 5398 431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('3714 4963 5398 431') [Valid Card?] UNION ALL
SELECT '37XX XXXX 5398431' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('37XX XXXX 5398431') [Valid Card?] UNION ALL
SELECT 'This is not a valid string' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('This is not a valid string' ) [Valid Card?] UNION ALL
SELECT '1234123412341234' [Test String], [dbo].[usp_LuhnsAlgorithm_New]('1234123412341234') [Valid Card?]

Fuente:

https://www.mssqltips.com/sqlservertip/3319/implementing-luhns-algorithm-in-tsql-to-validate-credit-card-numbers/

Validar tarjeta de credito con tsql

Cuantos VLF es demasiado?

Entendiendo que es VLF

De acuerdo al tamaño que tenga nuestro archivo de LOG (.ldf) es importante controlar los VLF (Virtual Log File).

Los VLF, son los archivos virtuales en que se divide nuestro log, y un exceso de los mismos puede provocar una reducción del rendimiento.

Verificando los VLF

  • Verificar cuantos VLF tenemos en nuestra base
  • De manera invidual

 

DBCC LOGINFO
  • De forma masiva
--variables to hold each 'iteration'  
declare @query varchar(100)  
declare @dbname sysname  
declare @vlfs int  
  
--table variable used to 'loop' over databases  
declare @databases table (dbname sysname)  
insert into @databases  
--only choose online databases  
select name from sys.databases where state = 0  
  
--table variable to hold results  
declare @vlfcounts table  
    (dbname sysname,  
    vlfcount int)  
  
 
 
--table variable to capture DBCC loginfo output  
--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version 
 
declare @MajorVersion tinyint  
set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1) 

if @MajorVersion < 11 -- pre-SQL2012 
begin 
    declare @dbccloginfo table  
    (  
        fileid smallint,  
        file_size bigint,  
        start_offset bigint,  
        fseqno int,  
        [status] tinyint,  
        parity tinyint,  
        create_lsn numeric(25,0)  
    )  
  
    while exists(select top 1 dbname from @databases)  
    begin  
  
        set @dbname = (select top 1 dbname from @databases)  
        set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '  
  
        insert into @dbccloginfo  
        exec (@query)  
  
        set @vlfs = @@rowcount  
  
        insert @vlfcounts  
        values(@dbname, @vlfs)  
  
        delete from @databases where dbname = @dbname  
  
    end --while 
end 
else 
begin 
    declare @dbccloginfo2012 table  
    (  
        RecoveryUnitId int, 
        fileid smallint,  
        file_size bigint,  
        start_offset bigint,  
        fseqno int,  
        [status] tinyint,  
        parity tinyint,  
        create_lsn numeric(25,0)  
    )  
  
    while exists(select top 1 dbname from @databases)  
    begin  
  
        set @dbname = (select top 1 dbname from @databases)  
        set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '  
  
        insert into @dbccloginfo2012  
        exec (@query)  
  
        set @vlfs = @@rowcount  
  
        insert @vlfcounts  
        values(@dbname, @vlfs)  
  
        delete from @databases where dbname = @dbname  
  
    end --while 
end 
  
--output the full list  
select dbname as 'Base', vlfcount  'Cant'
from @vlfcounts  
order by vlfcount desc

En base a la cantidad deberemos seguir el siguiente criterio

  • Menos de 1 MB, ignorar este caso.
  • Hasta  64 MB: 4 VLFs, cada uno de 1/4 del tamaño
  • De 64 MB a 1 GB: 8 VLFs, cada uno de 1/8 del tamaño
  • Mas de 1 GB: 16 VLFs, cada uno  de 1/16 del tamaño

 

Si obtenemos una cantidad mayor tenemos que:

  • Verificar el tamaño del LOG.
  • Reducir el LOG(*).
  • Crear de nuevo el LOG con un tamaño inicial igual al existente antes de reiniciarlo.

(*) será conveniente hacer un backup del mismo

 

-- Detecting and reducing VLFs in SQL Server 2008
-- Glenn Berry 
-- June 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Switch to your database
USE ngmetadata;
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Check individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;

-- Step 1: Compressed backup of the transaction log (backup compression requires Enterprise Edition in SQL Server 2008)
BACKUP LOG [ngmetadata] TO  DISK = N'N:\SQLBackups\ngmetadataLogBackup.bak' WITH NOFORMAT, INIT,  
NAME = N'ngmetadata- Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;
GO

-- Step 2: Shrink the log file
DBCC SHRINKFILE (N'ngmetadata_log' , 0, TRUNCATEONLY);
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Step 3: Grow the log file back to the desired size, 
-- which depends on the amount of write activity 
-- and how often you do log backups
USE [master];
GO
ALTER DATABASE ngmetadata MODIFY FILE (NAME = N'ngmetadata_log', SIZE = 8GB);
GO

-- Switch back to your database
USE ngmetadata;
GO

-- Check VLF Count for current database after growing log file
DBCC LogInfo;