Altering column size in SQL Server

Question

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

Solution 1

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

Solution 2

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

Solution 3

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

Solution 4

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

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.

View more solutions