SQL Server Editions and Features

A brief comparison of the various editions of SQL Server 2000 and SQL Server 2005 follows.

SQL Server 2000

Two editions of SQL Server 2000 exist that may be used for production databases: Standard and Enterprise. The Standard Edition is a more economical investment for most small businesses. It is full-featured, but lacks some scalability and availability features that make the Enterprise Edition more attractive for very large-scale business environments and servers, such as supporting a larger number of processors and more memory, as well as a few database objects specifically targeted toward the large enterprise. The Developer Edition IS the Enterprise Edition — that's right, it is actually the same code with some specific adaptations. The Developer Edition will run on a desktop operating system, such as Windows 2000 Professional and Windows XP, and is limited to 10 concurrent connections. With these exceptions, all SQL Server features in the Developer Edition should behave like the Enterprise Edition. Keep this in mind if you plan to implement the Standard Edition that doesn't support a few advanced features available in the Developer Edition.

SQL Server 2005

Several new features and capabilities have been added to SQL Server 2005. Some of the most notable features include native XML storage and query support, and integration with the .NET Common Language Runtime. The comparative editions of this version of SQL Server haven't really changed much. In addition to the Standard, Developer, and Enterprise editions, there is a variety of the product called the SQL Server 2005 Express Edition. This is essentially the replacement for the SQL Server 2000 Desktop Engine (MSDE) that shipped with versions of Office and Access in the past. It's a lightweight version of the SQL Server engine, intended to run on a desktop computer with a limited number of connections. As our friends at Microsoft continue to gently nudge users away from the Access JET database engine and toward SQL Server, their products will continue to become more aligned and standardized. Like the more serious editions, SQL Server Express can be managed from within Access, Visual Studio, or the SQL Server client tools.

The SQL language has been enhanced in a few places but is generally unchanged. Because Transact-SQL conforms to the industry standard ANSI SQL standard, you will find only a few minor additions to the supported syntax in SQL Server 2005.

Relational Database Engine

Big differences exist between a true RDBMS Relational Database Management System (RDBMS) and a file-based database product. Although a true RDBMS product, such as SQL Server, does store its data in files managed by the file system, the data in these files cannot be accessed directly. The concepts of relational integrity have been applied to file-based databases for several years. Programmers wrote these rules into their program code. The difference is that the RDBMS system contains this code to enforce business rules and doesn't allow a user or developer to work around them once a database has been designed with certain rules applied.

The language used to access nearly all relational database products is SQL. The dialect of SQL used in Microsoft SQL Server is called Transact-SQL. Using SQL is the front door to the data in a database and the administrative objects of the database server. Specialized programmatic interfaces also exist that developers can use to access a database with the appropriate security clearance. Unlike file-based databases, RDBMS systems are designed so there is no "back door" to a database.