T-SQL to U-SQL Data Type Conversion

This is just a quick post, mainly for my own reference. When working with code generated solutions we often need to convert datasets from SQL Server (T-SQL) data types to Azure Data Lake Analytics (U-SQL) data types. As you probably know U-SQL has a hybrid syntax of T-SQL and C# which uses .Net data types. That said the Microsoft doc’s data type conversion table doesn’t always help us, link below.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings

For example, a T-SQL int doesn’t convert to an Int32 in U-SQL, even though this is correct for .Net. We need to be a little more general when going from T-SQL to U-SQL and also use the correct casing.

Here is the Adatis the data type conversion table from T-SQL to U-SQL:

T-SQL Data Type U-SQL Data Type
bigint long
binary byte[]
bit bool
char string
date DateTime
datetime DateTime
datetime2 DateTime
datetimeoffset DateTime
decimal decimal
FILESTREAM attribute byte[]
float double
image byte[]
int int
money decimal
nchar string
ntext string
numeric decimal
nvarchar string
real float
rowversion byte[]
smalldatetime DateTime
smallint short
smallmoney decimal
sql_variant byte[]
text string
time DateTime
timestamp byte[]
tinyint byte
uniqueidentifier Guid
varbinary byte[]
varchar string
xml string

Finally, and again, mainly for my own reference here is a T-SQL script to create this as a database table.

Data Type Mappings.sql.zip

If this post helped you out, I’d be keen to know, so please add a comment.

Many thanks for reading.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.