Iver's web place

Navegador de Archivos

Calendario del Blog

February 2012
Sun Mon Tue Wed Thu Fri Sat
29 30 31 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 1 2 3

General

Programación

  • Eliminando registros duplicados

    Para no perder la costumbre y aprovechando el bendito insomnio, ahora posteare como emplear dos interesantes funcionalidades que provee SQL Server para borrar registro, primero las Common Table Expression que están disponibles desde la versión 2005 y la combinación de over/partition.


    Supongamos que tenemos una tabla con registros duplicados pero sin identificador único. Para esto hagamos una tabla sencilla que represente el escenario:


     
    CREATE TABLE Origen
    (
            Nombre VARCHAR(15),
            Direccion VARCHAR(200)
    )
    GO
     

    Luego insertamos los registros duplicados:


     
    INSERT INTO Origen
    VALUES
             ('Pedro Chavez'        ,'Av. Juan Escutia #20')
            ,('Pedro Chavez'        ,'Av. Juan Escutia #20')
            ,('Luis Macias'         ,'Calle San Francisco #44')
            ,('Luis Macias'         ,'Calle San Francisco #44')
            ,('Luis Macias'         ,'Calle San Francisco #44')
            ,('Alejandra Gómez'    ,'Calle Becerra #14')
            ,('Luis Macias'         ,'Calle San Francisco #44')
            ,('Luis Macias'         ,'Calle San Francisco #44')
    GO     
     

    Ahora podemos ver los registros repetidos con su respectivo identificador haciendo uso de la función ROW_NUMBER()


     
    WITH Repetidos (Id, Nombre, Direccion)
    AS (
    SELECT
            ROW_NUMBER() OVER (PARTITION BY Nombre, Direccion ORDER BY Nombre) AS Id,
            Nombre, Direccion
            FROM Origen
    )
    SELECT * FROM Repetidos
    GO
     

    Por último podemos emplear esto para hacer el respectivo delete.


     
    WITH Repetidos (Id, Nombre, Direccion)
    AS (
    SELECT
            ROW_NUMBER() OVER (PARTITION BY Nombre, Direccion ORDER BY Nombre) AS Id,
            Nombre, Direccion
            FROM Origen
    )
    DELETE FROM Repetidos WHERE Id > 1
    GO
     

    Cualquier baba de perro, que no!



    Para saber más

  • SET [QUOTED_IDENTIFIER/ANSI_NULLS] ON/OFF


    Existe una opción en el Management Studio de SQL Server que nos permite crear scripts de forma fácil y sencilla. Lo bonito de los scripts generados es eso, que se generan solos y con eso podemos ejecutarlos sin problemas. Sin embargo siempre nos generan algo como:


     
    USE [DataBaseName]
    GO
    /** Object:  Table [dbo].[TableName]    Script Date: 11/15/2010 17:32:47 **/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    ....
    ...
    ..
     


    Y como yo soy muy curioso en esto de conocer que carambas voy a ejecutar en el servidor de SQL, se me ocurrio investigar, para que sirven los benditos ANSI_NULLS, ANSI_PADDING y QUOTED_IDENTIFIER. Los resultados fueron los siguientes:



    ANSI_NULLS


    Le dice al motor de SQL como debe manejar las comparaciones con los valores NULL. Cuando asignamos esta opción a ON, todas las comparaciones con NULL usando el operador = o el <> nos dará como resultado un FALSE. Lo cual está bien porque es el comportamiento definido en el estándar ISO ya que para realizar las comparaciones con NULL es necesario usar IS NULL y IS NOT NULL. Así que cuando usamos la opción con OFF nos regresara las comparaciones con los operadores = y <> realizará un comportamiento similar. De ahí la razón por la que se asigna siempre a ON al inicio del script.
    Ej:



     
    SET ANSI_NULLS ON
    IF NULL = NULL
     PRINT 'OK'
    ELSE
     PRINT 'Nada'
    --R:  Nada
     
    SET ANSI_NULLS ON
    IF NULL IS NULL
     PRINT 'OK'
    ELSE
     PRINT 'Nada'
    -- R: OK
     
  • Trazas por default en SQL Server 2005

    Creo que este blog se convertirá en un blog de SQL porque es lo único en lo que pienso/trabajo en estos días.



    Por lo pronto escribiré algo sencillo sobre SQL Server 2005 y las trazas. Resulta que en un servidor que tenemos en desarrollo, encontré que había un archivo en el directorio de logs (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\) llamado log_152.trc y como son de esas cosas curiosas que después pueden servirnos decidí poner los diversos métodos para localizar las trazas que se generan por default en SQL Server 2005


     
    -- Para conocer si está activa o no la traza por default
    sp_configure 'default trace enabled'
    GO
    -- Para activar la configuración de traza por default
    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'default trace enabled', 1;
    GO
    RECONFIGURE;
    GO
    -- Para consultarlo mediante SQL
    SELECT * FROM sys.configurations WHERE configuration_id = 1568
    GO
    -- Usando la función fn_trace_getinfo() para obtener el archivo actual de logs
    SELECT * FROM ::fn_trace_getinfo(0)
    GO
    -- Obtener información de las trazas
    SELECT
         t.EventID,
         t.ColumnID,
         e.name AS Event_Descr,
         c.name AS Column_Descr
    FROM ::fn_trace_geteventinfo(1) t
         INNER JOIN sys.trace_events e
              ON t.eventID = e.trace_event_id
         INNER JOIN sys.trace_columns c
              ON t.columnid = c.trace_column_id
     


    Por hoy es todo .. abur! =P



    Para saber más


  • Mostrar los índices de una tabla en SQL Server 2005

    Muchas veces requerimos conocer que índices existentes en la base de datos, para esto podemos emplear el siguiente script.


     
    DECLARE _at_IndexInfo  TABLE (index_name         varchar(250)
                              ,index_description  varchar(250)
                              ,index_keys         varchar(250)
                              )
     
    INSERT INTO _at_IndexInfo
    exec sp_msforeachtable 'sp_helpindex ''?'''
    SELECT index_name, FROM _at_IndexInfo
     
     


    Ahora que si lo que deseas es listar solamente los índices de una tabla en particular sería algo como


     
    sp_helpindex 'Production.Product'
     

    Y si quieres algo más como el esquema, es mejor una consulta como:


     
    SELECT  s.name AS [Schema], t.name AS TableName,
        ind.name, ind.index_id, ic.index_column_id, col.name,
        ind.*, ic.*, col.*
    FROM sys.indexes ind
            INNER JOIN sys.index_columns ic
                    ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
            INNER JOIN sys.COLUMNS col
                    ON ic.object_id = col.object_id AND ic.column_id = col.column_id
            INNER JOIN      sys.TABLES t
                    ON ind.object_id = t.object_id
            INNER JOIN sys.schemas s
                    ON t.schema_id = s.schema_id
    WHERE
        ind.is_primary_key = 0
        AND ind.is_unique = 0
        AND ind.is_unique_constraint = 0
        AND t.is_ms_shipped = 0
            AND t.name LIKE '<Table_Name>'
            AND s.name LIKE '<Schema_Name>'
    ORDER BY
        t.name, ind.name, ind.index_id, ic.index_column_id
     
     
  • Tocando los índices III de III


    Continuando con las últimas publicaciones de SQL, ahora toca el turno a la última parte de "Tocando los índices":




    En el artículo anterior se mostró lo que es el reconstruir o defragmentar el índice y se hizo mención de las estadísticas. Tal vez en otro post profundice un poco más sobre las estadísticas.



    Por lo pronto me gustaría aclarar que son las estadísticas del SQL. Tal cómo la definición de estadísticas lo dice, es "una ciencia formal" (la estadística, claro está) referente a la recolección, análisis e interpretación de datos. A partir de estas, el motor de SQL decide la mejor forma de obtener la información que se le solicita en una consulta.


    Mejores prácticas en la creación de Índices


    Antes de crear cualquier índice, es necesario conocer el uso que le daremos a los datos. Si son de solo lectura (un Data Warehouse, sería un claro ejemplo), si se cargará información para después "masajear" la información y volcar los resultados a una tabla definitiva, etc. El objetivo de los índices es facilitar la búsqueda de datos al motor de SQL y no penalizar su desempeño. Si tenemos índices en una tabla que servirá para cargar información de millones de registros en el mismo proceso, lo mejor sería no crear índices hasta que se haya efectuado la inserción, esto ayudará al motor de SQL a guardar dicha información lo más ágil posible. En cuanto termine la carga, se pueden crear los índices respectivos en las columnas que lo requieran.



    Al nombrar los objetos es bueno seguir un estándar, con la finalidad de identificar fácilmente el objetivo de cada objeto. En el caso de los índices no hay excepción y en mi caso empleo la nomenclatura ix (de índice -IndeX), nombre del índice y si incluye alguna otra columna. Como el siguiente ejemplo:


     
    USE AdventureWorks2008R2;
    GO
    CREATE INDEX IX_ProductVendor_VendorID
        ON Purchasing.ProductVendor (BusinessEntityID);
    GO
     


    Es importante revisar periodicamente el estado de los índices, esto nos permite establecer un tiempo de mantenimiento en el que se podrán reorganizar o reconstruir los índices para su mejor desempeño, usando la siguiente consulta podemos ver el uso de los índices.


     
    SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
    GO
     


    La columna que nos interesa del resultado de la consulta anterior es avg_fragmentation_in_percent, lo que nos ayudara a saber que debemos hacer para cada índice de cada tabla.


    avg_fragmentation_in_percent value Corrective statement
    > 5% and < = 30% ALTER INDEX REORGANIZE
    > 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*


    También podemos modificar la consulta para una tabla en particular.


     
    USE AdventureWorks2008R2;
    GO
    SELECT a.index_id, name, avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
         NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
    GO
     


    Debido a que no solo es crear índices en la base de datos, es recomendable saber cuando crear y cuando no crear índices. Para esto se puede hacer uso de las herramientas disponibles desde la versión 2005 de SQL Server como: SQL Server Profiler, Database Engine Tuning Advisor, sin olvidar el uso de las Dynamic Management Views. Estás últimas son de mucha ayuda si las sabemos emplear, a continuación dejo algunos scripts que pueden ayudar al diagnostico de índices y sus creaciones o borrados masivos.



     
    --Indices que no se han usado desde el ultimo inicio del servidor
    SELECT i.name
    FROM sys.indexes i
    WHERE i.object_id=object_id('<table_name>') AND
        i.index_id NOT IN  (SELECT s.index_id
                            FROM sys.dm_db_index_usage_stats s
                            WHERE s.object_id=i.object_id AND    
                            i.index_id=s.index_id AND
                            database_id = <dbid> )
     
    -- Indices que aun no se han usado
    SELECT
          object_name(i.object_id),
          i.name,
          s.user_updates,
          s.user_seeks,
          s.user_scans,
          s.user_lookups
    FROM
          sys.indexes i
      LEFT JOIN
          sys.dm_db_index_usage_stats s
      ON s.object_id = i.object_id AND
         i.index_id = s.index_id AND
         s.database_id = <dbid>
    WHERE objectproperty(i.object_id, 'IsIndexable') = 1 AND
     -- index_usage_stats has no reference to this index (not being used)
     s.index_id IS NULL OR
     -- index is being updated, but not used by seeks/scans/lookups
     (s.user_updates > 0 AND s.user_seeks = 0
     AND s.user_scans = 0 AND s.user_lookups = 0)
     ORDER BY object_name(i.object_id) ASC
     
     -- Para eliminar índices sin usar
          SELECT
             o.name AS Objeto,
             i.name AS Indice,
             i.index_id AS IndID,  
             (user_seeks + user_scans + user_lookups) AS Lecturas,
             user_updates AS Escrituras,
             (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) AS Filas,
             CASE  
                WHEN s.user_updates < 1 THEN 100
                ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
             END AS LecturasVsEscrituras,
             'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) AS 'Sentencia DROP'
          FROM
             sys.dm_db_index_usage_stats s  
             INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  
             INNER JOIN sys.objects o ON s.object_id = o.object_id
             INNER JOIN sys.schemas c ON o.schema_id = c.schema_id
          WHERE
             OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND
             s.database_id = DB_ID() AND
             i.type_desc = 'nonclustered' AND
             i.is_primary_key = 0 AND
             i.is_unique_constraint = 0 AND
             (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
          ORDER BY Lecturas
     


    Espero esto sea de utilidad para alguien más. face-wink.png


    Para saber más.

Anterior página 1 2 3 4 ... 8 Siguiente página
39 Artículos

Estadísticas de visitantes

5
61
27327