I have recently learned about sp_tableoption which gives you the ability to store "large value types out of row" for particular table.
A colleague of me told me if I enabled it, each field is stored on 8 KB page at least, no matter how large it is. I was not able to find a confirmation of this, so I run the following test:
DROP TABLE Size
CREATE TABLE Size
(
[ID] TINYINT
,[Text] NVARCHAR(MAX)
)
EXEC sp_tableoption 'Size' ,'large value types out of row' ,0
TRUNCATE TABLE Size
INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 2, N'' -- add here very large string
SELECT [ID], DATALENGTH([Text]) Size, LEN([Text]) [Len]
FROM [dbo].[Size]
EXEC sp_spaceused Size;
DROP TABLE Size
CREATE TABLE Size
(
[ID] TINYINT
,[Text] NVARCHAR(MAX)
)
EXEC sp_tableoption 'Size' ,'large value types out of row' ,1
TRUNCATE TABLE Size
INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 2, N'' -- add here very large string
SELECT [ID], DATALENGTH([Text]) Size, LEN([Text]) [Len]
FROM [dbo].[Size]
EXEC sp_spaceused Size;
and get following results:
So, it seems that really the first row is now occupying more space (8 KB exactly). So, I add one more row and do the test again:
INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 1, N'in row text'
UNION ALL
SELECT 2, N'' -- add here very large string
The result was the same. So, it is not true that each row is occupying at least 8 KB, but the size is actually increased. I know that:
varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.
But this 16 byte pointer can be ignored in this case.
Can anyone tell is there a way to determined how much space overhead small
values are going to cost if the option is true?
I am planning to apply this option to a large table and want to calculated how the table size will be affected.
Note, I know that the value is moved out of the row
when it is inserted or updated only.