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:
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:
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:
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.
CREATE DATABASE StudyGuide
CREATE TABLE dbo.Customers
custID INT IDENTITY(1,1) PRIMARY KEY,
lName VARCHAR (50) NOT NULL,
createdate DATETIME2(0) NOT NULL DEFAULT GETDATE(),
ver ROWVERSION NOT NULL
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:
select * from dbo.Customers
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:
CREATE TABLE dbo.Customers
FIRST-COLUMN-NAME DATA_TYPE IDENTITY(1,1) PRIMARY KEY,
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