SQL Statements and Commands
1. Data Definition Language (DDL)
CREATE Creates databases, tables, or other objects
CREATE TABLE Employees (ID INT);
ALTER Modifies existing database objects ALTER TABLE Employees ADD Age INT;
DROP Deletes databases, tables, or objects DROP TABLE Employees;
TRUNCATE Removes all rows from a table (no rollback)
TRUNCATE TABLE Employees;
2. Data Manipulation Language (DML)
INSERT Adds new records to a table INSERT INTO Employees (ID, Name) VALUES (1, 'John')
UPDATE Modifies existing records in a table UPDATE Employees SET Name = 'Jane' WHERE ID = 1;
DELETE Removes specific records from a tableDELETE FROM Employees WHERE ID = 1;
3. Data Control Language (DCL)
GRANT Assigns specific permissions to users or
GRANT
roles SELECT ON Employees TO User1;
REVOKE Removes specific permissions from users
REVOKE
or roles
SELECT ON Employees FROM User1;
4. Transaction Control Language (TCL)
BEGIN TRANSACTION Starts a new transaction BEGIN TRANSACTION;
COMMIT Saves changes made during the current
COMMIT;
transaction
ROLLBACK Undoes changes made during the current
ROLLBACK;
transaction
SAVE TRANSACTION Sets a savepoint within a transaction SAVE TRANSACTION SavePointName;
5. Data Query Language (DQL)
SELECT Retrieves data from one or more tablesSELECT * FROM Employees;
Difference Between DDL and DML
Defines or modifies the structure of the database Manages data within the database tables
Commands: CREATE, ALTER, DROP, TRUNCATE Commands: SELECT, INSERT, UPDATE, DELETE
Affects schema and objects (e.g., tables, indexes) Affects the data stored in tables (e.g., rows, columns)
Generally auto-committed; cannot be rolled back in most cases
Can be rolled back or committed within a transaction
Usually faster for structural changes (e.g., creating tables)Typically slower due to data manipulation and processing overhead
Difference Between DROP, TRUNCATE, DELETE
Removes a table or database object
Removes all rows from a table Removes specified rows from a table
Deletes the table or object entirely
Removes all data but keeps the structure
Removes data, keeps the structure
Cannot be rolled back (permanently
Cannot
deleted)
be rolled back (permanently deleted)
Can be rolled back (if in a transaction)
Fully logged (every row deleted)Minimally logged Fully logged (every row deleted)
Fast (as it deletes the entire object)
Faster than DELETE (no row-by-row processing)
Slower (row-by-row processing)
Does not activate triggers Does not activate triggers Activates triggers (if defined)
Identity Column in MS SQL
In Microsoft SQL Server, an IDENTITY column is a column in a table that automatically generates unique numeric
values for new rows.
It is commonly used for primary keys.
Key Points:
- Purpose: To automatically generate unique sequential numbers (e.g., for primary keys).
- Definition: You define it with the IDENTITY property when creating or altering a table.
- Syntax: IDENTITY(seed, increment)
- Seed: The starting value for the first row (default is 1).
- Increment: The value by which the column increases for each new row (default is 1).
Example:
CREATE TABLE Employees (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(50)
);
Notes:
- No Gaps: There can be gaps in the values (e.g., if a transaction is rolled back or a row is deleted).
- Cannot Update: You cannot update the values in an IDENTITY column.
- Uniqueness: It guarantees unique values, typically used for primary keys.
