DDL vs DML vs DCL and how schema, data, and permissions changes differ

Overview

DDL, DML, and DCL are categories of SQL statements that change different things. DDL changes database structures, DML changes the data stored in those structures, and DCL changes permissions that control who can access or modify database objects.

DDL stands for Data Definition Language. It includes statements such as CREATE, ALTER, DROP, and TRUNCATE TABLE. DML stands for Data Manipulation Language. It includes SELECT, INSERT, UPDATE, DELETE, and MERGE. DCL commonly refers to Data Control Language. In SQL Server, this maps to permission statements such as GRANT, DENY, and REVOKE.

This topic matters because schema changes, data changes, and permission changes have different review paths, deployment risks, rollback strategies, audit requirements, and production blast radius. Adding a column is not the same kind of change as updating a customer's email or granting SELECT on a table.

For interviews, strong candidates can classify SQL statements, explain the operational implications, and describe how they would safely deploy migrations, data fixes, and permission changes.

Core Concepts

DDL

Data Definition Language changes database structures.

Common DDL statements include:

• CREATE
• ALTER
• DROP
• TRUNCATE TABLE
• CREATE INDEX
• ALTER TABLE
• CREATE VIEW
• CREATE PROCEDURE

Example:

CREATE TABLE dbo.Products
(
    ProductId BIGINT IDENTITY(1, 1) NOT NULL,
    Sku NVARCHAR(50) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(19, 4) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId),
    CONSTRAINT UQ_Products_Sku UNIQUE (Sku),
    CONSTRAINT CK_Products_Price CHECK (Price >= 0)
);

This creates a table, columns, data types, and constraints. It changes the schema.

DML

Data Manipulation Language reads or changes rows stored in database objects.

Common DML statements include:

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• BULK INSERT

Example:

INSERT INTO dbo.Products (Sku, Name, Price)
VALUES (N'KB-001', N'Wireless Keyboard', 49.99);

This does not change the table definition. It adds data to an existing table.

Another DML example:

UPDATE dbo.Products
SET Price = 44.99
WHERE Sku = N'KB-001';

This changes stored row values.

DCL and Permission Statements

DCL commonly refers to statements that control access. In SQL Server, permission statements include GRANT, DENY, and REVOKE.

Example:

GRANT SELECT ON dbo.Products TO ReportingUser;

This lets ReportingUser read from dbo.Products.

Deny example:

DENY DELETE ON dbo.Products TO ReportingUser;

Revoke example:

REVOKE SELECT ON dbo.Products FROM ReportingUser;

These statements change security metadata, not table structure or business rows.

Schema, Data, and Permissions

Think of the categories by what they change:

This distinction helps teams route changes correctly:

• DDL usually belongs in migrations.
• DML data fixes need data review and row-count safety.
• DCL belongs in security and least-privilege review.

DDL Examples

Add a column:

ALTER TABLE dbo.Customers
ADD MarketingOptIn BIT NOT NULL
    CONSTRAINT DF_Customers_MarketingOptIn DEFAULT 0;

Add a constraint:

ALTER TABLE dbo.Products
ADD CONSTRAINT CK_Products_Price
CHECK (Price >= 0);

Create an index:

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders(CustomerId, OrderDate DESC);

DDL changes can affect application compatibility. Adding a nullable column is usually safer than renaming or dropping a column. Adding a non-null column to a large table requires migration planning.

DML Examples

Insert:

INSERT INTO dbo.Customers (Email, DisplayName)
VALUES (N'[email protected]', N'Ada Lovelace');

Update:

UPDATE dbo.Customers
SET DisplayName = N'Ada Byron'
WHERE Email = N'[email protected]';

Delete:

DELETE FROM dbo.Sessions
WHERE ExpiresAtUtc < SYSUTCDATETIME();

Select:

SELECT CustomerId, Email, DisplayName
FROM dbo.Customers
WHERE IsActive = 1;

DML changes should be scoped carefully. For destructive statements, verify the WHERE clause and expected row count before execution.

DCL Examples

Grant:

GRANT SELECT ON SCHEMA::Reporting TO ReportReaders;

Deny:

DENY DELETE ON dbo.Orders TO AppReadOnlyRole;

Revoke:

REVOKE EXECUTE ON dbo.RebuildCustomerSummary FROM SupportUser;

GRANT gives a permission. DENY explicitly blocks a permission, often overriding permissions inherited through roles. REVOKE removes a previous grant or deny, returning permission resolution to whatever other roles or permissions apply.

TRUNCATE TABLE Classification

TRUNCATE TABLE removes data, so it feels like DML, but SQL Server groups it under DDL-style statements because it deallocates data pages and changes table allocation metadata.

TRUNCATE TABLE dbo.ImportStage;

Operationally, it behaves differently from DELETE:

• It removes all rows.
• It does not support WHERE.
• It resets identity.
• It does not fire delete triggers.
• It requires stronger permissions.
• It can be rolled back inside a transaction in SQL Server.

This is a good interview example because categories are useful, but behavior matters more than memorized labels.

Transactions and Rollback

SQL Server supports transactions for many DDL and DML operations.

BEGIN TRANSACTION;

UPDATE dbo.Products
SET Price = Price * 1.10
WHERE CategoryId = 5;

ROLLBACK TRANSACTION;

DDL can also participate in transactions in SQL Server:

BEGIN TRANSACTION;

ALTER TABLE dbo.Products
ADD TemporaryColumn INT NULL;

ROLLBACK TRANSACTION;

Do not assume every database platform behaves the same. Also remember that rollback ability does not remove operational risk. A long-running schema change can still block production traffic or fill the transaction log.

Deployment Risk

DDL risk:

• Breaking application code.
• Locking large tables.
• Long-running index builds.
• Failed migrations because existing data violates new constraints.
• Difficult rollback after destructive schema changes.

DML risk:

• Updating or deleting too many rows.
• Corrupting business data.
• Creating inconsistent states.
• Long transactions and log growth.
• Race conditions with live application writes.

DCL risk:

• Over-permissioning users or applications.
• Breaking jobs by revoking needed access.
• Failing least-privilege audits.
• Creating hidden access through roles.
• Confusing DENY and REVOKE.

Each category needs a different review mindset.

Safe DDL Practices

Safer schema changes often follow expand-contract deployment:

• Add new nullable column or new table.
• Deploy code that writes both old and new shape.
• Backfill data in batches.
• Validate data.
• Add NOT NULL, UNIQUE, or foreign key constraints.
• Switch reads to the new shape.
• Remove old columns later.

Example:

ALTER TABLE dbo.Customers
ADD NormalizedEmail NVARCHAR(320) NULL;

Backfill:

UPDATE dbo.Customers
SET NormalizedEmail = UPPER(Email)
WHERE NormalizedEmail IS NULL;

Then after validation:

ALTER TABLE dbo.Customers
ALTER COLUMN NormalizedEmail NVARCHAR(320) NOT NULL;

This is safer than one large breaking change.

Safe DML Practices

For data changes:

• Preview with SELECT.
• Use explicit transactions when appropriate.
• Check @@ROWCOUNT.
• Batch large updates or deletes.
• Use backups or restore points for high-risk changes.
• Log changed row keys when possible.
• Avoid running ad hoc scripts without review.

Example:

BEGIN TRANSACTION;

UPDATE dbo.Orders
SET Status = N'Archived'
WHERE Status = N'Completed'
  AND CompletedAtUtc < '2025-01-01';

SELECT @@ROWCOUNT AS UpdatedRows;

-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

In production scripts, do not leave commit decisions ambiguous. Use a reviewed process.

Safe DCL Practices

For permission changes:

• Grant to roles, not individual users, when possible.
• Follow least privilege.
• Prefer schema-level permissions only when the schema boundary is meaningful.
• Avoid granting broad permissions such as db_owner to applications.
• Document why access is needed.
• Test jobs and application paths after permission changes.
• Periodically review grants, denies, and role memberships.

Example:

CREATE ROLE ReportReaders;

GRANT SELECT ON SCHEMA::Reporting TO ReportReaders;

ALTER ROLE ReportReaders ADD MEMBER AnalystUser;

This is easier to manage than granting permissions directly to many users.

GRANT vs DENY vs REVOKE

GRANT gives permission.

GRANT SELECT ON dbo.Orders TO ReportReaders;

DENY explicitly blocks permission.

DENY DELETE ON dbo.Orders TO ReportReaders;

REVOKE removes a previous grant or deny.

REVOKE SELECT ON dbo.Orders FROM ReportReaders;

Important distinction: REVOKE does not necessarily mean "deny access." If the user gets the permission through another role, access may still be allowed. DENY is stronger and usually blocks inherited grants, except for special cases such as object owners and sysadmin.

Auditing and Review

Different statement types need different audit questions.

DDL review questions:

• Does this break existing code?
• Is it backward compatible?
• Will it lock large tables?
• Does existing data satisfy the new constraint?
• Is rollback possible?

DML review questions:

• Which rows will change?
• How many rows will change?
• Is the predicate correct?
• Can the change be rerun safely?
• Is there a backup or audit trail?

DCL review questions:

• Who gets access?
• What exact permission is granted or denied?
• Is a role better than a direct user grant?
• Is the permission too broad?
• Does this satisfy least privilege?

Common Mistakes

Common mistakes include:

• Treating DDL, DML, and DCL as trivia instead of operational categories.
• Running UPDATE or DELETE without previewing affected rows.
• Adding a NOT NULL column to a large existing table without a backfill plan.
• Dropping columns before all application versions stop using them.
• Granting permissions directly to users instead of roles.
• Confusing REVOKE with DENY.
• Granting broad database owner permissions to applications.
• Assuming TRUNCATE TABLE behaves exactly like DELETE.
• Forgetting that schema changes and permission changes need source control and review.

Best Practices

Best practices include:

• Put DDL changes in version-controlled migrations.
• Review DML data fixes with row counts and rollback plans.
• Manage DCL through roles and least privilege.
• Test schema changes against realistic data.
• Separate application deployments from destructive schema cleanup.
• Use explicit transactions carefully.
• Document high-risk permission changes.
• Prefer additive schema changes before breaking ones.
• Monitor long-running data and schema operations.
• Audit production access regularly.