Mapping data types between Langstack and MS-SQL

The table below shows the mapping between Langstack data types and the supported fields in MS-SQL Connector when performing ETL. (See note below):

Langstack Data Type	MSSQL Data Type	Direction
character	char	bidirectional
character	varchar	bidirectional
character	nvarchar	bidirectional
string	varchar	bidirectional
string	nvarchar	bidirectional
string	uniqueidentifier	bidirectional
string	timestamp*	unidirectional from MS-SQL to Langstack
string	rowversion*	unidirectional from MS-SQL to Langstack
GUID	uniqueidentifier	bidirectional
boolean	bit	bidirectional
integer	int (-2,147,483,648 - +2,147,483,647)	bidirectional
integer	bigint	bidirectional
numerical	int (-2,147,483,648 - +2,147,483,647)	unidirectional from MS-SQL to Langstack
numerical	bigint	unidirectional from MS-SQL to Langstack
numerical	decimal	bidirectional
numerical	numeric	bidirectional
numerical	smallmoney	bidirectional
numerical	money	bidirectional
numerical	float	bidirectional
numerical	real	bidirectional
DateTime	datetime	bidirectional
DateTime	date	bidirectional
DateTime	datetime2	bidirectional
DateTime	smalldatetime	bidirectional
timestamp	int	bidirectional
timestamp	bigint	bidirectional
timestamp	float	bidirectional
timestamp	real	bidirectional
timestamp	numeric	bidirectional
timestamp	decimal	bidirectional
timestamp	date	bidirectional
timestamp	smalldatetime	bidirectional
timestamp	datetime	bidirectional
timestamp	datetime2	bidirectional
JSON	varchar	bidirectional
JSON	nvarchar	bidirectional
List<JSON>	varchar	bidirectional
List<JSON>	nvarchar	bidirectional

Special notes regarding data mapping

MS-SQL field type timestamp and rowversion*

If the writer (destination) in the ETL application is a MS-SQL database, the data cannot be mapped to a SQL column of type rowversion and timestamp.

The reason for this limitation is MS-SQL automatically updates the rowversion and timestamp column whenever the row is updated and the field value is monotonically increasing on each update.

However, if the reader (source) is MS-SQL, a column with a rowversion or timestamp data type can be mapped to string Langstack data type only.

According to the MS-SQL Server documentation the timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server.

MS-SQL column identity

The identity column identifies a certain row in an MS-SQL table. It is a numeric column that is auto-incremented with integer values when rows are inserted.

There are six (6) data types that may be defined for identity columns:

• int

• bigint

• smallint

• tinyint

• numeric

• decimal

As with timestamp and rowversion, if the writer (destination) is MS-SQL, data cannot be written to the identity column and any attempts to write to the identity column will be ignored.

However, if the reader (source) is MS-SQL, the mapping to Langstack fields will be done as specified in the Data Type Mapping table