Creating tables (SQL Server tutorial)

The first step in creating a table is determining what type of data  you are storing. In many cases, the information you want to store is better held in multiple tables.  For example, you might have a single table for customers, a table for orders and a table for employees who take orders.  A diagram of this basic database might look like this:

Screen Shot 2014-11-04 at 12.56.45 PM

Figure 1.2.  This database diagram demonstrates relationships between tables

By having multiple tables, you can have multiple orders associated with a single customer, and multiple orders associated with a single employee. Additionally, by breaking up your data into multiple tables you can reduce the amount of storage used by your database. This process of breaking up tables to reduce redundant data is called normalization.  A fully normalized database has a minimum of redundant data in any given column.

In addition to eliminating redundancy, a normalized database allows for more flexibility of queries and better performance.  Consider, for example, a table containing the following data:

Screen Shot 2014-11-04 at 9.12.10 PM

 

In this case, whoever is doing the data entry — or whatever the source of the data — the state has been entered differently for different records.  By not having consistent entries, it becomes almost impossible to ask a simple question: which customers are in the state of Georgia?

With a normalized database, the state table would be completely separate, and data would look like this:

Screen Shot 2014-11-04 at 9.15.55 PM

 

A foreign key reference (we’ll get to those later in the book) in the customers table guarantees that the statecode entered in the customers table always matches one in the states tables.

Let’s go ahead and create the study guide database and the Customers table using the CREATE TABLE command.

With the statement I used above, we accepted all of the defaults and created a simple database.  If you paste that code into SQL Server Management Studio and execute it, you’ll see the following response:

Command(s) completed successfully.

Not a great deal of feedback.  But here is what the table we just created looks like:

Screen Shot 2014-11-04 at 11.14.15 AM

 

Figure 2.  Customers table with no data inserted

The results, of course, include no data. That will come later. In the meantime, we’ll talk about the nuts and bolts of creating your table.  The syntax for creating a table is not as complicated as it might look.  Here’s the basics:

CREATE TABLE (column names and data types here);

Inside the parenthesis, you define each of your columns with a name, a datatype and various options. The basic syntax within the parenthesis is fairly simple: column name followed by data type followed by options. Each column is separated by a comma:

 

In this case, we create a table with two columns.  The names of the columns are—FIRST-COLUMN-NAME and SECOND-COLUMN-NAME.

Other options you can use when executing the CREATE TABLE command include:

Constraints — nulls, check constraints, defaults, primary keys, foreign keys and unique

Storage — the file group, partition schema or table compression

Columns — length and precision of data types, special types of columns and collations if using a nonstandard collation.

More information on the CREATE TABLE command is located in the SQL Server 2012 Books Online article located here: http://msdn.microsoft.com/en-us/library/ms174979(v=sql.110).aspx

Leave a Reply

Your email address will not be published. Required fields are marked *