Can we get clustered indexes put on all the tables used by Ignition (at least for the MSSQL drivers)? Looks like they’re missing from sqlt_sci, sqlt_perm, sqlt_meta, and sqlt_err.
Seems to be a driving issue if you want to open Azure SQL up for cloud deployments.
We can try to work towards this. Currently, the MSSQL translator creates a clustered index for the primary key. Those tables don’t specify a primary key, because it wouldn’t be a single column, and the translator system doesn’t support multi-column primary keys. However, there’s a TODO in there to change that.
I’ll have to read up on Azure and the different requirements. At any rate, I think if we just allow multi-column primary key defs, this will handle itself naturally.
The limitation here is that Azure has no support for heaps (tables without indexes) and all indexes need to be clustered since you can’t page anything (and you’ll get error messages if they’re unclustered).