Hi. I’m Jason Roth. In the last video we
installed SQL Server 2008 R2. If you’re new to SQL Server, you may be
thinking, “Now What?” In this video I’ll show you how to create
your first database. We’ll then install a sample AdventureWorks
database. And we’ll conclude by importing a spreadsheet
into a database. This is not a video on database design, but
it should give you some invaluable hands-on experience with the tools.
And you can immediately begin using SQL Server. It all starts with a tool called SQL Server
Management Studio. On the “Start Menu”, click “All Programs”,
expand “Microsoft SQL Server 2008 R2”, and then start “SQL Server Management Studio”,
also known as SSMS. SSMS is an all-purpose SQL Server tool that
you will grow to depend on. First, you have to connect to your database
instance. Make sure “Server type” is set to “Database
Engine”. Next, type the “Server name”. If you just
installed the Express edition, then the default instance name is “.SQLEXPRESS”.
The period represents the local machine. If you installed another edition of SQL Server,
you may have installed a default instance. In that case, you could connect simply by
using the period alone. Click the Connect button. In the left pane
of SSMS, you can see the Object Explorer. This gives you a view of the SQL Server instance
you just connected to. If you expand the “Databases” node, you’ll
see that a clean installation SQL Server contains no user databases.
But creating one is easy. Right-click on the “Databases” node, and then select “New
Database…” Give the database a name, such as “MyDatabase”.
As with most objects in SQL Server, databases have many configurable options.
But if you’re getting started, it’s easier to accept the defaults.
Click OK to create your new database. Object Explorer shows the “MyDatabase” database
you just created. If you expand this node, you’ll see many
subsections that help you to manage the database. But you don’t have to understand or use
all of these features initially. Instead, let’s look only at Tables.
As described in a previous video, the data in your database is stored in tables.
Each table is similar to a spreadsheet with rows and columns.
Let’s add a table to our database. Right-click the “Tables” node, and then
select “New Table…” The table designer allows you to define the
columns of your table. I’m going to create a table to track my
friends’ birthdays. With databases, you should always have one
column uniquely identify each row. Since I could have two friends with the same
name, I’ll associate a number with each friend
in a column called “Id”. The data type of the column restricts the
type of data that can go into that column. Since “Id” is a number, I’ll set the
data type to “int”, which stands for integer. In future videos we’ll also make this our
Primary Key, but for now, let’s keep things simple and continue.
For the FirstName and LastNames columns, set the data type to “nvarchar(25)”,
which means a variable length string with a maximum of 25 characters.
The Birthday column can have a “date” data type.
If the value for a column is required, then uncheck the “Allow Nulls” checkbox.
Save the changes. In the “Choose Name” dialog, type Friends, and then click OK.
You can now see the Friends table in the Tables section of Object Explorer.
By default all database objects are owned by the “dbo” schema, so the table name
is displayed as “dbo.Friends”. Of course, there is still no data in this
database. Let’s add some friends to our table.
Right-click on the Friends table, and then select “Edit Top 200 Rows…”.
This view allows you to enter and update table data. Enter a few rows.
Each time you go to a new row, the previous row is committed (which means saved).
When you’re done, close this editing window. Now that we have data in the table, how do
we review that data? You may have noticed a trend in the tasks
so far: right-clicking in the Object Explorer provides
access to most management tasks. So right-click on the Friends table, and then
click “Select Top 1000 Rows…” This time it generates a query, which is just
a question about our data. This query requests to return the first 1000
rows of the Friends table. The results are displayed below.
Queries can be a powerful tool for analyzing data. For example,
I can edit this query and add a condition to show only friends whose first name starts
with the letter ‘J’. We’ll discuss queries more in future videos.
Now that you’ve had some experience creating your own database, let’s load up a well-designed
database sample. The AdventureWorks database samples are often
used in tutorials and documentation. AdventureWorks stores sample sales data for
a online bicycle store. Let’s look at how to install AdventureWorks
on your SQL Server instance. Go to http://msftdbprodsamples.codeplex.com.
Select the sample databases link that matches your version of SQL Server. In my case, it’s
SQL Server 2008 R2. Click the installation link, and accept the
license agreement. Run the installation program.
Accept the second license agreement, and then click Next.
In the Installation Instance, choose a SQL Server instance.
In my case, I only have SQL Server 2008 R2 Express installed, so the SQLEXPRESS named
instance is my only option. Note that there are several databases that
you can install. Depending on the prerequisites, you may be
restricted from installing one or more of these.
Click “More Information” next to the database to learn more.
However, if you just want to get a sample database for testing and learning,
you don’t necessarily require all of these databases.
In that case, I recommend only selecting the “AdventureWorks LT” database.
Click Install. When complete, click Finish.
In SSMS, you can press F5 to refresh the Database node. You can now see and expand the AdventureWorksLT
database. You already know how to select the rows in
each table. For example, here are the rows of the Product table.
For learning purposes, you can easily examine the nodes under each table to learn more about
the database design. For example, if I expand the columns of the
Products table, I can see all column names and their data types.
For our final example, say you have existing data, like this customer spreadsheet.
It’s possible to import this data into a database.
In SSMS, right-click on your database, click “Tasks”, and then select “Import Data…”
Set the data source to “Microsoft Excel”. Provide the file path to the spreadsheet.
Click Next several times and then Finish. If you refresh your tables list, you’ll
see the imported table. All of the spreadsheet data is now in your
SQL Server database. Obviously, this import process has more complexities,
but we’ll save that for another video. This video has just scratched the surface
of what you can do with SQL Server. But you should now be more comfortable using
SQL Server Management Studio to explore SQL Server and begin working with databases.
In the next video, we’ll look at how to write Transact-SQL queries.
For more information and resources go to http://learningsqlserver.wordpress.com. Thanks for watching!!