如何使用SQL Server的计算列

吴欣
682 阅读3分钟

计算列是一个虚拟列，其值是一个表达式的结果，主要使用存储在其他列中的其他数据。计算列是一个虚拟列，这意味着SQL Server并没有将它实际地存储在一个表中。相反，计算列的值是在数据被查询的时候计算出来的。然而，你可以通过指定持久化参数来物理存储它。

本指南将向你介绍在SQL Server中创建和使用计算列的要点。

在我们深入到一个实际的例子和使用计算列之前，有几个关键点需要注意。

  1. 计算列不能包含默认、非空或外键约束，除非该列被设置为持久化。
  2. 你不能在插入或更新语句中使用一个计算列。

SQL Server计算列的使用

为了更好地理解如何使用SQL Server计算列，我们将使用一个实际例子。

首先创建一个样本数据，如下面的查询中所示。

CREATE DATABASE sales_database;
USE sales_database;
CREATE TABLE sales(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    product_name VARCHAR(50),
    price money,
    quantity INT
);
INSERT INTO sales(product_name, price, quantity)
VALUES ('iPhone Charger', $9.99, 10),
       ('Google Chromecast', $59.25, 5),
       ('Playstation DualSense Wireless Controller', $69.00, 100),
       ('Xbox Series S', $322.00, 3),
       ('Oculus QUest 2', $299.50, 7),
       ('Netgear Nighthawk', $236.30, 40),
       ('Redragon S101', $35.98, 100),
       ('Star Wars Action Figure', $17.50, 10),
       ('Mario Kart 8 Deluxe', $57.00, 5);

一旦我们有了样本数据，我们就可以继续。

创建计算列：T-SQL

在SQL Server中创建一个计算列是很简单的。使用创建表或改变表的命令来添加一个计算列。

例如，我们可以创建一个计算列，根据价格和数量来计算物品的总价格。

请看下面的例子。

ALTER TABLE sales
ADD total_price AS price * quantity;

上面的查询根据价格*数量列的值创建了一个新的列。我们可以这样查询这个新表。

SELECT * FROM sales;

结果表如图所示：

注意total_price列的值是基于价格和数量列的值。

一旦我们更新任何一列的值，计算列中的值就会反映出变化，如图所示。

UPDATE sales SET quantity = 200
WHERE id = 3;

上面的查询将行号为3的数量更新为200。

如果我们再次查询数据，我们应该看到total_price的更新，以反映变化，如：。

SELECT * FROM sales;

要在创建表的过程中创建一个计算的，我们可以用AS关键字指定列，然后用公式来计算结果的集合。

DROP TABLE sales;
-- recreate table and re-insert sample data with new computed column
CREATE TABLE sales(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    product_name VARCHAR(50),
    price money,
    quantity INT,
    total_price AS price * quantity
);

这将用新的计算列重新创建表。

创建计算列：SSMS

你也可以使用SQL Server Management Studio来创建一个计算列。在对象资源管理器中，右键展开你的目标数据库->表->列->新列。

输入列名和接受的数据类型。

展开列的属性标签，选择计算列的规格。 设置该列的公式，如图所示。

要应用这些变化，选择主工具条并点击保存图标。

持久的计算型列

如果你想物理存储一个计算列的值，你可以在创建列的过程中添加持久化属性。

例如，下面的查询添加了一个持久化的计算列来计算产品的税收。

ALTER TABLE sales ADD tax AS (1.6 * quantity * price) persisted;

结果表如图所示。

注意，你不能在一个计算列中使用另一个计算列的值。例如，如果你试图将tax列的值设置为1.6 * total_price，SQL Server将返回一个错误。

显示计算列：T-SQL

要显示特定数据库中的计算列，请使用如下所示的查询。

SELECT * FROM sys.computed_columns;

该查询应该返回所选数据库中的计算列的详细信息。

总结

在这篇文章中，你了解了如何在SQL Server中创建和使用计算列。

avatar
文章
阅读
粉丝