Freddy Angarita

[Code] Archivo de configuración de Ejemplo para Instalaciones Desatendidas de SqlServer en Windows Server 2012 Core

Freddy Angarita — Thu, 04 Apr 2013 20:13:00 GMT

En Sql Server es permitido realizar instalaciones desatendidas, esto se puede realizar de dos maneras, la primera es usando la linea de comando, en donde se pueden enviar parámetros al ejecutable Setup.exe ubicado en los discos de instalación de SqlServer

Como Archivo adjunto, se encuentra un archivo de configuración de ejemplo para ser usado para realizar una instalación con todas las características (Qué se permiten para WindowsServer Core)

Este Artículo es complementario de [How to] Cómo realizar una instalación de SqlServer 2012 en Windows 2012 Core

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[How to] Cómo realizar una instalación de SqlServer 2012 en Windows 2012 Core

Freddy Angarita — Thu, 04 Apr 2013 20:04:00 GMT

Para realizar la prueba se requiere una máquina (física o virtual) para realizar la prueba, si no se cuenta con una licencia se puede descargar una versión de prueba en la página de Microsoft

Para el ejemplo usaré Hyper-V en una máquina con Windows 8 y SqlServer 2012 Developer Edition (descargar versión de prueba), aunque funciona para todas las versiones

Pero antes de empezar qué es Windows Server Core, para ponerlo en términos simples, es una versión que no tiene GUI, es decir, interfase gráfica con el usuario (Algunos Programas como el bloc de notas sí están disponibles) reduciendo así la cantidad de recursos requeridos por el sistema operativo para funcionar , lo que implica que la administración del sistema operativo debe realizarse enteramente mediante consola, claro, para SqlServer, existe la posiblidad de conectar remotamente un Management Studio lo que permitirá su administración gráfica

Una vez instalada o conectada la máquina virtual Windows Server 2012 Code Edition presenta una consola como la que se presenta en la siguiente imagen:

En el Menú de Hyper-V, Seleccionar Media => DVD Drive => Insert Disk... y seleccionar el Archivo .ISO del instalador de SqlServer, para este ejemplo usaré Developer Edition, pero para un servidor se debe usar Standar o Enterprise dependiendo del esquema de licencimiento con el que se cuente

Una vez se conecte el Disco, el mismo aparecerá como un DVD en la máquina virtual

Una vez preparado el archivo de configuración, se procede a la ejecución de la instrucción que inicia la instalación usando el archivo de configuración de ejemplo ubicado en [Code] Archivo de configuración de Ejemplo para Instalaciones Desatendidas de SqlServer en Windows Server 2012 Core, el cual contiene comentarios que explican las diferentes configuraciones realizadas en el archivo

Una vez se inicia la instalación correctamente se visualiza en pantalla el diálogo presentado en la siguiente gráfica:

Dado que las opciones de configuración ya fueron dadas en el archivo de configuración el instalador no realiza ninguna pregunta al usuario, y los valores no provistos serán tomados por defecto por instalador, para realizar una instalación completa se recomienda revisar todas las opciones de instalación; en la siguiente gráfica se observa el avance de la instalación:

Una vez Finalizada la instalación:

Para habilitar las conexiones remotas se debe ejecutar

EXEC sys.sp_configure N'remote access', N'1' 

GO 

RECONFIGURE WITH OVERRIDE 

GO

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Detección de Cambios - Parte1

Freddy Angarita — Sat, 19 Jan 2013 18:23:00 GMT

Detectar cambios en una base de datos es muy importante para el desarrollo de soluciones de inteligencia de negocios y el mantenimiento de los warehouses asociados, por lo que realizarlo de manera eficiente y fácil de mantener es de vital importancia para permitir un desarrollo y sostenibilidad del proyecto en el tiempo

En este especial de detección de cambios se presentarán tres diferentes técnicas usables en diferentes escenarios:

Dimensión de cambio lento en SSIS
Merge Join
Técnica especial de detección de cambios SSIS

En este primer artículo discutiremos el uso de la transformación de cambio lento de SSIS, esta transformación

Para este artículo se usará lla base de datos AdventureWorksDW2012

1El primer paso, será crear una tabla de prueba en donde realizaremos la detección del cambio:

CREATE TABLE [dbo].[TablaPrueba](
	[ProductKey] [int] IDENTITY(1,1) NOT NULL,
	[ProductAlternateKey] [nvarchar](25) NULL,
	[ProductSubcategoryKey] [int] NULL,
	[WeightUnitMeasureCode] [nchar](3) NULL,
	[SizeUnitMeasureCode] [nchar](3) NULL,
	[EnglishProductName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO

Luego se crea (o sea agrega un paquete para realizar la detección de cambios)

Luego se agrega un DataFlowTask

Dentro del DataFlowTask

Se agrega un origen de datos Ole DB con la siguiente consulta:

SELECT [ProductKey]
      ,[ProductAlternateKey]
      ,[ProductSubcategoryKey]
      ,[WeightUnitMeasureCode]
      ,[SizeUnitMeasureCode]
      ,[EnglishProductName] 
  FROM [DimProduct]

Luego se agrega una transformación de cambio lento (Slowly Changing Dimension)

Luego de conectar el origen de datos con la transformación de cambio lento se edita la transformación

Configuración de Datos

En este punto se selecciona la conexión con la base de datos con la que se desea trabajar y la tabla en la que se desean ubicar los cambios

También, se selecciona la llave de negocio (para este caso se selecciona ProductAlternateKey) con la que se va a realizar la comparación (se puede pensar como un JOIN)

El objeto es identificar:

Si no existe la llave en la tabla destino (para este caso Pruebas), crear el registro
Si existe la llave en la tabla destino, actualizar las columnas configuradas
Si existe en la tabla destino y no en la tabla de origen debe ser eliminado el registro en la tabla destino

Configucíon el tipo de atributo de cada columna

Tipo Configuración	Descripción	Uso
Fixed Attribute	Attributo Fijo, si cambia se presenta un error	Se usa para validar que valores no cambien y que se presente un error en tal caso
Changing Attribute	Atributo Cambiante, si cambia se actualiza	Se usa para actualizar en la tabla destino los cambios encontrados al comprarla con la tabla destino
Historical Attribute	Atributo histórico, guarda los cambios en nuevas filas	Permite llevar un historial de cambios realizados en la tabla origen en la tabla destino

Configuración de atributos fijos y cambiantes

Si se han seleccionado atributos fijos se podrá seleccionar la opción para que la transformación falle en caso que se detecten cambios
Si se han seleccionado atributos cambiantes se puede activar la opción para que realice cambios en todos los registros que se encuentren (eso es importante dado que Merge, el cual se estudiará en la parte 2) no permite este comportamiento

Configuración de Inferencia de Miembros de Dimensión

En caso que se esté haciendo la actualización de un Warehouse y alguno de los campos haga referencia a una dimensión aún no cargada, el sistema infiere este elemento y lo agrega (como si existiera en la tabla relacionada); esta práctica no es recomendada ya que puede llevar a incosistencias relacionales

FInalización Asistente

Presenta un resumen con las opciones seleccionadas

Visualización paquete

Ejecución del paquete

Luego de modificar una fila en la tabla origen

Éste método tiene varias ventajas, pero una de sus desventajas es que la actualización- inserción - borrado se realizan registro a registro lo que no ofrece alto desempeño

Para casos en donde la cantidad de registros es poca ésta tecnica puede ser favorable dada su fácil aplicación

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Sample] Cómo crear un reporte flexible usando Jerarquías (Recursivo)

Freddy Angarita — Wed, 12 Dec 2012 01:05:00 GMT

Saludos, A continuación publico el archivo de ejemplo del artículo [Code] Cómo crear un reporte flexible usando Jerarquías (Recursivo)

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo crear un reporte flexible usando Jerarquías (Recursivo)

Freddy Angarita — Wed, 12 Dec 2012 00:11:00 GMT

Saludos

Dentro del desarrollo de un reporte se presentan varios retos, uno de los mas recurrentes es crear un reporte flexible, que se acomode a los cambios en los datos sin necesidad de modificar el reporte, en el presente artículo presento una característica muy interesante de Reporting Services

Consideremos primero la siguiente Jerarquía:

Basado en el esquema anterior generemos una tabla de ejemplo

select 1 as Id, null as Padre, 'Sede Central' as Nombre, 0 as Valor union all
select 2 as Id, 1 as Padre, 'Estado' as Nombre, 0 as Valor  union all
select 3 as Id, 2 as Padre, 'Ciudad1' as Nombre, 0 as Valor  union all
select 4 as Id, 2 as Padre, 'Ciudad2' as Nombre, 0 as Valor  union all
select 5 as Id, 3 as Padre, 'Sede Centro' as Nombre, 11 as Valor  union all
select 6 as Id, 3 as Padre, 'Sede Sur' as Nombre, 12 as Valor  union all
select 7 as Id, 4 as Padre, 'Sede Norte' as Nombre, 15 as Valor  union all
select 8 as Id, 4 as Padre, 'Sede Occidente' as Nombre , 10 as Valor

Creando el Reporte

1) Se agrega un DataSet con los datos de ejemplo

2) En la parte de presentación, para el presente ejemplo, se selecciona siguiente

3) Para el presente ejemplo seleccionamos el estilo por defecto

4) Una vez finalizado el asitente para la creación de una tabla, editamos el grupo del reporte (recordemos que tenemos un solo grupo para el reporte que se está creando), Se debe agrupar por la columna Id dado que esta presenta un valor único y es parte de la llave para poder lograr recursividad en el reporte

5) En la misma ventana, se selecciona Avanzado (Advanced) y se estable como Padre Recursivo (Recursive Parent) al campo Padre el cual contiene la información sobre cual de los nodos es el padre en la Jerarquía

6) Si se ejecuta el reporte no se obtiene un resultado muy interesante hasta el momento

7) Pero si se usa la función Level() se podrá detectar el nivel de la jerarquía de la fila actual lo que es útil para generar una indentación que ayude a la comprensión de los datos

En la propiedad Padding=>Left del campo Nombre se agrega el siguiente código

=CStr(2 + (Level()*20)) + "pt"

Lo que crea una indentación como la presentada en la imagen

8) Si se desea agregar un esquema de colores al reporte para seguir algún tipo de convensión se puede usar una paleta de colores, como la del siguiente ejemplo

Para lograr aplicar la paleta seleccionada se agrega código al reporte:

El resultado Final:

Claro, un diseñador gráfico logrará un mejor resultado con la paleta de colores pero para este caso es netamente ilustrativo

El archivo del reporte de ejemplo se puede encontrar en: [Sample] Cómo crear un reporte flexible usando Jerarquías (Recursivo)

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[Code] Cómo obtener las columnas de una tabla identificando su llave primaria

Freddy Angarita — Tue, 20 Nov 2012 18:44:00 GMT

Se presenta el script para obtener todos los campos de una tabla y saber cual(es) de ellos son llave primaria

declare @tabla sysname 
set @tabla = 'mitabla' --El nombre de la tabla que se quiere evaluar

;With PrimaryKeys (object_id, column_id) as (
SELECT ic.object_id, ic.column_id AS ColumnName FROM 
sys.indexes AS i 
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id and ic.OBJECT_ID = OBJECT_ID(@tabla,N'U')
)
select name, case when PrimaryKeys.object_id is null then 0 else 1 end IsKey from sys.columns AS C
left join PrimaryKeys ON c.object_id = PrimaryKeys.object_id and c.column_id = PrimaryKeys.column_id
where c.OBJECT_ID = OBJECT_ID(@tabla,N'U')

Obsérvese que se usa la función OBJECT_ID para obtener el Id del Objeto que se quiere evaluar, otra opción sería:

OBJECT_NAME(ic.OBJECT_ID)  = @Tabla

pero es mas eficiente como se presenta en la consulta superior dado que sólo se ejecuta una vez la función escalar OBJECT_ID en lugar de una vez por cada registro, se observa también el uso de una instrucción CTE para calcular las llaves de la tabla

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[CODE] Cómo concatenar los resultados de una consulta

Freddy Angarita — Tue, 20 Nov 2012 17:50:00 GMT

Como se observa en el artículo [CODE] Cómo obtener la llave primaria de una tabla, es posible concatenar el resultado de una consulta en una variable, lo cual es especialmente útil en la generación de código dinámico:

declare @key varchar(8000)
set @key = ''
SELECT @key  = @key  + COALESCE(TABLE_NAME + ',' , '')  from INFORMATION_SCHEMA.TABLES

if len(@key) > 0 set @key = LEFT(@key,len(@key) - 1) --Eliminar la coma que sobra al final

print @key

Se debe tener en cuenta que existen algunas limintantes, por ejemplo se debe considerar el tamaño máximo de la variable, que dependiendo de la versión de SqlServer puede ser 8000 y usando varchar(max) puede ser máximo de 2,147,483,647 caracteres

Es de notar que usar funciones CLR o funciones de texto avanzadas de SqlServer 2012 puede mejorarse el rendimiento de esta solución

Los comentarios son bienvenidos, espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[CODE] Cómo obtener la llave primaria de una tabla

Freddy Angarita — Tue, 20 Nov 2012 17:35:00 GMT

Obtener la llave primaria de una tabla

Normalmente y una de las primeras (además de ser la forma en que funciona para todas las versiones), dado que usa las consultas estándard INFORMATION_SCHEMA, es la que se presenta a continuación:

declare @Keys varchar(8000)

set @Keys = '' --Se estable a cadena vacía porque si deja en nulo el resultado final sería nulo

SELECT @Keys = @Keys + COALESCE(isc.COLUMN_NAME + ',' , '') FROM 
INFORMATION_SCHEMA.COLUMNS AS isc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
AS kcs ON isc.TABLE_NAME = kcs.TABLE_NAME AND isc.COLUMN_NAME = kcs.COLUMN_NAME 
AND LEFT(kcs.CONSTRAINT_NAME, 2) = 'PK'

print @Keys --Presenta las llaves separadas por coma

Otra consulta, en la que se usa la metadata de la base de datos es: (y la recomendada):

SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id and i.is_primary_key = 1

El uso de la primera o la segunda estará determinado por la versión de sql y qué tan estándard es la solución que se esté desarrollando

Espero sea de ayuda,

FREDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP

[PPT] Presentación Novedades SqlServer 2012 BI

Freddy Angarita — Wed, 25 Apr 2012 20:21:00 GMT

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

Freddy Angarita — Tue, 10 Apr 2012 16:32:00 GMT

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)

Freddy Angarita — Wed, 18 Jan 2012 02:16:00 GMT

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

Freddy Angarita — Wed, 18 Jan 2012 02:02:00 GMT

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

Freddy Angarita — Sun, 08 Jan 2012 16:34:00 GMT

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

Freddy Angarita — Fri, 06 Jan 2012 01:20:00 GMT

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

Freddy Angarita — Mon, 02 Jan 2012 15:02:00 GMT

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

Freddy Angarita — Tue, 04 Oct 2011 00:36:00 GMT

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

Freddy Angarita — Mon, 26 Sep 2011 20:19:00 GMT

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

Freddy Angarita — Sat, 10 Sep 2011 21:46:00 GMT

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

Freddy Angarita — Wed, 07 Sep 2011 22:57:00 GMT

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

Freddy Angarita — Tue, 06 Sep 2011 16:24:00 GMT

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