多语言数据库的排序代码重用

看看你到目前为止所拥有的，[utils].[OrderByLanguage]内联表值函数 (ITVF) 很好，但它仍然看起来是一个相关的子查询，其中对于其中的每一行，api_utils.GetTable(@LangID)它都会传入所有行以api_utils.GetTable(@LangID)对其进行排序（即ORDER BY子句。

在运行时应用排序规则可能会非常昂贵，因为它必须在那一刻为这些值生成排序键。为了获得最佳性能，创建索引将提前生成排序键，甚至将它们按正确的顺序排列。但是处理多个语言环境确实很棘手。根据字符串的大小、需要多少个区域设置/排序规则以及将超过多少行，为每个区域设置创建源列的副本可能需要大量额外的磁盘空间（和 I/O）未来 3 - 5 年。幸运的是，您可以创建非持久计算列作为那些副本（不占用任何空间）并索引那些（确实占用空间）。虽然如果有 10 个语言环境，这可能不可行，但至少有一个基本列NVARCHAR(200)和 100 万（或更多）行，对于您的情况，它应该可以正常工作。使用这种方法，动态部分将是选择要从中选择的列（可通过动态 SQL 或IF语句实现，具体取决于情况）。但正如您在以下示例中看到的（打开“包括实际执行计划”），两个过滤查询（最后 2 个查询）都在预期索引上获取索引搜索并返回预期结果：

您可以在 dbfiddle.uk 上看到现场演示。

SET NOCOUNT ON;
-- DROP TABLE #T;
CREATE TABLE #T
(
  [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [Col1] VARCHAR(10) COLLATE Latin1_General_100_CI_AS,
  [Col2] AS ([Col1] COLLATE Latin1_General_100_CS_AS)
);

CREATE INDEX [IX_#T_Col1] ON [#T] ([Col1]);
CREATE INDEX [IX_#T_Col2] ON [#T] ([Col2]);

INSERT INTO #T ([Col1]) VALUES ('a'), ('A');

SELECT * FROM #T; -- 2 rows

SELECT [ID] FROM #T WHERE [Col1] = 'A'; -- 2 rows

SELECT [ID] FROM #T WHERE [Col2] = 'A'; -- 1 row

但是，鉴于此代码api.GetTable可能不是最佳方法。如果您想保留当前结构（尽可能多），那么您可以执行以下操作：

1. 转换api.GetTable为存储过程
2. 使用OUTPUT参数，这样你就不需要处理结果集
3. @Sort应该是临时表，不是表变量
4. 使用除/之外所需#Sort的所有列创建临时表：、、和。[Name]titleIDcategory[system]

5. 在一系列IF语句中，添加[title]列，但使用正确的排序规则：

   IF (@LanguageID = 2)
   BEGIN
     ALTER TABLE #Sort ADD [title] NVARCHAR(2000) COLLATE Modern_Spanish_CI_AI
   END;
   
   IF (@LanguageID = 4)
   BEGIN
     ALTER TABLE #Sort ADD [title] NVARCHAR(2000) COLLATE French_CI_AI
   END;
   
   ...
   

6. FROM api_utils.GetTable(@LangID) t主@Result=查询更改为：FROM #Sort t
7. ORDER BY (SELECT s.RowNumber...主@Result=查询更改为：ORDER BY t.[title]

这将需要在每次执行时重新应用排序规则，但是：

1. 它不需要对基表进行任何更改
2. 它将重新使用返回的数据api.GetTable（没有双重调用）
3. 它将使用[Name]列上的统计信息
4. 它不会是一个相关的子查询（呜呼！）
5. [Name]它为您提供了在列被填充后创建索引的选项。

代码重用

通过切换到存储过程和临时表，我们实际上可以实现代码重用的目标。将排序列添加到本地临时表的代码可以抽象为另一个存储过程。虽然在子过程调用中创建临时表没有帮助，因为一旦该子过程调用结束该临时表将消失，对子过程调用之前存在的临时表所做的更改将在该调用完成后继续存在. 例如：

设置

CREATE PROCEDURE #AddSortColumn
(
  @LanguageID TINYINT
)
AS
SET NOCOUNT ON;

DECLARE @IsColumnAdded BIT = 0;

IF (@LanguageID = 2)
BEGIN
  ALTER TABLE #Sort ADD [title] NVARCHAR(2000) COLLATE Modern_Spanish_CI_AI;
  SET @IsColumnAdded = 1;
END;

IF (@LanguageID = 4)
BEGIN
  ALTER TABLE #Sort ADD [title] NVARCHAR(2000) COLLATE French_CI_AI;
  SET @IsColumnAdded = 1;
END;

IF (@IsColumnAdded = 0)
BEGIN
  RAISERROR(N'Invalid @LanguageID: %d', 16, 1, @LanguageID);
  RETURN;
END;
GO

测试

CREATE TABLE #Sort (ID INT);

EXEC tempdb.dbo.sp_help '#Sort'; -- only 1 column

EXEC #AddSortColumn @LanguageID = 4;

EXEC tempdb.dbo.sp_help '#Sort'; -- now there are 2 columns

通过仅将特定排序规则放在这个存储过程中，在添加要支持的新语言时，您应该只有这个地方可以更新。

把它们放在一起

考虑到以上所有内容，我们最终得到以下存储过程，其中包含对 的单个调用api_utils.GetTable()，没有相关的子查询，并且 api_utils.AddSortColumn（您需要创建）可以在其他存储过程中使用并针对其他基表：

CREATE PROCEDURE api.GetTableAsJSON
(
  @LanguageCode VARCHAR(10),
  @JSONifiedTable NVARCHAR(MAX) OUTPUT
)
AS
SET NOCOUNT ON;

    DECLARE @LangID TINYINT;

    CREATE TABLE #Sort
    (
      [ID] INT NOT NULL PRIMARY KEY,
      [category] ...,
      [system] ...
    );

    SET @LangID = api_utils.GetLanguageID(@LanguageCode)

    EXEC api_utils.AddSortColumn @LanguageID = @LangID; -- add [Title] to #Sort

    INSERT INTO #Sort ([ID], [category], [system], [Title])
      SELECT t.[ID], t.[category], t.[system], t.[title]
      FROM   api_utils.GetTable(@LangID) t

    -- Optional index; try with and without to see which is better.
    --CREATE INDEX [IX_#Sort_Title] ON #Sort ([Title]);

    SET @JSONifiedTable = (
        SELECT
            CONVERT(VARCHAR(10), t.ID) AS [id],
            t.[category],
            t.[system],
            t.title,
            JSON_QUERY(utils.ToRawJsonArray((
                SELECT x.[Description]
                FROM api_utils.GetKeywords(t.[ID], @LangID) x
                ORDER BY x.[Description]
                FOR JSON AUTO), 'Description')
            ) AS [keywords]
        FROM #Sort t
        ORDER BY t.[Title]
        FOR JSON AUTO, ROOT('titles')
        );

    RETURN;
END;

另一种方法是只拥有一个代码生成器。这是如果你需要最大性能，因为它只比标记为我给定参数的答案的解决方案快 14%（当你在多个地方和同一代码的多个部分使用它时，给定的 14% 加起来相当快） .

# Place the following in your code then run this script and it will update for you
# without the `#` comment symbols. Replace `title` with whatever it field it should
# be sorting by.

#-- Start-Collation-Sort {sort-by:title}
#
#-- End-Collation-Sort

$text = @'
-- Start-Collation-Sort {sort-by:$1,table:$2}
-- This is generated code. Use ./CollationSortGenerator.ps1 to change
-- this code and all the other files which contain this code.
  CASE @LangID
    WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY $2.$1) -- en
    WHEN 3 THEN ROW_NUMBER() OVER (ORDER BY $2.$1) -- en-ca
    WHEN 6 THEN ROW_NUMBER() OVER (ORDER BY $2.$1) -- 'en-nz'
    WHEN 5 THEN ROW_NUMBER() OVER (ORDER BY $2.$1) -- 'en-za'
    WHEN 2 THEN ROW_NUMBER() OVER (ORDER BY $2.$1 COLLATE Modern_Spanish_CI_AI) -- es
    WHEN 4 THEN ROW_NUMBER() OVER (ORDER BY $2.$1 COLLATE French_CI_AI) -- 'fr-ca'
  END
-- `End-Collation-Sort
'@

$regex =
@'
(?m)^\s*-- Start-Collation-Sort {sort-by:(.+),table:(.+)}
[^`]*
-- `End-Collation-Sort
'@

Get-ChildItem -Path *.sql -recurse |
    Where-Object { (Select-String "-- Start-Collation-Sort"  $_ -Quiet -CaseSensitive -SimpleMatch) } |
    ForEach-Object {
        Write-Output ("Updating file ... " + ($_ | Resolve-Path -Relative ))

        (Get-Content $_ -Raw) -replace $regex, $text |
        Set-Content $_
    }