tsql创建表
问题介绍 (Introducing the Problem)
Gaps existence in automatic sequenced columns occurs all the time. Missing identity values (or other sequencing values) occur for a variety of Reasons.
自动排序的列中始终存在空白。 出于各种原因会导致缺少标识值（或其他排序值）。
The most common reasons include: roll backed transactions, failed inserts and Deletes, large row deletes after delete commands that occur after many inserts to a sequenced table and so forth.
最常见的原因包括：回滚事务，插入和删除失败，删除命令后的大行删除，这些命令是在对序列表的多次插入之后发生的，等等。
The sequence gaps appear in all kinds of sequences, IDENTITY, generic integer Columns that act as a sequenced primary key of a table and also dedicated sequence Objects.
序列间隙出现在各种序列，IDENTITY，用作表的已排序主键的通用整数列以及专用序列对象中。
In all of these cases, the sequenced number does not contain any duplicate values.
在所有这些情况下，序列号不包含任何重复值。
The sequence gaps scenarios problem can cause some of business questions since the values are not sequenced properly and values are missing for no apparent Reason.
序列缺口方案问题可能会引起一些业务问题，因为这些值没有正确排序，并且没有明显的原因导致值丢失。
In order to reduce gaps in sequences, I have constructed a stored procedure that analyze the gaps in a given table and sequence column.
为了减少序列中的缺口，我构建了一个存储过程，该过程分析给定表和序列列中的缺口。
The procedure gives back a new sequence number from one of four selected Methods (strategies):
该过程从四个选定的方法（策略）之一中返回新的序列号：
1 – (The first fit gap strategy) – The first number that fit in the first gap
1 –（第一个适合差距策略）–第一个适合第一个差距的数字
2 – (The last fit gap strategy) – The first number that fit in the last gap
2 –（最后适合差距策略）–最适合最后一个差距的数字
3 – (The Largest fit gap strategy) – The first number that fit in the largest gap.
3 –（最大适应缺口策略）–第一个适合最大缺口的数字。
4 – (The smallest fit gap Strategy) – The first number that fit in the smallest gap.
4 –（最小适合差距策略）–适合最小差距的第一个数字。
优势与劣势 (Advantage and weakness)
The advantage of this procedure is clear, it saves space. Rather than using a big integer that is 8 bytes for a large sequence column that advances all the time, we keep a shorter int column (only 4 bytes) and adjust the sequence value from time to time.
此过程的优点很明显，它节省了空间。 我们不使用一直在前进的较大序列列的8个大整数，而是保留较短的int列（仅4个字节）并不时调整序列值。
This scenario is useful when there are many deletes and not only inserts to the table.
当存在许多删除操作，而不仅仅是插入到表中时，此方案很有用。
The weakness of this method is clearly performance, executing the procedure takes time, and the execution time gets larger when the table is larger. Using an indexed (usually clustered index on the sequenced column), helps in this case.
该方法的弱点显然是性能，执行过程需要时间，并且当表较大时执行时间也会变长。 在这种情况下，使用索引（通常在序列列上聚集索引）会有所帮助。
解决方案如何运作？ (How does the solution work?)
I have created a stored procedure calledGetNextSequencethat acts as an advisor to the next value the sequence should have. The stored procedure uses a dynamic TSQL SELECT statement that is constructed inside the stored procedure. The statement uses the LEAD window function.
我创建了一个名为GetNextSequence的存储过程，该过程充当该序列应具有的下一个值的顾问。 存储过程使用在存储过程内部构造的动态TSQL SELECT语句。 该语句使用LEAD窗口函数。
Starting from the SQL SQL Server edition, The LAG and LEAD window functions have been introduced as functions for accessing the prior or subsequent rows along with the current one.
从SQL SQL Server版本开始，已引入LAG和LEAD窗口函数作为用于访问前一行或后一行以及当前行的函数。
If the table that is given as the parameter, has gaps in the given column, the table is then queried, comparing the Values that exist in the current row compared to the value in the next row.
如果作为参数给出的表在给定的列中有空格，则将查询该表，将当前行中存在的值与下一行中的值进行比较。
If the difference between the two values is greater than 1, then a gap exists, and therefore, will be returned in the result set.
如果两个值之间的差大于1，则存在间隙，因此将在结果集中返回。
If there are no gaps in the table (that means that there are no adjacent values with difference greater than 1, no rows are returned from the query and the procedure returns -1)
如果表中没有间隙（这意味着不存在相差大于1的相邻值，则不会从查询中返回任何行，并且过程将返回-1）
The assumption of the procedure is that the sequence column is a positive integer (n integer and > 0) then a positive integer value is returned.
该过程的假设是序列列为正整数（n整数且> 0），然后返回正整数值。
The Procedure gets three parameters and outputs one value.
该过程获取三个参数并输出一个值。
输入参数 (The input parameters)
The Table name that has gaps in the sequenced column (i.e @table parameter)
在顺序列中有空格的表名（即@table参数）
The name of the sequence column (i.e @col parameter)
序列列的名称（即@col参数）
The method, as a string that has four valid codes (@i.e. the @method parameter)
该方法，作为具有四个有效代码的字符串（@即@method参数）
The codes are:
这些代码是：
FIRST_FIT – The very first , minimal value for a sequence that fit in the first found gapFIRST_FIT –符合第一个发现的缺口的序列的第一个最小值 LAST_FIT – The first value of the sequence that fits in the last gap foundLAST_FIT –序列的第一个值，适合找到的最后一个缺口 LARGEST_GAP – The first value of the sequence that fit in the largest gap foundLARGEST_GAP –符合找到的最大缺口的序列的第一个值 SMALLEST_GAP – The first value of the sequence that fit in the smallest gap foundSMALLEST_GAP –符合找到的最小缺口的序列的第一个值
An Output parameter (The @sequence parameter):
输出参数（@sequence参数）：
If a sequence is found according to any method then a positive Integer value is returned according to the method selected如果根据任何方法找到序列，则根据所选方法返回正整数值sequence) + 1序列）+1
Note:
注意：
Note that it is up to the DBA that executes the procedure, to reseed the identity column on to alter the sequence back to the value suggested by the procedure.
请注意，由DBA执行该过程，重新设置Identity列以将序列更改回该过程建议的值。
程序TSQL代码 (The Procedure TSQL Code)
CREATE PROC GetNextSequence (@table VARCHAR(40),@col VARCHAR(30),@method VARCHAR(20),@seq BIGINT OUTPUT)ASBEGINSET nocount onDECLARE @dyntsql VARCHAR(1000)CREATE TABLE ##Sequences (start_seq BIGINT,end_seq BIGINT)SET @dyntsql = 'SELECT t.CurrRow + 1 AS [StartGap],t.NextRow - 1 AS [EndGap] FROM ( SELECT 'SET @dyntsql += @col + ' AS CurrRow,LEAD(' + @col + ', 1, NULL) OVER (ORDER BY ' + @col + ') AS NextRow FROM 'SET @dyntsql += @table + ' ) as t WHERE t.NextRow - t.CurrRow > 1'INSERT ##SequencesEXEC (@dyntsql)IF (@method = 'FIRST_FIT')BEGINSELECT @seq = start_seqFROM ##SequencesWHERE start_seq = (SELECT min(start_seq)FROM ##Sequences)ENDIF (@method = 'LAST_FIT')BEGINSELECT @seq = start_seqFROM ##SequencesWHERE start_seq = (SELECT max(start_seq)FROM ##Sequences)ENDIF (@method = 'SMALLEST_GAP')BEGINSELECT @seq = start_seqFROM ##SequencesWHERE end_seq - start_seq = (SELECT min(end_seq - start_seq)FROM ##Sequences)ENDIF (@method = 'LARGEST_GAP')BEGINSELECT @seq = start_seqFROM ##SequencesWHERE end_seq - start_seq = (SELECT max(end_seq - start_seq)FROM ##Sequences)ENDIF (@seq IS NULL)SET @seq = - 1DROP TABLE ##SequencesENDGO
Some Explanations for the code:
该代码的一些说明：
algorithmicformat :算法格式构造动态TSQL语句：
SELECT t.currRow + 1 as [Start Value In Gap], t.nextRow – 1as [Last Value in Gap]From (SELECT column as currRow , LEAD (column, 1 , NULL) OVER ( ORDER BY column)From table) as tWhere nextRow – currRow > 1
In The dynamic TSQL execution, thetableandcolumnare replaced by the corresponding parameters of the procedure.
在动态TSQL执行中，将表和列替换为过程的相应参数。
The result of this dynamic TSQL statement is entered into a temporary, global table called ##sequences.此动态TSQL语句的结果将输入一个临时的全局表## sequences。 This temporary table is then queried, according to the method parameter.然后根据方法参数查询该临时表。 If method is first-fit then the minimal value of the starting gap sequence is returned如果方法是首次拟合，则返回起始间隙序列的最小值 If method is last-fit then the maximal value of the starting gap sequence is returned 如果方法为最后拟合，则返回起始间隙序列的最大值 If method is smallest-gap then the value of the starting gap sequence 如果方法是最小间隙，则起始间隙序列的值 Where the Gap is the smallest is returned. 返回差距最小的地方。 If method is largest-gap then the value of the starting gap sequence 如果方法是最大间隙，则起始间隙序列的值 Where the Gap is the largest is returned 差距最大的地方返回
Here is an example for procedure execution with some explanations:
这是一个过程执行的示例，并带有一些解释：
As you can see there are gaps in the table:
如您所见，表中存在空白：
CREATE TABLE testTB (col INT)GOINSERT INTO testTB (col)VALUES (10),(11),(12),(50),(51),(52),(53),(54),(55),(90),(91),(92),(400),(850),(851),(852),(1054);
Then, we execute the procedure and test it with all the methods available:
然后，我们执行该过程并使用所有可用方法对其进行测试：
DECLARE @sq bigintexec GetNextSequence 'testTB','col','FIRST_FIT',@sq OUTPUTprint @sqexec GetNextSequence 'testTB','col','LAST_FIT',@sq OUTPUTprint @sqexec GetNextSequence 'testTB','col','SMALLEST_GAP',@sq OUTPUTprint @sqexec GetNextSequence 'testTB','col','LARGEST_GAP',@sq OUTPUTprint @sq
We get the following output values, after this code execution:
执行此代码后，我们得到以下输出值：
13
853
56
401
13
853
56
401
Explanation for the above example:
以上示例的说明：
The first gap is between 13 and 49, so according to the first fit method 13 is returned第一个间隙在13到49之间，因此根据第一个拟合方法返回13 The last gap is between 853 and 1053, so according to the last fit method 853 is returned最后的间隔在853和1053之间，因此根据最后的拟合方法返回853 The smallest gap is between 56 and 89, so according to the smallest gap method 56 is returned.最小间隙在56和89之间，因此根据最小间隙方法返回56。 The largest gap is between 401 and 849, so according to the largest gap method 401 is returned.最大间隙在401和849之间，因此根据最大间隙返回401。
翻译自: /creating-a-gap-in-sequences-tsql-stored-procedure-advisor/
tsql创建表
