sql delete删除列_现有表操作中SQL DELETE列概述

sql delete删除列

In this article, we will explore the process of SQL Delete column from an existing table. We will also understand the impact of removing a column with defined constraints and objects on it.

在本文中，我们将探讨从现有表中删除SQL列的过程。 我们还将理解删除具有定义的约束和对象的列的影响。

介绍 (Introduction)

In a relational database, we use the schema design technique Normalizations and split the large tables into smaller tables. It helps to reduce the redundancy and dependency of data. We have multiple normalization forms specified in the following image.

在关系数据库中，我们使用模式设计技术Normalizations并将大型表拆分为较小的表。 它有助于减少数据的冗余性和依赖性。 下图中指定了多种规范化形式。

We do not cover normalization techniques in this article. You can refer to article – What is Database Normalization in SQL Server?

本文不介绍标准化技术。 您可以参考文章– 什么是SQL Server中的数据库规范化？

Suppose you have an existing table with multiple columns. There are specific columns in this table that are not relevant now. You do not require these columns. For example, consider a product table, and in this table, you have a column []. We implemented a separate mechanism to capture product feedback, and this column is not relevant now.

假设您有一个包含多列的现有表。 该表中的某些特定列现在不相关。 您不需要这些列。 例如，考虑一个产品表，在此表中，您有一列[]。 我们实施了一种单独的机制来捕获产品反馈，因此本专栏现在不再相关。

Let’s understand why it is essential to remove unwanted columns from the SQL table. Below is my table structure and we need to remove column having data type varchar(2000).

让我们了解为什么从SQL表中删除不需要的列很重要。 下面是我的表结构，我们需要删除数据类型为varchar（2000）的列。

This table contains 4 million rows, and the user executed the command to select all records from this table.

该表包含400万行，用户执行命令以从该表中选择所有记录。

SELECT * FROM Products;

Capture the actual execution plan (press CTRL + M) of this query along with client statistics (press Shift + Alt + S).

捕获此查询的实际执行计划（按CTRL + M）以及客户端统计信息（按Shift + Alt + S）。

In another query window, execute the query without the [Productfeedback] column and capture the similar statistics.

在另一个查询窗口中，执行不带[Productfeedback]列的查询并捕获类似的统计信息。

SELECT  [ProductID]
      ,[ProductName]
      ,[ProductLaunchDate]
  FROM [SQLShackDemo].[dbo].[Products]

Let’s compare the actual execution plan of both queries in SSMS. You can notice that query without the [Productfeedback] column provides improved performance. You see low values of compiled memory, CPU time, reduced wait counts.

让我们比较一下SSMS中两个查询的实际执行计划。 您会注意到，没有[Productfeedback]列的查询提供了改进的性能。 您会看到较低的编译内存，CPU时间，减少的等待计数。

The query optimizer and SQL database engine require to put an additional effort for this column while it is not useful to retrieve data from it.

查询优化器和SQL数据库引擎需要为此列付出更多的努力，而从中检索数据却没有用。

Similarly, compare the client statistics of both queries. You can note a significant reduction in the TDS packets received from the server. Bytes sent to the client, Bytes received from the server, client processing time and total processing time for the query that does not contain the [Productfeedback] column. It can help you to save the network bandwidth as well.

同样，比较两个查询的客户端统计信息。 您可以注意到从服务器收到的TDS数据包大大减少了。 发送给客户端的字节，从服务器接收的字节，客户端处理时间以及不包含[Productfeedback]列的查询的总处理时间。 它也可以帮助您节省网络带宽。

Here, I intend that you should perform database clean up and remove the unwanted columns to improve query performance as well.

在这里，我打算您应该执行数据库清理并删除不需要的列，以提高查询性能。

Let’s explore how to remove a column from an existing SQL table.

让我们探讨如何从现有SQL表中删除列。

使用SSMS对象资源管理器SQL DELETE列 (SQL DELETE column using SSMS object explorer)

We can remove a column using the Graphical User Interface (GUI) method in SQL Server. Connect to an instance in SSMS, expand databases. In the particular database, locate the particular table and expand the columns.

我们可以使用SQL Server中的图形用户界面（GUI）方法删除列。 连接到SSMS中的实例，展开数据库。 在特定的数据库中，找到特定的表并展开列。

It shows all the columns of the particular table. Right-click on the column we want to remove and click on Delete as shown in the following image.

它显示特定表的所有列。 右键单击我们要删除的列，然后单击删除 ，如下图所示。

It opens a window and lists the column name to remove. Click Ok, and it removes the column from the table.

它打开一个窗口，并列出要删除的列名。 单击确定，它将从表中删除该列。

使用SSMS表设计器SQL DELETE列 (SQL DELETE columns using SSMS table designer)

We use the table designer in SSMS to define required columns, data types, and column properties such as primary key, identity columns. We can use it to delete a column from an existing table as well.

我们在SSMS中使用表设计器来定义所需的列，数据类型和列属性，例如主键，标识列。 我们也可以使用它从现有表中删除列。

Right-click on the table and go to Design.

右键单击表格，然后转到“设计”。

It shows all column of a particular table.

它显示特定表的所有列。

Right-click on the left-hand side of a column and you get option Delete Column. Click on it to delete a column.

右键单击列的左侧，您将获得选项Delete Column 。 单击它删除一列。

You can either save it by pressing CTRL + S or close the table designer and click Yes on the following window.

您可以通过按CTRL + S来保存它，也可以关闭表设计器并在以下窗口中单击“ 是 ”。

使用T-SQL表设计器SQL DELETE列 (SQL DELETE columns using the T-SQL table designer)

We can use Alter table command to remove a column as well. The syntax is simple to use. The following command removes [ProductFeedback] column from the [Products] table.

我们也可以使用Alter table命令删除列。 该语法易于使用。 以下命令从[Products]表中删除[ProductFeedback]列。

USE [SQLShackDemo]
GO
ALTER TABLE [dbo].[Products] DROP COLUMN [ProductFeedback]
GO

• First, specify the table name that contains the column to remove
  首先，指定包含要删除的列的表名
• Second, specify the name of the column that we want to delete
  其次，指定我们要删除的列的名称

We can also remove multiple columns in a single Alter table command, but all columns should belong to a single table. This command removes [productFeedback] and [ProductlaunchDate] columns in a single command.

我们还可以在单​​个Alter table命令中删除多个列，但是所有列应属于一个表。 此命令在单个命令中删除[productFeedback]和[ProductlaunchDate]列。

USE [SQLShackDemo]
GO
ALTER TABLE [dbo].[Products] DROP COLUMN [ProductFeedback], [ProductLaunchDate]
GO

SQL DELETE列包含CHECK约束 (SQL DELETE column contains CHECK constraints)

We use CHECK constraints to limit the value range in a column. Suppose you have a column having CHECK constrains on it.

我们使用CHECK约束来限制列中的值范围。 假设您有一列具有CHECK约束的列。

CREATE TABLE ProductSales
(ID           INT, 
 ProductName  VARCHAR(50), 
 productPrice DEC(10, 2) NOT NULL
                         CONSTRAINT ck_price_check CHECK(productPrice >= 0),
);

We want to remove [ProductPrice] column, but this column has a CHECK constraint on it. Let’s try to remove it.

我们要删除[ProductPrice]列，但是此列具有CHECK约束。 让我们尝试将其删除。

 ALTER TABLE [dbo].[ProductSales] DROP COLUMN ProductPrice
GO

You get an error message that objects CK_Price_Check is dependent on the column [ProductPrice].

您收到错误消息，对象CK_Price_Check依赖于[ProductPrice]列。

We cannot remove this column until it has a dependent object in it. To remove this column, first, remove the dependency and then delete the column.

在此列中有从属对象之前，我们无法删除它。 要删除此列，请首先删除依赖项，然后删除该列。

ALTER TABLE [dbo].[ProductSales] DROP CONSTRAINT ck_price_check
Go
ALTER TABLE [dbo].[ProductSales] DROP COLUMN ProductPrice
GO

Let’s try to remove column having CHECK constraint using table designer. Open table designer for the [ProductSales] table.

让我们尝试使用表设计器删除具有CHECK约束的列。 为[ProductSales]表打开表设计器。

Right-click on the column and verify that CHECK constraints exist on this table.

右键单击该列，并验证此表上是否存在“检查”约束。

It shows the CHECK constraints as per the following image.

它显示了如下图所示的CHECK约束。

Now, right-click on the column [ProductPrice] and delete column. It does not give any error message.

现在，右键单击[ProductPrice]列并删除列。 它不给出任何错误信息。

Close the designer window, and it asks you to click on Yes for saving changes.

关闭设计器窗口，并要求您单击“ 是”以保存更改。

It gives you a validation warning message and gives a message about the check constraint.

它为您提供验证警告消息，并提供有关检查约束的消息。

Click on Yes, and it fails to modify the table. It cannot remove the [Productprice] column because CHECK constraint is dependent on it.

单击是 ，它将无法修改表。 它不能删除[Productprice]列，因为CHECK约束取决于该列。

We cannot remove the column using table designer if the column has dependencies on other objects.

如果列依赖于其他对象，则无法使用表设计器删除该列。

定义了SQL视图SQL DELETE列 (SQL DELETE column having a SQL View defined )

Let’s create a SQL View to select all records from [ProductSales] table.

让我们创建一个SQL视图以从[ProductSales]表中选择所有记录。

Create View vw_ProductSales
as
SELECT * from ProductSales

This view depends on all columns of the [ProductSales] table. Drop the column using the Alter table command.

此视图取决于[ProductSales]表的所有列。 使用“更改表”命令删除该列。

ALTER TABLE [dbo].[ProductSales] DROP COLUMN ProductPrice
GO

We do not get any error message on the query execution. Let’s try to access the records from the view.

我们在执行查询时没有收到任何错误消息。 让我们尝试从视图访问记录。

SELECT *
FROM vw_ProductSales;

We get an error message because the table does not contain the [ProductPrice] column. This column was available once we created the SQL view.

我们收到一条错误消息，因为该表不包含[ProductPrice]列。 创建SQL视图后，此列可用。

• Note: we should check object dependencies before dropping a column. It will eliminate the issues that we face later on.

  注意 ：在删除列之前，我们应该检查对象的依赖性。 它将消除我们稍后面临的问题。

结论 (Conclusion)

In this article, we learned about SQL Delete a column from an existing table using different methods. We should be aware of all these methods. You should be careful in the production environment before removing columns. You should remove all dependencies and plan it properly.

在本文中，我们学习了有关使用不同方法从现有表中删除SQL列SQL。 我们应该意识到所有这些方法。 在除去色谱柱之前，在生产环境中应格外小心。 您应该删除所有依赖项并进行适当计划。

翻译自: https://www.sqlshack.com/sql-delete-column-from-an-existing-table/

sql delete删除列