摘要:TSQL新增字段插入数据表

你知道我们在SSMS中用UI界面新增一个字段到Table时，SQL做了哪一些事呢？

如下面Script可已了解，基本上SQL步骤如下：

（1）建立一个新数据表（新Schema）。

（2）将旧数据表数据Insert到新数据表内。

（3）删除旧数据表。

（4）将新数据表更名成旧数据表。

（5）建立索引。

所以如果您只是要将新字段加到数据表 ‘后面’ ，那就千万别用UI做，直接Alter Table来做。

如果您的数据字段一定要介于某字段跟某字段之间，那就无法用Alter Table来做了。

Alter Table [tablename] Add [NewClo1] Char(1),[NewCol2] Char(10)

如果用UI做，整个流程的Script如下：

/* 为了避免任何可能发生数据遗失的问题，您应该先详细检视此命令码，然后才能在数据库设计工具环境以外的位置执行。*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_indextest
	(
	id int NOT NULL IDENTITY (1, 1),
	id1 int NULL,
	id2 int NULL,
	aa nchar(10) NULL,
	name nchar(100) NULL,
	name2 nchar(100) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_indextest SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_indextest ON
GO
IF EXISTS(SELECT * FROM dbo.indextest)
	 EXEC('INSERT INTO dbo.Tmp_indextest (id, id1, id2, name, name2)
		SELECT id, id1, id2, name, name2 FROM dbo.indextest WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_indextest OFF
GO
DROP TABLE dbo.indextest
GO
EXECUTE sp_rename N'dbo.Tmp_indextest', N'indextest', 'OBJECT' 
GO
CREATE UNIQUE CLUSTERED INDEX Cluster_index ON dbo.indextest
	(
	id
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX NonCluster_index ON dbo.indextest
	(
	id
	) INCLUDE (id1, id2) 
 WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

但我想简化一下流程，因此自己写一个Script（如下），用Select Into来建立新数据表，基本上可以更快且不会产生

Transation Log，以下作法只适用单纯的数据表，如果您的数据表有用Trigger或Replication等其他功能，就得小心一点。

SET XACT_ABORT ON;
BEGIN TRAN
BEGIN TRY
	SELECT 
		id,
		Cast('' as nchar(20)) AS NewCol1,--加入的新字段1
		Cast('' as char(20)) AS NewCol2,--加入的新字段2
		id1,id2,name,name2 
	INTO DesTable 
	FROM SourceTable WITH (HOLDLOCK TABLOCKX);
	
	EXEC sp_rename N'SourceTable',N'SourceTable_old';
	EXEC sp_rename N'DesTable',N'SourceTable';
END TRY
BEGIN CATCH
	ROLLBACK;
	PRINT 'PROCESS FAIL!';
END CATCH 
IF @@TRANCOUNT > 0 COMMIT;

--完成后！注意权限设定及索引建立，都测试OK后Drop掉旧Table

我是ROCK

rockchang@mails.fju.edu.tw

原文:大专栏  TSQL新增字段插入数据表