1. Object Conversion

DATATYPE
FUNCTION & EXPRESSION
OBJECT
CREATE TABLESPACE
CREATE TABLE
CREATE USER
CREATE INDEX

This chapter describes considerations when converting OBJECTs from MS SQL Server to ALTIBASE.

DATATYPE

This section describes how to convert each DATATYPE when converting SQL SERVER TABLE to ALTIBASE.

DATATYPE	SQL Server	ALTIBASE
BINARY	Use BYTE
BIT	BIT
DATETIME	Use DATE
DATETIME2	Use DATE	The scale of Altibase supports up to microseconds (6 digits)
DATETIMEOFFSET	Use DATE
IMAGE	Use BLOB
NCHAR	NCHAR	Max length: 16000(UTF16), Max length: 10666(UTF8)
NTEXT	Use NVARCHAR	Max length: 16000(UTF16), Max length: 10666(UTF8)
NUMERIC	NUMERIC
NVARCHAR	NVARCHAR	Max length: 16000(UTF16), Max length: 10666(UTF8) NVARCHAR(MAX) converts to CLOB
REAL	REAL
SAMMLLDATETIME	Use DATE
SMALLINT	SMALLINT
TEXT	Use CLOB
TIME	Use DATE
VARBINARY	Use BLOB
VARCHAR	VARCHAR,	varchar(max) converts to CLOB
DATE TYPE	DATE	DATE
CHARACTER TYPE	CHAR	CHAR
INTEGER TYPE	BIGINT	BIGINT

FUNCTION & EXPRESSION

	SQL Server	ALTIBASE	REMARK
Aggregate	AVG	AVG
CHECKSUM_AGG	N/A
COUNT	COUNT
COUNT_BIG	COUNT
GROUPING	N/A
GROUPING_ID	N/A
MAX	MAX
MIN	MIN
STDEV	Use STDDEV
STDEVP	N/A
SUM	SUM
VAR	VARIANCE
Ranking	DENSE_RANK	DENSE_RANK
NTILE	N/A
RANK	RANK
ROW_NUMBER	ROW_NUMBER
Conversion	CAST, CONVERT	Use CAST	CONVERT of ALTIBASE function provides other functions
PARSE	Use CAST	Character set can be converted by using the CONVERT function
TRY_CAST	Use CAST	Error returns when casting fails
TRY_CONVERT	N/A
TRY_PARSE	N/A
Date	CURRENT_TIMESTAMP	Use SYSDATE
DATENAME	DATENAME
DATEPART	DATEPART
DAY	Use DATEPART
DATEADD	DATEADD	Statement change DATEADD (date, number, date_field_name)
Date	DATEDIFF	DATEDIFF	Statement change DATEDIFF (startdate, enddate, date_field_name)
DATEFROMPARTS	Use TO_DATE
DATETIME2FROMPARTS	Use TO_DATE
DATETIMEFROMPARTS	Use TO_DATE
DATETIMEOFFSETFROMPARTS	Use TO_DATE
EOMONTH	Use LAST_DAY
GETDATE	Use SYSDATE
GETUTCDATE	N/A
ISDATE	N/A
SMALLDATETIMEFROMPARTS	Use TO_DATE
SWITCHOFFSET	N/A
SYSDATETIME	Use SYSDATE
SYSDATETIMEOFFSET	N/A
SYSUTCDATETIME	N/A
TIMEFROMPARTS	Use TO_DATE
TODATETIMEOFFSET	N/A
YEAR	Use TO_CHAR
Logical	CHOOSE	N/A
IIF	Use NVL2
Mathematical	ABS	ABS
ACOS	ACOS
ASIN	ASIN
ATAN	ATAN
ATN2	ATN2
CEILING	Use CEIL
Mathematical	COS	COS
COT	N/A
DEGREES	N/A
EXP	EXP
FLOOR	FLOOR
LOG	Use LN	LOG of Altibase provides other functions
LOG10	N/A
PI	N/A
POWER	POWER
RADIANS	N/A
RAND	Use RANDOM	ALTIBASE returns in integer format
ROUND	ROUND
SIGN	SIGN
SIN	SIN
SQRT	SQRT
SQUARE	N/A
TAN	TAN
TAN	TAN
String	ASCII	ASCII
CHAR	CHAR
CHARINDEX	Use INSTR, POSITION
CONCAT	CONCAT
DIFFERENCE	N/A
FORMAT	N/A
LEFT	Use SUBSTR, SUBSTRING	SUBSTR (expr, start [DOCKI:, length]) Assign a positive number to Start
LEN	Use LENGTH
LOWER	LOWER
LTRIM	LTRIM	LTRIM (expr1 [DOCKI:,expr2]) in ALTIBASE
NCHAR	NCHAR
String	PATINDEX	Use INSTR, POSITION
QUOTENAME	N/A
REPLACE	Use REPLACE2
REPLICATE	REPLICATE
REVERSE	Use REVERSE_STR
RIGHT	Use SUBSTR, SUBSTRING	SUBSTR (expr, start [DOCKI:, length]) Assign a negative number to Start
RTRIM	RTRIM	RTRIM (expr1 [DOCKI:,expr2]) in ALTIBASE
PATINDEX	Use INSTR, POSITION
QUOTENAME
REPLACE	Use REPLACE2
REPLICATE	REPLICATE
REVERSE	Use REVERSE_STR
RIGHT	Use SUBSTR, SUBSTRING	SUBSTR (expr, start [DOCKI:, length]) Assign a negative number to Start
RTRIM	RTRIM	RTRIM (expr1 [DOCKI:,expr2]) in ALTIBASE
SOUNDEX	N/A
SPACE	Use LPAD, RPAD
STR	Use TO_CHAR
STUFF	STUFF
SUBSTRING	SUBSTRING
UNICODE	N/A
UPPER	UPPER

OBJECT

	SQL Server	ALTIBASE	REMARK
CONSTRAINT	Supported	Supported
TRIGGER	Supported	Supported
Multi Key-Index	Supported	Supported
VIEW	Supported	Supported
UPDATABLE VIEW	Supported	Not supported
SEQUENCE	Supported	Supported
STORED FUNCTION/PROCEDURE	Supported	Supported
SYNONYM	Supported	Supported
TABLE Temporary Table	Supported	Supported	Temporary Table uses Volatile Table Space
USER	Supported	Supported
REPLICATION	Supported	Supported

CREATE TABLESPACE

The SQL Server manages the database as data storage, but ALTIBASE HDB manages the tablespace. Tablespaces in ALTIBASE HDB are classified into memory tablespaces and disk tablespaces according to the storage space. Depending on the creator, it is classified into system tablespaces, user-defined tablespaces, dictionary tablespaces, undo tablespaces, temporary tablespaces, data tablespaces, etc.

Therefore, when converting to ALTIBASE HDB, it is created using the CREATE MEMORY DATA TABLESPACE and CREATE DISK DATA TABLESPACE statements according to the data storage space.

The following describes various options specified in the CREATE TABLESPACE statement when converting SQL Server DATABASE to ALTIBASE HDB tablespace.

SQL Server	ALTIBASE	REMARK
FILENAME	FILENAME
SIZE	SIZE	Default: 100MB
MAXSIZE	MAXSIZE
FILEGROWTH	AUTOEXTEND ON NEXT
FILESTREAM	N/A
DEFAULT_FULLTEXT_LANGUAGE	N/A
DEFAULT_LANGUAGE	N/A
NESTED_TRIGGERS	N/A
TRANSFORM_NOISE_WORDS	N/A
TWO_DIGIT_YEAR_CUTOFF	N/A
DB_CHAINING	N/A
TRUSTWORTHY	N/A

CREATE TABLE

When converting TABEL from SQL Server to ALTIBASE, various options used in CREATE TABLE must be appropriately changed. ALTIBASE does not provide TEMPORARY TABLE, OBJECT TABLE, or XMLType TABLE.

ALTIBASE provides a memory table, therefore, a memory table is created by grasping the characteristics of the table to be converted. the memory tablespace must be specified. If a memory table is created, the options used in the CREATE TABLE statement in SQL Server cannot be used. For the statement for creating a memory table, please refer to the ATLIBASE SQL manual. If the user wants to convert the ORACLE TABLE to DISK TABLE, the user must convert various options that can be set when executing CREATE TABLE according to ALTIBASE as follows.

1. Column definition

SQL Server	ALTIBASE	REMARK
FILESTREAM	N/A
COLLATE	N/A
CONSTRAINT	CONSTRAINT	ALTIBASE supports constraints excluding CHECK. Also when the constraint is specified, the ON DELETE SET NULL (ON DELETE CASCADE is supported) option in the reference clause is not provided, so delete it. ALTIBASE does not provide an index name when specifying the PRIMARY KEY or UNIQUE.
IDENTITY	N/A
ROWGUIDCOL	N/A

2. data type

SQL Server	ALTIBASE	REMARK
Precision, scale	Precision, scale
max	N/A
CONTENT	N/A
DOCUMENT	N/A
xml_schema_collection	N/A

3. column constraint

SQL Server	ALTIBASE	REMARK
PRIMARY KEY	PRIMARY KEY
NULL, NOT NULL	NULL, NOT NULL
UNIQUE	UNIQUE
CLUSTERED, NONCLUSTERED	N/A
FOREIGN KEY REFERENCES	FOREIGN KEY REFERENCES
partition_scheme_name	PARTITION BY RANGE \| HASH \| LIST	When creating a table, specify the partition table type

4. computed column definition

Not supported

5. table constraint

SQL Server	ALTIBASE	REMARK
PRIMARY KEY	PRIMARY KEY
CLUSTERED, NONCLUSTERED	N/A
FOREIGN KEY REFERENCES	FOREIGN KEY REFERENCES
partition_scheme_name	PARTITION BY RANGE \| HASH \| LIST	When creating a table, specify the partition table type

6. table_option

SQL Server	ALTIBASE	REMARK
DATA_COMPRESSION	N/A

7. index_option

SQL Server	ALTIBASE	REMARK
PAD_INDEX	N/A
FILLFACTOR	N/A
IGNORE_DUP_KEY	N/A	ALTIBASE is ON
STATISTICS_NORECOMPUTE	N/A	ALTIBASE is OFF
ALLOW_ROW_LOCKS	N/A	Define according to Durability Level setting
ALLOW_PAGE_LOCKS	N/A	Define according to Durability Level setting
DATA_COMPRESSION	N/A

CREATE USER

ALTIBASE does not create database login and user separately but creates and manages them as a single user.

SQL Server

ALTIBASE

CREATE INDEX

ALTIBASE provides only BTREE and RTREE INDEX, but does not provide BITMAP, CLUSTER, Function-based, REVERSE, and Global partitioned INDEX.

The following is a conversion method for the options used in the CREATE INDEX statement when converting to ALTIBASE.

SQL Server	ALTIBASE	REMARK
UNIQUE	UNIQUE
CLUSTERED \| NONCLUSTERED	N/A
ASC \| DESC	ASC \| DESC
INCLUDE	N/A
filter_predicate	N/A
partition_scheme_name	PARTITION ON	Only Local Index is supported
ON filegroup_name	N/A
table_or_view_name	Table_name	View is not supported
PAD_INDEX	N/A
FILLFACTOR	N/A
SORT_IN_TEMPDB	N/A
IGNORE_DUP_KEY	N/A	ALTIBASE is ON
STATISTICS_NORECOMPUTE	N/A	ALTIBASE is OFF
DROP_EXISTING	N/A
ONLINE		ALTIBASE is OFF
ALLOW_ROW_LOCKS	N/A	Define according to Durability Level setting
ALLOW_PAGE_LOCKS	N/A	Define according to Durability Level setting
MAXDOP	PARALLEL	Apply only when creating
DATA_COMPRESSION	N/A