Article

High Performance Data Access: Why and How to Use Queries in Microsoft Dynamics NAV 2015

by Christopher Studebaker
Senior Consultant, UXC Eclipse,

Editor's Note: This article is adapted from the new book Programming Microsoft Dynamics NAV 2015 by David Studebaker and Christopher Studebaker, published by PACKT.

The Query object in Microsoft Dynamics NAV 2015 originated in NAV 2013 with improved performance in mind. It allows us to create advanced T-SQL queries using familiar NAV tools.

Externally, a Query can be used as:

  • A web service which is Cloud compatible
  • A web service source for XML or OData. OData is different from XML in that it contains the field definitions and styles along with the data itself.
  • A data feed for external reporting tools such as Excel, SharePoint, and SSRS

Internally, a Query can be used as:

  • A direct data source for Charts
  • Providers of data to which Cues (displayed in Role Centers) are bound.
  • A dataset variable in C/AL to be accessed by other object types (Reports, Pages, Codeunits, etc.).

Queries are more limited than SQL stored procedures with some design compromises made for better performance. Data manipulation, variables, sub-queries, and dynamic elements (such as building a query based on selective criteria) are not allowed in a Query.

Queries resemble SQL Views. A new SQL Joins feature allows NAV to generate advanced T-SQL statements. These Join methods are supported:

  • Inner: the query compares each row of table A with each row of table B to find the pairs of rows satisfying the Join criteria.
  • Full Outer: all records from ...

    FREE Membership Required to View Full Content:

    Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
    Learn more about us here

    Get full access
BI and Reporting Business Central Data Management Dynamics 365 Dynamics NAV NAV 2013 NAV 2015
About Christopher Studebaker

Chris Studebaker has been designing, developing, implementing and selling in NAV and SQL Server since 1999, specializing in high user count, high data volume applications in retail, manufacturing, job shop, and distribution. Chris has worked on many NAV implementations with integrations to external databases and third party add-on products.  Some special applications have included high volume order entry, pick-to-light systems, procurement analysis and Web front ends.  Chris is a Senior Consultant for UXC Eclipse. 

More about Christopher Studebaker