varchar(max) 无处不在? [英] varchar(max) everywhere?

问题描述

将所有 Sql Server 2008 字符串列设为 varchar(max) 有什么问题吗?我允许的字符串大小由应用程序管理.数据库应该坚持我给它的东西.在 Sql Server 2008 中将所有字符串列声明为 varchar(max) 类型是否会影响性能，无论实际进入它们的数据有多大?

Is there any problem with making all your Sql Server 2008 string columns varchar(max)? My allowable string sizes are managed by the application. The database should just persist what I give it. Will I take a performance hit by declaring all string columns to be of type varchar(max) in Sql Server 2008, no matter what the size of the data that actually goes into them?

推荐答案

通过使用 VARCHAR(MAX)，您基本上是在告诉 SQL Server将值存储在此字段中的最佳方式"，SQL然后服务器将选择是将值存储为常规 VARCHAR 还是 LOB(大对象).通常，如果存储的值小于 8,000 字节，SQL Server 会将值视为常规 VARCHAR 类型.

By using VARCHAR(MAX) you are basically telling SQL Server "store the values in this field how you see best", SQL Server will then choose whether to store values as a regular VARCHAR or as a LOB (Large object). In general if the values stored are less than 8,000 bytes SQL Server will treat values as a regular VARCHAR type.

如果存储的值太大，则允许列从页面溢出到 LOB 页面，就像它们对其他 LOB 类型(text、ntext> 和 image) - 如果发生这种情况，则需要额外的页面读取来读取存储在额外页面中的数据(即性能不佳)，但是这仅在存储的值时发生太大.

If the values stored are too large then the column is allowed to spill off the page in to LOB pages, exactly as they do for other LOB types (text, ntext and image) - if this happens then additional page reads are required to read the data stored in the additional pages (i.e. there is a performance penatly), however this only happens if the values stored are too large.

实际上，在 SQL Server 2008 或更高版本下，即使使用固定长度的数据类型(例如 VARCHAR(3,000))，数据也可能溢出到其他页面上，但是这些页面称为行溢出数据页面，并且处理方式略有不同.

In fact under SQL Server 2008 or later data can overflow onto additional pages even with the fixed length data types (e.g. VARCHAR(3,000)), however these pages are called row overflow data pages and are treated slightly differently.

简短版本:从存储的角度来看，对于某些 使用 VARCHAR(MAX) 而不是 VARCHAR(N) 没有任何缺点>N.

Short version: from a storage perspective there is no disadvantage of using VARCHAR(MAX) over VARCHAR(N) for some N.

(注意这也适用于其他可变长度字段类型NVARCHAR 和VARBINARY)

仅供参考 - 您无法在 VARCHAR(MAX) 列上创建索引

FYI - You can't create indexes on VARCHAR(MAX) columns

这篇关于varchar(max) 无处不在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！