在没有游标的情况下在 SQL Server 2005 中使用序列号 [英] Working with sequential numbers in SQL Server 2005 without cursors

问题描述

我目前正在开展一个项目，该项目需要有一个流程来为某些记录分配控制编号".这还需要能够在以后运行并包含没有更改的控制编号的记录，并为这些记录分配一个未使用的控制编号.这些控制编号由外部实体预先分配，长度为 9 位.您通常会得到一个范围，具体取决于您的公司将生成多少估计记录.例如，其中一家公司估计他们需要 50 个，因此他们为我们分配了 790123401 到 790123450 的范围.

I'm currently working on a project that needs to have a process that assigns "control numbers" to some records. This also needs to be able to be run at a later date and include records without a control number that changed, and assign an unused control number to these records. These control numbers are preassigned by an outside entity and are 9 digits long. You would usually get a range depending on how many estimated records your company will generate. For example one of the companies estimated they would need 50, so they assigned us the range 790123401 to 790123450.

问题:现在我使用游标来分配这些数字.对于每个单独的记录，我去检查表中是否已经采用了序列中的第一个数字，如果是，则增加该数字，然后重新检查.这种检查会针对表中的每条记录不断进行.其中一家公司有 17,000 条记录，这意味着对于每条记录，如果所有数字都被采用，我可能会迭代最坏的 17,000 次.

The problem: right now I'm using cursors to assign these numbers. For each individual record, I go and check if the first number in the sequence is already taken in the table, if it is, I increment the number, and recheck. This check goes on and on for each record in the table. One of the companies has 17,000 records, which means that for each of the records, I could be potentially iterating at worst 17,000 times if all numbers have been taken.

我真的不介意初始运行中的所有重复，因为第一次运行会将控制编号分配给很多记录.我的问题是，如果稍后一条记录被更改，现在应该有一个与之关联的控制号，那么重新运行该过程将意味着它会遍历每个可用的号码，直到我得到一个未使用的号码.

I really don't mind all the repetition on the initial run since the first run will assign control numbers to a lot of records. My problem is that if later a record gets changed and now should have a control number associated with it, then re-running the process would mean it would go through each available number until I get an unused one.

我看过很多关于如何在没有游标的情况下使用序列的例子，但大多数都是针对 Oracle 的.我在这个特定项目中使用 SQL Server 2005.

I've seen numerous examples on how to use sequences without cursors, but most are specific to Oracle. I'm using SQL Server 2005 for this particular project.

建议?

推荐答案

您要查找某个范围内所有未分配的数字?如果是这样，您可以外部连接到数字表.下面的示例使用 CTE 即时创建一个，如果这是您的范围的最大大小，我建议使用一个包含至少 17,000 个数字的永久数字.

You are looking for all unassigned numbers in a range? If so you can outer join onto a numbers table. The example below uses a CTE to create one on the fly I would suggest a permanent one containing at least 17,000 numbers if that is the max size of your range.

DECLARE @StartRange int, @EndRange int
SET @StartRange = 790123401
SET @EndRange = 790123450;

WITH YourTable(ControlNumber) AS
(
SELECT 790123401 UNION ALL
SELECT 790123402 UNION ALL
SELECT 790123403 UNION ALL
SELECT 790123406
),
Nums(N) AS
(
SELECT @StartRange
UNION ALL
SELECT N+1
FROM Nums
WHERE N < @EndRange
)
SELECT N 
FROM Nums
WHERE NOT EXISTS(SELECT * 
                 FROM YourTable 
                 WHERE ControlNumber = N )
OPTION (MAXRECURSION 0)          

这篇关于在没有游标的情况下在 SQL Server 2005 中使用序列号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！