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 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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Database script not executing in SQL Server 2008
- T-SQL inner query like join based off IN results
- SQL Update & INNER JOIN
- select Count from Table
- cross tab query
- Execute sql script and not wait for completion
- MS Access / SQL Server - VBA: Upload local file to filestream on remote SQL server
- Convert right outer join to Linq query in EF
- SQL Server / incremental column per grouping
- Entity Framework : two inner joins on the same table
- How to reference old column value in SQL Server?
- Need to subtract two columns using SQL
- MSSQL - Question about how insert queries run
- Rename the columns of a SQL table with field values of a table
- SQL Server- if else/case when statement
- Is a SQL Server recursive CTE considered a loop?
- UPDATE heap table - Deadlock on RID
- Select first record from each group in a table based on a state column switching between 0 and 1 using SQL
- Performance Of Data Storage Objects
- Confusion regarding nchar in SQL Server
- Accessing SQL Server on Windows Server 2008 R2 from remote client
- What will happen when i update table with same values again and again?
- Finding the T-SQL to return these values
- Using Local Variables in a CTE causes drastic Slowdown
- Geometry operations on latitude/longitude coordinates
- Kafka-Connect For MSSQL Invalid value java.sql.SQLException: No suitable driver found for for configuration
- NLog database target and SQL Server application role
- Not getting SSRS report output in desired format
- How to code a nested sql statement to get row number of a specific item in mssql?
- Msg 6522 "Cannot load dynamically generated serialization assembly" doing XML Serialization
- SQL - How to pivot and combine data across rows
- How do I configure a new SQL Server login for authentication using Visual Studio?
- How to GROUP BY DATETIME column in SQL
- Publishing Databases using DacPac in Visual Studio 2013
- SQL sort ascending but null values descending
- Create automated installer of VB.Net program + SQL Server & script to create the DB & user
- Select Combination of columns from Table A not in Table B
- ExecuteScalarAsync hangs but ExecuteScalar returns instantly
- Able to update dataset but unable to commit changes to Database
- SQL Server 2008R2 from Table to edges for Graph
- Is it possible to add id column just result of sql query
- Can you explain the use of sys.sp_addextendedproperty in the following code?
- Connect to OLEDB data source from Java?
- SQL Server vs MySQL, is sargability the same?
- How should this expression be changed in order to perform an IF THEN causality?
- Ambiguous column name error
- T-SQL Consolidate and merge two rows into one
- SQL Server data to php
- How to merge contiguous intervals in SQL Server
- Sql Service Broker, CLR Intergration, Triggers back end problem
- Parsing XML fro URL using T-SQL
- Compatibility mode allows new command?
- How to configure Azure VM for SSIS package using FTP task
- Avoid NULL columns using DEFAULT Empty String
- In MSSQL Server 2008 and later, any major differences between "sp_addlinkedserver" and using registered servers?
- SQL LEFT JOIN on three table with condition
- When SSIS is saving a PDF from an SSRS, the PDF is corrupted
- SQL 'stuff' and 'FOR XML PATH' generating strange symbols
- Read Committed Data Without Waiting in SQL Server
- Using SELECT TOP from one column, then sorting on a different column
- SQL Server : select correct columns of duplicated records with group by clause
- Best way to retrieve pdf attachments saved as varbinary(max) from SQL Server and save to a local drive using C#
- Which data type should be used for saving images in database?
- Error while executing query in C#, SQL Server: trying to update if value already present else insert
- SQL with table name as parameter and query longer than 4000 characters
- getting images out of mssql in C# using streams
- SQL pivot nested
- Condition inside select query
- Copy one cell from one database to another
- How to get the sum of hours per week by activity using the sql time datatype?
- SQL Update: Cannot change one of composite key value of first record
- RubyODBC Cannot allocate SQLHENV
- SQL Conditional Unique Constraint With Where Clause Within Same Table
- How to get last 7 days data from current datetime to last 7 days in sql server
- How to get days from date to date in SQL Server?
- Are there any better search methods instead of string.Contains("keyword") using asp.net?
- Entity Framework 6 - GetExecutionPlan performance hit
- SQL Server Indexed Views vs Oracle Materialized View
- Remove extra spacing, line breaks, tabs from field
- Select count of rows that have a certain number of rows in a related table
- Remove duplicate columns from Query result
- Parallel workers with guarantee of processing
- Is there a way to specify the column length in a temp table through the use of a SQL parameter in SQL Server?
- Mapping existing tables to Domain classes in C#
- Calling a stored procedure to return value into variable
- SQL Server Login - failed login attempts
- SSMS (SQL) - sum function
- Join two tables on different databases
- Using variables in Oracle script
- DATE lookup table (1990/01/01:2041/12/31)
- VB.NET - Failed to convert parameter value from a String to a Guid issue
- ODBC continually prompts for password
- Refactor querying hardcoded values in function
- How to disable query execution plan caching per query in Entity Framework?
- Group by a list in the where clause and then show the row whether it has a value or is null
- XML INSERT INTO Query
- SQL ROUND() function issue
- Truncate specific number of characters in SQL cells
- Linking an address table to multiple other tables
- Finding averages across three tables in sql
- Get XML child node value with dynamic parent in SQL Server
- How to prevent stored proc from returning intermediate recordsets?
- Optimizing Delete on SQL Server
- how to Parameterizing sql sentence for grouping and filtering
- SQL query table with subquery range from second table
- How to do the dynamic precision from a table
- T-SQL Union with Dissimilar Columns
- MySql, Without loop filling a table with semi/random data
- how to implement crosstab in query with multi join and subqueries ? (SQL Server)
- T-SQL: cross apply; too many iterations on line items
- SQL How to select contents of a row directly above a row, and move both into a new table
- Temporary enabling of ON DELETE CASCADE
- Can you set the max an autoincrement will reach
- Norwegian character sorting wrongly in sql order by
- SSIS Insert continuous data from one server / database to another
- SQL Server remove records if group by returns same record more than 4 times
- SSIS Event (or something) when a package ends
- SQL Server record chained
- Convert nvarchar(50) to datetime (Second's part not coming after update the table)
- SQL Server: log modifications
- How to get Product Version of Microsoft Sql Server from .mdf file Header using C# code
- SQL Server - Table Metadata
- Execution times of subquery for an IN statement
- Clustered and nonclustered indexes performance
- How to have query return samples of row values as columns?
- Delete Records Based on Query Output
- Chunking a FileUpload in asp.net
- Can't understand this query
- Transform an entire database varchar to uppercase
- R - Test whether a Data.Frame Cell is NULL/empty
- SQL Server 2005- Investigate what caused tempdb to grow huge
- Cannot find data type boolean
- Unit Testing Database Driven .NET Applications
- How would a single script truncate a particular table in every database?
- Unable to login to sql server
- Installed Service Pack 3 for SQL Server 2005 but it does not show up when selecting @@version
- Aggregate function in SQL Update query
- Calculate MTD using CTE and Projected Sales in SQL
- SQL IsNumeric(), shouldn't it return a bit?
- How to check if a field exists in SQL subquery?
- Using SQL Server Express edition instead of Compact edition as local database cache?
- Assign default value to a datacolumn in a select statement
- How to match/compare values in two resultsets in SQL Server 2008?
- Incorrect syntax near SET in SQL Server 2000 stored procedure
- MS Visual Studio existing saved sql files cannot access saved database connection from server explorer
- Choosing only rows where one of the columns has a value
- Invalid Column Name on EF7 - RC2
- What is the storage hit for only using 10 character of a nvarchar(100) vs a nvarchar(255)?
- SQL ORDER BY CASE + ORDER BY ID DESC
- I am having difficulties getting my query to work correctly. Can you please assist?