Entity: Support INCLUDE in INDEX

Andrii Bezuglyi
2019-08-10T13:47:10+03:00

SQL Server has a nice feature: field inclusion into index. It allows in some scenarios to take values from index, instead of looking up clustered index. I suggest to add support for the following: ```json "idx_ldoc_Document_eav_int"...

SQL Server has a nice feature: field inclusion into index. It allows in some scenarios to take values from index, instead of looking up clustered index.

I suggest to add support for the following:

    "idx_ldoc_Document_eav_int": {
      "type": "INDEX",
      "definition": {
        "keys": {
          "intValue": {}
        },
        "include": {
          "attrID": {},
          "docID": {}
        }
      }
    },

This shall generate for MS SQL:

CREATE NONCLUSTERED INDEX idx_ldoc_Document_eav_int ON dbo.doc_Document_eav
  (intValue) INCLUDE (attrID, docID)

For other vendors, columns in "include" may be part of index, unless index is unique. For unique index, for other vendors, just ignore "include".

I whole, this would allow:

Take advantage of a nice SQL Server feature, and have a better performance on some cases

When other vendors support it, we will be ready: