Route Parameter In Razor Pages Sagar Jaybhay

In this article we will understand Route Parameter In Razor Pages By Sagar Jaybhay.

Route Parameter:

Another way to pass value from is route template. If you see the above image you have seen that the id parameter is passed as a query string parameter. But we want to pass as route template to do this we need to add below code in our Display template of Details .cshtml page.

@page "id"

By doing so our URL will become like below

Here are URL contains id but not as a QueryString parameter. If you want to make the id parameter as optional value then you need to add a Question mark after this like below.

@page " id?"

Now we want to change the route URL you can do so by using the route template right now in our application we go to details view and we want our URL like https://localhost:44316/employee/details/view/a101/  to achieve this modify route template like below and you can achieve this.

@page "view/id"

The output is shown in the below image. By doing so /view and /id is appended to default URL and URL looks like below

But this URL so long we want some custom URL like https://localhost:44316/employee/view/a101/ To achieve this URL we need to modify route template like below

@page "/employee/view/id"

The output of this shown in below image and we can remove details name in URL

If you want to include as many parameters uses below syntax the name present in curly braces are route parameters.

@page "/employee/view/id/name"

Here keep in mind Model binder binds route parameter to id parameter in OnGet() method.

But if you want to access the Id parameter in the Display template you need to create a public property and assign value to it in the OnGet method.

Now this property we use in our Display Template so our output looks like below

So here we need to assign route parameter value to public property in OnGet() method why because by default route parameter support for OnPost()
the method only but we want to access for OnGet() means get call also so we need to use below syntax for that and no need to assign value in OnGet() method. Code for this is written below

 [BindProperty(SupportsGet = true)]
 public string ID get; set; 

The bind property attribute binds route parameter value to ID property in Page template public property. By default, BindProperty Bind value to this public property only for Post request so we pass SupportsGet flag to true to support for Get() call also.

GitHub:- https://github.com/Sagar-Jaybhay/AspNerRazorPages

Query String Parameters In Asp.Net Razor 2020

In this article we will understand Query String Parameters its significance. How to Use Query String Parameters in Asp.Net Razor Pages ?

Query String Parameters

If we want to pass data from the Display template to Code
behind file which is Page Model in our case then we can use the
asp-data-parameter_name tag helper to pass data from the display template to
the Page Model class. This parameter catches in OnGet or OnPost method.

Now we have to achieve functionality like when we click
on View button we can get in detail information about Employee in table format.
For this, we need to create one Details Razor page in our Employee folder which
resides under Page folder.

First, we need to pass data from View for that use below the line of code.

 <tr><td><a class="btn btn-primary" asp-page="/Employee/Details" asp-route-ID="@emp.ID">View</a></td></tr>

Now we need to cacth this Id in OnGet method for that we pass parameter in OnGet() method.

public void OnGet(string id)
 

 

In this Model binding maps id parameter value to OnGet() method id parameter. And by default id is pass as query string parameter in URL.

We don’t have any method which can get Employee Information by using Id so we need to create this in Interface and then we need to implement this in our DBRepository class as we implement this interface in this class.

The code for Interface and DbRepository have shown below.

 public interface IEmployeeRepos
 
 IEnumerable<Employee> GetAllEmployees();
 Employee GetEmployee(string Id);
 

 public class DBRepository: IEmployeeRepos
 
 private List<Employee> _empList;
 public DBRepository()
 
 _empList = new List<Employee>() 
 new Employee() Dept=Department.HR,Email="sagarjaybhay@gmail.com",ID="A101",Name="Sagar Jaybhay",Photopath=@"sagar jaybhay.png",
 new Employee()Dept=Department.IT,Email="rani@hotmail.com",ID="A201",Name="Rani",Photopath=@"rani.jpg" ,
 new Employee() Dept=Department.Testing,Email="raja@gmail.com",ID="A301",Name="Raja",Photopath=@"ram.png",
 new Employee() Dept=Department.Testing,Email="raghu@gmail.com",ID="A401",Name="Ragu",Photopath=""

 ;
 


 public IEnumerable<Employee> GetAllEmployees()
 
 return this._empList;
 

 public Employee GetEmployee(string Id)
 
 return this._empList.Where(s => s.ID == Id).SingleOrDefault();
 
 

Here is one note: When you use the anchor tag element with an asp-page tag helper then not use default href attribute if you do so you will get an error. Following is the error.

An
unhandled exception occurred while processing the request.

InvalidOperationException: Cannot override the 'href' attribute for <a>. An <a> with a specified 'href' must not have attributes starting with 'asp-route-' or an 'asp-action', 'asp-controller', 'asp-area', 'asp-route', 'asp-protocol', 'asp-host', 'asp-fragment', 'asp-page' or 'asp-page-handler' attribute.

GitHub: https://github.com/Sagar-Jaybhay/AspNerRazorPages

Generic List Routing & Routing Constraints

In this article we will understand How to display Generic List In Asp.Net Razor Pages? How Routing Works in Asp.Net Razor Pages ? How to apply Routing Constraints in Asp.Net Razor Pages by Sagar Jaybhay.

Generic List Routing & Routing Constraints

Now see below image we need to display employee list like this image. We have Employee property present in our Index page model we access this in our view. Now iterating over this list we apply some bootstrap CSS and formatting to display like below.

Code in the Display template

@page
@model RazorApplication.Pages.Employee.IndexModel
@
 ViewData["Title"] = "Employees";


 <h1>Employees</h1>

<h3>No of Employees : @Model.Employees.Count()</h3>


<hr />
<br />
<div class="container-fluid">
 @foreach (var emp in Model.Employees)
 
 var imgSrc = @"images/" + (emp.Photopath.Trim().Length != 0 ? emp.Photopath : "noimage.png");
 <div class="row" style="border:thin 1px black">

 <div class="col-lg-4">
 <img src="@imgSrc" alt="@emp.Name" style="height:150px;width:150px" />
 </div>
 <div class="col-lg-4">
 <table style="border:thin;1px" class="table table-bordered table-active">
 <tbody>
 <tr>
 <td>Name</td>
 <td>@emp.Name</td>
 </tr>
 <tr>
 <td>Email</td>
 <td>@emp.Email</td>
 </tr>
 <tr>
 <td>Department</td>
 <td>@emp.Dept</td>
 </tr>
 </tbody>
 </table>
 </div>
 <div class="col-lg-2">
 
 <table class="table table-bordered">
 <tbody>
 <tr><td><a href="#" class="btn btn-info">Edit</a></td></tr>
 <tr><td><a href="#" class="btn btn-primary">View</a></td></tr>
 <tr><td><a href="#" class="btn btn-danger">Delete</a></td></tr>
 </tbody>

 </table> 
 
 
 </div>
 </div>
 <hr />
 <br />
 

</div>

See the above image in that we use this Employees Property in our Index.cshtml Display template.

Basics Of Routing In Asp.Net Razor Pages:

Routing is the URL pattern matching
techniques and in this, it matches URLs with razor Pages. It is like most of
page centric frameworks where URLs match with physical file paths. Keypoint to
remember it start with the Root folder and in this Pages is the root folder.

Like MVC Razor pages also use
Conventions and Configuration for routing. Razor pages use the same
infrastructure as MVC for routing.

The standard Razor Pages 3.x site
template includes 3 pages in the root folder.

1. Error.cshtml
2. Index.cshtml
3. Privacy.cshtml

By default, route templates are generated by taking the
root path of each Content Page and then it removes root folder name from the
start of path and extension from the end of the path.

In Asp.Net Razor pages Index.cshtml is the default
document present in any folder so it has 2 different routes one with “blank and
other with https://sagarjaybhay.com/index

So in our application, we created the Employee folder and
in that we Index.cshtml so our route becomes

1. “blank which is
   an empty string path is “https://yourdomain.com/Employee”
2. “Employee/Index”
   this is the second path https://yourdomain.com/Employee/index

But if you create Employee.cshtml in your root domain and
Employee folder is present in your Pages folder then when you run your
application it will throw an exception.

An
unhandled exception occurred while processing the request.

AmbiguousMatchException: The request
matched multiple endpoints. Matches:



/Employee

/Employee/Index

Microsoft.AspNetCore.Routing.Matching.DefaultEndpointSelector.ReportAmbiguity(CandidateState[]
candidateState)

How to handle this AmbigousMatch Exception in Asp.Net Razor pages?

1. One to overcome
   this error by renaming one of the file or folder names in our Asp.net razor
   page application.
2. The second way
   is to Overwrite default routes. We know that if Routes in Asp.Net razor pages
   are mapped to a physical file location.

@page "EmployeeList"

We are giving above custom route names to our page in Employee
folders Index.cshtml file and error is gone see below image

So by giving this custom route, you can’t use the default
route in our application means  https://yourdomain.com/Employee/Index
this route won't work.

1. The third way to
   overcome this error is used Route parameter in our application

@page "name"

In the above we give name is our route parameter and when
we invoke the URL

https://yourdomain.com/Employee/abc here ABC is our
route parameter.

Constraint on Route Parameter

If we see above URL we pass ABC to name parameter in Index view which can accept any value like character, number any value to add a constraint. Now we want our URL parameter to accept the only character then we have name:constraint_name syntax.

@page "name:alpha"

By doing this our URL only accepts the character and if we pass number it will throw an error.

GitHub:- https://github.com/Sagar-Jaybhay/AspNerRazorPages

What Is DataBase Normalization By Sagar Jaybhay

In this article we will understand What Is DataBase Normalization ? Different Types of Normalization forms By Sagar Jaybhay.

DataBase Normalization

Database
normalization is a process of organizing data and minimizing data redundancy
which in turn ensures data consistency.

The problem of data redundancies:

1. Data is duplicated
2. Disk space required more which is wastage
3. Data inconsistency
4. DML queries become slow(Insert, update, delete)

create table emps(empname nvarchar(20),gender nvarchar(20),salary float,deptname nvarchar(20),deptheaad nvarchar(20),deptlocation nvarchar(20))
insert into emps values('sagar','male',1000,'Hr','Raju','London'),
('A','female',2000,'IT','X','UK'),
('B','male',3000,'Account','Y','USA'),
('C','female',4000,'Support','Z','India');

Database normalization
is a step by step process. There is 6 normal forms that start from 1^st
normal form to the 6^th normal form.

But most
of the databases support up to 3^rd normal form.

In
general, normalization means broken down the table into multiple tables where
we can avoid data redundancies in which repeating columns or rows move to
another table.

Below is unnormalize table and we want to normalize this table.

In the
above case, you can see we are repeating the depthead and department name
column. Suppose this table has millions of records and in the future, our
department head will change then Raju to xyz then we need to update millions of
records and this is time-consuming and performance will degrade.

So if we remove this repeating rows into another table which decreases space requirement and time required for this is minimum.

1st Normal Form

• It means data in the column should be atomic and no column contains multiple data by comma-separated.

DeptName	EmpName
Hr	Sagar, Suresh, Ramesh
IT	X, y, Z

This not good.

• The table does not contain any repeating column groups.

DeptName	EmpName1	Empname2	Empname3
Hr	Sagar	Suresh	Ramesh
IT	X	Y	Z

• You can identify each record by the primary key.

Deptid	DeptName
1	HR
2	IT

Deptid	Empname
1	Sagar
1	Suresh
1	Ramesh
2	X
2	Y
2	Z

2^nd Normal Form(2NF)

1. The Table needs to meet the requirement of 1^st normal
   form.
2. Need to move redundant data to separate table
3. Create a relationship between these tables using primary key and
   foreign key.

EmpID	EmpName	Gender	Salary	DeptName	DeptHead	DeptLocation
1	Sagar	Male	10000	HR	X	India
2	Seeta	Female	20000	IT	Y	USA
3	Suresh	male	30000	Sales	Z	UK
4	Raju	Male	40000	Account	K	London

Now we are splitting the above table into 2 different tables which look like below here deptid is a foreign key by which relationship is achieved.

DeptID	DeptName	DeptHead	DeptLocation
1	HR	X	India
2	IT	Y	USA
3	Sales	Z	UK
4	Account	K	London

EmpID	EmpName	Gender	Salary	DeptID
1	Sagar	Male	10000	1
2	Seeta	Female	20000	2
3	Suresh	male	30000	3
4	Raju	Male	40000	4

3^rd Normal Form

1. The table needs to meet all the conditions in the first normal form
   and second normal form.
2. The table does not contain any column that not fully depend on the
   primary key of that table.

EmpID	EmpName	Gender	Salary	Annual Salary	DeptID
1	Sagar	Male	10000	120000	1
2	Seeta	Female	20000	240000	2
3	Suresh	male	30000	360000	3
4	Raju	Male	40000	480000	4

In the above table, the Annual salary table does not fully depend on empid. So there is no need for the annual salary you can compute this query so you can remove this column.

GitHub Profile:- https://github.com/Sagar-Jaybhay

How to create Models In Asp.Net Blazor

In this article you will understand How to create Models In Asp.Net Blazor By Sagar Jaybhay.

In Asp.Net Razor pages we don’t have models folder now to perform CRUD operation we will create the Model Class Library Project in our application. By creating a class library project it is easily used in any other project like web API, asp.net mvc.

Models In Asp.Net Razor Pages:

public class Employee
 
 public string ID get; set; 
 public string Name get; set; 
 public string Email get; set; 
 public string Photopath get; set; 
 public Department? Dept get; set; 

 

 public enum Department
 
 IT, HR, Support, Testing, Account
 

Models In Razor

In our, we added this.NetStandard class library projects with Employee as class and Department as an enum.

Now we will create a DataAccess Layer in
our application to do this we again create .Net standard Class library project.
In this, we create one Interface which has the GetAllEmployees method and which
returns a list of employees. After that, we create the DbRepository class which
implements the Interface. We create this for use of Dependency injection and
Inversion of control and to know more about this pattern use the below link.
https://sagarjaybhay.com/repository-pattern-asp-net-core-by-sagar-jaybhay/

Now we refer these projects in our main application and to check everything working fine we create the Employee field in our Index page and constructor we inject the IEmployeeRepos object by using dependency injection. Now we have to check the count of an employee. So in the Page Display template, we use the below code.

<h3>No of Employees : @Model.Employees.Count()</h3>

And our page model class looks like below

 public class IndexModel : PageModel
 
 public List<RazorPages.Models.Employee> Employees;
 public IndexModel(IEmployeeRepos employeeRepos)
 
 EmployeeRepos = employeeRepos;
 

 public IEmployeeRepos EmployeeRepos get; 

 public void OnGet()
 
 Employees = EmployeeRepos.GetAllEmployees().ToList(); 
 
 

After running this application we get below error output.

An unhandled exception
occurred while processing the request.

InvalidOperationException:
Unable to resolve service for type 'DAL.IEmployeeRepos' while attempting to
activate 'RazorApplication.Pages.Employee.IndexModel'.

Microsoft.Extensions.DependencyInjection.ActivatorUtilities.GetService(IServiceProvider sp, Type type, Type requiredBy, bool isDefaultParameterRequired)

To resolve this error we need to do the
following changes in our application. We
forget to register our service that’s why this error occurred.  The error occurred because we never initialize
Interface object with concrete type in our startup class configure service
method. It means that below is code for registering the service in the Configure
method.

To initialize this use the below code of configure service method

public void ConfigureServices(IServiceCollection services)
 
 services.AddRazorPages();
 services.AddSingleton<IEmployeeRepos, DBRepository>();
 

In the above code, we use the
AddSingleton method what is this? To learn about this visit this link to
understand better https://sagarjaybhay.com/what-is-the-difference-between-addtransient-vs-addsingleton-vs-addscoped-in-asp-net-core-mvc-by-sagar-jaybhay/

The output looks like below

GitHub Link:- https://github.com/Sagar-Jaybhay/AspNetRazor

CTE (common table expression) In Depth

CTE (common table expression) and Updatable CTE you will understand this in this article by sagar jaybhay in depth.

CTE (common table expression)

CTE Introduced in the SQL server 2005.

CTE is
like a temporary result set which is defined within the execution of the
current context or execution scope of single select, insert, update delete and
create view statement.

It is
similar to a derived table and it is not stored as an object like other objects
in the SQL server.

Remember CTE table is created with the keyword.

with CTEtable
as
(
select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name
)
select * from CTEtable
where 
empcount>100;

CTE

In the above query, we didn’t mentioned the column name if your inner query is given distinct column name then there is no need to define column name else you need to define like shown below

with CTEtable(deptname,empcount)
as
(
select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name
)
select * from CTEtable
where 
empcount>100;

CTE with column name defined

In the
above query, you specify 2 columns so remember you need to specify the columns
that select query is returning if our inner select query returning 3 columns
then you need to specify these 3 columns in CTE.

CTE is
only referenced by select, insert, update and delete statement immediately
follows the CTE expression.

In this, With clause, you can create multiple CTE tables.

with CTEtable(deptname,empcount)
as
(
select d.deptname as deptname, COUNT(e.id) as empcount from tbldept as d
join tblEmp as e on d.deptid=e.deptid
group by d.deptname
),
tblnew_hr(deptname,id)
as
(
select d.deptname,e.id from tblEmp e join tbldept d on
e.deptid=d.deptid
)
select * from CTEtable
union 
select * from tblnew_hr

Multiple CTE

Updatable CTE

It is
possible to update the CTE the answer to this is Yes or No.

If your CTE is based on a single table then you can update using CTE. Which in turn update the underlying table.

with update_cte
as
(
select id, name, salary from tblEmp
)

update update_cte set salary=5555 where id =2

select * from tblEmp;

Updatable CTE

If CTE is based on more than one table and updates affect only the base table then this is possible.

with update_mul_cte
as
(
select e.id,d.deptname,e.geneder from tblEmp e join tbldept d on e.deptid=d.deptid
)

update update_mul_cte set geneder='male' where id=2;
select * from tblEmp;

Multiple CTE Update

But if you are going to update data in both tables which are present in CTE it will throw an error.

with update_mul_cte
as
(
select e.id,d.deptname,e.geneder from tblEmp e join tbldept d on e.deptid=d.deptid
)

update update_mul_cte set geneder='male',deptname='fff' where id=2;
select * from tblEmp;

Update Multiple Table CTE Error

Below is
an error that is thrown by it.

Msg 4405,
Level 16, State 1, Line 11

View or function 'update_mul_cte' is not updatable because the modification affects multiple base tables.

---

GitHub Profile :- https://github.com/Sagar-Jaybhay

---

Working With Multiple Tables In MVC By Sagar Jaybhay

Insted Of Trigger Table Variable Derived Table

Insted Of Trigger Table Variable Derived Table about this you will able to understand in this article By Sagar Jaybhay In SQL Server.

Before read this article read below article First to better understanding.

Part 1: - https://sagarjaybhay.com/what-is-triggers-in-sql-sagar-jaybhay-part-1/

Part 2:-https://sagarjaybhay.com/update-trigger-instead-of-trigger-sagar-jaybhay/

Insted Of Trigger

Instead Of Update Trigger

Instead of an update trigger is similar to instead of insert trigger. It is fire when we are going to update the records in view and in below code, you will see I am using Update function which we get the value true or false if we trying to set that parameter value in our case we are trying to deptname so update(deptname) return true.

alter trigger tr_updateempdec
on vw_empdec
instead of Update
as
begin
declare @deptname nvarchar(20);
declare @deptid int;

select @deptname=deptname from inserted;
print('department is '+@deptname)

if(UPDATE(deptname))
begin
select @deptid=tbldept.deptid from tbldept join
inserted on tbldept.deptname=inserted.deptname;

if(@deptid is null)
begin
raiserror('dept is null ',16,1)
return
end;

update tblEmp set deptid=@deptid from inserted join tblEmp on tblEmp.id=inserted.id;
end

end;


update vw_empdec set deptname='Hr' where id=1;

above is the query of our view which we trying to update.

Instead of Delete trigger

This triggers also the same which is fire when someone tries to delete records from view.

Insted Of Delete Trigger

When you trying to delete records from view then we get the following error

Msg 4405,
Level 16, State 1, Line 6

View or function 'vw_empdec' is not updatable because the modification affects multiple base tables.

create trigger tr_deletetrigger
on vw_empdec
instead of delete
as
begin

delete from tblEmp
where tblEmp.id in (select id from deleted)
end;

delete from vw_empdec where id=1;

For avoiding this error we create above delete trigger which works perfectly fine.

Table Variable In SQL Server

It is like
a temporary table and table variable also created in tempdb

The scope
of table variable in batch, stored procedure and in the statement of blocks in
which it is declared.

Also,
table variables can be passed as parameters.

Below is
the syntax of the table variable.

When you use table variable you need to define the data type and column name in that table.

declare @tabledemo table(departname varchar(20),empcount int);


insert into @tabledemo
select d.Department_Name, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

select * from @tabledemo where empcount>100;

Derived Table In SQL Server

This
derive tables like a temporary table or table variable but it is available only
in the context of the current query.

Remember
when you derived a table you need to give a name for that table else it is not
working.

If you trying to access this derived table in another select statement outside the context of this query then it is not possible.

select deptname, empcount
from
(
select d.Department_Name as deptname, COUNT(e.empid) as empcount from Department as d
join Employee as e on d.DepartmentID=e.DepartmentID
group by d.Department_Name
) as derivedtable 
where 
empcount>100;

Derived Table In SQL Server

GitHub Link:- https://github.com/Sagar-Jaybhay

Lab 5 - Strongly Typed Model in Asp.Net MVC By Ssagar Jaybhay

What Is Triggers In SQL By Sagar Jaybhay Part 1

In this series of article you will understand What is Triggers in SQL Server and How many Different Types of Triggers Present in SQL Server by Sagar Jaybhay.

Triggers in SQL Server

In SQL
server there are different types of triggers are present

1. DML triggers
2. DDL triggers
3. Logon triggers

DML Triggers

This type
of trigger is fired automatically when DML query is executed on an underlying
table means Insert, Update or Delete query fired then DML triggers are
triggered.

DML stands for data manipulation trigger and it is fire when data is modified using this insert, update or delete query or command.

DML triggers are classified into 2 different types of triggers

1. After triggers / For triggers
2. Instead of triggers

After Trigger

The name itself suggests that when the query is executed after that after trigger will fire. This means that after triggering action the after trigger will fire. It means after complete execution of Insert, Update and Delete query the trigger will fire.

Instead Of Trigger

This instead of trigger will fire, instead of triggering action. This Insert, update and delete are causes to fire instead of trigger.

In
general, we can consider a trigger a stored procedure or function which can
trigger after some kind of triggering action.

When you create a trigger you can create this for a specific table and specific event.

After Trigger

This
trigger will fire after the operation is completed means insert, update or
delete.

From this
trigger, we get inserted row in which whatever the value we inserted table we
get this. Inserted is a table which is also called a Magic table which is
maintained by the SQL server and which retains a copy of row which we inserted
into the table. It is accessed inside the context of creating a trigger

We are having simple student table in that 5 rows and we are creating after triggering on insert by which trigger will fire when we insert a row and whatever value inserted we get this simply by using select * from inserted; query in the trigger. Below is a query for that trigger

create trigger afterinsert
on studenttable
for insert
as
begin
select * from inserted
end;

AfterInsert Trigger

In this
above image, you can see when insert command completed successfully the select
will fire.

Now we have to add newly added row into another table how we do that

alter trigger afterinsert
on studenttable
for insert
as
begin
declare @id int;
declare @fullname nvarchar(2000);
select @id=Id from inserted;
select @fullname= first_name from inserted;

insert into afterinserttable values(@id,'name is '+CAST( @fullname as nvarchar(100)) + cast(GETDATE() as nvarchar));
select * from afterinserttable;
end;

insert into studentTable values(7,'sagar1','jaybhay1','sagar1@sagarjaybhay.net','Male','2020-02-07');

In the above query, we inserted data into the newly created table which is afterinserttable and we select all rows from that table. When the trigger is called row is inserted and all rows from that table are selected. See below image

Alter afterinsert trigger

In insert trigger, we get Inserted table in a trigger like that for delete trigger we get deleted table in that create a table. If we try to access this outside create trigger syntax we get an error.

create trigger afterdelted
on studenttable
for delete
as
begin
declare @id int;
declare @fullname nvarchar(2000);
select @id=Id from deleted;
select @fullname= first_name from deleted;

insert into afterinserttable values(@id,'name is '+CAST( @fullname as nvarchar(100)) + cast(GETDATE() as nvarchar));
select * from afterinserttable;
end;

GitHub: https://github.com/Sagar-Jaybhay

View In Asp.Net MVC

What Is Indexed View In SQL By Sagar Jaybhay

In this article we will understand What Is Indexed View In SQL Server By Sagar Jaybhay.

Indexed View

A normal view or non-indexed view is a saved SQL query or a virtual table. When you try to get data from the table the data is coming from an underlying table. So it means the view is a virtual table and doesn’t store any data.

But when you create an index on view it gets materialized which means you can store data in view so in the SQL server we called is as Indexed view.

There are some guidelines for creating an indexed view.

1. The view should be created with schemabinding option.
2. If in your select function contains aggregate functions like sum, avg
   like that then for this you need to replace the null value with other value
   means 0 or not null value.
3. If your select query contains group by clause then you need to use
   Count_Big(*) expression.
4. Whatever table you used in view you need to specify 2 part names of
   these tables the example of above shown below

create view IndexedView
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from Employee as e 
inner join Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

Indexed View In SQL By Sagar Jaybhay

Now by using the above query, we created a view but we are not able to create an index on that so that you can get below error

Msg 1939,
Level 16, State 1, Line 59

Cannot create an index on view 'IndexedView' because the view is not schema bound.

Indexed View In SQL Error Message By Sagar Jaybhay

For this, we need to create a view schemabinding option and now we can alter our view. For altering view our query looks like below

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

Msg 4512,
Level 16, State 3, Procedure IndexedView, Line 4 [Batch Start Line 58]

Cannot schema bind view 'IndexedView' because the name 'Employee' is invalid for schema binding. Names must be in two-part format and an object cannot reference

Now when we are altering the view with schema binding option we get the above error. To fix this we need two-part names of the table.

Now our view gets created by using below query

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(IsNull(e.salary,0)) as totalsal from dbo.Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

But when we are going to create the index we will get below error

Msg 10138,
Level 16, State 1, Line 68

Cannot create an index on view 'temp.dbo.IndexedView' because its select list does not include proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select a list.

Now we add count_big(*) in our query

alter view IndexedView
with schemabinding
as
select d.Department_Name as dept,sum(isnull(e.salary,0)) as totalsal,
COUNT_BIG(*) as totalemp
from dbo.Employee as e 
inner join dbo.Department as d
on d.DepartmentID=e.DepartmentID
group by d.Department_Name;

and when we use below query

create unique clustered index view_deptindex
on IndexedView(dept);

to create an index it works perfectly fine.