I need to store messages of length between 1 and 4200 characters in my database. Should I create the column with "...NAMEHERE CLOB(5000) NOT NULL, ..." or "...NAMEHERE NTEXT NOT NULL", or is something else optimal?
It works with both VARCHAR(5000) and CLOB(5000). Performance depends on the type of database and table. If your database is all in-memory mem:, use VARCHAR(5000). For file:databases CLOB(5000) uses less memory and should perform better in a large database.
The difference is in the file where the data for this column is stored. With VARCHAR(5000) it is stored together with other columns. With CLOB(5000) it is stored in the separate .lobs file.
If you use CLOB, change the LOB SCALE with SET FILES LOB SCALE 2 to allocate the file spaces in units of 2 kilobytes and reduce waste.