CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlalter-table
Sreedhar Danturthi
Sreedhar Danturthi

Reputation: 7571

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)

Upvotes: 405

Views: 1201299

Answers (10)

Dinesh vishe
Dinesh vishe

Reputation: 3598

Right-click the table you want to modify --> Select "Design" --> Change the value in the "Data Type" column as shown in the following image:

screenshot of the Data Type column with a column's data type value getting changed

Then Save to complete the change to the table design.

GUI method is used for Small tables. You are making change in big tables then use command only and side by side check blocking occur or not till command complete.

Upvotes: 5

Pike
Pike

Reputation: 99

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

Upvotes: -1

Shaini Sinha
Shaini Sinha

Reputation: 567

For Oracle For Database:

ALTER TABLE table_name MODIFY column_name VARCHAR2(255 CHAR);

Upvotes: 1

Feng Zhang
Feng Zhang

Reputation: 1948

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

Upvotes: 0

Techie Everyday
Techie Everyday

Reputation: 63

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

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175636

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.

Upvotes: 10

Darren
Darren

Reputation: 70728

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

Upvotes: 720

Durgesh Pandey
Durgesh Pandey

Reputation: 2422

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

Upvotes: 61

Hamid Heydarian
Hamid Heydarian

Reputation: 912

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

Upvotes: 28

Priyank Patel
Priyank Patel

Reputation: 6996

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

Upvotes: 48

Related Questions