我有三个潜在建议:

(1)将您的数字表设为只读(例如，拒绝更新/插入/删除). EVER，您为什么要从此表中删除?您的应用程序当然不应该这样做，您的用户也不应该手动进行操作.用户按下此按钮的作用是什么?"时，不需要所有这些检查约束.按钮，则只需删除按钮即可.

(1) Make your numbers table read-only (e.g. deny update/insert/delete). Why would you be deleting from this table, EVER? Your app certainly shouldn't be doing it, and your users shouldn't be able to manually do so either. No need for all these check constraints for users pressing the "what does this button do?" button, when you can simply remove the button.

DENY DELETE ON dbo.Serial TO [your_app_user];
-- repeat for individual users/roles

(2)更容易的是创建一个触发器而不是触发器来防止删除:

(2) Even easier would be to create an instead of trigger to prevent deletes in the first place:

CREATE TRIGGER dbo.LeaveMyNumbersAlone
ON dbo.Serial
INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;
  RAISERROR('Please leave my numbers table alone.', 11, 1);
END

是的，这是可以克服的，但是必须有人竭尽全力来做到这一点.而且，如果您雇用的人可能会这样做，并通过对数据库的通用访问权信任他们，请祈祷这是他们计划造成的最大损失.

Yes, this can be defeated, but someone has to really go out of their way to do it. And if you're employing folks that are likely to do this, and trusting them with generic access to the database, pray that this is the most damage they're planning to do.

是的，如果您删除/重新创建数字表或在其他地方实现它，您可能会忘记重新实现触发器.但是您也可能会忘记手动解决差距的任何事情.

And yes, you might forget to re-implement the trigger if you drop / re-create the numbers table or implement it somewhere else. But you might also forget anything you might manually do to deal with gaps anyway.

(3)如果您愿意随时获取数字，则可以完全避免使用数字表.为此，我使用sys.all_columns和sys.all_objects之类的目录视图，具体取决于我需要多少个数字:

(3) You can avoid a numbers table altogether if you're willing to derive numbers on the fly. I use catalog views like sys.all_columns and sys.all_objects for this, depending on how many numbers I need:

;WITH n AS (SELECT TOP (10000) n FROM 
  (SELECT n = ROW_NUMBER() OVER
    (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
  ) AS x ORDER BY n
)
SELECT n FROM n ORDER BY n; -- look ma, no gaps!

如果只需要100行，则可以只使用其中一个视图而无需交叉联接.如果需要更多，可以添加更多视图.不试图将您从数字表中移开，但这会绕过一些局限性，例如(a)在每个实例上建立数字表，以及(b)在哲学上反对这种事情的人(我在我的书中遇到过很多事业.)

If you only need 100 rows, you can just use one of the views without the cross join; if you need more, you can add more views. Not trying to push you away from a numbers table but this gets you around limitations such as (a) building a numbers table on every single instance and (b) people who are philosophically opposed to such a thing (I have come across many in my career).

顺便说一句，这确实应该在产品中.请在以下连接"项中投票并说明实际的业务用例:

As an aside, this really should be in the product. Please vote and state real business use cases in the following Connect item:

http://connect.microsoft.com/SQLServer/feedback/details/258733/add-a-built-in-in-table-of-numbers