Cambiar Columna a Identity sin Reconstruir Tabla

Una regla de oro que yo tengo en cuanto al diseño de tablas es que estas siempre deben tener como primary key una columna de tipo Identity. Esto nos ayuda a tener una llave primaria que sea corta, optimizada para la búsqueda y sin posibilidad de actualización. Sin embargo hace poco tiempo me encontré en una situación en la que una de las tablas más grandes una de las bases de datos que yo administro no tenía una llave Identity, sino que era una columna de tipo integer normal, y había un requerimiento de transformarla a Identity.

Si bien es cierto uno podría ir al SSMS y abrir la tabla en modo diseño para luego colocar la columna Id como Identity, pero… esto no es tan simple ya que si se hace de esta manera lo que hará tras bambalinas es que SSMS creara un script para crear una nueva tabla con la estructura que se desea, ósea con la columna Id como Identity, y luego pasara toda la data desde la tabla original a la nueva tabla, para finalmente eliminar la tabla original y renombrar la nueva tabla como la original. Como se ha descrito, son varios pasos e involucra un algo uso del Transaction Log dependiendo del tamaño de la tabla. Esto podría ser aceptable en el caso que la tabla no sea muy grande, sin embargo ese no era mi caso, entonces luego de revisar varios foros y paginas salió la solución que voy a describir a continuación, la cual no requiere la reconstrucción de la tabla, sino que hace uso de un comando propio de particionamiento de tablas para poder lograr el objetivo.

Para poder mostrar el ejemplo vamos a crear una tabla a la que agregaremos 1 millon de registros, esta tabla tendrá tres columnas, la columna Id será de tipo entero pero no Identity.

CREATE TABLE dbo.BigTable
(id int not null primary key
,nombre varchar(50) not null
,apellido varchar(50) not null)
GO

WITH ID(number) AS
(
SELECT 1 AS number
UNION ALL
SELECT number + 1
FROM ID
WHERE number < 1000000
)
INSERT INTO dbo.BigTable
SELECT number, 'nombre_' + CAST(number AS VARCHAR), 'apellido_' + CAST(number AS VARCHAR)
FROM ID
OPTION(maxrecursion 0)

Ahora para poder convertir la columna Id a Identity debemos crear otra tabla que sea un espejo en estructura a mi tabla “BigTable”, pero esta nueva tabla si tendrá la columna Id como Identity.

CREATE TABLE dbo.BigTable_Tmp
(id int IDENTITY(1,1) not null primary key
,nombre varchar(50) not null
,apellido varchar(50) not null)
GO

Seguidamente aplicaremos el comando que hace que toda la magia sea posible, literalmente este comando es el que te salvara de incontables minutos viendo como tu tabla se reconstruye, tu Transaction Log crece junto a tus preocupaciones de si es que algo sale mal tendrás que tener tu tabla bloqueada por más tiempo.

ALTER TABLE dbo.BigTable
  SWITCH TO dbo.BigTable_Tmp
GO

Finalmente debemos eliminar la tabla original y renombrar la nueva para que tenga el nombre de la tabla original y las aplicaciones y otros objetos la sigan referenciando sin ningún problema.

DROP TABLE dbo.BigTable
GO
EXEC sp_rename 'dbo.BigTable_Tmp','BigTable'
GO

Como se puede observar el proceso es bastante sencillo y te permitirá ahorrar muchísimo tiempo y dolores de cabeza. En caso la tabla a modificar tenga relaciones (FKs) con otras tablas estas deberán ser eliminadas previas a la eliminación de la tabla original, y reconstruidas luego del renombrado de la tabla nueva.

Anuncios

Convertir Cadena en Tabla

¿Alguna vez no se ha encontrado con la necesidad de convertir una cadena de valores, quizás separados por comas (,), en una tabla con registros para poder utilizarla luego? Bueno yo si he enfrentado esta situación en diversas oportunidades, y lo que he tenido que hacer es básicamente crear una función que me retorne una tabla para poder cumplir con el objetivo. Sigue leyendo “Convertir Cadena en Tabla”

Historial de Backups con PowerShell

Hace un tiempo atrás escribí un post acerca del historial de backups y restores de una base de datos, esos scripts estoy seguro que les fueron de mucha utilidad, sin embargo yo sabia tenían algunas limitaciones las cuales requerían que en algunos casos tengamos que hacer cambios al código para que pudiera hacer lo que necesitábamos, pero si no dominábamos las tablas de sistema que están involucradas en el código, estos cambios se tornaban incluso más difíciles. Pues bueno ahora les traigo otra alternativa a esos scripts, este primer post nos guiara en los aspectos mas importantes del cmdlet de Powershell que vamos a utilizar para poder obtener esta misma información y más. Sigue leyendo “Historial de Backups con PowerShell”

Instalando Modulo DBATools

Hola, tal y como lo comente en mi entrada anterior existe un modulo de powershell creado inicialmente por Chrissy LeMaire (b|t), este modulo fue inicialmente concebido como una serie de comandos para hacer una migración entre instancias SQL Server de forma sencilla a través de Powershell, sin embargo con el paso del tiempo y el entusiasmo de la comunidad por querer hacer de este modulo algo mas que una herramienta para migración entre de instancias, una serie de personajes reconocidos en la comunidad de SQL Server se unieron a Chrissy para añadir más funcionalidad a este modulo y de esta manera pueda ser aprovechada por todos los DBAs que día a día luchamos para poder mantener nuestras bases de datos en linea y corriendo. Sigue leyendo “Instalando Modulo DBATools”

Introducción a Powershell para DBAs

Estimados amigos, hace relativamente poco tiempo he podido descubrir que PowerShell brinda un sinfín de posibilidades para automatizar nuestras tareas rutinarias tanto a nivel de base de datos como a nivel de la administración propia de los servidores; sí imagino lo que algunos estan pensando,  al parecer he vivido bajo de una roca y no he podido darme cuenta antes de todas las ventajas que ofrece PowerShell no sólo para los DBAs sino para los administradores de servidores en general.

Sigue leyendo “Introducción a Powershell para DBAs”

Tempdb: Reducir Tamaño

Bienvenidos nuevamente, este post es sobre un tema que seguramente muchos de ustedes han tenido que enfrentar, y es que hay ocaciones en las cuales  uno se encuentra con que el disco del servidor que aloja nuestra “tempdb” se encuentra casi llego y algunos procesos que demandan gran espacio en ella se caen justamente por falta de espacio. Sigue leyendo “Tempdb: Reducir Tamaño”