View Disc Usage

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

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):

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

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

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');

StackExchange Database Administrators Q#36688, answer score: 5