microsoft_sql_server_cdc: use cluster index when primary key is missing

[artemklevtsov]

Hi,

When using snapshot we can use cluster index instead a primary key.
We can try additional query here to search cluster index.

As alternative, to add config field snapshot_order_columns that defines order by columns.

To find cluster index:

SELECT t.name,  c.name FROM sys.indexes AS i
  JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
  JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
  JOIN sys.tables t ON i.object_id = t.object_id
  JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE i.type = 1 -- 1 is cluster
 AND s.name = ? -- schema
 AND t.name = ? -- table
ORDER BY ic.key_ordinal;

Regards

[josephwoodward]

Hi @artemklevtsov, thank you for the feedback/suggestion, that sounds like a good idea.

We could potentially have a clustered index as well, but override both with a snapshot_order_columns option to further control ordering if needed 🤔