ALTER TABLE : The ALTER TABLE command is used to alter the columns in a table or add, deletes and modifies columns in a table. So the ALTER TABLE command is used as below:
We have an EmployeeDetail table in TESTDB database and we will add some new columns in EmployeeDetail table by alter command. The syntax od ALTER TABLE is as:
ALTER TABLE EMPLOYEEDETAIL
ADD CONTACT_NUMBER(18,2), ALTERNATE_CONTACT_NUMBER NUMERIC(18,2)
The example is as below:
Here, we have added two new columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table. So if we want to change the data type of these columns then we will use ALTER COLUMN command.
ALTER COLUMN : The ALTER COLUMN command is used to change the data type of an existing columns in a table. So here we will change a data type of added columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table.
The syntax of ALTER COLUMN is as below:
ALTER TABLE EMPLOYEEDETAIL
ALTER COLUMN CONTACT_NUMBER(NVARCHAR(20)),
ALTERNATE_ CONTACT_NUMBER(NVARCHAR(20))
Note: The SQL Server doesn't allow to alter multiple columns with single ALTER TABLE command.
The example is as below:
Here, we have changed data types of added columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER in EMPLOYEEDETAIL table. So if we want to drop these columns then we will use DROP COLUMN command.
DROP COLUMN : The DROP COLUMN command is used to delete or drop an existing columns from table. So here we will drop columns i.e. CONTACT_NUMBER, ALTERNATE_CONTACT_NUMBER from EMPLOYEEDETAIL table.
The syntax of DROP COLUMN is as below:
ALTER TABLE EMPLOYEEDETAIL
DROP COLUMN CONTACT_NUMBER , ALTERNATE_CONTACT_NUMBER
The example is as below:
- - - - - - Cheers, Happy to Help! - - - - - -
