max data types performance
- Madeira Team
- Jul 27, 2012
- 1 min read
On some occasions, migrating from text, ntext or image to varchar(max), nvarchar(max) or varbinary(max) can hurt performance.
How is that possible?
Well, when using text, ntext and image, SQL Server’s default behavior is to keep the LOB data outside of the row, and it just adds each row a 16 byte pointer that references to the place where the relevant LOB information is stored.
When using max fields, the default is the opposite. Up to a size of 8000 bytes, SQL Server keeps LOB data inside the row, and not outside of it, which makes the row much wider. As a result, when performing clustered index seekscan, there is a need to read much more pages at the leaf level of the index, and it makes the query run much longer.
To make SQL Server save LOB data outside of the row when working with max fields, execute the following command:
[code lang=”sql”]
exec sp_tableoption N’MyTable’,’large value types out of row’,’ON’
[/code]
For further reference, see: In-Row Data
Managing complex data types efficiently can make a huge difference in system performance, especially when real-time analytics are involved. Similarly, having reliable diagnostics tools for your car is crucial to prevent breakdowns and costly repairs. Exploring fixd reviews reveals how users value accurate, timely alerts that help keep their vehicles running smoothly. Just like optimizing data structures enhances computing efficiency, using dependable car diagnostics improves vehicle health management, making both tech and car care more effective and less stressful.