Non-clustered indexes for columns defined within a composite Primary Key

I have a multi-tenant database where I am utilizing an AccountId column within all tables as a part of the composite Primary Key for tenant isolation. Is it beneficial to create additional non-clustered indexes on each column that is part of the composite Primary Key to assist SQL Server with maintaining accurate statistics and improving query performance when joining to lookup tables?

For instance, within an associative table that defines one-to-many relationships between an Account and the American State in which they have offices, in theory which of the two options is preferable given the following structure and sample query?

Create the Account and State tables and populate with sample data.

```
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 NOT NULL
,[AccountName] varchar 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 NOT NULL
,[StateName] varchar NOT NULL
,CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED ([StateId] ASC)
,CONSTRAINT [UQ_State_Code] UNIQUE NONCLUSTERED ([StateCode] ASC)
,CONSTRAI

I'm not sure that the sample query is a fair example to base a recommendation on. The sample query is not a typical multi-tenant application query since it is not specific to a particular client. It is more of a support or management query looking to gain insight about all (or at least multiple) customers. Of course, it could also be maintenance related (e.g. garbage collection would look for oldest dates and not care about AccountId). So let's separate this out:

-
General

I don't see any benefit to ever having a non-clustered index that is solely the leading / left-most key column in the clustered index. The clustered index is already in that order and so statistics exist for it. So, the IX_AccountState_Account index in Option 2 is purely waste, and a drag on the system.

-
Support / Management / Maintenance queries

These queries, especially the maintenance ones, can work across AccountId values. As such, some queries will certainly benefit from non-clustered indexes on clustered index key columns that are not AccountId (or to put it more generally: that are not the left-most / leading key column). This assumes that you have queries that filter / sort on only the entity ID. Not having any queries like that means you probably don't need this index.

-
Application queries

These queries should always include AccountId, so I don't see how they would benefit from an index that is solely on the entity ID.

While I have worked on multi-tenant systems similar to what you are describing, I just thought of something that never occurred to me before: since the clustered index statistics are based on the left-most / leading column and not the combination of key columns, it might be beneficial to manually create statistics for those objects. I seem to recall that it is just the density portion that will account for all key columns (not just the leading column), but that might help the query optimizing. This needs to be tested as I have not tried it (and I won't be able to do such a test anytime soon).

StackExchange Database Administrators Q#231407, answer score: 2