Deleting a Column in SQL Server
Last Updated :
25 Jan, 2024
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 the database. In this article, we will see a detailed explanation of the Delete command, the syntax of the Delete command, and an explanation of examples of the delete command step by step.
In the world of relational databases, the structure of tables plays a pivotal role in data organization. There are instances where it becomes necessary to modify the structure of a table, and one common operation is deleting columns. This article will guide you through the process of efficiently deleting columns from a table in SQL.
Prerequisites
- Know how to use MS SQL Server
- Basic knowledge of SQL Syntax
- Understanding of the primary key in the database to check whether data needs to be deleted.
Create Table Command
The create table command is used to create a table in the database. This is a command very important command in SQL Server.
Syntax:
CREATE TABLE table_name(column_name1 datatype1,
column_name2 datatype2,
column_name3 datatype3,
.
.
columns);
Here,
- table_name: Name of table.
- column_name: Name of column.
- datatype: Datatype of column name
Remove Duplicate Rows in SQL Server
Duplicate data can be a persistent challenge in relational databases, impacting both performance and data integrity. In SQL Server, crafting a script to eliminate duplicate rows is a powerful strategy.
Syntax:
WITH DuplicateCTE AS (
SELECT
YourColumns,
ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY (SELECT NULL)) AS RowNum
FROM YourTable
)
DELETE FROM DuplicateCTE WHERE RowNum > 1;
Here,
- Replace
YourColumns
with the actual column names you want to consider.
- Modify
Column1, Column2
to the columns that determine duplicates.
- The
ROW_NUMBER()
function assigns a unique number to each row based on the specified columns.
- The
PARTITION BY
clause ensures that the numbering restarts for each unique combination of columns.
- The
DELETE
statement removes rows with a row number greater than 1, keeping only the first occurrence.
Examples of Removing Duplicate Rows in SQL Server
Step 1: Create a Sample Table
Consider a table named Employee
with the following structure:
Step 2: Create Script to Remove Duplicate Rows
Now, let's use a script to remove duplicate rows based on the FirstName
and LastName
columns:
Examples of SQL Server DELETE Single Row
Example 1: Deleting single Record
Let's take a table named 'Employee' and we want to delete the record of employee_id is 10..
SQL Query:
DELETE from Employee
WHERE employee_id=10;
Before Deletion:
Before DeletionExplanation: Here in the above image, we see the records of the table before deleting any record from the table. You can fetch these records from the table using the SELECT Statement.
Running Delete query:
Query for Deleting Single row Explanation: In the above image you see that running delete query to remove the record from the table. In this example we remove single data of employees whose employee_ID is 10 for this we use the condition using the WHERE keyword
After Deletion:
OutputExplanation: In this image, you can see that after deletion of the record of an employee whose employee_ID is 10 is removed from the employee table.
Example 2: Deleting Multiple Records
In this example we delete multiple records from the table, we delete the record of all whose department ID is 2
SQL Query:
DELETE from Employee
WHERE department_id= 2;
Before Deletion:
Single row deleted tableExplanation: Here in the above image, we see the records of the table before deleting any record from the table. You can fetch these records from the table using the SELECT Statement
Running Delete Query:
Query for delete multiple rowsExplanation: In the above image you see that running delete query to remove the record from the table. In this example, all the records whose department_id is 2 will be removed because we use the condition to remove all employee data whose department_id is 2.
After Deletion:
Output after deletion of multiple rowsExplanation: In this image, you can see that after deletion of the records of employees whose department_id is 2 is removed from the employee table.
Example 3: Deleting All Records
In this example, we delete all the records from a table. It can be done by running a delete query without using a condition.
SQL Query:
DELETE From Employee;
Before Deletion:
after multiple deleteExplanation: Here in the above image, we see the records of the table before deleting any record from the table. You can fetch these records from the table using the SELECT Statement.
Running SQL Query:
all delete statementExplanation: In the above image you see that running delete query to remove the record from the table. In this example, all the records will be removed from the table because there is no condition so it will delete all the records from the table.
After Deletion:
Output Explanation: In this image, you can see that after deletion of all records of employees from the employee table.
Conclusion
SQL Server DELETE is a very important tool that is used to remove unwanted data from the tables. Understanding the syntax and its usage is important and is effective in managing and maintaining the database. You can easily understand the concept of DELETE command from the given examples.The DELETE
statement contributes to maintaining a well-organized and optimized database by allowing the removal of unwanted or outdated information.
Similar Reads
Rename Column in SQL Server
SQL Server is a widely used Relational Database Management System (RDBMS) that allows users to create and manage databases effectively. Renaming a column in a database is a common task usually required when users want to change the database schema. In this article, we will explore different methods
3 min read
Rename column SQL Server 2008
Renaming a column in a database is a common task that often arises when users want to change the database schema. In SQL Server 2008, renaming columns can be done efficiently using the sp_rename system-stored procedure, as the ALTER TABLE RENAME COLUMN syntax is not supported in this version. This a
4 min read
Change Primary Key Column in SQL Server
Primary Key refers to the column of a table that uniquely identifies a row in a table. It contains unique values and can not contain NULL values. For the purpose of the demonstration, we will be creating geeks for geeks table in a database called âgeeksâ. Step 1: Creating the database The database i
2 min read
Delete Views in SQL Server
In the area of relational databases, SQL Server is one of the most powerful and popular systems. It is flexible to make possible the development of complex data structures and their manipulation. SQL Server offers a crucial tool for managing data which is Delete Views. They allow users to delete row
4 min read
SQL Server DELETE and DROP TABLE Commands
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.
4 min read
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
sp_columns - SQL Server
In SQL Server, managing and understanding database schemas is crucial for effective database administration and development. The sp_columns stored procedure is a valuable tool for retrieving detailed metadata about the columns of a specified table or view. In this article, We will learn about sp_col
6 min read
How to Check a Column is Empty or Null in SQL Server
In SQL Server table columns, there can be times when there is NULL data or Column Value is Empty (''). When data is displayed or used in data manipulations, there could be a need to remove these records with NULL values or Empty column values or replace the NULL value with another value like EMPTY v
5 min read
How to Update a Column in a Table in SQL Server
In the database management area, updating columns in a table is a normal query and it is important software that ensures the accuracy and integrity of data. Whether we are making spelling corrections, editing or altering existing information, or being attentive to changing requirements, carrying out
4 min read
How to Add an IDENTITY to an Existing Column in SQL Server
It enables you to store, organize, and manipulate data in a relational format, meaning data is organized into tables. It Stores and manages data for dynamic web applications, ensuring effective user experiences. In this article, we will learn about How to add an identity to an existing column in SQL
5 min read