What does WITH VIEW_METADATA option do with views in SQL Server?

Question

I am using SQL Server 2012.

The definition is:

WITH VIEW_METADATA
When specified the metadata of the view is returned instead of base table

This is the query:

alter view dbo.sales(vi)
with view_metadata
as
   select o.[SalesOrderID] 
   from [Sales].[SalesOrderHeader] o 

   select * from dbo.sales

Returns:

vi
43698
43699
43700
43701

and when changed to WITH SCHEMABINDING also generates the same result.

Could somebody please tell me the meaning of WITH VIEW_METADATA and how it is different from other view options?

Answer 1

VIEW_METADATA

When client-side APIs (for example: DB-Library, ODBC, and OLE DB APIs, ORM tools) request SQL Server the metadata information, also known as Browse-mode metadata, for a query that references the view, SQL server returns the base table names, which the view is made up of, when describing columns in the result set.

In certain scenarios, for example:

1. For security reasons you want to hide the base table names from the client application, OR
2. The client application because you restructured a former base table into a new structure and organization in your DB and used a view of the same name make the schema appear the same to the app. But the app is trapping the base-table name of the columns, which is no longer correct

you can create the view with VIEW_METADATA option.

VIEW_METADATA causes the SQL Server to return the view name when describing columns in the result set and hide the base tables from the client application.

You would not see any differences with the view with VIEW_METADATA or without VIEW_METADATA when interacting from within SSMS.

See Practical use of VIEW_METADATA on views?Any? on SQL Central for more discussion

SCHEMABINDING

If a view is created with schemabinding, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. For example:

create table  dbo.SomeTable  (
     c1   int  null,
     c2   int  null
    ) 
go

create view dbo.SomeView with schemabinding 
as
    select c1 from dbo.SomeTable
go

alter table dbo.SomeTable drop column c1
go

--Returns error:
--  Msg 5074, Level 16, State 1, Line 13
--  The object 'SomeView' is dependent on column 'c1'.
--  Msg 4922, Level 16, State 9, Line 13
--  ALTER TABLE DROP COLUMN c1 failed because one or more objects access this column.

Now remove the schemabinding option from the view and retry:

alter view dbo.SomeView
as
    select c1 from dbo.SomeTable
go

alter table dbo.SomeTable drop column c1
go
-- Works fine, but any further references to the view return errors.

select top 10 * from dbo.SomeView 
--  Msg 207, Level 16, State 1, Procedure SomeView, Line 3 [Batch Start Line 30]
--  Invalid column name 'c1'.
--  Msg 4413, Level 16, State 1, Line 31
--  Could not use view or function 'dbo.SomeView' because of binding errors.

Answer 2

With SCHEMABINDING is extremely common because it locks up the underlying tables from changing the structure of the table so the view will continue to work. Without it other developers could drop a column that is used in the view breaking the view.

'With view_metadata' provides clients metadata about the view that the client needs to create cursors against the view. Its not used nearly as often as SCHEMABINDING. https://technet.microsoft.com/en-us/library/ms187956%28v=sql.110%29.aspx

When you are in SSMS doing a select neither of these two parameters have much of an effect and you see the same data and column.