Creating an Indexed View

Question

Hi All,

Can you please guide me to create an indexed view for the below view? Basically it has Date and values for few columns.

CREATE VIEW [dbo].[Test] AS
SELECT  CAST(Date as date) as Date,Tmlast,case when ( [EacM2] >5000)
       then '5000'
       else [EacM2] end as EnergyAC,IrrTot as Irradiation,
datename(m,Tmlast)+' '+cast(datepart(yyyy,TmLast) as varchar) as MonthYear, EacLast, REPLACE(StnId, 'IN-021A','IN-021') as StationID
FROM
[dbo].[IN-021T-ALL]
WHERE TmLast IN (SELECT MAX(TmLast) FROM [dbo].[IN-021T-ALL] GROUP BY  substring(convert(varchar(20),TmLast),1,10))
GO

Answer 1

You need to tell us on what basis view would be queried.

Based on that you need to add clustered index on the required columns

Thats all you need to make it indexed view

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

Hi Visakh,

I am confused. On what basis should an index be created? 

Answer 3

Hi Visakh,

Could you please help?

Thanks

Answer 4

Hi Visakh,

Could you please help?

Thanks

Based on how queries will be using the view

Particularly which column will be used in join operations, which columns will be used for searching in WHERE clauses etc

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 5

There are a lot of restrictions on indexed views. I cannot vouch for it, but I don't think that view complies with the requirements for an indexed view.

What are you really trying to achieve?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

I am trying to reduce the performance of the queries as it's fetching data from two tables everytime. So it takes a lot of time to load.

Answer 7

Hi Krishnamurthy2810,

Actually, the function used to create an indexed view is very easy, we can create an view using the following command:

CREATE VIEW [name] WITH SCHEMABINDING
AS
....

GO

Then create index on the view like below:

create unique clustered index [index_name]
on [name]([column_name])

However, there are many limitation when creating the index, it depends on your environment:

The data will be unique and the column cannot be created with group, for more information, please refer to this document: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017

Best Regards,

Teige

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].