First, the table. Each number shows how many rows fit into a table where every row always contained 1 int value and 6 string values, all with the same string length (the available field length could be larger). First, the lengths were stored using the non-Var data type. Then the same was done with the Var data type: a new string was generated, but with the same length as the text at the same index that had been stored when the non-Var data items were created.
If the non-Var items were no longer sufficient, a new number was generated within the range between the maximum and minimum length. This did not apply to the Text and NText data types, which always had a fixed size. The .mdf file limit was 30 MB; as soon as the DB with its single table crossed the 30 MB mark, rows stopped being added.
Below is a table with the number of rows that fit into NText and NVarChar items. For the NChar type I could not store a single row because of the error ”
Index ‘PK__Koc_Phot__3214EC27075BC5CD‘ row length exceeds the
maximum permissible length of ‘8060‘ bytes.”, which I do not understand at all.
| Maximum length | NChar | NVarChar | Change value | Change percentage |
| 20 | 109249 | 194664 | +85415 | +43.8 |
| 40 | 54625 | 104076 | +49451 | +42.8 |
| 60 | 37555 | 70415 | +32860 | +46.6 |
| 2000 | 1752 | |||
| 4000 | 864 |
How it was calculated:
Change percentage = (NVarChar – NChar) / NVarChar, with the decimal point moved 2 places to the right
Change value = Change percentage * NVarChar, or NVarChar – NChar
| Fixed string length | NText | Text | Change value | Change percentage |
| 20 | 41761 | 41761 | 0 | 0 |
| 40 | 22131 | 41761 | +19630 | +47 |
| 60 | 18341 | 41761 | +23420 | +56 |
| 2000 | 1111 | 1627 | +516 | +31.7 |
| 4000 | 564 | 1111 | +547 | +49.2 |
| 6000 | 378 | 570 | +192 | +33.6 |
| 10000 | 228 | 423 | +195 | +46 |
| 1000000 | 3 | 5 | +2 | +40 |
I do not have a comparison for the VarChar and Char data types here, because I do not consider them that important. I only have the results achieved with the VarChar type, since I could not get Char working with any of these values – 6000, 4000, 2000:
The VarChar_6000 file received 1072 rows.
As for exceeding the 30MB size, only 2 files managed it, namely Text_1000000.mdf at exactly 32MB and NText_1000000.mdf at exactly 38MB. Simply put, this DB, like any other, is not designed to store strings this long.
However, interesting values can be seen in the log files (*.ldf). While all the DBs had a log file of around 1MB, these 3 exceptions had a file size of 9 or even 19MB:
NText_10000.ldf – 9 NText_1000000.ldf – 19 Text_1000000.ldf – 9
Below is a comparison table of the SQL data types NText and Text:
For small values up to 2000 characters, the NVarChar data type is the best fit; for longer ones, Text . Still, if you are fighting for every byte of your hosting database, it is best to store long texts in a txt file, for example, unless you need them in the DB for some reason (such as indexing).