TA貢獻1752條經驗 獲得超4個贊
IDENTITYsys.syscolpars
SEQUENCEnext value for seq
ALTER TABLE...SWITCH
identityCREATE TABLE dbo.tblFoo (bar INT PRIMARY KEY,filler CHAR(8000),filler2 CHAR(49))INSERT INTO dbo.tblFoo (bar)SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))FROM master..spt_values v1, master..spt_values v2
identityBEGIN TRY;
BEGIN TRANSACTION;
/*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
set the correct seed in the table definition instead*/
DECLARE @TableScript nvarchar(max)
SELECT @TableScript =
'
CREATE TABLE dbo.Destination(
bar INT IDENTITY(' +
CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1) PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)
ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
'
FROM dbo.tblFoo WITH (TABLOCKX,HOLDLOCK)
EXEC(@TableScript)
DROP TABLE dbo.tblFoo;
EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';
COMMIT TRANSACTION;END TRYBEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();END CATCH;INSERT INTO dbo.tblFoo (filler,filler2) OUTPUT inserted.*VALUES ('foo','bar')bar filler filler2----------- --------- ---------10001 foo bar
DROP TABLE dbo.tblFoo
舉報