How to Create a Table in a Database in SQL Server 2008 R2

This tutorial will show how to create a table in a database in SQL Server 2008 R2. Creating a table within an appropriate database is an important essential when keeping data organized. Think of a library system and how each book is categorized in genres making for a more enjoyable experience. Now imagine if the same library was not categorized in genres but rather just bunched all together, it would be difficult to find specific data since it is in random order. So organizing data from the beginning will prevent headaches in the future when the database gets bigger.

When creating a table we use schemas, which contain objects such as tables, views, stored procedures, etc. Tables reside within schemas, and schemas reside within databases. In this tutorial we will create a table in a Sports database using a schema called dbo that is created automatically in every database and is also used as the default schema for users that were not associated explicitly with another schema.

Setting Up

Since this tutorial will be using a database, we must be connected to the Database Engine and have a database called Sports. Make sure to connect to the Database Engine on startup so you are eligible to create a database. In this example we will create a database in a query as this is the best way to understand how it is created. Click the ‘New Query’ button and you should see a blank space. We will use what is called a “CREATE DATABASE” statement that enables the query to create a database. We then follow up the statement with a desired database name, in this example it will be Sports.

PgSQL

1

CREATEDATABASESports;

Database Table

Creating a table in a database is similar to creating a table in SQL Server Compact except that we have to provide a database that we want to insert the table into and a schema for the table. To specify what database we will use, we use the USE statement followed by a database name. So in our case it will be ‘USE Sports’, which can be translated as “Using the Sports database”. We then use the CREATE TABLE statement followed by a schema and database name. In our case it will be ‘CREATE TABLE dbo.SportsTeams.’, which can be translated as “We will create a table called SportsTeams that uses the dbo schema.”

In addition to specifying a database and table name, we will create three columns: Name, City, and TeamState. Each column uses a data type of varchar and does not accept null values. In this table we will be requesting the name of a sports team, and the city and state of the team.

PgSQL

1

2

3

4

5

6

7

8

USESports

CREATETABLEdbo.SportsTeams

(

Namevarchar(30)not null,

Cityvarchar(30)not null,

TeamStatevarchar(30)not null

);

Execute the query and it should complete successfully. Look in the object explorer and you should see the SportsTeams table with the columns created in the ‘Columns’ folder.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.