ms sql – devops Mauro Bernal

Listado de la etiqueta: ms sql

Crear indices faltantes

15 noviembre, 2017/1 Comentario/en Blog, Fecha, MSSQL, T-SQL/por Mauro Bernal

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

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

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

Como siempre son para MS SQL Server.

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

Veamos el código

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

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

Resultado de correr el script

Luego de unos segundos veremos algo de este estilo:

buscando indices faltantes con tsql 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

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

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:

eomoth para primer dia del mes

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

Tutorial Rank y Dense_Rank

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

Aprende a usar Rank() y Dense_Rank()

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

Rank:

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

Dense_Rank

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

Entendiendo con un ejemplo

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

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

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

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

Contenido de esta tabla

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

select * from myTable

Base para ejemplo de Dense Rank

Uso de Rank y Dense_Rank para armar ranking

La idea de estas dos funciones es armar un ranking.

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

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

Con Rank

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

Uso de Rank

Con Dense_Rank

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

Uso de dense_rank ts