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?
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.
Although I understand your point to call a store procedure, I would think on different strategies:
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.
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.
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.