Installed SQL Server?...Now What? (Getting Started)
![Kuoxsr - Conformity [audio only]](http://img.youtube.com/vi/Pia18cGVDqQ/2.jpg)
Uploaded by learningsqlserver on 07.02.2011
Transcript:
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!!
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!!