Últimos artículos

Estas son las últimas entradas en el blog.
De todo un poco para solucionar los problemas diarios de un administrador de IT y DBA

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.