3

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

  • 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
5

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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