Solved

Specify a collation for 1 specific column in a table

Ricky
Rank
Userlevel 3
Badge +8

Hi,

 

Is it possible to specify a collation for 1 specific column in a table in the model?

The default database collation is set to SQL_Latin1_General_CP1_CI_AS

Currently we have this special column that really wants be case-sensitive and wants to be unique. The latter can be achieved, but we cannot seem to find a way to make this specific column case sensitive towards the database.

I also would like to have the user interface to react to it as it was case insensitive in lookups for example. How can I achieve such functionality? An example is milliohm [mΩ] to meg(a)ohm [МΩ] (or mOhm and MOhm) - the column for such unit has to be unique since they actually really mean a load of difference in resistance, but I want them to be found by the user when typing in any case ‘mohm’.

 

rgds Ricky

icon

Best answer by Ricky 9 July 2021, 14:08

View original

3 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi Ricky,

Technically you could achieve this by writing a Manual/Upgrade control procedure that changes the collation for that specific column. You can use a SQL-typed control procedure to build up the Manual/Upgrade script to include all columns with a certain tag. Maybe a tag called 'MANUAL_COLLATION’ and a value equal to the desired collation could be a way of changing it. Do test this thoroughly to make sure everything goes well.

Info about it here: Set or Change the Column Collation - SQL Server | Microsoft Docs

Feel free to create an idea for this. 

"Wake me up before you low-code"
Ricky
Rank
Userlevel 3
Badge +8

I was hoping for native out-of-the-box support in the SF(and client) instead of fiddling with tags, dynamic stuff and manuals. They tend to break the SF-model-driven mindset and before you know it, we’re all gonna be working around the features that the SF has at slightest inconvenience a feature has (or had)…

Besides the manual stuff, the Client will not handle it very well either: 

above is Latin1_General_100_CS_AI_SC_UTF8 (needs UTF8 and case sensitive for for both case and sub/superscripting uniqueness)

below is (the not so unique) SQL_Latin1_General_CP1_CI_AS

 

Ricky
Rank
Userlevel 3
Badge +8

Just made an idea out of it.

 

Reply


Terms & ConditionsCookie settings