Comparando relaciones entre tablas con campos tipo Entero vs. Caracter
En el diseño de bases de datos relacionales existen diversas formas de hacer el diseño y va a depender mucho de la experiencia del DBA o del Developer, en este artículo se analiza la diferencia al relacionar las tablas con un datos de tipo Entero o con datos de tipo caracter.
Muchos diseñadores de base de datos usan la propiedad Identity para definir un campos ID con el que se establece la relación con otras tablas, aquellas tablas que tengan un ID con Primary key, deberían tener un campo adicional para guardar el código del registro en el que se debe definir una restricción de tipo Unique para que este código no se repita y se convierta en una clave alterna (AK). También se va a demostrar que tener el código como clave alterna, la búsqueda es mas lenta.
Para la comparación se va a crear dos esquemas, luego en estos esquemas se crearán tablas de Categorias y Productos
identicas, con los mismos registros, relacionadas en un esquema con campos tipo Entero y en el otro esquema con datos
tipo Caracter, luego realizaremos consultas analizando el tiempo en que demora mostrarse el resultado.
Usando la base de datos Northwind
use Northwind
go
Ejercicio 1
Para este ejercicio se va a crear dos esquemas: Entero y Caracter, y establecer la relación entre las tablas con el dato de tipo Entero para el esquema llamado Entero y con el tipo de datos nchar para el esquema llamado Caracter. Luego se va a comparar usando el resultado del Plan de ejecución para determinar con cual de las relaciones es mas rápida la extracción de datos.
Creando los esquemas
Create schema Entero
go
Create schema Caracter
go
Tablas en el Esquema Entero
Creando las tablas Categorías y Productos en el esquema Entero, y copiar los registros de Categories y Products de la base de datos Northwind. En este esquema las tablas están relacionadas por un campo de tipo Int.
Create table Entero.Categorias
(
CategoriasCodigo int identity,
CategoriasDescripcion nvarchar(100),
CategoriasEstado nchar(1)
constraint CategoriasPK Primary key (CategoriasCodigo)
)
go
Insertando los registros
Insert into Entero.Categorias
select C.CategoryName + ': ' + CAST(C.Description as nvarchar(80)), 'A'
from dbo.Categories As C
go
Para ver los registros
Select * from Entero.Categorias
go
La imagen siguiente muestra el resultado
Create table Entero.Productos
(
ProductosCodigo int identity,
ProductosDescripcion nvarchar(100),
ProductosPrecio Numeric(9,2),
ProductosStock Numeric(9,2),
ProductosEstado nchar(1),
CategoriasCodigo int
constraint ProductosPK Primary key (ProductosCodigo),
constraint ProductosCategoriasFK Foreign key (CategoriasCodigo)
references Entero.Categorias(CategoriasCodigo)
)
go
Insertando los registros
insert into Entero.Productos
select
P.ProductName,
P.UnitPrice,
P.UnitsInStock, 'A',
P.CategoryID
from dbo.Products As P
go
Para ver los registros
select * from Entero.Productos
go
La imagen siguiente muestra el resultado
Esquema Caracter
Creando las tablas Categorías y Productos en el esquema Caracter, y copiar los registros de Categories y Products de la base de datos Northwind, este esquema las tablas están relacionadas por un campo de tipo nchar
Create table Caracter.Categorias
(
CategoriasCodigo nchar(4),
CategoriasDescripcion nvarchar(100),
CategoriasEstado nchar(1)
constraint CategoriasPK Primary key (CategoriasCodigo)
)
go
Note que el campo código es de tipo nchar(4)
Insertando los registros
Insert into Caracter.Categorias
select
Right('000' +trim(Str((C.CategoryID))),4) ,
C.CategoryName + ': ' + CAST(C.Description as nvarchar(80)), 'A'
from dbo.Categories As C
go
Create table Caracter.Productos
(
ProductosCodigo nchar(7),
ProductosDescripcion nvarchar(100),
ProductosPrecio Numeric(9,2),
ProductosStock Numeric(9,2),
ProductosEstado nchar(1),
CategoriasCodigo nchar(4),
constraint ProductosPK Primary key (ProductosCodigo),
constraint ProductosCategoriasFK Foreign key (CategoriasCodigo)
references Caracter.Categorias(CategoriasCodigo)
)
go
Insertando los registros
insert into Caracter.Productos
select
Right('000000' +trim(Str((P.ProductID))),7) ,
P.ProductName,
P.UnitPrice,
P.UnitsInStock, 'A',
Right('000' +trim(Str((P.CategoryID))),4)
from dbo.Products As P
go
Listando los registros de ambas tablas, se puede ver que son los mismos del esquema en el que relacionan con
el dato de tipo Int.
select * from Caracter.Categorias
go
select * from Caracter.Productos
go
La imagen siguiente muestra el resultado
Comparando los listados
Listado con la relación con campos tipo Int (Entero)
Select
P.ProductosCodigo,
P.ProductosDescripcion,
P.ProductosPrecio,
C.CategoriasDescripcion
from Entero.Productos As P
join Entero.Categorias As C on P.CategoriasCodigo = C.CategoriasCodigo
go
La imagen siguiente muestra el resultado
El Plan de ejecución de la orden anterior nos muestra el costo
Note el costo de: 0.0189873
Listado con la relación con campos tipo nchar (Caracter)
Select
P.ProductosCodigo,
P.ProductosDescripcion,
P.ProductosPrecio,
C.CategoriasDescripcion
from Caracter.Productos As P
join Caracter.Categorias As C on P.CategoriasCodigo = C.CategoriasCodigo
go
La imagen siguiente muestra el resultado
El Plan de ejecución de la orden anterior nos muestra el costo
Note el costo de: 0.0189873, igual al costo de la relación de las dos tablas con los datos de tipo Entero.
Conclusión: Ambos planes de ejecución muestran el mismo tiempo de respuesta, por lo que, siguiendo este parámetro, es indiferente el uso de cualquiera de los tipos de datos para relacionar las tablas, puede ser Int o Nchar.
Importante: Puede ejecutar el siguiente comando para eliminar los planes de ejecución del cache para obtener datos
mas exactos.
DBCC FREEPROCCACHE WITH NO_INFOMSGS
go
Ejercicio 2
Cuando se tiene un campo que es el ID de tipo numérico, la tabla debe tener otro campo que almacene el código del registro. Para el siguiente ejercicio se va a crear un esquema llamado ConCodigo y en este se van a crear las tablas Categorías y Productos en la que se agregará un campo para el código.
El objetivo es demostar que si se usa ID de tipo número o entero, al buscar un registro filtrando por el código, el tiempo de búsqueda por el campo que no es la PK demora más. El campo que almacena el código debe ser clave alterna, para ello se especificará la restricción Unique.
Creando el esquema
Create schema ConCodigo
go
La tabla de Categorias
Create table ConCodigo.Categorias
(
CategoriasID int identity,
CategoriasCodigo nchar(4),
CategoriasDescripcion nvarchar(100),
CategoriasEstado nchar(1)
constraint CategoriasPK Primary key (CategoriasID),
constraint CategoriasCodigoUQ Unique (CategoriasCodigo)
)
go
Insertando los registros
Insert into ConCodigo.Categorias
select
Right('000' +trim(Str((C.CategoryID))),4) ,
C.CategoryName + ': ' + CAST(C.Description as nvarchar(80)), 'A'
from dbo.Categories As C
go
Ver las categorías
select * from ConCodigo.Categorias
go
El listado de las categorías se muestra en la siguiente imagen
Note que existe un campo adicional para el código de la Categoría.
Los Productos
Create table ConCodigo.Productos
(
ProductosID int identity,
ProductosCodigo nchar(7),
ProductosDescripcion nvarchar(100),
ProductosPrecio Numeric(9,2),
ProductosStock Numeric(9,2),
ProductosEstado nchar(1),
CategoriasID int,
constraint ProductosPK Primary key (ProductosID),
constraint ProductosCodigoUQ Unique (ProductosCodigo),
constraint ProductosCategoriasFK Foreign key (CategoriasID)
references ConCodigo.Categorias(CategoriasID)
)
go
Insertando los registros
insert into ConCodigo.Productos
select
Right('000000' +trim(Str((P.ProductID))),7) ,
P.ProductName,
P.UnitPrice,
P.UnitsInStock, 'A',
P.CategoryID
from dbo.Products As P
go
Ver los productos
select * from ConCodigo.Productos
go
La imagen siguiente muestra el resultado
Realizando las búsquedas
Para comprobar los tiempos se va a buscar el registro por el código en las tablas Categorías y Productos, se va a comparar el costo en la tabla del esquema Caracter que tiene el campo Código de tipo nchar como PK versus la búsqueda en la tabla que tiene el código como clave alterna, la que se ha especificado la restricción de tipo Unique.
Select * from Caracter.Categorias As C
where C.CategoriasCodigo = '0003'
go
Plan de ejecución estimado
Note el costo de 0.0032831
Búsqueda en la tabla que tiene ID, el campo con el código es clave alterna.
Select * from ConCodigo.Categorias As C
where C.CategoriasCodigo = '0003'
go
Plan de ejecución estimado
Note el costo de 0.0065704
Resumen
Costo de la búsqueda de una categoría en la tabla con PK tipo nchar: 0.0032831
Costo de la búsqueda de una categoría en la tabla con PK tipo int: 0.0065704
Definitivamente la búsqueda en la tabla con dato de tipo nchar como PK es más rápida.
Búsqueda del producto
Las instrucciones para mostrar el producto con código "0000003" son
Select * from Caracter.Productos As P
where P.ProductosCodigo = '0000003'
go
Plan de ejecución estimado
Note el costo de 0.0032831
Búsqueda en la tabla que tiene ID, el campo con el código es clave alterna.
Select * from ConCodigo.Productos As P
where P.ProductosCodigo = '0000003'
go
Plan de ejecución estimado
Note el costo de 0.0065704
Resumen
Costo de la búsqueda de una categoría en la tabla con PK tipo nchar: 0.0032831
Costo de la búsqueda de una categoría en la tabla con PK tipo int: 0.0065704
Definitivamente la búsqueda en la tabla con dato de tipo nchar con PK es más rápida.