I just read in a forum about a user who want to replikate a table, but the table doesn’t have a PK. The table is pretty large, and having the table not available while adding the PK is undesireable. The table has a clustered index already, and there are other columns which are known to be unique (presence of unique indexes).
What I wanted to test is whether we can just add the PK constraint using the ONLINE option. Show answer is “yes”. We can’t turn a unique index into a PK using some meta-data only operation, unfortunately. That would be the easiest step. But we can add a unique constraint using the ONLINE option – there’s even an example syntax for this in BOL. We can then remove the pre-existing unique index using ONLINE. Since we are using ONLINE, we need to be on Enterprise or Developer Edition.
I wanted to test this, and below is my test script:
USE tempdb
SET NOCOUNT ON
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(c1 INT NOT NULL, c2 CHAR(100))
CREATE UNIQUE CLUSTERED INDEX x ON t(c1)
INSERT INTO t
SELECT TOP(5000000) ROW_NUMBER() OVER(ORDER BY a.id), 'x'
FROM syscolumns AS a
CROSS JOIN syscolumns AS b
CROSS JOIN syscolumns AS c
GO
-----------------------------------------------------
--Now try to add a PK "online"...:
-----------------------------------------------------
--Add a nullable identity?
ALTER TABLE t ADD c3 INT IDENTITY NULL
--Msg 8147, Level 16, State 1, Line 1
--Could not create IDENTITY attribute on nullable column 'c3', table 't'.
GO
--Add a PK using ONLINE?
--Prepare a new connection with following INSERTs
--to verify it can run simultaneously:
--INSERT INTO t(c1, c2) VALUES(5000001, 't')
--INSERT INTO t(c1, c2) VALUES(5000001, 't')
--INSERT INTO t(c1, c2) VALUES(5000002, 't')
--GO
--INSERT INTO t(c1, c2) VALUES(5000003, 't')
--Above prepared? OK, execute below and jump to
--other window to verify it is online
ALTER TABLE t ADD CONSTRAINT PK_t PRIMARY KEY NONCLUSTERED (c1) WITH(ONLINE = ON)
GO
--Verify the indexes using my own sp_indexinfo
EXEC sp_indexinfo 't'
Just installed the zo88app. Looks promising, will update the review once I play sometime! So far it is a good app. zo88app
777betcasino… That’s the ticket! If you are looking for a new online casino, I highly recommend it. This platform has awesome bonuses! 777betcasino
U888U888COOL on .org is also pretty cool! More site options are always good! Check both versions for your play. u888u888cool
Sevengame is my go-to for a quick fix of gaming fun. Got some cool games I haven’t seen anywhere else. Worth a look see what thrills you! sevengame