SQL Server 2005: Development Questions

What’s the difference between SQL Server Express and SQL Server 2005?

Here’s the short answer. For the majority of core database objects, such as tables and stored procedures, there are no differences. For example, the security implementations are the same. However, when it comes to things like management tools, business intelligence (BI), and failover support, SQL Server 2005 has several features that SQL Server Express doesn’t support. In general, given its limitations of a 4GB size and 1GB of memory, SQL Server Express is a good desktop or small Web site database. For a review of all the feature differences, including those between the different versions of SQL Server 2005, go to http://www.microsoft.com/sql/2005/productinfo/sql2005features.mspx.

Why doesn’t SQL Server Express have an Enterprise Manager-style management interface?

It does, in the early phases. In June 2005, Microsoft released Beta 1 of the SQL Server 2005 Express Manager and will release the final tool in the first half of 2006. This tool provides a management interface for all SQL Server 2000 editions (including MSDE), as well as for SQL Server 2005 Developer and Express Edition databases on both local and remote computers. Express Manager is free, but like SQL Express, it’s limited. For example, you can open only one database at a time for management operations. I recommend going to the SQL Server Express home page, http://lab.msdn.microsoft.com/express/sql, to find the link to the current version of this tool. (Note that although Express Manager will manage SQL Server 2000 databases, you need to have previously installed either SQL Server 2005 or SQL Server Express on the computer where the tool will be installed.)

How do I find the equivalent of Query Analyzer for SQL Server 2005?

With the release of Visual Studio 2005, you should start to use Visual Studio as your query analyzer. You’ll find that you can carry out most of your database queries by using a connection from the server explorer. I want to stress this because if you want to leverage many of Visual Studio 2005’s point-and-click capabilities, you need to define a database for Visual Studio 2005 to access.

However, if you’re looking for something closer to Query Analyzer, the Query Editor, which is part of the Express Manager, will give you that familiar look and feel of dynamic query execution, though it lacks Query Analyzer’s debugging support for T-SQL. When you connect to a database, the right side of your display becomes the Query Editor. Similarly, if you’re using SQL Server 2005, you have access to the SQL Server Management Studio, which ships with SQL Server 2005. Creating a New Query in this tool opens a new tabbed display area that behaves just like the Query Analyzer of old.

Do I need a SQL Server license to ship an application I build with SQL Server Express?

No, SQL Server Express is a free download, available to everyone, so you can ship an application that references it and have it install with your application. Also note that because the Express Manager is a separate download, installing SQL Server 2005 on a customer’s computer doesn’t have to include a local management interface to your packaged database.

Should I upgrade to SQL Server 2005 from my SQL Server 2000 or 7.0 database?

You should consider this only after you’ve run the SQL Server 2005 Upgrade Advisor. This utility examines your current database configuration and the features you’re using and notifies you of any potential problems associated with upgrading your current server. The tool is currently available from Microsoft; go to http://www.microsoft.com/sql/2005/default.asp and look for a link to the download, currently about halfway down the page.

What does having .NET assemblies as stored procedures mean to me as a developer?

It means that you have the option to create a procedure in SQL Server that goes beyond what you’d normally use T-SQL to accomplish. However, keep in mind that the syntax for .NET doesn’t include any true relational data statements. So at some level, you still need to have some T-SQL commands that retrieve data. I expect that, although CLR stored procedures can be useful, most people won’t design their applications around the use of .NET stored procedures.