Getting Started with SQL on SQL Server (Part 1)

This is a blog post that I’ve tried to write three times now.  My intent is to provide someone new to SQL (and SQL Server) enough background and theory about SQL and the relational model that makes their transition to using SQL in their job easier than mine was but not so much theory they get overloaded and fall into a state of analysis paralysis.  There are a couple of reasons I have found this difficult to write.

  1. There is a lot of theory behind SQL that has to be glossed over in a guide that is intended to help someone to get their brains around SQL.  What should and should not be covered is, for me, a difficult decision to make.  Too much theory and it can’t be applied immediately.  Not enough theory and lessons are immediately applicable but it harder to know how to expand on them for different but related uses.
  2. SQL and the relational model aren’t the same.  They use different terminology and SQL will allow you to do things that aren’t preferred in the relational model.
  3. To go from practically zero knowledge about SQL to being able to query a database and reliably get the data back that you want requires quite a bit of upfront knowledge.
  4. In the relational database world many of the words used: entity, column, attribute, row, record, set, etc. have a nuanced definition that often depend on who you talk to.  Some are used interchangeably or synonymously by some people while others insist on strict usage of certain words for certain phenomena.  A surprising number of flame wars revolve around something so innocuous.  It is easy to get bogged down by these nuances in an attempt to please all parties.  But in doing that what was intended to be a short series of blog posts becomes a book.

To write this I had to make some decisions about what terminology to use and what theory to reveal.  In general, I have fallen to using the terminology that is used in SQL Server, table instead of relation, row or record instead of tuple, column instead of attribute, etc. In a few places I throw in my own unique vernacular as well.  It is probably things like this that got me banned from speaking at PASS.  I kid, they didn’t actually ban me.  My invitation got “lost” in the mail. C’est la vie.

The book Pro SQL Server 2008 Relational Database Design and Implementation is an excellent resource to get a better grasp on the nuances of relational vocabulary and other relational theory.  CJ Date’s book SQL and Relational Theory: How to Write Accurate SQL Code is another good source of info about relational theory and he often expounds on when relational theory and SQL differ.

However we aren’t designing a database but learning how to query one and those nuanced distinctions, while good to know, aren’t necessary or possible for a blog post sized guide.  In practice I’ve heard renowned members of the SQL Server community use many of these terms interchangeably and have yet to meet someone on the job who cares if I call a row a record or a column an attribute.  If you are a newbie to SQL Server and you run into one of the few pedantic souls who cares, don’t worry about it.  If you work for one of them you can always quit. It can also be fun to intentionally use the “wrong” word just to tweak them. You can almost always tell from the context what someone means even if they don’t use the “correct” word.

Why I am writing this.

There are some things that I wish I had been told when I first got into databases that would have made the job much easier.  For people who have been working with SQL Server for years this information will be elementary and good to read if it is late and you are out of sheep ( uh…for counting, get your mind out of the gutter.)  It’s the kind of thing you will learn by necessity as you, by trial and error, try to extract data from the database using SQL.  But for those who haven’t been using SQL for years I hope this little introduction will make the move into querying a SQL Server database a bit easier.

So this will go into the category of “Things I wish someone had told me.”  It is very basic stuff but most of us don’t start out writing great SQL queries on our first day just like we don’t start reading Shakespeare when we are four.

My first real post in this series will be about sets, rows and columns.