What INCLUDE() function does when creating index in MS SQL Server?

Question

What is the difference between creating an index using INCLUDE function vs not?

What would be the difference between the following two indexes?

CREATE NONCLUSTERED INDEX SomeName ON SomeTable (
    ColumnA
    ,ColumnB
    ,ColumnC
    ,ColumnD
    ) INCLUDE (
    ColumnE
    ,ColumnF
    ,ColumnG
    )

vs

CREATE INDEX SomeName ON SomeTable (
    ColumnA
    ,ColumnB
    ,ColumnC
    ,ColumnD
    ,ColumnE
    ,ColumnF
    ,ColumnG
    )

Answer 1

The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it's not part of the tree.

INCLUDE columns are not key columns in the index, so they are not ordered. This means it isn't really useful for predicates, sorting etc.. However, it may be useful if you have a residual lookup in a few rows from the key columns.

INCLUDE columns are not key columns in the index, so they are not ordered. This makes them not typically useful for JOINs or sorting. And because they are not key columns, they don't sit in the whole B-tree structure like key columns

By adding Include (or nonkey)columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:

• They can be data types not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.

An index with Included columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

For more info refer Microsoft docs: Create Indexes with Included Columns

Answer 2

When an execution plan uses an index, it has access to all the columns in the index. If all the columns from a given table are in the index, there is no need to refer to the original data pages. Eliminating that data page lookup is a gain in efficiency.

However, including columns in indexes has overhead for the indexing structure itself (this is in addition to duplicating the values).

The INCLUDE keyword allows for column values to be in the index, without incurring the overhead of the additional indexing structure. The purpose is to resolve queries without having to look up the column information on the original data pages.