Shulou(Shulou.com)05/31 Report--

This article introduces how to modify the field type of the online table in Sqlserver. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Originally, many fields are of type nvarchar, but since fields of type nvarchar cannot be indexed, you need to change the field type of the table to varchar online.

The execution script is as follows:

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, transaction_id), CONVERT (varchar, trans_no), CONVERT (varchar, broker), CONVERT (varchar, code), CONVERT (varchar, consumer_id), CONVERT (varchar, consumer_code), CONVERT (nvarchar, consumer_name), CONVERT (varchar, description), CONVERT (nvarchar, comment), CONVERT (varchar (50), execution_group), input_ts, output_ts, CONVERT Provider_id), CONVERT (varchar (50), provider_code), CONVERT (nvarchar, 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 (50), service_name), CONVERT (varchar (50), service_version), CONVERT (varchar (50), 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 Naturdbo.Tmpacks 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

As you can see, sqlserver to modify the table field type is to create a new table, convert the data of the old table and insert it into the new table, and then drop the old table.

Then add an index to the fields of the new table.

Because the whole process takes place in a single transaction, the table is locked.

Test it.

During the non-busy period, it takes 14 minutes and 32 seconds to modify the field type of an online table with about 369W items of data.

About how to modify the table field type of the online table in Sqlserver is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.