Transact SQL

With the EXECUTE statement, all parameter values must be converted to character or Unicode and made a part of the Transact-SQL string.

Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement.

A DDL Trigger can fire after execution of any Transact-SQL event that belongs to a predefined grouping of similar events.

For a list of columns that this view inherits, see sys. Objects (Transact-SQL).

However, the trigger does not fire when any Transact-SQL statements on which it was programmed are executed.

GO signals both the end of a batch and the execution of any cached Transact-SQL statements.

To see a list of currently created event notifications, use sys. Event_notifications (Transact-SQL).

For a list of conditions that must be met before a function can be schema bound, see CREATE FUNCTION (Transact-SQL).

This doubles the number of Transact-SQL statements that must be coded to implement a given piece of logic.

Use the generic query designer when you want to control the exact Transact-SQL used for a query.

This script should contain a set of Transact-SQL statements that execute against the database or databases that you want to tune.

The Transact-SQL statements that accept a cursor name parameter also support the GLOBAL keyword to identify the scope of the name.

Used to process multiple results , which can be generated by executing batch Transact - SQL statements .

Is the name of a Transact-SQL language event that, after execution, causes a DDL trigger to fire.

This can be useful if you do not have the Transact-SQL script file used to create the stored procedure.

Transact-SQL stored procedures cannot be modified to be CLR stored procedures and vice versa.

If your application supports only a single locale, provide a static locale in the Transact-SQL rule used to generate notifications.

This enables Transact-SQL batches, stored procedures, and triggers to work with the output one row at a time.

Is the text of the Transact-SQL query used to select the records to be submitted as events.

For a complete description of these options, see ALTER DATABASE (Transact-SQL).

MSmerge_conflicts_info (Transact-SQL) table is a system table and is not deleted from the database, even if it is empty.

This can be useful if you do not have the Transact-SQL script files used to create the function.

Specifies the start of a Transact-SQL distributed transaction managed by Microsoft Distributed Transaction Coordinator (MS DTC).

The Connect to Database dialog box closes, and your Transact-SQL Editor is available with a connection to your Northwind database.

The DDL trigger fires after execution of any Transact-SQL language event that belongs to event_group.

When OFF is specified, identifiers cannot be in quotation marks and must follow all Transact-SQL rules for identifiers.

If the queue specified in the RECEIVE statement has the queue status set to OFF, the statement fails with a Transact-SQL error.

For Transact-SQL user-defined functions, the system verifies the definition and prevents the definition of nondeterministic functions.

UPDATE(column) can be used anywhere inside the body of a Transact-SQL trigger.

Any dynamic Transact-SQL that is assigned to a variable will be truncated if it is larger than the buffer allocated for that variable.

Is the name used to refer to the physical file in all Transact-SQL statements.

Allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt.

For more information about this statement, see BULK INSERT (Transact-SQL).

Also, you can identify the source database of a view from the source_database_id column of the sys. Databases (Transact-SQL) catalog view.

If your application supports only one device, provide a static device name in the Transact-SQL rule used to generate notifications.

These stored procedures can be written in either Transact-SQL or in one of the. NET languages.

Is the name of a predefined grouping of Transact-SQL language events.

To use sqlcmd interactively, or to build script files to be run using sqlcmd, users must understand Transact-SQL.

Transact-SQL keywords that control the flow of execution of SQL statements and statement blocks in triggers, stored procedures, and batches.

For an in-depth description of these RESTORE options, see RESTORE (Transact-SQL).

The bcp command allows you to export the result set from a Transact-SQL statement to a data file.

Indicates that the subsequent character string is to be used with pattern matching. For more information, see LIKE (Transact-SQL).

When the value of type is custom_script, the name and full path of a Transact-SQL script file is expected.

Is the name of a predefined group of Transact-SQL or SQL Trace event types.

A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune.

CREATE DEFAULT statements cannot be combined with other Transact-SQL statements in a single batch.

Identifiers cannot be in quotation marks and must follow all Transact-SQL rules for identifiers.

If the XML ON command has not been issued before a Transact-SQL statement that outputs XML streams is executed, the output will be garbled.

Some job steps, such as long-running Transact-SQL statements (BACKUP) or some DBCC commands, may not respond quickly to stop requests.

This can be specified using either Transact-SQL statements or database application programming interface (API) functions.

Utility allows you to enter Transact-SQL statements, system procedures, and script files.

Most of the Transact-SQL statements in the workload do not reference tunable objects.

When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

When ON, a Transact-SQL distributed transaction is started when a remote stored procedure is executed from a local transaction.

To obtain the Transact-SQL text that caused recompilation is no longer required.

Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers.

SQL Server allows rowsets from OLE DB providers to be referenced in Transact-SQL statements as if they were SQL Server tables.

In Transact-SQL, a batch is a set of SQL statements submitted together and executed as a group, one after the other.

You can write Transact-SQL stored procedures and functions that start the processing on the XML column for your business needs.

For a list of the date format styles that are deterministic, see CAST and CONVERT (Transact-SQL).

In a Transact-SQL stored procedure any SELECT statement that returns zero or more rows sends the results to the connected caller's "pipe. "

For information about this table, see logmarkhistory (Transact-SQL).

A workload is a set of Transact-SQL statements that execute against databases that you want to tune.

A Transact-SQL statement that imports data directly from a data file into a database table or nonpartitioned view.

For more information about changing the size of a database, see ALTER DATABASE (Transact-SQL).

This topic introduces the backup and restore history tables and also the Transact-SQL statements that are used to access backup history.

Cannot be specified as a parameter data type in either Transact-SQL or CLR functions.

Examines the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement.

Supports the setting of parameter values separately from the Transact-SQL string as shown in the following example.

When OFF, calling remote stored procedures from a local transaction does not start a Transact-SQL distributed transaction.

Displays the number of milliseconds required to parse, compile, and execute each Transact-SQL statement after statements execute.

If none of the Transact-SQL statements in the procedure had an error, the variable remains at 0.

Tables and views in external data sources can be referenced directly in SELECT, INSERT, UPDATE, and DELETE Transact-SQL statements.

Cannot be specified as a return data type in either Transact-SQL or CLR functions.

Although Transact-SQL debugging does not freeze other executions on the server, it could cause other connections to wait by holding a lock.

The following nondeterministic built-in functions cannot be used in Transact-SQL user-defined functions.

Graphical display of Showplan information showing the logical steps built into the execution plan of a Transact-SQL statement.

Use PRINT to help in troubleshooting Transact-SQL code, check the values of data, or produce reports.

Rules and restrictions for distributed queries and linked servers, and for allowable Transact-SQL statements.

If you use Transact-SQL to configure database mirroring, you need to understand how to set transaction safety.

Allow you to store data for use as input in a later Transact-SQL statement.

When that transaction is completed, the next Transact-SQL statement starts a new transaction.

Dynamic management views and functions cannot be referenced in Transact-SQL statements by using one-part names.

The name of the database in which to execute a Transact-SQL step.

The DROP INDEX Transact-SQL statement can be used to drop existing primary or secondary XML and non-XML indexes.

This editor can be used to edit the current Transact-SQL batch, or the last executed batch.

Without control-of-flow language, separate Transact-SQL statements are performed sequentially, as they occur.

When editing SQL statements, certain Transact-SQL statements are enclosed in a box surrounded by a thin line.

These stored procedures can only be executed from another stored procedure, a trigger, or a Transact-SQL batch or script.

A command cannot be followed by a Transact-SQL statement or another command.

When a single Transact-SQL statement acquires at least 5, 000 locks on a single table or index.

For more information about how to put a database into emergency mode, see ALTER DATABASE (Transact-SQL).

The class can have overloaded methods, but these methods can be called only from within managed code, not from Transact-SQL.

If the statement is a stored procedure or Transact-SQL statement, it becomes the root of the graphical execution plan tree structure.

For more information about setting the access mode of a database, see ALTER DATABASE (Transact-SQL).

For more information about the modifications that can be made to a table, see ALTER TABLE (Transact-SQL).

Describes how these Transact-SQL programs centralize business rules, tasks, and processes within the server.

Return the rowsets that can be used in the place of a table reference in a Transact-SQL statement.

In Transact-SQL procedures, the column names or alias names of the first query are returned to the calling environment.

Describes reasons why Transact-SQL statements may fail when a notification subscription is registered.

To do this, set the command type for the dataset to Text and enter the Transact-SQL text by typing directly into the SQL pane.

The following example shows a simple Transact-SQL user-defined function and identifies the major components of the function.

By disabling the CHECK constraints, you can import the data and then use Transact-SQL statements to clean up the data.

For information about this new option and an example, see sp_setapprole (Transact-SQL).

Comments can be inserted on a separate line or within a Transact-SQL statement.

Most code examples in the Transact-SQL Reference have been tested on servers that are running a case-sensitive sort order.

In this case, these operators are equivalent to the Transact-SQL IS NULL and IS NOT NULL operators.

The Transact-SQL programming language provides several SET statements that change the current session handling of specific information.

The name of the user account to use when executing a Transact-SQL step.

Option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches.

The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.

Transact-SQL statements in the TRY block following the statement that generates an error will not be executed.

Persisted computed columns that either directly or indirectly reference methods in the assembly, through Transact-SQL functions or methods.

Finishes asynchronous execution of a Transact-SQL statement.

Open Data Services exposes the extended stored procedure API used to write DLL functions that can be called from Transact-SQL statements.

After this option is set ON, all subsequent Transact-SQL statements return the statistical information until the option is set to OFF.

Standard transactional publications are created by default when using Transact-SQL or Replication Management Objects (RMO).

Gets the number of rows changed, inserted, or deleted by execution of the Transact-SQL statement.

The created OLE object is automatically destroyed at the end of the Transact-SQL statement batch.

Is any valid Transact-SQL statement or statement grouping as defined with a statement block.

Additional information can be found by saving traces to tables and using Transact-SQL to query the event data.

DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server.

For pull subscriptions, the entry can be cleaned up by sp_droppullsubscription (Transact-SQL) or sp_subscription_cleanup (Transact-SQL).

For a Transact-SQL step, the most recent Transact-SQL error number encountered while running the command.

This most frequently occurs when an application issues a CANCEL or a Transact SQL KILL command without a corresponding ROLLBACK command.

For more information about these options, see Setting Server Configuration Options and sys. Configurations (Transact-SQL).

When a Transact-SQL dialect procedure encounters an error, execution continues at the following statement.

Event notifications can be designed to occur after a particular Transact-SQL statement or stored procedure is run.

The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked-server name.

This rule is a Transact-SQL query that matches event and subscription data and inserts the results into the notification class view.

Property ordinarily specifies a Transact-SQL statement with a valid FOR XML clause.

An action contains a Transact-SQL query used to generate notifications based on matches between subscription fields and event fields.

In each selected stored procedure, verify that all variables that are used in dynamic Transact-SQL are handled correctly.

The timeout period elapsed prior to the completion of a Transact-SQL statement.

Under a MARS session, Transact-SQL explicit and implicit transactions become batch-scoped transactions.

For a description of the BULK INSERT syntax, see BULK INSERT (Transact-SQL).

Explicit database references occur when a Transact-SQL statement names the database that holds the data.

This method is recommended for Transact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

Is not specified, the Transact-SQL script file is generated in the directory where the utility runs.

Pieces of text that are inserted into Transact-SQL statements or scripts to explain the purpose of the statement.

ALTER FUNCTION cannot be used to change a Transact-SQL function to a CLR function or vice-versa.

After this option is set ON, information about all subsequent Transact-SQL statements are returned until the option is set OFF.

A self-signed certificate created with the Transact-SQL statement CREATE CERTIFICATE meets the requirements in the preceding list.

For more information about granting endpoint permissions, see GRANT Endpoint Permissions (Transact-SQL).

For more information, see REVOKE System Object Permissions (Transact-SQL) and DENY System Object Permissions (Transact-SQL).

To see all messages processed by Database Mail, use sysmail_allitems (Transact-SQL).

Transact-SQL statements that are entered in an interactive session can edited by entering the: ED command and the sqlcmd prompt.

DECLARE CURSOR accepts both a syntax based on the SQL-92 standard and a syntax using a set of Transact-SQL extensions.

A stored procedure is a group of Transact-SQL statements that have been predefined and precompiled on the server.

Objects in these linked servers can be referenced in Transact-SQL statements using four-part names.

A workload is a set of Transact-SQL statements that executes against databases you want to tune.

The Transact-SQL ALTER TABLE. SWITCH statement lets you transfer blocks of data quickly and efficiently between partitioned tables.

Gets the line number within the Transact-SQL command batch or stored procedure that generated the error.

Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names.

Writes the recommendation as a Transact-SQL script to the file name and destination specified.

Returns the Transact-SQL script for creating the conflict table on the Subscriber for the queued subscription article.

These objects may be called by a Transact-SQL statement or another CLR object running in the server.

The identifier must not be a Transact-SQL reserved word .

Almost any Transact-SQL code that can be written as a batch can be used to create a stored procedure.

Gets the line number within the Transact-SQL command batch or stored procedure that contains the error.

For push subscriptions, the entry can be cleaned up by sp_subscription_cleanup (Transact-SQL).

In a typical database unit test, a Transact-SQL (T-SQL) test script runs and returns an instance of the ExecutionResult class.

These include user-defined type variables or functions and user-defined functions, but cannot reference a Transact-SQL statement.

Transact-SQL statements, including stored procedures and extended stored procedures.

For more information about the parameters that are used in the ALTER TRIGGER statement, see CREATE TRIGGER (Transact-SQL).

Transact-SQL DDL statement can be used to modify existing XML and non-XML indexes.

GO is not a Transact-SQL statement; it simply signals to the utilities how many SQL statements should be included in a batch.

The administrative report is produced by the NSAdministrationHistory (Transact-SQL) stored procedure.

Describes the primary syntax elements used in Transact-SQL statements.

A collection of Transact-SQL statements used to perform an operation.

Transact-SQL defines server system stored procedures and system functions that return catalog information.

To use SET STATISTICS IO, users must have the appropriate permissions to execute the Transact-SQL statement.

A Transact-SQL script or application connection executes a Transact-SQL statement that starts a distributed transaction.

Like DML triggers, more than one DDL trigger can be created on the same Transact-SQL statement.

For more information about the parameters used in the ALTER VIEW statement, see CREATE VIEW (Transact-SQL).

For more information about Transact-SQL stored procedures, see Understanding Stored Procedures.

Any number of UNION operators can appear in a Transact- SQL statement .

The application snapshot report is produced by the NSSnapshotApplications (Transact-SQL) stored procedure.

One effective tool for handling errors, including those in transactions, is the Transact-SQL TRY. . . CATCH construct.

Demonstrates using CLR store procedures and calling Transact-SQL stored procedures from CLR stored procedures.

A precompiled collection of Transact-SQL statements that are stored under a name and processed as a unit.

Imposes conditions on the execution of a Transact-SQL statement.

For more information about these SET options, see SET STATISTICS PROFILE (Transact-SQL) and SET STATISTICS XML (Transact-SQL).

The following code example causes a foreign key violation error in a transaction that has other Transact-SQL statements.

These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP.

For more information about operating modes, see Transact-SQL Settings and Database Mirroring Operating Modes.

You can use the DBCC OPENTRAN Transact-SQL reference to verify if there is an active transaction in a database at a particular time.

For a list of and more information, see Windows Collation Name (Transact-SQL) and SQL Collation Name (Transact-SQL).

Parameters and return codes can exchange data values with either Transact-SQL variables or application variables.

For more information about viewing a list of existing triggers, see sys. Triggers (Transact-SQL) and sys. Server_triggers.

Transact-SQL statements operating directly on these tables will take longer and require more system resources to process.

The isolation level under which a Transact-SQL statement executes determines its locking and row versioning behavior.

A command consists of one Transact-SQL data manipulation language (DML) statement or one data definition language (DDL) statement.

This option is equivalent to specifying WITH STANDBY in a Transact-SQL RESTORE statement.

Utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in.

For more information about opening and releasing tape devices, see BACKUP (Transact-SQL) and RESTORE (Transact-SQL).

For more information, see Notification Services Stored Procedures (Transact-SQL).

Notification Services generates notifications by using Transact-SQL queries that join event and subscription data.

It displays the estimated query execution plan tree of the Transact-SQL statement being executed.

This option is equivalent to the RECOVERY option in a Transact-SQL RESTORE statement.

Is any number and type of Transact-SQL statements to be included in the procedure.

SHOWPLAN permission on all databases containing objects that are referenced by the Transact-SQL statements.

A condition action also has a Transact-SQL query, but this query defines a view from which subscribers can define their subscriptions.

ID of the context database that was in effect when the Transact-SQL statement corresponding to this plan was compiled.

The Transact-SQL statements in an action perform some work for the rule.

For more information about dropping subscriptions, see Subscribing to Publications and sp_dropsubscription (Transact-SQL).

Several Transact-SQL scripts are executed to create the tables, after which several Execute SQL tasks run to populate the tables.

Transact-SQL batches, stored procedures, and triggers use the WHERE CURRENT OF clause to perform positioned updates.

Open or type a Transact-SQL script that contains the queries you want to analyze into the Management Studio query editor.

One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements.

Provides the Transact-SQL syntax for granting permissions on system stored procedures, functions, catalog views, and other system objects.

These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition.

Explicitly start a transaction through an API function or by issuing the Transact-SQL BEGIN TRANSACTION statement.

For Transact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type.

Specifying an input mask for a column so that applications can validate data before running a Transact-SQL statement.

For rows that are not of type PLAN_ROW, this column contains the text of the Transact-SQL statement.

A group of Transact-SQL statements can be enclosed in a TRY block.

Each operation corresponds to an individual Transact-SQL RESTORE statement.

DML triggers and DDL triggers are created, modified, and dropped by using similar Transact-SQL syntax, and share other similar behavior.

For more information about displaying statistics, see DBCC SHOW_STATISTICS (Transact-SQL).

In this walkthrough, you create a database unit test that tests a simple Transact-SQL (T-SQL) script.

Users interactively enter Transact-SQL statements in a manner similar to working at the command prompt.

An action is a group of Transact-SQL statements that Notification Services runs each time it fires a subscription rule.

Each escalation event operates primarily at the level of a single Transact-SQL statement.

The following table lists the Transact-SQL stored procedures that are included in the AdventureWorks sample OLTP database.

The Transact-SQL keywords are traditionally typed using uppercase letters, but the keywords are not case sensitive.

The Transact-SQL SET statement options for displaying execution plan information produce output in XML and text.

Transact-SQL batches, stored procedures, and triggers use the FETCH statement to fetch from Transact-SQL cursors.

Use sp_enumeratependingschemachanges (Transact-SQL) to list the pending schema changes for the publication.

To specify connection information, use the stored procedure sp_link_publication (Transact-SQL).

The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION.

For more information about deferred name resolution, see CREATE PROCEDURE (Transact-SQL).

If a RECEIVE statement or a GET CONVERSATION GROUP statement specifies an unavailable queue, that statement fails with a Transact-SQL error.

Captures information about stored procedures and Transact-SQL batch execution.

Each job step runs in the context of a proxy, except for Transact-SQL job steps.

Information about database users is visible in the sys. Database_principals (Transact-SQL) catalog view.

Transact-SQL statements can also be used in the definitions of indexes and chronicle tables in the ADF.

Controls the behavior of the Transact-SQL COMMIT TRANSACTION statement.

Describes the fundamental processes of building Transact-SQL statements.

Setting ANSI_PERMISSIONS to OFF provides Transact-SQL-compatible permissions on UPDATE and DELETE.

Is a Unicode string that contains a Transact-SQL statement or batch for which parameter information is that you want.

Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.

For more information about the ANSI_NULLS database option, see ALTER DATABASE (Transact-SQL) and Setting Database Options.

Cursor names can be referenced only by other Transact-SQL statements.

Changing the path or file name in the Restore As column is equivalent to using the MOVE option in a Transact-SQL RESTORE statement.

A table-valued user-defined function can be used where table or view expressions are allowed in Transact-SQL queries.

For more information about setting compatibility levels and interpreting empty strings, see sp_dbcmptlevel (Transact-SQL).

To use Transact-SQL to create an endpoint to use with Windows Authentication, use a CREATE ENDPOINT statement.

SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements.

For example, use a BEGIN. END pair of statements when including more than one Transact-SQL statement in a logical block.