Skip to main content
Solved

Collation of domain

  • 21 May 2024
  • 7 replies
  • 86 views

Is there a way to set the collation for a domain.

It would already be helpful to know how to set / get the global collation, but I did not find it.

I added some fields with the domain type “code_tag” to the application to avoid referencing numbers from code, and the collation can make a speed difference of a factor 5 in this scenario. 

So, I’m looking for a way to enforce that those have one of the two standard  “SQL … Latin … CS … BIN” collations. 

This topic has been closed for comments

7 replies

Renée Evertzen
Rank
Userlevel 4
Badge +3

Hello Daan,

There currently is no setting present for this in the Software Factory. If this is something you would like to have I recommend creating an idea for this. If possible with a business case example. Then we could look into implementing this in the future.

This doesn't fix your current situation and you most likely know this already, but would it be a fix for you to just set the desired collation at the database level?

Kind regards,

Renée

D
Rank
Userlevel 1
Badge +3

Thank you, maybe it actually does give a solution, but I have to look for side effects.

All userdata is unicode, and at the places where it is not, the user shouldn’t care about the sort order. 

It might require to move some things that aren’t unicode to become unicode, …, in exchange everything that is not sorts faster.

It might still be an idea to add it, but it is not my most urgent idea.

 





 

D
Rank
Userlevel 1
Badge +3

I just got a reminder email to select a best answer. The fact that it is not possible makes that there isn’t  best answer.

 

 

Mark Jongeling
Rank
Userlevel 7
Badge +23

I just got a reminder email to select a best answer. The fact that it is not possible makes that there isn’t  best answer.

It does, Renée's answer answers your question the best. 

Looking at the Microsoft docs, it looks like User defined data types cannot have a different collation other than the database default. Only User defined table types may have columns that have a different collation: CREATE TYPE (Transact-SQL) - SQL Server | Microsoft Learn

This idea is for the platform to support User defined table types; feel free to add the ability to change the collation of columns: Adding table types, and the option to pass them to procedures | Thinkwise Community (thinkwisesoftware.com)

"Wake me up before you low-code"
D
Rank
Userlevel 1
Badge +3

Hi mark,

I have to see when I have time… my use case is just optimisation, in general UTF-8 can handle everything.

The point, which made me investigate this, is that I wanted to change some integer constants in characters or short words. That only has a minimal performance impact if the collation on those columns is is CS_BIN.

I got another (better) idea, and that is to create a template type which can be inserted anywhere. I don’t know it this already exists.

This could be a single point to register constants. The software factory could already fill one template during “creation”, and the developer could create a second template with application constants.

Only for views, and table returning functions, there is a little problem
, which requires a second template.

The first template would look like;

declare @const_hidden_outside = 3

declare @const_asset_state_disposed = 9

… etc.

 

And the second ( unfortunately has to look like ... and that makes it difficult ) …

 

with const ( select * from values …. etc. )
or

cross apply ( … ) const 
 

I think that there should be a way to allow people to define “constants” at a central place. Nothing should be included automatically, if the developer thinks “I want to have the constants here”, he/she can include the templates. It doesn’t break anything that exists, ... the only thing needed is one template type that can be used anywhere. ( And a bright idea, on how to implement those constants for views and functions )

I think this might be a good idea,

Greetings,

Daan



 

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hey Daan,

A “generic” code group is something you could already achieve creating a control procedure that dynamically assigns, bypassing the Assigning (static) screen. Code group can be MANUAL (disable logic prefilter in lookup to select) for example.

This control procedure is assigned using SQL and of type Program object item. For every place you want to assign the template, simply add a record in #prog_object_item and let the Staged strategy take care of the rest. The prog_object_id column needs to be filled with the corresponding existing prog_object_id; e.g. badge_[tab_id], task_[task_id], layout_[report_id], proc_[subroutine_id] among others.

Easy example control procedure code:

;with cte as
(
    select v.prog_object_id
    from 
    (
        values 
            ('badge_customer'),
            ('task_add_customer'),
            ('prc_open_document_customer_add_record')
    ) v(prog_object_id)
)
insert into #prog_object_item
(
    prog_object_id,
    prog_object_item_id,
    order_no,
    template_id
)
select
    c.prog_object_id,
    @control_proc_id,
    3, -- Just after the starting template (and maybe transaction template)
    @control_proc_id -- Template name equals to control procedure name
from cte c

Did not test the above but I believe it should work 😅. This is currently not possible using Static assignments.

"Wake me up before you low-code"
D
Rank
Userlevel 1
Badge +3

 

Hi  Mark, 

Yes, with code it is possible, but I wanted to do it static :-)

I think that the developer should choose to include or not as it also has a “minor” performance impact.

I might launch it as an idea, and I will test the manual group, and where I can assign it.

Thanks,

Daan



 

Terms & ConditionsCookie settings