Contents

Executing SQL statements

This section describes the following features:

• Entire script
• Selected block of text
• Current statement
• Execute to Cursor
• How to stop execution
• Representing query execution results
• SQL Query History
• Query Execution Options
• Transactions

Entire script

To execute an entire script, click on the SQL toolbar.

Alternatively, select the Execute command on the SQL menu or press F5.

Selected block of text

To execute an arbitrary text block:

1. Select the text block using the mouse or keyboard.

2. Run the Execute command while executing the entire script. Alternatively, select Execute from the shortcut menu of SQL Editor.

The instructions on how to select a block of text can be found in the Text selection section of the SQL Editor overview topic.

Current statement

To execute a current statement, click Execute Current Statement on the SQL toolbar or press F8.

Alternatively, right-click a required statement, and select Execute Current Statement command on the shortcut menu.

Execute to Cursor

To execute a script to the current position of the cursor:

1. Move the cursor to the required position.

2. Select the Execute To Cursor command from the SQL menu.

Alternatively, you can press Ctrl+Shift+F10.

If the statement is complex and contains multiple queries or T-SQL commands, the Execute To Cursor option will execute all statements above the cursor, including the statement where the cursor is placed. All statements that are below the cursor will not be executed.

Note

To better understand the Execute To Cursor option, study the following examples:

Example 1.

In this worked example, the SELECT Address and SELECT Culture queries will be executed. Please note, that the WHERE clause of the SELECT Culture query will also be executed. The SELECT Customer query won’t be executed.

Example 2.

In this case, the SELECT Address, SELECT Culture, and SELECT Customer queries will be executed.

Example 3.

In this case, the SELECT Address and SELECT Culture queries will be executed whereas the SELECT Customer query won’t be executed.

How to stop execution

To stop SQL execution, click Stop Execution on the SQL toolbar or press Alt+Pause.

Representing query execution results

The query execution results can be represented in two ways: in text and in grid formats.

1. To represent results as text, click on the SQL toolbar before query execution.

2. To represent results as grid, click on the SQL toolbar before query execution.

SQL Query History

dbForge Query Builder delivers a number of features that help optimize query performance. One of them is SQL Query History.

It’s not necessary to manually save every query that you run or those that are executed on a regular basis, such as monthly tasks, in a separate document. The SQL Query History feature captures and stores essential details about your executed SQL commands over a given time span, allowing for easy access. This functionality enables users to review, modify, and look up past queries executed on the database. Additionally, it provides insights into who ran each query and when, alongside other pertinent details.

The SQL Query History is particularly useful in scenarios where an examination of past actions is required, such as investigating specific cases, reviewing backup logs, or retrieving queries after an SQL Server malfunction.

The Query History is organized into two main sections:

SQL Query History grid: A tabular representation of the query history.

Preview pane: An area for viewing detailed query information.

The columns in the SQL Query History grid include:

Status: Reflects the success or failure of the query execution. Query Text: The text of SQL query that was executed. Database: The name of the database where the query ran. Size (in bytes): The memory footprint of the query. Executed On: The exact time and date the query was run. Duration: The time it took for the query to execute. File: The name of the file containing the SQL statement. Server: The server on which the query was executed. User: The individual who initiated the query execution.

To open the SQL Query History, either press Ctrl+Alt+H or click View on the main toolbar, point to Other Windows, and select SQL Query History.

This is what the SQL Query History window looks like.

If you point to the Query Text column, you will be able to see the entire executed script without having to go back to the SQL document.

How to edit an executed SQL query

To edit an executed SQL query, right-click it in the SQL Query History list and select To New SQL Window. This will open the query in a new SQL document, where you make any necessary modifications to its text.

Alternatively, simply double-click the required query in the SQL Query History list.

Query execution options

To customize the default behavior and make it best suit the users’ needs, right-click the SQL document, point to Settings, and select Query Execution Options.

The Options window contains the following options and option groups:

Option Group	Option Name	Description
General	ROWCOUNT	Causes SQL Server to stop processing the query after the specified number of rows are returned.
General	TEXTSIZE	Specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, andd image data returned by a SELECT statement.
Advanced	ARITHABORT	Terminates a query when an overflow or divide-by-zero error occurs during query execution.
Advanced	CONCAT_NULL_YIELDS_NULL	Controls whether concatenation results are treated as null or empty string values.
Advanced	DEADLOCK_PRIORITY	Specifies the relative importance that the current session continues processing if it is deadlocked with another session.
Advanced	FMTONLY	Returns only metadata to the client. Can be used to test the format of the response without actually running the query.
Advanced	LOCK_TIMEOUT	Specifies the number of milliseconds a statement waits for a lock to be released. A value of -1 (default) indicates no time-out period (that is, wait forever). When a wait for a lock exceeds the time-out value, an error is returned. A value of 0 means to not wait at all and return a message as soon as a lock is encountered.
Advanced	NOCOUNT	Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
Advanced	NOEXEC	Complies with each query but does not execute it.
Advanced	PARSEONLY	Examines the syntax of each Transact-SQL statement.
Advanced	QUERY_GOVERNOR_COST_LIMIT	Overrides the currently configured query governor cost limit value for the current connection. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run indefinitely. The query governor disallows execution of any query that has an estimated cost exceeding that value.
Advanced	SHOWPLAN_TEXT	Causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.
Advanced	STATISTICS IO	Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.
Advanced	STATISTICS TIME	Displays the number of milliseconds required to parse, compile, and execute each statement.
Advanced	TRANSACTION ISOLATION LEVEL	Controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.
ANSI	ANSI_NULL_DFLT_ON	Modifies the behavior of the session to override default nullability of new columns when the ANSI null default option for the database is false.
ANSI	ANSI_NULLS	Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server.
ANSI	ANSI_PADDING	Controls the way the column stores values shorter than the defined size of the column, and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data.
ANSI	ANSI_WARNINGS	Specifies ISO standard for several error conditions.
ANSI	CURSOR_CLOSE_ON_COMMIT	Controls the behavior of the Transact-SQL COMMIT TRANSACTION statement The default value for this setting is off. This means that the server will not close cursors when you commit a transaction.
ANSI	IMPLICIT_TRANSACTION	Sets implicit transaction mode for the connection.
ANSI	QUOTED_IDENTIFIER	Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters bot generally allowed by the Transact-SQL syntax rules for identifiers.

These settings will be applied only to the current SQL document. All the other documents will comply with the general application settings Tools > Options > Query Execution > General.

Note

Unless the query execution settings are explicitly declared in the script, the default values will be set to the ones from the Options window. For example, if SET NOEXEC OFF is explicitly declared at the beginning of the script, NOEXEC will be turned off regardless of its state specified in the options.

Transactions

A transaction is a single logical unit of work that accomplishes a particular database operation. Each transaction begins with a specific task and ends when all the tasks are successful. In this case, all the changes are committed to the database. If the transaction fails, is cancelled or rolled back, the database changes are discarded.

In SQL Server, there are several types of transactions:

• AutoCommit: A default transaction in SQL Server. Each statement is considered to be a transaction and is either committed (if it is successful) or rolled back (if it fails).
• Explicit: A transaction starts with the BEGIN TRANSACTION command and ends with the COMMIT or ROLLBACK commands.
• Implicit: SQL Server launches an implicit transaction for each statement after the previous transaction is complete. The implicit transaction is running until you explicitly add the ending statement, either COMMIT or ROLLBACK.

Query Builder allows managing transactions through the statements in a SQL document and with the help of menu commands.

Manage transactions through SQL statements

You can manage transactions with the help of two SQL statements BEGIN TRANSACTION and COMMIT/ROLLBACK TRANSACTION. If neither BEGIN TRANSACTION nor COMMIT/ROLLBACK TRANSACTION are specified, statements are executed in the autocommit mode.

To use explicit transactions, first, you need to open a transaction with the BEGIN TRANSACTION statement and then declare the required DMLs. If you execute the above-mentioned statements without COMMIT/ROLLBACK TRANSACTION, the transaction will turn to be open. In this case, no matter what other statements you run, data cannot be added, modified, or deleted in the table.

To use DML statements in the open transaction, you need to run the COMMIT TRANSACTION statement. If, for some reason, you need to cancel the executed statements in the open transaction, you should run the ROLLBACK TRANSACTION statement.

As you can see, the first two INSERT statements were applied. Though the latter INSERT statement was executed but was not applied to the table.

Manage transactions with the help of menu commands

In the dbForge tool, you can execute the following operations on the transactions:

• Begin Transaction: Defines the starting point of the explicit transaction.
• Rollback: Reverts the database changes to the original state.
• Commit: Applies the changes to the database.

To use transactions, prior to executing a DDL statement from the SQL document, you need to click Begin Transaction on the Transaction toolbar or on the main SQL menu. Then, you can click Execute.

After that, the transaction remains open. You can keep on executing SQL statements or roll back all the statements executed earlier by clicking Rollback on the Transaction toolbar or on the main SQL menu. Then, the transaction is closed. Alternatively, instead of the rollback, you can click Commit to apply all the previously executed statements to the database and complete the transaction.

Note

Menu commands define behavior only when executing a SQL document. They cannot be used with a Query Builder document and in Data Editor.

Set IMPLICIT_TRANSACTIONS

IMPLICIT_TRANSACTIONS turns on an implicit transaction mode for the current connection. In Query Builder, you can enable or disable IMPLICIT_TRANSACTIONS on the main menu Tools > Options > Query Execution > General. In the Search box, type implicit. The matching command will be highlighted.

When disabled, statements start and end transactions implicitly. In this case, you do not need to commit or roll back the transaction. It is done automatically.

When enabled, each statement begins a new transaction. Thus, you need to explicitly commit or roll back the transaction to complete it.

Note

To better understand how IMPLICIT_TRANSACTIONS work, see the following examples.

SELECT @@TRANCOUNT AS TransactionCount;

SELECT TOP 2 ProductName, Price FROM Production.Product

SELECT @@TRANCOUNT AS TransactionCount;

Example 1: Implicit transaction

Disable IMPLICIT_TRANSACTIONS in the Options dialog, open a new SQL document and execute the SELECT statements:

In the output, @@TRANCOUNT returns 0. It means that there are no in-progress transactions at that moment.

Example 2: Explicit transaction

Now, enable IMPLICIT_TRANSACTIONS in the Options dialog, open a new SQL document and execute the SELECT statements:

In the output, @@TRANCOUNT returns 1, that is the number of the Begin Transaction statements which are still running. Thus, we need to explicitly commit or roll back the transaction to complete it. To do that, in a new SQL document, execute the following statements:

In the result, we see that the number of transactions reduced to 0.

Want to Find out More?

• Previous
• Next