During the last CFCamp Jochem van Dieten did a session on DB stuff and one item raised my interest. Basically it came down to this:
If your database uses unicode, your index uses unicode, too. Now, if you search in an indexed field and use the type “CF_SQL_VARCHAR”, the DB cannot directly use that on the index and does an index scan (or was it a table scan even?).
In order to prevent this, you should use the type “CF_SQL_NVARCHAR”. If you have a large database, that could improve performance considerably.
In my local app I switched the types to nvarchar and everything still worked (as I had expected). But of course in my local setup I don’t have enough data to really notice a difference.
What is the Pixl8 team’s opinion on this? Is this something worth considering when it comes to large Datasets? If so, should the preside core be refactored to use NVARCHAR, too?
Thank you!
