用左連接合併右手錶欄位，還是更好的方法？

我有兩個 SQL Server 2014 表，一個具有“en-GB”文化名稱和描述，第二個具有其他文化的翻譯。

“en-GB”表…

CREATE TABLE [dbo].[HomePages] 
(
   [Id]          INT            IDENTITY (1, 1) NOT NULL,
   [Key]         NVARCHAR (25)  NOT NULL,
   [Name]        NVARCHAR (100) NOT NULL,
   [Description] NVARCHAR (256) NOT NULL,
   [Active]      BIT            CONSTRAINT [DF_HomePages_Active] DEFAULT ((0)) NOT NULL,
   CONSTRAINT [PK.dbo.HomePage] PRIMARY KEY CLUSTERED ([Id] ASC)
);

翻譯表：

CREATE TABLE [dbo].[HomePageTranslations] 
(
   [Id]          INT            IDENTITY (1, 1) NOT NULL,
   [HomePageId]  INT            NOT NULL,
   [CultureName] NVARCHAR (5)   NOT NULL,
   [Name]        NVARCHAR (100) NOT NULL,
   [Description] NVARCHAR (256) NOT NULL,
   CONSTRAINT [PK_HomePageTranslations] PRIMARY KEY CLUSTERED ([Id] ASC),
   CONSTRAINT [FK_HomePageTranslations_Globalization] FOREIGN KEY ([CultureName]) REFERENCES [dbo].[Globalization] ([CultureName]) ON DELETE CASCADE,
   CONSTRAINT [FK_HomePageTranslations_HomePages] FOREIGN KEY ([HomePageId]) REFERENCES [dbo].[HomePages] ([Id]) ON DELETE CASCADE
);

en-GB 表中總會有值，但在轉換錶中可能是也可能不是等效值。我正在使用的查詢應該返回翻譯後的值（如果它們存在），或者如果它們不存在則回退到 en-GB 值。查詢在此處的儲存過程中：

CREATE PROCEDURE [dbo].[HomePage_GetForKeyAndCulture]
(
   @Key NVARCHAR(25),
   @CultureName NVARCHAR(5)
)
AS
BEGIN
   SELECT      [base].[Id],
               [base].[Key],
               COALESCE(translation.[Name], base.[Name]) [Name],
               COALESCE(translation.[Description], base.[Description]) [Description],
               [base].[Active]

   FROM        [dbo].[HomePages]               [base]
   LEFT JOIN   [dbo].[HomePageTranslations]    [translation]
           ON  [translation].[HomePageId]  =   [base].[Id]
           AND [translation].[CultureName] =   @CultureName

   WHERE       [Key]                       =   @Key;
END

如您所見，我正在使用 LEFT JOIN 和 COALESCE 函式來實現此目的。我知道 LEFT JOINS 的性能並不過分，那麼有沒有更高效的方法來實現這一點？

**編輯：**以下建議表現在添加了索引，看起來像……

首頁表…

CREATE TABLE [dbo].[HomePages] 
(
   [Id]          INT            IDENTITY (1, 1) NOT NULL,
   [Key]         NVARCHAR (25)  NOT NULL,
   [Name]        NVARCHAR (100) NOT NULL,
   [Description] NVARCHAR (256) NOT NULL,
   [Active]      BIT            CONSTRAINT [DF_HomePages_Active] DEFAULT ((0)) NOT NULL,
   CONSTRAINT [PK.dbo.HomePages] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE UNIQUE NONCLUSTERED INDEX [IX.HomePages_Key]
   ON [dbo].[HomePages]([Key] ASC);

還有 TRANSLATION 表…

CREATE TABLE [dbo].[HomePageTranslations] 
(
   [Id]          INT            IDENTITY (1, 1) NOT NULL,
   [HomePageId]  INT            NOT NULL,
   [CultureName] NVARCHAR (5)   NOT NULL,
   [Name]        NVARCHAR (100) NOT NULL,
   [Description] NVARCHAR (256) NOT NULL,
   CONSTRAINT [PK_HomePageTranslations] PRIMARY KEY CLUSTERED ([Id] ASC),
   CONSTRAINT [FK_HomePageTranslations_Globalization] FOREIGN KEY ([CultureName]) REFERENCES [dbo].[Globalization] ([CultureName]) ON DELETE CASCADE,
   CONSTRAINT [FK_HomePageTranslations_HomePages] FOREIGN KEY ([HomePageId]) REFERENCES [dbo].[HomePages] ([Id]) ON DELETE CASCADE
);

CREATE UNIQUE NONCLUSTERED INDEX [IX_HomePageTranslations_HomePageIdCultureName]
   ON [dbo].[HomePageTranslations]([HomePageId] ASC, [CultureName] ASC);

沒有人為答案而煩惱

上的索引

$$ HomePages $$ $$ Key $$ 上的索引

$$ HomePageTranslations $$ $$ CultureName $$,$$ HomePageId $$

並在適當的情況下使其成為唯一約束 查詢本身對我來說看起來不錯

您可能只使用視圖和索引

$$ HomePageTranslations $$ $$ HomePageId $$並有很好的反應

10s 的數千並不多

引用自：https://dba.stackexchange.com/questions/141442