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 SELECT
saí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 data
será decodificado para uso posterior no banco de dados SQL Server com um compatibility level of 130 or greater
, e estou usando compatibility level 140
ou 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 efficient
em 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, SELECT
saí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 JSON
saí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
Você notou que suas consultas estão cheias de
group by
construções?Isso é um potencial impacto no desempenho, ter que processar todos os registros relevantes e então "remover" as duplicatas. É melhor não armazenar duplicatas em primeiro lugar.
Além disso, parece-me que seu banco de dados está tendo que fazer uma quantidade potencialmente significativa de trabalho apenas para colocar os dados de volta na "forma" em que deveriam estar para começar. Isso é um impacto no desempenho, bem ali.
Acho que você pode ter perdido que sua primeira consulta 'atualizada' retorna valores incorretos. As linhas 2, 3 e 5 parecem ter um espaço inicial ! (Considere isso "gentilmente verificado".)
E se você precisar consultar esses dados para um recurso específico , pode esquecer qualquer índice. Seu banco de dados terá que fazer o Table Scan.
Afinal, como você determinaria se um dado valor aparece em um campo agregado?
Dado:
... funcionaria , mas ...
... não faria !
(Você não pode usar
like '%notifications%'
porque alguém [outra pessoa] pode adicionar outro recurso que inclua esta palavra (digamos, "notificações por e-mail") mais tarde!)Quanto ao desempenho geral , não podemos dizer.
Teste seu cenário e veja como ele funciona, mas suspeito que ele começará a ficar significativamente mais lento conforme seus dados se expandem.