Cómo agrego una columna a una gran tabla de serveres SQL

Tengo una tabla de SQL Server en producción que tiene millones de filas, y resulta que necesito agregarle una columna. O, para ser más preciso, necesito agregar un campo a la entidad que representa la tabla.

Sintácticamente esto no es un problema, y ​​si la tabla no tuviera tantas filas y no estuviera en producción, sería fácil.

Realmente lo que busco es el curso de acción. Hay muchos sitios web con tablas extremadamente grandes, y deben agregar campos de vez en cuando. ¿Cómo lo hacen sin un time de inactividad sustancial?

Una cosa que debería agregar, no quería que la columna permitiera valores nulos, lo que significaría que necesitaría tener un valor pnetworkingeterminado.

Por lo tanto, o bien necesito descubrir cómo agregar una columna con un valor pnetworkingeterminado de manera oportuna, o tengo que encontrar una manera de actualizar la columna en un momento posterior y luego configurar la columna para que no permita nulos.

ALTER TABLE table1 ADD newcolumn int NULL GO 

no debería tomar tanto time … Lo que toma mucho time es insert columnas en el medio de otras columnas … b / c entonces el motor necesita crear una nueva tabla y copyr los datos a la nueva tabla.

La única solución real para el time de actividad continuo es la networkingundancia .

Reconozco la respuesta de @ Nestor de que agregar una nueva columna no debería llevar mucho time en SQL Server, pero aún así, podría ser una interrupción que no es aceptable en un sistema de producción. Una alternativa es hacer el cambio en un sistema paralelo, y luego una vez que la operación se complete, cambie lo nuevo por el anterior.

Por ejemplo, si necesita agregar una columna, puede crear una copy de la tabla, luego agregar la columna a esa copy y luego usar sp_rename() para mover la tabla anterior a un lado y la nueva tabla en su lugar.

Si tiene restricciones de integridad referencel que apuntan a esta tabla, esto puede hacer que el intercambio sea aún más complicado. Probablemente tenga que soltar las restricciones brevemente al intercambiar las tablas.

Para algunos types de actualizaciones complejas, puede duplicar por completo la database en un server server separado. Una vez que esté listo, simplemente intercambie las inputs de DNS para los dos serveres y voilà!

Apoyé a una compañía de bolsa de valores en la década de 1990 que ejecutó tres serveres de bases de datos duplicates en todo momento. De esta forma, podrían implementar actualizaciones en un server, al time que conservan un server de producción y un server de conmutación por error. Sus operaciones tenían un procedimiento estándar de rotation de las tres máquinas a través de roles de producción, conmutación por error y mantenimiento todos los días . Cuando necesitaban actualizar el hardware, el software o alterar el esquema de la database, les llevó tres días propagar el cambio a través de sus serveres, pero podían hacerlo sin interrupción en el service. Todo gracias a la networkingundancia.

"Agregue la columna y luego realice lotes de ACTUALIZACIÓN relativamente pequeños para rellenar la columna con un valor pnetworkingeterminado. Eso debería evitar cualquier desaceleración notable".

Y después de eso, debe establecer la columna en NOT NULL, que se activará en una transacción grande. Entonces, todo funcionará realmente rápido hasta que lo hagas así que probablemente hayas ganado realmente muy poco. Solo sé esto por experiencia de primera mano.

Es posible que desee cambiar el nombre de la tabla actual de X a Y. Puede hacerlo con este command sp_RENAME '[OldTableName]', '[NewTableName]'.

Vuelva a crear la nueva tabla como X con la nueva columna establecida en NOT NULL y luego batch insert de Y a X e incluya un valor pnetworkingeterminado en su inserción para la nueva columna o coloque un valor pnetworkingeterminado en la nueva columna cuando vuelva a crear la tabla X.

He hecho este tipo de cambio en una tabla con cientos de millones de filas. Todavía tardó más de una hora, pero no reventó nuestro logging de trans. Cuando traté de simplemente cambiar la columna a NOT NULL con todos los datos de la tabla, tardé más de 20 horas en matar el process.

¿Ha probado solo agregar una columna llenándola con datos y estableciendo la columna en NOT NULL?

Así que al final no creo que haya una bala mágica.

No quería que la columna permitiera valores nulos, lo que significaría que necesitaría tener un valor pnetworkingeterminado.

Agregar una columna NOT NULL con una Restricción DEFAULT a una tabla de cualquier cantidad de filas (incluso miles de millones) se volvió mucho más fácil comenzando en SQL Server 2012 (pero solo para Enterprise Edition) ya que permitía que fuera una operación en línea (en la mayoría de los casos ) donde, para las filas existentes, el valor se leerá de los metadatos y no se almacenará realmente en la fila hasta que se actualice la fila o se reconstruya el índice agrupado. En lugar de parafrasear más, aquí está la sección relevante de la página de MSDN para ALTER TABLE :

Agregar columnas NOT NULL como una operación en línea

A partir de SQL Server 2012 Enterprise Edition, agregar una columna NOT NULL con un valor pnetworkingeterminado es una operación en línea cuando el valor pnetworkingeterminado es una constante de time de ejecución . Esto significa que la operación se completa casi instantáneamente independientemente del número de filas en la tabla. Esto se debe a que las filas existentes en la tabla no se actualizan durante la operación; en su lugar, el valor pnetworkingeterminado se almacena solo en los metadatos de la tabla y el valor se busca según sea necesario en las consultas que acceden a estas filas. Este comportamiento es automático; no se requiere syntax adicional para implementar la operación en línea más allá de la syntax ADD COLUMN. Una constante de time de ejecución es una expresión que produce el mismo valor en time de ejecución para cada fila en la tabla, independientemente de su determinismo. Por ejemplo, la expresión constante "Mis datos temporales", o la function del sistema GETUTCDATETIME () son constantes de time de ejecución. Por el contrario, las funciones NEWID () o NEWSEQUENTIALID () no son constantes de time de ejecución porque se produce un valor único para cada fila de la tabla. Agregar una columna NOT NULL con un valor pnetworkingeterminado que no sea una constante de time de ejecución siempre se realiza fuera de línea y se adquiere un locking exclusivo (SCH-M) durante la operación.

Mientras que las filas existentes hacen reference al valor almacenado en metadatos, el valor pnetworkingeterminado se almacena en la fila para cualquier nueva fila que se inserte y no especifica otro valor para la columna. El valor pnetworkingeterminado almacenado en metadatos se mueve a una fila existente cuando se actualiza la fila (incluso si la columna real no se especifica en la instrucción UPDATE), o si la tabla o índice agrupado se reconstruye.

Las columnas de tipo varchar (max), nvarchar (max), varbinary (max), xml, text, ntext, image, hierarchyid, geometry, geography o CLR UDTS no se pueden agregar en una operación en línea. No se puede agregar una columna en línea si al hacerlo, el tamaño de fila máximo posible supera el límite de 8.060 bytes. La columna se agrega como una operación fuera de línea en este caso.

selecciona en una nueva tabla y renombra. Ejemplo, Agregar la columna i a la tabla A:

 select *, 1 as i into A_tmp from A_tbl //Add any indexes here exec sp_rename 'A_tbl', 'A_old' exec sp_rename 'A_tmp', 'A_tbl' 

Debe ser rápido y no tocará su logging de transactions, como podría ser la inserción en lotes. (Acabo de hacer esto hoy con una table de 70 millones de filas en <2 min).

Puede envolverlo en una transacción si necesita que sea una operación en línea (algo puede cambiar en la tabla entre seleccionar y renombrar).

Otra técnica es agregar la columna a una nueva tabla relacionada (Asumir una relación de uno a uno que se puede aplicar dando al FK un índice único). A continuación, puede completar esto en lotes y luego puede agregar la unión a esta tabla donde quiera que aparezcan los datos. Tenga en count que solo consideraría esto para una columna que no quisiera utilizar en cada consulta en la tabla original o si el ancho de logging de la tabla original era demasiado grande o si estaba agregando varias columnas.