Freddy Angarita

[PPT] Presentación Novedades SqlServer 2012 BI

2012-04-25T20:21:00Z

Adjunto el archivo de la presentación sobre novedades de Inteligencia de negocios en el Sql Server 2012 - Del evento Saturday 124

[WebCast] Creación de un Modelo Semántico Tabular Sql Server 2012

2012-04-10T16:32:00Z

En el video se observa paso a paso la creación y exploración de un modelo semántico tabular en Sql Server 2012

[Code] Nueva versión crear tabla a partir de un texto separado por comas (CSV)

2012-01-18T02:16:00Z

En un artículo anterior, [Code] Crear una tabla a partir de un texto separado por comas (CSV), se presentó cómo realizar ésta tarea de la manera más simple, ahora, se complementará ésta solución presentando una alternativa que usa CTEs para realizar el trabajo recursivo de analizar las palabras en la sentencia

Usando la capacida de las expresiones CTEs de crear iteraciones se permite la creación de la iteración consigo mismo, es decir en la primera parte, se pone la semilla y en la segunda parte (luego de union all) se implementa la iteración, la cual devuelve las posiciones inicial y final del texto al que se le va a aplicar substring:

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512)) 
RETURNS table 
AS 
RETURN ( 
    WITH Pieces(pn, start, stop) AS ( 
      SELECT 1, 1, CHARINDEX(@sep, @s) 
      UNION ALL 
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) 
      FROM Pieces 
      WHERE stop > 0 
    ) 
    SELECT pn, 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s 
    FROM Pieces 
  )

La solución anterior presenta una forma interesante de aproximarse al problema pero aún usa recursión, esto se puede eliminar usando XML como base de análisis

CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX)) 
 
RETURNS @t TABLE 
    ( 
        val VARCHAR(MAX) 
    )    
AS 
    BEGIN 
        DECLARE @xml XML 
        SET @XML = N'' + REPLACE(@s, @sep, '') + '' 
 
        INSERT INTO @t(val) 
        SELECT r.value('.','VARCHAR(255)') as Item 
        FROM @xml.nodes('//root/r') AS RECORDS(r) 
 
        RETURN 
    END

El truco consiste en convertir el texto, en un xml y luego usando el tipo de datos especial (y sus funciones) leemos la información como una tabla desde XML, el problema con esta solución es que tiene problemas de desempeño y se presenta solo como referencia

También se presenta uno de mis trucos favoritos el uso de la función dm_fts_parser, la cual permite realizar éste análisis rápidamente, con la ventaja de:

ser una función del sistema,
permitir configurar el idioma
Ser sensible al acentro y mayúsculas
Poder usar una lista de palabras a ignorar
Más funcionalidades

select display_term from sys.dm_fts_parser('"' + 'Mi texto separado por espacios' + '"', 1033, 0,0)

Es ejemplo está para idioma inglés con el código 1033, para un listado completo se puede consultar el listado LCID

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo convertir un número a letras

2012-01-18T02:02:00Z

Lo primero que se debe advertir antes de poner el código a disposición de todos, es que no recomiendo esta aproximación de manera práctica dado que SQL está optimizado para cálculos basados en conjuntos (Selects, Joins y demás); manejo de cadenas y cálculos se deberían hacer de otras maneras (CLR, Código Ciente, etc.), pero si alguna vez se necesita o si se quiere traducir a otro lenguaje se puede usar el siguiente código para generar números como letras. Para tener más claridad sobre el tema de buenas prácticas se puede revisar el artículo de optimización de consultas

No es el código más eficiente que se puede escribir para el tema, pero funciona:

create PROCEDURE [dbo].[sp_Dinero_a_Texto]
(
  @Numero NUMERIC(19,4)
 )
AS
BEGIN
 
  SET NOCOUNT ON
  DECLARE @lnEntero INT,
    @lcRetorno VARCHAR(512),
    @lnTerna INT,
    @lcMiles VARCHAR(512),
    @lcCadena VARCHAR(512),
    @lnUnidades INT,
    @lnDecenas INT,
    @lnCentenas INT,
    @lnFraccion INT
 
  SELECT @lnEntero = CAST(@Numero AS INT),
    @lnFraccion = (@Numero - @lnEntero) * 100,
    @lcRetorno = '',
    @lnTerna = 1
 
  WHILE @lnEntero > 0
  BEGIN /* WHILE */
 
    -- Recorro columna por columna
    SELECT @lcCadena = ''
    SELECT @lnUnidades = @lnEntero % 10
    SELECT @lnEntero = CAST(@lnEntero/10 AS INT)
    SELECT @lnDecenas = @lnEntero % 10
    SELECT @lnEntero = CAST(@lnEntero/10 AS INT)
    SELECT @lnCentenas = @lnEntero % 10
    SELECT @lnEntero = CAST(@lnEntero/10 AS INT)	

	--print cast(@lnCentenas as varchar(3)) + cast(@lnDecenas as varchar(3))+ cast(@lnUnidades as varchar(3))
	--print @lnEntero
 
    -- Analizo las unidades
    SELECT @lcCadena =
    CASE /* UNIDADES */
      WHEN @lnUnidades = 1 AND @lnTerna = 1 THEN 'UNO ' + @lcCadena
      WHEN @lnUnidades = 1 AND @lnTerna <> 1 THEN 'UN ' + @lcCadena
      WHEN @lnUnidades = 2 THEN 'DOS ' + @lcCadena
      WHEN @lnUnidades = 3 THEN 'TRES ' + @lcCadena
      WHEN @lnUnidades = 4 THEN 'CUATRO ' + @lcCadena
      WHEN @lnUnidades = 5 THEN 'CINCO ' + @lcCadena
      WHEN @lnUnidades = 6 THEN 'SEIS ' + @lcCadena
      WHEN @lnUnidades = 7 THEN 'SIETE ' + @lcCadena
      WHEN @lnUnidades = 8 THEN 'OCHO ' + @lcCadena
      WHEN @lnUnidades = 9 THEN 'NUEVE ' + @lcCadena
      ELSE @lcCadena
    END /* UNIDADES */

	print @lcCadena
 
    -- Analizo las decenas
    SELECT @lcCadena =
    CASE /* DECENAS */
      WHEN @lnDecenas = 1 THEN
        CASE @lnUnidades
          WHEN 0 THEN 'DIEZ '
          WHEN 1 THEN 'ONCE '
          WHEN 2 THEN 'DOCE '
          WHEN 3 THEN 'TRECE '
          WHEN 4 THEN 'CATORCE '
          WHEN 5 THEN 'QUINCE '
          ELSE 'DIECI' + @lcCadena
        END
      WHEN @lnDecenas = 2 AND @lnUnidades = 0 THEN 'VEINTE ' + @lcCadena
      WHEN @lnDecenas = 2 AND @lnUnidades <> 0 THEN 'VEINTI' + @lcCadena
      WHEN @lnDecenas = 3 AND @lnUnidades = 0 THEN 'TREINTA ' + @lcCadena
      WHEN @lnDecenas = 3 AND @lnUnidades <> 0 THEN 'TREINTA Y ' + @lcCadena
      WHEN @lnDecenas = 4 AND @lnUnidades = 0 THEN 'CUARENTA ' + @lcCadena
      WHEN @lnDecenas = 4 AND @lnUnidades <> 0 THEN 'CUARENTA Y ' + @lcCadena
      WHEN @lnDecenas = 5 AND @lnUnidades = 0 THEN 'CINCUENTA ' + @lcCadena
      WHEN @lnDecenas = 5 AND @lnUnidades <> 0 THEN 'CINCUENTA Y ' + @lcCadena
      WHEN @lnDecenas = 6 AND @lnUnidades = 0 THEN 'SESENTA ' + @lcCadena
      WHEN @lnDecenas = 6 AND @lnUnidades <> 0 THEN 'SESENTA Y ' + @lcCadena
      WHEN @lnDecenas = 7 AND @lnUnidades = 0 THEN 'SETENTA ' + @lcCadena
      WHEN @lnDecenas = 7 AND @lnUnidades <> 0 THEN 'SETENTA Y ' + @lcCadena
      WHEN @lnDecenas = 8 AND @lnUnidades = 0 THEN 'OCHENTA ' + @lcCadena
      WHEN @lnDecenas = 8 AND @lnUnidades <> 0 THEN 'OCHENTA Y ' + @lcCadena
      WHEN @lnDecenas = 9 AND @lnUnidades = 0 THEN 'NOVENTA ' + @lcCadena
      WHEN @lnDecenas = 9 AND @lnUnidades <> 0 THEN 'NOVENTA Y ' + @lcCadena
      ELSE @lcCadena
    END /* DECENAS */

	print @lcCadena
 
    -- Analizo las centenas
    SELECT @lcCadena =
    CASE /* CENTENAS */
      WHEN @lnCentenas = 1 AND @lnUnidades = 0 AND @lnDecenas = 0 THEN 'CIEN ' + @lcCadena
      WHEN @lnCentenas = 1 AND NOT(@lnUnidades = 0 AND @lnDecenas = 0) THEN 'CIENTO ' + @lcCadena
      WHEN @lnCentenas = 2 THEN 'DOSCIENTOS ' + @lcCadena
      WHEN @lnCentenas = 3 THEN 'TRESCIENTOS ' + @lcCadena
      WHEN @lnCentenas = 4 THEN 'CUATROCIENTOS ' + @lcCadena
      WHEN @lnCentenas = 5 THEN 'QUINIENTOS ' + @lcCadena
      WHEN @lnCentenas = 6 THEN 'SEISCIENTOS ' + @lcCadena
      WHEN @lnCentenas = 7 THEN 'SETECIENTOS ' + @lcCadena
      WHEN @lnCentenas = 8 THEN 'OCHOCIENTOS ' + @lcCadena
      WHEN @lnCentenas = 9 THEN 'NOVECIENTOS ' + @lcCadena
      ELSE @lcCadena
    END /* CENTENAS */
	print @lcCadena
 
    -- Analizo los millares
    SELECT @lcCadena =
    CASE /* TERNA */
      WHEN @lnTerna = 1 THEN @lcCadena
      WHEN @lnTerna = 2 AND (@lnUnidades + @lnDecenas + @lnCentenas <> 0) THEN @lcCadena + ' MIL '
      WHEN @lnTerna = 3 AND (@lnUnidades + @lnDecenas + @lnCentenas <> 0) AND
        @lnUnidades = 1 AND @lnDecenas = 0 AND @lnCentenas = 0 THEN @lcCadena + ' MILLON '
      WHEN @lnTerna = 3 AND (@lnUnidades + @lnDecenas + @lnCentenas <> 0) AND
        NOT (@lnUnidades = 1 AND @lnDecenas = 0 AND @lnCentenas = 0) THEN @lcCadena + ' MILLONES '
      WHEN @lnTerna = 4 AND (@lnUnidades + @lnDecenas + @lnCentenas <> 0) THEN @lcCadena + ' MIL MILLONES '
      ELSE ''
    END /* MILLARES */
	print @lcCadena
 
    -- Armo el retorno columna a columna
	--print @lcCadena
    SELECT @lcRetorno = @lcCadena + @lcRetorno
    SELECT @lnTerna = @lnTerna + 1
 
  END /* WHILE */
 
  IF @lnTerna = 1
    SELECT @lcRetorno = 'CERO'
 
  SELECT RTRIM(@lcRetorno) + CASE WHEN LEN(LTRIM(STR(@lnFraccion,2))) = 1 THEN '0'+LTRIM(STR(@lnFraccion,2))
                                               ELSE LTRIM(STR(@lnFraccion,2)) END AS Texto
 
END

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Llenado de Espacio en Datos (Generación de Datos Acumulados) por Grupos

2012-01-08T16:34:00Z

Llenado de Espacio en Datos (Generación de Datos Acumulados) por Grupos

Normalmente los productos de software llevan un registro de los hechos que se Fsentan en el negocio, ventas, desembolsos, cobros, intereses, etc. Pero por supuesto no todos los días se vende, se desembolsa, se cobra initereses, cómo podemos resolver de una eficiente la necesidad ver los datos acumulados, este escenario, por lo meno de manera parcial, fué resuelto en un artículo anterior [Code] Cómo calcular un total acumulado Rápido SIN Cursores. Este artículo complementa esa solución presentando cómo se haría lo mismo para generar datos acumulados (o llenar espacios) teniendo en cuenta diferentes grupos dentro de los datos

También se presentará una aplicación de otro artículo llamado [Code] Cómo generar N Filas Rápido Sql Server el cual se usará para generar los datos necesarios para llenar los espacios de la información.

Datos de ejemplo

declare @hechos table (id int, fecha date, producto int, venta decimal)

insert into @hechos	values (1, '2011-09-01', 1, 10)
insert into @hechos	values (2, '2011-09-03', 1, 20)
insert into @hechos	values (3, '2011-09-04', 1, 2)
insert into @hechos	values (4, '2011-09-08', 1, 1)
insert into @hechos	values (5, '2011-09-10', 1, 3)
insert into @hechos	values (6, '2011-09-12', 1, 4)

insert into @hechos	values (7, '2011-09-01', 2, 3)
insert into @hechos	values (8, '2011-09-03', 2, 2)
insert into @hechos	values (9, '2011-09-04', 2, 1)
insert into @hechos	values (10, '2011-09-08', 2, 5)
insert into @hechos	values (11, '2011-09-10', 2, 6)
insert into @hechos	values (12, '2011-09-12', 2, 7)

Los datos en formato tabla:

Id	Fecha	Producto	Venta
1	2011-09-01	1	10
2	2011-09-03	1	20
3	2011-09-04	1	2
4	2011-09-08	1	1
5	2011-09-10	1	3
6	2011-09-12	1	4
7	2011-09-01	2	3
8	2011-09-03	2	2
9	2011-09-04	2	1
10	2011-09-08	2	5
11	2011-09-10	2	6
12	2011-09-12	2	7

Observemos que las fechas de venta para cada producto no suceden en días consecutivos, se tienen 2 diferentes productos a los que se quiere calcularle sus ventas acumuladas por día y una cantidad de ventas

Lo primero que haremos es calcular el número de días de diferencia entre la mayor y menos fecha de toda la tabla (para todos los productos)

declare @FechaMin date, @FechaMax Date, @Diferencia int
select  @FechaMin = min(Fecha), @FechaMax = MAX(Fecha) from @hechos

set @Diferencia = DATEDIFF(dd,@FechaMin, @FechaMax)

Alternativamente podríamos crear una tabla que tenga la fecha mínima y máxima por cada producto, pero se presentará así para mantener simple el ejemplo.

Pasos:

Se generea un CTE con una tabla que tiene todas las fechas posibles (incluso las que no están en la tabla @hechos), llamado Fechas, El código de GenTable se encuentra en [Code] Cómo generar N Filas Rápido Sql Server
También se declara una tabla @Acumulado que tendrá el resultado final con los valores acumulados por producto
En el CTE ProductoFecha aplicamos una función de conjunto cross join para aplicar cada fecha generada a cada una de los productos de @hechos, lo que nos dá como resultado todas las fechas posibles para cada producto
Luego, se inserta en @Acumulado el resutaldo de hacer left join entre ProductoFecha (que tiene todas las fechas) y @Hechos lo que nos da, cada producto y cada fecha posible, en las fechas que no se realizaron ventas se agrega cero (0)

declare @Acumulado table (fecha date, producto int, venta decimal, VentaAcumulada decimal)

;With Fechas (Fecha) as
(
select DATEADD(dd,GT.i,@FechaMin) Fecha from dbo.GenTable(0,@Diferencia,1) GT
)
, ProductoFecha (Producto, Fecha) as (
select distinct Producto, Fechas.Fecha from @hechos cross join Fechas
)
insert into @Acumulado (fecha, producto, venta, VentaAcumulada)
Select PF.Fecha, PF.Producto, isnull(H.Venta,0) as Ventas, 0  from ProductoFecha PF left join @hechos H ON PF.Producto = H.producto and PF.Fecha = h.fecha

Ahora se procede a calcular el total acumulado por grupos:

declare @runningtotal decimal , @ProductoActual int

set @runningtotal = 0 
select @ProductoActual = 0 

--Anterior
update @Acumulado 
set 
@runningtotal = VentaAcumulada = case when not @ProductoActual = Producto then venta else 
case when Venta = 0 then @runningtotal else venta end
end
,@ProductoActual = Producto

La parte clave es donde se define el Producto actual por que permite la detección de cambio de grupos para generar así su acumulado, esta técnica puede ser aplicada a múltiples grupos, por ejemplo, ciudad - producto solo agregando otro grupo (para el caso del ejemplo sería @ProductoActual) en el orden que se necesite y así generar todos los grupos necesarios

Los resultados:

fecha	producto	venta	VentaAcumulada
2011-09-01	1	10	10
2011-09-02	1	0	10
2011-09-03	1	20	20
2011-09-04	1	2	2
2011-09-05	1	0	2
2011-09-06	1	0	2
2011-09-07	1	0	2
2011-09-08	1	1	1
2011-09-09	1	0	1
2011-09-10	1	3	3
2011-09-11	1	0	3
2011-09-01	2	3	3
2011-09-02	2	0	3
2011-09-03	2	2	2
2011-09-04	2	1	1
2011-09-05	2	0	1
2011-09-06	2	0	1
2011-09-07	2	0	1
2011-09-08	2	5	5
2011-09-09	2	0	5
2011-09-10	2	6	6
2011-09-11	2	0	6

Para que ésta solución funcione, los datos deben estar previamente ordenados (por producto y fecha)

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Conteo de Filas de cada una de las tablas de la base de datos

2012-01-06T01:20:00Z

Conteo de Filas de cada tabla

Haciendo diagnóstico de las bases de datos, realizando seguimientos al crecimiento del número de registros y en fin para conocer el número de registros en todas las tablas existen varias técnicas pero existen unas mejores que otras y a contiuación se presentarán:

Una de las aproximaciones es usar uno de los procedimientos almacenados más prácticas que tiene TSql sp_MSforeachtable la cual nos permite ejecutar algún tipo de instrucción para cada una de las tablas de la base de datos, incluso admite filtros

exec sp_MSforeachtable 'select ''?'' TableName, count(*) Cnt from ?';

El asunto con esta solución es que se genera un resultset para cada una de las tablas (es decir, no es un solo resultado, sino uno por cada tabla) lo cual para cosas como un reporte son complicadas de manejar.

Entonces la siguiente aproximación sería desarrollar un procedimiento almacenado que genere la consulta y luego la una y me presente un solo resultado, algo como:

declare @tempTable table
(
    TableSchema nvarchar(256),
    TableName nvarchar(256),
    Cnt bigint
);

declare @sql nvarchar(4000);
declare @tableSchema nvarchar(256);
declare @tableName nvarchar(256);
declare @columnName sysname;
declare @cnt bigint;

declare tableCursor cursor for
    select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = 'BASE TABLE';

open tableCursor;

fetch next from tableCursor into @tableSchema, @tableName;

while @@FETCH_STATUS = 0
begin
    set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName + ']';

    exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

    insert into @tempTable select @tableSchema, @tableName, @cnt;

    fetch next from tableCursor into @tableSchema, @tableName;
end;

close tableCursor;
deallocate tableCursor;

select * from @tempTable;

Por supuesto esta solución tiene varios inconvenientes, para empezar usa un cursor para leer el listado de tablas de la consulta estandar INFORMATION_SCHEMA, segundo usa un sql dinámico para armar poco a poco la consulta que se desea ejecutar y algo que no se ve a simple vista y es la necesidad de ejecutar COUNT para cada una de las tablas, por lo cual ésta no es la solución adecuada tampoco

Ahora veremos cómo podemos encontrar el conteo y de paso saber cual es la tabla que cuenta con mayor cantidad de registros

Sql 2000

Sql 2005+

SELECT OBJECT_NAME(id),rowcnt
FROM SYSINDEXES
WHERE OBJECTPROPERTY(id,’isUserTable’)=1 AND indid < 2
ORDER BY rowcnt DESC

select sc.name +'.'+ ta.name
,sum(pa.rows) -- Valor aproximado
from sys.tables ta
inner join sys.partitions pa
on pa.object_id = ta.object_id
inner join sys.schemas sc
on ta.schema_id = sc.schema_id
where ta.is_ms_shipped = 0	AND pa.index_id IN (1,0)
group by sc.name,ta.name
ORDER BY sum(pa.rows) DESC

Sql Server crea estadísticas para cada tabla lo que le permite optimizar las consultas que ejecutamos por lo que podemos usar estas estadísticas para obtener éste dato, también, analizar la consulta, observamos que se tiene en cuenta si la tabla está particionada y asimismo se consideran los esquemas para que la información quede completa

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Pivote dinámico con sql server

2012-01-02T15:02:00Z

Pivotes Dinámicos

En muchas ocaciones tenemos información en filas y queremos convertirlas en columnas, ésto se presenta especialmente en escenarios de análisis en donde tener la información en colunas es un requerimiento.

Revisemos el ejemplo que presenta MSDN en su documentación

Si tenemos una consulta:

USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;

La cual arroja los siguiente resultados

DaysToManufacture	AverageCost
0	5.0885
1	223.88
2	359.1082
4	949.4105

y queremos verla en columnas

Cost_Sorted_By_Production_Days	0	1	2	3	4
AverageCost	5.0885	223.88	359.1082	NULL	949.4105

Debemos usar PIVOT

SELECT 'AverageCost' AS
Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable

PIVOT
(AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

Revisando la sintaxis de la consulta tenemos que debemos definir una a una las columnas que deseamos obtener con la consulta y que éstas correspondan al Valor que queremos poner en esa columna, es decir, para cero (0) días de manufactura debemos crear una columan con el nombre 0 para que se asocie el valor que se tiene para esa columna, y se observa también que se debe incluir una función de agregación para crear un contexto para cada celda en el momento de poner el valor en la columna, esto es clave para poder generar el pivote.

Si se ha trabajado anteriormente con tablas dinámicas se verá que el comportamiento es similar tan solo debemos escribir nosotros mismos la consulta

EN SQL 2000

Acá tenemos una idea de cómo se podría solucionar en Sql 2000, algunas de las funciones que se presentan en ésta solución pronto será descontinuadas en las futuras versiones de SQL

use AdventureWorks2008R2;
go

SELECT  
  [No. Sales per year]=CASE WHEN row IS NULL THEN 'Sum'  
                   ELSE CONVERT(VARCHAR(80),[row]) END , 
  [1996] =SUM( CASE col WHEN '2005' THEN data ELSE 0 END ), 
  [1997] =SUM( CASE col WHEN '2006' THEN data ELSE 0 END ), 
  [1998] =SUM( CASE col WHEN '2007' THEN data ELSE 0 END ), 
  [Total]= SUM( data ) 
FROM  
   (SELECT [row]=P.firstname+' '+ P.lastname,  
           [col]=YEAR(SOH.OrderDate),  
           [data]=COUNT(*) 
    FROM Sales.SalesPerson SP INNER JOIN Sales.SalesOrderHeader SOH ON (SP.BusinessEntityID=SOH.SalesPersonID)  
    inner join HumanResources.Employee E ON SP.BusinessEntityID = E.BusinessEntityID 
    inner join Person.Person P ON E.BusinessEntityID = P.BusinessEntityID 
    GROUP BY P.firstname+' '+ P.lastname, YEAR(SOH.OrderDate) 
    )f 
GROUP BY row WITH ROLLUP 
ORDER BY GROUPING(row),total DESC

IDEAS

Siempre que aprendemos algo se nos ocurren mas ideas, tales como: Qué pasa si se tienen días de manufactura diferentes? qué pasa si quiero una columna para cada uno de esos días, que pasa si lo hacemos por producto? por pais? qué pasa si cada uno de ellos tiene un comportamiento diferente?

Muchas de esas preguntas se responden realizando un pivote con columnas dinámicas, es decir, al contrario de la sintaxis normal en donde debemos definir cada columna, que se interprete y de obtengan todas las columnas que necesito sin necesidad de definirlas una a una

CONSULTA DINÁMICA

use AdventureWorks2008R2;
go

declare @columnas varchar(max)

set @columnas = ''

select @columnas =  coalesce(@columnas + '[' + cast(DaysToManufacture as varchar(12)) + '],', '')
FROM (select distinct DaysToManufacture from Production.Product) as DTM

set @columnas = left(@columnas,LEN(@columnas)-1)

DECLARE @SQLString nvarchar(500);

set @SQLString = N'
SELECT *
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN (' + @columnas + ')
) AS PivotTable;'

EXECUTE sp_executesql @SQLString

Pero esta solución tiene el inconveniente de tener que lidiar con consultas Ad-Hoc (Dinámicas) que asocian posibles problemas de seguridad y rendimiento a la solución, esta solución así como la demás que se puedan implementar debe considerar dónde se van a usar, posiblemente sea mas eficiente realizar el pivote en reporting services (Matriz) que en Sql, esto debe evaluarse para cada y depende de lo que se quiera realizar

OPCION CTE - Nombres de columna dinámicos

Para 2005 o superior existe una alternativa para facilitar, mas no soluciona del todo el problema y es crear una consulta que asigna valores (números a las columnas) facilitando la construcción final de la consulta. En este caso queremos pivotear por el nombre del mes, pero supongamos que el rango que damos en la primera consulta cambia, entonces los nombre de las columnas (Nombre del mes) no podrían ser usados.

Por ejemplo si enviamos como parámetro el primero de agosto, hasta el 31 de diciembre o si enviamos el 1 de enero al 30 de junio, tendríamos que cambiar constantemente la consulta o presentar todos los meses, para solucionar esto tenemos el siguiente ejemplo

use AdventureWorksDW2008R2;
go

;With monthyears as (
SELECT DISTINCT SpanishMonthName as CalendarMonthYearName, MonthNumberOfYear as MonthOfYear,CalendarYear
      FROM dbo.DimDate 
      WHERE DateKey > @FechaInicio AND DateKey < @FechaFinal
),
CalMonthYears AS
(
SELECT CalendarMonthYearName AS 'CalendarMonthYearName',
      MonthOfYear,
      CalendarYear,
      ROW_NUMBER() OVER (ORDER BY CalendarYear DESC) AS 'YearMonthNum'  
      FROM monthyears
)
SELECT ProductKey,
      [1],[2],[3],[4],[5],[6]
FROM
(
-- your query to get your data, pivot this data
SELECT YearMonthNum,s.ProductKey,
      SUM(OrderQuantity) AS 'Quantity'
      FROM FactResellerSales s
      INNER JOIN dbo.DimDate d ON s.OrderDateKey = d.DateKey
      INNER JOIN CalMonthYears cmy ON d.SpanishMonthName = cmy.CalendarMonthYearName
GROUP BY YearMonthNum,s.ProductKey
) AS SourceTable
PIVOT
(
SUM(Quantity)
FOR YearMonthNum IN (
      [1],[2],[3],[4],[5],[6]
      )
) AS PivotTable

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Calcular Edad a cualquier fecha

2011-10-04T00:36:00Z

Este problema normalmente se soluciona del lado del cliente (Reporte, Aplicación) pero algunas ocaciones necesitamos presentarlo en algunas consultas por lo que la siguiente función puede ser práctica en varios escenarios

Tomemos como ejemplo las siguientes fechas (obsérvese especialmente la última fecha que muestra el número de día siguiente, diez años) y veamos cómo las funciones tradicionales muestran la diferencia en años, pero el cálculo no es correcto para la edad del usuario, incluso en la segunda aproximación tratando de solucionarlo por meses tampoco obtenemos el resultado esperado.

declare @Fechas table (FechaNacimiento date)
declare @hoy date
set @hoy = GETDATE()

insert into @Fechas values ('1978-05-20')
insert into @Fechas values ('1963-01-14')
insert into @Fechas values (DATEADD(yyyy,-10, DATEADD(dd,1,@hoy)))

select * from @Fechas

select

DATEDIFF(yyyy,FechaNacimiento, @hoy), cast(DATEDIFF(mm, FechaNacimiento, @hoy) as decimal) / 12 from @Fechas

Obtendremos resultados como (dependiendo de la fecha en que se ejecute el script, dado que toma la fecha actual del sistema):

Edad Esperada	Diferencia Años	Diferencia Meses
33	33	33.416666
48	48	48.750000
9	10	10.000000

Ahora la solución propuesta para éste problema es crear una función que lo resuelva retornando el valor adecuado, incluso para fechas diferentes a la actual (se puede enviar como parámetro la fecha a la que quiero calcular la edad)

create

function ObtenerEdad(@FechaNacimiento date, @AFecha date = null)
returns int
Begin

declare @hoy date, @Edad int
if @hoy is null set @hoy = GETDATE() else set @hoy = @AFecha

set

@Edad = CASE WHEN MONTH(@FechaNacimiento) > MONTH(@hoy) THEN (YEAR(@hoy)
- YEAR(@FechaNacimiento) - 1) WHEN MONTH(@hoy) = MONTH(@FechaNacimiento)
AND DAY(@FechaNacimiento) > DAY(@hoy) THEN YEAR(@hoy) - YEAR(@FechaNacimiento)
- 1 ELSE YEAR(@hoy) - YEAR(@FechaNacimiento) END

return @Edad
End

Si intento de nuevo con los valores ingresados inicialmente obtenendré la edad correcta en cada caso

select dbo.ObtenerEdad(FechaNacimiento) as Edad, DATEDIFF(yyyy,FechaNacimiento, @hoy), cast(DATEDIFF(mm, FechaNacimiento, @hoy) as decimal) / 12
from @Fechas

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo numerar filas en SSIS

2011-09-26T20:19:00Z

Numerar Filas puede ser de gran ayuda para crear lógica en SSIS, para generarlas se pueden seguir los siguientes pasos

Agregar un DataFLow Task
Agregar un Origen de datos con una consulta a la que le queramos generar los números de fila
Agregar un Script Component Agregar una Columna de Salida Llamada (para el ejemplo) Numero, con un código similar al presentado a continuación:

public

class ScriptMain : UserComponent{
private int _RowNumber = 0;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
_RowNumber++;
Row.Numero1 = _RowNumber;}
}

El códiigo generará el número de fila de acuerdo al ordenamiento actual
Se podrá repetir el paso 3 para agregar el número de ordenamientos que sea necesario

Se agrega un paquete de ejemplo para realizar ésta tarea y como parte del ejemplo se distribuyen los productos en pares e impares

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Info] Contadores de Rendimiento para diagnóstico y afinación de SqlServer

2011-09-10T21:46:00Z

A continuación presento alguno de los contadores de rendimiento, pertenecientes a la instrumentación de SqlServer que ayudan al proceso de diagnóstico y optimización de SqlServer

En el gráfico se presenta el grupo de contadores de rendimiento, luego al abrirlo se presenta cada contador independiente y luego en la parte de abajo (si aplica) que es lo q se quiere medir

Procesador

Processor		Esperado
%User Time	SqlServer se ejecuta en modo de Usuario. El modo privilegiado es usado por el Sistema operativo para acceder a los componentes de hardware. Este componente debería presentar un valor mayor de 70%, si el valor es menor que esto se deben revisar los indicadores %privileged time y %processor time para verificar si existe algún tipo de problema con el procesador	>70%
%Privileged Time	El sistema operativo mueve los hilos al modo de acceso privilegiado para acceder servicios de Hardware. Este contador debe tener un valor menor a 20%.	<20%
%Processor Time	Presenta el porcentaje de tiempo usado por la CPU en un periodo de tiempo. Idealmente debe ser menor que 70%.	<70%
Interrupts/Sec	Es el promedio de incidentes por segundo, al cual el procesador recibió y procesó interrupciones de Hardware

System		Esperado
Processor Queue Length	Es el número de hilos que espearan a ser procesados. Este contador por valor general divido por el número de núcleos disponibles. Debería ser menor que 2	<2

Disco

Physical Disk		Esperado
Current Queue Length	Un tamaño sostenido de una cola de disco presenta un posible problema con el subsistema de I/O.	0
Average Queue Length	Si el promedio de longitud de la cola es mayor que 2 indica que existe un problema potencial con el subsistea de IO, esto tambien puede deberse a un indicador disk sec/read y disk sec/write alto.	<=2
Disk Sec/Read Disk Sec/Writes	Ninguno de estos contadores debería tener un valor superior a 15ms en condiciones normales. Valores continuos sobre 20ms puede indicar un problema en el disco o sobrecarga del sistema o alto fraccionamiento del disco. Bajo estas condiciones se pueden considerar las siguientes alternativas: Mover los archivos de base de datos a discos adicionales Crear grupos de archivos en diferentes discos y pasar algunas tablas a cada uno de esos discos (pueden tener difernte RAID) lo cual puede influir notablemente en el desempeño Revisar los índices de las tablas Revisar los contadores de CPU y memoria con el fin de identificar otros cuellos de Botella	<=15

Memory/Cache

Memory

Page Faults/Sec:

Los fallos de página ocurren cuando se busca una página en memoria y no se encuentra. Existen 2 tipos de fallos de página:

Fuertes (Hard) : Requiere acceso a disco

Suaves (Soft). La página buscada está en alguna otra parte de la memoria

Un alto número de fallos de página por segundo indica que existe un problema con la memoria usada por Sql Server. Usando este contador junto con SqlServer:MemoryManager se puede determinar si existe algun tipo de presión sobre la memoria

Bajo estas condiciones puede considerarse:

Revisar los procedimientos y las prácticas usadas para el desarrollo de los mismos
Aumentar la memoria del servidor
Revisar el espacio en disco disponible en donde se ubiquen los temporales del sistema operativo

Pages/Sec

Este contador es valor real de fallos Fuertes de páginas y debe ser usado en correlación con Page Faults/Sec y SQL Server memory Manager para revisar la presión sobre la memoria

SQL Server

Access Methods
Forwarded Records/Sec	Numero de registros traídos usando punteros de registros, es decir, cuando los registros tienen espacios vacíos entre ellos o cuando el tamaño del registro no queda ajustado al tamaño de una página, esta situación puede presentarse cuando se inicia con un registro corto y se actualiza el dato quedando parte de éste en otra página lo que requiere la generación de un puntero para saber en qué lugar está la otra parte del registro. Esto se puede evitar siguiendo los siguientes pasos: Ser cuidadoso al determinar cuales de las columnas permiten o no nulos Usar valores por defecto (para evitar el uso de nulos) Usar char en lugar de Varchar cuando sea posible Manejar un esquema de normalización en donde el número de campos sea menos (Soluciones OLTP)
Full Scans/Sec	Se realiza una lectura completa de una Tabla o de un índice. Esto puede ser causado por el uso indebido de índices.
Page Splits/Sec	Fraccionamiento sobre las páginas de los índices. Este indicador está asociado a las páginas hoja del Arbol de índices que no se almacenan conitnuamente lo que lleva a su fraccionamiento en disco. Esto se puede evitar configurando apropiadamente Fill Factor

Memory Manager

Memory Grants Pending

Memoria que se necesita para procesar cada una de las peticiones de los usuarios. Si no se dispone de memoria suficiente entonces el proceso debe esperar a que se le sea asignada para su ejecución, lo que por supuesto impacta el desempeño de la consulta o procedimiento en ejecución

Para evitarlo se pueden seguir los siguientes pasos:

Agregar mas memoria al servidor
Asignando mas memoria a SqlServer
Creando Índices apropiados

Buffer Manager		Esperado
Buffer Cache Hit Ratio	Porcentaje del tiempo en que las páginas solicitadas están ya en memoria. Idealmente debe mantenerse sobre 99%, si es menor que 95% indica que SqlServer no tiene suficiente memoria y que agregar más memoria al servidor, asignada a SqlServer, sería muy beficioso	>99%
Checkpoints/Sec	Paginas escritas a disco durante el proceso de CheckPoint. Se identifica presión sobre la memoria si el contador tiene un valor superior a 300 Segundos	<300s
Lazy Writes/Sec	Paginas escritas a disco durante el proceso LazyWriter (Escribe las páginas en segundo plano al disco). Se identifica presión sobre la memoria si el contador tiene un valor superior a 300 Segundos	<300s
Page Life Expectancy	Este es uno de los principales contadores para identificar presión sobre la memoria. Determina el tiempo en segundos en el que la página reside en el Caché de SqlServer. Si el valor es bajo indica los siguientes problemas: El Caché es Frio (Revisar información sobre Fallos de Página Page Faults) Problemas de Memoria Falta de creación de ïndices Si Checkpoints/Sec, Lazy Writes/Sec y Page life expectancy juntos es menor a 300 Segundos entonces indica que la causa es la falta de memoria y que se debe agregar más memoria al servidor

Databases
Transactions/Sec	Indica el número de transacciones que ocurren por sengundo en el servidor

General StatisticsCounters
User Connections	Número de conexiones hechas a SqlServer

Latches
Average Latch wait Time	Son objetos livianos de sincronización. No se mantienen durante la duración de la transacción. Típicamente se usan cuando se transfieren filas a memoria, mientras se contolan modificaciones a las filas, etc. Un valor alto puede indicar algún problema con el subsistema de Memoria

Locks

Average Wait Time(ms)
Lock Wait time(ms)
Lock waits/Sec

Los contadores relacionados a los bloqueos que mantiene SqlServer en un momento determinado de las transacciones. Las transacciones deben ser tan cortas como sea posible y por lo tanto debería mantener bloqueos la menor cantidad de tiempo posible para evitar para bloqueos a otros usaurios o procesos. Un Valor alto para cualquiera de estos contadores indica:

Presión sobre la memoria
Problemas con el Disco
Índices inadecuados
Diseño inadecuado de tablas u objetos de SqlServer
Inadecuada ubicación de los archivos de base de datos
Uso indebido de los niveles de aislamiento de SqlServer

Los mencionados, son algunos de los contadores preferidos, aparte de esos pueden revisarse estos contadores:

Object Name Counter Name
SQL Statistics Compilations/sec
SQL Statistics Recompilations/sec
SQL Statistics Batch Requests/sec
SQL Server: Buffer Manager Readahead pages/sec

Algunas Definiciones

Páginas Sucias: Páginas que han entrado al caché y han sido modificadas, pero aun no se han escrito a disco.

CheckPoints: Escribe todas las páginas sucias a disco. Crea un punto al cual se garantiza que todas las páginas han sido escritas a disco

Fill Factor: Factor por el cual crecen los archivos de base datos, cuando se espera una gran cantidad de nuevos registros en la base de datos se debe establecer un FillFactor alto con el fin que los datos no queden fragmentados y un FillFactor un poco menor en caso de ser utilizada en procesos de actualización y solo consulta

Lazy Writer: es un proceso que elimina conjuntos de buffers viejos y no usaados de memoria (Los buffers que tienen cambios deben ser escritos de nuevo al disco antes que el buffer pueda ser reusado para una página diferente).

Espero sea de ayuda

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo detectar los grupos de archivos que tiene cada tabla en la base de datos

2011-09-07T22:57:00Z

En el artículo en el artículo de optimización de consultas se menciona como uno de los procesos de optimización, la asignación de grupos de archivos a diferentes tables para utilizar mejor el hardware del servidor y hacer de las consultas un proceso mas veloz, para determinar cual son las tablas que son candidatas para pasarlas a otros grupos de archivos podemos usar:

select si.rows as 'ConteoFilas', SO.Name as 'Tabla', SI.name as 'Nombre Indice', SFG.groupname as 'Grupo de Archivos'
from sysobjects as SO join sysindexes as SI on SO.Id = SI.id join sysfilegroups as SFG on SI.GroupId = SFG.GroupId
order by si.rows desc, SO.Name , SI.name, SFG.GroupName

Las tablas que contengan un mayor número de registros son candidatas para hacer el paso a otro(s) grupos de archivo

select

Esto nos devuelte el numero de filas y nombre de la tabla y el grupo de archivos al cual pertenece, en versiones como Standard éste puede ser uno de los pasos para lograr más velocidad en tablas de hechos, tales como movimientos y registros paso a paso

si.rows as 'Rows', SO.Name as 'Table', SI.name as 'Index', SFG.groupname as 'Filegroup' from sysobjects as SO join sysindexes as SI on SO.Id = SI.id join sysfilegroups as SFG on SI.GroupId = SFG.GroupId
order by si.rows desc, SO.Name , SI.name, SFG.GroupName

Espero sea de ayuda

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo consultar los registros no asociados con otros registros de la misma tabla

2011-09-06T16:24:00Z

Detectar si un determinado usuario tiene o no autorización para realizar alguna tarea, detectar cuales de los ítems no ha sido asociados a él es tan solo algunas de las aplicaciones para éste artículo.

Como primera instancia tenemos una tabla de ejemplo, que se genera usando:

declare @t1 table (id int, VALOR CHAR(1))

insert into

@t1 values (1, 'A')
insert into @t1 values (2, 'B')
insert into @t1 values (3, 'C')
insert into @t1 values (4, 'D')
insert into @t1 values (5, 'E')

select

* from @t1

id	VALOR
1	A
2	B
3	C
4	D
5	E

Queremos saber por cada uno de los valores en la columna Id, cuales de los valores en la columna VALOR no están asociados con un Id

Es decir que para el Id 1, los valores deben ser B,C,D,E

La primera aproximación para saber cual de los VALORES está aplicado a cada uno de los Id es realizar un Cross Join

SELECT

* from @t1 t1 cross join @t1 t2

id	VALOR	id	VALOR
1	A	1	A
2	B	1	A
3	C	1	A
4	D	1	A
5	E	1	A
1	A	2	B
2	B	2	B
3	C	2	B
4	D	2	B
5	E	2	B
1	A	3	C
2	B	3	C
3	C	3	C
4	D	3	C
5	E	3	C
1	A	4	D
2	B	4	D
3	C	4	D
4	D	4	D
5	E	4	D
1	A	5	E
2	B	5	E
3	C	5	E
4	D	5	E
5	E	5	E

Se nota en ésta combinación que 1 ya está asociado con A en la fila y notamos que en las columnas vemos que los valos con 1 no están asociados

Por lo que si creamos un condición podemos eliminar los que ya están asociados dejando solo los que NO están asociados

SELECT t2.id, t1.VALOR from @t1 t1 cross join @t1 t2 where not (t1.id = t2.id and t2.VALOR = t2.VALOR )

id	VALOR
1	B
1	C
1	D
1	E
2	A
2	C
2	D
2	E
3	A
3	B
3	D
3	E
4	A
4	B
4	C
4	E
5	A
5	B
5	C
5	D

Espero sea de ayuda

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Como crear el esquema de un archivo Raw SSIS

2011-08-26T16:45:00Z

Importar grandes volúmenes de datos puede tomar mucho tiempo en SSIS si no se hace de la manera apropiada, y una de las principales técnicas para lograr este propósito es el uso de archivos Raw (Crudos), los cuales son archivos binarios de altísimo rendimiento que por sus características permiten leer y escribir datos a alta velocidad haciéndolos ideales para ser el paso intermedio entre el origen y el destino de los datos (Stage).

Para usarlos, sin embargo, debe crearse primero un archivo vacío que tenga el esquema de datos que vamos a utilizar posteriormente en nuestra solución, así que presento los pasos para crear este esquema usando un paquete que apoya dicha tarea:

Crear un nuevo paquete
Agregar un Data Flow
Agrear un Origen de Datos Ole Db
Crear una consulta para el origen Ole Db que devuelva cero registros (top 0) y que tenga los registros que deseamos incluir en el esquema del archivo raw
select top 0
cast(null as int) as Id,
cast(null as varchar(50)) as Value
Si es necesario, Agregar una transformación DataConversion para establecer el tipo de datos desedo para cada columna
Agregar un Destino de Archivo Raw y seleccionar las columnas que se quieren en el archivo Raw
Ejecutar el Paquete
Usar el Archivo Raw

Adjunto se encuentra un paquete de ejemplo para realizar ésta tarea

Espero sea de ayuda

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo Obtener los últimos Identity (Autonumérico) Insertados

2011-06-10T19:41:00Z

Siempre buscando la optimización de consultas debemos intentar siempre el uso de operaciones de conjunto sobre las operaciones fila a fila, éste es uno de los ejemplos en donde usando T-Sql podemos obtener los últimos IDs generados a partir de la inserción en una las tablas que tienen Identity,

DECLARE @output TABLE (id int) --Tabla para capturar los IDs generados

Insert into A (fname, lname) -- Tabla en la que insertaremos los datos
OUTPUT inserted.ID INTO @output --Captura de los IDs generados con el uso de la palabra clave OUTPUT
SELECT fname, lname FROM B -- Consulta que trae los datos a insertar

select * from @output -- Presentando resultados (opcional)

Mediante el uso de ésta técnica podemos aumentar notablemente la velocidad de nuestra consultas y procedimientos almacenados

Espero sea de ayuda

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Optimización de Consultas Parte 3 - Recompilación de procedimientos Almacenados

2011-01-17T23:08:00Z

Cada vez que se ejecuta un procedimiento almacenado en SqlServer por primera vez, se optimiza y su plan de ejecución se compila y se almacena en el caché de SqlServer. Cada vez que el procedimiento almacenado se ejecuta luego de ser almacenado en el caché, usará el mismo plan de ejecución, eliminando la necesidad de compilar y de almacenar el plan de ejecución de dicho procedimiento cada vez que se ejecuta. Si éste procedimiento se ejecuta 1000 o más veces por día, los recursos de hardware se optimizan al ahorrarse dichos pasos.

Si los parámetros dentro del procedimiento almacenado son idénticos en el Where, entonces reusar el plan de ejecución tiene mucho sentido. Pero qué pasa si los valores de los parámetros en cada ejecución cambian?, que pasa si el tamaño del resultado varia notablemente (Columnas y Filas), que sucede si para unos parámetros la búsqueda óptima es una búsqueda por índice, pero para otros valores el mejor camino es un table scan?

Todo eso depende de que tan genéricos sean los parámetros, de que tan bien esté definida la funcionalidad del procedimiento almacenado si están definidos de tal manera que no recarguemos su funcionalidad y que los resultados sean parecidos aun los valores de los parámetros sean diferentes el procedimiento ejecutará de manera óptima y obtendrá todos los beneficios mencionados anteriormente. Pero para el caso contrario, reutilizar el plan de ejecución puede no ser lo óptimo, como resultado la consulta se ejecutará más lentamente que si se creara dinámicamente su plan de ejecución.

En la mayoría de los casos, no es una cuestión de la cual preocuparse, pero si se hace la pregunta, por qué mi procedimiento almacenado ejecuta bien en el diseñador de consultas pero toma mucho tiempo en producción, éste puede ser el caso. Siempre teniendo en cuenta que el diseño del procedimiento almacenado, junto con granularidad, ofrecen la mejor estrategia para diseñarlos y claro para mantenerlos

Cómo solucionar el problema:

1. Aplicar recompilación a todos los procedimientos periódicamente
Ejecutar con el procedimiento almacenado del sistema sp_recompile sobre todos los procedimientos almacenados de la base de datos (Cada dos o tres meses)

2. Crear los procedimientos almacenados que consideremos que sean candidatos para sufrir este problema con la opción With Recompile, teniendo en cuenta que cada vez que ejecutemos el procedimiento almacenado el mismo se recompilará (se generará un nuevo plan de ejecución)
CREATE PROCEDURE MiProcedimiento
WITH RECOMPILE
AS
....

3. Si el caso en donde los resultados cambian demasiado dependiendo de valores específicos y plenamente identificados podemos ejecutarlo usando la opción with recopile de exec:
EXECUTE MiProcedimiento @p2 = 'A' WITH RECOMPILE;
GO

4. Query Hint RECOMPILE
A partir de SqlServer 2005 se introdujo RECOMPILE como query hint, lo que logra este hint es que el analizador de consulta descarte cualquier plan de ejecución previamente generado para esa consulta dentro del procedimiento almacenado y recompile sólo esta consulta

5. Creando Guías de Plan de Ejecución
Permite realizar cambios administrativos sobre procedimientos almacenados que ya han sido creados en la base de datos que no se puede o no se quiere modificar para agregar algún tipo de optimización como la mencionada. Esto se logra forzando hints sobre las consultas o fijando un plan de ejecución diferente a dicho procedimiento o consulta.

La creación de guías de plan es una opción avanzada de administración es recomendado leer detenidamente la documentación sobre el tema y luego observar los casos en donde sea prudente aplicarlo
Debemos usar éste tipo de solución cautelosamente dado que usar recompile, aunque nos aseguramos que cada vez el plan de ejecución generado es correcto, hace que perdamos los muy buenos beneficios de tener un plan de ejecución almacenado para el procedimiento y nos guía hacia un mejor diseño de los procedimientos almacenados que incluiremos en nuestros sistemas

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Info] Optimización de Consultas Parte 1 - Generalidades

2011-01-17T15:54:00Z

En la siguientegráfica se presenta el esquema de ejecución de consultas en SqlServer

From y Join
1. Usar Hints
Podemos utilizar hints de consultas tales como (nolock) sobre las tablas que queremos consultar para cambiar el modo de acceso de readcommited a readuncommited --ADVERTENCIA: Utilizar este hint puede traer problemas de datos, tales como lecturas sucias o registros fantasma.

El uso de hints debe realizarse con especial cuidado y teniendo en mente las consecuencias de los mismos, asimismo, el optimizador de consultas podrá fácilmente encontrar el mejor camino para retornar la consulta dado que utiliza información estadística, caché, entre otros elementos para devolver los resultados.

2. Otra técnica que podemos usar antes del Where es poner las condiciones del where (cuando sean aplicables) dentro del mismo Join, es decir:
select a.Id, from tabla1 a inner join tabla2 b on a.id = b.id where b.cantidad > 50

puede ser reescrito como:

select a.Id, from tabla1 a inner join tabla2 b on a.id = b.id and b.cantidad > 50

Esto aplica especialmente para sqlServer 2005 y versiones anteriores, el analizador de SqlServer 2008 se vale de su algoritmia avanzada para determinar cuál es el mejor camino entre estas dos opciones.

3. Favorecer la lógica y operaciones por Conjuntos
Las operaciones por conjuntos implican la realización de operaciones por bloques, para lo cual Sql está optimizado:

3.1. Evitar el uso de funciones escalares en las consultas para cada campo (en cualquier parte de la consulta), dado que la ejecución se realiza una por una, lo cual no favorece las operaciones por bloques.
3.2. Evitar al MAXIMO el uso de cursores.
3.3. Estimular el uso de funciones de usuario que devuelven tablas, pues se calculan primero y luego se aplican a la consulta favoreciendo operaciones de conjuntos.
3.4. Asimismo, estimular el uso de expresiones CTE:

Permite la reutilización de consultas, usando el join, de esta manera consultamos dos (2) veces la misma tabla
select a.Id from tabla1 ainner join tabla2 b on a.Tipo = B.IdTIpo
inner join tabla2 c on a.Tipo2 = C.IdTIpo

con CTE, solo una vez:

declare @tabla1 table (Id int, TipoA int, TipoB int) --Solo por ejemplo
declare @tabla2 table (IdTipo int) --Solo por ejemplo
;with Tabla(Id)
as(
select IdTipo from @tabla2
)
select * from @tabla1 a inner join Tabla
on a.TipoA = tabla.id
inner join @tabla1 b on b.TipoB = tabla.id

Where

1. Escribir las expresiones lógicas de menos completa a más compleja, es decir, es mejor:
select a.Id, from tabla1 a inner join tabla2 b on a.id = b.id and b.cantidad > 50 and nombre like '%abc%'

que usar:

select a.Id, from tabla1 a inner join tabla2 b on a.id = b.id a nombre like '%abc%' and b.cantidad > 50

Esto se debe a la evaluación de la lógica tipo corto circuito

Select
1. Evitar el uso de funciones escalares por cada campo.
2. Usar Case puede ser una buena alternativa para devolver resultados directamente al usuario, usarlos con precaución, si es posible, reemplazarlos con case del lado del cliente (Reporting Services, por ejemplo).

Order By
1. Evitar al MÁXIMO los ordenamientos innecesarios dentro de las consultas, si se observan los planes de ejecución de las consultas se encuentra que es una de las cosas que más consume buena parte del tiempo de ejecución de la consulta; se recomienda ordenar, en la mayoría de los casos, del lado del cliente.

Result Set
1. Evitar al MÁXIMO el uso del select *, limitar la cardinalidad del resultado (tiene como resultado) permite, genera, mejora el uso de...? mejoras en el uso de memoria y latencia de red, dado que los resultados son sólo aquellos que necesitamos, a la vez que evitamos que el analizador de consultas deba determinar dinámicamente qué campos usar en el conjunto de resultados.

Grouping and Aggregation
Una vez finalizada la etapa de la consulta en donde se obtienen los resultados iniciales, el motor procede a realizar el agrupamiento y agregación de los resultados (agregación: aplicar una función a un grupo)
Debemos evitar al máximo realizar Agrupaciones y Agregaciones del lado de la base de datos, dado que son operaciones costosas, esto es especialmente cierto para operaciones de reportes.

Having
Sólo debe ser aplicado para filtrar sobre las funciones de agregación, NO debe ser utilizado para filtrar las filas de la consulta, lo cual se debe incluir en el Where de la consulta.

OTRAS RECOMENDACIONES
Usar CLR
Programación .Net para para crear procedimientos almacenados, funciones de usuario y agregaciones, etc. Usarlo cuando se requiera realizar cálculos avanzados del lado de la base de datos, en ningún momento debe usarse como reemplazo para las operaciones a acceso a datos.

Particionamiento de Datos
Crear grupos de archivos en diferentes discos y ubicar en ellos las tablas de mayor uso, lo cual favorece las operaciones INNER JOIN (Versión inferior a la enterprise)

Particionamiento de Vertical de Filas
Crear particionamiento de filas usando funciones de particionamiento para ubicar asimismo las filas en diferentes grupos de archivos y optimizar el acceso a los datos en las operaciones JOIN (versión Enterprise)

Dividir las consultas en consultas más pequeñas
Usando variables tipo tabla podemos realizar consultas preliminares sobre algunas de las tablas que participan en las operaciones JOIN o LEFT JOIN, de esta manera cargaremos a memoria (luego de cierto límite de filas las variables tipo tabla también pasan a disco, por lo que debemos ubicar en ellas solo las tablas que relativamente tengan pocos registros: menos de 100,000) de manera anticipada, permitiéndonos la reutilización de las mismas, con la ventaja que cuando salen de ámbito se destruyen automáticamente (no hay que realizar un drop como a las tablas temporales).

En próximos artículos extenderé estos y otros temas

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Optimización de Consultas Parte 2 - Anti Semi joins

2011-01-17T15:13:00Z

Como todos los temas de optimización las soluciones presentadas en éste artículo deben probarse, intentar varias alternativas y luego decidir dependiendo de los resultados y especialmente de la evolución de los datos, es decir, dependiendo del momento del ciclo de vida de la base de datos tendremos cierto uso de la información (por ejemplo durante procesos de migración) y luego tendremos otro tipo (en Producción, cuando cierto tipo de producto salga de circulación, etc.)

El proceso de optimización y mantenimiento es algo continuo y debe estar acorde a las necesidades y cambios del negocio

Pimero, qué es un SEMI JOIN

SELECT     HumanResources.Employee.BusinessEntityID, MyEmployees.EmployeeID
FROM         HumanResources.Employee LEFT OUTER JOIN
                      MyEmployees ON HumanResources.Employee.BusinessEntityID = MyEmployees.EmployeeID

Es cuando queremos hacer un left (o Right join) con otra(s) tabla(s) y queremos ver qué registros figuran en la primera tabla (HumanResources.Employee) y cuales en la segunda(MyEmployees)

Si en la segunda tabla no existe dicho registro se presentará NULL como se vé en la gráfica

Ahora, Definamos un ANTI SEMI JOIN

Queremos encontrar los registros que estén en la primera tabla (HumanResources.Employee) que NO estén en la segunda tabla (MyEmployee)

Ésta consulta cumple su cometido pero analicemos a fondo su funcionamiento, realiza el Left Join que mas pesado que realizar un inner join normal y filtra los resultados para la columna null

Ahora, qué ALTERNATIVA tenemos para éste caso:

Usaremos la función Exists

SELECT a.BusinessEntityID
FROM HumanResources.Employee a
where not exists(select EmployeeID from MyEmployees b where a.BusinessEntityID = b.EmployeeID)

Observemos la comparación entre el primer método y la alternativa:

El costo relativo para el batch es el mismo, y aparentemente tienen el mismo costo, pero observemos 2 cosas interesantes sobre éste plan de ejecución, primero es que de la primera manera tenemos un filtro, es de recordar que los ordenamientos, las agrupaciones y los filtros son las operaciones más costosas dentro de una consulta; en la segunda parte ejecutando con Not Exists, vemos que el analizador identifica que es un Left Anti Semi Join, por lo cual automáticamente aplica las optimizaciones para este caso y luego realiza las operaciones adicionales de la consulta

Usando repetidamente ésta técnica, especialmente para consultas que impliquen muchos registros puede aumentar el rendimiento de la misma a la vez que permite una facil escritura y mantenimiento e incluso control sobre la consulta a realizar

FREDDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Crear una tabla a partir de un texto separado por comas (CSV)

2011-01-03T22:24:00Z

Ahora con la aparición de los parámetros tipo tabla en SqlServer, se elimina la necesidad de crear parametros complejos o de trucos para enviar información hacia los procedimientos almacenados, pero para quienes no quieren enviar esta información, crear los tipos de datos que se requieren para recibir los parámetros tipo tabla, presento una alternativa para ello en varias aproximaciones

Función de usuario para la generación de Tablas

--USO: select * from fnCSV2Table('1,2,3,4,5,6,7,8',default)
--Convierte una CSV a una tabla, lo que facilita las consultas 
--Reduce la necesidad de consultas dinámicas dentro de los procedimientos almacenados
create function fnCSV2Table(@CSV varchar(max), @Separator char(1) = ',')
returns @list table (data int)
as
Begin
	declare @SecondIndex int
	declare @FirtsIndex int
	
	set @SecondIndex = charindex(@Separator,@CSV)
	set @FirtsIndex = 0
	
	if @SecondIndex > 0 set @CSV = @CSV + @Separator
	
	while (@SecondIndex > 0)
	Begin
		insert into @list values(substring(@CSV,@FirtsIndex,@SecondIndex - @FirtsIndex))
		set @FirtsIndex = @SecondIndex + 1
		set @SecondIndex = charindex(@Separator,@CSV,@FirtsIndex)
	End
	if @FirtsIndex =0 and len(@CSV)>0 
		insert into @list values(@CSV)
	return
End
go

--USO: select * from fnConvertCSV2TextTable('1,2,3,4,5,6,7,8',default)
--Convierte una CSV a una tabla (tipo texto), lo que facilita las consultas 
--Reduce la necesidad de consultas dinámicas dentro de los procedimientos almacenados
create function fnConvertCSV2TextTable(@CSV varchar(max), @Separator char(1) = ',')
returns @list table (data varchar(8000))
with encryption
as
Begin
	declare @SecondIndex int
	declare @FirtsIndex int
	
	set @SecondIndex = charindex(@Separator,@CSV)
	set @FirtsIndex = 0
	
	if @SecondIndex > 0 set @CSV = @CSV + @Separator
	
	while (@SecondIndex > 0)
	Begin
		insert into @list values(substring(@CSV,@FirtsIndex,@SecondIndex - @FirtsIndex))
		set @FirtsIndex = @SecondIndex + 1
		set @SecondIndex = charindex(@Separator,@CSV,@FirtsIndex)
	End	
	if @FirtsIndex =0 and len(@CSV)>0 
		insert into @list values(@CSV)
	return
End
go

Es posible que no sea la aproximación mas óptimizada desde el punto de vista programación T-Sql, pero si es seguro, soluciona el problema de una manera simple y facil de administrar

Cómo Usuarla

Una vez convertida a tabla podemos usarla en un join para filtrar los datos que queremos consultar, podemos usar Cross Apply para aplicar a cada registro la función, etc., la idea es generar una tabla y poderla utilizar facilmente dentro de nuestro código

FREDDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo generar N Filas Rápido Sql Server

2010-12-22T14:35:00Z

Como siempre, una de las cosas que mas se necesita al crear un procedimiento es generar un número de determinado de filas para poderlas combinar con alguna otra tabla, ésta técnica presenta cómo crear una tabla que cuente de un número a otro, en incrementos de mánera eficiente

LA PRIMERA APROXIMACIÓN

declare @StartNumber int, @Nrows int, @increment int


set @StartNumber = 1
set @Nrows  = 1000000
set @increment = 1


----
declare @q table  (i int)
declare @index int

set @index = @StartNumber 

while @index < @Nrows 
Begin
	insert into @q values(@index)
	set @index = @index + @increment 
End

select i from @q

En éste punto, se genera el resultado y tiene los parámetros que se podrían esperar: el Inicio, El número de filas y el Incremento, pero al ejecutarlo con un millón de filas, toma (en mi computador) 25 segundos, lo cual es muy lento, sin emabrgo ésta aproximación tendríaa el límite de registros del tipo de datos que usemos como parámetro, es decir, se puede llegar hasta el máximo que permite un entero, o el máximo que soporte un bigint, lo cual es bastante grande, pero mucho mas lento al hacerlo uno por uno. Ahora vamos a investigar otra alternativa que nos permitirá generar los registros mucho más rápido usando otras técnicas

SEGUNDA APROXIMACIÓN

declare @StartNumber int, @NumberOfRows int, @Increment int

set @StartNumber = 1
set @NumberOfRows = 1000000
set @Increment = 1

declare @Rows table (i int)

Declare @NumGen Table (Num int)	
Declare @cnt int	
Set @cnt = @StartNumber
While @cnt <= 100 
Begin		
	Insert Into @NumGen values (@cnt)
	Set @cnt = @cnt + @Increment
End	
set @StartNumber = @StartNumber - @Increment 

insert into @Rows
Select @StartNumber + RowNum	From 	
(
Select Row_Number() Over (Order By N1.Num) As RowNum
From @NumGen N1, @NumGen N2, @NumGen N3, @NumGen N4, @NumGen N5, @NumGen N6, @NumGen N7, @NumGen N8, @NumGen N9, @NumGen N10) RowNums	
Where RowNum <= @NumberOfRows

select * from @Rows

Lo que sucede es que estamos haciendo una generación inicial de una tabla hasta 100 normalmente (también porque para casos en donde la tabla a generar es mas corta) y luego multiplicando por conjuntos éste esfuerzo con el fin de no generar una a una las filas sino por bloques de a 100 filas y en éste caso 10 bloques de a 100 filas, al hacer ésta combinación aplicamos una combinación lo cual nos da un máximo de filas a generar de 100^10, lo cual suficiente en la mayoría de los casos.

Usando nuestra solución podemos generar la función con la firma:

alter function GenTable(@StartNumber int, @NumberOfRows int, @Increment int = 1)

Y podemos utilizarlo para crear un ejemplo: Imaginemos que queremos agregar hora a hora las siguientes 12 horas a las fechas de actividad de un cliente, cómo se podría hacer?

EJEMPLO DE USO

--Tabla que proviene de la Base de Datos simulada--
declare @fechas table (IdCliente int, Fecha datetime)

insert into @fechas values 
(1, '2001-01-20 08:23:20'), 
(2, '2001-02-24 18:23:20'), 
(3, '2001-03-23 12:23:20')
--Fin Tabla que proviene de la Base de Datos simulada--

select * from @fechas 

insert into @fechas 
select IdCliente, DATEADD(hh,ids.i,fecha) as FechasHoras  from @fechas 
cross join (select i from dbo.GenTable(1,12,default)) Ids
order by FechasHoras

select * from @fechas 
order by Fecha

Aplicamos cada registro generado a cada una de las filas (CrossJoin) que provienen de la tabla y luego usamos la función DateAdd para hacer el trabajo fila a fila, sin usar cursores, sin necesidad de recorrer la variable tipo tabla y como siempre favoreciendo las operacione de conjunto sobre las operaciones realizadas fila a fila

Por supuesto exiiten otras alternattivas se puede generar usando CTE, la cuestión es la bandera MAXRECURSION, la cual limita el rango de acción de la solución planteada a cierto número de registos (depende de la implementación) Y claro la MAS recomendada y eficiente de todas que es usar CLR, especial para este tipo de casos Los invito a probar las diferentes alternativas y enviar sus comentarios sobre el tema

FREDDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo calcular un total acumulado Rápido SIN Cursores

2010-10-28T20:36:00Z

Una de las cosas que nos lleva a usar cursores es el cálculo de totales acumulados, así que usando una de las caraterísticas no muy documentadas de SQL podemos realizar éste trabajo con poco esfuerzo y de una manera rápida y efectiva

--DBCC DROPCLEANBUFFERS -- Limpia caché de datos SOLO SE UTILIZA PARA COMPARAR CON OTRAS IMPLEMENTACIONES ELIMNAR EN AMBIENTE DE PRODUCCIÓN

--DBCC FREEPROCCACHE -- Limipia el caché de procedimientos SOLO SE UTILIZA PARA COMPARAR CON OTRAS IMPLEMENTACIONES ELIMNAR EN AMBIENTE DE PRODUCCIÓN

declare @runningtotal decimal(17,2)

set @runningtotal = 0

update TablaValores

set @runningtotal = ValorAcumulado = @runningtotal + ValorFila

Espero sea de ayuda

FREDDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP