Home > Worst practices: cyclic-drop and create a temporary column

Worst practices: cyclic-drop and create a temporary column

Sergio Govoni 1 Novembre 2010 Senza categoria 2,330 Views

La vista di sistema sys.columns, come citano i BOL, restituisce una riga per ogni colonna di un oggetto contenente colonne, ad esempio viste o tabelle. I tipi di oggetti che possono contenere colonne sono elencati di seguito:

  • Funzioni assembly con valori di tabella
  • Funzioni SQL inline con valori di tabella
  • Tabelle interne
  • Tabelle di sistema
  • Funzioni SQL con valori di tabella
  • Tabelle utente
  • Viste

In particolare la colonna column_id della vista sys.columns espone l’identificativo (ID) univoco assegnato ad ogni colonna presente all’interno di un oggetto, che da questo momento in poi ammettiamo essere una tabella.

 

Scenario

Alcuni giorni fa, ho ricevuto da un cliente, una segnalazione di errore sul programma che gestisce la generazione dei documenti di trasporto (DdT), programma scritto ad-hoc per il cliente e utilizzato da tempo con profitto insieme a SQL Server 2005.

La segnalazione pervenuta è la seguente: In fase di generazione nuovo documento di trasporto il programma restituisce all’operatore il messaggio di errore:

Messaggio 1714, livello 16, stato 1, riga 2
Istruzione ALTER TABLE non riuscita. ID di colonna univoci esauriti per la tabella ‘ShippingHeader’.

Error Message 1714

Figura 1 – Messaggio 1714

Per la memorizzazione dei documenti di trasporto, il programma utilizza la tabella dbo.ShippingHeader relazionata con le tabelle dbo.Product e dbo.Customer. Il seguente frammento di codice T-SQL permette di creare una versione di esempio (con struttura semplificata) di queste tabelle nel database di sistema [tempdb].

use [tempdb];
go

— dbo.Product
if OBJECT_ID(‘dbo.Product’, ‘U’) is not null
  drop table dbo.Product;
go

create table dbo.Product
(
  ProductID varchar(25) not null
  ,SafetyStockLevel smallint not null
  ,Size varchar(5) null
  ,ModifiedDate datetime not null default getdate()
  ,Status bit not null default 1
  ,constraint PK_Product primary key(ProductID)
);
go

if (object_id(‘dbo.Customer’, ‘U’) is not null)
  drop table dbo.Customer;
go

— dbo.Customer
create table dbo.Customer
(
  CustomerID int identity(1, 1) not null primary key
  ,CustomerName varchar(40) not null
);
go

— dbo.ShippingHeader
if OBJECT_ID(‘dbo.ShippingHeader’, ‘U’) is not null
  drop table dbo.ShippingHeader;
go

create table dbo.ShippingHeader
(
  ShippingID integer identity(1, 1) not null
  ,ProductID varchar(25) not null
    foreign key (ProductID)
      references dbo.Product(ProductID)
  ,ShipDate datetime default getdate() not null
  ,ShipNumber as (isnull(N’SO’ + convert([nvarchar](23), [ShippingID], 0), N’*** error ***’))
  ,CustomerID int default 1 not null
    foreign key (CustomerID)
      references dbo.Customer(CustomerID)
  ,ShipName varchar(20) default ‘name’
  ,ShipAddress varchar(40) default ‘address’
  ,ShipCity varchar(20) default ‘city’
  ,ShipPostalCode varchar(20) default ‘postal code’
  ,ShipCountry varchar(20) default ‘country’
  ,DeliveryDate datetime default (getdate() + datepart(ss, getdate())) not null
  primary key(ShippingID)
);
go

Dopo alcune verifiche, ci accorgiamo che il software di gestione delle spedizioni, ad ogni elaborazione (= ad ogni generazione di un DdT) esegue (in un altro linguaggio di programmazione) il codice equivalente al seguente frammento T-SQL:

/*
  Frammento di codice T-SQL eseguito, ad ogni elaborazione, dal software di
  gestione delle spedizioni
*/

begin
  …
  — add temporary field
  alter table dbo.ShippingHeader add testfield integer

  …
  …
  …

  — update value for particular record
  update
    dbo.ShippingHeader
  set
    testfield = @value
  where
    (<condizione>)

  …
  …
  …

  — using value
  select
    testfield
  from
    dbo.ShippingHeader
  where
    (<condizione>)

  …
  …
  …

  — drop column
  alter table dbo.ShippingHeader drop column testfield

  …

end;
go

La user-table dbo.ShippingHeader rappresenta l’oggetto che memorizza i documenti di trasporto emessi dall’azienda, su questa tabella ad ogni elaborazione viene creata e distrutta la colonna temporanea TestField (usata dall’elaborazione stessa). Dopo alcuni mesi di lavoro, è stato raggiunto il limite massimo degli identificativi univoci (ID) assegnabili in fase di creazione di una nuova colonna. Non è quindi più possibile aggiungere colonne alla tabella dbo.ShippingHeader, SQL Server restituisce il messaggio di errore indicato in precedenza perché il contatore colid della tabella di sistema sys.syscolpars non può essere incrementato in quanto ha raggiunto il limite massimo di valori rappresentabili.

La colonna colid, nella versione 2005 di SQL Server, è di tipo smallint e con questo tipo di dato si possono rappresentare (con segno positivo) 2^15-1 elementi, ossia 32.767… dopo aver iniziato ad utilizzare il programma di gestione delle spedizioni, il cliente ha quindi inserito più di 32.767 documenti di trasporto.

La situazione è riproducibile utilizzando la stored procedure dbo.usp_leave_gaps_colid() definita di seguito:

— Create procedure dbo.usp_leave_gaps_colid
if OBJECT_ID(‘dbo.usp_leave_gaps_colid’) is not null
  drop procedure dbo.usp_leave_gaps_colid;
go

create procedure dbo.usp_leave_gaps_colid(@tablename as sysname)
as begin
  begin try
    while (1=1)
    begin
      begin transaction
      exec(‘alter table ‘ + @tablename + ‘ add testfield integer’);
      commit
     
      begin transaction
      exec(‘alter table ‘ + @tablename + ‘ drop column testfield’);
      commit
    end
  end try
  begin catch
    rollback
    return
  end catch
end;
go

Con un po’ di pazienza, ed eseguendo la stored procedure dbo.usp_leave_gaps_colid a cui passiamo come parametro il nome della tabella incriminata … si raggiunge il limite degli identificativi assegnabili alla colonna colid della tabella di sistema sys.syscolpars:

exec dbo.usp_leave_gaps_colid @tablename = ‘ShippingHeader’;
go

 

Soluzione

Dopo aver modificato il software di gestione delle spedizioni, in modo che utilizzi un’altra logica di elaborazione che non prevede l’ADD e il DROP di una colonna temporanea ad ogni esecuzione, ci siamo subito posti il problema di come poter applicare futuri aggiornamenti (aggiunta di nuove colonne) sulla tabella dbo.ShippingHeader.

Dopo alcune verifiche, abbiamo adottato la soluzione che consiste nel droppare e ricreare la tabella dbo.ShippingHeader. Ricreando la tabella, il contatore colid (column_id nella vista sys.columns) verrà resettato, l’assegnazione dei prossimi identificativi univoci (ID) ripartirà dal valore (+1) ottenuto con il seguente comando T-SQL:

use [tempdb];
go

select
  MAX(c.column_id) as MAX_ColID
from
  sys.columns as c
where
  (c.object_id = object_id(‘dbo.ShippingHeader’));
go

Per ricreare la tabella abbiamo individuato questi step:

  1. Duplicazione della tabella dbo.ShippingHeader
  2. Eliminazione di tutte le integrità referenziali definite su dbo.ShippingHeader
  3. Eliminazione della tabella dbo.ShippingHeader
  4. Rinomina (in dbo.ShippingHeader) della tabella precedentemente copiata
  5. Applicazione delle integrità referenziali

Duplicazione della tabella dbo.ShippingHeader

Per duplicare (copiando i dati) la tabella dbo.ShippingHeader, sul database tempdb, abbiamo utilizzato il wizard Importazione/Esportazione guidata SQL Server. Completata la procedura, il database tempdb conterrà le tabelle illustrate in figura 2.

Tempdb

Figura 2 – tempdb dopo la duplicazione della tabella dbo.ShippingHeader copiata in dbo.ShippingHeader2

Eliminazione di tutte le integrità referenziali definite su dbo.ShippingHeader

Per individuare facilmente i comandi di eliminazione delle integrità referenziali definite sulla tabella abbiamo utilizzato le funzioni di scripting di SQL Server, in particolare abbiamo selezionato la funzione di generazione del codice per le istruzioni DROP e CREATE, come illustrato in figura 3.

Funzioni di scripting di SQL Server

Figura 3 – Funzioni di scripting di SQL Server

In questo secondo step abbiamo utilizzato le istruzioni ALTER TABLE DROP… come illustrato nel seguente frammento di codice T-SQL:

USE [tempdb] GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__ShippingH__Custo__0BC6C43E]’) AND parent_object_id = OBJECT_ID(N'[dbo].[ShippingHeader]’))
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [FK__ShippingH__Custo__0BC6C43E]

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__ShippingH__Produ__08EA5793]’) AND parent_object_id = OBJECT_ID(N'[dbo].[ShippingHeader]’))
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [FK__ShippingH__Produ__08EA5793]

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__ShippingH__ShipD__09DE7BCC]’) AND type = ‘D’)
BEGIN
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [DF__ShippingH__ShipD__09DE7BCC] END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__ShippingH__Custo__0AD2A005]’) AND type = ‘D’)
BEGIN
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [DF__ShippingH__Custo__0AD2A005] END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__ShippingH__ShipN__0CBAE877]’) AND type = ‘D’)
BEGIN
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [DF__ShippingH__ShipN__0CBAE877] END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__ShippingH__ShipA__0DAF0CB0]’) AND type = ‘D’)
BEGIN
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [DF__ShippingH__ShipA__0DAF0CB0] END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__ShippingH__ShipC__0EA330E9]’) AND type = ‘D’)
BEGIN
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [DF__ShippingH__ShipC__0EA330E9] END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__ShippingH__ShipP__0F975522]’) AND type = ‘D’)
BEGIN
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [DF__ShippingH__ShipP__0F975522] END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__ShippingH__ShipC__108B795B]’) AND type = ‘D’)
BEGIN
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [DF__ShippingH__ShipC__108B795B] END

GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__ShippingH__Deliv__117F9D94]’) AND type = ‘D’)
BEGIN
ALTER TABLE [dbo].[ShippingHeader] DROP CONSTRAINT [DF__ShippingH__Deliv__117F9D94] END

GO

Eliminazione della tabella dbo.ShippingHeader

Dopo aver eliminato le integrità referenziali, è possibile eliminare la tabella dbo.ShippingHeader, nello script generato in precedenza da SQL Server troviamo anche il seguente comando T-SQL:

USE [tempdb] GO

/****** Object:  Table [dbo].[ShippingHeader]    Script Date: 08/10/2010 23:44:02 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShippingHeader]’) AND type in (N’U’))
DROP TABLE [dbo].[ShippingHeader] GO

Rinomina (in dbo.ShippingHeader) della tabella precedentemente copiata

Per rinominare la tabella dbo.ShippingHeader2 in dbo.ShippingHeader, abbiamo utilizzato la stored procedure di sistema sp_rename che consente di modificare il nome di un oggetto creato dall’utente nel database corrente. Il seguente comando T-SQL illustra l’utilizzo di sp_rename:

use [tempdb];
go

exec sp_rename
  @objname = ‘dbo.ShippingHeader2’
  ,@newname = ‘ShippingHeader’;
go

Applicazione delle integrità referenziali

L’ultimo step consiste nel ripristinare la PRIMARY KEY e le integrità referenziali (compresi i DEFAULT) nell’attuale dbo.ShippingHeader. I comandi di creazione delle integrità referenziali sulla nuova tabella dbo.ShippingHeader sono stati precedentemente individuati e scriptati (nel secondo step), vengono ora utilizzati come illustrato di seguito:

USE [tempdb]

GO

ALTER TABLE [dbo].[ShippingHeader] ADD PRIMARY KEY([CustomerID])
GO

ALTER TABLE [dbo].[ShippingHeader] WITH CHECK ADD FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customer] ([CustomerID])
GO

ALTER TABLE [dbo].[ShippingHeader] WITH CHECK ADD FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ProductID])
GO

ALTER TABLE [dbo].[ShippingHeader] ADD  DEFAULT (getdate()) FOR [ShipDate] GO

ALTER TABLE [dbo].[ShippingHeader] ADD  DEFAULT ((1)) FOR [CustomerID] GO

ALTER TABLE [dbo].[ShippingHeader] ADD  DEFAULT (‘name’) FOR [ShipName] GO

ALTER TABLE [dbo].[ShippingHeader] ADD  DEFAULT (‘address’) FOR [ShipAddress] GO

ALTER TABLE [dbo].[ShippingHeader] ADD  DEFAULT (‘city’) FOR [ShipCity] GO

ALTER TABLE [dbo].[ShippingHeader] ADD  DEFAULT (‘postal code’) FOR [ShipPostalCode] GO

ALTER TABLE [dbo].[ShippingHeader] ADD  DEFAULT (‘country’) FOR [ShipCountry] GO

ALTER TABLE [dbo].[ShippingHeader] ADD  DEFAULT (getdate()+datepart(second,getdate())) FOR [DeliveryDate] GO

 

Conclusioni

In SQL Server 2005 (per fortuna) non c’è modo di resettare, con un UPDATE, il valore della colonna column_id della vista sys.columns, l’unico modo è ricreare la tabella per la quale vogliamo venga resettato il contatore. SQL Server 2008 ha aumentato il limite di identificativi univoci portandolo a 500K … comunque anche con SS2008 se non avessimo modificato il programma sarebbe stata solo questione di tempo, ci saremmo ritrovati ancora nella stessa situazione.

L’utilizzo ciclico di una colonna temporanea creata e distrutta, su una user-table, ad ogni elaborazione di una procedura non è una buona pratica di programmazione.

Lo scenario descritto rappresenta una semplificazione di un caso reale, non abbiamo trattato la rigenerazione di eventuali indici definiti nella tabella dbo.ShippingHeader.

Non vorrei ribadire l’ovvio, ma prima di eseguire la procedura descritta in un ambiente di produzione è necessario averla provata in modo completo su un backup del DB in ambiente di test.

 

Approfondimento

Qualora esistano tabelle (o oggetti) che dipendono dalla tabella da ricreare (FOREIGN KEY che referenziano la PRIMARY KEY della tabella da ricreare) sarà necessario eseguire anche i seguenti passi:

  • Eliminazione delle dipendenze dalla tabella da ricreare
  • Ripristino delle dipendenze

Nello script Oggetti che dipendono da una tabella: DROP e ADD FOREIGN KEY CONSTRAINT viene illustrato come individuare facilmente i comandi di eliminazione e ripristino delle FOREIGN KEY sulle tabelle che dipendono dalla tabella data.

 

Risorse esterne

Microsoft Connect, There is no way to reset the column_id value

 

Tags

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Optimized Locking in Azure SQL Database: Concorrenza senza limiti!

Nel panorama delle applicazioni moderne, dove scalabilità e concorrenza sono elementi fondamentali, mantenere prestazioni elevate …

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

− one = three

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

User Group Italiano SQL Server - Via Del Capanno 26, Lodi - P.IVA: 03015800968
Tutti i contenuti (c) UGISS, 2025