Microsoft SQL Server (MS SQL) to SingleStore Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Microsoft SQL Server (MSSQL, MS SQL), Azure SQL Database, Azure Synapse to SingleStore.

Databases:

  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000, Azure SQL Database, Azure Synapse
  • SingleStore 8.x and 7.x

Migration Reference

SQL Language Elements

Datetime arithmetics:

SQL Server SingleStore
1 GETDATE() + n Add n days to a datetime ADDDATE(NOW(), n)
DATE_ADD(NOW(), INTERVAL n DAY)
TIMESTAMPADD(DAY, n, NOW())
2 GETDATE() + 0.1 Add 0.1 of 24 hours i.e. 144 minutes DATE_ADD(NOW(), INTERVAL 144 MINUTE)

Data Types

Converting datetime data types:

SQL Server SingleStore
1 DATETIME Date and time with fraction (milliseconds) DATETIME(6) Only precision of 6 is accepted

Other data types:

SQL Server SingleStore
1 SYSNAME System-defined type for identifiers NVARCHAR(128)

Built-in SQL Functions

Converting datetime functions:

SQL Server SingleStore
1 FORMAT(datetime, format) Convert to string with format DATE_FORMAT(datetime, format)
2 GETDATE() Get the current date and time NOW(6) Only precision of 6 is accepted

Error handling functions:

SQL Server SingleStore
1 RAISERROR(text, severity, state) Raise an user error RAISE USER_EXCEPTION(text)

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

SQL Server SingleStore
1 col AS exp Computed column col AS exp PERSISTED AUTO

SELECT Statement

Converting SQL queries from SQL Server to SingleStore:

SQL Server SingleStore
1 LOOP | HASH | MERGE Join hints i.e. INNER LOOP JOIN Removed

CREATE PROCEDURE Statement

Converting Transact-SQL stored procedures functions:

SQL Server SingleStore
1 CREATE PROCEDURE | ALTER PROCEDURE name CREATE OR REPLACE PROCEDURE name
2 Optional () for procedure parameters () required
3 AS AS
4 Declarations inside BEGIN block DECLARE block is between AS and BEGIN clauses

For more information, see Conversion of Transact-SQL Statements.

Transact-SQL Statements

Setting options:

SQL Server SingleStore
1 SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED | COMMITTED
Isolation level Commented
2 SET XACT_ABORT ON Rollback transaction on SQL error Commented

Code block:

SQL Server SingleStore
1 BEGIN … END BEGIN … END; ; is required for internal (not outer) blocks

Exception block:

SQL Server SingleStore
1 BEGIN TRY … END TRY
BEGIN CATCH … END CATCH
Exception block BEGIN …
EXCEPTION WHEN OTHERS THEN …
END