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:
Listado de la etiqueta: tsql
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
Que 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:
Podremos 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:
Con 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:
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
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
Con Dense_Rank
select *, DENSE_RANK() over ( Order by Ventas) as Ranking from myTable
ts
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:
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
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
Esta 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
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:
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;