Creating Your First Table in Microsoft SQL Server

Altering tables

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

1. Ensure that SSMS is open and you are connected to your server.

2. Expand the Databases folder.

3. Expand the SBSChp4SSMS database.

4. Expand the Tables folder.

5. Right-click the HumanResources.Employee table and select Design.

6. Type Gender in the first empty row in the Column Name column.

7. In the Data Type column, type char(1).

8. In the Allow Nulls column, uncheck the box.

   

   Click to view larger image

9. Click Save.

   Add a column to an existing table using T-SQL

   1. Open the query editor in SSMS.

   2. 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;