Tag Info

New answers tagged

0

I finally worked around the problem using SQL Server 2012 sequences. Unfortunately, this won't work with versions prior to 2012. Speed was o.k, one or two minutes for about 20 million IDs to insert. declare @old_max_id int -- get somehow declare @sql nvarchar(2000) set @sql=N'CREATE SEQUENCE INSERT_IDS START WITH ' + str(@old_max_id + 1) + N' INCREMENT BY ...


2

I have had great success with overall perfomance gains by moving the clustered index to an identity, and leaving the GUID as the PK. If you have a GUID as a clustered PK, you are telling SQL to physically sort the data randomly, leading to page splits and fragmentation in the main table as well as in the non-clustered indexes. By getting your database on ...


6

You can do this. WITH T AS (SELECT ISNULL((SELECT MAX(ID) FROM StagingTable), 0) + ROW_NUMBER() OVER (ORDER BY NaturalID) AS New_ID, ID FROM StagingTable WHERE ID IS NULL) UPDATE T SET ID = New_ID So the windowed function is used in the SELECT list but you can still use the result of it to ...


1

Do exactly as Aaron said, or if you are afraid of nulls, or don't want to use COALESCE, perhaps you could do something along these lines: USE tempdb; GO -- Create the table CREATE TABLE dbo.T ( IdentityID INT NOT NULL CONSTRAINT PK_T PRIMARY KEY CLUSTERED IDENTITY(1,1) , CopyOfIdentityID INT NULL ); GO -- create a trigger to copy the values from ...



Top 50 recent answers are included