Hi everyone!
I had an issue during an external data sync. I have a code field that had two values differentiated by accent, for example:
"codigo" "código"
By default, my database collation is Latin1_General_100_CI_AI_SC. After some research, I realized that I need to change the collation to Latin1_General_100_CI_AS_SC.
Has anyone done this before? Could this query cause any issues?
ALTER TABLE {Table}
ALTER COLUMN [Column] NVARCHAR(20)
COLLATE Latin1_General_100_CI_AS_SC
No issue with this query, you need to check which database you are using, the syntax can be a bit different.You can use the forge component to run your query:
Thanks Shingo!
As mentioned by shingo you can use sql-sandbox but OutSystems recommends keeping database collation case-insensitive (CI_*) overall (see docs: https://success.outsystems.com/documentation/11/setup_outsystems_infrastructure_and_platform/setting_up_outsystems/default_platform_server_and_database_configurations).
Changing individual columns like this is ok for specific needs (accent-sensitive searches/indexes), but test thoroughly — it can affect queries, indexes, and integrations if not consistent.
Thanks Bhanu!
Happy to Help @Inácio Milano
Dear Inacio,
In OutSystems, this behavior is expected and fully driven by the database.
OutSystems does not manage or override column collations. All string comparisons performed by Aggregates, Filters, Joins, Exists, Count, validations, and sync logic are executed in the database and therefore respect the column collation.
With Latin1_General_100_CI_AI_SC, values like "codigo" and "código" are considered equal. After changing the column to Latin1_General_100_CI_AS_SC, comparisons become accent-sensitive, and OutSystems will automatically distinguish those values without any change in the application logic.
Things to be aware of in OutSystems:
Functional behavior changes: searches, syncs, and business rules that previously matched values ignoring accents will no longer do so.
Advanced SQL / views: if this column is compared or joined with other columns using a different collation, you may get collation conflict errors and will need to align collations or apply COLLATE explicitly.
Indexes and constraints: if the column is part of indexes or UNIQUE constraints, they may need to be recreated when changing the collation.
From an OutSystems perspective, your ALTER TABLE … ALTER COLUMN … COLLATE approach is valid. Just make sure to test the change in a non-production environment and validate all affected syncs and queries.
References (official)
Microsoft: COLLATE (Transact-SQL)
Microsoft: Set or Change the Column Collation
Microsoft: Collation Precedence (why conflicts happen)
Best, Miguel
Thanks Miguel, that's exactly what i need!