Sybase ASE to Microsoft SQL Server Migration Reference

Technical information on migration from Sybase Adaptive Server Enterprise to Microsoft SQL Server.

Last Update: Sybase Adaptive Server Enterprise 15.0 and Microsoft SQL Server 2012

Data Types

Converting data types:

Sybase ASE SQL Server
1 BIGDATETIME Date and time with fraction DATETIME2(6)
2 BIGINT 64-bit integer BIGINT
3 BIGTIME Time (Hour, minute, second and fraction) TIME(6)
4 BINARY(n) Fixed-length binary string BINARY(n)
5 BIT 0 or 1; NULL is not allowed BIT
6 CHAR(n), CHARACTER(n) Fixed-length string CHAR(n), CHARACTER(n)
7 DATE Date (year, month and day) DATE
8 DATETIME Date and time with fraction DATETIME2(6)
9 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
10 DOUBLE PRECISION Double-precision floating-point number FLOAT
11 FLOAT(p) Floating-point number FLOAT
12 IMAGE Variable-length binary data, ⇐ 2G VARBINARY(max)
13 INT, INTEGER 32-bit integer INT, INTEGER
14 MONEY 64-bit currency amount MONEY
15 NCHAR(n) Fixed-length national character string NCHAR(n)
16 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
17 NVARCHAR(n) Variable-length national character string NVARCHAR(n)
18 REAL Single-precision floating-point number REAL
19 SMALLDATETIME Date and time SMALLDATETIME
20 SMALLINT 16-bit integer SMALLINT
21 SMALLMONEY 32-bit currency amount SMALLMONEY
22 TEXT Variable-length character data, ⇐ 2G VARCHAR(max)
23 TIME Time (Hour, minute, second and fraction) TIME(6)
24 TINYINT 8-bit unsigned integer, 0 to 255 TINYINT
25 UNICHAR(n) Fixed-length Unicode string NCHAR(n)
26 UNITEXT Variable-length Unicode data, ⇐ 1G NVARCHAR(max)
27 UNIVARCHAR(n) Variable-length Unicode string NVARCHAR(n)
28 UNSIGNED BIGINT 64-bit unsigned integer NUMERIC(20)
29 UNSIGNED INT 32-bit unsigned integer NUMERIC(10)
30 UNSIGNED SMALLINT 16-bit unsigned integer NUMERIC(5)
31 VARBINARY(n) Variable-length binary string VARBINARY(n)
32 VARCHAR(n) Variable-length string VARCHAR(n)

Built-in SQL Functions

Converting functions:

Sybase ASE SQL Server
1 ABS(num) Get the absolute value ABS(num)
2 ACOS(num) Get the arc cosine ACOS(num)
3 ASCII(str) Get ASCII code of left-most char ASCII(str)
4 ASEHOSTNAME() Get the workstation name HOST_NAME()
5 ASIN(num) Get the arc sine ASIN(num)
6 ATAN(num) Get the arc tangent ATAN(num)
7 ATN2(x, y) Get arctangent of x and y ATN2(x, y)
8 BIGINTTOHEX(exp) Convert bigint to 8-byte hex CONVERT(VARBINARY(8), CAST(exp AS BIGINT))
9 BINTOSTR(exp) Convert hexadecimal to string
10 CEILING(num) Get the smallest following integer CEILING(num)
11 CHAR(num) Get character from ASCII code CHAR(num)
12 CHAR_LENGTH(string) Get length of string in characters LEN(string)
13 CHARINDEX(substr, str) Get position of substr CHARINDEX(substr, str)
14 COALESCE(exp1, exp2, …) Return first non-NULL expression COALESCE(exp1, exp2, …)
15 COL_LENGTH('table', 'col') Get length of column COL_LENGTH('table', 'col')
16 COL_NAME(tbl_id, col_id) Get name of column COL_NAME(tbl_id, col_id)
17 COMPARE(str, str2) Compare two strings User-defined function
18 CONVERT(dataype, exp) Convert to another datatype CONVERT(dataype, exp)
19 COS(num) Get the cosine COS(num)
20 COT(num) Get the cotangent COT(num)
21 CURRENT_BIGDATETIME() Get the current date and time GETDATE()
22 CURRENT_BIGTIME() Get the current time with fraction GETDATE()
23 CURRENT_DATE() Get the current date CONVERT(DATE, GETDATE())
24 CURRENT_TIME() Get the current time GETDATE()
25 DATALENGTH(exp) Get length of exp in bytes DATALENGTH(exp)
26 DATEADD(unit, num, date) Add an interval to datetime DATEADD(unit, num, date)
27 DATEDIFF(unit, start, end) Get datetime difference in units DATEDIFF(unit, start, end)
28 DATENAME(datepart, date) Get datepart as string DATENAME(datepart, date)
29 DATEPART(datepart, date) Get datepart as interger DATEPART(datepart, date)
30 DAY(datetime) Extract day from datetime DAY(datetime)
31 DB_ID(['database_name']) Get ID number of database DB_ID(['database_name'])
32 DB_INSTANCEID() Get ID number of instance @@SERVICENAME
33 DB_NAME(['database_id']) Get the name of database DB_NAME(['database_id'])
34 DEGREES(num) Convert radians to degrees DEGREES(num)
35 EXP(n) Raise e to the nth power EXP(n)
36 FLOOR(num) Get the largest preceding integer FLOOR(num)
37 GETDATE() Get the current date and time GETDATE()
38 GETUTCDATE() Get the current UTC datetime GETUTCDATE()
39 HEXTOBIGINT(exp) Convert hexadecimal to bigint CONVERT(BIGINT, exp)
40 HEXTOINT(exp) Convert hexadecimal to integer CONVERT(INT, exp)
41 HOST_ID() Get the workstation ID HOST_ID()
42 HOST_NAME() Get the workstation name HOST_NAME()
43 INDEX_COL(obj, id, key) Get indexed column name INDEX_COL(obj, id, key)
44 INDEX_COLORDER Get column order User-defined function
45 INDEX_NAME Get index name User-defined function
46 INSTANCE_ID() Get ID number of instance @@SERVICENAME
47 INSTANCE_NAME() Get name of instance @@SERVICENAME
48 INTTOHEX(integer) Convert integer to hexadecimal CONVERT(VARBINARY(8), integer)
49 ISDATE(string) Check for a valid datetime ISDATE(string)
50 ISNULL(exp, replace) Replace NULL ISNULL(exp, replace)
51 ISNUMERIC(string) Check for a valid numeric ISNUMERIC(string)
52 IS_SINGLEUSERMODE() Check for a single-user mode User-defined function
53 LEFT(string, n) Get n leftmost characters LEFT(string, n)
54 LEN(string) Get length of string in characters LEN(string)
55 LOG(numeric) Get natural logarithm LOG(numeric)
56 LOG10(numeric) Get base 10 logarithm LOG10(numeric)
57 LOWER(string) Lowercase string LOWER(string)
58 LTRIM(string) Remove leading spaces LTRIM(string)
59 MONTH(date) Extract month from date MONTH(date)
60 NEWID([flag]) Generate GUIDs NEWID()
61 NEXT_IDENTITY('table') Get next identity value of table IDENT_CURRENT('table') + IDENT_INCR('table')
62 NULLIF(exp1, exp2) Return NULL if exp1 = exp2 NULLIF(exp1, exp2)
63 OBJECT_ID(name) Get database object ID OBJECT_ID(name)
64 OBJECT_NAME(id [,db_id]) Get database object name OBJECT_NAME(id [,db_id])
65 OBJECT_OWNER_ID(id) Get database object’s owner ID OBJECTPROPERTY(id, 'OwnerId')
66 PARTITION_ID(table, ptn) Get partition ID User-defined function
67 PARTITION_NAME(indid, id) Get partition name
68 PARTITION_OBJECT_ID(id) Get object ID for specified ptn ID User-defined function
69 PASSWORD_RANDOM(len) Get pseudorandom password User-defined function
70 PATINDEX(pattern, string) Get starting position of pattern PATINDEX(pattern, string)
71 PI() Get number pi PI()
72 POWER(value, n) Raise value to the nth power POWER(value, n)
73 RADIANS(numeric) Convert degrees to radians RADIANS(numeric)
74 RAND([integer]) Get random float value in (0, 1) RAND([integer])
75 RAND2() Var. RAND for each returned row
76 REPLICATE(string, n) Repeat string n times REPLICATE(string, n)
77 RESERVE_IDENTITY(tbl, n) Repeat string n times
78 REVERSE(string) Get reverse string REVERSE(string)
79 RIGHT(string, n) Get n rightmost characters RIGHT(string, n)
80 ROUND(num, integer) Get rounded value ROUND(num, integer)
81 RTRIM(string) Remove trailing spaces RTRIM(string)
82 SIGN(exp) Get sign of exp SIGN(exp)
83 SIN(num) Get sine SIN(num)
84 SOUNDEX(string) Get 4-character sound code SOUNDEX(string)
85 SPACE(integer) Get string of spaces SPACE(integer)
86 SPID_INSTANCE_ID(spid) Get instance ID by spid
87 SQUARE(exp) Get square SQUARE(exp)
88 SQRT(num) Get square root SQRT(num)
89 STR_REPLACE(s, sub, r) Replace substring REPLACE(s, sub, r)
90 STRTOBIN(exp) Convert string to hexadecimal
91 STUFF(exp, start, len, rep) Replace characters in string STUFF(exp, start, len, rep)
92 SUBSTRING(exp, pos, len) Get a substring of exp SUBSTRING(exp, pos, len)
93 SUSER_ID(exp) Get the server user’s ID SUSER_ID(exp)
94 SUSER_NAME(exp) Get the server user’s name SUSER_NAME(exp)
95 TAN(num) Get tangent TAN(num)
96 TEXTPTR(exp) Get pointer that conform to LOB TEXTPTR(exp)
97 TEXTVALID(exp, pointer) Check validity of a pointer TEXTVALID(exp, pointer)
98 TO_UNICHAR(int) Convert int to Unicode character CONVERT(NVARCHAR, int)
99 TSEQUAL(value1, value2) Compare two timestamp values value1 = value2
100 UHIGHSURR(exp, start) Check for higher part of surrogate pair
101 ULOWSURR(exp, start) Check for lower part of surrogate pair
102 UPPER(string) Uppercase string UPPER(string)
103 USCALAR(exp) Get Unicode code of exp UNICODE(exp)
104 USER Get the current user SYSTEM_USER
105 USER_ID([user_name]) Get user ID USER_ID([user_name])
106 USER_NAME([user_id]) Get user name USER_NAME([user_id])
107 XMLEXTRACT(xpath, exp) Extract XML data
108 XMLPARSE(exp) Parse XML document
109 XMLREPRESENTATION(exp) Check for XML content
110 XMLVALIDATE(exp) Get XML with information
111 YEAR(date) Extract year from date YEAR(date)