when defining a field, text, 16k on servoy, mssql 2008 makes this a ntext field.
but this is not recognized on the its way back by servoy when retrieving the db structure, though it works on the app side.
I have manually changed that to nvarchar(max), which is seen as text by servoy as I understand ntext is µsoft specific. but this is huge, 2GB.
any drawback in doing this? is it clever or is it gonna waste 2Gb for each 10k field? I’ll find out soon anyway, but if anyone had to play with pieces of text in mssql2008 and servoy, I am interested to know what they do.
Hi Lesouef,
I think this 2GB is just a max length. It’s not that you are using a CHAR() datatype that pads any data you put in with spaces.
You should see the length option as a contraint on the column, the underlying code will decide how to store it in the best way. At least this is how PostgreSQL works. With Pg you could use all TEXT columns, even for 1 char data this would be stored in the same way as a VARCHAR(1). And VARCHAR(3000) data would be stored the same way as TEXT. I would think MSSQL is smart in the same way.
But you could do some tests yourself by inserting some data in those large VARCHARs and see how large the database grows and then do the same with small VARCHARs with the same data.
I would be surprised if you would see much difference.
Hope this helps.
not that obvious with mssql. I found some info on the web which says ntext is deprecated, and not to be used for performance reasons since nvarchar(max) has been introduced with sql2005. ntext fields was stored outside the db (not exactly, but I don’t remember the terms and lost the URL already, you would have understood this better than me), and only the address was in the field from a db storage pt of view. actually, i don’t really care for performance for the present case, it is ontly a few recs. but I don’t like the idea of the field type not recognized by servoy while generated from within servoy. basically, any text field > 4000 chars created from servoy is generated as ntext by sql2008 instead of varchar(max). the limit is supposed to be 8k but in practise, I see a 4k (4000 exactly, not 4 real k) when doing it manually, so there is an obvious compatibility pb here when mysql allows text fields > 64k for instance; keeping a common data structure whatever is the db would mean to limit to the lowest limit? or recognize ntext field? not easy. obviously the driver translate nvarchar(16k) to ntext instead of nvarchar(max). µsoft and jtds the same. jtds is just a bit better as it sees ntext correctly, but does not generates nvarchar(max) either.
Please can you file a case then so Servoy support can take a look at this.
Hi Lesouef,
Servoy uses a mapping per database dialect to create the column for the specific database.
For sqlserver, we do use ntext for columns >4000.
This logic is probably no longer optimal for newer versions of sqlserver.
Please create a case in our support system so we will investigate.
Note that Servoy will always use the information from existing tables, so you can work around this by creating the tables/columns manually in the database.
Rob
ok, I’ll try to find again what I read on this, it will be more relevant for you than for me, but I am sure the guy was firm on don’t use ntext anymore.
the weird thing in what you say is that you will use what is set in the db: in this case, why is ntext displayed as unknown by servoy when using he mssql driver?
see the capture, this was created using text,16k, > resulting in a ntext in sql2008, and see what it does after the db structure reload (field is ‘sql_code’)
you can also see the field ‘sql_code2’ which was done the same then I modified as nvarchar(max) on the db side before reloading.
I have not made any size tests in between both methods ntext and nvarchar(max) as I have only a few records of these (my custom queries actually), and this is not visible on the whole db size.
lesouef:
why is ntext displayed as unknown by servoy when using he mssql driver?
Servoy depends on what the driver reports.
The jtds driver reports ntext as a text(1073741823), the mssql driver apparently reports unknown.
Rob
smart that! deprecated does not mean unsupported normally… well the µgiant does what he wants…
what does servoy do in this case? defaults to what? because it works, at least up to 9k (max I used already), and the limit is probably applied (did not test 1G!).
in other words, is there any potential problem to use that unknown type field, or shall i change that?
Microsoft Learn: Build skills that open doors in your career
ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
Servoy defaults to storing as string, so that works in your case.
But microsoft may drop it, so better not to use them in new solutions.
Rob
ok. thanks. first time I need > 4k text fields, so not too late to change it.