控制表中的最大行数

目录

介绍

又怎样？

关于性能

资源消耗

结论

兴趣点

• 下载控制 - 997 B

有时，您可能需要控制可以向表中添加多少条记录。这可以是整体限制或基于表中某些列的限制，例如基于外键。

一个简化的示例可能是您希望将单个订单的订单行数限制为三个。

让我们首先创建一个简单的测试场景。我们需要一个像下面这样的OrderItem表：

CREATE TABLE OrderItem (
   OrderId   INT           NOT NULL PRIMARY KEY ,
   Ordered   DATETIME
);

当然，还有一张OrderRow表：

CREATE TABLE OrderRow (
   OrderRowId INT          NOT NULL PRIMARY KEY ,
   OrderId    INT          NOT NULL FOREIGN KEY REFERENCES OrderItem (OrderId),
   Product    VARCHAR(100) NOT NULL,
   Amount     INT          NOT NULL,
   Price      DECIMAL      NOT NULL
);

在这一点上的一个小免责声明：通常，主键将是一个自动递增的列，但为了在这个技巧中简单起见，我使用了一个传统的列。

现在我们可以尝试向表中添加一些数据，首先是订单：

INSERT INTO OrderItem (OrderId, Ordered)
VALUES (1, CURRENT_TIMESTAMP);

然后是订单行：

INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (1, 1, 'Product A', 1, 100);
INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (2, 1, 'Product B', 1, 200);
INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (3, 1, 'Product C', 1, 300);
INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (4, 1, 'Product D', 1, 400);
INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (5, 1, 'Product E', 1, 500);

如果您对此进行测试，一切都会顺利进行，并且会在OrderRow表中插入五行。那么如何限制行数呢？

也许最简单的方法是为表定义触发器。触发器的目的是检查是否添加了太多行，如果发现太多则抛出错误。触发器可能如下所示：

CREATE TRIGGER OrderRow_Trigger
ON OrderRow
AFTER INSERT
AS
BEGIN
   DECLARE @orderId      int;
   DECLARE @totalCount   int;
   DECLARE @maxCount     int = 3;
   DECLARE @errorText    varchar(100);

   DECLARE curAmountCheck CURSOR FAST_FORWARD FOR
      WITH OrderIds AS (
         SELECT DISTINCT
                i.OrderId AS OrderId
         FROM inserted       i
      )
      SELECT oi.OrderId AS OrderId,
             COUNT(*)   AS Amount
      FROM OrderIds oi
      INNER JOIN OrderRow o on o.OrderId = oi.OrderId
      GROUP BY oi.OrderId
      HAVING COUNT(*) > @maxCount
      ORDER BY oi.OrderId
      OFFSET 0 ROWS
      FETCH FIRST 1 ROW ONLY;

      OPEN curAmountCheck;
      FETCH NEXT FROM curAmountCheck INTO @orderId, @totalCount;
      IF @@FETCH_STATUS = 0 BEGIN  
         SET @errorText = 'Order ID ' + _
         CAST(@orderId AS VARCHAR(100)) + ' has too many rows (' +  _
         CAST(@totalCount AS VARCHAR(100)) + ')';
      END;
      CLOSE curAmountCheck;
      DEALLOCATE curAmountCheck;

      IF @errorText IS NOT NULL BEGIN
         THROW 50001, @errorText,1;
      END;

END;

让我们把逻辑分解成几部分：

首先是触发器定义，每当表中出现新行时（INSERT）都会执行此触发器。触发逻辑在insert AFTER被执行，这意味着所有的约束条件已检查。例如，如果违反NOT NULL约束，该触发器将不会执行，因为该行未添加到表中。

然后我们有一些用于程序逻辑的变量。该@maxCount其实是没有必要的，但它有助于看到行的最大限制。

查询。这是触发器的主要组件。查询获取所有订单行过多的订单。为了简化查询，我使用通用表表达式(CTE) 将查询分解为更小的部分。命名OrderIds查询只是获取已插入批处理中的所有单个OrderId。查询是从inserted表中完成的，该表是一个虚拟表触发器，可以用来查看批处理中插入了哪些行。

现在我们知道了所有的OrderIds，CTE的外部部分简单地获取每个OrderId增加的订单行数。请注意，同一批次也可以删除订单行，但除非也有插入，否则我们无需担心这些。从实际OrderRow表中获取总金额，分组，然后只返回超过最大限制的组。

查询被定义为游标。这可以通过多种方式完成，但在本例中，我选择使用游标来轻松获取一些信息性数据以包含在潜在错误消息中。

最后，打开游标并使用它获取数据。如果获取成功，则意味着我们有太多订单行的OrderId。在这种情况下，会抛出错误。

现在我们必须测试这是否真的有效。让我们使用以下脚本来完成它：

INSERT INTO OrderItem (OrderId, Ordered)
VALUES (2, CURRENT_TIMESTAMP);
GO

INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (6, 2, 'Product A', 1, 100);
INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (7, 2, 'Product B', 1, 200);
INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (8, 2, 'Product C', 1, 300);
INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (9, 2, 'Product D', 1, 400);
INSERT INTO OrderRow (OrderRowId, OrderId, Product, Amount, Price)
VALUES (10, 2, 'Product E', 1, 500);
GO

运行时，它会产生以下输出：

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)
Msg 50001, Level 16, State 1, Procedure OrderRow_Trigger, Line 29 [Batch Start Line 73]
Order ID 2 has too many rows (4)

重要的提示！由于这不是约束而是触发器，除非触发器存在并被激活（为特定订单插入行），否则它不会强制执行此逻辑。这意味着旧行保持原样。如果你看这个技巧的开头，你会注意到我们为OrderId1添加了5个订单行。没有任何改变，这些行仍然存在于表中。因此，您可能需要根据要求手动检查旧数据。

触发器和游标总是引发关于性能和潜在问题的讨论。进行这个讨论很好，所以让我们深入探讨一下这个主题。

首先，我们需要考虑我们所比较的对象。如果强制执行此业务规则，则至少有四种不同的可能性：

1. 在插入单个行之前，从客户端使用SQL查询检查行数。
2. 与上一个项目符号中的检查相同，但查询是在从客户端调用的存储过程中实现的。
3. 检查从客户端的金额后的insert。
4. 如本技巧所述，由触发器完成的检查。

将项目符号一、二、三与第四个选项进行比较，至少应考虑三个主要差异：

1. 往返代价。其他选项要求在执行之前（或之后）执行insert从客户端到数据库的单独调用。这需要往返，这会导致网络流量和可能的其他资源使用，具体取决于架构。这可能会也可能不会给操作带来瓶颈。
2. 插入多行。如果应用程序想要使用一次运行将多行插入到表中，如何验证行数？例如，当使用INSERT INTO .. SELECT——结构或MERGE语句时，就会发生这种情况。应用程序必须以某种方式知道应该检查的行，或者需要将单个语句分成循环。显然，打破语句使用循环将是一个糟糕的选择性能明智。
3. 与前一个项目符号相比，差异中的第三个选项有点相似。不知何故，应用程序需要知道插入了哪些行以便之后进行检查。如果只插入一行，这很简单，但如果插入多行，情况就不同了。在最坏的情况下，需要一遍又一遍地检查所有现有行。

看看这三个不同之处，使用基于触发器的解决方案的好处是不需要往返，触发器处理插入到批处理中的所有行，而不管它们有多少。

往返影响是特定于环境的，因此我们无法在本技巧中对其进行分析。在单次运行期间插入多行是一个架构点，因此它也在此范围之外。我们可以比较的是单个SQL语句的执行，所以让我们来看看它们。

那么，触发检查的代价是多少？一种简单但不全面的看待这个问题的方法是调查执行计划。我所做的是添加10'000行OrderItem和 30'000行OrderRow，然后执行单个INSERT INTO OrderRow语句并检查实际计划。

计划INSERT看起来是这样的：

使用计划FETCH CURSOR如下：

如您所见，触发器中的提取占总成本的22%。一般来说，与insert声明相比，这实际上并不多。当然，必须记住由于插入而需要的索引维护成本。

嗯，不错，但关键问题是，这与单独使用的单独约束相比如何。为了进行比较，我使用了以下语句：

DECLARE  @orderId  int = 2;
DECLARE  @maxCount int = 3;

SELECT o.OrderId AS OrderId,
       COUNT(*)  AS Amount
FROM   OrderRow o
WHERE  o.OrderId = @orderId
GROUP BY o.OrderId
HAVING COUNT(*) > @maxCount;

该计划如下所示：

仅看成本，它大约是触发器中使用的查询量的一半。但是，请记住，成本的差异不是因为查询的位置（触发器或外部），而是因为触发器中的查询使用inserted表来检查所有插入的行。如前所述，这在触发器之外是不可能的。

另请注意，我们只比较计划，而不是整个执行，因此不包括网络流量等。

作为结论，我想说从性能的角度来看，即使将逻辑放在触发器内部也没有太大区别，只要实现设计得好。然而，当使用基于触发器的解决方案时，它有两个主要好处：

• 无论插入是如何完成的，业务逻辑总是被强制执行。
• 始终强制执行业务逻辑，即使是基于集合的插入

您可能会觉得有用的一些链接是：

• CREATE TRIGGER (Transact-SQL)
• DECLARE CURSOR (Transact-SQL)
• WITH common_table_expression (Transact-SQL)
• SELECT - GROUP BY- Transact-SQL
• Use the inserted and deleted Tables
• THROW (Transact-SQL)

Control Maximum Number of Rows in a Table - CodeProject