Does field size in the select tool matter when using a V_WString?

Question

So I'm a bit confused when it comes to field size and V_WString.

In the Select Tool doc: https://help.alteryx.com/20221/designer/select-tool

It mentions the following: "To change the supported length (characters for string...) select Size and enter a number."

In the Data Type doc: https://help.alteryx.com/20221/designer/data-types

It mentions: "Variable Length Wide String. The length of the field adjusts to accommodate the entire string within the field and will accept any character."

So four questions, the data I'm working with contains VERY long strings in some fields. So I'm very wary of truncated fields.

1. Which one has a higher priority? The set field size limit in the select tool or V_WString inherent auto adjust property?

2. Does field size just dictate the limit of a field or does it allocate memory to reserve that space? Because I would assume that Alteryx would go with the most performant option.

3. I ALWAYS set all V_WString field size to max value of 2,147,483,647. I don't think I ever encountered any issues, is there a problem with this?

4. Does field size affect the pushed table in the output database in any shape or form?

Thanks.

Tonya_Smith · Answer

@Anonymous1

Thank you for tagging Engine and I apologize for not getting you answers sooner.

I have worked with Engineering to get you answers to all of your questions, as well as identify differences between the original Engine and AMP Engine.

By default in original Engine V_WString is set to a real string size (max from records) and in AMP Engine it was simplified and by default set to max value.

1. Which one has a higher priority? The set field size limit in the select tool or V_WString inherent auto adjust property?

A: Select tool should have priority.

2. Does field size just dictate the limit of a field or does it allocate memory to reserve that space? Because I would assume that Alteryx would go with the most performant option.

A:  Engine has 2 representations of data: `metainformation` (field info) that is shown for users in MetaData view and `binary` (record) where Engine actually stores knowledge about data and the data itself. In field info the size of a field is limited to a whole field, in record there is knowledge about data size of each item. That means Engine does not allocate memory depending on field info, but only allocates needed amount to store actual data.

3. I ALWAYS set all V_WString field size to max value of 2,147,483,647. I don't think I ever encountered any issues, is there a problem with this?

A:  There should not be any issues with it. The only note is that field has limit of 2G bytes, but string length is represented in char units so when setting 2G it is actually set to 2G divided by size of char unit = 1G (assuming 2 bytes per char unit).

4. Does field size affect the pushed table in the output database in any shape or form?

A:  DataBases have the same approach, they have metainformation about a column (usually type and max size). When data is stored the column metainformation is created from FieldInfo. Note: some databases have a max size of column set to 65K in such case the string would be truncated.