How To Change The Data Type Of A Column In Sql

10 Answers 10

                ALTER TABLE TableName  ALTER Column ColumnName NVARCHAR(200) [NULL | NOT NULL]                              

EDIT As noted Nil/NOT Nix should have been specified, encounter Rob's answer as well.

answered Mar 9, 2009 at sixteen:07

cmsjrcmsjr

53.2k 10 gold badges 68 argent badges 62 bronze badges

vi

• Why should it be specified? What is the benefit? Everything I want to leave as it is I don't take to mention, I think.

  Mar 10, 2017 at seven:48

• @TheincredibleJan That would be cool, only unfortunately it doesn't piece of work that way. If you lot event an Change TABLE TableName Change COLUMN ColumnName command to change an existing column that is [NOT Zippo], and you don't explicitly specify it, information technology volition be [NULL] afterwards, every bit that'south the default.

  Apr 19, 2017 at 6:10

• This simply works if yous don't have constraints and indexes on that column, otherwise, you need to driblet everything and recreate it, which is boring and grunt work, especially if you accept many foreign references on that cavalcade. e.thou. when changing from tinyint to int.

  Jun 9, 2018 at xiv:eighteen

• Careful if you have an IDE open (like SSMS). Even with my Designer Tab closed on the Table I was Altering (after running the script and right-clicking on the Table to select "Design") information technology still showed the old Information Types! It was only after endmost ALL of my Tabs in Management Studio and opening the Design View again that information technology finally showed the updated DataType. Very scary, and so be careful (it may exist a cache-issues that is fixed by now or i MS never bothers fixing). For those wondering why I ran T-SQL when I use Pattern-View, I wanted to alter my DataTypes to SysName (which SSMS does not permit).

  April 10, 2019 at ii:37

• I had no luck with these commands in XAMPP.ALTER TABLE table Change Cavalcade column datatype worked for me.

  May vi, 2019 at 14:28

Don't forget nullability.

                ALTER TABLE <schemaName>.<tableName> ALTER COLUMN <columnName> nvarchar(200) [Zero|Non Zero]                              

answered Mar 11, 2009 at xvi:38

Rob GarrisonRob Garrison

six,684 4 gilt badges 23 silver badges 23 bronze badges

3

• What is the matter with nullability? If I don't want to change it - what is the benefit in setting information technology once again?

  Mar 10, 2017 at 7:50

• ^ It will default to NULL later the alter tabular array statement finishes executing without explicitly defining what it should be.

  Jul 31, 2017 at xx:29

• @sc305495 To be exact, it will default to whetever your ANSI_NULL_DEFAULT settings is.

  Apr 15, 2019 at 12:17

Employ the Alter table argument.

              Alter table TableName Alter Column ColumnName nvarchar(100)                          

answered Mar ix, 2009 at 16:08

John SansomJohn Sansom

40.4k nine gold badges 70 silverish badges 82 bronze badges

The syntax to change a column in an existing table in SQL Server (Transact-SQL) is:

                Change Tabular array table_name     Alter COLUMN column_name column_type;                              

For instance:

                Modify TABLE employees     Change Cavalcade last_name VARCHAR(75) NOT Null;                              

This SQL Server ALTER Tabular array instance will modify the cavalcade called last_name to be a data blazon of VARCHAR(75) and force the column to not allow aught values.

see here

pbaris

4,305 four gold badges 38 silver badges lx bronze badges

answered Oct eleven, 2015 at 21:15

ane

• delight add more information to your answer and format the code / query!

  Oct 11, 2015 at 21:22

For irresolute data type

              change table table_name  alter column column_name datatype [NULL|Not Nada]                          

For irresolute Primary key

              ALTER TABLE table_name   ADD CONSTRAINT PK_MyTable Primary Cardinal (column_name)                          

answered Sep 26, 2016 at 20:12

As long as yous're increasing the size of your varchar you're OK. As per the Alter Table reference:

Reducing the precision or scale of a column may cause information truncation.

answered Dec 23, 2014 at 18:25

jocassidjocassid

3,773 1 gilt badge 12 silver badges 6 bronze badges

                Change TABLE [dbo].[TableName] Alter COLUMN ColumnName VARCHAR(Max) Nix                              

answered Mar 5, 2020 at 7:53

one

• This doesn't seem to add anything over the existing accustomed reply from years ago, and also the changes y'all've posted to the tabular array structure don't really match the question.

  Mar 5, 2020 at 11:05

in 11g:

              Alter Tabular array TableName Change ColumnName DataType;                          

EG: Alter TABLE employees Modify BIRTH_DATE VARCHAR(30);

answered Mar 9, 2021 at 6:15

Biman PalBiman Pal

175 1 silver badge 9 bronze badges

              ALTER TABLE [Operation].[dbo].[CRA_283_Violation] Change Cavalcade [Throughput_HS_DC_NodeB_3G_Alarm] scrap                          

mxmissile

10.8k 3 gold badges fifty silvery badges 78 statuary badges

answered Apr 6, 2021 at 6:42

Try this:

                Modify Tabular array "table_name" Modify "column_name" "New Data Type";                              

Marius

15k nine gold badges 52 silver badges 74 statuary badges

answered Jan 29, 2015 at xiii:l

3

• "Change" apparently is not correct. Please compare with other answers.

  January 29, 2015 at 17:01

• This is sometime, just appears that Kai Tzer was proving MySql and/or Oracle DDLs.

  Mar 7, 2018 at 19:55

• Concord with Sheldon, the answers here non helped me with oracle sql, but this

  April 12, 2018 at 9:44

Not the answer you're looking for? Browse other questions tagged sql-server tsql blazon-conversion alter-table or ask your ain question.

Source: https://stackoverflow.com/questions/626899/how-do-you-change-the-datatype-of-a-column-in-sql-server

Posted by: helgesonafror1942.blogspot.com

Share this post