|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer|
Debugging SQL Database Objects
This topic applies to:
Pro and Team
Does not apply
Command or commands hidden by default.
Database objects can now be written in T-SQL or in a common language runtime (CLR) language. This section describes how to debug SQL database objects. There are topics for each database object type: one for the SQL/CLR version and, if applicable, one for the T-SQL version. Some types of SQL/CLR database objects cannot be written in T-SQL.
The development life cycle for database objects includes coding, deployment to a database, unit testing, application testing, and debugging. Before SQL Server 2005, all database objects were written in T-SQL. It was common to go through the entire life cycle using the Query Analyzer tool, which included a T-SQL debugger, except for application testing, which was done by using Visual Studio. In SQL Server 2005, Query Analyzer has been replaced by the SQL Server Management Studio. Because of the new capability of creating SQL/CLR database objects, debugging is performed inside Visual Studio, instead of in SQL Server Management Studio, which does not include a debugger.
This section focuses on development using the SQL Server project type of Visual Studio. Developing SQL/CLR objects is easy using a SQL Server project type: coding, deployment, unit-testing and debugging are integrated into a single IDE, all automated into a simple process. T-SQL objects can also be developed in this project type. For more information, see Creating SQL Server 2005 Objects in Managed Code.
Other ways to code database objects include the following:
T-SQL objects can be coded and deployed in Visual Studio using a Database project type. This project type provides design-time tools for creating database objects. You can also do SQL Server administrative tasks such as creating tables, views, and indexes with this project type. For more information, see Overview of Visual Database Tools.
Complex new or legacy T-SQL objects can be written, modified, deployed and unit-tested in SQL Server Management Studio. If debugging is required, you use Visual Studio. If most of your database development work is performed in T-SQL, and you already have experience with Query Analyzer, this might be a good option for you.
Visual Studio is not actually required for developing SQL/CLR database objects; you could also use a text editor or third-party IDE for development, and deploy the objects using the SQL Server Management Studio. This option requires more knowledge of SQL Server than the option of using Visual Studio and will likely be less productive.
Deployment can be done in two ways:
As soon as you have created a SQL/CLR object, you can deploy it using T-SQL commands in the SQL Server Management Studio.`
T-SQL objects can also be deployed through SQL Server Management Studio. In this scenario development and deployment are a single step, because you write a script that will create the object, and then you run the script.
Unit testing and debugging can be done several ways.
Existing database objects can be debugged using Visual Studio outside of any project. Using Server Explorer you can step into a database object. For more information, see How to: Step into an Object Using Server Explorer.
You can unit test objects by running a T-SQL script in the SQL Server Management Studio which will execute the object, and you can debug the object from Visual Studio. For more information, see How to: Debug Transact-SQL Using the SQL Server Management Studio.
You can run a test script from a Visual Studio 2005 Database project.
To monitor the flow of SQL debugging information over the network, you can use the Microsoft Network Monitor, which is available at the Microsoft Download Center (http://www.microsoft.com/downloads/details.aspx?FamilyID=18b1d59d-f4d8-4213-8d17-2f6dde7d7aac&DisplayLang=en).
As soon as the database object has been unit tested, applications that call the object must be tested. This process is described in Multi-tier Application Database Debugging.