Share via

Editing Database Scripts and Objects with the Transact-SQL Editor

  • Article

You can author, validate, and execute Transact-SQL scripts and queries in the Transact-SQL editor. You can also use the editor to modify definitions of database objects such as tables, views, indexes, stored procedures, and so on. The Transact-SQL editor is the primary way to author scripts that run before and after you deploy a database. The Transact-SQL editor provides the same basic functionality as the code editors for Visual C# or Visual Basic.

Features of the Transact-SQL editor include the following:

  • All common features for Visual Studio editors, which include find and replace, bookmarks, block indent and un-indent, integration with the Visual Studio Error List window, and block commenting and un-commenting.

  • Support for shortcut keys compatible with other editors and SQL Server Management Studio.

  • Transact-SQL syntax coloration for different versions of Transact-SQL, to improve readability and to make it easier to create complex statements.

  • Validation of Transact-SQL syntax without executing the script or query.

  • The ability to edit while disconnected.

  • Multiple sets of query results displayed as a grid, displayed as text, or saved to a file on disk.

  • Collection and display of client statistics when you run queries.

  • The ability to execute multiple queries in one editor instance, with multiple result sets generated. The queries are executed sequentially.

  • The ability to execute multiple queries at the same time in different editor instances.

  • Configurable settings for executing a query.

  • Support for SQLCMD.

Database Sessions and Connections

You can have multiple instances of the Transact-SQL editor open at the same time. You can execute scripts or queries at the same time in the different sessions. This approach is useful if you have multiple, long-running queries, such as re-indexing operations.

In an individual Transact-SQL editor instance, you can work while connected or disconnected, and you can always edit scripts and queries. However, you cannot validate or execute queries if you do not have a connection to a database. You can change databases or connect to a different server without closing your session.

The same Transact-SQL editor is used when you open a database object from Schema View and when you open the file that contains that object from Solution Explorer. When you modify a database object, you are editing the underlying .sql file. To update the database on the server, you must build and deploy your changes.

Common Tasks

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.

Task

Supporting content

Open the Transact-SQL editor: You usually open the Transact-SQL editor either to connect to a database and run Transact-SQL scripts or to modify objects or scripts in your database or server project.

Connect to or disconnect from a database: If you want to use the Transact-SQL editor to run scripts, such as when you prototype new queries or procedures, you must connect to a database. You can disconnect from a database and edit scripts while offline. You can then later reconnect to the same database or to a different database in the same editor session.

Write and validate Transact-SQL statements: You author Transact-SQL statements in the Transact-SQL editor in much the same way that you write code. The Transact-SQL editor provides keyboard shortcuts and syntax highlighting that is specific to the Transact-SQL editor. You can validate any script or script fragment before you execute that script. If you must use SQLCMD statements in your Transact-SQL scripts, you can enable SQLCMD mode.

Execute some or all of the Transact-SQL statements: You specify options that control how your queries are executed, including the format of the results and whether client statistics are gathered. You can cancel long-running queries instead of waiting until they are completed. After each batch in your query is executed, you can review the results that are returned in the format that you have specified.

Save the Transact-SQL statements: When you have finished modifying your script or object definition, you can save your changes. You can discard changes by closing the editor without saving.

Get hands-on experience: You can become familiar with how to use the Transact-SQL editor by following the walkthrough to create and execute a simple Transact-SQL script.

Title

Description

Verifying Existing Database Code with Unit Tests

When you write a Transact-SQL script for a database unit test, you modify those scripts by using the Transact-SQL editor.

Compare and Synchronize Database Schemas

When you export the schema update script to the editor, you view that script in the Transact-SQL editor.

Compare and Synchronize Data in One or More Tables with Data in a Reference Database

When you export the data update script to the editor, you view that script in the Transact-SQL editor.