I am trying to change a column from a varchar(50)
to a nvarchar(200)
. What is the SQL command to alter this table?
ALTER TABLE TableName
ALTER COLUMN ColumnName NVARCHAR(200) [NULL | NOT NULL]
EDIT As noted NULL/NOT NULL should have been specified, see Rob's answer as well.
Why should it be specified? What is the benefit? Everything I want to leave as it is I don't have to mention, I think.
@TheincredibleJan That would be cool, but unfortunately it doesn't work that way. If you issue an
ALTER TABLE TableName ALTER COLUMN ColumnName
command to change an existing column that is[NOT NULL]
, and you don't explicitly specify it, it will be[NULL]
afterwards, as that's the default.This only works if you don't have constraints and indexes on that column, otherwise, you need to drop everything and recreate it, which is tedious and grunt work, especially if you have many foreign references on that column. e.g. when changing from tinyint to int.
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") it still showed the old Data Types! It was only after closing ALL of my Tabs in Management Studio and opening the Design View again that it finally showed the updated DataType. Very scary, so be careful (it may be a cache-bug that is fixed by now or one MS never bothers fixing). For those wondering why I ran T-SQL when I use Design-View, I wanted to alter my DataTypes to
SysName
(which SSMS does not allow).I had no luck with these commands in XAMPP.
ALTER TABLE table MODIFY COLUMN column datatype
worked for me.