La gente de Datavisión (partner de Microsoft con experticia en SQL) tuvo la gentileza de llevarme a Teotihuacán el pasado 10 de enero. Teotihuacán es uno de los lugares arqueológicos más importantes de América y lugar de visita obligatoria cuando se está en México. Durante esta visita finalmente pude cumplir mi sueño de subir tanto la pirámide del sol como la de la luna. Antes había estado en Teotihuacán pero siempre subía primero la pirámide del sol, y me quedaba sin fuerzas para subir la pirámide de la luna. Esta vez la hice a la inversa, y con duras penas, pero llegue.
En orden acostumbrado: Héctor Eugenio Jiménez, Javier Loria, Miguel Angel Granados y Jesús Gil Velasco.
Subiendo la pirámide de la Luna
Pirámide del Sol, vista desde la pirámide de la Luna
Arriba, en la pirámide del Sol.
Pirámide de la Luna vista desde la pirámide del Sol
En la primera parte expuse (http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=25), que con frecuencia las organizaciones no tienen el nivel de madurez de inteligencia de negocios y de planeamiento estratégico necesario para construir paneles de mando o tableros control (Scorecards y/o Dashboards); y estas herramientas deben ser el fruto del planeamiento estratégico y fluyen de arriba (dirección/gerencia) hacia abajo (mandos medios y empleados) en la organización. Quiero resaltar, que no es que no crea en estos componentes, ni mucho menos; creo que son una herramienta invaluable, pero con mayor frecuencia de lo me gustaría reconocer, estos paneles y tableros de control no son herramientas efectivas de administración y no son más que piezas decorativas de software que sirven para satisfacer el ego corporativo y justificar excesos de gasto en infraestructura de BI.
A pesar de esto, es mi opinión los departamentos de tecnología de empresas que no están maduras para implementar una infraestructura de monitoreo (scorecards/dashboards) pueden beneficiarse de las tecnologías de indicadores inteligencia de negocios, si cambian la perspectiva de KPIs por OPIs.
Empecemos entonces por definir y separar los términos:
KPI: por sus siglas en ingles (Key Performance Indicator): Medida que ayuda a una organización a definir y medir el progreso hacia objetivos estratégicos corporativos.
OPI: por sus siglas en ingles (Operational Performance Indicator): Medida que ayuda a una organización a examinar y monitorear sus resultados operativos.
Los KPIs son fruto del planeamiento estratégico. La dirección o gerencia, evalúa estrategias y desarrolla un plan para garantizar el éxito de dicha estrategia; luego busca cuales son las mejores medidas que indiquen que se mueve la organización en la dirección correcta; más tarde fija objetivos para estas medidas y finalmente evalúa su cumplimiento.
Los OPIs por el contrario no son fruto del planeamiento estratégico, son fruto del conocimiento operativo (know how) de la empresa. El proceso para definir OPIs, es más simple que el KPIs, se medidas que son relevantes para la empresa y se capturan y se controlan contra un valor esperado que no necesariamente es un objetivo. A pesar de es fácil definir OPIs, requieren mas trabajo operativo por lo que sus características de "medatadata" que le son fundamentales. Debe para cada OPI definirse:
Técnicamente hablando los OPIs tienen también dos elementos de Metadata adicionales:
Los OPIs son una comunicación de expertos del negocio hacia los ejecutivos, por eso es crítico que los OPIs completen con información no numérica los valores numéricos.
El siguiente es un paso a paso de como implementar en una aplicación WPF o de Silverlight con temas que el usuario puede seleccionar de acuerdo a su gusto. Como simplificación solo implantaremos un tema controle el fondo (background) de la ventana principal y el color de los botones. Este paso a paso usa Web Expression 2.
de
Button b = sender as Button;
ResourceDictionary r = new ResourceDictionary();
r.Source = new Uri(b.Name.ToString() + ".xaml", UriKind.Relative);
Application.Current.Resources.MergedDictionaries.Clear();
Application.Current.Resources.MergedDictionaries.Add(r);
<ResourceDictionary.MergedDictionaries> <ResourceDictionary Source="Tema1.xaml"/></ResourceDictionary.MergedDictionaries>
En un posteo reciente http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=20 use un "patrón" de comando de SQL que tiene la siguiente forma:
SET @Comando='' SELECT TOP 10 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice FROM dbo.FragmentacionIndices ORDER BY TotalPages DESC;
Este comando permite concatenar un texto usando una variable, pero mi amigo Eladio Rincón, que el equipo de desarrollo de SQL, sostiene que ese comando no está garantizado que brinda resultados consistentes.
Ahora para otro proyecto tengo que concatenar una de nombres que están relacionados con una tabla. Despues de estudiar algunas alternativas me gusta la siguiente solución:
; WITH ProveedoresOrdenados (ProductID, VendorId, NumProveedor) AS
(
SELECT ProductVendor.ProductID
, ProductVendor.VendorID
, ROW_NUMBER() OVER ( PARTITION BY ProductID ORDER BY VendorID) AS NumProveedor
FROM Purchasing.ProductVendor
)
, ProdVendedorRecursivo (ProductId, VendorId, Description, NumProveedor)
AS (
SELECT ProveedoresOrdenados.ProductID
, ProveedoresOrdenados.VendorId
, CAST(ProveedoresOrdenados.VendorID AS VARCHAR(8000))
, ProveedoresOrdenados.NumProveedor
FROM ProveedoresOrdenados
WHERE NumProveedor=1
UNION ALL
SELECT ProveedoresOrdenados.ProductId, ProveedoresOrdenados.VendorId
, ProdVendedorRecursivo.Description + ', ' + CAST(ProveedoresOrdenados.VendorId AS VARCHAR(8000))
JOIN ProdVendedorRecursivo
ON ProveedoresOrdenados.ProductID=ProdVendedorRecursivo.ProductId
AND ProveedoresOrdenados.NumProveedor=ProdVendedorRecursivo.NumProveedor+1
SELECT ProdVendedorRecursivo.ProductId, ProdVendedorRecursivo.Description
FROM ProdVendedorRecursivo
JOIN (SELECT ProductId, MAX(ProdVendedorRecursivo.NumProveedor) AS NumProveedor
FROM ProdVendedorRecursivo GROUP BY ProductId) AS Last
ON ProdVendedorRecursivo.ProductId=Last.ProductId
AND ProdVendedorRecursivo.NumProveedor=Last.NumProveedor
El ejemplo se basa en la BD de AdventureWorks y la tabla ProductVendors. ProductVendors es captura una relación de muchos a muchos entre Products y Vendors el comando pretende obtener para cada producto una columna con la lista de Proveedores (Vendors).
El comando usa 2 CTEs y algunas funciones disponibles desde SQL 2005 para obtener el resultado deseado. La primera consulta usa ROW_NUMBER para obtener un número consecutivo de proveedor para cada producto, este número facilita los JOINS de la segunda consulta. La segunda consulta aprovecha las consultas recursivas para concatenar la descripción, de manera tal que cada línea tiene la descripción de todos los anteriores. Finalmente la consulta filtra y despliega solo la última fila de cada producto.
No estoy seguro de su desempeño en tablas de tamaño considerable.
Esta versión corrige algunos errores de la anterior, esa versión puede encontrarse en: http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=26
SET LANGUAGE Spanish;
GO
ALTER FUNCTION dbo.Pascua (@Yr as int)
RETURNS DATETIME
AS
BEGIN
-- Codigo encontrado en: http://www.tek-tips.com/faqs.cfm?fid=5075
Declare @Cent int, @I int, @J int, @K int, @Metonic int, @EMo int, @EDay int
Set @Cent=@Yr/100
Set @Metonic=@Yr % 19
Set @K=(@Cent-17)/25
Set @I=(@Cent-@Cent/4-(@Cent-@K)/3+19*@Metonic+15) % 30
Set @I=@I-(@I/28)*(1-(@I/28)*(29/(@I+1))*((21-@Metonic)/11))
Set @J=(@Yr+@Yr/4+@I+2-@Cent+@Cent/4) % 7
Set @EMo=3+(@I-@J+40)/44
Set @EDay=@I-@J+28-31*(@EMo/4)
RETURN CAST(CAST(@Yr*10000+@Emo*100+@Eday AS VARCHAR(8)) AS DATETIME);
/*This algorithm is from the work done by JM Oudin in 1940 and is accurate from year 1754 to 3400.*/
END;
-- Llena Dimension con Datos
; WITH Fechas(Fecha, DimFechaId) AS
SELECT CAST('19010101' AS DATETIME)
, YEAR(CAST('19010101' AS DATETIME))*10000
+MONTH(CAST('19010101' AS DATETIME))*100
+DAY(CAST('19010101' AS DATETIME))
SELECT DATEADD(DAY, 1, Fecha), YEAR(DATEADD(DAY, 1, Fecha))*10000+MONTH(DATEADD(DAY, 1, Fecha))*100+DAY(DATEADD(DAY, 1, Fecha))
FROM Fechas
WHERE DATEADD(DAY, 1, Fecha)<'20210101'
, Festividades (Festividad, Festivo, TipoFeriado, Dia, Lunes)
AS( SELECT 'Dia de todos los Santos' ,'Festivo NO Obligatorio', 'NoAplica', 1101, 0
UNION ALL SELECT 'Dia de Muertos' ,'Festivo NO Obligatorio', 'NoAplica ', 1102, 0
UNION ALL SELECT 'Aniversario de la Batalla Puebla' ,'Festivo NO Obligatorio', 'NoAplica', 505, 0
UNION ALL SELECT 'Aniversario de la Revolución Mexicana' ,'Festivo Obligatorio', 'Pasar', 1120, 3
-- 3er Lunes Noviembre
UNION ALL SELECT 'Natalicio de Benito Juarez''s' ,'Festivo Obligatorio', 'Pasar', 321, 3
-- 3er Lunes Marzo
UNION ALL SELECT 'Navidad', 'Festivo Obligatorio' ,'Fijo', 1225, 0
UNION ALL SELECT 'Dia de la Raza' ,'Festivo NO Obligatorio', 'NoAplica', 1012, 0
UNION ALL SELECT 'Dia de la Bandera' ,'Festivo NO Obligatorio', 'NoAplica', 224, 0
UNION ALL SELECT 'Dia de Independencia' ,'Festivo Obligatorio', 'Fijo', 916, 0
UNION ALL SELECT 'Dia del Trabajo', 'Festivo Obligatorio' ,'Fijo', 501, 0
UNION ALL SELECT 'Año Nuevo' ,'Festivo Obligatorio', 'Fijo', 101, 0
UNION ALL SELECT 'Día de la Virgen de Guadalupe' ,'Festivo Obligatorio', 'Fijo', 1212, 0
UNION ALL SELECT 'Dia de la Madre' ,'Festivo NO Obligatorio', 'NoAplica', 510, 0
UNION ALL SELECT 'Aniversario de la Constitucion' ,'Festivo Obligatorio', 'Pasar', 205, 1
-- Primer Lunes de Febrero
-- Agregar INSERT a la tabla.
SELECT DimFechaId
, Fechas.Fecha AS Fecha
, (DimFechaId/10000)*10000+101 AS AnioId
, CAST(Year(Fechas.Fecha) AS CHAR(4)) AS AnioNombre
, YEAR(Fechas.Fecha)*10000
+ (1+(DATEPART(Quarter, Fechas.Fecha)-1)*3)*100
+1 AS TrimestreId
, 'Q'+CAST(DATEPART(Quarter, Fechas.Fecha) AS VARCHAR(1))
+ '-' +CAST(Year(Fechas.Fecha) AS CHAR(4)) AS TrimestreNombre
, (DimFechaId/100)*100+1 AS MesId
, CAST(DATENAME(Month, Fechas.Fecha) AS VARCHAR(25))+ '-' + CAST(Year(Fechas.Fecha) AS CHAR(4)) AS MesNombre
, case when YEAR(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*10000
+MONTH(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*100
+DAY(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))
>YEAR(Fechas.Fecha)*10000+101 THEN
YEAR(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*10000
ELSE YEAR(Fechas.Fecha)*10000+101 END AS SemanaId
, 'S'+RIGHT('0'+CAST(DATENAME(Week, Fechas.Fecha) AS VARCHAR(25)),2)+ '-' + CAST(Year(Fechas.Fecha) AS CHAR(4)) AS SemanaName
, DATEPART(Weekday, Fechas.Fecha) AS DiaSemanaID
, DATENAME(Weekday, Fechas.Fecha) AS DiaSemanaNombre
, CASE
WHEN DATEPART(Weekday, Fechas.Fecha) IN (6,7) THEN 'FinSemana'
ELSE 'EntreSemana'
END AS DiaEntreFinSemana
WHEN YEAR(Fechas.Fecha)<1991 THEN 'No Aplica'
WHEN DATEPART(Weekday, Fechas.Fecha) IN (7) THEN 'NO Hábil'
WHEN Fechas.Fecha BETWEEN DATEADD(day, -2, dbo.Pascua(YEAR(Fechas.Fecha)))
AND dbo.Pascua(YEAR(Fechas.Fecha)) THEN 'NO Hábil'
WHEN (Fechas.DimFechaId%10000)=1201 -- Primero de Diciembre
AND ((Fechas.DimFechaId/10000)-1994)%6=0 -- 1 de Cada 6 años
THEN 'NO Hábil'
WHEN Festividades.Festivo='Festivo Obligatorio'
AND (Festividades.TipoFeriado='Fijo' or YEAR(Fechas.Fecha)<=2006) -- Nueva Ley Aplica del 2006 en Adelante
WHEN FLunes.Festivo='Festivo Obligatorio'
AND DATEPART(Weekday, Fechas.Fecha) =1
AND Festividades.TipoFeriado='Pasar'
AND YEAR(Fechas.Fecha)>2006
ELSE 'Hábil'
END AS DiaHabilFeriadoCorp
WHEN dbo.Pascua(YEAR(Fechas.Fecha))=Fechas.Fecha THEN 'Pascua'
WHEN Fechas.Fecha BETWEEN DATEADD(day, -1, dbo.Pascua(YEAR(Fechas.Fecha)))
AND DATEADD(day, -3, dbo.Pascua(YEAR(Fechas.Fecha)))
THEN DATENAME(Weekday, Fechas.Fecha) + ' Santo'
THEN 'Transmisión del Poder Ejecutivo Federal'
ELSE COALESCE(Festividades.Festividad
, FLunes.Festividad
, 'NO Festivo') END AS Festividad
AND dbo.Pascua(YEAR(Fechas.Fecha)) THEN 'Festivo Obligatorio'
THEN 'Festivo Obligatorio'
WHEN Festividades.Festivo IS NOT NULL and Festividades.TipoFeriado='Pasar'
THEN 'Festivo Pasado'
WHEN Festividades.Festivo IS NOT NULL
THEN Festividades.Festivo
WHEN FLunes.Festivo IS NOT NULL
THEN 'Lunes: Festivo Pasado'
ELSE 'NO Festivo' END AS TipoFestivoFestivo
LEFT JOIN Festividades
ON (Fechas.DimFechaId % 10000)=Festividades.Dia
AND YEAR(Fechas.Fecha)>=1990
LEFT JOIN Festividades AS FLunes
ON DATEPART(Weekday, Fechas.Fecha)=1 -- Dia Lunes
AND FLunes.TipoFeriado='Pasar' -- Que el Feriado sea Pasar
AND FLunes.Dia/100=month(Fechas.Fecha) -- Que sea el mismo Mes
AND DATEADD(DAY
, ((8-DATEPART(WEEKDAY, CAST(CAST(YEAR(Fechas.Fecha)*10000+(FLunes.Dia/100)*100+1 AS CHAR(8)) AS SMALLDATETIME)))%7)
+ ((FLunes.Lunes-1)*7)
, CAST(CAST(YEAR(Fechas.Fecha)*10000+(FLunes.Dia/100)*100+1 AS CHAR(8)) AS SMALLDATETIME))
=Fechas.Fecha
AND DATEPART(Weekday
, CAST(CAST( FLunes.Dia+10000*YEAR(Fechas.Fecha) as varchar(8)) AS SMALLDATETIME))
NOT IN (1, 6,7)
OPTION (MAXRECURSION 0);
El siguiente código lo genere para que mis amigos del Nacional Monte de Piedad y tiene la lógica necesaria para generar un calendario de fechas desde 1901 hasta 2020, incluyendo los feriados obligatorios y no obligatorios de ley (Día de todos los Santos, Día de Muertos, Aniversario de la Batalla Puebla, Aniversario de la Revolución Mexicana, Natalicio de Benito Juarez, Navidad, Día de la Raza, Día de la Bandera, Día de Independencia, Día del Trabajo, Año Nuevo, Día de la Virgen de Guadalupe, Día de la Madre, Aniversario de la Constitución), semana santa (jueves santo, viernes santo, sábado santo y pascua), y el día del traslado del poder ejecutivo de ley.
El código se complica por:
El código puede ser limpiado, para ser simplificado y probablemente requiere ser más comentado, NUEVA VERSION EN: http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=27:
CREATE FUNCTION dbo.Pascua (@Yr as int)
-- Codigo para llenar fecha de dimension Fecha en Mexico.
UNION ALL SELECT 'Natalicio de Benito Juarez' ,'Festivo Obligatorio', 'Pasar', 321, 3
, 300*(DimFechaId/300)+1 AS TrimestreId
, CAST(DATENAME(Month, Fechas.Fecha) AS VARCHAR(25))+ '-'
+ CAST(Year(Fechas.Fecha) AS CHAR(4)) AS MesNombre
, YEAR(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) ))*10000
+DAY(DATEADD(DAY, 1, DATEADD(DAY, -DATEPART(Weekday, Fechas.Fecha), Fechas.Fecha) )) AS SemanaId
, 'S'+RIGHT('0'+CAST(DATENAME(Week, Fechas.Fecha) AS VARCHAR(25)),2)+ '-'
+ CAST(Year(Fechas.Fecha) AS CHAR(4)) AS SemanaName
AND (Festividades.TipoFeriado='Fijo' or YEAR(Fechas.Fecha)<=2006)
-- Nueva Ley Aplica del 2006 en Adelante
AND DATEADD(DAY, ((8-DATEPART(WEEKDAY, CAST(CAST(YEAR(Fechas.Fecha)*10000
+(FLunes.Dia/100)*100+1 AS CHAR(8)) AS SMALLDATETIME)))%7)+ ((FLunes.Lunes-1)*7)
ORDER BY Fechas.Fecha
OPTION (MAXRECURSION 0)
Definitivamente el lenguaje SQL no fue creado para hacer este tipo de consultas!
Un tema recurrente en la teoría de inteligencia de negocios son los indicadores claves de desempeño o KPIs por sus siglas en ingles (Key Performance Indicators). Los KPIs son métricas que las empresas u organizaciones usan para medir el cumplimiento o avance de objetivos estratégicos.
Cuando se emplean dentro de un contexto de cuadros o tableros de mando balanceado, y usando la metodología de Kaplan y Norton se construyen en 4 etapas, de un proceso cíclico:
Diagrama de Proceso de Kaplan y Norton:
Es claro que este proceso se sale del control de los departamentos de tecnología y que debe ser promovido y regulado por la dirección general de la empresa. A pesar de esto, de forma recurrente me encuentro que muchas organizaciones quieren implementar una estrategia de KPIs y de cuadros de mando sin tomar las consideraciones estratégicas que implica, sin involucrar a los ejecutivos o dirección general de la empresa y más importante sin tener la madurez de inteligencia de negocios necesaria.
Es muy opinión que la implementación de cuadros de mando de forma prematura, cuando la organización no está suficientemente madura y sin realizar los procesos necesarios para madurar, produce efectos destructivos para la organización.
Por eso creo que es necesaria la construcción de un "nuevo término" que le permitan a las organizaciones madurar en inteligencia de negocios y facilitar la instalación de una infraestructura tecnológica que luego puede ser usada cuando la dirección requiera cuadros de mando estratégicos y KPIs.
Mas sobre la necesidad de OPIs, en el próximo posteo http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=30.
En ciertas ocasiones es necesario extraer de una tabla cuales son los números "continuos" de Facturas o de números de documentos. Esto es útil particularmente por razones de auditoría y de BI. La siguiente consulta realiza dicha operación:
SELECT Principio.NumeroFactura, MIN(final.NumeroFactura)FROM (SELECT n1.NumeroFactura FROM dbo.Facturas AS n1 LEFT JOIN dbo.Facturas AS n2 ON n1.NumeroFactura=n2.NumeroFactura+1 WHERE n2.NumeroFactura IS NULL) AS PrincipioJOIN (SELECT n1.NumeroFactura FROM dbo.Facturas AS n1 LEFT JOIN dbo.Facturas AS n2 ON n1.NumeroFactura=n2.NumeroFactura-1 WHERE n2.NumeroFactura IS NULL) AS FinalON Principio.NumeroFactura <=Final.NumeroFacturaGROUP BY Principio.NumeroFactura
La consulta puede ser intimidante al principio pero en realidad solo usa dos técnicas básicas. La primera técnica puede encontrarse en las sub-consultas internas. Analice la sub-consulta:
SELECT n1.NumeroFacturaFROM dbo.Facturas AS n1LEFT JOIN dbo.Facturas AS n2ON n1.NumeroFactura=n2.NumeroFactura+1WHERE n2.NumeroFactura IS NULL
Se puede ver que usa la combinación de un LEFT JOIN de la tabla con ella misma y un IS NULL para producir los inicios de bloques de facturas. Note que el ON usa NumeroFactura+1 o sea traducido de SQL a español podría ser: liste los NumeroFactura que no tienen (IS NULL) un número anterior consecutivo (ON n1.NumeroFactura=2.NumeroFactura+1). Esta técnica ser repite en la otra subconsulta pero usando un -1, por lo que obtiene los números que no tiene números posteriores consecutivos.
La segunda técnica se emplea para unir estas dos consultas, ya que no tiene ningún elemento identificador en común, por lo que la técnica básicamente une la factura inicial del rango escogiendo el Final del bloque de facturas como la más pequeña de las facturas finales que sea mayor o igual a el mismo. Eliminando las sub-consultas puede leerse:
SELECT Principio.NumeroFactura, MIN(final.NumeroFactura)FROM PrincipioJOIN FinalON Principio.NumeroFactura <=Final.NumeroFacturaGROUP BY Principio.NumeroFactura
Esta consulta funciona en cualquier versión de SQL (7.0, 2000, 2005 y 2008). Pero en 2005 y 2008 puede hacerse más legible cuando se usan CTEs:
;WITH Principio AS (
SELECT n1.NumeroFactura
FROM dbo.Facturas AS n1
LEFT JOIN dbo.Facturas AS n2
ON n1.NumeroFactura=n2.NumeroFactura+1
WHERE n2.NumeroFactura IS NULL)
, Final AS (
ON n1.NumeroFactura=n2.NumeroFactura-1
SELECT Principio.NumeroFactura, MIN(final.NumeroFactura)
FROM Principio
JOIN Final
ON Principio.NumeroFactura <=Final.NumeroFactura
GROUP BY Principio.NumeroFactura
Esta es una versión mejorada del procedimiento automático de desfragmentación de índices, que usa una tabla para almacenar el resultado de la consulta y así evita usar una y otra vez dm_db_index_physical_stats que tiene serios problemas de rendimiento. El código original puede verse en: http://blogs.solidq.com/ES/jloria/Lists/Posts/Post.aspx?ID=20.
USE <BaseDatos>;
CREATE FUNCTION FilteredIndexFragmentation(
@DatabaseID INT
, @ObjectID INT
, @IndexID INT
, @PartitionNumber INT=NULL
, @AverageFragmentation INT =0
, @FragmentCount BIGINT =0)
-- Author: Javier Loria, Solid Quality Mentors
-- Create date: 5/Dic/2008
-- Description: Funcion que lista los indices con un porcentaje de fragmentacion LOGICA mayor al indicado,
-- y con una cantidad mayor de fragmentos.
-- Encapsula dm_db_index_physical_stats., se requiere para poder hacer CROSS APPLY.
-- No reporta fragmentacion de tablas sin indices, indices XML o Geograficos.
-- Emplea el modo limitado 'LIMITED', por el alto costo y mal desempeno del modo 'DETAILED'
RETURNS @IndexStats TABLE(
DatabaseID SMALLINT
, ObjectID INT
, IndexID INT
, PartitionNumber INT
, IndexDepth TINYINT
, FragmentationRate FLOAT
, FragmentCount BIGINT
, AverageFragmentSize FLOAT
, PageCount BIGINT)
INSERT INTO @IndexStats(DatabaseID, ObjectID, IndexID, PartitionNumber, IndexDepth,FragmentationRate
, FragmentCount, AverageFragmentSize, PageCount)
SELECT database_id, object_id, index_id, partition_number,
index_depth, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count
FROM sys.dm_db_index_physical_stats (@DatabaseID, @ObjectID, @IndexID, @PartitionNumber, 'LIMITED' )
WHERE index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
AND avg_fragmentation_in_percent > @AverageFragmentation
AND fragment_count>@FragmentCount
RETURN
END
CREATE TABLE dbo.FragmentacionIndices(
Name sysname NOT NULL
PRIMARY KEY
, Indice varchar(512) NOT NULL
, Tamano varchar(8) NOT NULL
, TotalPages bigint NOT NULL
);
CREATE PROCEDURE [dbo].[DefragmentaIndices](
@Tipo VARCHAR(10)='Grandes' -- Medianas, Pequenas, Genera
DECLARE @db_id INT;
DECLARE @NumPages BIGINT;
DECLARE @NumIndexes INT;
DECLARE @Comando NVARCHAR(MAX);
DECLARE @DB INT
-- Parametros "ALAMBRADOS"
-- +8192 Paginas: Grande
-- +256 Paginas: Mediano
-- -256 Paginas: Pequeno
-- -32 Paginas: No Defragmenta
-- La tabla se llena con las 400 Tablas + Grandes
-- Grandes: Defragmenta 10, Medianas: Defragmenta 10, Pequenas: Defragmanta 100
SET NOCOUNT ON;
SET @DB=DB_ID() --Requerido por modo de compatibilidad 80.
IF (@Tipo NOT IN('Grandes', 'Medianas', 'Pequenas', 'Genera'))
RAISERROR('Parametro @Tipo Invalido, use: Grandes, Medianas o Pequenas', 16,1);
RETURN;
SET @db_id = DB_ID();
SET @Comando='';
IF @Tipo='Genera'
TRUNCATE TABLE dbo.FragmentacionIndices;
INSERT INTO dbo.FragmentacionIndices(Name, Indice, Tamano, TotalPages)
SELECT TOP 400
IndexPages.Name
, 'ALTER INDEX '
+ IndexPages.Name
+' ON '+OBJECT_NAME(ObjectID)+' REBUILD;' AS Indice
, CASE WHEN totalPages>=8192 THEN 'Grandes'
WHEN totalPages>=256 THEN 'Medianas'
ELSE 'Pequenas' END AS Tamano
, TotalPages
FROM (SELECT indexes.object_id
, indexes.index_id
, Indexes.Name
, sum(allocation_units.total_pages) as totalPages
FROM sys.indexes AS indexes
JOIN sys.partitions AS partitions
ON indexes.object_id = partitions.object_id
and indexes.index_id = partitions.index_id
JOIN sys.allocation_units AS allocation_units
ON partitions.partition_id = allocation_units.container_id
WHERE indexes.index_id >0
AND allocation_units.total_pages>0
GROUP BY indexes.object_id, indexes.index_id, Indexes.Name
HAVING sum(allocation_units.total_pages)> 32) AS IndexPages
-- CROSS APPLY FilteredIndexFragmentation(@DB, IndexPages.object_id, IndexPages.index_id, NULL, 20,3) AS FIF
JOIN FilteredIndexFragmentation(@DB, NULL, NULL, NULL, 20,3) AS FIF
ON IndexPages.object_id=FIF.ObjectID
AND IndexPages.index_id=FIF.IndexID
ORDER BY (IndexDepth*IndexDepth*FragmentationRate*FragmentCount/100) DESC;
IF @Tipo='Grandes'
-- Reindexa las 10 mas grandes sin importar el tamano
SET @Comando=''
SELECT TOP 10 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice
FROM dbo.FragmentacionIndices
ORDER BY TotalPages DESC;
-- Borra
DELETE dbo.FragmentacionIndices
JOIN (SELECT TOP 10 Name FROM dbo.FragmentacionIndices
ORDER BY TotalPages DESC) AS A
ON FragmentacionIndices.Name=A.Name
IF @Tipo='Medianas'
-- Reindexa las 10 mas grandes, que no sean "Grandes" (Medianas, Pequenas)
WHERE Tamano<>'Grandes'
JOIN (SELECT TOP 10 Name
IF @Tipo='Pequenas'
-- Reindexa las 100 Pequenas + Grandes
SELECT TOP 100 @Comando=@Comando+CHAR(13)+CHAR(10)+Indice
WHERE Tamano='Pequenas'
JOIN (SELECT TOP 100 Name
EXEC sp_executesql @Comando
/*
-- Pruebas
EXEC [dbo].[DefragmentaIndices] 'Genera'
SELECT * FROM dbo.FragmentacionIndices
EXEC [dbo].[DefragmentaIndices] 'Grandes'
EXEC [dbo].[DefragmentaIndices] 'Medianas'
EXEC [dbo].[DefragmentaIndices] 'Pequenas'
--
*/
En el ambiente de SQL de Microsoft no hay problema de hacer un SELECT sin usar FROM. Por ejemplo es lícito hacer:
SELECT 'Prueba'
Pero en otras plataformas, un SELECT sin FROM es sintaxis inválida.
Para lograr el mismo objetivo en Oracle puede usarse la tabla Dual, y en DB2 sysibm.sysdummy1:
SELECT 'Prueba' FROM DUAL
SELECT 'Prueba' FROM sysibm.sysdummy1
