SQL Server Learning Resources for Beginners

Over the last couple of months I’ve been putting up some of my favorite learning resources. Recently my friend Robin Hunt (twitter | linkedin) at ThinkData Solutions asked me for some recommendations for some SQL Server learning resources for beginners. I thought I’d share that list here as well.

Books

Below are some of my favorite books on the subject. The links are to Kindle format, mostly because I’m a Kindle junkie, but you can also get the paperback version from a link on each page.

Microsoft SQL Server 2008 Step by Step – Any of the step by step books are good quality. There’s no 2012 version of this that I know of, but I’m sure one will be coming.

Microsoft SQL Server 2012 A Beginners Guide – This is a good resource if you are totally new to SQL Server. It’s very complete, covering all aspects of SQL Server Administration.

Introducing Microsoft SQL Server 2012 – This book is an easy read, so even beginners shouldn’t have any issues understanding it. It focuses mostly on new features in 2012, so it’s not quite a comprehensive book. But the Kindle version is FREE, so it makes a good resource for this list.

Professional Microsoft SQL Server 2012 Administration – If you already have some good technical background you might want something one step above a beginner book, but is still easy for people new to SQL Server to understand, then this is a really good book to get. (Disclaimer, I’m a co-author of this book.)

Videos

SQL Share – This is a really unique site. It’s designed for the busy professional. All the videos here are very short, generally in the 10 minute range, and focus on one very specific topic. As I write this, the featured video is on working with foreign keys. It doesn’t have anything in the way of an overall course though, so I’d suggest it as a great resource to learn more about a topic you are interested in, or perhaps read about but don’t quite understand. (Disclaimer,  I have some videos on this site.)

 

#sqlhelp

Another good resource is Twitter. Yes, Twitter. Do a search on the #sqlhelp hash tag and you’ll see all sorts of great questions being asked and answered. The SQL community does a great job of monitoring this hash tag and providing assistance.

Using PowerShell To Find A Value In A Set Of Files

I often present PowerShell at SQL Saturdays. One question I’m often asked is “Why learn PowerShell? I can do everything in T-SQL.” Well here’s a great example.

I was developing some SSIS packages that took a set of text files and loaded them into multiple dimension and fact tables. In the process I had one particular value that was creating duplicate values in one of the target tables. Obviously, I needed to find that value in the various files and see what’s different about it versus the other data.

The problem was there are close to 200 files I’m loading. Each file has hundreds of lines, and each line is over 200 characters long. Yikes! That’s a lot of data to try to look through. It would have been very time consuming to look in all of those files manually.

PowerShell to the rescue! In just a few minutes I created a PowerShell script to loop over the files, find the ones that had the value I was looking for, and list those file names. Made it easy then to open just the files I needed and look at the rows. I could have gone further, actually printed out the data, or perhaps load it into some array where it would count it, or more.

I decided to share the script, which you will see below. It actually took me longer to write up all the comments than it did to write the initial code. With the comments removed there’s only 9 lines of real code in the script.

Using PowerShell I was able to quickly find the rows I needed and find the issue. This is just one simple example of how PowerShell can be used to quickly solve a problem that might take a long time to accomplish in other languages.

 

#-------------------------------------------------------------------------
# Script: Search for a string in a group of files
# Author: Robert C. Cain
#
# Revision History
#   V01 - 05/28/2012 - Initial version
#-------------------------------------------------------------------------
# This PowerShell script will loop over a collection of text files 
# which are delimited somehow (comma, tab, etc) and look for a 
# specific value. It will then print out the name of the file. 
#-------------------------------------------------------------------------

# Here I've hardcoded a value to look for, you could also
# pass this in as a paramter if you convert this to a function
$ValueToLookFor = '123456789'

# Our data files have a header row, this should match the 
# names of those columns. This will allow us to address 
# the columns by name later in the routine
$header = "Column1", "Column2", "ColumnToSearch", "Column3", "Column4"

# Move to the location with all the files
Set-Location 'C:\MyData'

# Retrive a list of files that we need to look thru
# Note the use of the filter to only select some files
# If you are doing all files you can omit the filter
$files = Get-ChildItem -Filter "Data*.txt" | Select-Object Name

foreach($file in $files)
{
  # Load the data in the file into $data
  #
  # In this example the files are tabbed delimted, hence the need
  # to specify the tab character as a delimter. If your file is
  # comma delimted you can omit the -Delimter, otherwise specify
  # whatever yours is. 
  #
  # We also need to pass in the $header variable so PowerShell 
  # will be able to associate each column of data with a name
  $data = Import-Csv $file.Name -Delimiter "`t" -Header $header

    foreach($line in $data)
    {
      # Here's where the header comes in handy, I can now use the 
      # column name as the .Property to look in
      if ($line.ColumnToSearch -eq $ValueToLookFor)
      { 
        # Print out the name of the file. You could do other things
        # like print out the actual line, or load it into a variable,
        # just to name a few. 
        $file.Name
        
        # Use a break if you only want the file name listed once
        # I actually let it print out multiple times to indicate how
        # many times it's in the file

        # break  
      }
    } # foreach($line in $data)
} # foreach($file in $files)

Column Cut Copy Paste in VS SSMS and PowerShell

Did you know it’s possible to do Column based cut, copy and paste in Visual Studio, SQL Server Management Studio, and PowerShell v3? Not many people do. Even less people know that with VS 2010 and SSMS 2012 you get a little “extra” functionality. Watch the video to find out all the juicy details.

 

Atlanta Code Camp 2012

It’s time again for the Atlanta Code Camp! Still a few hours left to register, you can do so at http://www.atlantacodecamp.com/. Why would you want to come? Well for one to see me, I’ll be giving two presentations.

The first is right after lunch, The Decoder Ring to DW/BI (Data Warehousing / Business Intelligence). In this talk I’ll walk through all the concepts behind designing a data warehouse, including some real world examples to help you understand the differences between Facts, Dimensions, Surrogate Keys, and more.

You may think a BI talk is an odd one for a developer oriented day. More and more though developers are being directed toward the data warehouse to get information for their applications, to combine with the system they are designing. Understanding how data warehouses work will give you a leg up when your company establishes it’s own data warehouse.

The slides for my presentations can be downloaded HERE.

My second session is “Become a PowerShell Pop Star”. In this very fast session we’ll start at ground zero with PowerShell, Microsoft’s scripting language. You’ll see all about cmdlets, variables, functions, programming and more.

Follow

Get every new post delivered to your Inbox.

Join 101 other followers