Module 1: DATA AND PROCESS MODEL
Module 2: MASTER TABLES AND PAGES
Module 3: DOCUMENTS
Module 4: POSTING
Module 5: FEATURE INTEGRATION
Module 6: REPORTING
Module 7: STATISTICS
Module 8: DIMENSIONS
Module 9: ROLE TAILORING
Module 10: INTERFACES
Module 11: WEB SERVICES
Module 12: TESTING AND DEBUGGING
Module 13: SQL SERVER OPTIMIZATION
- Lesson 1: SQL Server for Microsoft Dynamics NAV
- Lesson 2: Representation of NAV Tables and Indexes in SQL Server
- Lesson 3: Collation Options
- Lesson 4: SQL Server Query Optimizer
- Lesson 5: SQL Server Query Optimizer
- Lesson 6: Data Access Redesign
- Lesson 7: C/AL Database Functions and Performance on SQL Server
- Lesson 8: Bulk Inserts
- Lesson 9: Locking, Blocking, and Deadlocks
- Lesson 10: SIFT Data Storage in SQL Server
- Lesson 11: SQL Server Profiler
Lesson 1: SQL Server for Microsoft Dynamics NAV
SQL Server for Microsoft Dynamics NAV
SQL Server for Microsoft Dynamics NAV : SQL Server is a comprehensive database platform that provides enterprise-class data management with integrated business intelligence (BI) tools. SQL Server can be characterized as a set-based engine. This means that SQL Server is very efficient when it retrieves a set of records from a table, but less so when records are accessed one at a time.
Access to SQL Server from Microsoft Dynamics NAV is performed with the Microsoft Dynamics database driver that is discussed later in this module. The SQL
Server interface from Microsoft Dynamics NAV Server was rewritten for Microsoft Dynamics NAV 2013 to use ADO.NET instead of ODBC. The advantages of the new access layer are described later in this module.
When SQL Server receives a query (in the form of a Transact-SQL statement), it uses the SQL Query Optimizer to create and execute the query. The Query Optimizer evaluates the query and makes decisions about how to execute the query in the execution plan. For example, the Query Optimizer decides which index to use, whether to use parallel execution, and so on. Query Optimizer assumes that the client generates queries according to its own logic, and that these queries are not optimized for SQL Server. The primary criteria that Query Optimizer uses to decide which execution plan to use is the performance cost of executing the query.
SQL Server stores data in B+ tree structures. One index is used to store the data physically on a disk. Other indexes are used to find a range and point to the data in the main index. This main index is called the Clustered Index. On SQL Server, you can define any index as the main (clustered) index. However, on the Microsoft Dynamics NAV Development Environment it is the default primary key of the table.