Creating Your First Table in Microsoft SQL Server

Creating tables

Admittedly, creating tables with SSMS is much easier than with T-SQL. The biggest disadvantage to using SSMS, though, is not having very portable code. Once T-SQL is written, it can be saved and executed against the same instance or another instance of SQL Server without your having to re-create the script, but this is not the case with SSMS. If you use the table designer to create a table, you are required to perform the same steps on another instance of SQL Server if you want to re-create the table. Nevertheless, it is worth knowing and understanding the steps. You should learn how to create the table using T-SQL not only because most things on SQL Server are accomplished using T-SQL, but also because it allows for easy portability.

Now it is time to create a table of your own. Create a table named Addresses using the information provided in Table 5-5.

Table 5-5 Address table requirements

Create a table using SSMS

1. With SSMS open, expand the Databases folder.

2. Expand the SBSChp4SSMS database.

3. Expand the Security folder.

4. Right-click the Schemas folder.

5. Select New Schema from the menu.

6. In the Schema – New dialog box, type HumanResources in the Schema Name text box.

7. Type dbo in the Schema Owner text box.

8. Click OK.

9. Right-click the Tables folder. The table designer opens.

10. Select New Table from the menu.

    

    Click to view larger image

11. In the Column Name column, type AddressID.

12. Click in the Data Type column and select int from the drop-down list.

13. In the Column Properties tab that is located at the bottom of the table designer window, scroll down and expand Identity Specification.

14. Set the Is Identity property to Yes.

15. In the next row of the column list, type StreetAddress in the Column Name column.

16. Click in the Data Type column and select varchar from the drop-down list, changing the character string length to 125.

17. Uncheck the box under the Allow Nulls column.

18. Repeat steps 16–18 for each additional column, setting the property according to the specifications.

    

    Click to view larger image

19. Select View | Properties. The Properties window opens.

20. Locate and click in the Schema property. Select HumanResources from the drop-down list.

21. Locate and expand the Regular Data Space Specification property. In the Filegroup or Partition Scheme Name property, ensure that SBSSSMSGroup1 is selected.

    

    Click to view larger image

22. Click the Save button.

23. Type Address in the text box in the Choose Name window.

24. Click OK.

Create a 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 SBSChp4TSQL;
   CREATE TABLE HumanResources.Address
   (
        AddressID int NOT NULL IDENTITY(1,1),
        StreetAddress varchar(125) NOT NULL,
        StreetAddress2 varchar(75) NULL,
        City varchar(100) NOT NULL,
        State char(2) NOT NULL,
        EmployeeID int NOT NULL
   ) ON [SBSTSQLGroup1];