什么是Tally Table:
首先，Tally Table 只是一个概念，只是普普通通一个帮你生成 sequence number?的方法
原理：
通过利用CTE，已经自连接产生的笛卡儿积造出多行单列数据，最后使用ROW_NUMBER窗口函数为每一行编号。得到一个有序非重复序列。
美妙的事情就发生在如何去利用这个有序非重复的序列。5 级的CTE嵌套可以产生40亿行的序列。但当你只需要去取这个5级CTE前N行的时候，优化器会意识到你只要前N行，那么查询处理到第N行就会停止了。所以不会有性能浪费。
使用这些sequence number有什么用呢？
1.要生成一张时间表
具体实现：SQL Server版本
IF OBJECT_ID(‘dbo.GetNums’, ‘IF’) IS NOT NULL
DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
LO AS (SELECT c FROM (VALUES(l),(1)) AS D(c)),
Ll AS (SELECT 1 AS c FROM LO AS A CROSS JOIN LO AS B),
L2 AS (SELECT 1 AS c FROM Ll AS A CROSS JOIN Ll AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
LS AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM LS)
—-SELECT TOP(@high – @low + 1) @low + rownum – 1 AS n
SELECT @low + rownum – 1 AS n
FROM Nums
ORDER BY rownum
OFFSET 0 ROWS FETCH FIRST @high – @low + 1 ROWS ONLY;
GO—-利用序列生成时间序列
DECLARE
@start AS DATETIME2 = ‘2012-02-12 00:00:00.0000000’,
@end AS DATETIME2 = ‘2012-02-18 12:00:00.0000000’;
SELECT DATEADD(hour, n*12, @start) AS dt
FROM dbo .GetNums(O, DATEDIFF(hour , @start, @end)/12) AS Nums
利用DATEADD()函数以及我们的序列表，我们就可以轻松的生成一个每间隔12小时的datetime序列。
注意：SQLServer中你可以使用TOP， 2012版本开始，你也可以使用Fetch/offest。
2.自增列序号间断的排查
假设你有一个表，序号是从 10-9999。因为，插入删除或者提交回滚导致序号有间断。你要找出间断的值有哪些。
你就可以利用Tally table，同样获取一个10-999的序列，然后做JOIN（用哪种JOIN自己思考）。然后就可以轻松找出间断值了。
3.直接为某一个表生成一个唯一值的序列键
