Coder Perfect

In SQL Server, you can change the column size.

Category: sql

Tag: , , , ,

Problem

How do I alter the salary column size in the employee database from numeric(18,0) to numeric(18,0)? (22,5)

Asked by Sree

Solution #1

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

Answered by Darren

Solution #2

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

Answered by Priyank Patel

Solution #3

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

Answered by Durgesh Pandey

Solution #4

If you run ALTER COLUMN without mentioning the attribute NOT NULL, the column will be changed to nullable if it isn’t already. As a result, you must first determine whether the column is nullable, and if it isn’t, define the attribute NOT NULL. You can also use the following line, which tests the nullability of the column first and then executes the command with the appropriate 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

Answered by Hamid Heydarian

Solution #5

Here’s an interesting approach: How To Enlarge Your Columns With No Downtime by spaghettidba.

ALTER Database ALTER COLUMN is a metadata-only action when the table is compressed at the ROW level.

Answered by Lukasz Szozda

Post is based on https://stackoverflow.com/questions/10138116/altering-column-size-in-sql-server