限制 SQLServer 中的 NVARCHAR 大小？
我想知道在 SQL Server 2008 中将 NVARCHAR 字段设置为 MAX 而不是特定大小，并限制应用程序逻辑的输入会产生什么后果。 另外，这会是一种糟糕的设计实践吗？
I was wondering what would be the consequences of setting NVARCHAR fields to MAX instead of a specific size in SQL Server 2008, and limiting the input from the application's logic. Also, would these be a bad design practice?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
NVARCHAR(MAX) 在处理较小数据时比旧的 NTEXT 数据类型要好得多，但是，NVARCHAR(n) 在某些领域始终更高效。
作为一般规则，使用最能代表您要存储的数据的数据类型几乎始终是最佳实践。
NVARCHAR(MAX) is a lot better at performing with smaller data than the old NTEXT data type, however, NVARCHAR(n) will always be more efficient in some areas.
As a general rule, using the datatype that best represents the data you are storing is almost always the best practice.
这对性能影响有很多。 特别是，在通用字符串填充标量 UDF 中，我注意到输出被声明为 VARCHAR(MAX) 时存在巨大的性能差异，即使输入从未 > 40 个字符。 更改为 VARCHAR(50) 取得了巨大的进步。
There are a lot of performance implications. In particular, in a general purpose string padding scalar UDF, I noticed huge performance differences where the output was declared as VARCHAR(MAX), even though the inputs were never > 40 characters. Changing to VARCHAR(50) made a HUGE improvement.
只是为了补充所有其他答案：还要注意数据可能来自其他来源的情况，例如 - 仅作为示例 - 从应用程序外部导入的文本文件； 这可以绕过任何应用程序逻辑，除非您在导入例程中复制它......
Just to complement all the other answers: be also careful of scenarios where the data can come from other sources, like - just as an example - text files imported from outside your applications; this could bypass any application logic, unless you duplicate it in the import routines...
如果您知道由于 SQL 存储此类数据的方式，它们永远不会容纳超过有限数量的字符，则不应将所有字段设置为 NVARCHAR(MAX) - 足够小以适合页面的数据将存储在页面，但当它变得太大时，它将被移出页面并单独存储。
另外，您确定需要 NVARCHAR，因为它存储的 unicode 数据占用的空间是标准 VARCHAR 的两倍？ 如果您知道您将使用标准字符，则使用 VARCHAR 代替。
慢慢来，想想你的应用程序的用途。 如果您的地址字段理论上没有大小限制，您将如何将其打印在信封上？ 你说你会在前端应用程序中实现逻辑，但为什么仍然允许数据库有太大的数据？ 如果数据进入数据库破坏了前端的逻辑，会发生什么？
You shouldn't set all your fields to NVARCHAR(MAX) if you know they will never hold more than a finite number of characters due to the way SQL stores this kind of data - data small enough to fit in the page will be stored in the page, but when it grows too large it will be moved off the page and be stored separately.
Also, are you sure you need NVARCHAR as this stores unicode data which takes up twice the space of standard VARCHAR? If you know you will be using standard characters then use VARCHAR instead.
Slo, think of the uses of your application. If you have an address field that has no theoretical limit on its size, how would you print it on your envelope? You say you will implement logic in the front end applciation, but why still allow the database to have data that is too large? And what happens if data gets into the database that breaks the logic in your front end?
主要缺点是
NVARCHAR(MAX)无法使用普通索引进行索引。此外，
NVARCHAR(MAX)类型的变量以及解析这些变量的函数的性能也存在一些问题。如果您只想按原样存储和检索数据，而不是在
SQL Server端解析数据，那么NVARCHAR(MAX)就可以了。Main drawback is that
NVARCHAR(MAX)cannot be indexed with plain indexes.Also, there are some issues with variables of type
NVARCHAR(MAX)and with performance of functions parsing these variables.If you just want to store and retrieve data as is, and not parse it on
SQL Server's side, thenNVARCHAR(MAX)is fine.设置限制会对最大长度进行某些验证产生副作用
Seting a limit has a side effect of some validation on the max length