Geek Answers Handbook

SQL column data type with lots of data

I have a table containing millions of records.

I change one data type of a column to another (money to decimal)

Noticing that it takes a long time to execute the alter statement. Probably due to the fact that there is a lot of data.

Is there a way to increase performance for this scenario?

+5
source share
3 answers

All millions of rows must be changed simultaneously in one transaction

Another option is to create a new table, insert into batches, delete the old table, rename the new table. However, this may take longer.

+6
source

- , temp, temp.

CREATE TABLE dbo.tmp_MyTable 
   (ID Integer, Name varchar (100), MyChangedField decimal (8,2))

INSERT INTO dbo.tmp_MyTable SELECT * FROM dbo.MyTable

DROP TABLE dbo.MyTable

EXECUTE sp_rename N'dbo.tmp_MyTable', N'MyTable', 'OBJECT'

, . , / , .

- SQL Server Management Studio script, , .

+3

- , CAST(), , ?

0

More articles:


All Articles