Understanding how Microsoft Dynamics GP works with Microsoft SQL Server
Many people who have not been working with Microsoft Dynamics GP since the early non-SQL versions of Great Plains Dynamics sometimes wonder about the way that the application works with Microsoft SQL Server.
The answer is in the long history of the product and especially in the fact that it originally did not work with Microsoft SQL Server, but instead with the flat file database platforms of Ctree and Btrieve (later called Pervasive.SQL). Flat file database platforms are also known as ISAM (Indexed Sequential Access Method) database platforms. Support for the Microsoft SQL Server relational database was added for version 3.15.
To make the development environment Dexterity (and hence Dynamics) work with SQL Server required mapping the existing functionality supported by ISAM platforms to SQL Server equivalents. As some features of SQL Server were not available in the ISAM platforms they were not leveraged until the code was changed to be SQL optimised.
This article will attempt to explain why Microsoft Dynamics GP sometimes uses Microsoft SQL Server in a unusual or unexpected way.
Data Types
Dexterity is a 32 bit environment and this means that it considers an integer as 16 bit and a long integer as 32 bit. SQL Server supports 64 bit integers and so we have to map to smallint and int respectively. Currency fields in Dexterity are stored with a fixed 5 decimal places (using formats to control how many decimal places are seen) and so we map to a numeric datatype with 5 decimal places.
Dexterity's use of datetime data type in SQL often raises questions. As the Ctree and Btrieve platforms had separate Date and Time data types, we needed to map the two data types to the single datetime data type in SQL. For Dates, we leave the time component of the datetime data type blank (00:00:00) and for Times we leave the date component of the datetime data type as blank (01/01/1900).
Below is a table showing the mappings for all data types:
| Dexterity Control Type | SQL Server Data Type |
|---|---|
| Boolean | tinyint |
| Check Box | tinyint |
| Combo Box | char |
| Composite | Each component of the composite is stored in its own column in the SQL table. The name of each column is the physical name of the composite field followed by an underscore and the number of the component. |
| Currency | numeric(19,5) |
| Date | datetime (time component left as 00:00:00) |
| Drop-down List | smallint |
| Horizontal List Box | smallint |
| Integer | smallint |
| List Box | smallint |
| Long integer | int |
| Multi-select List Box | binary(4) |
| Non-native List Box | smallint |
| Picture | image |
| Radio Group | smallint |
| String | char |
| Text | text |
| Time | datetime (date component left as 01/01/1900) |
| Visual Switch | smallint |
NOT NULL
Dexterity does not understand the concept of NULL. Ctree and Btrieve did not use NULL and always initialised data to a blank or empty state. For example: zero value for numeric fields, empty string for string or text fields. Because Dexterity does not handle NULL values all fields in Microsoft Dynamics GP tables are defined as NOT NULL.
If a view is created to work with Microsoft Dynamics GP (including SmartList Builder), it should be created using the the ISNULL() function to set columns to a "blank" value when there is the posibility of a NULL value being returned.
Column Defaults
You might have seen the four defaults added to SQL Server. The GPS_CHAR, GPS_DATE, GPS_INT and GP_MONEY defaults are bound to the columns in Microsoft Dynamics GP tables. Binding the defaults to use when an insert or update statement does not have a value for the column avoids errors caused because the columns are set to NOT NULL.
DEX_ROW_ID
Ctree and Btrieve did not have the concept of a primary key. Normally the first defined key was setup as the "Primary" key but that is not always the case. Also there was no requirement to have the primary key as unique. So for when the SQL implementation for Dexterity was created, the developers added the DEX_ROW_ID identity column to every table. This field would then be added to any primary key that allowed duplicates so that the key created in SQL was unique (due to the added DEX_ROW_ID identity column)
The DEX_ROW_ID column is also used with the DEX_LOCKS and DEX_SESSION tables in the tempdb. These tables are used by Dexterity to implement its optimistic currency control (OCC) or passive locking. This passive locking functionality allows Dexterity to perform the equivalent of field level locking. Two users can modify different fields in the same record of data at the same time and both sets of changes will be saved.
DEX_ROW_TS
The DEX_ROW_TS field that is now appended to some tables in v10.0 is a new feature which allows a server based timestamp to be stored against each row of a table. This functionality was added to help with Business Data Catalog (BDC) indexing for SharePoint searches. To avoid indexing the same data multiple times, the timestamp can be checked to see what data has been updated since the last indexing process was executed.
This functionality can also help with synchronisation with external applications such as Customer Relationship Management (CRM) systems. Note that this column does use the datetime datatype with both a date and a time value.
zDP Stored Procedures
The zDP Stored Procedures were added to improve performance, by having the regularly used select statements based on the indexes pre-compiled. See the What do the zDP_ Auto Generated Stored Procedures do? article for a detailed explanation.
Prior to SQL Server 2000 ad hoc select statements were not cached, but stored procedures were cached for repeated use. By creating stored procedures for data extraction, the performance was improved. The Database query engine in SQL Server 2000 (or later) now stores execution plans of select statements, so the performance difference is no longer as significant.
Referential Integrity
The referential integrity features of SQL Server are only used in a few places in the system. This is because Ctree and Btrieve did not offer any referential integrity features and so the Dexterity code itself was used to validate parent/child relationships and check when records are still in use. All the referential integrity was coded into the application and is not implemented at the server.
For more information please see the followup article, Understanding how Microsoft Dynamics GP works with Microsoft SQL Server continued.
For other related topics have read of the following posts:
Why does Microsoft Dynamics GP encrypt passwords?
What is Column desSPRkmhBBCreh?
Let me know if you found this useful.
David
29-May-2009: Added link to followup article.