Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
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.

Debugging SQL Database Objects

This topic applies to:


Visual Basic



Web Developer


Topic does not applyTopic does not applyTopic does not applyTopic does not apply


Topic does not applyTopic does not applyTopic does not applyTopic does not apply

Pro and Team

Topic appliesTopic appliesTopic appliesTopic applies

Table legend:

Topic applies


Topic does not apply

Does not apply

Topic applies but command hidden by default

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.

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.

Overview of Visual Database Tools

Describes limitations of using SQL Debugging features.

Common Procedure Reference

Describes procedures that occur in many different scenarios and samples

Transact-SQL Database Debugging

Shows how to debug various kinds of T-SQL database objects: stored procedures, triggers, user-defined functions, and extended stored procedures.

SQL CLR Database Debugging

Shows how to debug various kinds of CLR database objects: stored procedures, triggers, user-defined scalar functions, user-defined table-valued functions, user-defined aggregates, and user-defined types.

Overview of Visual Database Tools

Describes restrictions and limitations of using SQL Debugging features.

Setting Up SQL Debugging

Lists software requirements for SQL debugging (for the server and workstation), instructions for enabling SQL debugging, installing, configuring, and troubleshooting setup.

Multi-tier Application Database Debugging

Describes the necessary setup procedures, and provides a sample that shows how to debug a multi-tiered database application.

Monitoring the Flow of SQL Debugging Information

Suggests using the Network Monitor (NetMon) or a similar tool to monitor the flow of SQL debugging information and provides a pointer to the Network Monitor documentation.

Community Additions

© 2015 Microsoft