Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Dropping Tables

In SQL Server, the DROP TABLE statement is used to permanently remove an existing table from a database. In this section, we will learn how to safely drop tables and understand what happens when a table is deleted.

What is DROP TABLE?

The DROP TABLE statement completely removes a table from the database.

When a table is dropped, its structure and stored records are removed.

SQL
DROP TABLE Students;

DROP TABLE Flow

SQL Server
Students Table
       ↓
DROP TABLE
       ↓
Table Removed
       ↓
Data + Table Structure Deleted

Drop a Table

Suppose we have a table named Students. The following statement removes it:

SQL
DROP TABLE Students;

After executing this statement, the Students table will no longer exist in the database.

Table Before DROP

StudentId StudentName Age Course
1 Rahul 22 Java
2 Priya 21 .NET
3 Amit 23 Python

Execute DROP TABLE

SQL
DROP TABLE Students;

Verify Whether the Table Exists

Before performing operations, we can check whether a table exists in the current database.

SQL
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Students';

If the table has been dropped successfully, the query will not return the Students table.

DROP TABLE IF EXISTS

SQL Server also supports DROP TABLE IF EXISTS. It is useful when we want to drop a table only if it exists.

SQL
DROP TABLE IF EXISTS Students;

This avoids an error when the specified table does not exist.

Dropping Multiple Tables

Multiple tables can be dropped by using separate DROP TABLE statements.

SQL
DROP TABLE IF EXISTS Students;

DROP TABLE IF EXISTS Courses;

DROP TABLE IF EXISTS Departments;

DROP vs DELETE

DROP and DELETE are different SQL operations.

DROP TABLE DELETE
Removes the complete table. Removes rows from a table.
Table structure is removed. Table structure remains.
Table no longer exists. Table still exists.
Used to remove a database table. Used to remove records.

DROP vs TRUNCATE

Operation Table Structure Data
DROP Removed Removed
TRUNCATE Remains Removed
⚠ Important Warning

DROP TABLE permanently removes the table structure and its data. Always verify the table name before executing a DROP TABLE statement, especially when working with important or production databases.

Practical Example

The following example creates a temporary table and then removes it.

SQL
CREATE TABLE DemoStudents
(
    StudentId INT,
    StudentName VARCHAR(100)
);

DROP TABLE DemoStudents;

Result

After executing DROP TABLE, the DemoStudents table is removed from the database along with its table structure and stored records.

Summary

In this section, we learned how to remove a table using DROP TABLE, how to check whether a table exists, and the difference between DROP, DELETE and TRUNCATE operations.