What will be best way to modify the column data type in sql server?

alter column datatype in sql server
sql alter column size varchar
alter table add multiple columns
alter table add column mysql
sql data types
convert column sql
sql server alter multiple columns
alter table modify column mysql

I have a table which holds 64million records, I want to modify the VARCHAR types to NVARCHAR for some of the columns.

The normal alter statement is taking 5hrs to complete. which I can't run in production.

ALTER TABLE [dbo].[Sample] ALTER COLUMN [Name] NVARCHAR(255) NOT NULL.

Any other method which will be faster than this.

No indexes, no constraints, no triggers, it a simple table holds 64million data.

Also, I can't do/create a dummy table with NVARCHAR types and load data to the dummy table, rename the actual table or delete it and rename the dummy table to the actual one.

You might find it faster to store the data in a temporary table, truncate and re-insert the data:

select *
into temp_sample
from sample;

truncate table sample;

alter table [dbo].[Sample] alter column [Name] NVARCHAR(255) NOT NULL;

insert into sample
    select *
    from temp_sample;

What will be best way to modify the column data type in sql server , You might find it faster to store the data in a temporary table, truncate and re-​insert the data: select * into temp_sample from sample; truncate  To modify the data type of a column. In Object Explorer, right-click the table with columns for which you want to change the scale and click Design. Select the column for which you want to modify the data type. In the Column Properties tab, click the grid cell for the Data Type property and choose a new data type from the drop-down list.

I have never tried this with sql-server but for an oracle db you can use a PARALLEL command https://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

ALTER TABLE <table_name> PARALLEL 32;

I think sql-server uses MAXDOP but I am not to sure how it fully works SQL Server : ALTER TABLE ADD COLUMN MAXDOP=1

SQL ALTER TABLE Statement, The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server,  Best way to do this is script out the drop and create of the replication, run the drop, alter table, create in one go and then start the snapshot job. Shamless self promotion - read my blog http

Try the below. Add a new column with NVARCHAR(255) and update the column incrementally. This will not give any burden to the memory.

ALTER TABLE [dbo].[YourTable] ADD [NewCol] NVARCHAR(255) NULL;
GO

WHILE 1=1
BEGIN
    UPDATE TOP (5000) [dbo].[YourTable]
    SET NewCol = OldCol
    WHERE NewCol IS NULL
    IF @@ROWCOUNT = 0
        BREAK
END
ALTER TABLE [dbo].[YourTable] DROP COLUMN [OldCol]
EXEC sp_rename '[dbo].[YourTable].NewCol','OldCol'

Modify Columns (Database Engine), Modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the  Changing data type of any column. Before changing the data type of a column make sure that data would not be compromised while converting to new data type. This task may be performed by using the following T-SQL command. In our case we would change data type VARCHAR(50) to NVARCHAR(50).

How to Change a Column's Data Type in SQL Server (T-SQL , How to Change a Column's Data Type in SQL Server (T-SQL) put a lot of thought into what data type each and every column should accept. Despite our best efforts at trying to foresee every possible scenario that may hit  The SQL UPDATE statement is used to change column values. Though an update statement can modify columns data from many sources, such as literal values or other query results, the basic format is the same. There are three components to an UPDATE statement: The table you wish to change. The column you wish to change.

SQL, Sometimes we need to change the data type of a column. To do this, we use the ALTER TABLE Modify Column command. For Oracle and MySQL, the SQL  To modify the data type of a column, you use the following statement: ALTER TABLE table_name ALTER COLUMN column_name new_data_type( size ); The new data type must be compatible with the old one, otherwise, you will get a conversion error in case the column has data and it fails to convert.

Best practices for SQL Server database ALTER table operations, In this tip I cover which tool is best for altering tables in SQL Server with minimum PROPERTY for a column; Changing data type of any column where table drop and recreation needs to take place in SSMS designer. If you are using SQL Server, you can make the SQL Server Management Studio do the work for you! Bring up your table structure (right-click on the table column and select "Modify") Make all of your changes (if the column transformation is illegal, just add your new column - you'll patch it up in a moment).

Comments
  • The best way could be create an additional column and do data update incrementally. And alias the column
  • Could you please explain why you can't use a second table when you do the conversion? It might be important to know about the limitations you have when figuring out a way to do this.
  • You could add a new column, populate it from the old one, delete the old.
  • For completeness: your question isn't tagged with SQL Server 2016, but it's worth mentioning that SQL Server 2016 introduces ONLINE = ON for ALTER COLUMN operations. The operation would still take 5 hours (or even longer) but the table would remain available. Of course, whether or not upgrading is an option is another matter.
  • @MikaelEriksson - my DBA's are not ok for that approach
  • Certain ALTER TABLE operations do support parallelism and MAXDOP, but ALTER COLUMN isn't one of them.