When working with databases, there are often situations where we need to modify the structure of a table. This can involve adding, updating, or deleting columns. In this article, we will explore how to perform these operations in SQL Server.
Adding a Column
To add a new column to an existing table, we can use the ALTER TABLE statement. Let’s say we have a table called Employee with the following structure:
CREATE TABLE Employee (
EmployeeId int identity(1,1),
EmployeeNumber nvarchar(10),
FirstName nvarchar(50),
LastName nvarchar(150),
Age int,
PhoneNo nvarchar(15),
Address nvarchar(200),
EmploymentDate datetime
)If we want to add a new column called RegDate of type datetime to the Employee table, we can use the following SQL statement:
ALTER TABLE Employee
ADD RegDate datetime NULLThis will add a nullable column called RegDate to the Employee table.
Updating a Column
Sometimes, we may need to modify the properties of an existing column. For example, let’s say we want to increase the character limit of the FirstName column from 50 to 100. We can achieve this by using the ALTER TABLE statement with the ALTER COLUMN clause:
ALTER TABLE Employee
ALTER COLUMN FirstName nvarchar(100)This will update the FirstName column in the Employee table to have a character limit of 100.
Deleting a Column
If we want to remove a column from a table, we can use the ALTER TABLE statement with the DROP COLUMN clause. For example, to delete the RegDate column from the Employee table, we can use the following SQL statement:
ALTER TABLE Employee
DROP COLUMN RegDateThis will remove the RegDate column from the Employee table.
By using these techniques, we can easily modify the structure of a table in SQL Server to meet our changing requirements.