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
- How Can I Skip a row(an iteration) in MSSQL Cursor based on some condition?
- C# SQL Update CommandText and Parameters
- Improve SQL Query to find redundant data
- Implementing Unique if not Null Constraint with SQLAlchemy and MS SQL Server
- Apply like function on an array is SQL Server
- Taking long time to fill data table from data adapter C#
- How are Severity and State in raiserror in T-SQL actually used in practice?
- Sample Employee SQL Server database with data
- get sql server to warn about truncation / rounding
- SQL SERVER converting dates with multiple formats
- Display all columns where instances of case_num column > 1 and ordering by amount desc per case_num
- Joining Two SQL Tables on Different Databases
- how to get day wise dynamically get data using sql server?
- Importing txt to SQL Server
- How to cope with SQL Server 2012 vs SQL (Azure) Database sql files
- Can't print report from SQL Reporting Services
- How to do a one-time load for 4 billion records from MySQL to SQL Server
- create same table in a if and else statement, which shows error in SQL Server
- SSIS - Odbc source don't take dynamic variable
- How to achieve Line Feed (\n) and Carriage Return (\r) with char(13)+char(10)
- How can i read out the Id with the name
- Slow Update vs Slow Select
- How to get transaction execute time in SQL Server?
- Which account is used when executing xp_cmdshell 'wmic ... "java -jar ..."'
- Not able to Deploy the SSIS Project to SQL Server
- Replace certain pattern in a long string in MS SQL using T-SQL
- Combine two tables which different number of rows in SQL server
- User defined function replacing WHERE col IN(...)
- Waiting for DB restore to finish using sqlalchemy on SQL Server 2008
- TSQL XML exists query with namespaces and IF-ELSE condition
- How to get a list of database names from a SQL Server instance using Entity Framework?
- Can you use "on-the-fly" column names in SQL WHERE clasue?
- SQL server update table with missing values
- Maximizing SQL Server Service Broker Throughput
- nextval and curval confusion in PostgreSQL
- Join a table to itself
- Sorting Multiple Parent/Child on Recursive SQL Query
- PowerBI / SQL : incorrect syntax
- Group By Only For One Column
- Select Statement Results Side by Side
- String or binary data would be truncated. — even though the column length was increased
- Query to get output as I need
- Concatenate Two Values On Insert - SQL
- How would I figure out many Groups have Customers with different countries
- Connecting to an SQL database using only Ajax?
- How to write the T-SQL STRING_AGG function
- How to do I connect to a SQL Server instance from PHP 7 on an AWS Amazon Linux EC2 instance?
- Update several records in a table with a single SQL Server MERGE (from XML)
- What impacts performance on odbc_fetch_array()
- Visual Studio 2010 Load Testing: MySQL as results repository
- when to prefer pessimistic model of transaction isolation over optimistic one?
- Take 1 hour from time field
- case statement in SQL, how to return multiple variables?
- SQL connection failure and network reliability
- sql error : only one expresssion can be specified in the select list?
- How to do a batch STDistance using a Table Type with Lat Longs?
- SQL Join based on code embedded in a table
- Stored Procedure Divide by Zero Despite the Results Being non-zero
- sql 2005 force table rename that has dependencies
- backup database mysql using php
- How to add a word to a row in crystal Report in C#
- SQL Server query XML datatype performance issue
- SQL Server : Json Column Query/Select Based On Value Within Json Array
- Create Unique constraint for 'true' only in EF Core
- Optimization of a T-SQL Query with probability for selection
- SQL Server runs very slow when called from .NET application
- Perl DBI / MS ODBC Driver (LinuxL:RHEL) / SQL-Server: How to insert/update BLOB varbinary(max) data?
- MSSQL parameterized query causes "Incorrect syntax near the keyword 'from'"
- SQL Server XML Processing: Join different Nodes based on ID
- SSIS component failure does not work
- Call to a member function query() on a non-object
- need empty row in output between rows with data Report Builder 3.0 T-SQL
- Is there any way to find the largest value for any numeric column over all in a table?
- How to increase the performance of the query
- SQL - any performance difference using constant values vs parameters?
- Trustworthy is ON with UNSAFE assemblies not running
- SQL Server: how to optimize "like" queries?
- Convert binary to varchar
- linqTOsql returning a "specified cast not valid" exception at runtime
- Negative database IDs in SQL Server
- How to Check table Exist or Not and then Create a table if not Exist?
- Performance issue with SQL Server and XML datatype columns
- MVC - ALTER TABLE DROP COLUMN failed because one or more objects access this column
- SQL Server, getting "for xml" value
- How to find all the dependencies of a table in sql server
- Joining multiple tables (many-to-many relationship)
- Updating column with value from other table, can't use distinct function
- SqlServer constrain field value dependent on another field value
- Saving image to the database
- SQL Server Select into Specific DataFrame Format
- How can I change data type of a column in SQL Server?
- Getting a incorrect WHERE syntax with my sql statement
- SQL sub query select doubts
- PHP not inserting into database SQL SERVER 2008
- SQL Server Window Function assign groups by order based on whether or not the row before or after is of the same type
- Autoincrement of primary key column with varchar datatype in it
- Conversion of 12 Hour Clock to 24 Hour Clock in SQL Server
- How can I get the list of columns from a temporary table?
- Why SQL Server unexpectedly stops issuing range locks
- there is already an object named '#yyyx' in the database
- Merging multiple rows with same ID in SQL Server
- Updating a production database with SQL encryption - mass update shortcuts?
- Get Weeks in SQL
- From stored procedures to REST services
- How to import SSIS package from file folder in integration services catalogs?
- Where is the SQL Query Analyzer in SQL Server Management Studio 2012
- Optimize IQueryable query to let EF generate a single SQL query instead multiple. Child collection of an entity must contains a custom collection
- SQL primary key - complex primary or string with concatenation?
- Mondrian Error:Internal error: No server instance has id
- asp.net connection string issue (database is on server side)
- Show Lao language in SQL Server 2014 Management Studio
- NULL Values on query condition
- Connection between IIS App Pool Identity and SQL Server
- Can you pass a variable into the interval of DATEDIFF
- How to sort part of the table?
- Full-text Index stop list for Persian language
- TSQL- How to count number of instances
- row_number over multiple columns based on trigger
- Does SQL Server guarantee sequential inserting of an identity column?
- SQL: Group By on Consecutive Records Part 2
- Using Full-Text Search in Linq / ODataController
- Null value being returned from a Stored Procedure using Linq to SQL
- access lookup and yes/no proper techniques?
- A required file could not be downloaded while installing SQL Server 2019 Developer
- Recursive SQL Query not give correct results
- Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column
- bulk insert by dynamic file name
- SQL server bulk insert rowterminator failed
- Return primary key of inserted row using SQLAlchemy & SQL Server
- Where's the rest of the space used in this table?
- Pyodbc MS SQL Server - Chinese turns into gibberish
- How do I filter and sum only negative or positive numbers in a field using an expression in SQL Reporting Services 2014
- USE and UNION ALL
- Where can I get a simple table of time zones for use in SQL server?
- How do I make a cross table select in SQL?
- How to convert File in byte array[] and store in database using Angular & ASP.Net Core?
- How to fill encrypted columns in database using SqlBulkCopy?
- SQL Server indexing includes questions
- Why script doesn't work in SQL Server (2005, 2014, ...) the first time
- SQL select latest record of particular student with particular status
- EF Core 1.0.0 chaining Where clauses
- Special Characters - Sql
- Getting Arguments from Hangfire.Job table
- Format time using 12 hour notation
- Replace cursors with queries
- Is there any editable Auto-Increment besdies IDENTITY?
- SQL Server to return set of top 10 records
- Connecting Xamarin.Android with SQL Server Database
- MSSQL connection string - Windows Authentication using another set of credentials
- identify insert stored proc