Share via


Debugging SQL Database Objects

This topic applies to:

Edition

Visual Basic

C#

C++

Web Developer

Express

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

Standard

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

Pro and Team

Topic applies Topic applies Topic applies Topic applies

Table legend:

Topic applies

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.

In This Section

  • 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.

See Also

Tasks

How to: Debug Transact-SQL Using the SQL Server Management Studio

Concepts

Debugger Security

Other Resources

Creating SQL Server 2005 Objects in Managed Code