How do I index a SQL Table where multiple columns make up the primary key in Azure Search Service?

I have a SQL database with a table whose primary key is a collection of three columns, say col1, col2, and col3. I want to have an index over that table using Azure Search Services, but the key required has to be a single field, and the type can only be Edm.String. Does this mean I have to create a view where combines the columns into a single column that works as the key?

What exactly is the function of they key for the Azure Search Service index?

Azure AI Search requires a single field as the document key, and that field must be of type Edm.String. If your SQL table uses a composite primary key (e.g., col1, col2, and col3), you will need to create a view or a computed column that concatenates those columns into a single string field that uniquely identifies each row.

In Azure AI Search, the key field Acts as the document ID in the index and it Must be in a single column Of type Edm.String . It Unique across all documents

This key enables Azure Search to: - Identify, update, and delete documents and updates unique index - Add tracking for incremental indexing You must create a single-column unique key in one of the following ways:

Create a view that combines col1, col2, and col3 into a new id column:

CREATE VIEW vw_MySearchData AS
SELECT
  CAST(col1 AS VARCHAR) + '-' + CAST(col2 AS VARCHAR) + '-' + CAST(col3 AS VARCHAR) AS id,
  col1,
  col2,
  col3,
  other_columns...
FROM MyTable;

Then use this view (vw_MySearchData) as the data source in Azure Search, and set id as the key. Add a Computed Column

If you don’t want to use a view, create a computed column in the base table:

ALTER TABLE MyTable
ADD id AS CAST(col1 AS VARCHAR) + '-' + CAST(col2 AS VARCHAR) + '-' + CAST(col3 AS VARCHAR);

Create the Index and Indexer in Azure Search

After you have the view or computed column in place:

Example index definition (partial):

{
  "name": "my-index",
  "fields": [
    { "name": "id", "type": "Edm.String", "key": true, "filterable": true },
    { "name": "col1", "type": "Edm.String" },
    { "name": "col2", "type": "Edm.String" },
    { "name": "col3", "type": "Edm.String" }
  ]
}

The key in an Azure Search index is the unique identifier for each document in the index. Think of it like a primary key in a SQL table.