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)

Darren · Accepted Answer · 2018-07-28T15:15:33.787

677

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

edited Jul 28 '18 at 15:15

answered Apr 13 '12 at 09:12

Darren

68,902
24
138
144

69

It's also important that if the column is with attribute `NOT NULL` then it has to be mentioned in the query else it will set it to `NULL` by default. `ALTER TABLE [Employee] ALTER COLUMN [Salary] NUMERIC(22,5) NOT NULL` – Suvendu Shekhar Giri Jun 29 '15 at 07:25
12

If you have an index on that column you'll have to drop it, then execute the alter table code and then create the index again – Sr.PEDRO Sep 05 '17 at 19:18

score 53 · Answer 2 · edited Jan 02 '18 at 03:03

53

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

edited Jan 02 '18 at 03:03

Pang

9,564
146
81
122

answered May 11 '16 at 11:26

Durgesh Pandey

2,314
4
29
43

Priyank Patel · Answer 3 · 2012-04-13T09:17:41.030

46

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

edited Apr 13 '12 at 09:17

answered Apr 13 '12 at 09:12

Priyank Patel

6,898
11
58
88

Hamid Heydarian · Answer 4 · 2017-01-06T00:45:03.110

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

score 8 · Answer 5 · answered Apr 28 '20 at 20:14

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.

Vishe · Answer 6 · 2022-11-28T05:05:26.063

4

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.

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.

edited Nov 28 '22 at 05:05

answered Jan 29 '17 at 05:59

Vishe

3,383
1
24
23

check properly might be you are missing some step. – Vishe Feb 11 '17 at 08:47
10

Tools -> Options... / >Designers -> Table and Database Designers -- uncheck "Prevent saving changes that require table re-creation" – 7anner Feb 28 '18 at 01:15
1

For most things, I prefer scripting. But for column changes, I like the UI. Don't have to remember to check for NOT NULL or any calculations or any additional column properties. Just click Save (after you make the settings change in SSMS as @7anner notes). – Jacob H Dec 31 '18 at 20:03
1

This is not the correct solution, using Design for changing the Table datatype will result in dropping and recreating the table, you need to use alter ALTER TABLE [Employee] ALTER COLUMN [Salary] NUMERIC(22,5) NOT NULL – Arunkumar Arjunan Aug 16 '21 at 09:43

score 2 · Answer 7 · edited Sep 28 '20 at 20:23

2

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

edited Sep 28 '20 at 20:23

Dinesh

812
4
14

answered Sep 28 '20 at 14:51

Techie Everyday

45
7

score 0 · Answer 8 · answered Oct 23 '20 at 15:39

0

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

answered Oct 23 '20 at 15:39

Feng Zhang

1,698
1
17
20

score 0 · Answer 9 · answered Feb 16 '21 at 14:25

0

For Oracle For Database:

ALTER TABLE table_name MODIFY column_name VARCHAR2(255 CHAR);

answered Feb 16 '21 at 14:25

Shaini Sinha

527
6
10

Dinesh is asking to change the numeric data type not the string one. – Meyssam Toluie Mar 22 '22 at 22:13
above is just example he is providing rest developer has to think @MeyssamToluie – ShivShankar Namdev Apr 03 '22 at 17:16

score -1 · Answer 10 · answered Jun 22 '21 at 07:18

-1

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

answered Jun 22 '21 at 07:18

Pike

99
1
3

Dinesh is asking to change the numeric data type not the string one. – Meyssam Toluie Mar 22 '22 at 22:12

Altering column size in SQL Server

10 Answers10