SQL Server Reference Guide

Before you can develop applications against your SQL Server database, you need to learn about Transact-SQL (T-SQL), which is Microsoft's superset of the Structured Query Language (SQL) invented to query relational databases. Formally defined, the SQL language is a set-based, transaction-oriented language — meaning that it works more like a scripting language or batch file (or even older versions of BASIC) than it does like an object-oriented programming language. That has advantages and disadvantages, but either way you will need to learn to use it to work with SQL Server databases. Fortunately, it is simple to pick up. In this tutorial, I will cover T-SQL in a general sense, and I will explain a little bit about the most common command, SELECT.

SQL is a language that is composed of two types of commands: Data Definition Language (DDL) and Data Manipulation Language (DML). You use DML commands to select, insert, update and delete data; and you use DDL commands to create and alter tables, views, and other database objects. In the next few tutorials, I will explain the DML commands for the most part, but I will also cover some DDL along the way.

The beauty of SQL is that the constructs and syntax are very logical. There are just a few simple commands to learn, and then it is just the intelligent layering of those commands and you are on your way to some fantastic programming.

Transact-SQL (T-SQL) adds Microsoft SQL Server platform-specific extensions to the basic SQL language. It contains all of the standard SQL commands, and completely new ones to do the work Microsoft SQL Server allows you to do.

Most of the time a group of SQL commands is referred to as a "query" or a "batch," and sometimes a "script." Technically, a query is a group of commands that return data, a batch is a set of T-SQL Statements that run as a single unit, and a script is a text file made up of T-SQL statements. I will use the term "query" throughout this article to mean not only those statements that return data, but also statements that do any kind of work.

One final word about the general use of T-SQL is the methods you can use to send the query, and the ways in which you can receive the results. The SQL Server Instance does not care whether the query comes from third-generation programming code, the osql or SQLCMD command-line interfaces, or the graphical tools such as Query Analyzer (QA) or SQL Server Management Studio (SSMS).

In fact, the graphical tools are just programs that use the same interfaces that other programs use. The way that all these programs work is that they establish a connection to a SQL Server Instance, access a database, run commands, and then receive results. In the case of QA or SSMS, you open the tool, type in the name of the server to access and then provide the login credentials to access that server. I will use SSMS in this tutorial since it can connect to SQL Server Instances from version 2000 to the latest release. The general rule of thumb in this area is that a higher-level tool can usually interact with about three versions back — so in my case, SQL Server 2008 SSMS can work quite well with SQL Server 2000 Instances.

I like working with SSMS 2008 as well because it has syntax coloring. Syntax is the format of the statements and commands you type, and as you type a command in SSMS it will turn a color based on what it does. That means the word SELECT is a different color than @@VERSION. That helps you quickly see if your query is spelled correctly — at least the T-SQL commands! SSMS 2008 also has auto-completion for databases, tables and so on, and it includes a Debugger — something I will cover in another tutorial.

In SSMS, you can open a new query by clicking on the New Query button. Depending on how you connected, you might be prompted to enter the name of a SQL Server Instance and your connection credentials. When you see the blank window for the query area, you will notice the name of the database you are "in" (called the database context) in the top-left-hand side of your screen.

Comments

I start almost every query (unless it is quite short) with a set of comments. Comments are code that doesn’t run, and in T-SQL you have two options to include them in your script. The first option is called a block comment, and it starts with a forward slash and an asterisk (/*) — you then type whatever you like and "close" the comment block with an asterisk and a forward slash (*/). It all looks like this:

/* This is a comment. 
It will not run. */

You can also use a line comment. This type of comment has two dashes (--) and is terminated by pressing the RETURN key, like this:

--This is a comment and will not run. 

I try not to use many line comments, since they might get split somehow when you copy and paste them and break the code. Also, block comments let me span multiple lines, so I think that’s a bit better to use.

Query Context

I do not rely on the graphical setting of the database context I mentioned a moment ago, and I recommend that you do not either. It is better to ensure that you set the proper database context before you start, so that you do not run commands (like an INSERT or CREATE) in the wrong database. You can set the database context with the USE command. It looks like this:

USE AdventureWorks;
GO

Spaces and Cases

There are a couple of interesting things to note in that last bit of code. The first is the spacing. Case and spaces aren't important for the commands in T-SQL, except within a word. So this command is the same as the one above:

usE 
  		AdventureWorks;

gO

But this won't work:

US
 E pubs
 GO

Because, as you can see, I have broken the USE command. In any case, nothing happens until you press the F5 key or select the green arrow from the icon bar.

For clarity, I will try to capitalize the keywords in SQL, and to keep the indentation logical.

Learning to Read T-SQL in Books Online

Whenever you type a command in T-SQL in SSMS, you can highlight that command and press SHIFT-F1 to find the proper syntax for the command. But when the Books Online reference comes up, it can be a little daunting to read. Consider, for instance, the CREATE DATABASE command, found here: http://technet.microsoft.com/en-us/library/ms176061.aspx. There are quite a few symbols, italics and other conventions that can be hard to read.

But it really isn’t that hard. There’s a "key" to all of these symbols located here: http://technet.microsoft.com/en-us/library/ms177563.aspx that will help you dissect the statement. Open this page in another browser and I will walk you through a simple example: http://technet.microsoft.com/en-us/library/ms187928.aspx.

This is the CAST and CONVERT statement reference, which is a function you can use to take one kind of data (such as text) and convert it to another (such as a number). Don’t worry about actually using this function right now — focus more on the syntax in the gray box at the top of the page.

Take a look at the syntax for just the CAST statement:

CAST ( expression AS data_type [ (length ) ])

First, the keywords in the syntax are in upper-case. That means you have to type those as they are. Second, the parentheses are not in between a set of brackets [ ], so that means they are not optional. But the length part of the syntax is — meaning that you don’t have to include it.

Both the expression and data_type syntax elements are explained further in the topic page.

While the CREATE database statement is far more complicated, it uses the same concepts. You can open that "key" page of the T-SQL conventions and keep it open while you learn more complex commands, and after a while you won’t need it anymore.

I also use the Books Online method to see the examples, so that I know how to us the commands. Using both the syntax and the examples along with the concepts in the topic page usually gets me where I need to go.

Example T-SQL using the SELECT Command

Now that you understand the basics of the T-SQL environment, you can experiment with one of the most common, powerful commands in the language — SELECT.

This command returns data from a table or view or even a function. For now I will concentrate on selecting data from tables.

At a minimum the format of the select command looks like this:

SELECT something
FROM somewhere

The something part is normally a column in a table, like this:

 SELECT Title

And the somewhere part is normally a table name, like this:

FROM Conect

So if you're playing along at home and you have the AdventureWorks sample database installed, open SSMS, connect to a server, type these commands and press F5:

USE [AdventureWorks];
GO
SELECT 
       [Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [AdventureWorks].[Person].[Contact];
GO

Wait — there’s much more here than I’ve explained. First, there seem to be quite a few brackets [ ] all over the place. Those actually aren’t required, and I’ll drop them from now on. They allow you to have spaces or other characters in the object names (like a table named [This is my table], which is just a horrible idea) without breaking the code. Also, you’ll notice that I have [AdventureWorks].[Person].[Contact] in the FROM line, which is simply the database name, a period, the schema name (more on this here) and a period, and then the table name. I won’t cover the schema discussion here, since it isn’t important for this discussion, but in many cases the schema is the letters dbo. Read that other article to learn more about that.

Back to the format. You’ll also notice that I have each column is on its own line. That is my convention, and is not required — in other words, this also works:

USE [AdventureWorks]; 
GO
SELECT [Title], [FirstName], [MiddleName], [LastName]
FROM [AdventureWorks].[Person].[Contact]; 
GO

I separate the columns and place them with their preceding comma so that I can easily read and correct my code. You should follow whatever best practices your work environment uses. If your work environment doesn’t have any, you should research what other folks do and use what fits in your organization best.

You’ll also notice I included a semicolon and then the word GO on its own line. What the GO keyword does is separate commands into a group, called a batch. While it is not always necessary to have a GO, it is a great practice to get into, since sometimes it's critical. By the way, the keyword is configurable, so we can set a different batch separator if you wish.

Suppose you want the column of names in "lastname, firstname" format? You know what you want, and you now know how the SELECT command works, so all you need to do is a little formatting, and for that you can use the plus-sign to put everything together along with a comma and a space in between. In T-SQL, "literal" words or symbols you want are placed between the single "tick" mark, which on most keyboards is an un-shifted quote-mark:

SELECT 'something I want to show up on the screen';
GO
Let me put all that together:
USE [AdventureWorks]; 
GO
SELECT LastName + ', ' + FirstName
FROM [AdventureWorks].[Person].[Contact];
GO

Using the plus sign instead of a comma between the fields concatenates (or "brings together") the values in the columns. Adding the ', ' part gives you the comma and a space to separate the names.

Continuing with this "layered" approach, I now decide to title the column that contains the names as "Names" Here's how I can do that:

USE [AdventureWorks]; 
GO
SELECT LastName + ', ' + FirstName AS 'Names'
FROM [AdventureWorks].[Person].[Contact];
GO

Pretty neat! And all with just the SELECT and FROM keywords. In future tutorials, I’ll explain how to layer these commands even further to make some pretty impressive code.

I’ll take this command a little further, and apply a function to the column that is returned. A function is simply code that SQL Server runs on the column you specify. If I would like all the last names in upper case, regardless of how they are stored in the database, I can use the UPPER() function. Here's the command to do that:

USE [AdventureWorks]; 
GO
SELECT UPPER(LastName) + ', ' + FirstName AS 'Names'
FROM Person.Contact;
GO

Running this query now gives me the last names in upper-case.

Sometimes, formatting exercises like this one can throw you a curve. For instance, if the last names have a few spaces at the end, the comma moves too far to the right. Since I do not know if the data has this issue, I need to plan ahead to take care of the problem in my query. Luckily SQL Server provides yet another function, called RTRIM(), which removes spaces on the right of a column. I will combine this function to fix the problem:

USE AdventureWorks; 
GO
SELECT UPPER(RTRIM(LastName)) + ', ' + FirstName AS 'Names'
FROM Person.Contact;
GO

This is called "nesting" a function. The only tricky part here is to keep the parentheses straight, which is why it is so helpful to use SSMS to check the syntax. You will receive a red "squiggly line" when the syntax is incorrect, which you should check for before you press F5.

So far I've used column names with the SELECT command. What if you don't know the names of the columns? You can easily get all of the columns and all of the rows in this table with this command:

USE AdventureWorks; 
GO
SELECT *
FROM Person.Contact;
GO

I don't recommend using this technique to "explore" a table. The reason is that it causes SQL Server to do something called a "table scan," which is quite costly in terms of performance. Not only that, there may be millions and millions of rows, which ties up the system and locks other users from updating data while it's retrieved. To avoid these dangers, you can modify this command slightly with the TOP construct:

USE AdventureWorks; 
GO
SELECT TOP 10 *
FROM Person.Contact;
GO

The TOP modifier returns only the number of rows you ask for, but gives us some data as well as the headings you want. You can specify any number of rows that you want after the TOP modifier.

SELECT has many other uses — it forms the basis of lots of Transaction SQL Commands. Later, in the tutorial on JOIN operations, I will explain how to make more complex SELECT statements.

InformIT Articles and Sample Chapters

If you are still using SQL Server 2000, you definitely want to upgrade to the later versions. There’s an article here on T-SQL Enhancements in SQL Server 2005.

Books and eBooks

Ryan Stephens and team has a great book on learning SQL in just one hour a day, Sams Teach Yourself SQL in One Hour a Day, 5th Edition.

Online Resources

You can get a great tutorial on SQL free at this location: http://www.sqlcourse.com/