Data Manipulation Language (DML) Statements (Transact-SQL)

Microsoft SQL Server 2005 provides new and enhanced query syntax elements for more powerful data accessing and processing.

New DML Clauses and Options

Clause or option	Description
OUTPUT Clause	Returns inserted, updated, or deleted rows to the calling application as part of the INSERT, UPDATE, or DELETE statement.
WITH common_table_expression	Specifies a temporary named result set or view defined within the scope of a SELECT, INSERT, UPDATE, or DELETE statement.
.WRITE Clause	Enables partial updates and high performance appends to varchar(max), nvarchar(max), and varbinary(max) data type columns as part of the UPDATE statement.

Enhanced DML Statements and Clauses

Statement or clause	Description
SELECT	Provides the following new syntax items: WITH common table expression TOP expression User-defined type column name for selecting a user-defined type value
INSERT	Provides the following new syntax items: WITH common table expression TOP expression OUTPUT clause Allows for the insertion of user-defined type values.
UPDATE	Provides the following new syntax items: WITH common table expression TOP expression OUTPUT clause User-defined type column name for updating a user-defined type value .WRITE clause
DELETE	Provides the following new syntax items: WITH common table expression TOP expression OUTPUT clause
FOR Clause	Provides the following new FOR XML directives: TYPE enables the retrieval of query results as an xml type. XMLSCHEMA enables requests of an XSD schema. Allows for nested FOR XML queries.
FROM	Provides the following additional table-source syntax: APPLY PIVOT/UNPIVOT TABLESAMPLE Bulk column alias
TOP	Can be used with SELECT, INSERT, UPDATE, and DELETE statements.

SQL Server 2005 also includes new DDL statements. For more information, see Data Definition Language (DDL) Statements (Transact-SQL).

New Service Broker-related Statements

A queue can be the target of a SELECT statement, but a queue cannot be used with an INSERT, UPDATE, or DELETE statement. SQL Server 2005 introduces the following DML statements for working with Service Broker objects.

Statement	Description
BEGIN DIALOG	Starts a new conversation between two services.
BEGIN CONVERSATION TIMER	Starts a timer on a conversation.
END CONVERSATION	Ends an existing conversation.
GET CONVERSATION GROUP	Locks the conversation group for the next available message on a queue.
MOVE CONVERSATION	Associates an existing conversation with a different conversation group.
RECEIVE	Retrieves one or more messages from a queue.
SEND	Sends a message to a service.

SQL Server 2005 also includes new DDL statements for working with Service Broker objects. For more information, see Data Definition Language (DDL) Statements (Transact-SQL).

See Also

Other Resources

What's New and Enhanced in Transact-SQL (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance