While checking the column definition in SQL Server via SYS.all_columns, I found that the max_length of image data type is set as 16. Can anyone help explain the meaning of this?
CodePudding user response：
The 16 you see there does not refer to the max size of the data, it refers to the size of the pointer to the LOB value (which by default is stored off-row).
The documentation for sys.columns mentions the 16 bytes, but does a poor job of explaining why (and it only says it applies to text but in fact it applies to text, ntext, and image).
There is a much better little info nugget on the TEXTPTR topic, where one of the examples says:
returns the 16-byte text pointer
Note that the documentation for image has had this warning since SQL Server 2005 was released more than 16 years ago:
IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
I also blogged about this just a few months ago: