我正在我的学校使用SQL Server 2005为一个小型web应用程序开发数据库。 我在varchar vs nvarchar的问题上看到了几个学派的思想:
使用varchar,除非你要处理大量国际化的数据,否则就使用nvarchar。 只要用nvarchar就可以了。
我开始看到观点二的优点了。我知道nvarchar占用了两倍的空间,但这并不一定是一个大问题,因为它只存储几百个学生的数据。对我来说,不担心它,允许所有东西都使用nvarchar似乎是最简单的方法。还是我遗漏了什么?
我正在我的学校使用SQL Server 2005为一个小型web应用程序开发数据库。 我在varchar vs nvarchar的问题上看到了几个学派的思想:
使用varchar,除非你要处理大量国际化的数据,否则就使用nvarchar。 只要用nvarchar就可以了。
我开始看到观点二的优点了。我知道nvarchar占用了两倍的空间,但这并不一定是一个大问题,因为它只存储几百个学生的数据。对我来说,不担心它,允许所有东西都使用nvarchar似乎是最简单的方法。还是我遗漏了什么?
当前回答
Generally speaking; Start out with the most expensive datatype that has the least constraints. Put it in production. If performance starts to be an issue, find out what's actually being stored in those nvarchar columns. Is there any characters in there that wouldn't fit into varchar? If not, switch to varchar. Don't try to pre-optimize before you know where the pain is. My guess is that the choice between nvarchar/varchar is not what's going to slow down your application in the foreseable future. There will be other parts of the application where performance tuning will give you much more bang for the bucks.
其他回答
在过去的几年里,我们所有的项目都使用了NVARCHAR,因为所有这些项目都是多语言的。从外部源导入的数据(例如ASCII文件等)在插入到数据库之前被上转换为Unicode。
我还没有遇到任何与较大索引相关的性能问题,等等。索引确实会使用更多的内存,但是内存很便宜。
无论您是使用存储过程还是动态构造SQL,都要确保所有字符串常量都有N前缀(例如SET @foo = N' hello world.';),这样常量也是Unicode。这避免了在运行时进行任何字符串类型转换。
YMMV。
总是使用nvarchar。
对于大多数应用程序,您可能永远都不需要双字节字符。但是,如果您需要支持双字节语言,而您的数据库模式中只有单字节支持,那么返回并在整个应用程序中进行修改是非常昂贵的。
将一个应用程序从varchar迁移到nvarchar的成本将远远超过在大多数应用程序中使用的一点点额外磁盘空间。
I can speak from experience on this, beware of nvarchar. Unless you absolutely require it this data field type destroys performance on larger database. I inherited a database that was hurting in terms of performance and space. We were able to reduce a 30GB database in size by 70%! There were some other modifications made to help with performance but I'm sure the varchar's helped out significantly with that as well. If your database has the potential for growing tables to a million + records stay away from nvarchar at all costs.
For your application, nvarchar is fine because the database size is small. Saying "always use nvarchar" is a vast oversimplification. If you're not required to store things like Kanji or other crazy characters, use VARCHAR, it'll use a lot less space. My predecessor at my current job designed something using NVARCHAR when it wasn't needed. We recently switched it to VARCHAR and saved 15 GB on just that table (it was highly written to). Furthermore, if you then have an index on that table and you want to include that column or make a composite index, you've just made your index file size larger.
做决定时要考虑周全;在SQL开发和数据定义中,似乎很少有“默认答案”(当然,除了不惜一切代价避免游标)。
由于您的应用程序很小,使用nvarchar与使用varchar相比,基本上没有明显的成本增加,而且如果您需要存储unicode数据,您也省去了潜在的麻烦。