Matching column values ​​as prefixes

Question

I store string prefixes in my SQL Server table, I want to find out if any of these values ​​is a valid prefix for a given parameter value.

eg. Suppose I have a list of phone calls without calls, and it includes an entry prohibiting all phone calls to numbers starting with " 1425123", instead of inserting 10,000 numbers ( 14251230000in 14251239999), instead it saves the prefix.

Same:

CREATE TABLE Prefixes (
    Value varchar(10)
)

CREATE INDEX IX_Value UNIQUE Prefixes ( Value )

Evaluated as follows:

DECLARE @value varchar(10) = 'foobar'

SELECT
    *
FROM
    Prefixes
WHERE
    @value LIKE ( Value + '%' );

Azure SQL SQL Server Management Studio, , . 70 000 Azure SQL S1 200 500 . .

(Value = @value) .

200-500 .

- Trie ( ), - :

DECLARE @v1 varchar(1) = LEFT( @value, 1 )
DECLARE @v2 varchar(2) = LEFT( @value, 2 )
DECLARE @v3 varchar(3) = LEFT( @value, 3 )
DECLARE @v4 varchar(4) = LEFT( @value, 4 )
DECLARE @v5 varchar(5) = LEFT( @value, 5 )
DECLARE @v6 varchar(6) = LEFT( @value, 6 )
DECLARE @v7 varchar(7) = LEFT( @value, 7 )
DECLARE @v8 varchar(8) = LEFT( @value, 8 )
DECLARE @v9 varchar(9) = LEFT( @value, 9 )

SELECT
    *
FROM
    Prefixes
WHERE
    Value = @v1 OR
    Value = @v2 OR
    Value = @v3 OR
    Value = @v4 OR
    Value = @v5 OR
    Value = @v6 OR
    Value = @v7 OR
    Value = @v8 OR
    Value = @v9

, ( Index Seek), , , 10 , ... .

? , SQL Server , (.. , SQL)?

Answer 1

.

1-10, , , .

, V , .

SELECT IIF(EXISTS (SELECT *
                   FROM   (VALUES(1),(2),(3),
                                 (4),(5),(6),
                                 (7),(8),(9),(10)
                          ) V(number)
                          JOIN Prefixes P WITH(FORCESEEK)
                            ON P.Value = LEFT(@value, number)
                   WHERE  number <= LEN(@value)), 1, 0) AS PrefixExists 

  |--Compute Scalar(DEFINE:([Expr1014]=CASE WHEN [Expr1015] THEN (1) ELSE (0) END))
       |--Nested Loops(Left Semi Join, DEFINE:([Expr1015] = [PROBE VALUE]))
            |--Constant Scan
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Union1010]))
                 |--Filter(WHERE:([Union1010]<=len([@value])))
                 |    |--Constant Scan(VALUES:(((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10))))
                 |--Index Seek(OBJECT:([tempdb].[dbo].[Prefixes].[IX_Value] AS [P]), SEEK:([P].[Value]=substring([@value],(1),[Union1010])) ORDERED FORWARD)

Answer 2

, , , sargable, Prefixes.Value where.

.

( 10).

, , , EXISTS? , ; . , IN .

..

IF EXISTS (
    SELECT  *
    FROM    Prefixes
    WHERE   Value IN (@v1, @v2, ...)
)
    RETURN 1
ELSE
    RETURN 0

---

PS , Full Text Indexing. ( , , .) , , . ; , .

---

, , , :

• @Value = '9999999999' .
• , Prefixes.Value < '9999999999'.
• @value like value + '%'.
• , .

, ( ) , first value < @value, , @value like value + '%'. , Prefixes "" (, , ).

Value, , .

.

SELECT  *
FROM    (
        SELECT  TOP 1 Value as PossiblePrefix
        FROM    Prefixes
                /* WHERE can leverage index;
                   but requires NO redundant Prefixes.Value rows
                   so that it returns only ONE possible prefix that
                   has a chance of matching @Value.*/
        WHERE   Value <= @Value
        ORDER BY Value DESC
        ) pp
WHERE   @Value LIKE pp.PosisblePrefix + '%'

, , .