Data Types

SQL 4 CDS attempts to implement the same data types, precedence rules, conversions and precision, scale and length rules as SQL Server, with the following exceptions:

Unsupported data types

The following data types are not supported in SQL 4 CDS:

• varbinary
• timestamp
• image
• hierarchyid
• User defined types
• Cursors

XML type

SQL 4 CDS only supports the xml data type in a limited way. The following features are not supported:

• XML Schemas.
• DOCUMENT format.
• WITH XMLNAMESPACES.
• XML DML
• exist() Method
• modify() Method
• nodes() Method

EntityReference type

SQL 4 CDS also introduces a custom data type, EntityReference.

Lookup columns in D365 (including Customer and Owner columns) as well as primary ID columns use this type, which combines the unique identifier with the type name and source instance name information.

Display

When an EntityReference value is included in the results of a query it is shown as a link - double-click the link to open the record in a browser.

Insert & Update

To insert or update a value in a polymorphic lookup column you must supply both the ID and the entity type name the ID relates to. You can do this either by using an EntityReference type, or supplying the two elements separately, e.g.:

INSERT INTO contact (firstname, lastname, parentcustomerid)
SELECT 'Mark', 'Carrington', accountid
FROM   account
WHERE  name = 'Data8'

Because accountid is the primary ID column of the account table, it uses the EntityReference type and contains the ID and type name information required to populate the parentcustomerid polymorphic lookup column in the contact table.

INSERT INTO contact (firstname, lastname, parentcustomerid, parentcustomeridtype)
VALUES ('Mark', 'Carrington', '473EF7A5-35DD-4AD0-802F-48455C9BA05F', 'account')

Because the ID represented by the string literal does not contain any indication of the type of record it references, the parentcustomeridtype column must also be included in the INSERT statement and be set to either account or contact.

You can create an EntityReference value using the CREATELOOKUP() function, e.g.:

INSERT INTO contact (firstname, lastname, parentcustomerid)
VALUES ('Mark', 'Carrington', CREATELOOKUP('account', '473EF7A5-35DD-4AD0-802F-48455C9BA05F'))

Because the CREATELOOKUP() function returns an EntityReference value and not just a GUID, the parentcustomeridtype column does not need to be populated directly.

Conversions

EntityReference has a precedence below all the standard SQL types, and can be implicitly converted to the uniqueidentifier and string types.