High Performance Data Access: Why and How to Use Queries in Microsoft Dynamics NAV 2015
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 hereor login