O tratamento do formato JSON ou antipadrão (valores delimitados por vírgulas etc.) em bancos de dados relacionais introduz problemas de desempenho e manutenção

Se...

INSERT INTO TABLE_NAME
    SELECT
        STRING_AGG(COLUMN_NAME, ',')
    FROM
        TABLE_NAME

introduz um antipadrão (ou seja, pode causar baixo desempenho, resultados incorretos (por favor, verifique isso) e problemas de manutenção das consultas T-SQL) ;

Que tal isto:

INSERT INTO TABLE_NAME(COLUMN_NAME)
    SELECT
        N'{"KEY_VALUE": [' + TBN.STR_AGG + '"]}' JSON_FORMAT_VALUE
    FROM
        (SELECT
             STRING_AGG('"' + COLUMN_NAME, + '", ') STR_AGG
         FROM
             TABLE_NAME) TBN

Esta é minha consulta de exemplo de teste real:

DECLARE @users TABLE(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    username NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) NOT NULL,
    status VARCHAR(50)
);

DECLARE @features TABLE(
    id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(200) NOT NULL,
    is_enabled BIT
);

DECLARE @feature_user TABLE(
    user_id INT NOT NULL,
    feature_id INT NOT NULL
);

INSERT INTO @users(
    username,
    email,
    status
)
VALUES(
    N'john_doe',
    N'[email protected]',
    'active'
),
(
    N'mark_daniels',
    N'[email protected]',
    'inactive'
),
(
    N'alice_jane',
    N'[email protected]',
    'active'
);

INSERT INTO @features(
    name,
    description,
    is_enabled
)
VALUES(
    'notifications',
    'Send notifications to users',
    'TRUE'
),
(
    'csv export',
    'Export data to CSV format',
    'FALSE'
),
(
    'redesign landing page',
    'Revamp the landing page layout',
    'TRUE'
);

INSERT INTO @feature_user
VALUES(
    1,
    1
),
(
    1,
    2
),
(
    1,
    3
),
(
    2,
    1
),
(
    2,
    2
),
(
    3,
    3
)

-- Produces comma-delimited data structure
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.status,
    STRING_AGG(f.name, ', ') AS feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status;


-- Produces denormalized data structure
SELECT  
    feature_data.user_id,
    feature_data.username,
    feature_data.email,
    feature_data.status,
    N'{"feature_data": [' + feature_data.feature_names + '"]}' feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.status,
    STRING_AGG('"' + f.name, + '", ') feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;

-- The below query to check if the JSON data is valid
SELECT  
    ISJSON(N'{"feature_data": [' + feature_data.feature_names + '"]}') feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT 
    STRING_AGG('"' + f.name, + '", ') feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;

E as SELECTsaídas da consulta são:

Créditos ao dono da ideia: Como evitar linhas redundantes ao unir tabelas em SQL?

Entretanto, esse cenário de caso reflete a configuração do meu banco de dados.

Explicações: O JSON dataserá decodificado para uso posterior no banco de dados SQL Server com um compatibility level of 130 or greater, e estou usando compatibility level 140ou SQL Server 2017.

A aplicação dessa ideia quanto a se relacionar com o exemplo dado é ao concatenar dados dentro de uma única propriedade (por exemplo, conjunto de colunas específico) . Qual dos exemplos dados oferece muito performance efficientem termos de consideração do constantly growing database.

Além disso, alguém pode fornecer um conjunto de consultas para evitar antipadrões ?

Quero me aprofundar no esquema antipadrão para realmente entender como ele afeta as consultas.

ATUALIZAR:

DECLARE @compile_table_str_agg TABLE( --denormalizing data through comma-delimited data compilation
    user_id INT NOT NULL,
    username NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) NOT NULL,
    status VARCHAR(50),
    feature_names VARCHAR(100) NOT NULL
);

DECLARE @compile_table_json TABLE( --denormalizing data through json-structure data compilation
    user_id INT NOT NULL,
    username NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) NOT NULL,
    status VARCHAR(50),
    feature_names VARCHAR(100) NOT NULL
);

INSERT INTO @compile_table_str_agg
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.status,
    STRING_AGG(f.name, ', ') AS feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status;

INSERT INTO @compile_table_json
SELECT  
    feature_data.user_id,
    feature_data.username,
    feature_data.email,
    feature_data.status,
    N'{"feature_data": [' + feature_data.feature_names + '"]}' feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM(
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.status,
    STRING_AGG('"' + f.name, + '", ') feature_names
    --ADD HERE THE RECORDS THAT YOU DON'T WANT TO BE DUPLICATED ROWS, WHERE YOU NEED TO USE THE SAME APPROACH WITH feature_names
FROM @users u
LEFT JOIN @feature_user fu ON fu.user_id = u.id
LEFT JOIN @features f ON fu.feature_id = f.id
GROUP BY u.id, u.username, u.email, u.status) feature_data;

-- Retrieving the compiled STRING_AGG data
SELECT
    user_id,
    username,
    email,
    status,
    ca.*
FROM
    @compile_table_str_agg str_agg1
CROSS APPLY(
    SELECT
        com_delimited.*
    FROM
        STRING_SPLIT(
            feature_names, ',') com_delimited
) ca;

-- Retrieving the compiled JSON structure data
SELECT
    user_id,
    username,
    email,
    status,
    sa.ft_values
FROM
    @compile_table_json json1
CROSS APPLY
    OPENJSON(json1.feature_names) WITH(
            feature_data NVARCHAR(MAX) '$.feature_data' AS JSON
        ) ca
        CROSS APPLY OPENJSON(ca.feature_data) WITH (ft_values NVARCHAR(25) '$') sa;

E as UPDATE, SELECTsaídas da consulta são:

CONCLUSÃO: Ambos oferecem a mesma saída, mas qual oferece mais desempenho ou eficiência de manutenção, mesmo quando o banco de dados fica maior?

REFERÊNCIA DE CONSULTA JSON:

DECLARE @json NVARCHAR(MAX);

SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[0].town', 'Philippines');

SELECT modifiedJson = @json;

DECLARE @json2 NVARCHAR(MAX);

SET @json2 = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json2) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
);

DECLARE @json3 NVARCHAR(MAX);

SET @json3 = N'[
  {"id": 3, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"},
  {"id": 1, "info": {"name": "DevQt", "surname": "PH", "skills": ["Dart", "Java", "C#", "VB", "Javascript", "SQL"]}, "age": 26, "dob": "2005-11-04T12:00:00"}
]';

SELECT id,
    firstName,
    lastName,
    age,
    dateOfBirth,
    skill
FROM OPENJSON(@json3) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR(MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(15) '$')
ORDER BY id;

DECLARE @jsonVariable NVARCHAR(MAX);

SET @jsonVariable = N'[
  {
    "Order": {
      "Number":"SO43659",
      "Date":"2011-05-31T00:00:00"
    },
    "AccountNumber":"AW29825",
    "Item": {
      "Price":2024.9940,
      "Quantity":1
    }
  },
  {
    "Order": {
      "Number":"SO43661",
      "Date":"2011-06-01T00:00:00"
    },
    "AccountNumber":"AW73565",
    "Item": {
      "Price":2024.9940,
      "Quantity":3
    }
  }
]';

-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;

E as JSONsaídas da consulta são:

Explicações adicionais: Incluí a referência no tratamento de JSON para que os outros vejam minha percepção de incorporar JSON com bancos de dados relacionais no SQL Server.

Referência original: Dados JSON no SQL Server