How is a blob stored in an indexed view?

Question

Question

Assuming that I am doing an indexed scan of a table containing a column varbinary(max), will the binary content be physically copied to the indexed B-Tree view, or will the original fields be “referenced” in some way, without physically duplicating their contents?

In other words, if I do an indexed scan of a table containing a BLOB, will the duplicate storage needed for the BLOB be duplicated?

More details

When using a full-text index for binary data, for example varbinary(max), we need an additional filter-type column to indicate how to extract text from this binary data so that it can be indexed, for example:

CREATE FULLTEXT INDEX ON <table or indexed view> (
    <data column> TYPE COLUMN <type column>
)
...

In my particular case, these fields are in different tables, and I'm trying to use an indexed view to combine them, so they can be used in a full-text index.

Of course, I could copy the type field to the BLOB table and save it manually (while maintaining synchronization with the original), but I'm wondering if I can make a DBMS for this automatically, which would be preferable if there is a steep price for payment in terms of storage.

In addition, combining these two tables into one will have its negative consequences, and not go into details ...

Answer 1

will duplicate the storage needed for blob?

Yes. The indexed view will have its own copy.

CREATE TABLE dbo.T1
  (
     ID   INT IDENTITY PRIMARY KEY,
     Blob VARBINARY(MAX)
  );

DECLARE @vb VARBINARY(MAX) = CAST(REPLICATE(CAST('ABC' AS VARCHAR(MAX)), 1000000) AS VARBINARY(MAX));

INSERT INTO dbo.T1
VALUES      (@vb),
            (@vb),
            (@vb);

GO

CREATE VIEW dbo.V1
WITH SCHEMABINDING
AS
  SELECT ID,
         Blob
  FROM   dbo.T1

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON dbo.V1(ID)

SELECT o.NAME       AS object_name,
       p.index_id,
       au.type_desc AS allocation_type,
       au.data_pages,
       partition_number,
       au.total_pages,
       au.used_pages
FROM   sys.allocation_units AS au
       JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
       JOIN sys.objects AS o
         ON p.object_id = o.object_id
WHERE  o.object_id IN ( OBJECT_ID('dbo.V1'), OBJECT_ID('dbo.T1') ) 

+-------------+----------+-----------------+------------+------------------+-------------+------------+
| object_name | index_id | allocation_type | data_pages | partition_number | total_pages | used_pages |
+-------------+----------+-----------------+------------+------------------+-------------+------------+
| T1          |        1 | IN_ROW_DATA     |          1 |                1 |           2 |          2 |
| T1          |        1 | LOB_DATA        |          0 |                1 |        1129 |       1124 |
| V1          |        1 | IN_ROW_DATA     |          1 |                1 |           2 |          2 |
| V1          |        1 | LOB_DATA        |          0 |                1 |        1129 |       1124 |
+-------------+----------+-----------------+------------+------------------+-------------+------------+