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:
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_BackupResultado 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 GOResultado 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/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ásA 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 especificosEliminado 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_LuhnExisten 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 ENDPara 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 descEn 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;