SQL Server 2005: utilizzo di sequenze generate anziché colonne Identity?

Sto davvero pensando di passare da colonne Identity in SQL Server 2005 e andare con qualche tipo di generatore di sequenze (per quanto ne so non c’è nulla di costruito nel 2005 per fare questo, potrebbe arrivare nel 2011?) Per ottenere ID univoci per le file inserite.

Esiste un modello di implementazione o best practice per questo? Incontrerò problemi di blocco? Quali sono i lati negativi rispetto all’utilizzo delle colonne Identity?

Sì, SQL 11 ha oggetti SEQUENCE, vedere SQL Server v.Next (Denali): Utilizzo di SEQUENCE .

La creazione di sequenze manuali è ansible, ma non consigliata. Il trucco per fare un generatore di sequenze è usare UPDATE WITH OUTPUT su una tabella di sequenze. Ecco lo pseudo-codice:

CREATE TABLE Sequences ( Name sysname not null primary key, Sequence bigint not null default 0); GO CREATE PROCEDURE sp_getSequence @name sysname, @value bigint output AS UPDATE Sequences SET Sequence = Sequence + 1 OUTPUT @value = INSERTED.Sequence WHERE Name = @name; GO 

Ho omesso alcuni dettagli, ma questa è l’idea generale. Tuttavia, c’è un grosso problema: qualsiasi transazione che richiede il valore successivo in una sequenza bloccherà quella sequenza fino a quando non si impegna, perché posizionerà un blocco di aggiornamento sul valore della sequenza. Ciò significa che tutte le transazioni devono essere serializzate una dopo l’altra quando si inseriscono valori e il degrado delle prestazioni risulta insopportabile nelle distribuzioni di produzione reali.

Preferisco di gran lunga che tu rimanga con i tipi di id quadro. Anche se non sono perfetti, sono di gran lunga migliori di quelli che puoi ottenere da soli.

Il modo in cui risolvo questo problema era una tabella ‘Sequenze’ che memorizza tutte le mie sequenze e una procedura memorizzata ‘nextval’.

Tabella Sql:

 CREATE TABLE Sequences ( name VARCHAR(30) NOT NULL, value BIGINT DEFAULT 0 NOT NULL, CONSTRAINT PK_Sequences PRIMARY KEY (name) ); 

PK_Sequences è usato solo per essere sicuro che non ci saranno mai sequenze con lo stesso nome.

Procedura memorizzata Sql:

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'nextVal') AND type in (N'P', N'PC')) DROP PROCEDURE nextVal; GO CREATE PROCEDURE nextval @name VARCHAR(30) AS BEGIN DECLARE @value BIGINT BEGIN TRANSACTION UPDATE Sequences SET @value=value=value + 1 WHERE name = @name; -- SELECT @value=value FROM Sequences WHERE [email protected] COMMIT TRANSACTION SELECT @value AS nextval END; 

Inserisci alcune sequenze:

 INSERT INTO Sequences(name, value) VALUES ('SEQ_Workshop', 0); INSERT INTO Sequences(name, value) VALUES ('SEQ_Participant', 0); INSERT INTO Sequences(name, value) VALUES ('SEQ_Invoice', 0); 

Finalmente ottieni il prossimo valore di una sequenza,

 execute nextval 'SEQ_Participant'; 

Qualche codice c # per ottenere il valore successivo dalla tabella Sequence,

 public long getNextVal() { long nextval = -1; SqlConnection connection = new SqlConnection("your connection string"); try { // Connect and execute the select sql command. connection.Open(); SqlCommand command = new SqlCommand("nextval", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@name", SqlDbType.NVarChar).Value = "SEQ_Participant"; nextval = Int64.Parse(command.ExecuteScalar().ToString()); command.Dispose(); } catch (Exception) { } finally { connection.Dispose(); } return nextval; }