Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is this statement correct?

Behind the scenes SQL Server actually stores the data in the view as a physical table which must be updated if any of the data behind the view changes

I did some searching but couldn't find any resources on how views work in SQL Server. I'd always thought that views don't take up any extra space for data unless you're using a indexed/materialized view.

Thanks

share|improve this question
3  
No the statement is not correct except for indexed views as you already mentioned. SQL Server just stores the view definition (and possibly algebrized trees) for non indexed views. No data. Where is the statement from? –  Martin Smith Mar 14 '13 at 13:26
    
Thanks Martin. I just wanted to double check –  Andrew Walters Mar 14 '13 at 13:30
add comment

2 Answers

up vote 4 down vote accepted

Your thinking is correct. Take the below example on a non-indexed view:

To create the test view:

use AdventureWorks2012;
go

create view dbo.DepartmentView
as

    select
        Name,
        GroupName
    from HumanResources.Department;

go

Now if you were to look at the execution plan of a SELECT on the view:

select *
from dbo.DepartmentView;

You can see below that the index of the underlying table is referenced (HumanResources.Department):

enter image description here

Likewise, when you update the view, you will see similar behavior:

update dbo.DepartmentView
set GroupName = 'QA'
where GroupName = 'Quality Assurance';

enter image description here

You can also write a few queries against the system catalog views to show that the only thing that is persisted is the definition of the view:

-- see the object in sys.objects
select *
from sys.objects
where object_id = object_id('dbo.DepartmentView');

-- ensure there are no indexes related to this view object
select *
from sys.indexes
where object_id = object_id('dbo.DepartmentView');

-- the definition is persisted
select
    object_name(object_id) as object_name,
    definition
from sys.sql_modules
where object_id = object_id('dbo.DepartmentView');
share|improve this answer
add comment

The correct statement is the last one. Non indexed views take up only enough space for the code behind them. Indexes are only "materialized" if you index them. At that point they take up space depending on the amount of data, columns etc.

You can test this yourself by adding a view on a large table and see that the size of the database doesn't change significantly.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.