T-SQL: Ricerca di Valori Assenti in Sequenza Numerica (it-IT)
Purposes
The purpose of this short article is to show a method to locate any holes in a SQL sequence within a SQL Server table to quickly identify lack of consequence within a flow. This approach is useful when there is a need, for example, to verify that there are no missing documents in a given list (think about having to track a flow of invoices, and have to check through the registration number of the document, if some of them are missing in a given range).
Basic concept
The script is based on a simple concept: if we could have an archive that contains sequentially any number of possible cases for handling our case, we could compare our actual list (where potentially some numbers will not be present) with that ideal, that contains them all, and then check for the deficiencies.
Firstly, you need to create the ideal list, or the one that contains any number of interest. Suppose the values with which we will work can occupy the range between 1 and 99999. The table can be created as follows:
CREATE TABLE #storeNum( number INT , PRIMARY KEY( number))
DECLARE @index INT
SET @index = 1
WHILE @index <= 99999
BEGIN
INSERT INTO #storeNum (number) VALUES ( @index)
SET @index = @index + 1
END
First, a memory table with a single field is predisposed, which is also a primary key, which will contain for each record our progression of values. Then, by means of a loop, we will increment this variable, and at each increment we will perform an insertion operation in the given structure. At the end of this process, our #storeNum table will contain 99999 records, each of which is valued with a number between 1 and 99999.
use
Let's now have a data store to use for comparison. We will create a table named Products, with two fields: the first is a hypothetical unique product ID, while the second is a description of the product itself. In a table of this kind we will enter some test records:
CREATE TABLE Products( ProductID INT PRIMARY KEY, ProductDes VARCHAR (50))
INSERT INTO Products VALUES(1 , 'Product 1' )
INSERT INTO Products VALUES(2 , 'Product 2' )
INSERT INTO Products VALUES(3 , 'Product 3' )
INSERT INTO Products VALUES(4 , 'Product 4' )
INSERT INTO Products VALUES(5 , 'Product 5' )
INSERT INTO Products VALUES(10 , 'Product 10' )
We have 5 consequential records, while the sixth has an ID of 10. By following the above logic, in the event of analyzing our list of products to hunt for any numbering defects, the result we would expect to achieve will be the records having IDs from 6 to 9, absent from the Products table but present in #storeNum. Let's see how to do it:
SELECT number
FROM #storeNum
WHERE number NOT IN (SELECT ProductID FROM Products) AND
number <= (SELECT MAX(ProductID) FROM Products )
This simple SELECT contains two subqueries nested in the selection clause. What we are going to do will be to extract from the #storeNum table those values that are not contained (NOT IN) in the selection of all ID values of the Products table (SELECT ProductID FROM Products). If we stopped here, we would get the values 6, 7, 8, 9 but also all those between 11 and 99999, as they are absent in the Products table but not in #storeNum. As a top limit, we can then set the highest value in the Products table: since the highest absolute ID is 10, we will probably not be interested in investigating what values beyond this limit are absent, because it will be obvious that everyone will satisfy the condition, and we will have many records beyond the actual field of analysis. Then,
By running the script (with sample data) in Management Studio, we will get this result.
Full script
-- Creazione tabella temporanea #storeNum e suo popolamento con i valori tra 1 e 99999
CREATE TABLE #storeNum( number INT , PRIMARY KEY( number))
DECLARE @index INT
SET @index = 1
WHILE @index <= 99999
BEGIN
INSERT INTO #storeNum (number) VALUES ( @index)
SET @index = @index + 1
END
-- Creazione tabella Products e suo popolamento
CREATE TABLE Products( ProductID INT PRIMARY KEY, ProductDes VARCHAR (50))
INSERT INTO Products VALUES(1 , 'Product 1' )
INSERT INTO Products VALUES(2 , 'Product 2' )
INSERT INTO Products VALUES(3 , 'Product 3' )
INSERT INTO Products VALUES(4 , 'Product 4' )
INSERT INTO Products VALUES(5 , 'Product 5' )
INSERT INTO Products VALUES(10 , 'Product 10' )
-- Estrazione buchi nella sequenza dei prodotti
SELECT *
FROM #storeNum
WHERE number NOT IN (SELECT ProductID FROM Products) AND
number <= (SELECT MAX( ProductID) FROM Products )
-- Eliminazione tabelle di esempio
DROP TABLE #storeNum
DROP TABLE Products