Instant SQL Masterpieces
Some common operations if done in a classic way will take ages on big tables. That is why we do magic of instant changes, no matter how big the tables are. We use instant, metadata-level commands, for example: SWITCH, TRANSFER, TRUNCATE PARTITION, partitioning to 1 unlimited partition and back. Sometimes we copy a small amount of rows to keep, and some other clever tricks.
Change IDENTITY increment
Unfortunately, ALTER TABLE ALTER COLUMN does not allow changing of any identity property. DBCC CHECKIDENT does not have option to change the increment: it can only change the seed using the RESEED option. What works is creating a cloned table with a desired increment, and switching the rows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- DROP TABLE IF EXISTS dbo.Test, guest.Test CREATE TABLE dbo.Test(x INT IDENTITY(1,1)) INSERT INTO dbo.Test DEFAULT VALUES -- x=1 -- ALTER TABLE dbo.Test ALTER COLUMN x INT IDENTITY(3, 3) -- does not work unfortunately -- Identical table, but different INCREMENT CREATE TABLE guest.Test(x INT IDENTITY(2,2)) -- (max+1), 2 BEGIN TRAN ALTER TABLE dbo.Test SWITCH TO guest.Test -- works! DROP TABLE dbo.Test ALTER SCHEMA dbo TRANSFER guest.Test COMMIT -- Done! -- Test INSERT INTO dbo.Test DEFAULT VALUES -- x=2 SELECT * FROM dbo.Test INSERT INTO dbo.Test DEFAULT VALUES -- x=4 SELECT * FROM dbo.Test |
