CODEWITHSUNDEEP
sql-serversql-server-2012
SE1986
SE1986

Reputation: 2750

Where is the definition of the view stored in SQL Server

I can create a table like this:

CREATE TABLE Test
(
    ID INT
)

and then create a view to reference this table:

CREATE VIEW vwTest
AS
SELECT * FROM Test

I can then select from the view 

SELECT * FROM vwTest

and this returns all the results for the ID column

I can then modify the underlying table:

ALTER TABLE Test
ADD SomethingElse NVARCHAR(10)

and then when I select from the view again, I still only see the ID column.

This leads me to believe that views are compiled upon creation, is this correct? I assume this is better for performance?

How does SQL Server store this compilation? Does it a keep a definition of all compiled objects somewhere in one of the system databases?

Upvotes: 2

Views: 4727

Answers (1)

Paul Karam
Paul Karam

Reputation: 4210

TL;DR;

When you create a view, SQL-server will store the view information in three separate sys tables: sys.views, sys.columns, and sys.sql_expression_dependencies.

When you alter the underlying table design, the view doesn't automatically get updated. You can use one of the three methods to update the View information:

  • Drop/Create the View.
  • sp_refreshview 'ViewName'
  • sp_refreshsqlmodule 'ViewName'

Looking at Microsoft Documentation, you can see that:

If a view depends on a table or view that was dropped, the Database Engine produces an error message when anyone tries to use the view. If a new table or view is created and the table structure does not change from the previous base table to replace the one dropped, the view again becomes usable. If the new table or view structure changes, the view must be dropped and re-created.

If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

When a view is created, information about the view is stored in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies. The text of the CREATE VIEW statement is stored in the sys.sql_modules catalog view.

Now to answer your questions:

How does SQL Server store this compilation?

It stores them in the following catalog views: sys.views, sys.columns, and sys.sql_expression_dependencies.

Does it a keep a definition of all compiled objects somewhere in one of the system databases?

Yes, it does. This is why you don't get the updated table design unless you drop/create your view again or by just running: sp_refreshview 'ViewName'.

After saying this, following your example. I created the table and the view. Here's the information from the catalog views:

select v.name,
       v.object_id,
       v.type_desc,
       col.name,
       col.column_id,
       sed.referenced_class_desc,
       sed.referenced_entity_name
FROM sys.views as v
inner join sys.columns as col
    on col.object_id = v.object_id
inner join sys.sql_expression_dependencies as sed
    on sed.referencing_id = v.object_id
where v.name = 'vwTest'

We get the next result:

name      object_id   type_desc   name           column_id   referenced_class_desc   referenced_entity_name
--------- ----------- ----------- -------------- ----------- ----------------------- -----------------------
vwTest    581577110   VIEW        ID             1           OBJECT_OR_COLUMN        Test

You can however refresh the view using sp_refreshview 'ViewName', after executing it, the above query will return:

name      object_id   type_desc   name           column_id   referenced_class_desc   referenced_entity_name
--------- ----------- ----------- -------------- ----------- ----------------------- -----------------------
vwTest    581577110   VIEW        ID             1           OBJECT_OR_COLUMN        Test
vwTest    581577110   VIEW        SomethingElse  2           OBJECT_OR_COLUMN        Test

As mentioned by @Gareth Lyons, sp_refreshsqlmodule 'ViewName' is also an option and will refresh the view details.

Upvotes: 5

Related Questions