Sign in to save your place.
DDL, DML, and TCL—the three languages of SQL. See what each does and when to use it!
DDL changes the structure of the database: tables, columns, indexes, keys. It does not insert or update rows.
| Command | What it does |
|---|---|
| CREATE | Creates a new object (table, view, index) |
| ALTER | Changes an existing object (add/drop column, change type) |
| DROP | Deletes an object (table, view, etc.) |
DDL is like building or remodeling the filing cabinet: add a new drawer (CREATE TABLE), add a new section in a drawer (ALTER TABLE ADD column), or remove a drawer (DROP TABLE). You're not putting papers in yet—just changing the structure.
DML reads and changes the data inside tables. This is what you'll use most often.
| Command | What it does |
|---|---|
| SELECT | Reads data (queries). Does not change anything. |
| INSERT | Adds new rows |
| UPDATE | Changes existing rows |
| DELETE | Removes rows |
SELECT is read-only. INSERT, UPDATE, and DELETE change data. Always use WHERE with UPDATE and DELETE in production so you don't change or delete every row by mistake!
TCL controls transactions: a set of one or more statements that either all succeed or all are undone.
If you don't use BEGIN/COMMIT/ROLLBACK, each single statement is its own transaction (auto-commit). Wrapping several statements in a transaction lets you cancel everything if something goes wrong.
DDL = change structure (tables, columns). DML = change data (SELECT, INSERT, UPDATE, DELETE). TCL = group changes (BEGIN, COMMIT, ROLLBACK).
-- DDL: create table CREATE TABLE Students (Id INT, Name VARCHAR(50)); -- DML: add and read data INSERT INTO Students VALUES (1, 'Ali'); SELECT * FROM Students; -- TCL: transaction BEGIN TRANSACTION; UPDATE Students SET Name = 'Ahmad' WHERE Id = 1; COMMIT;
In your practice database, try: (1) CREATE TABLE with two columns. (2) INSERT one row. (3) SELECT * FROM your table. (4) Use BEGIN TRANSACTION; UPDATE one row; ROLLBACK; and confirm the update was undone.
Correct! SELECT is DML (read data).
SELECT is DML—it reads data. CREATE/ALTER are DDL; ROLLBACK is TCL.
Correct! ROLLBACK undoes the current transaction.
ROLLBACK undoes changes since BEGIN TRANSACTION; COMMIT saves them.
Microsoft's MERGE documentation tells you to match only target columns to source columns in the ON clause. Do not filter the target there, for example AND target.active = 1. That pattern can update or insert the wrong rows. Do not use MERGE with queued updating replication. Replace it with separate INSERT and UPDATE statements.
A safer upsert for one key is: update the rows that match, then insert the rows that do not, inside a transaction. Deduplicate the source before either statement. The native json type is not the thing to learn in this lesson. On SQL Server 2022, JSON is still nvarchar plus JSON functions. The native json type is generally available on Azure SQL Database and Azure SQL Managed Instance under the SQL Server 2025 or Always-up-to-date policy, and it is in preview on SQL Server 2025. Preview is not a production default.
BEGIN TRAN;
UPDATE target
SET name = source.name
FROM customers AS target
JOIN #incoming AS source ON source.email = target.email;
INSERT INTO customers (email, name)
SELECT source.email, source.name
FROM #incoming AS source
WHERE NOT EXISTS (
SELECT 1 FROM customers AS target WHERE target.email = source.email
);
COMMIT;Try this. Write the update-then-insert pair for a table of customers keyed by email. Say what you would do if the source contained the same email twice.
What does Microsoft warn about the MERGE ON clause?