In water fall methodology we have designed the database before starting development. But the reality is different. When go to development more often we need to modify column datatype or re size column datatype. Here in this article i will share the SQL syntax of Modifying or Re sizing an existing column of a SQL Server table.
SQL Syntax of Modifying Existing Column DataType:
ALTER TABLE "Table_Name" ALTER COLUMN "Column_Name" "New Data Type";
SQL Example of Modifying Existing Column DataType:
ALTER TABLE dbo.tblSupplier ALTER COLUMN Email VARCHAR(100); -- Previous DataType was CHAR(50) ALTER TABLE dbo.tblSupplier ALTER COLUMN Email VARCHAR(50);
Note: To change allow null property of a column to False all existing rows must have contains the value for this column otherwise you will get “ERROR: ‘XXX’ table- Unable to modify table. Cannot insert the value NULL into column ‘YYY’, table ‘XXX’; column does not allow nulls. INSERT fails. The statement has been terminated.“
Note: Keep in mind that if a Table contains x length of dataany & you want to set x-1 size of the column then you will get the ERROR: String or binary data would be truncated. The another change is, you may want to change the datataype of an existing column. Then must keep in mind that the new datatype must be cmpatible with existing datatype otherwise you may loose data or get the ERROR: Conversion failed when converting the Previous DataType value ‘… ‘ to data type New DataType.