SQL Server Reference Guide

The data inside a database isn't very useful unless you can access it. And even using the management tools within SQL Server isn't how everyday users expect to interact with the system.

SQL Server provides a rich set of interfaces that you can use to access, edit and control the data in its databases. In this section of the guide, I'll explain how to plan, create and maintain programs to access the system.

Speaking of a test system, in a few moments I'll explain where to get everything you need to create and use your own programs. In fact, you can get everything you need for free — even for programs you'll use at your company. I'll direct you to the licensing information for these free offerings so that you can coordinate that with your IT department.

Programming Languages

SQL Server has only one programming language — at least for version 2000. This language is called Transact-SQL (T-SQL). T-SQL is an extension to the Structured Query Language — or SQL (pronounced ess-que-ell or "sequel") — that was developed in the late 1960's. This language is a procedural language. That means it proceeds from one step to another, sometimes with commands that allow it to "branch out" or move to another section of the code. Transact-SQL adds things that are not found in the original SQL specification, usually dealing with SQL Server specific features or administrative tasks.

T-SQL doesn't have very sophisticated screen-handling capabilities. You can't create a Microsoft Windows application, or create a button with it. It deals almost entirely with handling data or the data subsystem.

When you write a program that your users will run on a Windows (or other operating system) based computer, you'll need to use another language. These days most of the programming languages are object-oriented, meaning that they work with an object in lots of ways, rather than moving down a set of steps as in T-SQL. There are a lot of these languages out there, with the notables being C++ for Windows and UNIX operating systems, and C# and Visual Basic for Windows-only systems. There are lots of others, of course, such as Java, that are available for many operating systems.

Interestingly, when you write a program for your users, you'll often use an object-oriented language for much of the code, and then embed Transact-SQL statements within that code when the users want to talk to the database. SQL Server also provides a set of mechanisms that I'll describe in a moment that allow you to work with the data entirely in the object-oriented language. If you learn to do things this way, you don't have to use Transact-SQL at all.

Beginning in SQL Server 2005, you can create object-oriented programs that run inside SQL Server. This capability is provided by the Common Language Runtime (CLR) feature, and while you won't use it to create user interfaces, you can use the power of the object-oriented language within the database engine itself. This makes a lot of sense when your client program is on a system that doesn't have a lot of power, or you want to do complex things with the data SQL Server stores.

Application Programming Interfaces (API's)

I mentioned that SQL Server allows you to talk directly to the database without having to use the Transact-SQL language. It does this by providing another software layer that sits in-between the database and the program that is "talking" to it. This kind of program is called an Application Programming Interface, or API.

There are several API's for SQL Server, and all of them do slightly different things. Some API's allow you to work with data from the database engine, and others allow you to work with Analysis Services or another feature.

In most programming languages, you have the ability to reference, or point to, an API. Other languages call this an "Include," because you "include" the program bits from the API into your program. It's kind of like getting some of the programming for free.

When you reference or include these API's into your code, you automatically get several statements you can use to access data and control the system. Each programming language implements this process a little differently, as you'll see throughout these tutorials.

Locations of SQL Server Programs

When we talk about SQL Server programming, we're not just talking about one thing. There are actually several places that you can write SQL Server Programs, and they can even run in several different locations.

Server-Side Programming

You'll often hear the term "Server Side Program." This just means that the code you write, which are after all just a set of instructions, runs on the server. Even within this classification there are a few places the code can be placed.

The first is code that you create in SQL Server scripts that you, as a developer, run on the server for maintenance or other tasks. You're able to save this code in plain-text files, called "scripts." You can run those scripts by opening them in Query Analyzer (for 2000) or SQL Server Management Studio (for 2005 and higher).

You can also create scripts that aren't stored in a file, but are stored on the server and called by you or your programs. These scripts are called Stored Procedures, and you can learn more about them here. The interesting thing about these programs is that you only have to write them once, and multiple object-oriented programs can call them to run on the server but provide results back to your client program.

Finally, you can write programs that stay on the server, but are actually running as a client. You'll see this quite often in web-based programs, where the users access a website, which runs code on the web server, which may have the database system installed on it. This isn't technically server-side programming, but I mention it here in case you see it in other documentation. This type of programming might be written in Transact-SQL, in an object-oriented language, or in another type of procedural code called scripting languages, such as Perl or Visual Basic Scripting (vbs). These scripting languages normally embed T-SQL to send on to the server.

Client-Side Programming

Client-side programming is what you may automatically think of when you hear about a particular software package. This type of programming uses object-oriented languages that are compiled into an .EXE or other extension that the operating system can run. The code inside may use T-SQL or might use an API to access the data for the user.

Using a client-side or server-side program doesn't actually determine your program's architecture. You can create an application that talks directly to SQL Server (through T-SQL or an API call), which is called a Client/Server architecture. This kind of architecture was quite popular when personal computers were first networked to a server.

As time went on, another server was added between the database server and the client programs. This allowed one server to access the data for multiple kinds of users, and this type of architecture is called 3-Tier, because of its three parts.

Modern programs actually implement even more servers between the application and the data. These systems use an N-Tier architecture, because any number (N) of servers can be used.

Yet another system layout is the Service-Oriented Architecture. This architecture puts most of the program on various servers, whose job it is to "advertise" through electronic means what they are able to do. This server, for instance, might process orders, while that one might handle shipping. The client program in this case only needs to advertise what it needs done, and the entire system springs to life to handle the request. Starting in SQL Server 2005, features are built-in to create and manage this kind of architecture.

Where to get what you need

Now that you know what you can do with the server, you probably want to know where and how to get your hands on one.

SQL Server

Microsoft sells SQL Server, of course, and there are various editions that they have that handle different levels of service. But you can also download an edition of SQL Server for free.

This edition is called SQL Server Express, and it's completely legal to download it and install it for free, even for your company's commercial use. You can learn more about it here.

Programming Languages and Compilers

When you get SQL Server, you get everything you need to work with data using Transact-SQL. But SQL Server isn't an object-oriented language editor and compiler, so you'll need to choose a language and learn about how to use it.

Many languages are free, but even so they have specific licenses about how you can use them. Microsoft has another set of free software on the programming side, called the Express Editions. You can download any of these, and all of them work against SQL Server.

There's a wealth of information on programming languages on the web and at your library, not the least of all here at InformIT.

InformIT Articles and Sample Chapters

Although I'll cover SQL Server programming in this section of the guide, there are literally thousands of articles, books and tutorials right here on InformIT.

Online Resources

For SQL Server programming, there is no better reference than the Microsoft Developer Network.

Click Next to continue reading about Microsoft SQL Server Programming. Next topic: An Outline for Development