你的 Nvarchar() 有多大
设计数据库时，在决定 nvarchar 应该有多大时，您会考虑哪些决定。
如果我要制作一个地址表，我的直觉反应是地址行 1 为 nvarchar(255)，就像旧的访问数据库一样。
我发现使用这个让我对旧的“字符串将被截断”感到烦恼。 我知道可以通过限制输入框来防止这种情况，但如果用户确实拥有超过 255 的地址行，则应该允许这种情况。
我应该让我的 nvarchar(????) 有多大
When designing a database, what decisions do you consider when deciding how big your nvarchar should be.
If i was to make an address table my gut reaction would be for address line 1 to be nvarchar(255) like an old access database.
I have found using this has got me in bother with the old 'The string would be truncated'. I know that this can be prevented by limiting the input box but if a user really has a address line one that is over 255 this should be allowed.
How big should I make my nvarchar(????)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我的建议：让它们和你真正需要的一样大。
例如，对于邮政编码列，10-20 个字符绝对足够。 电话号码也是如此。 电子邮件可能会更长，50-100 个字符。 名字——嗯，我通常用 50 个字符就够了，名字也是如此。 如果您确实需要，您总是可以轻松地扩展领域 - 这根本不是一项艰巨的任务。
将所有 varchar/nvarchar 字段设置得尽可能大确实没有意义。 毕竟，SQL Server 页是固定的并且每行限制为 8060 字节。 拥有 10 个 NVARCHAR(4000) 字段只是自找麻烦......（因为如果你真的尝试用太多数据填充它们，SQL Server 会向你吐槽）。
如果您确实需要一个非常大的字段，请使用 NVARCHAR/VARCHAR(MAX) - 只要它们适合，它们就会存储在您的页面中，如果它们太大，则会被发送到“溢出”存储。
NVARCHAR 与 VARCHAR：这实际上可以归结为您是否真的需要“外来”字符，例如日语、中文或其他非 ASCII 样式字符？ 在欧洲，甚至一些东欧字符也不能再用 VARCHAR 字段表示（它们将被去掉 hachek（？拼写？）。西欧语言（英语、德语、法语等）都可以很好地使用 VARCHAR 字段来表示。 但是：
NVARCHAR 在磁盘和 SQL Server 内存中确实使用了两倍的空间，如果您确实需要它，那么您真的需要它吗？由你决定。
马克
My recommendation: make them just as big as you REALLY need them.
E.g. for a zip code column, 10-20 chars are definitely enough. Ditto for a phone number. E-Mails might be longer, 50-100 chars. Names - well, I usually get by with 50 chars, ditto for first names. You can always and easily extend fields if you really need to - that's no a big undertaking at all.
There's really no point in making all varchar/nvarchar fields as big as they can be. After all, a SQL Server page is fixed and limited to 8060 bytes per row. Having 10 fields of NVARCHAR(4000) is just asking for trouble.... (since if you actually try to fill them with too much data, SQL Server will barf at you).
If you REALLY need a really big field, use NVARCHAR/VARCHAR(MAX) - those are stored in your page, as long as they fit, and will be sent to "overflow" storage if they get too big.
NVARCHAR vs. VARCHAR: this really boils down to do you really need "exotic" characters, such as Japanese, Chinese, or other non-ASCII style characters? In Europe, even some of the eastern European characters cannot be represented by VARCHAR fields anymore (they will be stripped of their hachek (? spelling ?). Western European languages (English, German, French, etc.) are all very well served by VARCHAR.
BUT: NVARCHAR does use twice as much space - on disk and in your SQL Server memory - at all times. If you really need it, you need it - but do you REALLY ? :-) It's up to you.
Marc
我个人不使用 nvarchar :-) 我总是使用 varchar。
不过，我倾向于使用 100 作为名称，使用 1000 作为评论。 捕获和处理较长的字符串是客户端可以做的事情，例如通过正则表达式，因此 SQL 只能获取它期望的数据。
您可以通过参数化调用来避免截断错误，例如通过存储过程。
例如，如果参数定义为 varchar(200)，那么如果您发送 > 则截断会默默发生。 200. 只有 INSERT 或 UPDATE 语句才会抛出截断错误：如果使用参数，则不会发生这种情况。
SQL Server 的 255 个“限制”回到 6.5，因为 vachar 被限制为 255。SQL Server 7.0 + 更改为 8000 并添加了对 unicode 的支持
编辑：
为什么我不使用 nvarchar：双倍内存占用、双倍索引大小、双倍磁盘大小，根本不需要它。 我在一家在全球设有办事处的瑞士大公司工作，所以我并不狭隘。
还在这里讨论： varchar 与 nvarchar 性能
经过进一步反思，我建议unicode 对客户端开发人员有吸引力，但作为开发人员 DBA，我关注性能和效率......
I don't use nvarchar personally :-) I always use varchar.
However, I tend to use 100 for name and 1000 for comments. Trapping and dealing with longer strings is something the client can do, say via regex, so SQL only gets the data it expects.
You can avoid truncation errors be parameterising the calls, for example via stored procs.
If the parameter is defined as varchar(200), say, then truncation happens silently if you send > 200. The truncation error is thrown only for an INSERT or UPDATE statement: with parameters it won't happen.
The 255 "limit" for SQL Server goes back to 6.5 because vachar was limited to 255. SQL Server 7.0 + changed to 8000 and added support for unicode
Edit:
Why I don't use nvarchar: Double memory footprint, double index size, double disk size, simply don't need it. I work for a big Swiss company with offices globally so I'm not being parochial.
Also discussed here: varchar vs nvarchar performance
On further reflection, I'd suggest unicode appeals to client developers but as a developer DBA I focus on performance and efficiency...
这取决于该字段代表什么。 如果我正在做一个快速原型，我会保留默认值 255。对于诸如评论之类的任何内容，我可能会将其设置为 1000。
我让它更小的唯一方法实际上是我明确知道 zip 的大小的东西代码或 NI 编号等
It depends on what the field represents. If I'm doing a quick prototype I leave the defaults of 255. For anything like comments etc I'd probably put it to 1000.
The only way I'd make it smaller really is on things I definately know the siez of, zip codes or NI numbers etc.
对于需要有一定限制的列（例如姓名、电子邮件、地址等），您应该设置相当高的最大长度。 例如，超过 50 个字符的名字似乎有点可疑，超过该大小的输入可能包含的不仅仅是名字。 但对于数据库的初始设计，请采用合理的大小并将其加倍。 因此，对于名字，请将其设置为 100（如果 100 是您的“合理大小”，则将其设置为 200）。 然后将应用投入生产，让用户玩足够长的时间来收集数据，然后检查实际的
max(len(FirstName))。 那里有任何可疑的值吗？ 有超过 50 个字符的吗？ 找出里面有什么，看看它是否真的是名字。 如果不是，输入表单可能需要更好的解释/验证。对评论进行同样的操作； 最初将它们设置为
nvharchar(max)。 然后，当您的数据库增长到足以让您开始优化性能时。 将评论的最大长度加倍，这样您的专栏就有了一个合适的最大长度。For columns that you need to have certain constraints on - like names, emails, addresses, etc - you should put a reasonably high max length. For instance a first name of more than 50 characters seems a bit suspicious and an input above that size will probably contain more that just a first name. But for the initial design of a database, take that reasonable size and double it. So for first names, set it to 100 (or 200 if 100 is your 'reasonable size'). Then put the app in production, let the users play around for a sufficiently long time to gather data and then check the actual
max(len(FirstName)). Are there any suspicious values there? Anything above 50 chars? Find out what's in there and see if it's actually a first name or not. If it's not, the input form probably needs better explanations/validations.Do the same for comments; Set them to
nvharchar(max)initially. Then come back when your database has grown enough for you to start optimizing performance. Take the max length of the comments, double it and you have a good max length for your column.