Index on views

(1) Share

Report

Posted on by kumarnaresh

In F and O, VS developer experience, I see an option to create index on views. But when synchronized to DB, indexes are not created on the corresponding SQL views. So, can you guys please help me understand, why would we need index on views or keys on data entities in D365 F and O.

I have the same question (0)

All responses (12)

Answers (2)

Verified answer

huijij 19,811 on at

Like (0)

Report

Hi kumarnaresh,

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently:

d365ffo.com/.../

www.linkedin.com/.../lets-talk-indexing-craig-watts

Was this reply helpful? Yes No
Suggested answer

Sheikh Sohail 6,125 on at

Like (0)

Report

I would suggest you understand the view on SQL first and then move to D365FO

SQL Part

www.sqlshack.com/.../

docs.microsoft.com/.../create-indexed-views

D365FO

You will experience the difference, if you are applying the index on the fields that you are using in join to different tables(if not already exists) or fields that are in use of where conditions.

Was this reply helpful? Yes No
kumarnaresh on at

Like (0)

Report

I understand what's an index in SQL. But, what I have noted is that even when I create index on views in F and O, the index is not created on that view in DB, when we synchronize the changes to the DB. So, am not sure what those are meant for.

Was this reply helpful? Yes No
kumarnaresh on at

Like (0)

Report

I understand what's an index in SQL. But, what I have noted is that even when I create index on views in F and O, the index is not created on that view in DB, when we synchronize the changes to the DB. So, am not sure what those are meant for.

Was this reply helpful? Yes No
kumarnaresh on at

Like (0)

Report

responses were directed towards indexes in general. So, edited the question, to be more clear.

Was this reply helpful? Yes No
Suggested answer

André Arnaud de Cal... 300,915 Super User 2025 Season 2 on at

Like (0)

Report

Hi Kumarnaresh,

Your observation is correct. Indexes on views are not supported with Dynamics 365 F&O. When performing a build, you should see a warning about the same. The Key on the data entity is used to retrieve an existing record for update purposes.

Was this reply helpful? Yes No
Suggested answer

Sheikh Sohail 6,125 on at

Like (0)

Report

Sorry for the late reply, I was replicating the same scenario at my end, and found that you are correct, the index is not replicating on SQL level, and now I am in discussion with the Fast track team to discuss further.

I will keep posting their final wording of the Microsoft solution architect.

Was this reply helpful? Yes No
Suggested answer

Sheikh Sohail 6,125 on at

Like (0)

Report

Hi

I was in discussion with Fast Track and receive the feedback like below

As discussed, AX7 does not support indexing on views. If you create an index, the compiler will through a warning as below in Visual Studio:

However, you can create an index via the backend in the SQL database.

So, If you want to create an Index in your view, you need to provide the Script to Microsoft and they will execute on your behalf on production.

Was this reply helpful? Yes No
kumarnaresh on at

Like (0)

Report

Hi André Arnaud de Calavon,

Thanks for your answer.

I can understand that by data entity record update, you mean the transactional needs on the entity.

But, as far as I know data entities are used in 3 cases

1) out of the box reporting features

2) Office apps integration

3) data management across projects.

and these cases don't seem to need transactions(not sure though).

Can you please let me know in specific that in which case, data corresponding to a data entity gets updated.

Was this reply helpful? Yes No
kumarnaresh on at

Like (0)

Report

Hi Sheiikh Sohail Hussain,

Thanks for your answer.

I have 2 doubts.

1) why VS experience has the index option for views, if it's not supported.

2) Even if we manage to create index on views, in SQL directly, on future deployments, when schema deltas are present, views will be recreated and manually created index will be dropped. So, do we have to create index manually during every deployments.

Thanks,

Naresh.

Was this reply helpful? Yes No