我尽力让我的问题尽可能简单明了。所以我首先引用这个回答帖子说，
如果要从 Windows 读取和写入 FILESTREAM 数据，请单击启用 FILESTREAM 进行文件 I/O 流访问。
在我的计算机上，我已经成功使用 SQL SERVER 设置了 FILESTREAM，并且已经将其表中的数据处理到 API，但我想知道“启用 FILESTREAM 进行文件 I/O 流访问”在性能优化方面能起到什么作用。
鉴于以下设置：
这里的主要问题是，这是否会在请求点和文件位置之间引入不同的访问和数据检索？
我的意思是，如果我可以在没有这些设置的情况下管理 SQL Server 中的 FILESTREAM 数据，那么当请求相应文件数据时，此配置是否提供对负责访问的数据的相同控制？
我还没有看到它是否有用，但我认为它在我的用例中具有潜在的用处...但是，我的主要目标是在不牺牲文件数据和应用程序目的地（API 或其他东西）之间的精细控制和可管理性的情况下优化性能。
如果...
INSERT INTO TABLE_NAME
SELECT
STRING_AGG(COLUMN_NAME, ',')
FROM
TABLE_NAME
引入了反模式 （即可能导致性能不佳、结果不正确（请验证这一点）以及 T-SQL 查询的可维护性问题）；
这个怎么样：
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
这是我的实际测试示例查询：
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;
查询SELECT输出如下：
感谢想法的所有者：在 SQL 中连接表时如何避免冗余行？
但是，这种情况反映了我的数据库设置。
解释：将JSON data被解码以供稍后在 SQL Server 数据库中使用compatibility level of 130 or greater，并且我正在使用compatibility level 140或SQL Server 2017。
与给定示例相关的此想法的应用是在连接单个属性（例如，特定列集）内的数据时。给定示例中的哪一个performance efficient在考虑方面提供了很多帮助constantly growing database。
除此之外，有人可以提供一组查询以避免反模式吗？
我想深入研究反模式方案，以便真正理解它是如何真正影响查询的。
更新：
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;
查询UPDATE, SELECT输出如下：
结论：两者提供相同的输出，但是即使数据库变得更大，哪一个能提供更高的性能或可维护性效率？
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;
查询JSON输出如下：
附加说明：我已经包含了处理 JSON 的参考，以便其他人了解我对在 SQL Server 中将 JSON 与关系数据库嵌入的看法。
原文参考： SQL Server中的JSON数据