SQL Server index recommendations

yduiuuwa 于 2023-06-28 发布在 SQL Server

关注(0)|答案(2)|浏览(101)

I have these 3 tables

Company (
    Id,
    Name
)

User (
    Id,
    Name,
    CompanyId
)

UserAccess (
    Id,
    UserId,
    Date,
    Type /* 'Login' or 'Logout' */
)

The query for the access report is:

DECLARE @CompanyId int = 1;

SELECT
    u.Name,
    ua.*
FROM
    UserAccess ua
INNER JOIN 
    User u ON ua.UserId = u.Id
WHERE
    u.CompanyId = @CompanyId

The UserAccess table can have thousands or millions of rows. So I was thinking of creating a non-clustered index on UserAccess table ( UserId ), or is it better to also add CompanyId column and reference the index to it? ( Company table wouldn't be necessary in the query).

What are your recommendations?

Thanks in advance.

sql-server

来源：https://stackoverflow.com/questions/76561305/sql-server-index-recommendations

2条答案

按热度按时间

nnvyjq4y1#

I would recommend to move with non clustered indexes on the UserId column in the UserAccess table:

CREATE NONCLUSTERED INDEX idx_UserAccess_UserId
ON UserAccess (UserId);

For creating a non-clustered index on the UserId and CompanyId columns in the UserAccess table:

CREATE NONCLUSTERED INDEX idx_UserAccess_UserId_CompanyId
ON UserAccess (UserId, CompanyId);

As well as if you gets corruption within using non clustered index, the you can follow these steps: https://www.stellarinfo.com/blog/handle-non-clustered-index-corruption-in-sql-database/

I hope this helps!!

赞(0）回复(0）举报 2023-06-28

tf7tbtn22#

SELECT  u.Name, ua.*
    FROM  UserAccess ua
    INNER JOIN  User u  ON ua.UserId = u.Id
    WHERE  u.CompanyId = @CompanyId

The execution will start with u , which needs this covering index:

INDEX(CompanyId, Id,  Name)

Then ua needs (preferably clustered)

INDEX(UserId)

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server index recommendations

2条答案

相关问题

热门标签

最新问答