SQL Server DELETE and DROP TABLE Commands
Last Updated :
29 Apr, 2024
In SQL Server, managing data involves not only inserting and updating records but also removing them when they're no longer needed. Two important commands for data removal are DELETE and DROP TABLE. These commands play crucial roles in maintaining database integrity and managing database structures.
In this article, we'll learn about the DELETE and DROP TABLE commands in SQL Server, exploring their syntax, use cases, and so on.
Introduction to DELETE and DROP TABLE Commands
DELETE Command
- The DELETE command is used to remove rows from a table based on specified criteria. It allows you to selectively delete data while keeping the table structure undamaged.
- The
DELETE
command is transactional which means we can wrap it in a transaction to ensure that either all the specified rows are deleted or none are.
- SQL Server logs
DELETE
operations which can be useful for auditing purposes or in case we need to recover deleted data using database backups or transaction logs.
- The
DELETE
command can be used to remove rows from a table that is referenced by foreign key constraints in other tables.
Syntax:
DELETE FROM table_name
WHERE condition;
DROP TABLE Command
- The DROP TABLE command is used to delete an entire table from the database. This command removes both the table structure and all associated data, effectively deleting the table from the database.
- It is a straightforward way to delete an entire table without the need to delete individual rows.
- Dropping a table releases the disk space occupied by the table and its data, which can be useful for managing database storage.
Syntax:
DROP TABLE [IF EXISTS] table_name;
Example of SQL Server DELETE and DROP TABLE Commands
Let's start by creating a sample products table in SQL Server and then performing the DELETE and DROP TABLE tasks.
-- Create the products table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2),
quantity INT
);
-- Insert sample data into the products table
INSERT INTO products (product_id, product_name, price, quantity)
VALUES
(1, 'Laptop', 999.99, 10),
(2, 'Smartphone', 699.99, 20),
(3, 'Tablet', 399.99, 15),
(4, 'Headphones', 99.99, 30);
product_id | product_name | price | quantity |
---|
1 | Laptop | 999.99 | 10 |
2 | Smartphone | 699.99 | 20 |
3 | Tablet | 399.99 | 15 |
4 | Headphones | 99.99 | 30 |
This SQL script creates a products table with columns for product_id, product_name, price, and quantity, and inserts some sample data into the table.
Example Using the DELETE Command
-- Delete all products with a price less than 500
DELETE FROM products
WHERE price < 500;
Output:
product_id | product_name | price | quantity |
---|
1 | Laptop | 999.99 | 10 |
2 | Smartphone | 699.99 | 20 |
In this example, the DELETE command removes all rows from the orders table where the total_amount column is less than 500.
Example Using the DROP TABLE Command
-- Drop the products table
DROP TABLE products;
In this example, the DROP TABLE command deletes the products table from the database, including all its columns and data.
Dropping a Table with Constraints
When a table has foreign key constraints referencing it, we must first drop those constraints before dropping the table itself. Otherwise, we will encounter an error. Here's how to drop a table with constraints:
-- Create a new table with a foreign key constraint referencing the products table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
product_id INT,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- Drop the orders table
DROP TABLE orders;
In this example, we first create a new table orders with a foreign key constraint referencing the products table. Then, we drop the orders table before dropping the products table.
Conclusion
The DELETE and DROP TABLE commands are essential tools for managing data in SQL Server databases. While the DELETE command removes specific rows from a table based on specified criteria, the DROP TABLE command deletes entire tables from the database, including all data and table structure. In this article, we explored the concepts of DELETE and DROP TABLE commands in SQL Server, provided examples of their usage, and discussed the output of these commands. As you continue to work with SQL Server, mastering these commands will enable you to effectively manage data and database structures in your applications. Experiment with these concepts in your SQL Server environment to deepen your understanding and enhance your database management skills.
Similar Reads
SQL Server ALTER TABLE DROP COLUMN
In SQL Server, there could be some situations when we may have to delete or drop a column from a table. Sometimes the column in a table was created wrongly or maybe it is no longer required and has become obsolete. So, to drop a column from a table, the ALTER TABLE, DROP COLUMN SQL query is used. In
4 min read
SQL Server ALTER TABLE
In SQL Server, there are various commands to Add, Update, and Delete a Database Schema called DDL or Data Definition Language commands. A Table in a database is part of the database schema and the 'ALTER TABLE Moify Column' command is used to make changes to the database table column data type, colu
5 min read
SQL Server Common Table Expressions
SQL Server is a relational database management system (RDBMS) that is used to handle complex data and maintain it in of tabular manner. With the help of SQL Server, one can easily protect their data as it provides various security features. In this article, we are going to explore SQL server's CTE a
8 min read
Deleting a Column in SQL Server
Structure Query Language (SQL) is a standard language to manipulate and manage the database. SQL is a very powerful language for managing the database. SQL Server Delete command is one of the SQL commands that is used to remove the data that is not useful or due to which inconsistency occurred in th
5 min read
CREATE TABLE in SQL Server
SQL Server provides a variety of data management tools such as querying, indexing, and transaction processing. It supports multiple programming languages and platforms, making it a versatile RDBMS for various applications. With its robust features and reliability, SQL Server is a popular choice for
4 min read
Select Statement in MS SQL Server
The SELECT statement in SQL Server is a foundational SQL command used for querying and retrieving data from one or more tables within a database. This command allows users to specify which columns and rows to retrieve and apply filters to focus on specific data and perform various operations to mani
4 min read
Select into and temporary tables in MS SQL Server
In SQL Server, the SELECT INTO TEMP TABLE statement is used to select data from one or more source tables and insert it into a temporary table. Temporary tables are extremely useful when dealing with intermediate results, or when working with subsets of data within a session without modifying or aff
4 min read
Magic Tables in SQL Server
Magic tables are the temporary logical tables that are created by the SQL server whenever there are insertion or deletion or update( D.M.L) operations. The recently performed operation on the rows gets stored in magic tables automatically. These are not physical table but they are just temporary int
3 min read
Copy Tables Between Databases In SQL Server
Copying tables between databases in SQL Server can be crucial for data migration, backups, or setting up test environments. One effective method to achieve this is by generating scripts using SQL Server Management Studio (SSMS).In this article, we will learn how to Copy Tables Between Databases In S
3 min read
SQL Server Describe Table
SQL Server is a widely used Relational Database Management System (RDBMS) that allows users to create and manage databases effectively. When working with databases in SQL Server it is essential to understand the schema of the tables present in the database. Describing a table means getting informati
4 min read