T-SQL Tutorial

T-SQL Statements - DML, DDL, DCL and TCL


DDL (Data Definition Language)

Data Definition Language (DDL) statements defines objects in a database.
Use DDL statements to create, alter, or drop objects in a database.

CREATE - statement used to start create new objects like: database, table, view, index, temporary table, trigger, function or procedure.
ALTER - statement used to modify table name or table columns(add, drop, rename), view, trigger.
DROP - uses to drop objects in the database.
RENAME - uses to change the objects name in the database.
DISABLE TRIGGER - sets status trigger inactive.
ENABLE TRIGGER - sets status trigger active.
COLLATIONS - defines a collation of a database or table column.
UPDATE STATISTICS - updates query optimization statistics on a table or indexed view.
TRUNCATE TABLE - delete all rows from a table.


DML (Data Manipulation Language)

Data Manipulation Language (DML) affect the information stored in the database.
Use DML statements to insert, update, and delete the rows in the database.

INSERT - insert records into a table.
UPDATE - change records in a table.
DELETE - delete records into a table.
MERGE - insert, update, or delete records on a table from the results of a join.
BULK INSERT - imports records from a data file into a database table or view.






DCL (Data Control Language) - Permissions statements

Data Control Language (DCL) - permissions statements control which users and logins can access data and perform operations.
The T-SQL DCL statements are: GRANT, REVOKE, DENY.

GRANT - statement used to grant privileges, roles or permissions on database objects.
REVOKE - uses to revokes permissions granted.
DENY - uses to deny permissions.


TCL (Transaction Control Language)

Transaction Control Language (TCL) commands are used to manage transactions in the database.
The T-SQL TCL statements are: COMMIT, ROLLBACK, SAVEPOINT.

BEGIN DISTRIBUTED TRANSACTION - begin distributed transaction.
BEGIN TRANSACTION - begin transaction statement.
COMMIT TRANSACTION - commit transaction is the end point of a successful transaction.
COMMIT WORK - marks the end of a transaction.
ROLLBACK TRANSACTION - rolls back an unsuccessful transaction.
ROLLBACK WORK - rolls back a user-specified transaction.
SAVE TRANSACTION - sets a savepoint within a transaction.