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

  • Article

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

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