The goal of this article is to create an Employees database
in SQL Server and populate it with test data in three quick steps. No theory
or lengthy technical explanations are given. Just concise instruction for
those that want to quickly see what SQL Server is. If you are familiar with
Access, MySQL, Oracle, or some other data store application, then this article
can serve as the “Hello World” sample application for you. Two different
methods can be used to manage SQL Server, the GUI (Graphical User Interface),
and the command line. Both will be covered starting with the GUI version. This
article assumes SQL is already installed.
GUI Step 1 – Create a Database
A “Database” in SQL Server is a container that holds a group
of related objects. In addition to storing the actual data, a Database also
stores the structure of how that data is saved (called a Table), and any
related helper objects such a Queries (saved ways to look data up). To begin, we’ll
create a Database using the graphical interface called the “SQL Server
From the Windows Start Menu, select “Microsoft SQL Server”,
and then “SQL Server Management Studio”. Once the Management Studio starts,
right click the Databases folder and select new Database. Enter a name in the
“Database name” text box. For this example, well use the name “Test”.
As you the type the database name in, the Logical Name for
the file types Data and Log will automatically fill in as well. The “Data”
file is where the actual data is saved on the hard drive. The “Log” file keeps
track of any changes to that data. Click OK.
GUI Step 2 – Create a Table
Now that the Database is created, a structure to hold the
actual data is needed. This structure is called a Table. Think of Tables as
containing Columns and Rows, like a spreadsheet.
To create a Table, expand the Databases folder, and then
expand the newly created “Test” database.
Right click “Tables” and select “New Table”. You will be
prompted to fill in “Column Name” and “Data Type”. Fill in “EmpName” with a
data type of “varchar(50)”. In the next row, enter a Column Name of Wage with
a data type of “money”.
Unlike Excel or a spreadsheet, a column in a Database must
know ahead of time what type of data it will be storing. A data type of VarChar(50)
tells SQL Server to expect character data (text), of a variable size not to
exceed 50 characters. While you can enter numbers into a VarChar field, they
could not be added nor have math functions done against them. For math to be
allowed on a column, it must be some type of numeric field, which is why a data
type of “money” was used for the Wage column.
Save the Table by clicking the Save icon from the top menu
bar; the icon is shown below.
It will prompt for table name, enter “Employees” and click
GUI Step 3 – Enter and View Data
To enter data into the newly created Employees table, expand
the Tables folder from the left menu as shown below.
The newly created Employees table will be listed. Right
click it and select “Open Table”. A small grid will open. Enter a few data
lines as shown below. Moving to a new line after entering data automatically
Working with the Command Line
In three steps using the SQL Server Management Studio, we
created a Database, a Table, and entered data. The same three steps are now
shown below using the command line (Query Window). To open a Query Window,
select “New Query” from the top left menu in the SQL Server Management Studio
as shown below.
A dialog box will pop up asking what server to connect to.
Enter your server name and then click connect. A drop down box above the new
window determines which database you are working with. Leave the default of
“master”. This is SQL Server’s main system database.
When working with a command or query window, the language
used by SQL Server is TSQL.
TSQL Step 1 -Create Database
Create a new database called Test2 by entering the following
TSQL then pressing the “Execute” button on the top menu.
CREATE DATABASE Test2;
The Messages window on the bottom of the screen
should say, ”Command(s) completed successfully.”
TSQL Step 2 -Use new Database
Delete that line of TSQL and enter: USE Test2; . Again press execute. This tells the Query window to
run any future commands against they newly created Test2 database rather than
TSQL Step 3 -Create new Table
Next, we’ll create an Employees table to hold data. Delete
any TSQL in the Query window and execute the following:
CREATE TABLE Employees
TSQL Step 4 -Enter Data
Execute the following statement to
enter data into the newly created table.
INSERT INTO Employees
INSERT INTO Employees
INSERT INTO Employees
TSQL Step 5 – View Data
The final step is to view
the data just entered. Remove the previos INSERT statements and exeucte the
As shown below, all the
newly added records will be displayed.
In this article we used both the
Grafical User Interface and TSQL command to create a new Database and a new
Table. Values were inserted into the new table and then retreived. If your
SQL Server was installed using defaults, then our examples just created are stored
on your harddrive under c:\program files\ Microsoft SQL Server \ MSSQL \ Data.
There should be a Test. mdf (the actual data), and Test.ldf (a transaction log
of the changes we made to test). These files are binary specific to SQL Server
and cannot be used by Notepad, Word, Excel, or someother appliction.