-1

We have a table with ~6 million of products:

ID Name Code
1 One 123.456.789-M
2 Two 852.789456
3 Three 1-123654.P

What is a good way (with regards to performance) to filter products by code column without masks (traces, slashes or dots)?

Example:

SELECT id, name FROM products WHERE code = '123456789M' OR code = '1123654P';
0
5

Use an indexed computed column:

CREATE TABLE dbo.Products
(
    ID integer NOT NULL UNIQUE CLUSTERED,
    [Name] varchar(10) NOT NULL PRIMARY KEY NONCLUSTERED,
    Code varchar(20) NOT NULL,
    SearchCode AS 
        ISNULL(
            CONVERT(varchar(20), 
                REPLACE(
                    REPLACE(Code, '-', ''), 
                    '.', '')
                ), 
            ''),
    
    INDEX [IX dbo.Products SearchCode (Name)] 
        (SearchCode) 
        INCLUDE ([Name])
);

INSERT dbo.Products
    (ID, [Name], Code)
VALUES
    (1, 'One', '123.456.789-M'),
    (2, 'Two', '852.789456'),
    (3, 'Three', '1-123654.P');
SELECT P.ID, P.[Name]
FROM dbo.Products AS P
WHERE P.SearchCode = '123456789M';
ID Name
1 One

plan

db<>fiddle online demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.