Difference between Index ZipIDX On ZipCode [ Data = (City,State) ]; and Index ZipIDX On (City,State)

Creating an index with the Data keyword specifies a list of properties whose values are stored within the index. This approach provides several benefits:

1. Storing Property Values in the Index: If data values are stored in the index, this can improve query performance by allowing queries to be satisfied entirely from the index, thereby avoiding additional reads of the data global (master data). For example, if properties like Social Security Number (SSN), Date of Birth (DOB), and Name are stored within the index, queries fetching these values can execute directly against the index without accessing the full set of data [1][2][3].

2. Efficiency in Selective Queries: Maintaining data within an index can be especially helpful for selective or ordered searches. For instance, when retrieving records based on sorting criteria or specific property conditions, having data stored in the index enables faster processing. For example, querying for names starting with "Smith" while ordering by name can utilize the index stored values efficiently [1][2][3].

3. Example Syntax:

   Index QuickSearchIDX On Name [ Data = (SSN, DOB, Name) ];
   

   This syntax creates an index named QuickSearchIDX on the Name property, and it stores additional properties SSN, DOB, and Name within the index [1][3].

Limitations:

• Bitmap Indices Restriction: The Data keyword cannot be used with bitmap indices due to constraints in how bitmap indices handle storage [1][2][3].
  

Sources:

The Data keyword means to capture/store the values of City and State in the index but not as a key.  if your sql query needs to query for the ZipCode, City, and State with Data=(City,State) the query can be index satisfiable, meaning it will read data from the index and get the City and State from the index vs. having to go to the master map/Table to get the City and State.

In short: 

• Index ZipIDX On (City,State)  sorts by 2 levels City and State and holds a pointer to the  referred record
• Index ZipIDX On ZipCode [ Data = (City,State) ] sorts just by ZipCode and holds a pointer to the  referred record but also a copy of City and State This is faster if all you want is to map ZIP to City, State and have no need to access what else is in your record

Index ZipIDX On ZipCode [ Data = (City, State) ];

Here, the index is built on the ZipCode field, and the City and State fields are included in the Data portion of the index structure (i.e., stored as part of the index global). When a query retrieves only City and State based on a ZipCode filter, the SQL engine can perform an index-only scan and avoid accessing the main "Master map". This behavior is similar to a covering index in SQL databases because the requested data is fully available within the index itself.

SELECT City, State FROM Sample.Person WHERE ZipCode = 19904;

In this case, the index global might look like:

^gblI("ZipIDX", ZipCode, ID) = $LB("", City, State)

The query will be satisfied using just the index, avoiding a full table (master map) access.

However, this index does not provide any benefit when the query uses City or State as filter conditions, since those fields are not part of the index key. So, It will search on the "Master map"

Now, consider another index:

Index CityStateIDX On (City, State);

This is a composite index (or multi-column index), where both City and State are stored as part of the index key (subscripts), not as index data. The global would look like:

^gblI("CityStateIDX", City, State, ID) = "

SELECT state FROM SAMPLE.PERSON1 WHERE city ='Newton'

When a query uses City or State in the WHERE clause, the index is used to locate the matching IDs, but the SQL engine must then perform a lookup in the master map to fetch any other columns, even if you're selecting only indexed fields like City or State.

So, composite indexes help filter rows efficiently, but they don't function like covering indexes unless the query involves only index keys and you don't need to retrieve other columns.

Here is the Index types documentation