Text Datatype maximum character limit
Question
Application Type
Reactive

I am using text data type to pass  one of the paramter in Advanced SQL. But It seems like the basic Text data type supports only 2000 character. There are also some of the scenario where I need to  use Text Datatype and expected to hold string more than 2000 character. Can anyone suggest on this? 

mvp_badge
MVP

Hi Biswajit,


First of all, that's quite a long string you are passing on.

Do you need the string to be that long and do you need advanced SQL to do so?


Regarding the reason for the limit on 2000 characters, I think it is in line with the good practice of avoiding the use of 2000+ characters in a text field as those are saved as binaries in the database.


Kind Regards,
João

Thanks Joao, Basically I am not saving entire text in a DB  field it's a CSV content I am processing that for a bulk insert operation in a table using Stored Proc. I willl see if I can pass that as Binary and process that in stored proc.

mvp_badge
MVP

Although I understand your point to call a store procedure, I would think on different strategies:

  • Perhaps use a INSERT... SELECT bulk Advanced SQL inserts if possible;
  • Settle for an approach not so "performant" but run the process in the background (like BPTs or timer) to avoid impacting on the user experience.


Both approaches would be more maintainable and traceable moving forward, which would save time in the future when you or somebody else needs to analyse the code.


Kind Regards,
João

Thanks Again, Yeah, I though of BPT as well but actually in our business case the number of records can be somewhere around 2000 to 4000 records which in a asynchronous process of inserting one at a time will take very long to complete the transaction.

mvp_badge
MVP

Hi Biswajit,


You can launch LBPTs (faster than BPTs) as this is an automatic test and have them run parallel inserting let's say a batch of 10 each. There's a presentation of a previous NextStep edition explaining their approach and evaluating the performance attained which you can watch here.

In this case,  you could have an array of LBPT's inserting 10 at each time and the 4k records would be inserted very quickly.

Take into account that this is assuming that the order on which the records are processed is not important given the parallelism of the Light BPT.


Kind Regards,
João

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.