If you’re new to SQL Server and searching for online training, I’d just like to say:
In this post, I’d like to provide some tips for how a new SQL Server user can better use online resources with less frustration.
SQL Server is a complex product that is meant to serve a variety of needs, from the smallest application to corporations managing terabytes of data. But this presents two common problems in looking for learning resources online:
- It’s hard to know which areas are core SQL Server skills versus a specialized feature.
- Many trainings assume a level of knowledge that you may not have yet.
Let’s look at the problem a little closer and then dive into some practical advice.
Let’s say you’re just getting started with SQL Server or possibly just learning more about databases. You search for training on SQL Server 2008 R2 and see a tutorial on using ”Master Data Services”. Although this feature solves a specific business need, it is not part of the core SQL Server knowledge that you need to get started. But how would you know this if you’re new to SQL Server? You wouldn’t.
There is another variation on this. Let’s say you find a video titled, “Learning SQL Server 2008 R2″. After congratulating yourself and pressing play on the video window, you find that it covers all of the improvements of 2008 over 2005. This is great if you are using SQL Server 2008 or 2005, but it doesn’t help you learn core SQL Server skills.
|Note that if you have a requirement to learn a specific area of SQL Server, then you may have to skip straight to that area and do your best. But even then, if you’re new, it may help to go through the following tips as quickly as possible to give yourself a basic framework.|
Tip 1: Database Concepts
If you’re new to databases, the first thing you want to do is to look for topics that cover basic database concepts. SQL Server is a “Relational Database Management System” (RDBMS), so you’ll want to learn relational database concepts. These include understanding concepts like: tables, rows, columns, primary keys, foreign keys, and queries. At this point, stay away from highly-technical articles that tell you how to do it. Also stay away from highly academic articles. Just try to get a feel for the big picture of why databases exist and what are some common characteristics of relational databases. Here are some links that might help:
- Before We Begin: What is a Relational Database?
- Introduction to Relational Databases (DatabaseJournal.com)
- SQL Server 2005 Express for Beginners Video Series
- Introduction to SQL (Beginning Database Training) (Joes2Pros)
Tip 2: Install SQL Server
Before you go much further, you should install SQL Server on one of your machines. If you own a copy of SQL Server, such as the Developer Edition, use that. Otherwise, install SQL Server 2008 R2 Express. It’s free! By installing SQL Server, you can start to go through tutorials or just experiment to speed up your learning. Just note that it may already be installed by other programs. For more information, see the following links:
- Download page for SQL Server 2008 R2 Express
- What is SQL Server Express?
- How to Install SQL Server 2008 R2 Express
- SQL Server 2008 R2 Installation (MSDN)
Tip 3: Start with the Database Engine
I don’t mean that you should only search for topics about the SQL Server Database Engine, although you could benefit from knowing where this documentation lives. Instead, I’m pointing out that there are several major components of SQL Server. I’ve copied a graphic out of the documentation to illustrate this.
Here’s the thing: it’s going to be hard to work with Reporting Services without data to report on. And you can’t perform Analysis Services without data to analyze. Plus, if you’re using Express, you don’t even have access to some of these components.
Focusing on the database engine means looking for training on using SQL Server to perform tasks like creating databases, tables, views, and stored procedures. It also involves learning Transact-SQL queries to read and change data as well as to script database management tasks. My advice is to learn to do things first in SQL Server Management Studio, which is my next tip.
SQL Server has a lot of components that you’ll want to learn about later. Just don’t let those features distract you from your initial goal of learning core database skills. For suggested links, see the next tip.
Tip 4: SQL Server Management Studio
Have you ever installed a product, looked in the Start Menu, and found that it installed several programs? SQL Server is like that, and you may wonder which one to launch. The easiest way to begin learning is through SQL Server Management Studio (SSMS). You can gain a lot of database management skills by using this UI. Then, when it’s called for, you can learn to script out more work using Transact-SQL, which I’ll cover next.
Here are some helpful links to help you get started with SQL Server Management Studio:
- Getting Started Video
- SQL Server Management Studio (MSDN)
- SSMS Basics
- SSMS Tutorial (Technet)
- Getting Started with the Database Engine (MSDN)
- SQL Server 2008 Tutorial (Quackit.com)
- SQL Server 2008 Express How-to-Guide Video Series (MsDev.com)
- SQL Server 2005 Express for Beginners Video Series
- How to Design, Create, and Maintain a SQL Server Database (Technet Wiki)
Tip 5: Transact-SQL (T-SQL)
Transact-SQL (T-SQL) is a “structured query language” for working with the data in your database. With the UI of SSMS and the power of development tools, you can actually go a while without knowing T-SQL. But eventually, you need to learn it. I personally think it’s really fun (like a word puzzle for how to get the data you want).
Here is a classic example of a T-SQL query to return all of the data from a table:
|SELECT * FROM MyTable|
You can run these queries in SQL Server Management Studio in a “New Query” window. There is an Execute button on the toolbar, or you can just push F5. Almost anything you can do through SSMS, you can do in T-SQL commands. And you can far exceed the capabilities of any UI with the power of queries and scripts. Plus, if you do development, there may be times when you have to understand T-SQL in order to write your code (although development tools try to hide this from you).
One of my favorite tips for learning T-SQL is to use two SSMS features. The first one is to right-click on tables in SSMS and select “Script Table as”. The submenus give you the choice to create many different types of scripts. Here is a screenshot:
So if you selected “INSERT To”, you’d see one example of how to insert rows (you’d have to replace some of the placeholders with actual values to get it to work though). Then you could look up the INSERT command in the MSDN Library to learn more options. Another SSMS feature like this involves a Script button that you’ll find at the top of some windows. For example, here is what happens when I click the down arrow next to the Script button on the “New Database” dialog:
If I select “Script Action to New Query Window”, then a new query window is opened with the T-SQL commands necessary to perform that actions that I’m about to take in the dialog. This can help you to quickly answer the question of “How do I do that in a T-SQL script?”
T-SQL is a big subject, but if you’re going to use SQL Server, it pays to invest some time with it. Here are some links to more information:
- Transact-SQL Reference (MSDN)
- Transact-SQL Tutorial (MSDN)
- Writing SQL Queries: Let’s Start with the Basics (MSDN)
- Video: SQL Server Query Tutorial (Joes2Pros)
Tip 6: Everything Else…
I could have easily kept adding tips until this post became a full-blown book or learning plan for all of SQL Server. But that defeats the purpose. My hope is that this post has helped you to get started, to use SSMS, to create databases, to write queries. Now you should have a framework for going off and learning many other things: Analysis Services, Reporting Service, Integration Services, Development, etc.
A few parting tips:
- If you want to learn features of the paid editions (like Analysis Services) without going broke, purchase an inexpensive copy of the Developer edition. You can’t use it in production, but you can test and develop against it. And it has all of the features of the DataCenter edition. For more information, see this page.
- Bookmark the SQL Server Developer Center. This place has tons of resources and is regularly updated.
- If want to create applications that use SQL Server, you have a lot of choices. For resources, check out the Data Development Center and the wiki post Introduction to Development with SQL Server. Note that my post is really focussed on the database itself. Getting started with database development is beyond the scope (but will hopefully be addressed on this blog in the future).