Microsoft SQL Server to Amazon Redshift Migration

SQLines provides tools to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Microsoft SQL Server to Amazon Redshift.

Databases:

  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000
  • Amazon Redshift

Migration Reference

Data Types

Converting data types from SQL Server to Amazon Redshift:

SQL Server Redshift
1 BIGINT 64-bit integer BIGINT
2 BINARY(n) Fixed-length byte string, 1 ⇐ n ⇐ 8000 -
3 BIT 1, 0 or NULL SMALLINT
4 CHAR(n) Fixed-length string, 1 ⇐ n ⇐ 8000 CHAR(n)
5 DATE Date (year, month and day) DATE
6 DATETIME Date and time with fraction TIMESTAMP
7 DATETIME2(p) Date and time with fraction TIMESTAMP
8 DATETIMEOFFSET(p) Date and time with fraction and time zone TIMESTAMPTZ
9 DECIMAL(p,s) Fixed-point number DECIMAL(p,s)
10 DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
11 FLOAT(p) Floating-point number FLOAT
12 IMAGE Variable-length binary data, ⇐ 2G -
13 INT, INTEGER 32-bit integer INT, INTEGER
14 MONEY 64-bit currency amount DECIMAL(15,4)
15 NCHAR(n) Fixed-length Unicode UCS-2 string, 1 ⇐ n ⇐ 4000 -
16 NTEXT Variable-length Unicode UCS-2 data, ⇐ 2G LONGTEXT
17 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
18 NVARCHAR(n) Variable-length Unicode UCS-2 string, 1 ⇐ n ⇐ 4000 -
19 NVARCHAR(max) Variable-length Unicode UCS-2 data, ⇐ 2G -
20 REAL Single-precision floating-point number REAL
21 ROWVERSION Automatically updated binary data -
22 SMALLDATETIME Datetime (year, month, day, hour, minutes
and 00 seconds )
TIMESTAMP
23 SMALLINT 16-bit integer SMALLINT
24 SMALLMONEY 32-bit currency amount DECIMAL(6,4)
25 TEXT Variable-length character data, ⇐ 2G -
26 TIME(p) Time (Hour, minute, second and fraction) -
27 TIMESTAMP Automatically updated binary data -
28 TINYINT 8-bit unsigned integer, 0 to 255 SMALLINT
29 UNIQUEIDENTIFIER 16-byte GUID (UUID) data CHAR(16)
30 VARBINARY(n) Variable-length byte string, 1 ⇐ n ⇐ 8000 -
31 VARBINARY(max) Variable-length binary data, ⇐ 2G -
32 VARCHAR(n) Variable-length character string, 1 ⇐ n ⇐ 8000 VARCHAR(n)
33 VARCHAR(max) Variable-length character data, ⇐ 2G TEXT ⇐ 256
34 XML XML data, ⇐ 2G -

Built-in SQL Functions

Converting datetime functions:

SQL Server Redshift
1 EOMONTH(datetime) Get the last day of the month of datetime LAST_DAY(datetime)

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses from SQL Server to Amazon Redshift:

SQL Server Redshift
1 IDENTITY(start, increment) Identity column IDENTITY(start, increment)
2 CONSTRAINT name PRIMARY | UNIQUE … Constraint name PRIMARY | UNIQUE …
3 PRIMARY KEY CLUSTERED Primary key Constraint PRIMARY KEY
4 CHECK bool_expr CHECK Constraint Not supported by Redshift, commented

Primary, unique and foreign key constraints are informational only in Amazon Redshift.