I have a table where I add sparse columns dynamically:

CREATE TABLE [dbo].[my_table](
    [id] [BIGINT] NOT NULL,
    [column_set] XML COLUMN_SET FOR ALL_SPARSE_COLUMNS)

I add sparse columns at runtime with the following SQL:

ALTER TABLE my_table ADD my_sparse_column ... SPARSE

I want to create the SQL view for this table:

CREATE VIEW [dbo].[v_my_view]  
AS  
SELECT v.*       
  FROM my_table v

However I cannot query data from my sparse columns when I use the view:

SELECT my_sparse_column FROM v_my_view

However, I receive such an error:

This query works fine when executing it on the original table.

Is it possible to make it work?

CodePudding user response：

This behaviour is documented for SPARSE columns when there is a COLUMN_SET present.

Warning:

Adding a column set changes the behavior of SELECT * queries. The query will return the column set as an XML column and not return the individual sparse columns. Schema designers and software developers must be careful not to break existing applications. Individual sparse columns can still be queried by name in a SELECT statement.

So the view will never contain that column, unless you specifically select it, not just using select *.

There is another issue that you would get even if it wasn't SPARSE.

You are adding the column after creating the view.

You need to then run the following statement:

EXEC sp_refreshview N'dbo.v_my_view';

When creating a view, the view is parsed into a compiled expression tree (without any optimizations). Then, when you use the view, the compiler does not simply dump the view text into the outer query. Instead, it parses the outer query into an expression tree, and uses the expression tree from the view in the correct place.

So when you add a column, the expression tree is not updated. So you need to refresh the view definition.

You also need to rebuild any stored procedures which access this table or the view, for the same reason.

EXEC sp_refreshsqlmodule N'dbo.YourProc';

db<>fiddle