Overlapping cover art and individual indexes in SQL Server

Question

I have a question about the best indexing practices in SQL Server (or any RDBMS, for that matter). Take the following table:

ProfileID int
Text      nvarchar(50)

ProfileIDconnected to the table Profile. For each profile, each Textmust be unique. So I put the main key on both columns. Good.

However, I also want to be able to query the above table ProfileID. Therefore, I also put the index on ProfileID.

This means that I have an overlapping index. I don’t know if this is the total amount of waste since there is already a cover index, or if it is corrected because the cover index will be a hash of two columns (or I misunderstand the covers)?

Edit:

I created the index in order (ProfileID, Text). What if for argumentation there were three columns A, B and C that had a cover index of all 3. It would be useful if we requested “A” or “A, B and C” but not “B” or “C ", or" B and C "?

Answer 1

The index on (ProfileID, Text)(in this order) is also the index on ProfileID.

You can still create an additional index only for ProfileIDif you want to increase performance SELECTon queries that are not related to Text.

However, this has two drawbacks:

• DML (INSERT, UPDATE, DELETE)

• , , , .

  , , .

( )?

:

CREATE INDEX ix_mytable_profile__text ON mytable (ProfileID) INCLUDE (Text)

, Text .

, UNIQUE, . ProfileID, Text.

(ProfileID, Text). , A, B C, 3. , "A" "A, B C", "B" "C", "B C"?

CREATE INDEX ix_mytable_a_b_c ON mytable (a, b, c)

SELECT  a, b, 
FROM    mytable
WHERE   a = 1 

-- Index lookup, no table lookup. a is leading

SELECT  a, b, 
FROM    mytable
WHERE   a = 1
        AND b = 1

-- Index lookup, no table lookup. (a, b) are leading.

SELECT  a, b, 
FROM    mytable
WHERE   b = 1

-- Index full scan (`b` is not leading), no table lookup

SELECT  a, b, 
FROM    mytable
WHERE   c = 1

-- Index full scan (`c` is not leading), no table lookup

SELECT  a, b, , d
FROM    mytable
WHERE   a = 1

-- Index lookup, table tookup (d is not a part of the index).

SELECT  a, b, , d
FROM    mytable
WHERE   b = 1

-- Table full scan (there is no point in using index at all, neither for lookup nor for covering).