varchar(max) MS SQL Server 2000，有问题吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/737755/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
varchar(max) MS SQL Server 2000, problems?
提问by bplus
I've inherited a asp.net website project that currently runs SQL Server 2000 as its backend.
我继承了一个目前运行 SQL Server 2000 作为其后端的 asp.net 网站项目。
I've been doing some databases changes on a local copy of the db using SQL Server 2005 Express. I've create a table using varchar(max)columns. They are used to stored snippets of XHTML that are of arbitrary length.
我一直在使用 SQL Server 2005 Express 对数据库的本地副本进行一些数据库更改。我已经使用varchar(max)列创建了一个表。它们用于存储任意长度的 XHTML 片段。
While browsing around on stackoverflow I came across this: Are there any disadvantages to always using nvarchar(MAX)?
在浏览 stackoverflow 时，我遇到了这个问题： 总是使用 nvarchar(MAX) 有什么缺点吗？
User mattruma says he found out the "hard way" about using varchar(max)on SQL Server 2000.
用户 mattruma 说他发现了varchar(max)在 SQL Server 2000 上使用的“困难方式” 。
What should I use instead of varchar(max)given that the live database runs on SQL Server 2000?
如果varchar(max)实时数据库运行在 SQL Server 2000 上，我应该使用什么？
Thanks in advance for any help!
在此先感谢您的帮助！
采纳答案by JohnFx
It sounds like the varchar(MAX)limitations are a moot point if your live DB is SQL Server 2000, which doesn't support them. If you have more than 8K characters to store you are pretty much left with the only other option, a TEXTcolumn. However, beware that TEXTcolumns have a lot of limitations too.
varchar(MAX)如果您的实时数据库是不支持它们的 SQL Server 2000，那么这些限制听起来似乎没有实际意义。如果您要存储超过 8K 个字符，则几乎只剩下其他选项，即一TEXT列。但是，请注意TEXT列也有很多限制。
For example you can't sort or group on them easily, nor can you compare them for equivalency with other columns. That is you can't say Select * from mytable where Mytext1 = mytext2.
例如，您无法轻松地对它们进行排序或分组，也无法将它们与其他列的等效性进行比较。那是你不能说Select * from mytable where Mytext1 = mytext2。
Other relevant concerns:
其他相关问题：
- I'd suggest using an
NTextorNVarcharcolumn regardless of the way you go to support Unicode. - If the table has a lot of other columns and the
varchar(8000)column is likely to be frequently close to full, you may have problems with the row limit of 8K. Keep this in mind too.
- 无论您采用何种方式支持 Unicode，我都建议使用
NTextorNVarchar列。 - 如果表有很多其他列，并且该
varchar(8000)列很可能经常接近满，那么您可能会遇到 8K 行限制的问题。也要记住这一点。
回答by JoshBerke
VARCHAR(Max)was introduced in SQL Server 2005, and will not work on SQL Server 2000. You need to use either VARCHAR(8000)assuming that will be big enough. Otherwise you will need to use TEXT
VARCHAR(Max)是在 SQL Server 2005 中引入的，不能在 SQL Server 2000 上运行。VARCHAR(8000)假设它足够大，您需要使用任何一个。否则你将需要使用TEXT
Edit
编辑
Also if you switch to VARCHAR(8000)keep in mind there is a limit that a single row cannot have more then 8060 bytes. So if you fill up a varchar(8000)table and have a bunch of other large columns you will get an error. This is where Textcomes in.
此外，如果您切换到VARCHAR(8000)记住，有一个限制，即单行不能超过 8060 个字节。因此，如果您填满一张varchar(8000)表并有一堆其他大列，您将收到错误消息。这是Text进来的地方。
Texthas performance implication because by default it is stored in a separate location, and they keep a pointer in a table. There is a set option which changes this behavior so that text types are kept in the table until they reach a certain size. If you have mostly small blobs you might want to enable this.
Text具有性能影响，因为默认情况下它存储在单独的位置，并且它们在表中保留一个指针。有一个设置选项可以更改此行为，以便将文本类型保留在表格中，直到达到特定大小。如果您有大部分小斑点，您可能希望启用此功能。
回答by Otávio Décio
Use a TEXT column.
使用文本列。
回答by Mitchel Sellers
This depends on your needs. You can use a TEXT column instead of VARCHAR(MAX) but you have to be sure that your implementation doesn't need to search on that field, as you cannot do like comparisons on TEXT and NTEXT fields.
这取决于您的需求。您可以使用 TEXT 列而不是 VARCHAR(MAX) 但您必须确保您的实现不需要在该字段上进行搜索，因为您不能在 TEXT 和 NTEXT 字段上进行比较。
If you can limit yourself to 8000 characters, I would use a VARCHAR(8000) column to store the information.
如果您可以将自己限制为 8000 个字符，我将使用 VARCHAR(8000) 列来存储信息。
