I have a large table, but not huge (less than 2 million rows on aging hardware), and when adding a non-nullable column to an existing table, I usually follow the structure set out here to avoid problems with the script timing out when our database migrations run on deployment (FYI - This isn't a full text index problem).
So, in summary, I:
- Alter the table and add the column as NULL and do not add a default constraint
- Backfill the column in batches
- Alter the table and change the column to be NOT NULL and add the default constraint
However, in the following case I want to add a new UNIQUEIDENTIFER column and fill it with NEWSEQUENTIALID() rather than NEWID() values.
Without running in batches, my script would look like this:
IF NOT EXISTS ( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Invoice' AND COLUMN_NAME = 'InternalId') BEGIN ALTER TABLE Invoice ADD InternalId UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_Invoice_InternalId] DEFAULT (NEWSEQUENTIALID()) END GO
However if I split this up into batches, and attempt to fill the nullable InternalId with the following:
IF NOT EXISTS ( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Invoice' AND COLUMN_NAME = 'InternalId') BEGIN ALTER TABLE Invoice ADD InternalId UNIQUEIDENTIFIER NULL END DECLARE @MaxId INT, @LoopStart INT, @LoopEnd INT, @LoopSize INT = 50000 SELECT @MaxId = MAX(InvoiceId) FROM Invoice SELECT @LoopStart = MIN(InvoiceId) FROM Invoice SET @LoopEnd = @LoopStart + @LoopSize PRINT 'Updating InternalIds to a new GUID' WHILE @LoopStart <= @MaxId BEGIN -- update internal id UPDATE I SET InternalId = NEWSEQUENTIALID() FROM Invoice I WHERE I.InvoiceId BETWEEN @LoopStart AND @LoopEnd SET @LoopStart = @LoopEnd + 1 SET @LoopEnd = @LoopEnd + @LoopSize END IF EXISTS ( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Invoice' AND COLUMN_NAME = 'InternalId' AND IS_NULLABLE = 'YES') BEGIN ALTER TABLE Invoice ALTER COLUMN InternalId UNIQUEIDENTIFIER NOT NULL END IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE name = 'DF_Invoice_InternalId') BEGIN ALTER TABLE Invoice ADD CONSTRAINT [DF_Invoice_InternalId] DEFAULT ((NEWSEQUENTIALID())) FOR [InternalId] END
I get the following error:
Msg 302, Level 16, State 0, Line 40 The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.
Any tips on how to work around this? Or am I over-thinking this?
The reason for doing this change is to expose the Sequential IDs (
InternalId or could be called
PublicId) externally in an API, as a replacement for the current sequential numeric Ids (InvoiceId). The numeric Id (the primary key) should have been kept internal, as it exposes a sequential and guessable internal value. The Sequential GUID is still sequential, but also not so easily guessable. To illustrate the point, I'm doing something like this, but @First is being provided via an API call. It is used for polling and processing new invoices using a watermarking process.
CREATE TABLE #Test ( Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, DateCreated DATETIME NOT NULL DEFAULT(GETDATE()), Code NVARCHAR(50) NOT NULL ) DECLARE @Id INT DECLARE @NO_OF_CHARS INT = 10 SET @Id = 1 WHILE @Id <= 12000 BEGIN INSERT INTO #Test (Code) VALUES (SUBSTRING (REPLACE(CONVERT(VARCHAR(40), NEWID()), '-',''), 1, @NO_OF_CHARS)) SET @Id = @Id + 1 END ALTER TABLE #Test ADD InternalId UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWSEQUENTIALID()) DECLARE @First UNIQUEIDENTIFIER SELECT * FROM #Test SELECT @First = InternalId FROM #Test WHERE Id = 1 SELECT * FROM #Test WHERE InternalID > @First DROP TABLE #Test