Variable Length String for Database?

Feedback
1

PROBLEM
I want to make a Database column that is a large size to accommodate for an HTML Editor value. Something where the user could right up a full review of a subject matter.

In the Servoy Database manager, when you create a column with the ‘String’ datatype it is an ‘nvarchar(x)’ with the initial length value of 50 (but it can be changed to another number).

My question is about having a variable size. Because I would like to set a huge max size value, but have the actual size change based upon how big the String value actually is.

HELP
Would it be okay to use ‘nvarchar(max)’ and not worry about the huge size of it? Or just setting a huge number size limit for the String value type? Like is there any downside to having like 12 columns all with huge or max sizes? Will it cause instability? I want to be sure to avoid any issues like that.

Or, is there a way of implementing a Variable size for a String value? Like would the Media datatype allow for that? I’m just trying to think of a way of avoiding overloading the database server per-emptively.

2

And not to answer my question immediately I suppose :D , but for comparison this is what Microsoft suggests.

If you use nchar or nvarchar, we recommend that you:

  • Use nchar when the sizes of the column data entries are consistent.
  • Use nvarchar when the sizes of the column data entries vary considerably.
  • Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.

So nvarchar(4000) might be enough for my use case.

But again, I’m curious if there’s anything specifically that Servoy differs from this ‘official’ suggestion.

3

Hi John,

What to do and what to avoid does depend on the type of database.
In general you do want to keep the size of a database row as small as possible, the more data, the more time it takes to load the data.
From that point of view, it is sometimes better to separate the metadata and large content into 2 separate tables.
For example keep all product data in 1 table, but the product images in a separate.

Concerning Servoy side of things:
When you need a large text-field (similar to media-type) you can just create a text-field with size ‘-1’.

Hope this helps.

4

mboegem:
Hi John,

What to do and what to avoid does depend on the type of database.
In general you do want to keep the size of a database row as small as possible, the more data, the more time it takes to load the data.
From that point of view, it is sometimes better to separate the metadata and large content into 2 separate tables.
For example keep all product data in 1 table, but the product images in a separate.

Concerning Servoy side of things:
When you need a large text-field (similar to media-type) you can just create a text-field with size ‘-1’.

Hope this helps.

Yeah these large database columns are in a separate table entirely.

Basically I’m trying to figure out how to make a TEXT database column have a dynamically allocated size. From what I’m understanding here, setting an nvarchar to MAX will do so (which is the TEXT datatype in Servoy)… How do I set that ‘max’ value in Servoy itself? I can’t type in ‘max’’ so do I just put -1’ ?

5

Yes ‘-1’, that’s all

6

mboegem:
Yes ‘-1’, that’s all

Roger!

… Seems to have worked!

Much easier typing ‘-1’ than ‘214783647’ that’s for sure. :P