blocks|key|3141575|text|将数据类型与将存储在列中的数据进行匹配。通过类似的论证，您可以说为什么不将所有数据存储在NVARCHAR列中，因为数字和日期可以表示为数字字符串。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3141576|如果将存储在列中的数据的最佳匹配是VARCHAR，则使用它。|3141577|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

You match the datatype with the data that will be stored in the column. By a similar argument you could say why not store all data in NVARCHAR columns, because numbers and dates can be represented as strings of digits.

If the best match for the data that will be stored in the column is VARCHAR, then use it.

blocks|key|3141537|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3141538|第4点并不重要，因为存储空间非常便宜。|blockquote|3141539|3141540|3141541|它不仅仅是存储，还有带宽-+cpu、内存、备份、恢复、传输。节约。|3141542|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|J|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|K|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|L|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|M|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|N|7|@]|8|@]|9|$]]|$1|H|3|-4|4|5|6|O|7|@]|8|@]|9|$]]]|I|$]]

<blockquote>
 Point 4 doesn't matter because storage space is extremely inexpensive.
</blockquote>

it is not just storage, but bandwidth - cpu, memory, backup, recovery, transfer. Conserve.

blocks|key|3141379|text|我想说的是，仍然有充分的理由不使用nvarchar。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3141380|3141381|存储空间非常宝贵，例如在共享主机上或数据库非常大。|unordered-list-item|3141382|性能至关重要。|3141383|Brownfield开发(即数据库具有使用varchar的现有表)。|3141384|您正在与另一个仅支持单字节字符和/或可变字符的旧系统集成。|3141385|3141386|但是，新的开发应该使用nvarchar，特别是nvarchar。因为64位系统正在成为标准。此外，公司(即使是小公司)现在更普遍地是全球化的。|3141387|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|R|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|E|7|V|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

I'd say that there are still valid reasons to not use nvarchar. 

<ul>
<li>Storage space is at a premium, such as on a shared host or the database is
really huge. </li>
<li>Performance is critical. </li>
<li>Brownfield development (i.e. the database has existing tables that use varchar). </li>
<li>You are integrating with another older system that only understands single byte characters and/or varchar.</li>
</ul>

However new development should probably use nvarchar esp. since 64-bit systems are becoming the norm. Also, companies (even small ones) are now more commonly global.

blocks|key|3354757|text|对于许多不同类型的列，您应该选择VARCHAR而不是NVARCHAR，并且可以基于每个列进行选择。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3354758|不需要NVARCHAR产生的额外开销的典型列包括：|3354759|ID类型列:车牌、SSN、病历表标识符等。|3354760|代码列:国际货币代码(USD、UKP等)、ISO国家代码(美国、英国等)、语言代码(en-us等)、会计段代码等|3354761|邮政编码和邮政编码列。|3354762|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

You should choose VARCHAR over NVARCHAR for many different types of columns, and the choice would be on a per-column basis.

Typical columns which would not require the extra overhead NVARCHAR incurs would be:

ID-type columns: License plates, SSNs, Patient Chart identifiers etc.

Code columns: International currency codes (USD, UKP, etc.), ISO country codes (US, UK, etc), Language codes (en-us, etc), accounting segment codes, etc

Postal code and zip code columns.

blocks|key|356354|text|我相信nvarchars的比较比varchars更昂贵，所以它是完全有效的，甚至在你真的不需要unicode功能的地方是首选的，例如，对于一些内部ID。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|356355|而存储成本仍然是的问题所在。如果你有数十亿行，那么这些“小”的差异很快就会变大。|356356|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I believe that comparison of nvarchars is more costly than varchars so it's perfectly valid and even preferred in places where you really don't need unicode capabilities, i.e., for some internal IDs.

And storage cost still does matter. If you have billions of rows then those "small" differences get big pretty fast.

blocks|key|3354620|text|正如其他人指出的那样，这不仅仅是存储成本的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3354621|列的长度将影响每页的行数。每页具有较少的行数意味着可以装入缓存的行数较少，这会降低性能。我假设在MSSQL中，索引的NVARCHAR列将占用索引中更多的空间。这意味着每个块的索引项更少，因此索引中的块更多，因此在扫描(或搜索)索引时有更多的查找，这也会减慢索引访问。|3354622|所以它在每一条战线上都会失去你的表现。如果您真的不关心(或者可以测量性能并对此感到满意)，那也没问题。但是，如果您确实需要存储unicode字符，那么当然可以使用NVARCHAR。|3354623|我可能认为在整个数据库中使用NVARCHAR所获得的可维护性超过了任何性能成本。|3354624|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

As others have pointed out, it's not just the cost of the storage.

The length of a column will affect the number of rows per page. Having fewer rows per page means that fewer can fit into your caches, which drops performance. I am assuming that in MSSQL, a NVARCHAR column which is indexed will use up more space in the index. Which means fewer index entries per block, therefore more blocks in the index, therefore more seeks when scanning (or searching) indexes, which slows down indexed access too.

So it loses you performance on every single front. If you genuinely don't care (or can measure the performance and are happy with it, of course), then that's fine. But if you have a genuine requirement to store unicode characters, of course, use NVARCHAR.

I may be that the maintainability gained by using NVARCHAR throughout your database outweighs any performance cost.

blocks|key|3354693|text|这类问题总是有相同的答案：it+it。没有什么神奇的规则可以让你盲目地遵循。甚至在现代编程语言中使用GOTO也是合理的：Is+it+ever+advantageous+to+use+'goto'+in+a+language+that+supports+loops+and+functions?+If+so,+why?|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3354694|所以答案是:动动脑筋，想想具体的情况。在这个特定的实例中，请记住，如果您的需求发生了变化，您总是可以在数据库中将varchar转换为nvarchar。|3354695|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/24451/goto-usage^0|D|5|1O|2Q|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@$9|S|A|T|1|U]]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

These sorts of questions always have the same answer: it depends. There is no magical rule that you should follow blindly. Even the use of GOTO in modern programming languages can be justified: <a href="https://stackoverflow.com/questions/24451/goto-usage">Is it ever advantageous to use &#39;goto&#39; in a language that supports loops and functions? If so, why?</a>

So the answer is: use your head and think about the particular situation. In this particular instance keep in mind that you can always convert from varchar to nvarchar in the database if it turns out your requirements change.

blocks|key|3141720|text|我看到nvarchar列转换为varchar有两个原因：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3141721|3141722|应用程序正在使用具有4+4GB数据库大小限制的MSSQL+速成版。如果有许多数据库部署，则切换到MSSQL标准版的成本将太高，就像在单租户webapps或具有嵌入式DBMS的应用程序中一样。更便宜的ntext网页版可以帮助here.|ordered-list-item|offset|length|style|BOLD|3141723|nvarchar(4000)不够，但是您不需要ntext列。因此，您将转换为varchar(8000)。但是，在大多数情况下，您可能应该转换为nvarchar(max).|3141724|3141725|entityMap^0|0|0|T|3|0|0|G|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|P|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|Q|8|@$F|R|G|S|H|I]]|9|@]|A|$]]|$1|J|3|K|5|E|7|T|8|@$F|U|G|V|H|I]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

I have seen nvarchar columns converted to varchar for two reasons:

<ol>
<li>Application is using MSSQL Express
Edition, which has 4GB database size
limit. Switching to MSSQL Standard
Edition would be too expensive if
there are many database deployments,
as would be in single-tenant webapps
or applications with embedded DBMS.
The cheaper SQL2008 Web Edition
could help here.</li>
<li>nvarchar(4000) is not enough but you
 don't want an ntext column. So you
 convert to varchar(8000). However,
 in most cases you probably should convert to nvarchar(max).</li>
</ol>

blocks|key|356255|text|你的观点3是无效的。只为单一国家使用而设计的系统不必担心unicode，而且使用中的一些语言/产品根本不支持unicode，或者只支持部分unicode。例如，unicode只适用于美国(即使是加拿大的法语版本也只是拉丁语-1)，所以他们不需要或不必担心TurboTax，而且可能不支持它(我不知道他们是否支持，但即使他们支持，这也只是一个例子)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|356256|“当今的应用程序应该始终与Unicode兼容。”|356257|可能更有效地表达为：|356258|如果不需要特殊处理Unicode，并且以前存在的代码库或应用程序的任何其他部分不需要专门更新以支持Unicode，则当今的应用程序应该始终与Unicode兼容|356259|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/TurboTax^0|3J|8|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]]]

Your point 3 is invalid. Systems that are designed only for a single country's use don't have to worry about unicode, and some languages/products in use don't support unicode either at all or only partially. For example, <a href="http://en.wikipedia.org/wiki/TurboTax" rel="nofollow noreferrer">TurboTax</a> is only for the U.S. (and even with a Canadian version with French is still just LATIN-1), so they wouldn't need or have to worry about unicode and probably don't support it (I don't know if they do or not, but even if they do, it's just an example).

"Today's applications should always be Unicode compatible."

is probably more valid expressed as:

"Today's applications should always be Unicode compatible if nothing special needs to occur to handle Unicode properly, and a previously existing codebase or any other piece of the application does not need to be updated specifically to support it"

blocks|key|3354346|text|存储比历史上任何时候都便宜，但如果你能在一个给定的硬盘上存储两倍的数据，那是很有吸引力的，不是吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3354347|此外，还有用于缓存的RAM和固态硬盘，它们都比硬盘贵得多。当您有数百万行时，使用更紧凑的数据格式是有益的。|3354348|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Storage is less expensive than it's ever been historically, but still if you can store twice as much data on a given hard drive, that's attractive, isn't it?

Also there's RAM for caching, and solid-state drives, which are both a lot more expensive than hard drives. It's beneficial to use more compact data formats when you have millions of rows.

blocks|key|3354595|text|您的数据库服务器有没有办法使用UTF-8作为编码？然后，您将获得以下好处:存储空间低，主要用于ASCII加载，并且能够存储Unicode范围内的任何内容，因此可以进行扩展。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3354596|我想请您的数据库供应商也支持UTF8作为VARCHAR+SQL类型的编码。我不知道其他数据库服务器是如何做到这一点的，但我知道至少在MySQL和PostgreSQL中可以在VARCHAR和TEXT字段中使用UTF-8。|offset|length|style|CODE|3354597|话虽如此，使用UTF-16编码字段进行而不是的唯一原因是，如果您必须与在UTF-16输入上中断的应用程序进行交互。这将是大多数设计用于处理ASCII或ISO-8815文本编码的遗留应用程序，它们最好是处理UTF-8。|BOLD|3354598|entityMap^0|0|K|7|2E|7|2M|4|0|J|3|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@$D|O|E|P|F|G]|$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@$D|V|E|W|F|J]]|9|@]|A|$]]|$1|K|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|L|$]]

Is there a way for your database server to use UTF-8 as an encoding? You then get the benefits of low storage for mostly ASCII loads, and the ability to store anything in the range of Unicode so that expansion is possible.

I would ask your database vendor to support UTF-8 as an encoding for the <code>VARCHAR</code> SQL type, as well. I don't know how other DB servers do it, but I do know that you can use UTF-8 in <code>VARCHAR</code> and <code>TEXT</code> fields in at least MySQL and PostgreSQL.

All that having been said though, the only reason to not use a UTF-16 encoded field is if you have to interact with applications which will break on UTF-16 input. This would be most legacy applications which were designed to handle ASCII or ISO-8815 text encodings, which would be better off processing UTF-8.

blocks|key|3354928|text|我的学习是“使用NVARCHAR”作为默认值...但是@CadeRoux有一个很好的观点:如果您确定数据除了ASCII之外永远不会保存任何东西--比如美国车牌--+VARCHAR可能会为您节省一点成本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3354929|我要说的是，他那句名言的另一面是“对任何有名字(人物、街道、地点)或自然语言文本(电子邮件、聊天、文章、博客帖子、照片说明)的东西都要使用NVARCHAR”。否则，您的"firstname“列将无法正确地编码"François”或"José“，并且您的文本列将不允许带有”外国“音标的文本，或者--就此而言--非常常见的美国字符，如分号”，“，段落标记”？“，项目符号”·“。(因为这些都不是ASCII字符，并且没有好的、标准的方法将它们放入VARCHAR字段。相信我:你会伤到自己的。)|3354930|在我从事的任何项目中，我从来没有因为使用NVARCHAR而受到指责，因为我“在磁盘空间上浪费了太多的公司资金”。而且，如果我必须重新编写代码或DB模式(特别是在实时生产系统上)，重新调整所花费的成本将很容易超过购买一个小50%25的磁盘所带来的“节省”。|3354931|要真正理解这个问题，您必须了解ASCII、Unicode和Unicode的典型编码(如UCS-2和UTF-8)。|3354932|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

My leaning is "use NVARCHAR" as a default... but @CadeRoux has a good point: if you are SURE the data will never hold anything but ASCII -- like a US license plate -- VARCHAR might save you a tiny bit of cost.

I'd say the flip side of his well-put statement is "DO use NVARCHAR" for anything that will have names (people, streets, places) or natural language text (email, chat, articles, blog postings, photo captions). Otherwise, your "firstname" column will not be able to encode "François" or "José" correctly, and your text columns will not allow text with "foreign" diacritcal marks, or -- for that matter -- very common US characters like cent-mark "¢", paragraph mark "¶", a bullet "•". (Because none of those are ASCII characters, and there is no good, standard way to put them in to a VARCHAR field. Trust me: you'll hurt yourself.)

On ANY project I've worked on, I've NEVER been scolded for using NVARCHAR because I was "squandering too much company money on disk space". And if I had to rework code or the DB schema (especially on a live, production system), the cost spent in the re-fit would EASILY outweigh the "savings" from buying a disk that was 50% smaller.

To really understand this question you really have to understand ASCII, Unicode, and Unicode's typical encodings (like UCS-2 and UTF-8).

blocks|key|356182|text|我不是这方面的专家。但是为什么您不能使用UTF-8来获得小空间和unicode的组合呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|356183|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I'm no expert on the subject. But any reason why you couldn't use UTF-8 to get a combination of small space and unicode?

blocks|key|3141900|text|我见过一些数据库，在这些数据库中，索引(indexes?...different辩论)大于数据。如果在索引中可以避免一半的存储需求(varchar)，那么可以假设这相当于给定页面的命中密度的两倍，并且更有效的填充因子分解导致更快的数据检索/写入/锁定和更少的存储需求(前面已经提到过)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3141901|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I've seen some database where the indices (indexes?...different debate) have been larger than the data. If one can get away with half the storage demands (varchar) within the index then one assumes that equates to twice the hit density of a given page and more efficient fill-factoring leading to faster data retrieval/writing/locking &amp; less storage requirements (already mentioned).

<ol>
<li>VARCHAR does not store Unicode characters.</li>
<li>NVARCHAR does store Unicode characters.</li>
<li>Today's applications should always be Unicode compatible.</li>
<li>NVARCHAR takes twice the amount of space to store it.</li>
<li>Point 4 doesn't matter because storage space is extremely inexpensive.</li>
</ol>

Ergo: When designing SQL Server databases today, one should always use NVARCHAR.

Is this sound reasoning? Does anyone disagree with any of the premises?
Are there any reasons to choose VARCHAR over NVARCHAR today?

Is VARCHAR like totally 1990s?

数据存储

SQL Server

PostgreSQL

数据库

服务器

MySQL

characters.Today's compatible.NVARCHAR不存储Unicode characters.NVARCHAR而存储Unicode characters.Today‘s应用程序应该始终是Unicode compatible.NVARCHAR需要两倍的空间来存储它。第4点并不重要，因为存储空间是非常inexpensive.的。Ergo:现在设计SQL Server数据库时，

问VARCHAR是不是完全是90年代的？
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问VARCHAR是不是完全是90年代的？EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问VARCHAR是不是完全是90年代的？
EN