Non-clustered index with included columns

Covering Index

 Creating an index in SQL with included columns can help optimize query performance by allowing the database to retrieve all the required data from the index itself, without having to access the table. Here's an example of how you can create such an index:

The type of index you're referring to is called a non-clustered index with included columns.

• Non-clustered index: This type of index stores the data separately from the actual table data. It includes pointers to the table rows that contain the indexed data.
• Included columns: These are additional columns that are stored in the index but are not part of the index key. They help improve query performance by allowing the index to cover more queries without needing to access the table data.

Syntax

CREATE INDEX index_name
ON table_name (column1, column2)
INCLUDE (
    column3,
    column4
);

In this syntax:
index_name is the name of the index.
table_name is the name of the table on which the index is created.
column1 and column2 are the columns that the index is based on.
column3 and column4 are the columns that are included in the index but not used for indexing.

Example

CREATE INDEX IndxYear
ON [dbo].[Data] ([ShipmentYear])
INCLUDE (
    [PartId],
    [SerialNumber],
    [SupplierCode],
    [TestCodeId]
);

• In the above example [ShipmentYear] Column is already partitioned
•