Why is tenant_id the last field in the (fysically) generated index?

I noticed that in the database, the tenant_id is generated as the last field in the index.
To me it seems more logical to have the tenant_id as the first field of the composite index.
Is there a reason the field tenant_id isn’t the first field in the (fysically) generated indexes?

Hi Eric,

That's a good question, and the only thing that comes to  my mind is that the tenand_id has relatively lower selectivity (kinda, but not exactly the same as less variation) meaning it's probably less useful than the other columns in the index.

Note that the DB engine might not actually use the index if it determines it has a low selectivity, so putting lower selectivity columns at the end of the index is probably a good idea.

Or maybe I'm overthinking it, and it's just because they had to put it somewhere, and figured putting it at the end was easier. :)

It depends on how you use tenants. We have around 200 tenants and for us the tenant_id has a high selectivity. Expecialy for example we have a datatime field that we use a null date to signify a currently active record and an actual date for closed versioned records. In this case to get a list of all active records for a tenant then SQL query analyser itself recomends using an index of tenant_id + enddatetime. 

Letting the developer override the default of tenant_id at the end would give the ability to optimise the index usage regardles of how tenants were used.

I have an "idea" creted for this option.

https://www.outsystems.com/ideas/7937/allow-the-ability-to-use-tenant-id-in-sql-indexes?IsFromAdvancedSearch=True