web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / how to work index in t...
Microsoft Dynamics AX (Archived)

how to work index in table?

(0) ShareShare
ReportReport
Posted on by @rp@n 30

Dear All,

In "VenInvoiceTrans" table ItemIdIdx index is there.

ItemIdIdx combined Itemid and Invoicedate.

If i use index "ItemIdIdx" with select statement then it's search the records win Table  "VendInvoiceTrans" with combination (ItemId + InvoiceDate) from database ?

Is't correct?

Because i noticed after use indexing  fetching records from table much faster then before.

My question is how indexing perform in database level ?

Please give me more shed on this.

Thanks!

Arpan

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    This probably isn't the proper forum for your question, since it's basically a question about SQL Server.

    Indexes are always considered by SQL Server when fetching data.  Generally SQL Server, if properly setup and maintained, will choose the "best" plan for fetching and returning data to any query.  You can create indexes to give SQL Server more options, and certainly fields and combinations of fields that are commonly used should probably appear in an index (considering the trade-off between write and read performance when creating new indexes).  You should not have to specify an index when you write a query.  If doing so improves performance, then you might need to review your query, update statistics, or some other maintenance so that SQL Server will compile better plans.

    You will find plenty of information online about SQL Server, indexing, and performance tuning.  Yes, indexing is important to SQL Server performance, but it comes at a cost and a trade-off of performance.

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    Indexes work like multi-level sorting in Excel tables. It keeps tracking of record positions within the data file in the order of the first column, then second column, then third, etc.

    Ideally you would want an index for a select statement's where clause criteria or order/group by fields where the first column is a match on one of the covering indexes for fastest return time to find the position of the record within the SQL databases' data files.

    Clustered index is the main index on how the record is sorted on the disk, so you would want to use fields in the cluster index that are most frequently used for searching in that table.

    If you want to know more about it, there are excellent books available, try a Safari books online subscription, there are dozens of SQL books available there.

  • @rp@n Profile Picture
    @rp@n 30 on at

    Thanks Sukrut and Vilmos

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans