Navegador de Archivos
Calendario del Blog
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
- http://msdn.microsoft.com/en-us/library/ms175513(SQL.90).aspx
- http://msdn.microsoft.com/en-us/library/ms188787(v=SQL.90).aspx
- http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
-
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.
Para saber más.
Estadísticas de visitantes
Comentarios Recientes