Having the products table with the sales_country string field with the values like CZ;SK;HU;PL;DE;SI;GR, and the application user account settings (table users) with the same-kind field...

What is a reasonably efficient method to SELECT the allowed products for the user?

I was thinking about creating the product_sales_country_map table and user_sales_country_map with the product_code and country_code fields -- the later being the single country code split from the token field. The products then would be selected using JOIN of the four tables and GROUPing by the product code (or numbering the rows for the same product and selecting only one -- something like that). There is about 10 000 products and about 200 users. The products table is periodically refreshed every 30 minutes; so, there is a time when the mapping table for products could be rebuilt. The user account is updated manually, occasionally. So, there also is the time instant when the mapping table for the user could be rebuilt.

The resulting data set is to be the source for building web UI. So, I am not hunting microseconds; anyway...

Is there any simpler, still reasonably efficient SQL-language approach to get the subset of the allowed products?

Having the products table with the sales_country string field with the values like CZ;SK;HU;PL;DE;SI;GR, and the application user account settings (table users) with the same-kind field...

What is a reasonably efficient method to SELECT the allowed products for the user?

I was thinking about creating the product_sales_country_map table and user_sales_country_map with the product_code and country_code fields -- the later being the single country code split from the token field. The products then would be selected using JOIN of the four tables and GROUPing by the product code (or numbering the rows for the same product and selecting only one -- something like that). There is about 10 000 products and about 200 users. The products table is periodically refreshed every 30 minutes; so, there is a time when the mapping table for products could be rebuilt. The user account is updated manually, occasionally. So, there also is the time instant when the mapping table for the user could be rebuilt.

The resulting data set is to be the source for building web UI. So, I am not hunting microseconds; anyway...

Is there any simpler, still reasonably efficient SQL-language approach to get the subset of the allowed products?

• sql
• sql-server

• 1 Does this help? stackoverflow/questions/15280956/… – Bart McEndree Commented Mar 18 at 13:58
• 4 "What is a reasonably efficient method to SELECT the allowed products for the user?" Normalising your design would be the most efficient. Don't store delimited data, it's not (easily) indexable or searchable. – Thom A Commented Mar 18 at 13:58
• Your approach is fine. Make sure you index the country codes in both tables. Also, wrap your product table updates plus your truncate/rebuild of product-country map inside a transaction so there is not an awkward moment where a SQL request finds them out of sync. – Chris Maurer Commented Mar 18 at 14:42
• 1 While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. – Yitzhak Khabinsky Commented Mar 18 at 14:46
• 1 If you can't change your design, then you really need to denote that, @pepr , especially when said design breaks basic normalisation rules. Fixing the design will lead to the most efficient solutions. Delimited data doesn't lead to that, as (like I mentioned) it's not as easily indexed and searchable. You can't, for example, use an index to find products sold in Germany, as WHERE Sales_country = 'DE' wouldn't work, and WHERE ';' + Sales_country + ';' LIKE '%;DE;%' isnt' SARGable. – Thom A Commented Mar 18 at 15:00

1 Answer 1

Like I mentioned in the comments, the best suggestion would be to normalise your data; the design is the problem. You state you can't, however, I recommend you feed back to whoever designed this de-normalised design to fix it.

You could, however, get around this a little an create a view that normalises your data, and then INDEX that. Unfortunately, we can't use STRING_SPLIT as we'll need APPLY for that, and you can't use APPLY in an indexed view. We can, however, use a JOIN with a LIKE.

With some made up, overly simplified, data (as we lack sample data), your TABLEs, VIEW, INDEXes may look something like this. Note, as well, that this does not enforce referential integrity; if you INSERTed the value 'PQ,RS' into dbo.DenormalisedTable then the row would simple not appear in the VIEW.

CREATE TABLE dbo.NormalisedValues (ValueID int IDENTITY(1,1) CONSTRAINT PK_NormalisedValues PRIMARY KEY,
                                   ValueValue varchar(2) NOT NULL);
GO
CREATE TABLE dbo.DenormalisedTable (SomeID int IDENTITY CONSTRAINT PK_DenormalisedTable PRIMARY KEY,
                                    SomeDate date NOT NULL CONSTRAINT DF_DenormalisedTable_SomeDate DEFAULT SYSDATETIME(),
                                    DelimitedValue varchar(100) NOT NULL); --No FK for you, you're denormalised
GO

INSERT INTO dbo.NormalisedValues (ValueValue)
VALUES('AB'),('DE'),('FG'),('PQ'),('YZ');
GO
INSERT INTO dbo.DenormalisedTable (DelimitedValue)
VALUES('AB,YZ'),
      ('DE,FG,PQ'),
      ('YZ,AB'),
      ('YZ,DE,FG,AB,PQ')
GO
INSERT INTO dbo.DenormalisedTable (DelimitedValue)
VALUES('PQ,RS'); --Will insert, won't appear in the VIEW
GO

CREATE VIEW dbo.NormalisedView 
WITH SCHEMABINDING AS
    SELECT DT.SomeID,
           DT.SomeDate,
           NV.ValueValue AS NormalisedValue
    FROM dbo.NormalisedValues NV
         JOIN dbo.DenormalisedTable DT ON ',' + DT.DelimitedValue + ',' LIKE '%,' + NV.ValueValue + ',%';
GO
CREATE UNIQUE CLUSTERED INDEX CUI_NormalisedView ON dbo.NormalisedView (SomeID,NormalisedValue);
GO
CREATE INDEX IX_NormalisedView_NormalisedValue ON dbo.NormalisedView (NormalisedValue) INCLUDE (SomeID, SomeDate)
GO
SELECT *
FROM dbo.NormalisedView NV
WHERE NV.NormalisedValue = 'DE';
GO
SELECT *
FROM dbo.NormalisedView NV
WHERE NV.NormalisedValue = 'PR';
GO
SELECT *
FROM dbo.NormalisedView NV
WHERE NV.SomeID = 4;
GO
--Clean up
DROP VIEW dbo.NormalisedView;
DROP TABLE dbo.DenormalisedTable;
DROP TABLE dbo.NormalisedValues;

db<>fiddle