Difference between view and indexed view or materialized

Hello, welcome to Appsloveworld Technologies, In this session will learn about indexed views before continuing with the session. I strongly recommend reading the below two post of this series.

• What is a view and the advantages of views with an example
• Updatable views in sql server

what is an indexed or materialized view ,what actually happens when you create an index on a view?

A standard non-index view is just a stored SQL query. When we try to retrieve data from this view, the data is actually retrieved from the underlying base tables.

So a view is just a virtual table. It doesn’t really store any data by default. However, this default behavior can be changed by creating an index on that view.

So once we create an index on a view, the view gets materialized, which means a View is now capable of storing data.

In SQL Server we call these indexed views and an oracle they are termed as materialist views.

Let’s look at an example.let’s say I have an event organization company and I want to sell my event ticket.

For that, we have TicketDeatil table, which has got Ticket name, ShowTime, unit price, and TicketType i.e Bronze Silver & gold columns.

This table stores the master Ticket details. and then I have a TicketSale table which stores the sales of Tickets so basically this table stores the Ticket sales data.

Now, based on these two tables, I want you to create a view that can result in below-aggregated data.

so I want you to write a view which can actually return the total sales and total transactions by ticket.

Now we wanted to create an index on this view so it can get materialized. So obviously if you want a view to being index, then there are certain rules that you should be following.

Create view vWTotalSaleByTicket
with SchemaBinding
as
Select TicketName,
SUM(ISNULL((TotalQuantitySold * UnitPrice), 0)) as TotalSales,
COUNT_BIG(*) as TotalTransactions
from dbo.TicketSale
join dbo.TicketDetail
on dbo.TicketDetail.Id = dbo.TicketSale.TicketId
group by TicketName

if you look at the implementation of The View, we are saying the create view name and we are using “with schema binding option“.

• Now the first rule is that the view should be created with a schema binding option. If you are planning to create an index on that view.

So if you’re planning to create an index on a view, then that view should be created with schema binding option.

• The second rule is that if an aggregate function in select list references an expression, and if there is a possibility for that expression to become null, then a replacement value should be specified.

if you look at the view, we’re actually using an aggregate function Sum() and why we are using that because we want to compute the total sales made.

Create view vWTotalSaleByTicket
with SchemaBinding
as
Select TicketName,
SUM(ISNULL((TotalQuantitySold * UnitPrice), 0)) as TotalSales,
COUNT_BIG(*) as TotalTransactions
from dbo.TicketSale
join dbo.TicketDetail
on dbo.TicketDetail.Id = dbo.TicketSale.TicketId
group by TicketName

So we are using the Sum() function. And obviously, if you want to calculate the total sales, how will you calculate that we will multiply the quantity sold, but the unit price .

So here is the quantity and unit prices and the TicketDetail. So we have to multiply unit price with quantity sold and then sum that for each row in this TicketSale table.

that’s the reason why we are using the aggregate function and we are multiplying quantity sold with unit price.

Now, let’s say for some reason, TicketId or quantity sold is Null, and then when you multiply that this expression could return NULL.

So the second rule states that if we use an aggregate function in the select list, and if that aggregate function is making use of expression and if there is a possibility for that expression to become null, then you should specify a replacement value.

if this expression becomes null what is the replacement value that I want to give to the sum() function instead of giving it null I want to give zero.

SUM(ISNULL((TotalQuantitySold * UnitPrice), 0)) as TotalSales

• Another important rule is if you use a group by in your view,then the view select list must contain a COUNT_BIG() function.

we want to group by the ticket, the total sales, and total transactions. we are using a group by clause.

so obviously we actually need to count the total number of transactions. So we are using COUNT_BIG().

You can also use the count() function. But the problem is if you are planning to create an index to view, you cannot use count(). You will have to use the COUNT_BIG() function.

• Base tables in the view should be referenced with a two-part name. So here this view has two base tables, TicketSale and TicketDetail.

So when you are referring to these two tables, in this view, you should use the two-part name, the schema name, and the table name. You cannot use one part without schema or you cannot use a three-part name or four-part name. You will have to use only the two-part name. This schema name, dot the table name.

from dbo.TicketSale
join dbo.TicketDetail
on dbo.TicketDetail.Id = dbo.TicketSale.,TicketId

as you can above we are using dbo. table name. if we will not use the two-part name let’s see what happens.

it will give the error
“Msg 319, Level 15, State 1, Line 6
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an XML namespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.”

These are only a few of the rules. But for the complete list of rules, please visit Microsoft.

We have just created a view and we are executing the select statement on that view.

So when we execute select * from vWTotalSaleByTicket query, what is actually happening as it stands right now, this particular view is not storing any data.

when we execute select * from view, what is actually happening behind the scenes, the database engine will actually execute the select statement, which means the query has to go back to base tables and then it has to compute the sum total transactions, everything, and it has to grow by them.

so it has to every time you say select * from vWTotalSaleByTicket it has every time to compute those.

So obviously if your ticket sales table is huge because just imagine, companies like Amazon, Flipkart, Wallmart, etc., they will be making millions and millions of transactions.

if you want to sum them up every time you execute a query, just imagine how much time it’s going to take to compute those results.

But here, since we haven’t created an index on this view, the query will have to go back to the base tables and evaluate this expression every time you execute the query

we can change that view by creating an index on the View.

Create Unique Clustered Index UIX_vWTotalSaleByTicket_Name
on vWTotalSaleByTicket(TicketName)

And remember, the first index that you create on a view should be a unique clustered index.

And it makes sense because this view itself as it stands right now, it doesn’t store any data. So you cannot create a non clustered index if it doesn’t already have any data at all in that view.
So the first index that you’ll have to create should be a unique clustered index.

Now when I execute select * from vWTotalSaleByTicket query, it will return that data from that index itself. It doesn’t have to go back to the base tables to compute that.

Any time a transaction is made and if there is a row entered into this table, the view is also recompute and updated. So any time people issues to select * from vWTotalSaleByTicket, the calculations are done pretty fast and it can significantly improve the performance of your query.

So these indexed views are ideal for OLAP systems in a data warehouse and environment systems where the data is not frequently changed.

Mainly the data in a data warehouse is used for reporting and analysis purposes. maybe every week or every night they just batch load the data and then it gets reprocessed.

But then the best way to load the data when you have it index views is to basically drop those index views, load the data and then recompute or recreate those index views, which will definitely reduce the amount of time that it will take to process the data.

So these index views are suitable for environments where the data doesn’t frequently change. But for OLTP systems, index views might affect the performance because they change on a constant basis.

Whenever a new transaction is made and we have to continuously read the indexed view, which could actually affect the performance.

Sql table script

GO
/****** Object: Table [dbo].[TicketDetail] Script Date: 09/30/2020 7:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TicketDetail](
[Id] [int] NOT NULL,
[TicketName] [varchar](50) NOT NULL,
[ShowTime] [datetime] NOT NULL,
[UnitPrice] [float] NOT NULL,
[TicketType] [varchar](50) NULL,
CONSTRAINT [PK_TicketDetail] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TicketSale] Script Date: 09/30/2020 7:57:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TicketSale](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TicketId] [int] NULL,
[TotalQuantitySold] [int] NULL,
CONSTRAINT [PK_TicketSale] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY]
GO
INSERT [dbo].[TicketDetail] ([Id], [TicketName], [ShowTime], [UnitPrice], [TicketType]) VALUES (1, 'Dog Event Ticket', CAST('2020-08-19T00:00:00.000' AS DateTime), 20, 'Bronze')
GO
INSERT [dbo].[TicketDetail] ([Id], [TicketName], [ShowTime], [UnitPrice], [TicketType]) VALUES (2, 'Night Club Party Ticket', CAST('2020-08-19T00:00:00.000' AS DateTime), 10, 'Bronze')
GO
INSERT [dbo].[TicketDetail] ([Id], [TicketName], [ShowTime], [UnitPrice], [TicketType]) VALUES (3, 'Tomorrowland music festival', CAST('2020-08-19T00:00:00.000' AS DateTime), 15, 'Silver')
GO
INSERT [dbo].[TicketDetail] ([Id], [TicketName], [ShowTime], [UnitPrice], [TicketType]) VALUES (4, 'La Tomatina Festival', CAST('2020-08-19T00:00:00.000' AS DateTime), 20, 'gold')
GO
INSERT [dbo].[TicketDetail] ([Id], [TicketName], [ShowTime], [UnitPrice], [TicketType]) VALUES (5, 'Christmas party events', CAST('2020-08-19T00:00:00.000' AS DateTime), 50, 'Bronze')
GO
INSERT [dbo].[TicketDetail] ([Id], [TicketName], [ShowTime], [UnitPrice], [TicketType]) VALUES (6, 'New year''s eve events', CAST('2020-08-19T00:00:00.000' AS DateTime), 100, 'Bronze')
GO
SET IDENTITY_INSERT [dbo].[TicketSale] ON
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (1, 1, 4)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (2, 3, 3)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (3, 2, 4)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (4, 1, 7)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (5, 3, 5)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (6, 4, 8)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (7, 5, 9)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (8, 6, 5)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (9, 4, 1)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (10, 1, 10)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (11, 2, 40)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (12, 3, 25)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (13, 5, 12)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (14, 1, 13)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (15, 2, 10)
GO
INSERT [dbo].[TicketSale] ([Id], [TicketId], [TotalQuantitySold]) VALUES (16, 3, 4)
GO
SET IDENTITY_INSERT [dbo].[TicketSale] OFF
GO

 

The post Difference between view and indexed view or materialized appeared first on Software Development | Programming Tutorials.