How to select columns when creating an index?

Question

This seems like a strange question. I know different types of indexes in sql server (clustered, non-clustered, unique, filtered, index with columns included, etc.), and I know how to create them. I also know that the index depends on the query, but I do not know who selects the column when creating the index. For example, suppose a simple website allows users to publish text and images. There are two two tables on the website shown in the image:

User request on website:

Select UserID,UserName from User where Email='something' and Password='something'

, , (), ? , , , . , where. ?

, :

Select * from Posts where UserID='something'

. , . , ?

, , :

• .
• .
• .

. , , , .

Answer 1

, WHERE JOIN. Email Password.

, , .

:

CREATE NONCLUSTERED INDEX idx_User_Email_Password
    ON dbo.User (Email, Password);

, :

SELECT UserID, UserName
FROM User
WHERE Email = 'something'
    AND Password = 'something';

( ) , . UserID UserName, , ( dbo.User dbo.User ). SELECT (UserID UserName). , INCLUDED ( ).

CREATE NONCLUSTERED INDEX idx_User_Email_Password
    ON dbo.User (Email, Password)
    INCLUDE (UserID, UserName);

, .

, . , UserTypeID ( ). , UserTypeID UserID, SQL Server, , UserTypeID .

, :

CREATE TABLE #Users
(
    UserId INT
    , UserName VARCHAR(500)
    , Email VARCHAR(500)
    , Password VARCHAR(500)
);

CREATE CLUSTERED INDEX idx_Users_UserID
    ON #Users (UserID);

-- Some test data from my DB
INSERT INTO #Users (UserId, UserName, Email, Password)
SELECT TOP (10000) UserId, UserName, Email, 'password'
FROM Users;

:

SELECT *
FROM #Users;

, .

, UserId, ( UserId ):

SELECT *
FROM #Users
WHERE UserID = 602;

:

CREATE NONCLUSTERED INDEX idx_Users_Email_Password
    ON #Users (Email, Password);

SELECT *
FROM #Users
WHERE Email = '[e-mail]';

, , (PS. , , , , ):

. , :

CREATE NONCLUSTERED INDEX idx_Users_Email_Password_iUserName
    ON #Users (Email, Password)
    INCLUDE (UserName);

, : https://www.simple-talk.com/sql/performance/index-selection-and-the-query-optimizer/

Answer 2

UserId, , , .