How to change the column size of the salary
column in the employee
table from numeric(18,0)
to numeric(22,5)
This question is related to
sql
sql-server
database
tsql
alter-table
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);
For Oracle For Database:
ALTER TABLE table_name MODIFY column_name VARCHAR2(255 CHAR);
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" MODIFY ("Salary" NUMERIC(22,5));
alter table Employee alter column salary numeric(22,5)
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.
Source: Stackoverflow.com