Dynamics 365 Data Types and Equivalent SQL Server Data Types

Dynamics 365 Online makes it very difficult to analyze the Microsoft SQL data type equivalents for Dynamics 365 data types. 

Now a days almost all Dynamics 365 projects involve some form of data Migration from third party applications. The most common way is to create a staging area on Microsoft SQL Server and create tables resembling the entities and fields in Dynamics 365. If Dynamics 365 is on cloud then you have be careful when creating staging tables as the data types should match the data types of CRM attributes. 

List of SQL Server equivalent of CRM attribute data types:

• CRM Two-Option (Boolean) - In SQL Server the data type is [bit]

• CRM Currency (Simple or Rollup) (for Precision = 4) - In SQL Server the data type is [decimal](38, 4)

• CRM Date and Time (Date only) - In SQL Server the data type is [datetime]

• CRM Date and Time (Date and Time) - In SQL Server the data type is [datetime]

• CRM Lookup - In SQL Server the data type is [uniqueidentifier]

• CRM Unique Identifier - In SQL Server the data type is [uniqueidentifier]

• CRM Multiple Lines of Text - In SQL Server the data type is [nvarchar](max)

• CRM String (single line of text for maximum length of 160) - In SQL Server the data type is [nvarchar](160)

• CRM Picklist (option set) - In SQL Server the data type is [int]

• CRM Status - In SQL Server the data type is [int]

• CRM Status Reason - In SQL Server the data type is [int]

• CRM Whole Number - In SQL Server the data type is [int]

• CRM Decimal Number (for Precision = 2 and Precision = 10) - In SQL Server the data type is [decimal](38, 2) or [decimal](38, 10)

• CRM Floating Point Number (for Precision = 5) - In SQL Server the data type is [decimal](38, 5)

This will help in creating SQL tables resembling the entity field data types in CRM.