Come faccio ad aggiungere una colonna alla grande tabella di sql server

Ho una tabella di SQL Server in produzione che ha milioni di righe, e si scopre che ho bisogno di aggiungere una colonna ad esso. O, per essere più precisi, ho bisogno di aggiungere un campo per l’entità che la tabella rappresenta.

Sintatticamente questo non è un problema, e se la tabella non si hanno tante righe e non era in produzione, questo sarebbe stato facile.

Davvero di cosa sto dopo il corso di azione. Ci sono un sacco di siti web là fuori con grandi tavoli e deve aggiungere campi di volta in volta. Come fanno senza una sostanziale inattività?

Una cosa vorrei aggiungere, non volevo che la colonna per consentire null, il che significherebbe che avrei bisogno di avere un valore di default.

Quindi ho bisogno di capire come aggiungere una colonna con un valore di default in modo tempestivo, o ho bisogno di capire un modo per aggiornare la colonna in un secondo tempo e quindi la colonna non consente valori null.

 

6 Replies
  1. 28
    ALTER TABLE table1 ADD
      newcolumn int NULL
    GO

    non dovrebbe prendere molto tempo… Quello che prende un lungo periodo di tempo è quello di inserire le colonne all’interno di altre colonne… b/c, il motore ha bisogno di creare una nuova tabella e copiare i dati nella nuova tabella.

    • Sai una cosa, hai ragione. Fintanto che la colonna ha un valore NULL, quindi viene aggiunto abbastanza veloce. Ma se ho un valore di default, il suo prendere un lungo, lungo tempo. Quindi il vero problema di cui ho bisogno per pianificare è come aggiungere un valore di default per la colonna.
    • Aggiungere la colonna e quindi eseguire relativamente piccolo AGGIORNAMENTO batch per compilare la colonna con un valore di default. Che dovrebbero evitare eventuali rallentamenti evidenti.
    • Grazie Agent_9191, che mi sembra un buon approccio.
    • Forse si tratta di pignoleria, ma credo che il “b/c, il motore ha bisogno di creare una nuova tabella e copiare i dati nella nuova tabella” non è del tutto esatto. Per quanto ne so, il motore di database non consentire “l’inserimento” colonne, solo l’aggiunta di colonne al fine della definizione della tabella. Gli strumenti client sono responsabili per la creazione di una tabella temporanea, copia etc.
    • Kim, sì.. io non ho a dire che il motore fa (anche se è quello che ho scritto 🙂 ). È lo strumento client.
    • Non capisco perché questa risposta è stata accettata quando il richiedente afferma esplicitamente che vuole avere un valore di default.
    • L’approccio non funziona se la nuova colonna è parte della chiave primaria

  2. 12

    L’unica vera soluzione per la continuità è ridondanza.

    Riconosco @Nestor risposta che l’aggiunta di una nuova colonna non dovrebbe durare a lungo in SQL Server, ma che, tuttavia, potrebbe essere ancora un’interruzione che non è accettabile in un sistema di produzione. Un’alternativa è quella di rendere il cambiamento di un sistema parallelo, e poi una volta completata l’operazione, scambiare il nuovo per il vecchio.

    Per esempio, se avete bisogno di aggiungere una colonna, è possibile creare una copia della tabella, quindi aggiungere la colonna che copia, e quindi utilizzare sp_rename() di spostare il vecchio tabella a parte e la nuova tabella in posizione.

    Se si hanno vincoli di integrità referenziale che punta a questo tavolo, questo può rendere la swap ancora più difficile. Probabilmente per eliminare i vincoli brevemente come si scambiano le tabelle.

    Per alcuni tipi di complessi aggiornamenti, si potrebbe completamente duplicare il database su un altro server host. Una volta pronto, basta cambiare il DNS per i due server e voilà!

    Ho sostenuto una borsa di società
    nel 1990 che ha corso tre duplicati
    server di database in tutti i tempi. Che
    modo da poter implementare gli aggiornamenti su
    un server, pur mantenendo una
    la produzione di server e un server di failover
    server. Le loro operazioni avuto un
    procedura standard di rotazione del
    tre macchine di produzione,
    failover e manutenzione ruoli ogni
    giorno
    . Quando sono necessari per l’aggiornamento
    hardware, software, o alterare il
    schema del database, ci sono voluti tre giorni per
    propagare la modifica attraverso il loro
    i server, ma potevano farlo senza
    interruzione del servizio. Grazie
    per la ridondanza.

    • Come hai fatto a colmare le lacune di operazioni durante la manutenzione? Standard di replica?
    • Una borsa non ha bisogno per funzionare 24/7. Sono vicino alla campana.
    • Doh 🙂 Pensieri su come gestire che per 24/7 sistemi?
    • Giusto; ma questo deve essere risolto sincronizzando il delta di dati. La replica di registrazione e sono soluzioni comuni. Questo è un bel argomento molto ampio, però. Il mio punto è che l’alta disponibilità e ridondanza andare mano nella mano.
    • Bene, questo è quello che SSMS quando si generano le modifiche di script invece di risparmiare strumento e attendere per l’intervento completo.
  3. 8

    Non volevo che la colonna per consentire null, il che significherebbe che avrei bisogno di avere un valore di default.

    Aggiunta di un NOT NULL colonna con un DEFAULT Vincolo di una tabella di un qualsiasi numero di righe (forse miliardi) è diventato un sacco più facile partire da SQL Server 2012 (ma solo per Enterprise Edition), come essi hanno permesso di essere un’operazione in Linea (nella maggior parte dei casi) in cui, per le righe esistenti, il valore sarà letto da meta-dati e non effettivamente memorizzati nella riga fino a quando la riga viene aggiornata, o di un indice cluster viene ricostruito. Piuttosto che parafrasare più, qui è la sezione appropriata dalla pagina di MSDN per ALTER TABLE:

    Aggiunta di Colonne NOT NULL come Operazione Online

    A partire da SQL Server 2012 Enterprise Edition, l’aggiunta di una colonna NOT NULL il valore predefinito è un’operazione in linea quando il valore predefinito è un runtime costante. Questo significa che l’operazione è completata quasi istantaneamente, indipendentemente dal numero di righe nella tabella. Questo è perché le righe della tabella non vengono aggiornati durante il funzionamento; invece, il valore predefinito è conservata solo nei metadati della tabella e il valore è guardato come necessario in query con accesso a queste righe. Questo comportamento è automatico e nessun altro sintassi è necessario per implementare l’operazione in linea oltre la COLONNA aggiunta di sintassi. Un runtime costante è un’espressione che produce lo stesso valore in fase di runtime per ogni riga della tabella, indipendentemente dal suo determinismo. Per esempio, l’espressione della costante “i Miei dati temporanei”, o la funzione di sistema GETUTCDATETIME() sono runtime costanti. In contrasto, le funzioni NEWID() o NEWSEQUENTIALID() non sono runtime costanti a causa di un valore unico, prodotta per ogni riga della tabella. Aggiunta di una colonna NOT NULL con un valore di default che non è un runtime costante è sempre eseguita non in linea e un’esclusiva (SCH-M) blocco viene acquisito per tutta la durata dell’operazione.

    Mentre le righe esistenti di riferimento il valore memorizzato nel metadati, il valore predefinito è memorizzato sulla fila di nuove righe inserite e non si specifica un valore per la colonna. Il valore di default memorizzati nei metadati è spostato di una riga esistente quando la riga viene aggiornata (anche se la colonna non è specificato nell’istruzione UPDATE), o se la tabella o di un indice cluster viene ricostruito.

    Colonne di tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometria, geografia, o CLR i tipi definiti dall’utente, non può essere aggiunto in un’operazione online. Una colonna non può essere aggiunto in linea se così facendo il massimo possibile per la dimensione della riga di superare il limite di 8.060 byte. La colonna viene aggiunta come un’operazione di linea, in questo caso.

    • Che dire di una colonna null SQL Server 2012 standard edition 14M righe, 24×7 alta di concorrenza? Sarà un notevole tempo di inattività dovuto al blocco di schema?
    • Un NULL colonna invece di NOT NULL? Se sto capendo bene, che è un non-problema. È meta-dati e piuttosto immediata. Prima di SQL Server 2012 con la possibilità di aggiungere un NULL colonna immediatamente così a lungo in quanto ha un valore di default, l’unico modo per aggiungere una colonna senza il blocco era nulla da aggiungere NULL. Ma poi hai dovuto compilare tramite processo di Agente SQL o insiemi di 3000 righe per ogni AGGIORNAMENTO (per evitare l’escalation). Quindi no, non c’è bisogno di preoccuparsi di un NULL colonna, almeno non nella mia esperienza.
  4. 7

    “Aggiungere la colonna e quindi eseguire relativamente piccolo AGGIORNAMENTO batch per compilare la colonna con un valore di default. Che dovrebbero evitare eventuali rallentamenti evidenti”

    E dopo di che è necessario impostare la colonna NOT NULL che il fuoco fuori in un grande transazione. Così tutto sarà veramente veloce fino a fare in modo che probabilmente avete guadagnato molto poco davvero. Io so solo questo da esperienza di prima mano.

    Si potrebbe desiderare di rinominare la tabella corrente da X a Y. Si può fare con questo comando sp_RENAME ‘[OldTableName]’ , ‘[NewTableName]’.

    Ricreare la nuova tabella X con il nuovo set di colonne NOT NULL e quindi l’inserimento batch da Y a X e specificare un valore predefinito in per inserire la nuova colonna o l’immissione di un valore di default nella nuova colonna quando si ricrea la tabella X.

    Ho fatto questo tipo di modifica su un tavolo con centinaia di milioni di righe. Ci sono voluti più di un’ora, ma non soffiare il nostro trans log. Quando ho provato a cambiare la colonna NOT NULL con tutti i dati nella tabella ci sono voluti più di 20 ore prima ho ucciso il processo.

    Hai testato solo l’aggiunta di una colonna di riempimento con i dati e l’impostazione della colonna NOT NULL?

    Così, alla fine, non credo che c’è una bacchetta magica.

  5. 3

    selezionare in una nuova tabella e rinominare. Esempio, Aggiungendo la colonna i della tabella 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'

    Dovrebbe essere veloce e di non toccare il registro delle transazioni come l’inserimento in batch potrebbe.
    (Ho appena fatto questo oggi w/a 70 milioni di riga tabella < 2 min).

    Si può avvolgere in una transazione se avete bisogno di essere un’operazione in linea (qualcosa potrebbe cambiare nella tabella tra select e la rinomina).

    • Sto cercando di capire questo. Si inserisce in A_tmp da A_tbl. Ma poi si rinomina dal A_tbl per A_old e quindi rinominare A_old per A_tbl. Non ultimo rinominare essere da A_tmp per A_tbl?
    • sì, ho risolto
  6. 0

    Un’altra tecnica è quella di aggiungere la colonna di una nuova tabella correlata (si Supponga che un uno-di-un rapporto che è possibile applicare dando il FK un indice univoco). È quindi possibile inserire in batch e poi si può aggiungere il join alla tabella ovunque si desidera che i dati vengano visualizzati. Nota vorrei prendere in considerazione solo questo per una colonna che non vorrei utilizzare in ogni query sulla tabella originale o se il record larghezza della mia tabella originale era troppo grande o se stavo aggiungendo più colonne.

Lascia un commento