DDL (Data Definition Language)

• DDL is used to define and modify database structures (schemas).

• Main DDL commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.

• CREATE is used to make new tables, views, stored procedures, functions, etc.

• ALTER modifies existing objects like adding/dropping columns, changing data types.

• DROP permanently deletes database objects.

• TRUNCATE removes all rows from a table but keeps the structure; faster than DELETE.

• DDL commands auto-commit — you can’t roll them back without explicit transaction control.

• CREATE TABLE should include primary key and constraints for best practice.

• You can use IF EXISTS with DROP or CREATE to avoid errors.

• CREATE INDEX improves performance for read-heavy queries.

• Always take a backup before applying DDL changes in production.

• ALTER TABLE allows adding constraints (PK, FK, CHECK) after table creation.

• Use sp_help or INFORMATION_SCHEMA.TABLES to check object structure.

• You can generate DDL scripts from SSMS by right-clicking on the object.

• Constraints like UNIQUE, NOT NULL, DEFAULT are added via DDL.

• You can use CREATE SCHEMA to organize tables under logical groups.

• Follow naming conventions when creating objects for consistency and clarity.

• Interview Questions with solutions

🔹 DML (Data Manipulation Language)

• DML commands are used to modify and retrieve data in existing tables.

• Main DML commands include SELECT, INSERT, UPDATE, DELETE, and MERGE.

• SELECT is used to retrieve data from one or more tables or views.

• INSERT is used to add new records into a table.

• You can insert single or multiple rows in one INSERT statement.

• UPDATE modifies existing records based on conditions using the WHERE clause.

• Always use a WHERE clause in UPDATE to avoid updating all rows.

• DELETE removes records based on a condition, but the table structure remains.

• Without a WHERE clause, DELETE removes all records — use cautiously.

• MERGE combines INSERT, UPDATE, and DELETE in a single query (though you prefer not to use it, it’s worth mentioning).

• Transactions can be applied to DML commands (BEGIN TRAN, COMMIT, ROLLBACK).