Eliminar Repetidos con TSQL

10 noviembre, 2017/0 Comentarios/en Blog, T-SQL/por Mauro Bernal

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 repetidos 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

8 noviembre, 2017/1 Comentario/en MSSQL, T-SQL/por Mauro Bernal

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

8 noviembre, 2017/3 Comentarios/en MSSQL, T-SQL/por Mauro Bernal

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/

Cuantos VLF es demasiado?

31 mayo, 2017/0 Comentarios/en MSSQL, T-SQL/por Mauro Bernal

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;

Nivel de Fragmentacion de una base de dato

14 diciembre, 2015/0 Comentarios/en Blog, T-SQL/por Mauro Bernal

Nivel de Fragmentación

Uno de los principales tópicos que se debe verificar es el crecimiento de los archivos que componen una base de datos, como así también el nivel de fragmentación de los índices que la componen.

Asegurar el mejor rendimiento de una base de datos en MSSQL Server puede ser una tarea labiosa.

Verifica que bases de datos crecieron de manera automática

Cuando alguno de los archivos que componen la base (mdf y ldf) se queden sin espacios, y tiene permitido autocrecer (autogrowth) detienen la consulta en curso, y autoincrementa su tamaño, y continua con la operación inicial. Esto si ocurre muy seguido puede provocar que se reduzca el rendimiento de la base.
Una forma de detectarlo es consultando el log del SQL Server:

SELECT
DatabaseName as Base,
[FileName] as Archivo,
CASE EventClass
			WHEN 92 THEN 'Datos'
			WHEN 93 THEN 'Log'
END  as Tipo,
CONVERT(VARCHAR(20), EndTime - StartTime, 114) as TiempoDemora,
StartTime as FechaInicio,
EndTime as FechaFin
 

FROM (
SELECT pt = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1
) p
CROSS APPLY sys.fn_trace_gettable(pt, DEFAULT)
WHERE EventClass IN (92, 93)
ORDER BY StartTime DESC

De acuerdo al resultado será la base en la que deberemos trabajar:

Si aparece la TEMPDB la opción mas recomendable es: Crear un archivos de Datos (.MDF) y un archivo de LOG( ldf) por cada procesador que tiene el servidor. Y el tamaño inicial total de la base debe ser el actual + 10% . Y luego irlo ajustando a medida que se vaya necesitando.

Verifica el nivel de fragmentación de los indices

El segundo punto importante es verificar que nivel de fragmentación tienen cada uno de los indices que componen la base de datos. Este punto puede provocar que nuestra base de datos tenga un tamaño mayor que el real. Y provoque que las consultas se demoren un poco mas de lo que deberian:

Para corroborarlo podemos usar el siguiente script:

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
GO
CREATE TABLE #temp (
db SYSNAME DEFAULT DB_NAME(),
flname SYSNAME,
size_after_growth DECIMAL(18,2),
size DECIMAL(18,2),
space_used DECIMAL(18,2),
growth INT,
is_percent_growth BIT,
PRIMARY KEY CLUSTERED (db, flname)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT '
USE [' + name + ']
INSERT INTO #temp (flname, size_after_growth, space_used, size, growth, is_percent_growth)
SELECT
name
, CASE WHEN is_percent_growth = 1 THEN size * (1 + (growth / 100.)) ELSE size + growth END * 8. / 1024
, space_used = FILEPROPERTY(name, ''SpaceUsed'') * 8. / 1024
, size = size * 8. / 1024
, CASE WHEN is_percent_growth = 1 THEN growth ELSE growth * 8. / 1024 END
, is_percent_growth
FROM sys.database_files'
FROM sys.databases
WHERE [state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
db
, flname
, size_after_growth
, size
, space_used
, CAST(growth AS VARCHAR(10)) + CASE WHEN is_percent_growth = 1 THEN ' %' ELSE ' MB' END
FROM #temp
WHERE (is_percent_growth = 0 AND growth < 50)
OR (is_percent_growth = 1 AND growth < 5)
OR (size - space_used < 20)

Para correguirlo podemos ejecutar el siguiente script por cada una de nuestras bases de datos:

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT '
ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
CASE WHEN s.avg_fragmentation_in_percent > 30
THEN 'REBUILD'
ELSE 'REORGANIZE'
END + ';
'
FROM (
SELECT
s.[object_id]
, s.index_id
, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
WHERE s.page_count > 128 -- > 1 MB
AND s.index_id > 0 -- <> HEAP
AND s.avg_fragmentation_in_percent > 5
GROUP BY s.[object_id], s.index_id
) s
JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
JOIN sys.objects o ON o.[object_id] = s.[object_id]
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.sp_executesql @SQL

Sudo en Windows con PowerShell

28 agosto, 2015/0 Comentarios/en Windows/por Mauro Bernal

Si estás acostumbrado a trabajar con servidores linux, sabrás de lo que te hablo cuando te digo «SUDO»

Sudo for Windows es una aplicación realizada por Luke Sampson que te permite ejecutar lineas de commando con permisos elevados

Para instalar debes de ejecutar los siguientes commandos en orden:

iex (new-object net.webclient).downloadstring('https://get.scoop.sh')

set-executionpolicy unrestricted -s cu -f

scoop install sudo

Así es como se debería ver si salio todo bien:

Contar días hábiles entre una fecha

24 agosto, 2015/4 Comentarios/en Fecha, MSSQL, T-SQL/por Mauro Bernal

Como contar días entre fechas

La siguiente función para SQL Server permite contar la cantidad de días que existen entre un rango de fechas sin contar Sábados y Domingos.

CREATE FUNCTION [dbo].[DiasHabiles](@FechaInicio DATETIME,@FechaFin DATETIME) 
 RETURNS integer 
 AS 
 BEGIN

            DECLARE @DiasNormales INT	--Con esta variable calculamos cuantos dias "normales" hay en el rango de fechas 
            DECLARE @DiasHabiles INT  --Con esta variable acumulamos los dias totales 
            DECLARE @Contador INT		--Esta variable nos sirve de contador para saber cuando lleguemos al ultimo dia del rango 
            DECLARE @Aux_Fecha DATETIME	--Esta variable es la que comparamos para saber si el dia que esta calculando es sábado o domingo*

            /*Esta par de variables sirven para comparar las dos fechas, si son iguales, la funcion nos regresa un 0*/ 
            DECLARE @Aux_FechaInicio VARCHAR(10) 
            DECLARE @Aux_FechaFin VARCHAR(10) 

            --Inicializamos algunas variables 

			SET @DiasNormales = 0 
            SET @DiasHabiles = 0 
            SET @Contador=0 

            --Calculamos cuantos dias normales hay en el rango de fechas  
            SELECT @DiasNormales = DATEDIFF(DAY,@FechaInicio,@FechaFin) + 1 

            /*Ordenamos el formato de las fechas para que no importando como se proporcionen se comparen igual*/ 

            SELECT @Aux_FechaInicio = (SELECT CAST((CAST(datepart(dd,@FechaInicio)AS 
                                   VARCHAR(2))+'/'+ CAST(datepart(mm,@FechaInicio)AS 
                                   VARCHAR(2))+'/'+CAST(datepart(yy,@FechaInicio)AS VARCHAR(4))) as 
                                   varchar(10))) 
            SELECT @Aux_FechaFin = (SELECT CAST((CAST(datepart(dd,@FechaFin)AS 
                                   VARCHAR(2))+'/'+ CAST(datepart(mm,@FechaFin)AS VARCHAR(2))+'/'+ 
                                   CAST(datepart(yy,@FechaFin)AS VARCHAR(4)))as varchar(10))) 


            --Se comparan las dos fechas 

            IF @Aux_FechaInicio <>@Aux_FechaFin 
			BEGIN 
				/*Si la diferencia de fechas es igual a dos, es porque solo ha transcurrido un dia, asi que solo se valida que no vaya a marcar dias de mas*/ 
				IF @DiasNormales = 2 
				BEGIN 
					SELECT @DiasHabiles = 1 
				END 
				ELSE 
				BEGIN 
					WHILE @Contador < @DiasNormales 
					BEGIN 
						/*Se Iguala la fecha a que vamos a calcular para saber si es sabado o domingo en la variable @Aux_Fecha sumandole los dias que marque el contador, el cual no debe ser mayor que el numero total de dias que hay en el rango de fechas*/ 
						SELECT @Aux_Fecha = @FechaInicio + @Contador 
						/*Utilizando la funcion datepart con el parametro dw que calcula que dia de la semana corresponde una fecha determinada, determinados que no sea sabado (7) o domingo (1)*/ 
							IF ((datepart(dw,@Aux_Fecha) <> 1) and (datepart(dw,@Aux_Fecha) <> 7) ) 
							BEGIN
								/*Si no es sabado o domingo, entonces se suma uno al total de dias que queremos desplegar*/ 
								SELECT @DiasHabiles = @DiasHabiles + 1 
							END 
						--Se suma un dia mas al contador 
						SELECT @Contador = @Contador + 1 
					END 
				END 
			END 
            ELSE 
            BEGIN 
            --Si fuese cierto que las fechas eran iguales se despliegue cero 
            SELECT @DiasHabiles = 0 
            END 

            --Al finalizar el ciclo, la funcion regresa el numero total de dias 
            RETURN(@DiasHabiles) 
END

GO

Su uso es muy simple:

Select dbo.DiasHabiles('2015/07/13','2015/07/31') as DiasHabiles,DateDiff(d,'2015/07/13','2015/07/31') as DiasCorridos

Resultado_contar_fechas

En este ejemplo se puede ver la variación que existe entre la función DateDiff y nuestra función.

Averiguar el Puerto del SQL Server

21 julio, 2015/1 Comentario/en Blog, T-SQL/por Mauro Bernal

¿Cómo saber en que puerto está corriendo nuestro motor SQL Server? Primero que nada es conveniente entender porque es que no esta en su puerto default. Si eres novato en el tema, seguramente tienes la instalación estándar del motor de Microsoft, y nunca te has preguntado porque debería cambiarlo de puerto. Si es tu caso te dejo este artículo donde te contamos como es la seguridad del MS SQL Server y que cosas debes tener en cuenta. (https://maurobernal.com.ar/blog/consejos-para-asegurar-tu-ms-sql-server/)

Para aquellos que entendemos el porqué debemos cambiarlo, y no sabemos como hacerlo, te dejo este otro artículo. (https://maurobernal.com.ar/blog/mssql/cambiar-el-puerto-del-ms-sql-server)

Y para aquellos que sabemos lo anterior, pero por diferentes motivos nos toca implementar una aplicación, y no sabemos en que puerto está corriendo, a continuación te damos una idea de como averiguarlo.

Averiguar el Puerto en el que está funcionando el SQL Server:

Por defecto el motor SQL Server trabaja en los siguientes puertos de manera predeterminada:

TCP:1433
UDP:1433

Pero puede ocurrir que esto al tener varias instancias, o por otros motivos que no vienen al caso, no sean los puertos en los que está funcionando nuestro Motor. Para averiguar el puerto podemos utilizar los siguientes 3 métodos:

Registros de errores de lectura de SQL Server
Usando el Administrador de configuración de SQL Server (SQL Server Configuration Manager )
Usando el visor de sucesos (o Eventos) de Windows (Windows Application Event Viewer)

1-Registros de errores de lectura de SQL Server:

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on' 
GO

Los parámetros que puede utilizar con XP_READERRRORLOG se mencionan a continuación para su referencia:

1. Valor de los archivos de registro de errores que quieran leer: 0 = actual, 1 = Archivo # 1, 2 = Archivo # 2, etc …
Tipo de archivo: 1 o NULL = log de errores, 2= registro de Agente SQL
Cadena de búsqueda 1: Primer cadena de texto que se quiere buscar
Cadena de búsqueda 2: Segunda cadena de texto a buscar
Fecha de Inicio para la búsqueda
Fecha Fin para la búsqueda
Orden de los Resultados : N’asc’ = Ascendente, N’desc’ = Descendiente

2-Usando el Administrador de Configuración del SQL Server:

Se debe ir a INICIO \ Todos los Programas \ Microsoft SQL Server 200X \ Configuration Tools \ SQL Server Configuration Manager

3-Usando el Visor de Eventos de Windows:

Se deben filtrar los eventos cuyo ID es 26022

Video Explicando todo este proceso

Contar días sin Sábado ni Domingo

3 junio, 2015/0 Comentarios/en MSSQL, T-SQL/por Mauro Bernal

Como contar cuántos días hay en un período sin contar sábados y domingo

La siguiente es una función en TSQL de MS SQL Server, la cúal permite saber la cantidad de días que existe en un período determinado sin contar los fines de semana (sábado y domingo).

--Primeramente declaramos que vamos a crear una funcion, en este caso se llama Dif Dias y recibe dos parámetros, la fecha inicial del período y la final*/ 
CREATE FUNCTION [dbo].[DifDias](@StartDate DATETIME,@EndDate DATETIME) 
 RETURNS integer 
 AS 
 Begin 

		DECLARE @DaysBetween INT  --Con esta variable calculamos cuantos dias "normales" hay en el rango de fechas 
		DECLARE @BusinessDays INT  --Con esta variable acumulamos los dias totales 
		DECLARE @Cnt INT --esta variable nos sirve de contador para saber cuando lleguemos al ultimo dia del rango 
		DECLARE @EvalDate DATETIME   --esta variable es la que comparamos para saber si el dia que esta calculando es sábado o domingo*

		/*Esta par de variables sirven para comparar las dos fechas, si son iguales, la funcion nos regresa un 0*/ 
		DECLARE @ini VARCHAR(10) 
		DECLARE @fin VARCHAR(10) 

		--Inicializamos algunas variables 

		 SET @DaysBetween = 0 
		 SET @BusinessDays = 0 
		 SET @Cnt=0 

		--Calculamos cuantos dias normales hay en el rango de fechas  
		SELECT @DaysBetween = DATEDIFF(DAY,@StartDate,@EndDate) + 1 

		/*Ordenamos el formato de las fechas para que no importando como se proporcionen se comparen igual*/ 

		SELECT @ini = (SELECT CAST((CAST(datepart(dd,@StartDate)AS 
						VARCHAR(2))+'/'+ CAST(datepart(mm,@StartDate)AS 
						VARCHAR(2))+'/'+CAST(datepart(yy,@StartDate)AS VARCHAR(4))) as 
						varchar(10))) 
		SELECT @fin = (SELECT CAST((CAST(datepart(dd,@EndDate)AS 
						VARCHAR(2))+'/'+ CAST(datepart(mm,@EndDate)AS VARCHAR(2))+'/'+ 
						CAST(datepart(yy,@EndDate)AS VARCHAR(4)))as varchar(10))) 


		--Se comparan las dos fechas 

		IF @ini <>@fin 
		 BEGIN 

		/*Si la diferencia de fechas es igual a dos, es porque solo ha transcurrido un dia, asi que solo se valida que no vaya a marcar dias de mas*/ 

		IF @DaysBetween = 2 
		 BEGIN 
		 SELECT @BusinessDays = 1 
		 END 
		 ELSE 
		 BEGIN 
		 WHILE @Cnt < @DaysBetween 
		 BEGIN 

		/*Se Iguala la fecha a que vamos a calcular para saber si es sabado o domingo en la variable @EvalDate sumandole los dias que marque el contador, el cual no debe ser mayor que el numero total de dias que hay en el rango de fechas*/ 

		SELECT @EvalDate = @StartDate + @Cnt 

		/*Utilizando la funcion datepart con el parametro dw que calcula que dia de la semana corresponde una fecha determinada, determinados que no sea sabado (7) o domingo (1)*/ 

		IF ((datepart(dw,@EvalDate) <> 1) and 
		 (datepart(dw,@EvalDate) <> 7) ) 
		 BEGIN 

		/*Si no es sabado o domingo, entonces se suma uno al total de dias que queremos desplegar*/ 

		SELECT @BusinessDays = @BusinessDays + 1 
		 END 

		--Se suma un dia mas al contador 

		SELECT @Cnt = @Cnt + 1 
		 END 
		 END 
		 END 
		 ELSE 
		 BEGIN 

		--Si fuese cierto que las fechas eran iguales se despliegue cero 

		SELECT @BusinessDays = 0 
		 END 
		 
		 
		 
		--Al finalizar el ciclo, la funcion regresa el numero total de dias 

		return (@BusinessDays) 
		 END

GO

Ejemplo de Uso:

SELECT  dbo.DifDias('06/01/2015', '06/30/2015')  as DiasAbril

Truncar Fecha en SQL Server

25 marzo, 2015/0 Comentarios/en MSSQL, T-SQL/por Mauro Bernal

Porque trabajas todo el día con un motor MS SQL Server, y te toca cambiar los formatos de visualización de las fechas, aquí te dejo una ayuda memoria:

De la siguiente manera es posible quitar las horas y los segundos a una fecha.

select getdate()

-- La manera correcta (desde SQL Server 2008)
select cast(getdate() As Date)

-- La manera correcta(anterior a SQL Server 2008)
select dateadd(dd, datediff(dd,0, getDate()), 0)