SQL Server Reference Guide

I’m continuing a series of articles on designing and creating a database based on a set of Business Requirements. If you’re just joining, you may want to look over a few of those earlier articles to bring you up to where I am in this tutorial.

I’m now ready to convert the completed physical model into a SQL Server database. Before I begin, I’ll recap how I got to this point.

The original request for a database came from the business community. While this is a common way to start the process, it's also common for developers to just ask for a database, or the DBA team leader might be in on the original request. Wherever the appeal comes from, it will normally come in the form of business requirements. When you get them, take those requirements and re-write the paragraphs into single-thought sentences. From there, break down the sentences into nouns and verbs, which give you your entities, attributes and relationships.

Next confirm the design with the business community in a design review process, and then create a logical Entity Relationship Diagram (ERD) to graphically represent the data store. From there refine the ERD to a final logical design.

With the logical ERD in hand, you’ll convert the logical design into physical database objects. Entities become tables, attributes become columns, and relationships become foreign and primary keys. In this case, the final deliverable before I create the physical database is this design:

In the last tutorial you learned that the ERD is only one format for presenting the design. You can also use a spreadsheet with the same information. Either way, it's important to have a clear, understandable format to communicate what is needed to the data professional who will implement the database.

You’re now ready to create the database from the diagram. You have several options. One is to use the graphical tools in either Enterprise Manager (SQL Server 2000 and earlier) or SQL Server Management Studio (SQL Server 2005 or later). Using these tools, you can simply right-click the “Databases” node in their respective locations, and then “Create new Database.” From there, simply fill out the boxes on the panels you see, pressing the “Help” button when you don’t understand what a box does. I’ll explain more about those choices in a moment. Once the database is created, you can use the graphical tools, navigate to the “Tables” Node, and then right-click that to make a new table. You’ll be dropped into the Table Designer, where you can visually set up your tables, and even save a script to store the process for later.

You can also use the “Templates” feature in SSMS that I’ve documented here to create a table. In some cases, this is really the way to go — especially when you’re learning.

You have other options as well. Other programs that have the ability to send the Data Definition Language (DDL) commands such as ERWin or some other design tool can also create a database. Finally, you can create a database using those DDL commands directly. There is no "best" way, as all of these lead to a finished database.

In this tutorial, I’m going to show you the commands to create the database and set up all of the tables. This is the most “portable” way, and it allows you to perform the task on multiple Instances in many locations. Not only that, you can have others review your script, include it in a script library, and put it under source control. Scripts allow you to perform tasks in an automated way, and are general faster to create (once you learn how) than using a graphical tool.

Of course there are some down-sides to using a script. You have to know what to type, of course, and you need to keep the script up-to-date with the latest syntax. Happily, most of the syntax for these base objects doesn’t change, but you do need to be aware of that syntax to begin.

I’ll show you how to create the database using the ERD I’ve shown above — note that in other databases you’ll have more options to set and so on. Many of these can be changed later; others can’t be changed as easily. But the good news is that you can use the script to completely re-create the database in testing environments and so on. so that’s the method I’ll show you here.

As you’re typing these commands in Management Studio, you can highlight them and press SHIFT-F1, and the system will look up the help for that command. Also, in SQL Server 2008 and higher, you can use the Intellisense feature to help fill out the commands for you. And as I mentioned, you can use the Templates I’ve explained before to help you do this as well.

One note here before I get started. I’m not going to explain or comment much on the design here. There are some changes I would recommend, after discussing it with the design team, and not least of all I would recommend changing the naming of the objects. For one thing, it’s in the plural, and I tend to recommend a singular object name. Also, the table names and fields have underlines in them to separate the objects. Some languages have problems with these, so I stay away from those. Finally, there are some objects that use Keywords as names (Level being one of them) that I definitely don’t recommend.

Also, I think the design could be better. Even so, I’ll create the DDL commands here to show you how to deal with these issues, should they arise in your own designs.

I should also note that this is an example only — not everything in this tutorial is a best-practice or something you should do blindly in your own environment. Understand each option before you implement it!

Creating the Database

To begin, I need the database object itself. To do that, I open Management Studio (I’m using SQL Server 2008 R2 in this case) and then a query window. I always use a format file that looks like this:

/* <ScriptFileName, SYSNAME, ScriptFileName>.sql
Purpose: <Purpose, SYSNAME, Purpose>
Author: <Author, SYSNAME, Buck Woody>
Last Edited: <LastEdited, SYSNAME, LastEdited>
Instructions: <Instructions, SYSNAME, Instructions>
References:
*/

/* End <ScriptFileName, SYSNAME, ScriptFileName>.sql */

Then I just press CTRL-SHIFT-M and enter the name of the script file, the purpose and so on. That way I know what was done, when, and so on. You can read more about how I do that here.

Here’s the simplest command to create a database:

USE master;
GO
CREATE DATABASE ProjectTracking;
GO

That command will take a complete copy of the “model” system database and make a new database with that structure. Notice that I have a USE master; GO statement in there, which places me in the context of the master database — always a good practice for creating a new database.

While this command works, you probably want a bit more control than that.

You can look up the CREATE DATABASE command in Books Online, but it can be pretty daunting to read it all. Not that you shouldn’t learn the complete command — but to get started I’ll show you some of the more common methods I use here.

Here’s the complete command I used to get started — I’ll explain each line in a moment. Note that the formatting is my convention — you can use whatever you like, as long as you are consistent and it’s understandable to your team.

USE [master]
GO
CREATE DATABASE [ProjectTracking] 
ON  PRIMARY 
( NAME = N'ProjectTracking'
, FILENAME = N'C:\temp\ProjectTracking.mdf' 
, SIZE = 3072KB 
, MAXSIZE = UNLIMITED
, FILEGROWTH = 1024KB )

LOG ON 
( NAME = N'ProjectTracking_log'
, FILENAME = N'C:\temp\ProjectTracking_log.ldf' 
, SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%);
GO

Starting at the top, I’m back in the master database like before, and also using the same CREATE DATABASE statement. But notice I’ve surrounded the name of the databases with brackets, like this: [ ]. Those brackets tell the system to allow “special” characters in object names, and even allow system names to be used as objects — always a bad idea, but if you use these brackets in your code you’re safe. There are also some options below that, starting with the ON statement. This sets the FileGroup for the database. If you aren’t familiar with a FileGroup in SQL Server, you can read this article, but for this database you can take the default as I have here, called PRIMARY.

The next option (with a parenthesis before it) is the name option. I’ve set the name, but this time instead of the brackets I have an N in front. That sets the database name properly if the system where I’m creating the database is in Unicode — more on that here — but it’s safe to use regardless.

Next, I set the name of the files I plan to use for this database. To make the script more portable, I’m using the “temp” directory on my C drive for that, but in production I would edit that to have the proper locations for my drive layouts. More on that here.

I set an initial size for the database using the SIZE keyword — just 3GB in this database, and then I set it to grow automatically using the MAXSIZE = UNLIMITED command. This is also common in smaller databases, and even in some larger ones. To set the growth factor, I’ll use 1GB, but you should know that you can also use a percentage here — which you need to keep in mind will get bigger as your data grows.

Next comes exactly the same process for the log, with a couple of exceptions to show you your options. I’ve set a maximum size, and I’ve set a percentage growth number.

I haven’t specified all of my options in the CREATE DATABASE command. I normally create the database, and then implement a series of ALTER DATABASE statements after that to set the other options I want, in case I don’t want them or want them to be different. I find that having these two steps separated work well for me.

Here’s a list of options I set, but I’ll only explain a couple of the more important ones. I’ve covered the rest in my articles on Database Configuration:

ALTER DATABASE [ProjectTracking] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ProjectTracking] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ProjectTracking] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ProjectTracking] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ProjectTracking] SET ARITHABORT OFF
GO
ALTER DATABASE [ProjectTracking] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ProjectTracking] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [ProjectTracking] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ProjectTracking] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ProjectTracking] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ProjectTracking] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [ProjectTracking] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ProjectTracking] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ProjectTracking] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ProjectTracking] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ProjectTracking] SET  DISABLE_BROKER
GO
ALTER DATABASE [ProjectTracking] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ProjectTracking] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ProjectTracking] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ProjectTracking] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ProjectTracking] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ProjectTracking] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [ProjectTracking] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [ProjectTracking] SET  READ_WRITE
GO
ALTER DATABASE [ProjectTracking] SET RECOVERY FULL
GO
ALTER DATABASE [ProjectTracking] SET  MULTI_USER
GO
ALTER DATABASE [ProjectTracking] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ProjectTracking] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'ProjectTracking', N'ON'
GO

That’s it for the database. It’s ready to go, and I can create it any time. I can also edit the script, change the parameters I want, and run it again on another system. It’s time to move on to the tables.

Creating the Tables

Now I can create the tables. The key here is, well, the key. You need to remember to create the “parent” tables first — meaning the ones that have the Primary Keys that are referenced by the other tables. I’ll start with Clients.

USE [ProjectTracking];
GO

CREATE TABLE [dbo].[Clients](
 [Client_Code] [int] IDENTITY(1,1) NOT NULL
,[Name] [nvarchar](50) NOT NULL
,[Start_Date] [datetime] NOT NULL
,[Primary_Address] [varchar](255) NOT NULL
,[Primary_Phone] [varchar](30) NULL
CONSTRAINT [PK_Clients] 
PRIMARY KEY CLUSTERED 
(
[Client_Code] ASC
)
ON [PRIMARY]
) ON [PRIMARY] 

The very first thing to do is to ensure you’re in the right database, using the USE statement. If you don’t, you’ll create tables in the wrong place, which can in some cases be dangerous.

Next I use the CREATE TABLE statement, and you’ll notice that it has the “dbo” role as the schema. If you’re not familiar with schemas, look here.

The next part that comes is the column, the type, and any options for the type. In this first column I’ve specified that the int is an Identity, which means SQL Server will fill that out for me one number higher than the last. Then I specify that this column can’t be NULL, or that it can.

A comma follows (I put mine in front to track them better) and the process repeats.

Notice that at the end of the columns I’ve specified a constraint, this time for the Primary Key. This is where I can set the column(s) I want that makes each row unique. Notice also that it is clustered — meaning that the table will physically be arranged in that order. I’ll put that clustered index on the PRIMARY filegroup, close the parenthesis for that index, and then put the whole table on PRIMARY as well, closing those parenthesis along the way.

The rest of the tables look similar:

CREATE TABLE [dbo].[Staff_Members](
[Staff_Code] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Employment_date] [datetime] NOT NULL,
CONSTRAINT [PK_Staff_Members] PRIMARY KEY CLUSTERED 
(
[Staff_Code] ASC
)ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Skills](
[Skill_Code] [int] IDENTITY(1,1) NOT NULL,
[Staff_Code] [int] NOT NULL,
[Name] [varchar](30) NULL,
[Classification] [varchar](30) NULL,
[Level] [varchar](30) NULL,
CONSTRAINT [PK_Skills] PRIMARY KEY CLUSTERED 
(
	[Skill_Code] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Projects](
[Project_Code] [int] IDENTITY(1,1) NOT NULL,
[Client_Code] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Phase] [varchar](30) NOT NULL,
[Budget] [smallint] NOT NULL,
[State] [varchar](30) NOT NULL,
 CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED 
(
	[Project_Code] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Hours](
[Hours_Code] [int] IDENTITY(1,1) NOT NULL,
[Project_Code] [int] NOT NULL,
[Staff_Code] [int] NOT NULL,
[Role] [varchar](100) NULL,
[Start_Time] [datetime] NOT NULL,
[End_Time] [datetime] NULL,
[Rate] [smallmoney] NOT NULL,
[Description] [varchar](255) NULL,
CONSTRAINT [PK_Hours] PRIMARY KEY CLUSTERED 
(
	[Hours_Code] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO

Now I need to create the Foreign Key pointers back to the parent tables. Once again, I could have done this in the CREATE TABLE script, but by breaking it out this way I can ensure the order for the table was correct.

Here’s one example, which deals with the Staff_Members “parent” table, and the Skills “child” table.

ALTER TABLE [dbo].[Skills]  WITH CHECK 
ADD  CONSTRAINT [FK_Skills_Staff_Members] 
FOREIGN KEY([Staff_Code])
REFERENCES [dbo].[Staff_Members] ([Staff_Code])
GO

First, I’m using an ALTER TABLE statement, and notice it has a WITH CHECK option. that means as the system creates the Foreign Key, it will check to make sure the data in the two tables already follow the rules — that is, for every row in Skills, the parent table must have an entry for the Staff_Code. Otherwise, the constraint fails and you can fix the data.

Next comes the type of constraint. In this case it’s a Foreign Key, and I’ve given it the name FK_Skills_Staff_Members. You can really use any name you like, but I use FK for the type of constraint, and then the two table names that they join. It’s up to you how yours look.

Now comes the keys in the child table that point to the parent table. In this case it’s Staff_Code, because I named it the same in the child table as the parent.

From there, the REFERENCES statement sets the “Parent” table and columns that should hold the data that the “Child” table points to.

And that’s it. Here’s the whole script in one go if you want to read it that way:

USE [master]
GO
CREATE DATABASE [ProjectTracking] 
ON  PRIMARY 
( NAME = N'ProjectTracking'
, FILENAME = N'C:\temp\ProjectTracking.mdf' 
, SIZE = 3072KB 
, MAXSIZE = UNLIMITED
, FILEGROWTH = 1024KB )

LOG ON 
( NAME = N'ProjectTracking_log'
, FILENAME = N'C:\temp\ProjectTracking_log.ldf' 
, SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%);
GO

ALTER DATABASE [ProjectTracking] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ProjectTracking] SET ANSI_NULLS OFF
GO
ALTER DATABASE [ProjectTracking] SET ANSI_PADDING OFF
GO
ALTER DATABASE [ProjectTracking] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [ProjectTracking] SET ARITHABORT OFF
GO
ALTER DATABASE [ProjectTracking] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [ProjectTracking] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [ProjectTracking] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [ProjectTracking] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [ProjectTracking] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [ProjectTracking] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [ProjectTracking] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [ProjectTracking] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [ProjectTracking] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [ProjectTracking] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [ProjectTracking] SET  DISABLE_BROKER
GO
ALTER DATABASE [ProjectTracking] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [ProjectTracking] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [ProjectTracking] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [ProjectTracking] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [ProjectTracking] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [ProjectTracking] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [ProjectTracking] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [ProjectTracking] SET  READ_WRITE
GO
ALTER DATABASE [ProjectTracking] SET RECOVERY FULL
GO
ALTER DATABASE [ProjectTracking] SET  MULTI_USER
GO
ALTER DATABASE [ProjectTracking] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [ProjectTracking] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'ProjectTracking', N'ON'
GO

USE [ProjectTracking];
GO

CREATE TABLE [dbo].[Clients](
 [Client_Code] [int] IDENTITY(1,1) NOT NULL
,[Name] [nvarchar](50) NOT NULL
,[Start_Date] [datetime] NOT NULL
,[Primary_Address] [varchar](255) NOT NULL
,[Primary_Phone] [varchar](30) NULL
CONSTRAINT [PK_Clients] 
PRIMARY KEY CLUSTERED 
(
[Client_Code] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Staff_Members](
[Staff_Code] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Employment_date] [datetime] NOT NULL,
CONSTRAINT [PK_Staff_Members] PRIMARY KEY CLUSTERED 
(
[Staff_Code] ASC
)ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Skills](
[Skill_Code] [int] IDENTITY(1,1) NOT NULL,
[Staff_Code] [int] NOT NULL,
[Name] [varchar](30) NULL,
[Classification] [varchar](30) NULL,
[Level] [varchar](30) NULL,
CONSTRAINT [PK_Skills] PRIMARY KEY CLUSTERED 
(
	[Skill_Code] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Projects](
[Project_Code] [int] IDENTITY(1,1) NOT NULL,
[Client_Code] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Phase] [varchar](30) NOT NULL,
[Budget] [smallint] NOT NULL,
[State] [varchar](30) NOT NULL,
 CONSTRAINT [PK_Projects] PRIMARY KEY CLUSTERED 
(
	[Project_Code] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Hours](
[Hours_Code] [int] IDENTITY(1,1) NOT NULL,
[Project_Code] [int] NOT NULL,
[Staff_Code] [int] NOT NULL,
[Role] [varchar](100) NULL,
[Start_Time] [datetime] NOT NULL,
[End_Time] [datetime] NULL,
[Rate] [smallmoney] NOT NULL,
[Description] [varchar](255) NULL,
CONSTRAINT [PK_Hours] PRIMARY KEY CLUSTERED 
(
	[Hours_Code] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Skills]  WITH CHECK ADD  CONSTRAINT [FK_Skills_Staff_Members] FOREIGN KEY([Staff_Code])
REFERENCES [dbo].[Staff_Members] ([Staff_Code])
GO

ALTER TABLE [dbo].[Skills] CHECK CONSTRAINT [FK_Skills_Staff_Members]
GO

ALTER TABLE [dbo].[Projects]  WITH CHECK ADD  CONSTRAINT [FK_Projects_Clients] FOREIGN KEY([Client_Code])
REFERENCES [dbo].[Clients] ([Client_Code])
GO

ALTER TABLE [dbo].[Projects] CHECK CONSTRAINT [FK_Projects_Clients]
GO

ALTER TABLE [dbo].[Hours]  WITH CHECK ADD  CONSTRAINT [FK_Hours_Projects] FOREIGN KEY([Project_Code])
REFERENCES [dbo].[Projects] ([Project_Code])
GO

ALTER TABLE [dbo].[Hours] CHECK CONSTRAINT [FK_Hours_Projects]
GO

ALTER TABLE [dbo].[Hours]  WITH CHECK ADD  CONSTRAINT [FK_Hours_Staff_Members] FOREIGN KEY([Staff_Code])
REFERENCES [dbo].[Staff_Members] ([Staff_Code])
GO

ALTER TABLE [dbo].[Hours] CHECK CONSTRAINT [FK_Hours_Staff_Members]
GO

InformIT Articles and Sample Chapters

Here’s a chapter that gets you to this physical model: Creating a Database Diagram from the Conceptual Model.

Books and eBooks

Eric Johnson still has one of my favorite design books, A Developer's Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008, and I even use it in my college class that I teach on database design.

Online Resources

There are some free lessons here to help you get started with Transact-SQL and creating database objects.