Are there any benefits of using SqlDbType.VarChar instead of SqlDbType.NVarChar if all columns in the database are varchars

Question

All columns that contain text in the database used to be nvarchar but we converted them to varchar for performance reasons. I am now left with all my SqlParameters still using SqlDbType.NVarChar and I want to change them to SqlDbType.VarChar but I don't want to waste time doing it if I won't see any benefits from changing them. Is it worth changing them or am I wasting my time?

I am not interested in which one is better between varchar and nvarchar. I want to know if changing the SqlParameter.SqlDbType from SqlDbType.NVarChar to SqlDbType.VarChar will make any difference if all the fields in the database are varchars.

Answer 1

The difference is that nvarchar is used to store unicode data, which is used to store multilingual data in your database tables. Other languages have an extended set of character codes that need to be saved and this datatype allows for this extension. If your database will not be storing multilingual data you should use the varchar datatype instead. The reason for this is that nvarchar takes twice as much space as varchar, this is because of the need to store the extended character codes for other languages

so conclusion is change nvarchar to varchar if you are not storing multilingual data in your fields.

Answer 2

The benefit in changing your code is that it should match the database. If your columns are now varchar, should should access them as varchar.

Not doing so may mean you end up with text encoded incorrectly in the database or reading single byte characters as double byte characters. You risk mangling all text.

Answer 3

If you are using NVARCHAR parameters, I'd expect them to be converted to varchar using the default collation sequence of the current database: see this article for more info.

So unless you are using multiple collations in your database, I wouldn't expect problems. You will presumably be sending more data over the wire if you use NVARCHAR, but the volume would need to be very large for this to have any noticeable impact on performance.

As an aside, I have seen problems with Sybase 12 - if you use an NVARCHAR parameter as a condition in a WHERE clause that compares with an indexed VARCHAR column, Sybase 12 will do a table scan rather than automatically casting the parameter to VARCHAR and using the index. Which caused us some weird performance problems in the past until we understood what's going on. The solution here was to explicitly send parameters as varchar.

Answer 4

I agree with Oded's statement about keeping your code consistent for the next guy. While, technically, there may not be any benefit, one should always keep the next guy in mind. I have the dubious pleasure of reconciling 75 databases that, while having the exact same version number, are all structurally different. Some have foreign key constraints enforced, some don't. Some have primary keys defined, some don't. The same code base, though, is talking with all of them. Go figure. They got that way because someone several years ago took some time-saving short-cut. Then someone else. Then someone else. etc. etc. Now it is a big mess and it is a nightmare trying to sort it out.

Maintainability and readability should supercede here. After all one day someone will come along and say to himself, "I'm confused here. How is this SUPPOSED to be? Hmm... the code all says NVarChar, the database is VarChar... I know that NVarChars are needed for globalization and we might someday want to globalize this thing..." and next thing you know, you have lost your performance boost because this guy changed all the database fields back to NVarChars.