SQL Server Reference Guide

There are multiple places where you can write code against a SQL Server system, depending on the language you are using. In this overview, I’ll explain a few of the options you have, and where you might choose to use each one.

The data layer of programming is one which has the largest variety of people working with it. At one end of the spectrum you have the developer who normally uses an “application” programming language, like Java, C#, C++, Visual Basic.NET or even ASP.NET. This type of developer probably writes code that users will interact with through some sort of User Interface (UI). Sometimes these interfaces are graphical, and sometimes they are command-line based or receive their input from another program or device. If you’re developing in that way, you probably already have your environment and tools picked out. Perhaps it’s even chosen for you.

Developers at this end of the spectrum often write database code that is embedded within other programming constructs. They will create a connection object, and then a command, table or database object to work with. If it’s a command-type object, they set the Transact-SQL statements as a variable (most of the time) and then pass it on to the command object, which executes the command string.

They also might use a “middle tier” program, one that does not have a User Interface but takes inputs from a requesting program and then farms that back out to the database system or systems. This is common in many shops as well, and has a lot of advantages.

At the other end of the spectrum is the “DBA” developer, who knows not only how to write Transact-SQL code but also manages the system, taking care of security, backups and so on. They don’t often live within an application programming suite like Visual Studio, but are more comfortable with the tools that come with SQL Server.

And then there are the developers that live all along that spectrum — perhaps they aren’t a “pure” developer, or perhaps they do more than just manage the database platform. In any case, the coding I’m talking about in this article deals specifically with SQL Server, so the environment needs to be able to handle that environment and the Transact-SQL language with as much dexterity as it does the application-type environment and languages. The tools for that environment are more limited, so I’ll call out the ones I’m familiar with so that you can decide when and where to use each.

Even if you are a “pure” application developer, you want to be familiar with these tools. The tools I’ll describe will be better suited to working with the SQL Server database platform than an application programming environment that is designed for a particular language, simply because these tools are designed specifically for the database server.

I need to start out by defining a few terms and processes. As I have mentioned before, I’m a fan of the Microsoft Solutions Framework (MSF), so if you don’t have a defined process in your shop or yours isn’t working like you want, make sure you check it out. I have links at the bottom of this article on that. The MSF processes suggest the “Agile” approach to software development, so the terms you’ll hear me refer to are there.

Another term I’ll use is “Integrated Development Environment” or IDE, to refer to the entire graphical suite of tools for a given solution that assist you in code development, alteration and deployment out to your development, testing and production servers. For that reason, I won’t mention simple tools like VI, notepad and so on. While those let you type code, they don’t have syntax checking and highlighting, project integration and so on.

I’m also not including the Entity Relationship Modeling tools for that reason. While most all of the options should have this capability, this capability alone isn’t enough to write complicated stored procedures and work with User Defined Types (UDTs) that you’ll need to create with an “true” IDE. But, wherever another tool is deficient in this area (such as SQL Server Management Studio), I do recommend you add this to your toolbox if you design a lot of new databases or make complicated changes to older ones.

I will not include any mention of tools from one vendor that claim to work against another platform. I’ve found that these are almost useless, and the vendors only put limited functionality in the tools so that they can make the claim. I find that dishonest and a waste of time.

Third-party Tools

I’ll start with a brief description of a few third-party tools that you can use to code a database project, but to be honest I’ve only used a few of them, and I don’t use them any longer. That isn’t because they aren’t useful, but because of the primary reason to pay extra for something you already have anyway — let me explain that.

The primary reason to use a third-party tool to develop SQL Server code is when you have a multi-platform environment. In other words, if you’re using SQL Server, Oracle and DB2 in your organization, you might want to consider a tool which can “talk” to all of them. Even then, these tools don’t cover the platforms with the same level of detail and integration as the vendor’s tools.

The first tool I’m familiar with that will allow you to code a SQL Server project is called TOAD, from Quest Software. I’ve described it in a full article some time ago, so I’ll point you to that for the particulars. The upshot is that there is a free version and a pay-for version. To get any real project features (such as a debugger) you need the pay-for version. TOAD does have a module for Oracle and SQL Server, as well as DB2 and MySQL.

If you’re using the Revelation OpenInsight Embedded Engine (something found in many government projects), then you might want to check out the OpenInsight product. This environment presents the coding process in a series of steps, and also works with various database back-ends, including SQL Server. Once again, I haven’t found it to be as rich for developing SQL Server applications as the offerings from Microsoft.

WinSQL, from IndusSoft, does have some pretty impressive capabilities for developing against multiple systems, and it isn’t on the far end of expensive. You can check here for a full feature list of what each edition offers, but I don’t recommend anything less that the Professional edition for the serious developer. It also has probably the highest level of support for the most databases I’ve seen.

PowerSQL by Embarcadero has a great Project system, and is also multi-platform. You can check out that here. I’ve found it a bit more useful against Oracle than SQL Server, but it works well for both. It is also part of a suite of products that work together well not only for developing database code, but modeling and managing the systems as well. DBArtisan, another one of their products, has always been one of my favorite multi-platform monitoring and managing tools.

A surprising (or maybe not) no-show here is Eclipse, the open-source IDE that most IBM shops use. I find it to be a great platform, although it is pretty “do-it-yourself,” but as of right now I don’t see many folks using it with SQL Server. There are only a couple of plug-ins for it that I’ve seen, and most of those are for Java-to-ODBC connectivity and nothing more. But I keep checking back, because I think the potential is there for the best shot at integration.

So if you’re developing a great many database projects on multiple platforms, make sure you watch the demos for each of these products on their websites. Be prepared to do a bit of manual wiring up between your source-control system and these suites, but don’t let that deter you. Also plan to spend a bit of time learning each platform. Just because they are not part of a particular vendor’s offerings doesn’t mean they will be easier to learn and use.

Visual Studio Team Edition for the Database Professional

During the beta of this product I reviewed it, and since then it has grown up quite a bit. I’ll shorten the ridiculously long name to VSDB so that I don’t have to keep typing it here — and you might also have heard of it by its project code name of “Data Dude.”

As part of the Visual Studio Team Suite, this tool integrates very well in a purely Microsoft only shop. As the name implies, it will interoperate with Team Services on multiple levels, and has one of the best code-testing and modeling sections of all the tools I’m mentioning in this article. It also has the ability to generate load against a SQL Server database, and maintain versions of schema objects, along with comparisons of objects in another environment (such as production or testing) with the schema of the objects you have in your development environment.

The part I like best about this tool is that you can develop a complete database project without being connected to any database system at all. It also has a great refactoring capability and can track an object name change through the entire project.

The thing I like least about VSDB is the licensing and price — it’s a really steep climb for small companies. Of course, most small companies aren’t doing the kind of work this product is designed for, so perhaps it’s a wash anyway. You can buy VSDB as a stand-alone edition, but it really needs the whole organization to work within Visual Studio Team Edition to show its true value. However, if you do buy the “Enterprise Architect” edition you get this as part of the suite, so large development shops that focus on Microsoft technologies probably benefit the most from this edition.

SQL Server Management Studio

For some DBAs, the fact that I include SQL Server Management Studio (SSMS) as a development tool seems obvious. Application developers, however, often dismiss SSMs as a management tool. In fact, SSMS is built on Visual Studio, although it has been significantly changed to have more management features than you normally see in a development project.

But SSMS in SQL Server 2008 deserves a second look. A great many new features have been added that allow it to function more as a true development IDE, such as a new debugger, Intellisense (code completion), collapsible code blocks (for SELECT and BEGIN/END statements) to name just a few. Another very useful feature in SSMS for SQL Server 2008 is that you can run multiple queries against a group of servers by right-clicking a group name and selecting “New Query” from the menu that appears.

SSMS also has a fully integrated graphical Query Plan viewer, and you can import and export the plans. There’s a full query designer, you can trace the query in SQL Profiler and the Database Tuning Advisor, and it has the ability to create database Projects, although they are quite limited. You can check code in and out of the Visual Source Safe (VSS) code-control system, but that’s about it — most of the time I just take the project files and place those over whatever source code control system I’m using.

I have covered SSMS here and here, but in a more general way. In future tutorials I’ll zoom in on some of these development features, but even if your shop is still on SQL Server 2000 or 2005, you should definitely buy and install the 2008 developer edition (it’s only 50$ US) for your development efforts. Then you can slowly add the new version to the mix as you test features, but you gain the immediate advantage of being able to be more productive as you develop.

InformIT Articles and Sample Chapters

Not everyone is enthralled with Agile development — check out Agile Development: Weed or Wildflower? to find out more.

Books and eBooks

If you’re a Java developer, you probably already know about Eclipse, but just in case, The Java Developer's Guide to Eclipse, 2nd Edition is a great book on the subject.

Online Resources

The official Microsoft Solutions Framework whitepapers are here.

More information on the Agile software development process using MSF is here.