Overview

This document explains the considerations and methods for migrating from Microsoft SQL Server to ALTIBASE.

It targets SQL Server 2016 and Altibase version 7.1 or later.

Icon

This document is provided for informational purposes only and may be changed without prior notice. It may contain errors, and there is no explicit or implicit liability for commercial use or suitability for specific purposes.

The timing of development or release of the features and functions of Altibase products included in this document is at Altibase’s discretion.

Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to this document.

Considerations When Converting SQL Server Schema

The schema in SQL Server is a container for objects (a logical namespace).

In Altibase, schemas and users are not separated, so conversion must be done by mapping to a specific user unit.

Item	SQL Server	Altibase
Feature	Schemas and users are separated (since SQL Server 2005) One user can access multiple schemas Permissions can be granted at the schema level	Schema = User Schemas cannot be created separately; objects must be created per USER
예시	CREATE SCHEMA Sales AUTHORIZATION John; CREATE TABLE Sales.Customers (...);	CREATE USER Sales IDENTIFIED BY salespwd; CREATE TABLE Sales.Customers (...);

When migrating SQL Server schemas to Altibase, replace schemas with "Schema = User" as shown in the table below, and common schemas like dbo should be replaced by creating separate users.

Item	SQL Server	Altibase	Remarks
Schema Structure	Independent from user	user = Schema	When converting, SQL Server schemas must be mapped to Altibase users
Default Schema	dbo	Not supported	Altibase does not have dbo schema, so it should be replaced by creating a specific user
Common Object Management	Uses dbo	Create separate user and grant permissions	dbo.TableName format needs to be mapped to a specific user schema and granted permissions
Permission Grant Unit	Can be at schema level	Only at object level	Schema-level permissions must be broken down into object-level permissions
Naming Conflict Handling	Resolved by schema separation	Resolved by user name	Be careful that SQL Server schema names do not conflict with Altibase user names during conversion

Object Conversion

This section describes the considerations when converting SQL Server objects to ALTIBASE.

DATATYPE

This section explains how each DATATYPE in SQL Server tables is converted when migrating to Altibase.

Category	SQL Server	Altibase	SQL Server Maximum	ALTIBASE Maximum	Remark
Character Types	CHAR	CHAR	8000 byte CHAR(MAX) : 2GB	32000 byte	CHAR(MAX) is converted to CLOB in ALTIBASE.
	VARCHAR	VARCHAR	8000 byte VARCHAR(MAX) : Max 2GB	32000 byte	VARCHAR(MAX) is converted to CLOB in ALTIBASE.
	NCHAR	NCHAR	8000 byte NCHAR(MAX) : 2GB	Max length: 16000(UTF16), Max length: 10666(UTF8)	NCHAR(MAX) is converted to CLOB in ALTIBASE.
	NVARCHAR	NVARCHAR	8000 byte NVARCHAR(MAX) : 2GB	Max length: 16000(UTF16), Max length: 10666(UTF8)	NVARCHAR(MAX) is converted to CLOB in ALTIBASE.
	BINARY	BYTE	8000 byte	32000 byte
	VARBINARY	BLOB	8000 byte	2GB	VARBINARY(MAX) is converted to BLOB in ALTIBASE.
	IMAGE -> VARBINARY(MAX)	BLOB	2GB	2GB	Since SQL Server 2005, it is recommended to use VARBINARY(MAX) as a replacement.
	TEXT, NTEXT -> VARCHAR(MAX) NVARCHAR(MAX)	CLOB	TEXT : 65536 byte NTEXT : 2GB	2GB	Since SQL Server 2005, it is recommended to use VARCHAR(MAX) or NVARCHAR(MAX) as replacements.
Numeric Types	BIGINT	BIGINT
	NUMERIC	NUMERIC
	BIT	BIT
	SMALLINT	SMALLINT
	TINYINT	SMALLINT	TINEYINT : 1 byte	SMALLINT : 2 byte
	REAL	REAL
	INT	INTEGER
	MONEY	decimal(p, s)
	SMALLMONEY	decimal(p, s)
Date Types	DATE	DATE			Stores only the date. Format: YYYY-MM-DD
	DATETIMEOFFSET	Not Supported			Store the date, time, and time zone. Format: YYYY-MM-DD hh:mm:ss [+\|-]hh:mm In Altibase, this can be replaced with DATE if the time zone is excluded.
	DATETIME2	DATE			Store the date and time. Format: YYYY-MM-DD hh:mm:ss Altibase stores up to microseconds (6 decimal places).
	SAMMLLDATETIME	DATE			Store the date and time. Format: YYYY-MM-DD hh:mm:ss
	DATETIME	DATE			Store the date and time (for backward compatibility). Format: YYYY-MM-DD hh:mm:ss
	TIME	DATE			Store time only. Format: hh:mm:ss SQL Server stores up to 100 nanoseconds (7 decimal places). Altibase stores up to microseconds (6 decimal places).

FUNCTION & EXPRESSION

분류	SQL Server	Altibase	비고
Aggregate	AVG	AVG
	CHECKSUM_AGG	Not Supported
	COUNT	COUNT
	COUNT_BIG	Replace with COUNT
	GROUPING	GROUPING	A function that distinguishes aggregation levels when using ROLLUP, CUBE, or GROUPING SETS.
	GROUPING_ID	GROUPING_ID	Returns a numeric value corresponding to the GROUPING bit vector associated with the row.
	MAX	MAX
	MIN	MIN
	STDEV	STDDEV
	STDEVP	Not Supported
	SUM	SUM
	VAR	VARIANCE
Ranking	DENSE_RANK	DENSE_RANK
	NTILE	NTILE
	RANK	RANK
	ROW_NUMBER	ROW_NUMBER
Conversion	CAST, CONVERT	CAST	The CONVERT function in Altibase provides different functionality
	PARSE	CAST	The CONVERT function can be used to change the character set
	TRY_CAST	CAST	Returns an error if casting fails
	TRY_CONVERT	Not Supported
	TRY_PARSE	Not Supported
Date	CURRENT_TIMESTAMP	CURRENT_TIMESTAMP
	DATENAME	DATENAME
	DATEPART	DATEPART
	DAY	DATEPART
	DATEADD	DATEADD	In Altibase, date strings must be formatted using the TO_DATE() function
	DATEDIFF	DATEDIFF
	DATEFROMPARTS	TO_DATE
	DATETIME2FROMPARTS	TO_DATE
	DATETIMEFROMPARTS	TO_DATE
	DATETIMEOFFSETFROMPARTS	TO_DATE
	EOMONTH	LAST_DAY
	GETDATE	SYSDATE	Conversion Type: datetime
	GETUTCDATE	Replace with unix_date
	ISDATE	Not Supported
	SMALLDATETIMEFROMPARTS	TO_DATE
	SWITCHOFFSET	Replace with CONV_TIMEZONE
	SYSDATETIME	SYSDATE	Conversion Type: datetime2SWITCHOFFSET
	SYSDATETIMEOFFSET	Not Supported
	SYSUTCDATETIME	Replace with unix_date
	TIMEFROMPARTS	TO_DATE
	TODATETIMEOFFSET	Not Supported
	YEAR	Replace with DATEPART or EXTRACT
Logical	CHOOSE	Not Supported
Logical	IIF	Replace with case2
Mathematical	ABS	ABS
	ACOS	ACOS
	ASIN	ASIN
	ATAN	ATAN
	ATN2	ATN2
	CEIL(n)	CEIL(n)
	COS	COS
	COT	Not Supported
	DEGREES	Not Supported
	EXP	EXP
	FLOOR	FLOOR
	LOG	LOG	LOG (m, n)
	LOG10	LOG(10,m)
	PI	Not Supported
	POWER	POWER
	RADIANS	Not Supported
	RAND	RAND	SQL Server: Returns a pseudo-random float value between 0 and 1. Altibase: Returns a pseudo-random integer value between 0 and 2,147,483,647.
	ROUND	ROUND
	SIGN	SIGN
	SIN	SIN
	SQRT	SQRT
	SQUARE	Not Supported
	TAN	TAN
String	ASCII	ASCII
	CHAR	CHAR
	CHARINDEX	INSTR, POSITION
	CONCAT	CONCAT
	DIFFERENCE	Not Supported
	LEFT	SUBSTR, SUBSTRING	SUBSTR (expr, start [, length]) Specify a positive value for Start
	LEN	LENGTH
	LOWER	LOWER
	LTRIM	LTRIM	In Altibase, LTRIM(expr1 [, expr2]) is used.
	RTRIM	RTRIM	In Altibase, RTRIM(expr1 [, expr2]) is used.
	PATINDEX	INSTR, POSITION
	QUOTENAME	Not Supported
	REPLACE	REPLACE
	REPLICATE	REPLICATE
	REVERSE	REVERSE_STR
	RIGHT	SUBSTR, SUBSTRING	SUBSTR (expr, start [, length]) Specify a negative value for Start
	PATINDEX	INSTR, POSITION
	SOUNDEX	Not Supported
	SPACE	LPAD, RPAD
	STR	TO_CHAR
	STUFF	STUFF
	SUBSTRING	SUBSTRING
	UNICODE	Not Supported
	UPPER	UPPER

OBJECT

Category	SQL Server	Altibase	Remark
CONSTRAINT	Supported	Supported
TRIGGER	Supported	Supported
Multi Key-Index	Supported	Supported
VIEW	Supported	Supported
UPDATABLE VIEW	Supported	Supported	In Altibase, if the WITH READ ONLY option is not specified when creating a VIEW, an updatable view is created.
SEQUENCE	Supported	Supported
STORED FUNCTION/PROCEDURE	Supported	Supported
SYNONYM	Supported	Supported
Temporary Table	Supported	Supported	In Altibase, temporary tables are created and used within a Volatile TableSpace.
USER	Supported	Supported
REPLICATION	Supported	Supported

CREATE TABLESPACE

In SQL Server, databases are managed as data storage units, whereas in Altibase, tablespaces are managed.
In Altibase, tablespaces are classified based on storage type into memory tablespaces and disk tablespaces, by creator into system tablespaces and user-defined tablespaces, and by stored content into dictionary tablespaces, undo tablespaces, temporary tablespaces, and data tablespaces.

Therefore, when converting to Altibase, use CREATE MEMORY DATA TABLESPACE or CREATE DISK DATA TABLESPACE depending on the type of data storage.

The following explains guidelines for mapping various options in the CREATE TABLESPACE statement when converting a SQL Server DATABASE to an Altibase tablespace.

SQL Server	Altibase	Remark
FILENAME	FILENAME
SIZE	SIZE	default 100MB
MAXSIZE	MAXSIZE
FILEGROWTH	AUTOEXTEND ON NEXT
FILESTREAM	Not Supported	Delete when generating the statement in Altibase
DEFAULT_FULLTEXT_LANGUAGE	Not Supported	Delete when generating the statement in Altibase
DEFAULT_LANGUAGE	Not Supported	Delete when generating the statement in Altibase
NESTED_TRIGGERS	Not Supported	Delete when generating the statement in Altibase
TRANSFORM_NOISE_WORDS	Not Supported	Delete when generating the statement in Altibase
TWO_DIGIT_YEAR_CUTOFF	Not Supported	Delete when generating the statement in Altibase
DB_CHAINING	Not Supported	Delete when generating the statement in Altibase
TRUSTWORTHY	Not Supported	Delete when generating the statement in Altibase

CREATE TABLE

When converting a SQL Server TABLE to Altibase, various options used in the CREATE TABLE statement must be appropriately modified.
Altibase does not provide OBJECT TABLE or XMLType TABLE.
Altibase provides memory TABLE, so if the characteristics of the table to be converted fit a memory TABLE, it should be created specifying a memory TABLESPACE.

If a memory TABLE is created, the options used in the SQL Server CREATE TABLE statement cannot be used.
Refer to Altibase SQL manuals for the syntax to create memory TABLEs.

If converting a SQL Server TABLE to a DISK TABLE, the various options set in CREATE TABLE should be converted to Altibase-compatible options as follows.

1. Column definition

SQL Server	Altibase	Remark
FILESTREAM	Not Supported
COLLATE	Not Supported
CONSTRAINT	CONSTRAINT	In Altibase, you cannot specify an index name when defining a PRIMARY KEY or UNIQUE constraint
IDENTITY	Not Supported
ROWGUIDCOL	Not Supported

2. data type

SQL Server	Altibase	Remark
Precision, scale	Precision, scale
max	Not Supported
CONTENT	Not Supported
DOCUMENT	Not Supported
xml_schema_collection	Not Supported

3. column constraint

SQL Server	Altibase	Remark
PRIMARY KEY	PRIMARY KEY
NULL, NOT NULL	NULL, NOT NULL
UNIQUE	UNIQUE
CLUSTERED, NONCLUSTERED	Not Supported
FOREIGN KEY REFERENCES	FOREIGN KEY REFERENCES
partition_scheme_name	PARTITION BY RANGE \| HASH \| LIST	Specify the type of partitioned table when creating a table

4. computed column definition

Not Supported

5. table constraint

SQL Server	Altibase	Remark
PRIMARY KEY	PRIMARY KEY
CLUSTERED, NONCLUSTERED	Not Supported
FOREIGN KEY REFERENCES	FOREIGN KEY REFERENCES
partition_scheme_name	PARTITION BY RANGE \| HASH \| LIST	Specify the type of partitioned table when creating a table

6. table_option

SQL Server	Altibase	Remark
DATA_COMPRESSION	Replace with COMPRESS(column_name)	Altibase provides column-level compression

7. index_option

SQL Server	Altibase	Remark
PAD_INDEX	Not Supported
FILLFACTOR	Not Supported
IGNORE_DUP_KEY	Not Supported	Altibase is in the OFF state
STATISTICS_NORECOMPUTE	Not Supported	Altibase is in the OFF state
ALLOW_ROW_LOCKS	Not Supported	Defined according to the Durability Level setting
ALLOW_PAGE_LOCKS	Not Supported	Defined according to the Durability Level setting
DATA_COMPRESSION	Not Supported	Index compression is not supported

CREATE USER

In Altibase, database login and user are not created separately; a single user is created and managed.

SQL Server

Altibase

Remark

CREATE LOGIN <login_name> WITH PASSWORD = '<password>';

Users based on logins in master
CREATE USER user_name
    [
        { FOR | FROM } LOGIN login_name
    ]
    [ WITH DEFAULT_SCHEMA = schema_name ]
[ ; ]

CREATE USER <user_name> IDENTIFIED BY ‘<password>’
DEFAULT TABLESPACE = tablespace_name

CREATE INDEX

Altibase provides only BTREE and RTREE indexes and does not support BITMAP, CLUSTER, REVERSE, or Global partitioned indexes.

The following explains how to convert the options used in the CREATE INDEX statement when migrating to Altibase.

SQL Server	Altibase	Remark
UNIQUE	UNIQUE
CLUSTERED \| NONCLUSTERED	Not Supported
ASC \| DESC	ASC \| DESC
INCLUDE	Not Supported
filter_predicate	Not Supported
partition_scheme_name	PARTITION ON	Only local indexes are supported
ON filegroup_name	Not Supported
table_or_view_name	Table_name	Views are not supported
PAD_INDEX	Not Supported
FILLFACTOR	Not Supported
SORT_IN_TEMPDB	Not Supported
IGNORE_DUP_KEY	Not Supported	Altibase is in the ON state
STATISTICS_NORECOMPUTE	Not Supported	Altibase is in the OFF state
DROP_EXISTING	Not Supported
ONLINE	Not Supported	Altibase is in the OFF state
ALLOW_ROW_LOCKS	Not Supported	Defined according to the Durability Level setting
ALLOW_PAGE_LOCKS	Not Supported	Defined according to the Durability Level setting
MAXDOP	Not Supported	Applied only at creation
DATA_COMPRESSION	Replace with COMPRESS(column_name)

SQL Conversion

This explains how to convert SQL statements from SQL Server to Altibase.

JOIN

In addition to the joins below, Altibase also provides Semi Join and Anti Join.

SQL Server	Altibase	Remark
INNER JOIN	INNER JOIN
LEFT OUTER JOIN	LEFT OUTER JOIN
RIGHT OUTER JOIN	RIGHT OUTER JOIN
FULL OUTER JOIN	FULL OUTER JOIN
CROSS JOIN	CROSS JOIN

Execution Query

The query terminator in Altibase is “;”. Therefore, if you want to execute multiple queries at once, each query must be separated by the terminator “;”.

SQL Server	Altibase	Remark
GO	; Commit;	default Autocommit

Temporary Table

SQL Server	Altibase	Remark
CREATE TABLE #TempProcess	CREATE TABLE TEMPORARY table_name (…) on commit (...) rows TABLESPACE volatile_tablespace_name

Control Statement

	SQL Server	Altibase
IF	If (condition) Else if (condition) Else End	If condition then Elseif condition then Else End
While	While (condition) BEING END BREAK => Exit the while loop	While condition loop End loop Exit when condition => Exit the loop when the condition is met

Identity Attribute

Altibase does not have an Identity property. Therefore, if needed, create a SEQUENCE and apply it as the DEFAULT value for the column.

For example:

SQL Server Altibase

-- Apply auto-increment by 1 to the c1 column.

create table seq_test
(c1 int identity(1,1) not null

...
);

-- create a sequence
create sequence seq1 start with 1 increment by 1 nocache;
-- Apply the sequence as the default value.

create table seq_test
(c1 integer default seq1.nextval not null,
...
);

Procedure Conversion

The syntax for creating and executing a PROCEDURE in Altibase differs from that of SQL Server.
This example demonstrates how to modify a PROCEDURE.

Parameter Declaration

When converting the parameter declaration section from SQL Server to Altibase:
Remove the '@' symbol used in SQL Server parameter declarations.
Specify in, out, or in out depending on the nature of each parameter.
For functions, since they return a single value upon execution, the data type must be specified after RETURN.

SQL Server	Altibase

Variable Declaration

In Altibase, variables are declared between AS and BEGIN.
Remove the @ symbol from variable declarations used in SQL Server.
Replace the delimiter , with ; when declaring variables.
Always end the last variable declaration with a ; to indicate the end.

SQL Server	Altibase

Assignment

In SQL Server, variables are assigned values using SET and the @ symbol.
Altibase supports two methods to modify this:

Remove SET and the @ symbol, replace = with :=, replace the delimiter , with ;, and always end with a ; to indicate the end.
Remove the @ symbol, replace the delimiter , with ;, and always end with a ; to indicate the end.

SQL Server	Altibase

Flow Control (Control_flow_statement) - IF statement

In Altibase, every statement must end with a ; to indicate the end.
An IF statement begins with IF (condition) THEN.
It ends with END IF to indicate the end of the IF block.

SQL Server	Altibase

SELECT clause

In Altibase, every statement must end with a ; to indicate the end.
To send a SELECT result set to the client in Altibase, a REF CURSOR must first be defined as a database object.
The REF CURSOR should then be included as a parameter in the stored procedure.
When executing the corresponding query, use the OPEN command.
Remove the @ symbol used in SQL Server.

SQL Server	Altibase

Exception Handler

In Altibase, every statement must end with a ; to indicate the end.
The Exception Handler in Altibase is used to handle specific exceptions when they occur.
Exception handling must always be written within a BEGIN ... END; block.
Specify either a system-defined or user-defined exception name; when that exception occurs, the corresponding statement will be executed.
If the specific exception is not handled, it will be caught by the OTHERS routine as a fallback.
To check if the number of affected records is zero, use the SQL%ROWCOUNT constant.
System-defined exceptions are included at the end of the file.

SQL Server	Altibase

SP Call

In Altibase, every statement must end with a ; to indicate the end.
In SQL Server, a stored procedure is called using exec sp_name without parentheses around the parameters.
In Altibase, the stored procedure is executed by specifying the procedure name followed by its parameters in parentheses.
Remove the @ symbol used in SQL Server.

SQL Server	Altibase

Functions

In Altibase, every statement must end with a ; to indicate the end.
Usage may vary by function, and function names may differ.
Remove the @ symbol used in SQL Server functions.

SQL Server	Altibase

ETC

In Altibase, every statement must end with a ; to indicate the end.
Remove the @ symbol used in SQL Server.

SQL Server	Altibase
`UPDATE STATISTICS index_name`	Not required
`with (nolock)`	Not required (remove)
Use of keywords like `order`, `level`	Do not use keywords like `order`, `level` (reserved in Altibase)

DB Link

When using a DB Link in Altibase, use REMOTE_TABLE(dblink_name, query) for SELECT statements, and REMOTE_EXECUTE_IMMEDIATE(dblink_name, query) for DML statements.
For more details about ALTIBASE DB LINK, refer to the ALTIBASE 7.3 Online Manual (Database Link User’s Manual).

JOIN Update

In Altibase, there are two methods to perform a join update:

1. Use JOIN UPDATE

Each table must have a Primary Key or a Unique Key.

2. Use an update with MERGE JOIN

Merge into A using ( SELECT … )
WHEN matched then
Update …

Using Cursor

Let's look at how to convert a SQL Server cursor to Altibase using an example.

SQL Server	Altibase

Exception Code

Refer to the Error Message Reference manual.

Exception Name	Error Code (integer)	Error Code (hexadecimal)	Error Section
"CURSOR_ALREADY_OPEN"	201062	31166	qpERR_ABORT_QSX_CURSOR_ALREADY_OPEN
"DUP_VAL_ON_INDEX"	201063	31167	qpERR_ABORT_QSX_DUP_VAL_ON_INDEX
"INVALID_CURSOR"	201064	31168	qpERR_ABORT_QSX_INVALID_CURSOR
"INVALID_NUMBER"	201065	31169	qpERR_ABORT_QSX_INVALID_NUMBER
"NO_DATA_FOUND"	201066	3116A	qpERR_ABORT_QSX_NO_DATA_FOUND
"PROGRAM_ERROR"	201067	3116B	qpERR_ABORT_QSX_PROGRAM_ERROR
"STORAGE_ERROR"	201068	3116C	qpERR_ABORT_QSX_STORAGE_ERROR
"TIMEOUT_ON_RESOURCE"	201069	3116D	qpERR_ABORT_QSX_TIMEOUT_ON_RESOURCE
"TOO_MANY_ROWS"	201070	3116E	qpERR_ABORT_QSX_TOO_MANY_ROWS
"VALUE_ERROR"	201071	3116F	qpERR_ABORT_QSX_VALUE_ERROR
"ZERO_DIVIDE"	201072	31170	qpERR_ABORT_QSX_ZERO_DIVIDE
"INVALID_PATH"	201237	31215	qpERR_ABORT_QSX_FILE_INVALID_PATH
"INVALID_MODE"	201235	31213	qpERR_ABORT_QSX_INVALID_FILEOPEN_MODE
"INVALID_FILEHANDLE"	201238	31216	qpERR_ABORT_QSX_FILE_INVALID_FILEHANDLE
"INVALID_OPERATION"	201239	31217	qpERR_ABORT_QSX_FILE_INVALID_OPERATION
"READ_ERROR"	201242	3121A	qpERR_ABORT_QSX_FILE_READ_ERROR
"WRITE_ERROR"	201243	3121B	qpERR_ABORT_QSX_FILE_WRITE_ERROR
"ACCESS_DENIED"	201236	31214	qpERR_ABORT_QSX_DIRECTORY_ACCESS_DENIED
"DELETE_FAILED"	201240	31218	qpERR_ABORT_QSX_FILE_DELETE_FAILED
"RENAME_FAILED"	201241	31219	qpERR_ABORT_QSX_FILE_RENAME_FAILED

MSSQL to ALTIBASE Conversion Guide