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,
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:
- DISABLE TRIGGER
- ENABLE TRIGGER
- 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
In addition to SELECT, SQL Server uses the following DML statements:
- BULK INSERT
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 system (DBMS) architecture, design and strategy
Related Q&A from Bridget Botelho
Data catalogs are curated data portals for self-service analytics users. Learn about the capabilities of data catalog tools, vendors that sell them ... Continue Reading
See how augmented analytics compares to traditional BI and self-service analytics tools and what this new generation of AI-powered data analysis ... Continue Reading
The definition of personal data in the EU's GDPR data protection rules is broad enough to include any type of data that can be used to directly or ... Continue Reading