SQL Server SELECT MAX and Add 1

Question

下面的c#代码与MySQL服务器一起工作，正确地从表中获取列的最大值，同时这个查询将值加1，如下所示:

SqlDataReader dr = new SqlCommand("SELECT (MAX(Consec) +1) AS NextSampleID FROM Samples", Connection).ExecuteReader();  
while (dr.Read())
{ //in case of Maximum value of Consec = 555, the expected result: A556
txtSampleID.Text = "A" + dr["NextSampleID"].ToString();
}

然而，这段代码不再工作后，数据库从MySQL迁移到SQL Server，结果是相同的，如果MAX(Consec) = 555后，运行查询的结果是A555，它不像以前使用MySQL服务器时添加1。

问题:获得Consec的MAX值的正确查询是什么以及如何添加"1"在同一查询MAX的结果?

Answer 1

MySQL查询是错误的，除非在琐碎的应用程序中，只有一个用户，没有删除和没有关系:

• 并发调用将产生相同的MAX值，因此结果相同，重复的下一个值
• 删除记录将减少MAX值，导致先前的ID值被分配给新行。如果在另一个表中使用该ID值，则新记录最终将与它没有实际关系的行相关联。这可能非常糟糕。想象一下，一个病人的测试样本与另一个病人的测试样本混合在一起。
• 计算MAX需要锁定整个表或索引，从而阻塞或被其他阻塞。考虑到MySQL的MVCC隔离，这不会阻止重复，因为并发的SELECT MAX查询不会互相阻塞。

MAX+1可能在只有一个终端生成发票号码的POS应用程序中工作，但是一旦您添加了两个POS终端，您就有可能生成重复的发票。

另一方面，在电子商务应用程序中，几乎可以保证即使每个月只下两个订单，它们也会在同一时刻发生，从而导致重复。

正确的MySQL解决方案和等效的

MySQL中正确的解决方法是使用AUTO_INCREMENT属性:

CREATE TABLE Samples (
Consec INT NOT NULL AUTO_INCREMENT,
...
);

如果希望发票号包含其他数据，请使用计算列将递增的数字和其他数据组合在一起。

在SQL Server中等价的是IDENTITY属性:

CREATE TABLE Samples (
Consec INT NOT NULL IDENTITY,
...
);

在SQL Server和其他数据库中，另一个可用的选项是SEQUENCE对象。SEQUENCE可用于生成与表无关的递增数字。它也可以重置，使其成为会计应用程序的理想选择，其中发票号码在特定时期(例如每年)后重置。

由于SEQUENCE是一个独立的对象，您可以在使用NEXT value FOR例如:

在数据库中插入任何数据之前增加并接收新值。

SELECT NEXT VALUE FOR seq_InvoiceNumber;

NEXT VALUE FOR可以用作表列的默认约束，就像使用IDENTITY或AUTO_INCREMENT一样:

Create MyTable (
...
Consec INT NOT NULL DEFAULT (NEXT VALUE FOR seq_ThatSequence)
)

<<p>

多表序列/strong>相同的序列可以在多个表中使用。一个有用的例子是为从多个源导入的数据分配一个Document ID，这些数据存储在不同的表中，例如支付。

支付提供商(信用卡、银行等)使用不同的格式发送对账单。显然，你不能在那里丢失任何信息，所以你需要为每个提供者使用不同的表，但仍然能够以相同的方式处理付款，无论他们来自哪里。

如果你在每个表上使用IDENTITY，你最终会得到来自不同提供商的支付冲突的id。例如，在OrderPayments表中，您必须同时记录提供者名称和ID。生成支付的单一视图最终会产生不能被自己使用的ID值。

通过使用单个SEQUENCE，每个记录都将获得自己的ID，而与表无关。