oracle更新sqlserver表字段,Sqlserver修改在线表的表字段类型

原本很多字段都是nvarchar类型，因为nvarchar类型的字段无法添加索引，所以要在线把表的字段类型修改为varchar。

执行脚本如下：

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_events

(

id int NOT NULL IDENTITY (1, 1),

transaction_id varchar(255) NULL,

trans_no varchar(255) NULL,

broker varchar(32) NULL,

code varchar(100) NULL,

consumer_id varchar(255) NULL,

consumer_code varchar(100) NULL,

consumer_name nvarchar(255) NULL,

description varchar(100) NULL,

comment nvarchar(255) NULL,

execution_group varchar(50) NULL,

input_ts bigint NOT NULL,

output_ts bigint NOT NULL,

provider_id varchar(100) NULL,

provider_code varchar(50) NULL,

provider_name nvarchar(255) NULL,

request_body nvarchar(MAX) NULL,

request_header nvarchar(MAX) NULL,

request_ts bigint NOT NULL,

response_body nvarchar(MAX) NULL,

response_header nvarchar(MAX) NULL,

response_ts bigint NOT NULL,

service_id varchar(50) NULL,

service_code varchar(50) NULL,

service_name nvarchar(255) NULL,

service_version varchar(50) NULL,

service_url varchar(255) NULL,

category_name varchar(50) NULL,

source varchar(50) NULL,

type varchar(50) NULL

)  ON [PRIMARY]

TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE dbo.Tmp_events SET (LOCK_ESCALATION = TABLE)

GO

SET IDENTITY_INSERT dbo.Tmp_events ON

GO

IF EXISTS(SELECT * FROM dbo.events)

EXEC('INSERT INTO dbo.Tmp_events (id, transaction_id, trans_no, broker, code, consumer_id, consumer_code, consumer_name, description, comment, execution_group, input_ts, output_ts, provider_id, provider_code, provider_name, request_body, request_header, request_ts, response_body, response_header, response_ts, service_id, service_code, service_name, service_version, service_url, category_name, source, type)

SELECT id, CONVERT(varchar(255), transaction_id), CONVERT(varchar(255), trans_no), CONVERT(varchar(32), broker), CONVERT(varchar(100), code), CONVERT(varchar(255), consumer_id), CONVERT(varchar(100), consumer_code), CONVERT(nvarchar(255), consumer_name), CONVERT(varchar(100), description), CONVERT(nvarchar(255), comment), CONVERT(varchar(50), execution_group), input_ts, output_ts, CONVERT(varchar(100), provider_id), CONVERT(varchar(50), provider_code), CONVERT(nvarchar(255), provider_name), request_body, request_header, request_ts, response_body, response_header, response_ts, CONVERT(varchar(50), service_id), CONVERT(varchar(50), service_code), CONVERT(nvarchar(255), service_name), CONVERT(varchar(50), service_version), CONVERT(varchar(255), service_url), CONVERT(varchar(50), category_name), CONVERT(varchar(50), source), CONVERT(varchar(50), type) FROM dbo.events WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT dbo.Tmp_events OFF

GO

DROP TABLE dbo.events

GO

EXECUTE sp_rename N'dbo.Tmp_events', N'events', 'OBJECT'

GO

ALTER TABLE dbo.events ADD CONSTRAINT

PK__esb_even__3213E83FA9B6FB29 PRIMARY KEY CLUSTERED

(

id

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

可以看出，sqlserver进行修改表字段类型其实就是建立一个新表，把老表的数据转换类型后插入到新表，然后drop掉老表。

再对新表的字段添加索引即可。

因为整个过程是在一个事务中进行的，所以会锁表。

测试下来。

在非业务繁忙期的一个在线表369W条左右的数据量，整个在线修改表字段类型消耗了14分32秒。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22996654/viewspace-2147690/，如需转载，请注明出处，否则将追究法律责任。