如果一行中的所有数据(对于所有列)不会合理地占用8000或更少的字符，那么数据层的设计应该强制执行这一点。

数据库引擎可以更有效地将所有内容排除在blob存储之外。限制行越小越好。一页中可以塞进的行越多越好。当数据库必须访问更少的页面时，它的性能会更好。

起初我是这么想的，但后来又想了想。这样做会影响性能，但同样地，它也可以作为一种文档形式来了解字段的实际大小。当数据库位于更大的生态系统中时，它确实会强制执行。在我看来，关键是要在合理的范围内宽容。

ok, here's my feelings simply on the issue of business and data layer logic. It depends, if your DB is a shared resource between systems that share business logic then of course it seems a natural place to enforce such logic, but its not the BEST way to do it, the BEST way is to provide an API, this allows the interaction to be tested and keeps business logic where it belongs, it keeps systems decoupled, it keeps your tiers within a system decoupled. If however your database is supposed to be serving only one application, then lets get AGILE in thinking, what's true now? design for now. If and when such access is needed, provide an API to that data.

显然，这只是理想情况，如果您正在使用现有系统，那么您可能需要至少在短期内以不同的方式进行操作。

我有一个udf填充字符串，并把输出varchar(max)。如果直接使用它，而不是将其转换回正在调整的列的适当大小，则性能非常差。我最终将udf设置为一个任意长度的大音符，而不是依赖udf的所有调用者将字符串重新转换为较小的大小。

同样的问题也出现在MSDN论坛上:

Varchar(max) vs Varchar(255)

原文(更多信息):

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000) VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

我能看到的主要缺点是，假设你有这样的情况:

哪一个提供了关于UI所需数据的最多信息?

This

            CREATE TABLE [dbo].[BusData](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [RecordId] [nvarchar](MAX) NULL,
                [CompanyName] [nvarchar](MAX) NOT NULL,
                [FirstName] [nvarchar](MAX) NOT NULL,
                [LastName] [nvarchar](MAX) NOT NULL,
                [ADDRESS] [nvarchar](MAX) NOT NULL,
                [CITY] [nvarchar](MAX) NOT NULL,
                [County] [nvarchar](MAX) NOT NULL,
                [STATE] [nvarchar](MAX) NOT NULL,
                [ZIP] [nvarchar](MAX) NOT NULL,
                [PHONE] [nvarchar](MAX) NOT NULL,
                [COUNTRY] [nvarchar](MAX) NOT NULL,
                [NPA] [nvarchar](MAX) NULL,
                [NXX] [nvarchar](MAX) NULL,
                [XXXX] [nvarchar](MAX) NULL,
                [CurrentRecord] [nvarchar](MAX) NULL,
                [TotalCount] [nvarchar](MAX) NULL,
                [Status] [int] NOT NULL,
                [ChangeDate] [datetime] NOT NULL
            ) ON [PRIMARY]

还是这个?

            CREATE TABLE [dbo].[BusData](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [RecordId] [nvarchar](50) NULL,
                [CompanyName] [nvarchar](50) NOT NULL,
                [FirstName] [nvarchar](50) NOT NULL,
                [LastName] [nvarchar](50) NOT NULL,
                [ADDRESS] [nvarchar](50) NOT NULL,
                [CITY] [nvarchar](50) NOT NULL,
                [County] [nvarchar](50) NOT NULL,
                [STATE] [nvarchar](2) NOT NULL,
                [ZIP] [nvarchar](16) NOT NULL,
                [PHONE] [nvarchar](18) NOT NULL,
                [COUNTRY] [nvarchar](50) NOT NULL,
                [NPA] [nvarchar](3) NULL,
                [NXX] [nvarchar](3) NULL,
                [XXXX] [nvarchar](4) NULL,
                [CurrentRecord] [nvarchar](50) NULL,
                [TotalCount] [nvarchar](50) NULL,
                [Status] [int] NOT NULL,
                [ChangeDate] [datetime] NOT NULL
            ) ON [PRIMARY]

这将导致性能问题，尽管如果数据库较小，可能永远不会导致任何实际问题。每条记录将占用硬盘驱动器上更多的空间，如果您一次搜索大量记录，数据库将需要读取更多的磁盘扇区。例如，一个小的记录可以适合50个扇区，而一个大的记录可以适合5个扇区。如果使用大记录，则需要从磁盘读取10倍的数据。