Polat

Take it seriously. The risk is silently incorrect indexes, which is one of the worse failure modes a database has.

The mechanism: collation determines sort order for text. The database does not implement that itself — it delegates to the operating system's locale library. When you build an index on a text column, the entries are ordered according to the collation rules in force at the time.

If the library is later upgraded and its rules change — even for a small number of character sequences — then the stored index order no longer matches what the database now believes the order to be. The index is not corrupted in any way a check would notice. It is simply sorted by rules nobody uses any more.

The consequence is queries that miss rows. A lookup descends the index using the new rules, takes a branch that would have been right today, and does not find an entry filed under the old rules. No error, no warning, just an absent row.

That is why the warning exists and why it recommends rebuilding rather than suggesting it.

The remedy is to rebuild the affected indexes and then tell the database that the version is now current.

In practice:

1. Take a backup first. Not optional.
2. Reindex — the whole database is simplest and safest. On a large database this takes time and locks, so plan it; there is a concurrent variant that avoids most of the locking at the cost of being slower and occasionally leaving invalid indexes to clean up.
3. Refresh the recorded collation version so the warning clears. There is an ALTER statement for this at both database and collation level.

Step 3 alone makes the message go away and fixes nothing. That is the shortcut people find and it converts a visible warning into an invisible problem. If you do only one of the steps, do step 2.

Also worth checking constraints and partitions, since unique constraints and range partitioning on text both depend on the same ordering.

It genuinely requires assembling it, and the reason is structural: privileges are stored on the objects, not on the roles.

Each object carries an access control list recording who may do what to it. There is no central table of grants to walk, so a per-role view has to be built by scanning every object type and filtering.

Which means the practical answer is to query the information schema views, one per object class:

• Table and view privileges — the table privileges view.
• Column privileges — a separate view, and the one people forget.
• Routine privileges — functions and procedures.
• Schema and database privileges — these live in the system catalogues rather than the information schema, so you read the access control column directly.
• Default privileges — what will be granted on objects created in future. Entirely separate, easy to miss, and the cause of many "why does this role suddenly have access" puzzles.

Union those together with a role column and you have your overview.

One habit worth adopting whatever you build: grant to roles, never to individual users.

Create roles that represent a function — read-only reporting, application write access, migration — grant privileges to those, and make users members. Then this question becomes tractable, because there are five things to inspect rather than fifty.

Databases where privileges were granted ad hoc to individuals over years are the ones where this question has no good answer, and no query fixes that.

Worth adding what happens when you exceed it, because that is the design consequence.

The max variants store data off-row when it is too large, keeping a pointer in the row and the data in separate pages. That is why they can hold enormous values and why they behave differently — they cannot be indexed in the same way, some operations are more expensive, and there is a level of indirection on every access.

So the practical guidance that falls out of this: use the fixed maximum when your data genuinely fits, because it stays in the row and behaves predictably. Use the max variant when values may be large, and accept the trade.

The common mistake is declaring everything as the max variant "just in case", which imposes the indirection on data that never needed it.