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

User · Answer

ALTER TABLE  quot Employee quot  MODIFY   quot Salary quot  NUMERIC 22 5

User · Answer

alter table Employee alter column salary numeric 22 5

User · Answer

ALTER TABLE  table name  ALTER COLUMN  column name  varchar 150

User · Answer

Select table--  Design--  change value in Data Type shown in following Fig     Save tables design

User · Answer

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

User · Answer

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

User · Answer

For Oracle For Database  ALTER TABLE table name MODIFY column name VARCHAR2 255 CHAR

User · Answer

ALTER TABLE  Employee  ALTER COLUMN  Salary  NUMERIC 22 5  NOT NULL

User · Answer

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

[sql] Altering column size in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql

Examples related to alter-table