Sql-server – View Disc Usage

disk-spacesql serverview

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

Best Answer

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

Related Solutions

Sql-server – Indexed view in SQL Server

I build indexed views in SQL Server all the time to tune existing products. The optimizer is smart enough to use the index if you are utilizing the appropriate columns.

Using your example, it looks like you created the view but did not actually create an index upon it.

if object_id(N'mytable1') is not null 
drop table mytable1
if object_id(N'mytable2') is not null 
drop table mytable2
go

Create table mytable1 (ID int identity(1,1), Name1 nvarchar(100))
GO
Create table mytable2 (ID int identity(1,1), Name2 nvarchar(100))
GO

insert into mytable1 values ('steve')
insert into mytable1 values ('jack') 
insert into mytable1 values ('mike') 
insert into mytable1 values ('ralph') 
insert into mytable1 values ('simon')

insert into mytable2 values ('smith')
insert into mytable2 values ('jackson') 
insert into mytable2 values ('mikaelson') 
insert into mytable2 values ('montalvo') 
insert into mytable2 values ('singer')
go

if object_id(N'myview') is not null
drop view myview
go

Create view myview 
with schemabinding 
as 
select a.id, a.name1, b.name2
from dbo.mytable1 a 
join dbo.mytable2 b on a.Id = b.Id
GO

select a.name1, b.name2
from mytable1 a join mytable2 b on a.Id = b.Id
GO

Since there is no index on this view, we scan on the base tables:

But once we add an index, the optimizer can use it:

CREATE UNIQUE CLUSTERED INDEX [ix_cl_names] ON [myview]
(
    [name1] ASC,
    [name2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This appropriately used the view:

I can't change all my SQL scripts to select from the view instead of tables. I want to create indexed views and force SQL Server to get data from them instead of tables.

There is no hint or other method to force SQL Server to use an indexed view when it is not referenced in the query.

Additional information (from Geoff Patterson)

One extra point is that while the optimizer can, in Enterprise Edition only, use the indexed view in this case, it may make sense to directly reference the view using the NOEXPAND hint if you need to be 100% sure of the view index being used or if you ever want it to be used in Standard Edition.

I've frequently seen queries even in Enterprise Edition where the optimizer does not pick up on the fact that the view index can be used unless NOEXPAND is used. It's more common with complex queries, but can happen with simple queries also.

Paul White has one of the better articles I've read exploring the nuances of NOEXPAND; beyond just usage of the view index, the hint can also impact things like whether statistics are automatically created on the indexed view and cardinality estimates for the plan.

And from Zane: As a side note, be careful with indexed views as like any other index it will add to your update, insert, and delete times.

PostgreSQL – Creating a Database View Listing Distinct Days

create materialized view foo as 
  select distinct machine_code, date(datetime) from thing;

create unique index on foo(machine_code,date);

Beware that the conversion of datetime to date will occur in the timezone of whoever last created or refreshed the MV. You might want to use this instead:

create materialized view foo as 
  select distinct machine_code, date(datetime at time zone 'GMT+1') from thing;

But that might not do what you want with daylight savings time.

Best Answer

Related Solutions

Sql-server – Indexed view in SQL Server

PostgreSQL – Creating a Database View Listing Distinct Days

Related Question