Altering column size in SQL Server
SqlSql ServerDatabaseTsqlAlter TableSql Problem Overview
How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)
Sql Solutions
Solution 1 - Sql
ALTER TABLE [Employee]
ALTER COLUMN [Salary] NUMERIC(22,5) NOT NULL
Solution 2 - Sql
alter table Employee alter column salary numeric(22,5)
Solution 3 - Sql
ALTER TABLE [table_name] ALTER COLUMN [column_name] varchar(150)
Solution 4 - Sql
Running ALTER COLUMN without mentioning attribute NOT NULL will result in the column being changed to nullable, if it is already not. Therefore, you need to first check if the column is nullable and if not, specify attribute NOT NULL. Alternatively, you can use the following statement which checks the nullability of column beforehand and runs the command with the right attribute.
IF COLUMNPROPERTY(OBJECT_ID('Employee', 'U'), 'Salary', 'AllowsNull')=0
ALTER TABLE [Employee]
ALTER COLUMN [Salary] NUMERIC(22,5) NOT NULL
ELSE
ALTER TABLE [Employee]
ALTER COLUMN [Salary] NUMERIC(22,5) NULL
Solution 5 - Sql
Interesting approach could be found here: How To Enlarge Your Columns With No Downtime by spaghettidba
> If you try to enlarge this column with a straight “ALTER TABLE” command, you will have to wait for SQLServer to go through all the rows and write the new data type
> ALTER TABLE tab_name ALTER COLUMN col_name new_larger_data_type; > To overcome this inconvenience, there is a magic column enlargement pill that your table can take, and it’s called Row Compression. (...) > With Row Compression, your fixed size columns can use only the space needed by the smallest data type where the actual data fits.
When table is compressed at ROW level, then ALTER TABLE ALTER COLUMN is metadata only operation.
Solution 6 - Sql
Right-click the table you want to modify --> Select "Design" --> Change the value in the "Data Type" column as shown in the following image:
Then Save to complete the change to the table design.
Solution 7 - Sql
You can use ALTER command to modify the table schema.
The syntax for modifying the column size is
ALTER table table_name modify COLUMN column_name varchar (size);
Solution 8 - Sql
ALTER TABLE "Employee" MODIFY ("Salary" NUMERIC(22,5));
Solution 9 - Sql
For Oracle For Database:
ALTER TABLE table_name MODIFY column_name VARCHAR2(255 CHAR);
Solution 10 - Sql
In this case, you need to use ALTER TABLE statement to increase column size.
Here is the syntax for it
ALTER TABLE table_name MODIFY column_name varchar(new_length);