Eu tenho um banco de dados multilocatário em que estou utilizando uma AccountIdcoluna em todas as tabelas como parte da chave primária composta para isolamento de locatário. É benéfico criar índices não clusterizados adicionais em cada coluna que faz parte da Chave Primária composta para ajudar o SQL Server a manter estatísticas precisas e melhorar o desempenho da consulta ao ingressar em tabelas de pesquisa?
Por exemplo, dentro de uma tabela associativa que define relacionamentos um-para-muitos entre um Accounte o americano Stateem que eles têm escritórios, em teoria qual das duas opções é preferível dada a seguinte estrutura e consulta de amostra?
Crie as tabelas Accounte Statee preencha com dados de amostra.
DROP TABLE IF EXISTS [dbo].[Account];
DROP TABLE IF EXISTS [dbo].[State];
-- [Account] table and sample values.
IF OBJECT_ID('[dbo].[Account]', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[Account] (
[AccountId] [int] IDENTITY(1,1) NOT NULL
,[AccountAlias] [varchar](3) NOT NULL
,[AccountName] [varchar](128) NOT NULL
,CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ([AccountId] ASC)
,CONSTRAINT [UQ_Account_Alias] UNIQUE NONCLUSTERED ([AccountAlias] ASC)
,CONSTRAINT [UQ_Account_Name] UNIQUE NONCLUSTERED ([AccountName] ASC)
);
SET IDENTITY_INSERT [dbo].[Account] ON;
INSERT INTO [dbo].[Account] ([AccountId], [AccountAlias], [AccountName])
VALUES (1, 'SA1', 'Sample Account 1'), (2, 'SA2', 'Sample Account 2'), (3, 'SA3', 'Sample Account 3')
SET IDENTITY_INSERT [dbo].[Account] OFF;
END;
GO
-- [State] table and sample values.
IF OBJECT_ID('[dbo].[State]', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[State] (
[StateId] [tinyint] IDENTITY(1,1) NOT NULL
,[StateCode] [varchar](2) NOT NULL
,[StateName] [varchar](32) NOT NULL
,CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED ([StateId] ASC)
,CONSTRAINT [UQ_State_Code] UNIQUE NONCLUSTERED ([StateCode] ASC)
,CONSTRAINT [UQ_State_Name] UNIQUE NONCLUSTERED ([StateName] ASC)
);
SET IDENTITY_INSERT [dbo].[State] ON;
INSERT INTO [dbo].[State] ([StateId], [StateCode], [StateName])
VALUES (1, 'AL', 'Alabama'), (2, 'AK', 'Alaska'), (3, 'AZ', 'Arizona'), (4, 'AR', 'Arkansas'), (5, 'CA', 'California')
SET IDENTITY_INSERT [dbo].[State] OFF;
END;
GO
Criar AccountStateOPÇÃO 1 - apenas chave primária composta
DROP TABLE IF EXISTS [dbo].[AccountState];
IF OBJECT_ID('[dbo].[AccountState]', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[AccountState] (
[AccountId] [int] NOT NULL
,[StateId] [tinyint] NOT NULL
,CONSTRAINT [PK_AccountState] PRIMARY KEY CLUSTERED ([AccountId] ASC, [StateId] ASC)
,CONSTRAINT [FK_AccountState_Account] FOREIGN KEY ([AccountId]) REFERENCES [dbo].[Account]([AccountId])
,CONSTRAINT [FK_AccountState_State] FOREIGN KEY ([StateId]) REFERENCES [dbo].[State]([StateId])
);
INSERT INTO [dbo].[AccountState] ([AccountId], [StateId])
SELECT A.[AccountId], S.[StateId]
FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
WHERE A.[AccountId] = 1 AND S.[StateId] IN (1, 2, 3)
UNION
SELECT A.[AccountId], S.[StateId]
FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
WHERE A.[AccountId] = 2 AND S.[StateId] IN (3, 4, 5)
UNION
SELECT A.[AccountId], S.[StateId]
FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
WHERE A.[AccountId] = 3 AND S.[StateId] IN (1, 3, 5)
END;
GO
Criar AccountStateOPÇÃO 2 - chave primária composta + índices não clusterizados
DROP TABLE IF EXISTS [dbo].[AccountState];
IF OBJECT_ID('[dbo].[AccountState]', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[AccountState] (
[AccountId] [int] NOT NULL
,[StateId] [tinyint] NOT NULL
,CONSTRAINT [PK_AccountState] PRIMARY KEY CLUSTERED ([AccountId] ASC, [StateId] ASC)
,CONSTRAINT [FK_AccountState_Account] FOREIGN KEY ([AccountId]) REFERENCES [dbo].[Account]([AccountId])
,CONSTRAINT [FK_AccountState_State] FOREIGN KEY ([StateId]) REFERENCES [dbo].[State]([StateId])
,INDEX [IX_AccountState_Account] NONCLUSTERED ([AccountId])
,INDEX [IX_AccountState_State] NONCLUSTERED ([StateId])
);
INSERT INTO [dbo].[AccountState] ([AccountId], [StateId])
SELECT A.[AccountId], S.[StateId]
FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
WHERE A.[AccountId] = 1 AND S.[StateId] IN (1, 2, 3)
UNION
SELECT A.[AccountId], S.[StateId]
FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
WHERE A.[AccountId] = 2 AND S.[StateId] IN (3, 4, 5)
UNION
SELECT A.[AccountId], S.[StateId]
FROM [dbo].[Account] A CROSS JOIN [dbo].[State] S
WHERE A.[AccountId] = 3 AND S.[StateId] IN (1, 3, 5)
END;
GO
Consulta de amostra
SELECT
A.[AccountName]
,S.[StateName]
FROM
[dbo].[AccountState] A_S
JOIN [dbo].[Account] A
ON A_S.[AccountId] = A.[AccountId]
JOIN [dbo].[State] S
ON A_S.[StateId] = S.[StateId]
WHERE
S.[StateCode] = 'CA'
Das duas opções, que tipo de combinação de índice seria visto como mais adequado para escala? Somente chave primária composta ou chave primária composta mais índices não clusterizados adicionais? Ou existe outra opção mais viável?
