Altering column size in SQL Server

How to change the column size of the `salary` column in the `employee` table from `numeric(18,0)` to `numeric(22,5)`

alter table Employee alter column salary numeric(22,5)

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

ALTER TABLE [table_name] ALTER COLUMN [column_name] varchar(150)

ALTER TABLE [Employee]
ALTER COLUMN [Salary] NUMERIC(22,5) NOT NULL

Interesting approach could be found here: [How To Enlarge Your Columns With No Downtime by spaghettidba](

[To see links please register here]

)

> 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.

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);

ALTER TABLE "Employee" MODIFY ("Salary" NUMERIC(22,5));

For Oracle For Database:

`ALTER TABLE table_name MODIFY column_name VARCHAR2(255 CHAR);`

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);