- By David LeBlanc
Now you are equipped with the skills you need to create tables with T-SQL and SSMS, but what if someone asks you to change one of your tables? How will you make that change? Not to worry—just as you created the tables with T-SQL and SSMS, you can also modify the tables. You can add columns, change columns, and drop columns using either tool.
Prior to walking through the next set of steps, execute the following script:
USE SBSChp4TSQL; CREATE TABLE HumanResources.Employee ( EmployeeID int NOT NULL IDENTITY(1,1), FirstName varchar(50) NOT NULL, MiddleName varchar(50) NULL, LastName varchar(50) NOT NULL ) ON [SBSTSQLGroup1]; USE SBSChp4SSMS;CREATE TABLE HumanResources.Employee ( EmployeeID int NOT NULL IDENTITY(1,1), FirstName varchar(50) NOT NULL, MiddleName varchar(50) NULL, LastName varchar(50) NOT NULL ) ON [SBSSSMSGroup1];
Add a column to an existing table using SSMS
Ensure that SSMS is open and you are connected to your server.
Expand the Databases folder.
Expand the SBSChp4SSMS database.
Expand the Tables folder.
Right-click the HumanResources.Employee table and select Design.
Type Gender in the first empty row in the Column Name column.
In the Data Type column, type char(1).
In the Allow Nulls column, uncheck the box.
Add a column to an existing table using T-SQL
Open the query editor in SSMS.
In the query editor, enter and execute the following T-SQL code:
--Use this code to add the Gender column to the Employee table USE SBSChp4TSQL; ALTER TABLE HumanResources.Employee ADD Gender char(1) NOT NULL;