alex_aldo - Fotolia

Answer

What's the difference between DDL and DML?

What's the difference between DDL and DML? Get the answer and see examples of data manipulation language and data definition language commands for SQL databases.

Bridget Botelho
By
Published: 26 Jun 2018

DDL and DML are both used in SQL databases, but for very different reasons. To explain how they differ, let's examine how they are categorized.

Structured Query Language (SQL) is the standard programming language used to query data within a relational database or to create a database. SQL commands such as CREATE or ORDER are used to carry out specific functions.

These SQL commands are categorized into four groups: transaction control language for database transactions, data control language for granting permissions in a database, data manipulation language (DML) for modifying data in databases and data definition language (DDL) for altering the database structure.

More specifically, DML is used to modify existing data in tables, insert or update data in a table, or remove rows from a table. DDL's purpose is to change the database structure and to create new database objects or entire tables, along with the table name, column names and data types.

While this article focuses on SQL Server databases, DDL and DML commands are also used in Oracle, IBM Db2 and other databases, including databases in the cloud.

DDL vs. DML statements

DDL commands enable users to create, alter and add schema objects in a database, examine data within a table, and perform other structural functions. Here is an example of a DDL command:

CREATE TABLE [dbo].[Customer]
(
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [FName] [varchar](20) NULL,
     [LName] [varcharint](20) NULL,
      [DateOfBirth] [datetime] NOT NULL,
…

SQL queries vs. DML statements vs. DDL commands
How DDL commands, DML statements and SQL queries compare

You can add as many columns to the table as is necessary. In addition to that DDL command, the following commands provided by Microsoft are used to alter SQL databases:

  • ALTER
  • Collations
  • CREATE
  • DROP
  • DISABLE TRIGGER
  • ENABLE TRIGGER
  • RENAME
  • UPDATE STATISTICS

While DDL commands are utilized to create or remove data structures, DML statements are used to retrieve, add, modify, query or remove the data within those tables -- or databases.

One example of a DML statement is SELECT:

SELECT ID, FName, LName, DateOfBirth
FROM Customer;

In addition to SELECT, SQL Server uses the following DML statements:

  • BULK INSERT
  • DELETE
  • INSERT
  • MERGE
  • READTEXT
  • UPDATE
  • UPDATETEXT
  • WRITETEXT

The following clauses are used with those DML statements in SQL Server databases, according to Microsoft documentation: FROM, Hints, OPTION, OUTPUT, Search Condition, Table Value Constructor, TOP, WHERE, and WITH common_table_expression.

Dig Deeper on Database management