CODEWITHSUNDEEP
sql-serveroptimizationdatabase-schema
Anton Gogolev
Anton Gogolev

Reputation: 115751

SQL Server: Is it worth placing BLOB/CLOB columns near the "end" of a table?

Is is an urban legend or having BLOB/CLOB columns last in a list of columns in a table really benefits performance and other aspects?

To "visualise". Is there any difference performance-wise (and other- wise) between these two table layouts:

A                            B
---------------------        ---------------------
ID      int                  ID      int
Name    nvarchar(200)        Content nvarchar(max)
Content nvarchar(max)        Name    nvarchar(200)

Upvotes: 4

Views: 549

Answers (1)

gbn
gbn

Reputation: 432271

Yes, it's an urban legend

On-disk layout is completely independent of the ordering of columns in the CREATE table. See this for more Performance / Space implications when ordering SQL Server columns?

Note: whether a varchar column is at the "start" or the "end", SQL Server still reads it the same via an offset from the NULL bitmap. The MAX column may be in the record or in another page if too long. SO still doesn't matter

There was an issue years ago (pre-dot net) with some SQL Server client drivers that preferred large text parameters at the end. Ancient myths.

Upvotes: 2

Related Questions