Grimpi IT Blog

junio 10, 2011

Como obtener un listado de todos los packages de SSIS deployados dentro de la base msdb

Filed under: SQL Server, SSIS — grimpi @ 2:16 pm

SELECT sl.name As Owner, ISNULL(spf2.foldername,) + ‘/’ + spf.foldername as SSIS_Folder, sp.Name AS SSIS_Name, Description as SSIS_Description, verbuild, packagetype
FROM msdb.dbo.sysssispackages sp
inner join msdb.dbo.sysssispackagefolders spf on spf.folderid = sp.folderid
inner join msdb.sys.syslogins sl on sl.sid = sp.ownersid
left join msdb.dbo.sysssispackagefolders spf2 on spf2.folderid = spf.parentfolderid
ORDER BY sp.name

Importante: Esta consulta funciona para todos los packages instalados dentro de la base msdb, pero no para los que fueron deployados como archivos!

Importante 2: Esta consulta solo funciona en SQL Server 2008 en adelante. La vista msdb.dbo.sysssispackages no existe en SQL Server 2005.

Anuncios

mayo 6, 2011

Como liberar memoria de SQL Server sin reiniciar el servicio?

Filed under: SQL Server — grimpi @ 7:14 pm

/*Seteamos el uso maximo de SQL Server a un valor bajo (en este ejemplo, 100MB)*/
EXEC sys.sp_configure N’max server memory (MB)’, N’100′
GO
RECONFIGURE WITH OVERRIDE
GO
CHECKPOINT
GO

/*Seteamos el uso maximo de SQL Server al valor que deseamos, (en este ejemplo, 1024MB). Como la liberacion de memoria, el SQL Server no la hace inmediatamente, hacemos un delay de 1 minuto.*/
WAITFOR DELAY ’00:01:00′
GO
EXEC sys.sp_configure N’max server memory (MB)’, N’1024′
GO
RECONFIGURE WITH OVERRIDE
GO

abril 22, 2011

Como migrar una base de datos de SQL Server a PostgreSQL

Filed under: Migrador, PostgreSQL, SQL Server — grimpi @ 11:20 pm

Hace poco tuve que migrar varias bases de datos de SQL Server a PostgreSQL 8.4. Si bien existen varias maneras de hacer esto, no encontré ninguna herramienta fiable y gratis que me permita realizar esta operación. Por supuesto que si estamos dispuestos a pagar, existen utilidades de conversión de bases de datos y algunas muy buenas. Pero gratis, nada que funcione decentemente. Por lo tanto, aprovechando bastante código que ya tenía escrito para otra cosa, decidí hacer mi propio conversor de bases de datos de SQL Server 2005/2008 a PostgreSQL 8.4, que subo para que cualquiera la pueda probar y ahorrarse tiempo y dinero. La aplicación es muy sencilla: ingresamos el connection string de la base de datos de SQL Server y presionamos el botón “Generar Script”. Esto va a generar un codigo SQL en la caja de texto de la aplicación que deberemos copiar y ejecutarlo en algún lugar.

Qué cosas migra este utilitario?
• Schemas
• Tablas
• Constraints (Primary Keys, Unique Keys, Foreign Keys)
• Indices
• Tipos de datos (Domains en PostgreSQL)
• Vistas (Si el código SQL utilizado es compatible con PostgreSQL, no vamos a tener problemas, de lo contrario, tendremos que modificarlo manualmente para hacerlo compatible).
• Comentarios (Extended Properties en SQL Server)

Qué cosas no migra?
• Datos (para eso podemos utilizar algún ETL o SSIS)
• Store procedures/funciones/triggers (La aplicación sin embargo, nos permite generar el script de estos objetos, para que luego los modifiquemos manualmente).

Adicionalmente, este utilitario agrega un par de funciones que existen en SQL Server y no en PostgreSQL como por ejemplo “newid()”, cuando usamos columnas tipo ROWGUID en SQL Server.
También se genera un trigger automáticamente, cuando existe una columna Computed, ya que esta feature no existe en PostgreSQL. Tiene una mínima inteligencia en algunos aspectos, que nos pueden ahorrar mas tiempo todavía. Por ejemplo, cuando encuentra que se llama a la función GetDate(), se reemplaza por su homologa en PostgreSQL que es now(). Lo mismo hago para DatePart y un par de funciones mas.

Esta aplicación es muy básica, pero potente. No garantizo que funcione en todos los escenarios ni que sea infalible. Pero me ha servido para convertir bases de datos muy complejas de manera muy rapida y creo que puede servir a muchos.

La aplicación se puede bajar de esta URL.

Toda sugerencia, queja, critica sera bievenida.

abril 1, 2011

Habilitar/Deshabilitar una columna Identity de manera optima

Filed under: Identitys, SQL Server — grimpi @ 8:45 pm

En este post habíamos explicado como habilitar/deshabilitar la propiedad identity de una tabla en SQL Server. Habíamos visto que no existe un comando natural que haga esta tarea (algo así como un ALTER TABLE Cliente ALTER COLUMN ClienteId SET IDENTITY ON/OFF) y por lo tanto, no quedaba otra opción que reconstruir manualmente la tabla entera copiando los datos, con el enorme costo que esto representa.
Sin embargo, existe una forma alternativa, que si tenemos SQL Server 2005/2008 (solo en la edición Developer y Enterprise), podríamos utilizar. Esta solución en consiste en utilizar las opciones de particionamiento que se incorporaron en SQL Server 2005 para mover datos , en vez de utilizar el metodo clasico de reconstrucción de la tabla.
Veamos un ejemplo:

/*Creamos una tabla Paises de ejemplo con el campo row_id con la propiedad IDENTITY habilitada*/
CREATE TABLE dbo.Paises (row_id INTEGER IDENTITY PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);
GO

/*Insertamos información de ejemplo*/
INSERT dbo.Paises (data)
VALUES (‘Argentina’), (‘Brasil’), (‘Chile’), (‘Uruguay’)
GO

/*Paso 1: Borro todos los objetos dependedientes de la tabla a modificar*/
ALTER TABLE ClienteAsociadoAPais DROP CONSTRAINT Paises_Original_FK

/*Paso 2: Creamos una tabla idéntica a la tabla Paises, pero sin la propiedad identity sobre la columna row_id.*/
CREATE TABLE dbo.Destination (row_id INTEGER PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);

/*Paso 3: Movemos el contenido de una tabla a otra.*/
ALTER TABLE dbo.Paises SWITCH TO dbo.Destination;

/*Paso 4: Eliminamos la tabla original (que ahora no tiene datos).*/
DROP TABLE dbo.Paises;

/*Paso 5: Renombramos la tabla para que tenga el nombre original*/
EXECUTE sp_rename N’dbo.Destination’, N’Paises’, ‘OBJECT’;

/*Paso 6: Creo todos los objetos dependedientes de la tabla a modificada que habia borrado en el paso 1*/
ALTER TABLE ClienteAsociadoAPais ADD CONSTRAINT Paises_Original_FK

La clave de esto esta en la sentencia SWITCH, que en realidad no mueve datos, sino que simplemente cambia el puntero de datos de un lugar a otro, con lo cual nos ahorramos un montón de problemas relacionados con lockeos. Este método tiene enorme ventajas por el método que anteriormente habíamos visto en el post anterior. En tabla muy grandes, la diferencia de performance es enorme.
Veamos los siguientes números capturados del profiler, para la tabla ejemplo con varios millones de registros:

Metodo clasico de reconstruccion de tabla:

Duracion CPU Reads Writes
3,856,785 719 115,957 396

Metodo alternativo usando SWITCH:

Duracion CPU Reads Writes
259 140 519 2

Como se puede ver, no hay comparacion posible entre ambos metodos, en tablas de mucho volumen.

Sin embargo, hay un par limitaciones:

  • Esto solo funciona en las versiones Developer y Enterprise de SQL Server, ya que son las únicas que permiten manejo de particiones de tablas.
  • La tabla Destino debe tener exactamente la misma estructura que la tabla origen (con excepción obviamente de la propiedad identity sobre la columna).

Algo que hay que tener en cuenta, es que al igual que el método de reconstrucción de tabla, hay que borrar y crear nuevamente todas las foreign key asociadas a la tabla.

Links relacionados:
Partitioned Tables and Indexes

marzo 19, 2011

Monitorear el progreso de una operación masiva de INSERT/DELETE sobre una tabla

Filed under: SQL Server, T-SQL — grimpi @ 11:47 pm

Supongamos que queremos monitorear el progreso de una operación intensiva de insert/delete sobre una tabla dentro de una transacción.
La primera opción que uno piensa es hacer un SELECT COUNT(*) FROM Tabla a medida que se ejecuta la operación de insertado
o borrado de datos. Lamentablemente salvo que tengamos un isolation level muy bajo, esta consulta va a quedar bloqueada
hasta que se termine la ejecución de la otra transacción, por lo tanto, no vamos a poder monitorear el progreso de la misma.

Veamos este ejemplo:

INSERT INTO TablaCliente (ID, Nombre, Apellido)
SELECT ID, Nombre, Apellido FROM ServidorLinkeado.dbo.MaestroClientes

Yo quiero copiar el contenido de la tabla MaestroClientes de un servidor externo a mi base de datos. Supongamos que la tabla MaestroClientes contiene 20 millones de registros, lo que significa que aun en optimas condiciones, el proceso seguramente duraría al menos un par de minutos.
Ahora mientras se ejecuta el INSERT de mas arriba, hagamos un SELECT COUNT(*) FROM TablaCliente en otra sesión. Como verán, la consulta queda en espera, hasta que se termine la transacción que realiza el insertado de datos.
Sin embargo, si consultamos la vista de sistema sys.partitions, vamos a encontrar información muy interesante, especialmente en el campo Rows. Consultando esta vista para la tabla que deseamos monitorear a medida que se ejecuta la transacción, vamos a ver que el campo rows de esa vista varia.

Ejemplo:

SELECT object_id, rows FROM sys.partitions WHERE object_id = OBJECT_NAME(‘TablaCliente’)

A diferencia del SELECT COUNT(*) que hacíamos antes, esta consulta no queda bloqueada, pudiendo ser ejecutada varias veces mientras se realiza la transacción de insertado.
Entonces, como hacemos para ver el porcentaje de progreso?

1) Capturamos el total de registros que vamos a insertar:
SELECT COUNT(*) FROM ServidorLinkeado.dbo.MaestroClientes.

2) Capturamos el total de registros que ya existe en la tabla de destino:
SELECT rows FROM sys.partitions WHERE object_id = OBJECT_NAME(‘TablaCliente’)

3) Ejecutamos la transacción que realiza el insertado masivo de datos:
INSERT INTO TablaCliente (ID, Nombre, Apellido)
SELECT ID, Nombre, Apellido FROM ServidorLinkeado.dbo.MaestroClientes

4) Mientras se ejecuta la transacción, abrimos una nueva conexión y ejecutamos reiteradamente esta consulta:
SELECT rows FROM sys.partitions WHERE object_id = OBJECT_NAME(‘TablaCliente’)

 

 

Exactamente aplica la misma lógica, en el caso de que nos interese monitorear una operación de DELETE. La única diferencia es que en la columna rows de la tabla sys.partitions, veremos que su número desciende sucesivamente.

marzo 11, 2011

Cosas piolas que debería traer la nueva versión de SQL Server:

Filed under: Opinion, SQL Server, T-SQL — grimpi @ 2:13 pm

1) Encriptación irreversible por columna (similar al COLUMN ENCRYPT de Oracle). Ideal para almacenar passwords.

2) Agregar la sentencia CREATE OR REPLACE PROCEDURE/VIEW/FUNCTION como ya tienen Oracle o PostgreSQL. De esta manera, con una sola sentencia, nos evitamos verificar si existe o no una vista, store procedure o una función cuando deployamos.

3) CREATE TABLE NuevaTablaCliente AS TableClienteExistente. La posibilidad de crear una nueva tabla copiando la estructura de otra tabla ya existente es una feature muy piola de PostgreSQL.

4) ALTER COLUMN NombreColumn SET IDENTITY ON/OFF. Habilitar/deshabilitar un campo identity sin necesidad de reconstruir toda la tabla.

5) @NuevoParametro TablaCliente.ClienteId%TYPE. Poder declarar un parametro o variable como tipo de dato de una columna, en vez de declarar explicitamente el tipo de dato como hacemos actualmente.

6) Indíces Bitmap como los de Oracle.

7) Una funcion DECODE similar a la de Oracle (para el que no la conoce, es una especie de CASE…WHEN en una funcion).

8) Que no haya que configurar 50 parametros de Windows y DTC para poder correr una transacción distribuida correctamente.

9) Tener una función de conversión y formateo de datos potente, amigable y facil de usar, y no ese engendro de CONVERT.

10) Una función de SPLIT de string nativa para T-SQL que devuelva una variable de tabla.

11) ALTER TABLE para cambiar el orden de una columna en una tabla. Si bien es un poco “polémica” esta feature (se supone que el orden de las columnas no debería importarnos), la realidad es que a veces termina siendo útil. MySql tiene esta posibilidad.

12) Mejorar el error “String or binary data would be truncated.” cuando insertamos o modificamos datos. En que columna? Que valor fue el que tiro error? Si lo sabes, porque SQL Server no me lo queres decir?

13) Que soporte este tipo de sentencias: SELECT * FROM Clientes WHERE (col1, col2) NOT IN (SELECT col1, col2 FROM OtrosClientes). Actualmente el IN o NOT IN solo lo podemos hacer con una sola columna.

Alguna otra sugerencia?

febrero 24, 2011

Breve introducción de Read Uncommitted a Snapshot Isolation

Filed under: MVCC, SQL Server — grimpi @ 6:24 pm

Otras de las dudas muy habituales para los iniciados (y no tan iniciados) en SQL Server (y también en cualquier desarrollador de cualquier motor de base de datos) es que tipo de niveles de bloqueos existen (isolation level), para que sirven, en que caso usarlos y que desventajas tiene su uso.
Por lo tanto, decidí hacer un muy breve resumen de los distintos niveles existentes. Si bien, este post especifico esta enfocado en SQL Server, conceptualmente, puede servir para cualquier motor. Los niveles de aislamiento están especificados en el estandar ANSI.

Read Uncommitted: A veces llamado “lectura sucia” o “diry read”. Una transacción ejecutada en este nivel de aislamiento, puede leer datos que están siendo modificados por otra transacción concurrente. Ejemplo: Transacción T2 hace un update sobre el registro R1, sin embargo, la transacción T1 puede leer el registro R1 modificado, a pesar de que T2 potencialmente puede hacer un roll back sobre el mismo.

  • Ventajas: No se necesita hacer un lockeo para leer datos, aunque si para poder modificarlos.
  • Contras: No se puede garantizar la consistencia de los datos leídos. Puede darse el caso de que sea lea un dato que luego no exista más.
  • Uso habitual: En situaciones de alta concurrencia donde la potencial inconsistencia de datos sea tolerada.

Read Committed: Una transacción ejecutada en este nivel de aislamiento puede leer solamente datos commiteados. Por ejemplo, si una transacción concurrente T2 hace un update sobre el registro R1, la transacción T1 no podrá acceder al mismo y va a quedar bloqueada hasta que T2 haga un commit o un rollback sobre el registro.

  • Ventajas: Buen balance entre concurrencia y consistencia.
  • Contras: Mayor nivel de bloqueo.
  • Uso habitual: Es el isolation level mas común, de hecho, el que se usa por defecto en SQL Server.

Repeatable Read: Una transacción ejecutada en este nivel de aislamiento solo puede leer información comiteada con la garantía de que mientras dure la transacción, nadie va a poder leer esos datos. Esto significa que si vuelvo a leer esos datos dentro de la transacción, tengo total seguridad de que el resultado va a ser exactamente el mismo, ya que están bloqueados para la escritura en otra transacción.
Ejemplo: Asumamos que tenemos la tabla Cuentas que guarda el balance monetario de los clientes. Empezamos la transacción T1 con filtrando todos los balances cuyo monto sea mayor a 1000. Supongamos que nos devuelve 10 registros. Ahora empieza otra transacción T2, que inserta un nuevo registro cuyo balance es de 1020 y comitea. Si consultamos nuevamente en la transacción T1 todos los balances mayores a 1000, ahora nos va a devolver 11 registros. Esto se debe a que la transacción T1 bloqueó el conjunto de registros seleccionados en la consulta, pero no el predicado del filtro.
Ahora, si dentro de la transacción T2 intento eliminar todos los registros cuyo balance sea mayor a 1000, va a ocurrir un bloqueo, ya que están lockeados por la transacción T1.

  • Ventajas: Alta consistencia en los datos.
  • Contras: Mayor nivel de bloqueos y disminución del grado de concurrencia.
  • Uso habitual: En operaciones bancarias o financieras que requieran un grado de exactitud muy alto.

Serializable: Una transacción T1 ejecutada en este nivel ofrece el mayor grado de consistencia posible, eliminado los registros fantasmas. El motor puede llegar a bloquear incluso la tabla entera, para garantizar la máxima consistencia de datos.

  • Ventajas: Consistencia de datos absoluta.
  • Contras: Altísimo grado de bloqueo y muy bajo nivel de concurrencia, ya que las tablas pueden ser bloqueadas en su totalidad mientras se realiza una transacción.
  • Uso habitual: No es muy habitual. Casos muy raros cuando se requiera una gran consistencia de datos.

Adicionalmente, existen nuevos niveles de aislamiento a partir de SQL Server 2005.

Read-Committed-Snapshot : Este no es un nuevo nivel de aislamiento, sino una reimplementación del read comitted pero sin bloqueos.
SQL Server implementa versionado de registros mediante copias (snapshots) para evita el bloqueo.

  • Ventajas: Acceso no bloqueante a datos consistentes.
  • Contras: Mayor overhead para mantener el versionado de datos.
  • Uso habitual: Minimar los bloqueos lectura/escritura y el uso de el hint NOLOCK.

Snapshot Isolation: Ya habíamos hablado acá sobre este nivel de lockeo. Una transacción bajo este nivel de aislamiento, trabaja sobre una copia (o “snapshot”) de la base de datos. Cuando finaliza la transacción, hace un merge entre la copia y la base de datos.

  • Ventajas: Mayor nivel de consistencia incluso que un “repetable red”, con mayor nivel de concurrencia, ya que no realiza lockeos.
  • Contras: Mayor overhead para mantener las distintas versiones de un conjunto de datos, lo que impacta en la performance.
  • Uso Habitual: Cuando se necesita obtener información muy consistente y en entornos de alta concurrencia.

Recordemos que para activar especificar el nivel de aislamiento, se debe ejecutar SET TRANSACTION ISOLATION LEVEL <ISOLATION>

febrero 22, 2011

Link recomendado: Stairway to SQL Server

Filed under: Links, SQL Server — Etiquetas: , — grimpi @ 9:07 pm

Para quienes estén interesados en SQL Server, recomiendo y mucho este link del excelente portal SQL Server Central. La idea del “Stairway to SQL Server” es hacer una serie de tutoriales diseñados para pasar de conocimiento cero sobre un determinado tema de SQL Server, a un nivel de conocimiento profundo que permita empezar a utilizar esa característica en un entorno de producción.
Por el momento estos son los temas tratados (aunque se van a agregar nuevos):

  • SQL Server Agent
  • Server-side Tracing
  • SQL Server Indexes
  • Transactional and Merge Replication
  • Database Design
  • MDX
  • Integration Services
  • Reporting Services
  • StreamInsight

Realmente lo recomiendo.
Para poder entrar, hay que registrarse en el portal.

febrero 8, 2011

Control de concurrencia multiversión MVCC

Filed under: Engine, MVCC, PostgreSQL, SQL Server — grimpi @ 8:08 pm

Antes de explicar que es MVCC, hay que aclarar dos conceptos muy importantes: bloqueo pesimista y bloqueo optimista. El bloqueo optimista supone que no se va a hacer nada en el código de la aplicación que imponga explícitamente bloqueos de los recursos cuando se esté trabajando con ellos. Mientras que por otro lado, el bloqueo pesimista supone una intervención por parte de la aplicación como gestor del bloqueo.
Para ser más simples: El enfoque optimista delega en la base de datos el bloqueo y manejo de datos, mientras que el bloqueo pesimista, la aplicación es la encargada de gestionar la concurrencia.
MVCC (Multi version concurrency control) es una técnica de concurrencia optimista en donde ninguna tarea o hilo es bloqueado mientras se realiza una operación en la tabla, porque el otro hilo usa su propia copia (versión) del objeto dentro de una transacción.
Si bien obviamente la implementación interna de este algoritmo es distinta en cada motor de bases de datos, a grandes rasgos se podría decir que el MVCC internamente lo que hace es identificar cada transacción con un numero univoco y a cada registro de la tabla, con un numero de versión. Entonces, cada transacción trabaja con su copia y si se modifica un registro de una tabla, el contador de versión del registro se incrementa. Cuando se comitea, la copia del objeto reemplaza a la que existía en la base de datos. Si 2 transacciones modificaron la misma tabla, se hace un mergue de ambas tablas combinando las últimas versiones de cada registro. Si las 2 transacciones, modificaron exactamente el mismo registro, entonces en ese caso, cuando se commitee, el registro que finalmente queda, corresponde a la ultima transacción en realizar una modificación sobre ese registro.
De esta manera se logra que una lectura no bloquee una transacción de escritura y que una transacción de escritura tampoco bloquee una transacción de lectura. Para ser más claros, cuando hago un SELECT, puedo simultáneamente hacer un UPDATE y cuando hago un UPDATE puedo hacer simultáneamente un SELECT, algo que en el isolation habitual de las bases de datos, no es posible.
Al no existir ningún lockeo ni espera por parte del proceso, en determinadas situaciones concurrentes de fuerte escritura donde la información es modificada frecuentemente y de manera concurrente por muchos usuarios, se logran un gran mejoramiento de la performance del sistema. Es muy habitual usar este tipo modelo de concurrencia para evitar los famosos “deadlocks” que suelen ocurrir en bases de datos con mucha demanda.

Sin embargo, MVCC no debe ser usado en cualquier ocasión, ya que tiene un overhead muy importante. Todo el manejo de versionado tiene un alto costo, ya que las versiones de registros se copian y almacenan en tablas o estructuras físicas temporales que luego se descartan (por ejemplo, para guardar las copias, SQL Server usa la tempdb, mientras que Oracle usa el rollback segment). En SQL Server este overhead es un poco alto, por lo tanto solo en escenarios específicos conviene usar este modelo de concurrencia. A medida que las operaciones de escritura sobre la base de datos cobran relevancia sobre las operaciones de lectura, y por otro lado, nuestras lecturas son largas, los beneficios de usar MVCC aumentan, ya que reducimos los bloqueos.
Es fundamental la existencia de un alto grado de paralelismo en nuestra aplicación. MVCC es por definición, un modelo muy escalable y donde mejor se ven sus ventajas, es en escenarios de alta demanda.

Escenarios típicos:
• Aplicaciones que procesan transacciones en línea en gran escala.
• Aplicaciones mixtas que usan reportes contra datos sobre tablas en línea.
• Aplicaciones donde se ejecutan consultas largas mientras que simultaneamente se realizan operaciones sobre ella.

Escenarios donde no generalmente no debería usarse MVCC:
• Aplicaciones de datawarehousing que extraen información sobre tablas históricas donde no hay escritura.
• Aplicaciones que procesan datos en línea con poca concurrencia.
• Aplicaciones realtime, donde la velocidad es mas importante aun que la consistencia de información.

Todas las bases de datos modernas implementan actualmente este algoritmo de concurrencia optimista. En SQL Server se llama SNAPSHOT ISOLATION, en MySql se llama InnoDB Multi Version, en Firebird MGA y en Oracle y PostgreSQL, simplemente MVCC.

Links:
http://msdn.microsoft.com/en-us/library/ms345124(v=sql.90).aspx
http://www.codinghorror.com/blog/2008/08/deadlocked.html
http://www.rtcmagazine.com/articles/view/101612

febrero 2, 2011

Diferencias entre Unique Index vs Unique Constraint

Filed under: SQL Server — grimpi @ 3:30 pm

La otra vez tuve una discusión con un cliente que me inspiró a hacer este post. ¿Cual es la diferencia entre una constraint UNIQUE y un índice único? Pregunta bastante común para los iniciados en bases de datos.
Y la respuesta a esta pregunta, es que en el 90% de los casos, a efectos prácticos, es lo mismo. La diferencia es más conceptual que técnica.
Tanto una constraint de este tipo como un índice único, restringen los valores de una o varias columnas, impidiendo que se repitan valores. Cuando creamos una UNIQUE constraint, internamente se crea un índice sobre esa columna.
Veamos el siguiente ejemplo:

CREATE TABLE Emp (Id int, Name varchar(20), Email varchar(50))
GO

ALTER TABLE Emp ADD CONSTRAINT IX_Emp UNIQUE (Name)
GO

Ahora consultemos a las vistas de sistema para ver que pasó.

SELECT * FROM sys.indexes WHERE name = ‘IX_Emp’
GO
 
  

 

 

 

Vea que SQL Server crea automáticamente este índice nonclustered. Está marcado como unique constraint y se le da el mismo nombre que a nuestra constraint de la tabla.

El tema es que una constraint es vista como un elemento y requisito del negocio plasmado en la tabla, mientras que un índice único es una característica interna del motor de bases de datos, pero externa a las reglas de negocio de la aplicación. La diferencia es conceptual, a nivel técnico y de performance es lo mismo, pero hace mas clara la auto documentación de la bases de datos, definir una constraint por sobre un índice único.

Sin embargo, hay una diferencia teoría técnica fundamental. Una constraint de tipo UNIQUE sobre una o varias columnas, es una restricción que aplica a toda la tabla. En cambio, un índice único, la restricción aplica solamente sobre el conjunto de registros implicados en el índice. Como en la mayoría de los casos, siempre creamos índices sobre toda la tabla, no hay diferencias, pero hay varias situaciones particulares, donde no es lo mismo:

  • Foreign key: Puedo definir una constraint UNIQUE sobre una columna y usar esa columna como foreign key de otra tabla. Pero no se puede hacer lo mismo con un índice único.
  • Índices filtrados: A partir de SQL Server 2008, es posible definir un índice no por toda la tabla, sino por un conjunto restringido de registros. Si definimos a ese índice como único, esta restricción aplicaría entonces solamente al conjunto de registros implicados en el índice, no a toda la tabla.
    Esta opción nos podría ayudar a resolver la limitación de SQL Server de que solo podamos tener una clave NULL en una restricción de unicidad.
  • Vistas indexadas: Es una situación parecida a la del índice filtrado. Podría darse el caso de tener columnas cuyos valores se repiten, una está dentro de la vista y la otra no, por lo tanto, no se podría usar una constraint única.
    También podría darse el caso de que queremos unicidad en una columna dentro de un conjunto de varias tablas.
    Podríamos crear un índice único sobre una vista que agrupe a esas tablas. Con una constraint no podríamos hacerlo, porque la restriccion solo aplica a una sola tabla.
  • Performance: También hay cuestiones de performance muy finas que en situaciones particulares, hacen más conveniente usar un índice sobre una constraint.
    Por ejemplo, si bien, como ya dijimos antes, cuando creamos una UNIQUE, se crea un índice, este índice es interno, y las posibilidades de asignarle opciones del índice cuando es creado son mucho mas limitada. Podemos decirle con que FILLFACTOR debe ser creado ese índice pero por ejemplo, no podemos crear una constraint UNIQUE cuyo índice tenga la opción INCLUDE COLUMN habilitada. También cuando crea una restricción única, no hay forma de especificar que el índice creado para soportarlo sea ascendente o descendente. Existen algunas (extrañas) situaciones, donde esto podría interesarnos.

Ejemplo:
 
 CREATE TABLE Emp (Id int, Name varchar(20))
 GO

 ALTER TABLE Emp
  ADD CONSTRAINT IX_Emp UNIQUE (Name) WITH (FILLFACTOR = 20) INCLUDE (Email);
 GO 

Si corremos este script, nos va a tirar error. Ya que SQL Server no permite la opcion INCLUDE cuando creamos una UNIQUE constraint.

Resumiendo:
En la mayoría de los casos es lo mismo usar una u otra. Pero es preferible usar una constraint por sobre un índice, porque ayuda a documentar mejor las reglas de negocio de la base, pero por otro lado, usar un índice único nos permite hacer un tunning mas fino sobre la tabla.

Older Posts »

Crea un blog o un sitio web gratuitos con WordPress.com.