Bueno, comencemos por el principio. No es descabellado pensar que las claves naturales fueron las primeras que aparecieron, por ser las más lógicas, más “naturales” (permitidme el juego de palabras), por lo que leer la opinión de un “tal” Joe Celko no estaría mal, ¿verdad? Aunque está en inglés, creo que se entiende lo suficientemente bien como para no tener que traducirlo (y así no me ponéis en un compromiso 😉 ). Ahí va:
The IDENTITY column is one of these mistakes.
- It is not part of the SQL-92 Standard and it is highly proprietary to the Sybase family. It is not portable — not quite the same thing as proprietary, since you can often translate one SQL dialect into another with a simple replacement (i.e. the % operator becomes the MOD () function). So your code will not move over to a new database.
- IDENTITY looks like a datatype, but it is not. Create a table with one column in it and make it an IDENTITY column. Insert a number into the table and see what happens. Try to set it to NULL. If you cannot insert, update and delete all the columns, then this is not a table!
- IDENTITY looks like a constraint, but it is not. Try to create a table with two IDENTITY columns and it fails. If you cannot add it to a column, then it is not a constraint. It is possible to write a a set of constraints that prohibit data from ever being put in the table (their predicate is always FALSE). It is possible to write a a set of constraints that allow anything in the table (their predicate is always TRUE). But no constraint can prohibit the creation of the table itself — that is a meta-constraint.
- It is not relational. Consider this statement on a table, Foo, which has an IDENTITY column. Assume the query returns more than one row.
INSERT INTO Foo (x)
SELECT a FROM Bar;
You will get a result like this:
IDENTITY X
===========
1 ‘a’
2 ‘b’
3 ‘c’
but if the query changed an index or was put on the physical disk data page differently, you might have gotten:
IDENTITY X
============
1 ‘b’
2 ‘c’
3 ‘a’
Explain why one result is the logically correct choice for an identifier and all other choices are not, without any reference to the physical implementation. You cannot.
Instead of treating the query as a set, you are doing 1950’s sequential processing using the underlying sequential file system the Sybase family started with.
5) If you have designed your tables correctly, they will have a meaningful primary key derived from the nature of the entity they model. The IDENTITY column should be a redundant key. The reason IDENTITY columns are popular as keys is that they are easy to declare. This is also the same reason that people build non-normalized databases and put pennies in fuse boxes — easy is not right.
6) It is a bitch to do calculations on IDENTITY column values. Well, it was hard to do direct math on the sequential position of a record in a 1950’s punch card system and that it what the IDENTITY is mimicking.
7) There is no check digit in an IDENTITY columns value, so you have no way of verifying it if you use it as a key.
8) If you use IDENTITY as a key, the values tend to cluster on physical data pages because they are sequential. The result is that if the most recent rows are the most likely to be accessed, there will be locking contention for control of those physical data pages. What you really wanted in a key is some spread of the rows over physical storage to avoid having every user trying to get to the same page at the same time.
9) The actual implementation of the IDENTITY column has been problematic since Version 7.0. You can look up threads in the news groups to get assorted tales of woe.
There are other ways of getting a unique identifier for a table. The most portable method for getting a new identifier number which is not in the set is something like this:
INSERT INTO Foobar (keycol, a, b, c…)
VALUES(COALESCE((SELECT MAX(keycol) FROM Foobar) +1, 0), aa, bb, cc, …);
The scalar subquery expression returns the current high value for the key column, and then increments it. If there is no maximum value (i.e. this is the first row to be inserted), then it returns zero. Using this basic idea, you can replace the increment with a different constant or a random number generator. You can also add code to create a check digit.
Another method is to hash the columns that make up a compound key so that you have single short column that can be reconstructed if you need to verify it.
Uuuuuf… creo que le gustan, ¿verdad?. Bueno, veamos, ¿qué es lo que podemos sacar en claro de esta larga explicación?.
· En primer lugar, que una columna IDENTITY no es muy portable, al menos no directamente. Sin duda es un problema grave que nos podemos encontrar si a alguien se le ocurre la locura de migrar entre gestores de base de datos
· También nos encontramos con el problema de que, aunque podemos definir una columna como de este tipo de datos, luego en realidad no podemos manejarla como tal. Resulta paradójico, y en ciertos casos puede resultar problemático
· La facilidad de uso de estos datos y su prácticamente nulo mantenimiento ha hecho que se hayan extendido hasta el punto de ser usados erróneamente, dando lugar a malos diseños de tablas por no querer pensar más las cosas
· No son imprescindibles, ni mucho menos. Podemos conseguir su misma funcionalidad y aumentarla con muy poco código, y sabremos en todo momento qué es lo que está pasando
Bien, entonces, si dan problemas de mantenimiento, de migración, si pueden ser sustituidos por otras opciones, ¿para qué usarlos?. Bueno, pues porque también tienen su aspecto positivo y en determinados casos pueden ser muy beneficiosos para aumentar el rendimiento de nuestro sistema. Pero vayamos poco a poco, vamos a ver cómo podemos analizar el texto anterior desde otro punto de vista.
La primera réplica a dicho texto está clara: no hay que ser tan dogmático. Hay situaciones en las que saltarse la regla que dictan los libros nos proporciona muchos beneficios. También podríamos decir que en SQL Server, podemos insertar valores directamente en una columna de este tipo ejecutando la instrucción SET IDENTITY_INSERT <tabla> ON, así que la portabilidad en este sentido estaría solucionada, así que parece que no son tan negativos. ¿Volvemos a utilizarlos entonces?. Vamos a ver qué opináis después de analizar el siguiente código
/* Código con IDENTITY */
DROP TABLE Test
GO
CREATE TABLE Test(
TestID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TestName varchar(20) NOT NULL
)
GO
INSERT TEST VALUES (‘Uno’)
INSERT TEST VALUES (‘Dos’)
INSERT TEST VALUES (‘Tres’)
GO
— Revisar los Valores
SELECT * FROM TEST
GO
— Revisar el IDENTITY
DBCC CHECKIDENT(Test)
— Hagamos una transacción fallida
BEGIN TRAN
INSERT TEST VALUES (‘Cuatr’)
— OOPS NOS EQUIVOCAMOS, VOLVAMOS ATRÁS CORRIENDO!!
ROLLBACK TRAN
GO
— Menos mal que todo volvió como estaba antes. Hagámoslo ahora correctamente.
INSERT TEST VALUES (‘Cuatro’)
SELECT * FROM TEST
— ¿Pero qué ha pasado aquí? OOPS NO HAY ROLLBACK EN LOS IDENTITY!!!
DBCC CHECKIDENT(Test)
/* Código sin IDENTITY */
DROP TABLE TEST
GO
CREATE TABLE Test(
TestID INT –IDENTITY(1,1) NOT NULL PRIMARY KEY,
TestName varchar(20) NOT NULL)
GO
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, ‘Uno’ FROM TEST
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, ‘Dos’ FROM TEST
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, ‘Tres’ FROM TEST
GO
SELECT * FROM TEST
GO
— Vamos a ver qué pasa ahora si nos equivocamos
BEGIN TRAN
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, ‘Cuatr’ FROM TEST
— Ya no sé si fiarme… ¿o sí?
ROLLBACK TRAN
GO
— Qué nos mostrará… ¿
SELECT * FROM TEST
INSERT TEST SELECT COALESCE(MAX(TESTID),0)+1, ‘Cuatro’ FROM TEST
SELECT * FROM TEST
— AHORA SÍ!!!!
¡¡Vaya!! Resulta que ahora no me puedo fiar de los IDENTITY porque no quieren saber nada de las transacciones. Problema gordo éste, no hay duda: los valores se pierden y no se pueden recuperar. Está claro entonces: no hay que usarlos, me puedo encontrar con problemas gordos y que además no tienen solución. Los desecho totalmente… ¿seguro?.
Pongámonos ahora del otro lado. ¿Os acordáis cuando decía que las facilidades que nos brindaban este tipo de datos podían dar lugar a usos erróneos?, pues el código anterior es un claro ejemplo de ello. Es decir, dicho campo sólo está para simplificar, y su valor no me debería importar mucho, salvo para hacer uniones entre tablas. Veamos: si mi clave principal natural es empresa+serie+factura, estos datos los mantengo, y hago que sean únicos, y además añado un campo IDENTITY. El usuario nunca verá este campo IDENTITY, pero yo lo utilizaré internamente para hacer JOIN con él y así combinar esta tabla con otras. En ese caso poco me importa ese número o que me queden huecos al no hacer ROLLBACK de él. De este modo obtenemos dos puntos a favor: la simplicidad en las combinaciones y lo compacta que es la clave primaria de la tabla (no es lo mismo 20 caracteres que los 4 bytes que ocupa un entero en SQL Server)
Una página de índice con una clave de 10 bytes podrá almacenar, simplificando, unas 300 entradas por página (8000/(10+14)), mientras que un índice con una clave de 4 bytes podría almacenar, simplificando de nuevo, unas 400 entradas por página (8000/(4+14)). Esto representa una diferencia considerable, que permitiría en algunas operaciones una diferencia de rendimiento apreciable.
Otro caso distinto es la ejecución de JOIN, en cuyo caso el procesador debe realizar una serie de comparaciones de valores extraídos de los campos de enlace en ambas tablas. Con procesadores de 32 bits, cualquier valor mayor de 4 bytes representa bien una comparación externa al procesador, bien una operación que requiere más de un ciclo de CPU, lo cual representa en cualquier caso una merma de rendimiento. Sin embargo, en este caso, como en el de claves foráneas, los valores almacenados en esos campos son completamente irrelevantes para el usuario, y van a ser utilizados solamente por SQL Server para validar entradas y para ejecutar enlaces, por lo que un campo IDENTITY podría ser perfectamente válido.
Como parece que estamos cogiendo carrerilla, vamos a seguir defendiendo a los IDENTIY, o más en general a las claves artificiales, porque vamos a recordar que en SQL Server también tenemos los tipos de datos UNIQUEIDENTIFIER (GUID’s). Y es que un valor de cualquiera de estos tipos de datos no cambia nunca, justamente porque nos es irrelevante (no como un código de oficina o de artículo de nuestra empresa, que puede que a alguien se le ocurra la “magnífica” idea de modificarlo). Esto es importante en la concurrencia del bloqueo optimista. Veámoslo con un ejemplo. Supongamos que tenemos una la tabla con una clave que pueda cambiar:
Clave Campo1 Campo2
==================
C1 V11 V12
C2 V21 V22
El usuario A y el usuario B leen ambos el registro C1:
SELECT * FROM Tabla
El usuario A cambia el valor de la clave:
UPDATE Tabla SET Clave = C0 WHERE Clave =C1
Entonces el usuario B intenta cambiar el valor del campo1:
UPDATE Tabla SET Campo1 = V00 WHERE Clave = C1.
¿Qué ocurre?, simplemente no puede actualizar el registro porque no existe; peor aún, no puede refrescar el registro porque el usuario B no tiene nada que lo identifique. Con las claves artificiales esto no pasa, ya que las claves artificiales no cambian. No es un inconveniente que no se puedan cambiar, es una ventaja. Si ejecutas una instrucción como ésta:
UPDATE Tabla SET Campo1 = Valor1 WHERE Id = ValorId
Siendo Id la clave artificial, puedes estar seguro de que si la instrucción no actualiza ningún registro es porque el registro se ha eliminado, no quedando la posibilidad de que se haya modificado la clave primaria ya que es artificial.
Está claro que el punto anterior es una ventaja, y eso sin contar que el pequeño tamaño de un dato de tipo entero no es comparable con una PK formada por varios campos alfanuméricos. Son dos razones prácticamente no discutibles desde este punto de vista. Aunque siempre se podría contestar a este aspecto que si se modifica una clave, la cual identifica unívocamente a una tupla, igual nos tengamos que poner en el supuesto de que estemos hablando de otra tupla diferente; es decir, que teóricamente no tiene mucho sentido la operación de modificar una clave primaria, puesto que si su naturaleza permite esta operación, tal vez nos hayamos equivocado en la elección de los campos que componen dicha clave primaria.
¿Entonces nos quedamos con los IDENTITY?; bueno, puede ser un error desechar una clave natural simplemente por su tamaño (10, 100 ó 200 bytes). Sí, sí, leer atentamente lo siguiente y os sorprenderéis:
Supuestos:
| Tablas | Clave natural | Clave artificial |
| Empresas | 4 | ND |
| Almacenes | 4 | ND |
| Artículos | 20 | 4 (int) |
| Envase | 20 | 4 (int) |
| Carton | 20 | 4 (int) |
| LoteProduccion (bigint) | 50 | 8 |
En alguna tabla (la llamaremos ‘ArticulosProducidos’) se requiere que estas tablas estén relacionadas y juntas producen una llave primaria. Asumamos que esa tabla tiene 5 Millones de filas. Por último, todas las tablas requieren 90 bytes en otras columnas.
Esta tabla ‘ArticulosProducidos’, se ve así en índices agrupados:
| Tipo | #Pag | Kb | Niveles |
| Natural | 1.965 | 15.467 | 3 |
| Artificial | 258 | 2.031 | 2 |
Es decir, nos ahorramos un 80% en el índice agrupado y un 33% en el número de páginas requeridas para leer una fila, y como son sólo referencias a otras tablas no hay que crear un índice UNIQUE (que sería mortal porque consumiría ¡¡728 Mb!!). Pero eso no es lo mejor: si revisamos las páginas de datos se reducen sustancialmente, ya que ahora pasan de consumir 1 Gb a sólo 578 Kb: un ahorro del ¡¡¡30%!!!.
Si en tu empresa o a tu cliente, se les ocurre contratar a Joe Celko para validar tu diseño de BD, y sale con el cuento de ANSI, teoría de BD, etc. puedes rápidamente sacar tus cálculos y decirle a tu jefe o cliente:
· “Sí, efectivamente decidí romper XXXXX para lograr un incremento del 100% en el desempeño, ahora si usted quiere bajar la velocidad a la mitad, puedo volverla a normalizar.”
Todos sabemos la respuesta…
Pero no cantes victoria, ahora contratan a otro consultor (Mr. X) para que analice desempeño, y sale con que la consulta más frecuente es la ‘ArticulosProducidos’ filtrados por Artículo, Envase y Cartón se ejecuta el siguiente código (por claves naturales que es lo que conoce el usuario):
SELECT …
FROM ArticulosProduccion AS AP JOIN Articulos AS A ON AP.ArticuloID=A.ArticuloID JOIN Envases AS E ON AP.EnvaseID=E.EnvaseID JOIN Cartones as C ON AP.CartonID=C.CartonID
WHERE A.Articulo=@Articulo AND E.Envase=@Envase AND C.Carton=@Carton
Con lo cual se produce una búsqueda en cada uno de los índices de dichas tablas para luego buscar en la tabla de ‘ArticulosProducidos’, produciendo una lectura de 8 páginas (3 Tablas x 2 Niveles + 2 del índice sobre la llave Artificial), mientras que si hubieras mantenido el esquema relacional puro sólo hubieras tenido que leer 3 páginas haciendo que el plan de ejecución de tu consulta se vea horroroso.
Pero aún así tienes las de ganar porque vuelves a sacar tus números y dices:
· “Es correcto, pero los índices de dichas tablas ocupan 3.6 Mb y por la frecuencia que se usan normalmente están en memoria RAM, mientras que si uso una clave Primaria Natural ocupa 15 Mb, esto sin contar con la diferencia de espacio en los datos que produce un ahorro de 30%, si usted quiere bajar la velocidad en un …!.”
¡¡¡Felicidades!!! Tienes un diseño a prueba Joe y del consultor Mr. X.
Conclusiones
Ah, pero… ¿hay conclusiones después de todo esto?. Bueno, creo que hay una cosa clara: es un tema polémico, ¿verdad? 😉
No, hablando en serio, lo que podemos decir es que, como en muchos otros temas de las bases de datos, no existe una receta mágica ni algo que se pueda aplicar en todos los casos y que sea la mejor solución. Y es que estamos hablando de diseño, y ya sabemos todos lo que eso significa: muchas horas de análisis y de leer y releer los requisitos del usuario.
Si hablamos en términos de rendimiento, poco se puede decir en contra de tener una clave simple y estrecha. Sin embargo hay que conocer los problemas asociados a tener una clave mantenida por el sistema como son los IDENTITY (falta de transaccionalidad o migración) o bien mantenida por nosotros (normalmente suelen ser un cuello de botella en el rendimiento si no están bien implementados) antes de decidirse a dar el paso. Además, las claves naturales permiten ahorrar el espacio de tener que mantener la unicidad en dos sitios: en la clave artificial (como clave primaria) y en la clave natural (como clave alternativa y única).
