Sql-server – Should I used varchar(max) or varchar(4000) SPARSE

sparse-columnsql servervarchar

I have a "comment"-type column that is rarely used — around 6% non-null in a population of 3 million records. The average length (when used) is 6 characters, and the max so far is around 3KB. A maximum of 4000 characters is reasonable for this field. I have two options:

comments varchar(max) NULL -- this is the current column definition
comments varchar(4000) SPARSE NULL

My current understanding is that in both cases, a NULL value would require no storage — just the column's NULL bit set and a length of 0 in the row metadata.

But for the non-null cases, does one have a clear advantage over the other?

The extra 4-byte pointer for sparse columns with values suggests they are always stored off-row like text or very large varchar(max) fields. Is that the case?

If so, I'd lean toward using varchar(max), since it only stores values off-row if the total row length exceeds 8KB, and the majority of my values are short and unlikely to put a row over the limit.

I haven't seen this particular situation addressed in the BOL, so I'm hoping someone here knows enough about the innards of MSSQL to give some insight.

(If it matters, I'm currently using 2008R2, but hoping to upgrade soon to 2014.)

Best Answer

There is no advantage for the non-NULL cases when using SPARSE, and in fact, there are two stated disadvantages:

an extra 4 bytes per each non-NULL value
slightly longer access time

As you pretty much already gathered, the SPARSE option only makes sense for fixed-length datatypes; I can't think of a single reason to use it on variable-length types.

I am not sure that the extra 4 bytes implies anything about it being stored off-row, and the MAX types aren't entirely off-row when they exceed 8000 bytes as there is then the 16-byte pointer in the row to that off-row location.

Stick with VARCHAR(4000), no SPARSE, and I would even consider making it NOT NULL DEFAULT('') (an empty string is still 0 bytes, but now you don't need to mess with the NULL indicator, and can a comment really be "unknown" as opposed to "no comment"?).

Related Solutions

Sql-server – SQL Server 2008 R2 Dirty reads – how non-atomic

EDITED after reading the MSDN forum link from the comment, very interesting.

Regardless of isolation level, two users cannot update a single page simultaneously, nor can any user read a partially updated page. Just imagine how SQL Server would deal with a page where the header says Col3 starts at byte 17. But it really starts at byte 25, because that part of the row hasn't been updated yet. There's no way a database could handle that.

But for rows larger than 8k, multiple pages are used, and that makes a half-updated column possible. Copied from the MSDN link (in case the link breaks), start this query in one window:

if object_id('TestTable') is not null
    drop table TestTable
create table TestTable (txt nvarchar(max) not null)
go
insert into TestTable select replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 10
update TestTable set txt=replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 100000

This creates a table and then updates it with a string of 100.000x the same character. While the first query is running, start this query in another window:

while 1=1 begin
 if exists (select * from TestTable (nolock) where left(Txt,1) <> right(Txt,1))
    break
end

The second query stops when it reads a column that is half updated. That is, when the first character is different from the last. It will finish quickly, proving that it is possible to read half-updated columns. If you remove the nolock hint, the second query will never finish.

Surprising result! A half-updated XML column might break a (nolock) report, because the XML would be malformed.

Sql-server – SQL Server nvarchar(max) vs nvarchar(n) affects performance

You're seeing the cost overhead of using MAX types.

While NVARCHAR(MAX) is identical to NVARCHAR(n) in TSQL and can be stored in-row, it is handled separately by the storage engine because it can be pushed off-row. When off-row it is a LOB_DATA allocation unit, rather than ROW_OVERFLOW_DATA allocation unit and we can assume from your observations that this carries an overhead.

You can see the two types are internally stored differently with a little DBCC PAGE spelunking. Mark Rasmussen posted example page dumps that show the differences in What is the Size of the LOB Pointer for (MAX) Types Like Varchar, Varbinary, Etc?

We can probably assume it's the GROUP BY on the MAX column that causes the performance difference in your case. I've not tested other operations on a MAX type but it might be interesting to do so and see if similar results are seen.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 Dirty reads – how non-atomic

Sql-server – SQL Server nvarchar(max) vs nvarchar(n) affects performance

Related Question