Creating Your First Table in Microsoft SQL Server

Understanding schemas

While a database is the primary container of all objects, schemas offer another level of containment and organization within a database. Using a schema, a user can group objects of similar scope or ownership together. By default, the database owner (dbo) schema is automatically created within a database. Any object that is created is added to this schema. You can change this behavior in a couple of ways, as you will learn later in this book.

Consider the schema shown in Figure 5-2. You could create a schema containing information specific to the human resources department. However, if you extend the database to include sales information for each employee, you can place the new objects in a Sales schema.

Create a database schema using SSMS

1. Open SSMS and connect to a SQL Server instance.

2. Expand the Databases folder.

3. Expand the SBSChp4SSMS database.

4. Expand the Security folder.

5. Right-click the Schema folder and select New Schema from the context menu.

6. In the Schema – New dialog box, type Sales in the Schema Name text box and dbo in the Schema Owner text box.

   

   Click to view larger image

7. Click OK.

Create a database schema 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 create a SQL Server database with a single data and log file
   USE SBSChp4TSQL;
   GO
   CREATE SCHEMA Sales;
   GO
   CREATE SCHEMA HumanResources;
   GO

   NOTE

   As a best practice, try to create all schemas prior to creating tables. However, if that is not possible, you can always move a table or any other object from one to another using the ALTER SCHEMA ...TRANSFER statement.

A final thing to mention about schemas is that you can grant users permissions to schemas. In Chapter 25, “Security,” you’ll look in depth at several security aspects of SQL Server, including schemas.