locked
Creating an Indexed View RRS feed

  • 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

    Monday, December 3, 2018 5:08 AM

Answers

  • 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

    Monday, December 3, 2018 7:04 AM
    Answerer
  • 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 MSDNFSF@microsoft.com.

    Tuesday, December 4, 2018 8:39 AM

All replies

  • 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

    Monday, December 3, 2018 6:01 AM
    Answerer
  • Hi Visakh,

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

    Monday, December 3, 2018 6:06 AM
  • Hi Visakh,

    Could you please help?

    Thanks

    Monday, December 3, 2018 6:59 AM
  • 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

    Monday, December 3, 2018 7:04 AM
    Answerer
  • 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, esquel@sommarskog.se

    Monday, December 3, 2018 10:34 PM
  • 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.


    Monday, December 3, 2018 11:24 PM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, December 4, 2018 8:39 AM