Nov 12

So you want to learn SQL Server? – Part 1

After attending the PASS Summit last week, I really got pumped to start blogging and speaking. I wanted to talk about spinlocks and latches, parsing xml, and PowerShell. However, I realized that there are so many people out there that want to get started in SQL, but don’t know where to start. Inspired by my wife and by best bud, Brett, I decided I should start a series on getting started with SQL Server.

In this series, I will go through the real basics. We’ll start with installing a local instance of SQL Server and the AdventureWorks database. Then we’ll see how this evolves. My goal is to work with the people around me who know nothing about SQL to make this the best tutorial that exists for learning SQL.

Step 1 – Installing SQL Server for Learning

There are several editions of SQL Server. For now, I want you to stick with the free version, Express. You can download the latest version here. If you don’t have a Microsoft account already, you will need one.

Fill in the required information and then download the “SQL Server 2014 Express with Advanced Services 64 bit”. (32 bit if you have a 32 bit machine, but these are rare in today’s world…) Make sure you save it to a known location, like “Downloads” or “Documents”. Now, navigate to that location and double-click on the file. You may (depending on Windows version and settings) get a prompt that asks if you want to “Do you want to allow this app to make changes to your PC?”. Just click yes which will give you one more prompt asking where you would like to extract the files. You can just click “Ok” and use the default.

After the self-extracting installer is complete you will get a window that looks like this:
If it doesn’t show these options, make sure that “Installation” is selected on the left (see how it is BOLD in the image above?). Then go ahead and click on “New SQL Server stand-alone installation or add features to an existing installation.” This will cause another window to open up which will be your main window to complete the installation. Now, I’m not going to go into best practices for installing SQL Server here. This series is strictly for learning the scripting language and how relational databases work. Therefore, we will be accepting most of the defaults.

The next screen will look like this:


You can just go ahead and click “Next” to take you to the next screen. This is the screen where we will select what to install. The main things that you will need to learn to script T-SQL are Database Engine Services and Management Tools. I also recommend installing Documentation Components to make it easier to navigate “Help” and Books Online (BOL). However, it won’t hurt you to install other components. Especially if you plan on exploring more aspects of SQL in the future. My configuration for this install is in the image below.


Click next to move on to the “Instance Configuration” section. On this screen, I change the radio button from “Named Instance” to “Default Instance”. This should change the instance ID to MSSQLSERVER. This is important because anything other than the default has to be referenced by name. This allows several “instances” of SQL to be installed on the same machine. Make sure your screen looks like mine below and click “Next”.


The next screen is where you can set specific service accounts for the services to run under. We’ll leave this as default for this install.


Click “Next” again to take you to the “Database Engine Configuration” section. Select the “Mixed Mode” option and enter a password you will remember. This becomes the admin (sa) password for your SQL Server instance. Also, click on the “Add Current User” button to add yourself as an administrator.


Again, click “Next” to move on to the “Reporting Services Configuration” screen. If you didn’t select the option to install Reporting Services, you can skip this. Else, you can select the “Install Only” option.


Click “Next” to begin the install. When it completes, you will get a final conformation screen.


That’s it! You’ve completed your install of SQL Server Express! Let’s check to make sure you can connect. Hopefully you are all on Windows 8.1 or later. If you are, use the shortcut Windows+s and search for “SQL Server Management Studio”. If you are on an older version or prefer to navigate, just go to “All Programs” or “All Apps” find the folder for “Microsoft SQL Server 2014” and expand it. Then go to “SQL Server Management Studio 2014”. Open that up and a window will pop up that looks like below, but you will have to type in “localhost” for the server name.


If you did everything as I said above, you can click “Connect” and everything should work fine, giving you the screen below:


WOOHOO!!! Now have the ability to create, restore, and attach databases. In the next post, I will describe how to download the AdventureWorks database and get it onto your system. If you have any questions or this doesn’t work. Email me at jkarney@sqlknowitall.com or send a tweet to #SQLHelp



Leave a Reply