Cuando trabajamos con SQL Server, puede haber situaciones en las que necesitemos rebasar una columna de identificador para optimizar las consultas en nuestros datos. En este artículo, exploraremos cómo utilizar expresiones de tabla comunes (CTEs) para resolver este problema.

Consideremos un escenario empresarial en el que tenemos una tabla de alimentos que vendemos, cada uno con un identificador único. Con el tiempo, nos damos cuenta de que muchas de nuestras consultas implican agrupar los alimentos alfabéticamente. Sin embargo, nuestra lista de alimentos es simplemente una lista simple sin ningún tipo de agrupación. En lugar de utilizar subconsultas o CTEs para volver a agrupar o reordenar los datos, queremos actualizar permanentemente la columna de identificador.

Para demostrar esto, vamos a utilizar un conjunto de datos de ejemplo con una tabla de alimentos orgánicos y una tabla de pedidos que contiene los pedidos de nuestra tabla de alimentos orgánicos. Utilizaremos el enfoque de CTE para rebasar la columna de identificador.

CREATE TABLE tbOrganicFoodsList (
    OrganicFoodId SMALLINT NOT NULL,
    OrganicFood VARCHAR(50) NOT NULL
)

ALTER TABLE tbOrganicFoodsList ADD CONSTRAINT PK_OrganicFoodId_List PRIMARY KEY CLUSTERED (OrganicFoodId)

INSERT INTO tbOrganicFoodsList VALUES
    (1, 'Brócoli'),
    (2, 'Manzana'),
    (3, 'Higo'),
    (4, 'Patata'),
    (5, 'Col rizada'),
    (6, 'Pepino')

CREATE TABLE tbOrganicFoodOrders (
    OrderId INT NOT NULL,
    OrganicFoodId SMALLINT NOT NULL
)

ALTER TABLE tbOrganicFoodOrders ADD CONSTRAINT FK_OrganicFoodId_Orders FOREIGN KEY (OrganicFoodId) REFERENCES tbOrganicFoodsList (OrganicFoodId)

INSERT INTO tbOrganicFoodOrders VALUES
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 5),
    (3, 6),
    (3, 1),
    (4, 5),
    (4, 6),
    (5, 3),
    (5, 2)

Ahora que tenemos nuestro conjunto de datos de ejemplo, procedamos a rebasar la columna de identificador utilizando CTEs.

El primer paso es agregar una nueva columna a nuestra tabla que almacenará los valores de identificador originales antes del rebasamiento. Esto nos ayudará a evitar cambiar el nombre de las columnas y asegurarnos de tener una referencia a los datos originales.

ALTER TABLE tbOrganicFoodsList ADD OldId SMALLINT

UPDATE tbOrganicFoodsList SET OldId = OrganicFoodId

SELECT * FROM tbOrganicFoodsList

A continuación, utilizaremos una CTE llamada “UpdateOrder” para ordenar los alimentos alfabéticamente y asignar un nuevo identificador a cada alimento. Esta CTE servirá como base para actualizar la columna de identificador.

WITH UpdateOrder AS (
    SELECT ROW_NUMBER() OVER (ORDER BY OrganicFood ASC) AS UpdateId,
           OrganicFoodId
    FROM tbOrganicFoodsList
)
UPDATE UpdateOrder SET OrganicFoodId = UpdateId

SELECT * FROM tbOrganicFoodsList ORDER BY OrganicFoodId

Ahora, nuestra columna de identificador está ordenada alfabéticamente. Podemos proceder a actualizar la columna de identificador en la tabla de pedidos utilizando una CTE y una unión en el campo de identificador antiguo.

WITH UpdateBase AS (
    SELECT t2.OrganicFoodId AS OrganicFoodId_NeedsRebase,
           t.OrganicFoodId AS OrganicFoodId_Base
    FROM tbOrganicFoodsList t
    INNER JOIN tbOrganicFoodOrders t2 ON t.OldId = t2.OrganicFoodId
)
UPDATE UpdateBase SET OrganicFoodId_NeedsRebase = OrganicFoodId_Base

SELECT t2.OrderId, t2.OrganicFoodId, t.OrganicFood
FROM tbOrganicFoodsList t
INNER JOIN tbOrganicFoodOrders t2 ON t.OrganicFoodId = t2.OrganicFoodId

Finalmente, podemos eliminar la columna temporal y agregar las restricciones de clave primaria y clave externa nuevamente a nuestra tabla.

ALTER TABLE tbOrganicFoodsList DROP COLUMN OldId

ALTER TABLE tbOrganicFoodsList ADD CONSTRAINT PK_OrganicFoodId_List PRIMARY KEY CLUSTERED (OrganicFoodId)

ALTER TABLE tbOrganicFoodOrders ADD CONSTRAINT FK_OrganicFoodId_Orders FOREIGN KEY (OrganicFoodId) REFERENCES tbOrganicFoodsList (OrganicFoodId)

Al seguir estos pasos, hemos rebasado con éxito la columna de identificador en nuestra base de datos de SQL Server utilizando expresiones de tabla comunes.

Es importante tener en cuenta que si desea agrupar elementos alfabéticamente dentro de una categoría o aplicar agrupaciones personalizadas, puede modificar la lógica de la CTE en consecuencia. Además, al diseñar sus tablas, considere las columnas que se utilizarán en las consultas y asigne suficiente espacio entre los valores para evitar la necesidad de rebasar con frecuencia.

En conclusión, las expresiones de tabla comunes proporcionan una herramienta poderosa para organizar y actualizar datos en SQL Server. Al aprovechar las CTEs, puede rebasar eficientemente una columna de identificador para optimizar las consultas en sus datos.