Sybase 15.0.1 |
Oracle 10gR2 |
Verbiage |
Instance |
Database |
Instance |
Database |
Schema |
User |
User |
System & User Databases
master |
model |
sybsyntax |
sybsystemdb |
sybsystemprocs |
tempdb |
no equivalent
... uses its log files |
|
Schemas
SYS and SYSTEM
schemas |
SYS and SYSTEM
schemas |
SYS and SYSTEM
schemas |
SYS and SYSTEM
schemas |
SYS and SYSTEM
schemas |
temporary tablespace |
undo (rollback)
segment |
|
Storage
Concepts
Row |
no equivalent |
Page |
Extent |
Segment |
Datafile |
Tempfile |
DBSpace |
no equivalent |
|
Storage
Concepts
Row |
Undo |
Block |
Extent |
Segment |
Datafile |
Tempfile |
Tablespace |
Default
Tablespace |
|
Note:
The the way primary and secondary datafiles are mapped in SQL Server does
not relate to how data files are mapped in Oracle. |
no
equivalent |
Real Application Cluster |
Companion Mode |
Data Guard |
Suspended Mode |
Standby Server |
BCP |
SQL*Loader |
Primary Data File |
System Data Files |
Secondary Data File |
Data File |
Log File |
Log File |
Note:
The log file concept and architecture are completely different |
Truncate Transaction Logs |
Log Files Not Used For
Rollback |
|
Object Types |
Clusters
no
equivalent |
Cluster
by Hash |
no
equivalent |
Cluster
by Index |
no
equivalent |
Sorted
Hash Cluster |
|
|
Constraints
Primary Key |
Primary Key |
Unique
Key |
Unique |
Foreign
Key |
Referential
(Foreign Key) |
no equivalent |
Referential
ON DELETE CASCADE |
no equivalent |
Referential
ON DELETE SET NULL |
no equivalent |
Deferrable |
no equivalent |
Rely
/ Norely |
no equivalent |
Validate
/ Novalidate |
Check
& Rule |
Check |
Null
/ Not Null |
Null
/ Not Null |
no
equivalent |
Read
Only |
no
equivalent |
REF
(Nested Table Constraint) |
Table Level
Constraints |
no equivalent |
|
|
Database Linkages
Linked Server |
Database (DB) Link |
|
|
Dimension
|
|
Functions (user defined)
Function |
Function |
no
equivalent |
Pipelined
Table Function |
IN
and OUT Parameters Only |
IN,
OUT, and IN-OUT Parameters |
Parameter
Default |
Parameter
Default |
|
|
Indexes
Index |
B*Tree |
no
equivalent |
Bitmap |
Bitmap
Join |
Bitmap
Join |
no
equivalent |
Cluster |
Clustered Index |
Index Organized
Table |
no
equivalent |
Compressed |
no
equivalent |
Descending |
Function
Based |
Function
Based |
Global |
Global |
Local |
Local |
no
equivalent |
No
Segment (Virtual) |
Non-Unique |
Non-Unique |
no
equivalent |
Reverse |
no
equivalent |
REF |
Unique |
Unique |
|
|
Libraries
|
|
Materialized Views
no
equivalent |
Materialized
Views of any type |
|
|
Operators (user defined)
no
equivalent |
Operator |
no
equivalent |
Overloading |
|
|
Packages
no
equivalent |
Package
Header |
no
equivalent |
Package
Body |
no
equivalent |
Initialization
Section |
no
equivalent |
Overloading |
no
equivalent |
Serial
Reusability |
|
|
Procedures (user defined)
Procedure |
Stored
Procedure |
IN
and OUT Parameters Only |
IN,
OUT, and IN-OUT Parameters |
Parameter
Default |
Parameter
Default |
no
equivalent |
NOCOPY |
no
equivalent |
AUTHID |
|
|
Schemas
|
|
Surrogate Key Generator
no
equivalent |
Sequence |
Identity
& NEWID |
no
equivalent |
|
|
Synonyms
no
equivalent |
Private
Synonym |
no
equivalent |
Public
Synonym |
|
|
Tables
Table |
Heap
Table |
Cluster Index |
Index Organized
Table |
Column
Default |
Column
Default |
no
equivalent |
Compressed
Table |
Computed
Column |
Function
Based Index |
no
equivalent |
External Table |
no
equivalent |
Global
Temporary Table
(con commit delete rows) |
no
equivalent |
Global
Temporary Table
(on commit preserve rows) |
no
equivalent |
Nested Table |
Partition |
not
relevant due to architecture |
no
equivalent |
Hash
Partitioned |
no
equivalent |
List
Partitioned Table |
no
equivalent |
Range
Partitioned Table
|
no
equivalent |
Subpartitioned
Table |
Temporary Table |
not
relevant due to MVCC |
no
equivalent |
XML Table |
|
|
Types
Type |
Type |
no
equivalent |
Type
Header |
no
equivalent |
Type
Body with Methods |
no
equivalent |
Object |
no
equivalent |
VArray |
|
|
Views
View |
View |
Check
Option |
Check
Option |
no
equivalent |
ORDER
BY |
|
|
Built-in
Operators |
Arithmetic
Operators
+ (add) |
+ (add) |
- (subtract) |
- (subtract) |
* (multiply) |
* (multiply) |
/ (divide) |
/ (divide) |
% (modulo) |
mod
function |
power
function |
**
(power) |
|
Assignment
Operators
= (equals) |
:= (colon
equals) |
|
Bitwise
Operators
&
(bitwise
AND) |
utl_raw.bit_and |
|
(bitwise OR) |
utl_raw.bit_or |
^
(bitwise
exclusive OR) |
utl_raw.bit_xor |
~
(bitwise NOT) |
no equivalent |
no equivalent |
utl_raw.bit_complement |
|
Comparison
Operators
= (equal to) |
= (equal to) |
> (greater
than) |
> (greater
than) |
< (less
than) |
< (less
than) |
>= (greater
than or equal to) |
>= (greater
than or equal to) |
<= (less
than or equal to) |
<= (less
than or equal to) |
<> (not
equal to) |
<> (not
equal to) |
!= (not equal
to) |
!= (not equal
to) |
!=
(not
equal to) |
=^ (not equal
to) |
!= (not
equal to) |
~= (not equal
to) |
!< (not less
than) |
no equivalent |
!> (not
greater than) |
no equivalent |
|
Date
Operators
no equivalent |
+ (add) |
no equivalent |
- (subtract) |
|
Hierarchical
Operators
no equivalent |
CONNECT |
no equivalent |
CONNECT BY |
no equivalent |
CONNECT BY
PRIOR |
no equivalent |
CONNECT BY ROOT |
|
Conditions
ALL |
ALL |
AND |
AND |
ANY |
ANY |
BETWEEN |
BETWEEN |
CONTAINS |
Context
Operator |
no
equivalent |
DEPTH |
no
equivalent |
EMPTY |
no
equivalent |
EQUALS_PATH |
EXISTS |
EXISTS |
IN |
INFINITE |
no
equivalent |
IS A SET |
no
equivalent |
IS ANY |
IS
NULL |
IS NULL |
no
equivalent |
IS OF ONLY |
no
equivalent |
IS OF TYPE |
no
equivalent |
IS PRESENT |
LIKE |
LIKE |
no
equivalent |
MEMBER OF |
no
equivalent |
NAN |
NOT |
NOT |
OR |
OR |
no
equivalent |
PATH |
no
equivalent |
SOME |
no
equivalent |
SUBMULTISET OF |
no
equivalent |
UNDER_PATH |
|
Multiset
Operators
no equivalent |
MULTISET |
no equivalent |
MULTISET EXCEPT |
no equivalent |
MULTISET
INTERSECT |
no equivalent |
MULTISET UNION |
|
Set
Operators
INTERSECT |
INTERSECT |
no equivalent |
MINUS |
MERGE_UNION_ALL |
no equivalent |
UNION |
UNION |
UNION
ALL |
UNION
ALL |
|
String
Operators
+
(concatenation) |
||
(concatenation) |
|
Unary
Operators
+ (positive) |
+ (positive) |
- (negative) |
- (negative) |
|
|
Triggers |
DDL Triggers
no equivalent |
DATABASE |
no equivalent |
ALTER |
no equivalent |
ASSOCIATE
STATISTICS |
no equivalent |
AUDIT |
no equivalent |
COMMENT |
no equivalent |
CREATE |
no equivalent |
SCHEMA |
no equivalent |
(not
irrelevant) |
no equivalent |
DDL |
no equivalent |
DISASSOCIATE
STATISTICS |
no equivalent |
DROP |
no equivalent |
GRANT |
no equivalent |
NOAUDIT |
no equivalent |
RENAME |
no equivalent |
REVOKE |
no equivalent |
SUSPEND |
no equivalent |
TRUNCATE |
no equivalent |
ANALYZE |
|
Instead-Of Triggers
VIEW
TRIGGER |
INSTEAD-OF
TRIGGER |
|
System Triggers
no equivalent |
DATABASE |
no equivalent |
SCHEMA |
no equivalent |
AFTER LOGON |
no equivalent |
AFTER SERVERERROR |
no equivalent |
AFTER STARTUP |
no equivalent |
BEFORE LOGOFF |
no equivalent |
BEFORE SHUTDOWN |
|
Table Triggers
no equivalent |
before insert
statement level |
no equivalent |
before update
statement level |
no equivalent |
before delete
statement level |
no equivalent |
before insert
row level |
no equivalent |
before update
row level |
no equivalent |
before delete
row level |
after insert
trigger |
after insert
statement level |
after update
trigger |
after update
statement level |
after delete
trigger |
after delete
statement level |
no equivalent |
after insert
row level |
no equivalent |
after update
row level |
no equivalent |
after delete
row level |
no equivalent |
OF Clause |
no equivalent |
REFERENCING
Clause |
no equivalent |
native
compilation and wrap |
|
|
Functions |
Analytic Functions
Running
average |
no equivalent |
AVG |
Coefficient
of correlation |
no equivalent |
CORR |
Running
count by partition |
no equivalent |
COUNT |
Population
covariance of a set of pairs |
no equivalent |
COVAR_COUNT |
Sample
covariance of a set of pairs |
no equivalent |
COVAR_SAMP |
Cumulative
distribution in a group |
no equivalent |
CUME_DIST |
Rank
within a group without gaps |
DENSE_RANK |
DENSE_RANK |
Row
ranked first by DENSE RANK |
no equivalent |
FIRST |
First
value of an ordered set |
no equivalent |
FIRST_VALUE |
Provides
access to a row by offset |
no equivalent |
LAG |
Row
ranked last by DENSE RANK |
no equivalent |
LAST_VALUE |
Last
value of an ordered set |
no equivalent |
LAST_VALUE |
Provides
access to a row by offset |
no equivalent |
LEAD |
Maximum
value by partition |
no equivalent |
MAX |
Minimum
value by partition |
no equivalent |
MIN |
Divides
an ordered dataset into buckets |
NTILE |
NTILE |
Rowset
partitioning |
OVER |
OVER |
Calculates
the value of r-1/rows-1 |
PERCENT_RANK |
PERCENT_RANK |
An
inverse distribution function |
PERCENTILE_CONT |
PERCENTILE_CONT |
An
inverse distribution function |
PERCENTILE_DISC |
PERCENTILE_DISC |
Rank of a
value in a group |
RANK |
RANK |
Computes
ratio of a value to the sum of a set |
no equivalent |
RATIO_TO_REPORT |
Linear
regression function |
no equivalent |
REGR_AVGX |
Linear
regression function |
no equivalent |
REGR_AVGY |
Linear
regression function |
no equivalent |
REGR_COUNT |
Linear
regression function |
no equivalent |
REGR_INTERCEPT |
Linear
regression function |
no equivalent |
REGR_R2 |
Linear
regression function |
no equivalent |
REGR_SLOPE |
Linear
regression function |
no equivalent |
REGR_SXX |
Linear
regression function |
no equivalent |
REGR_SXY |
Linear
regression function |
no equivalent |
REGR_SYY |
Assigns
row numbers by partition |
no equivalent |
ROW_NUMBER |
Sample
standard deviation |
no equivalent |
STDDEV |
Square
root of the population variance |
STDDEV_POP |
STDDEV_POP |
Cumulative
sample standard deviation |
no equivalent |
STDDEV_SAMP |
Cumulative
running total |
no equivalent |
SUM |
Population
variance of a set |
VAR_POP |
VAR_POP |
Sample
variance of a set |
no equivalent |
VAR_SAMP |
Variance
of an expression |
no equivalent |
VARIANCE |
|
Collection Functions
Number of
elements in a nested table |
no equivalent |
CARDINALITY |
Creates a
nested table from selected rows |
no equivalent |
COLLECT |
Creates a
nested table of nonempty subsets |
no equivalent |
POWERMULTISET |
As above:
Of the specified cardinality |
no equivalent |
POWERMULTISET_BY_CARDINALITY |
Converts
a nested table into a unique set |
no equivalent |
SET |
|
Conversion Functions
ASCII
string into the DB character set |
no equivalent |
ASCIISTR |
BFILE
from directory + file name |
no equivalent |
BFILENAME |
Integer
to hex |
BIGINTTOHEX |
RAWTOHEX |
Bitvector
to a number |
no equivalent |
BIN_TO_NUM |
One data
type to another |
CAST
& CONVERT |
CAST |
String to a ROWID |
not relevant |
CHARTOROWID |
String to
a unicode string |
no equivalent |
COMPOSE |
One
character set to another |
no equivalent |
CONVERT |
Unicode
string to a string |
no equivalent |
DECOMPOSE |
Hex to
integer |
HEXTOINT |
TO_NUMBER |
Char
containing hexidecimal digits to raw |
no equivalent |
HEXTORAW |
Integer
to hex |
INTTOHEX |
RAWTOHEX |
Number
into a Day-to-Second interval |
no equivalent |
NUMTODSINTERVAL |
Number
into a Year-to-Month interval |
no equivalent |
NUMTOYMINTERVAL |
A value
to its hash |
no equivalent |
ORA_HASH |
Raw into
a hexidecimal containing string |
no equivalent |
RAWTOHEX |
Raw into
a hexidecimal containing 'N' string |
no equivalent |
RAWTONHEX |
Raw into
a hexidecimal object REF |
no equivalent |
REFTOHEX |
ROWID to CHAR |
not relevant |
ROWIDTOCHAR |
ROWID to NCHAR |
not relevant |
ROWIDTONCHAR |
Timestamp to its SCN equivalent |
not relevant |
SCNTOTIMESTAMP |
Converts RAW to NUMBER |
no equivalent |
SYS_OP_RAWTONUM |
An SCN to its timestamp equivalent |
not relevant |
TIMESTAMPTOSCN |
Convert
to
BINARY_DOUBLE data type |
no equivalent |
TO_BINARYDOUBLE |
Convert
to
BINARY_FLOAT data type |
no equivalent |
TO_BINARYFLOAT |
Convert
to CHAR
or VARCHAR2 data type |
DATEFORMAT
& STR |
TO_CHAR |
Convert
to CLOB
data type |
no equivalent |
TO_CLOB |
Convert
to DATE
data type |
DATE
(more limited) |
TO_DATE |
Convert
to
Day-to-Second Interval data type |
no equivalent |
TO_DSINTERVAL |
LONG or
LONG RAW to LOB data type |
no equivalent |
TO_LOB |
Single
byte to corresponding multi-byte |
no equivalent |
TO_MULTI_BYTE |
Convert
to NCHAR
data type |
no equivalent |
TO_NCHAR |
Convert
to NCLOB
data type |
no equivalent |
TO_NCLOB |
Convert
to NUMBER
data type |
CONVERT |
TO_NUMBER |
Multi-byte
to corresponding single byte |
no equivalent |
TO_SINGLE_BYTE |
Convert
to
TIMESTAMP data type |
DATETIME
(more limited) |
TO_TIMESTAMP |
To
TIMESTAMP WITH TIMEZONE data type |
no equivalent |
TO_TIMESTAMP_TZ |
Convert
to
Year-to-Month Interval data type |
no equivalent |
TO_YMINTERVAL |
Changes
character set |
no equivalent |
TRANSLATE_USING |
String to
UTF8 or UTF16 |
no equivalent |
UNISTR |
Convert
values to a date |
YMD |
TO_DATE |
|
Date-Time Functions
Date
addition |
DATEADD
& DAYS |
+ |
Date subtraction |
DATEDIFF
& DAYS |
- |
Add a
month |
no equivalent |
ADD_MONTHS |
First
non-null value |
COALESCE |
COALESCE |
Current date and time
(low precision) |
GETDATE |
CURRENT_DATE |
Current date and time
(high precision) |
no equivalent |
CURRENT_TIMESTAMP |
Current date and time
(low second) |
NOW
& TODAY |
SYSDATE |
Hours since
a starting date and time |
HOURS |
no equivalent |
Minutes since
a starting date and time |
MINUTES |
no equivalent |
Seconds since
a starting date and time |
SECONDS |
no equivalent |
Weeks since
a starting date and time |
WEEKS |
no equivalent |
Years since
a starting date and time |
YEARS |
no equivalent |
Current date and time
(high precision) |
no equivalent |
SYSTIMESTAMP |
Database's
time-zone |
no equivalent |
DBTIMEZONE |
Extract
part from date-time or interval |
no equivalent |
EXTRACT |
Alter
time zone information |
no equivalent |
FROM_TZ |
Largest
of a set of dates |
no equivalent |
GREATEST |
Last day of month |
no equivalent |
LAST_DAY |
Smallest
of a set of dates |
no equivalent |
LEAST |
Months
between dates |
MONTHS |
MONTHS_BETWEEN |
Time zone conversion |
no equivalent |
NEW_TIME |
First weekday after date |
no equivalent |
NEXT_DAY |
Quarter
of the calendar year |
QUARTER |
no equivalent |
Rounds
date to unit specified |
no equivalent |
ROUND |
Current
session's time zone |
no equivalent |
SESSIONTIMEZONE |
Coordinated
universal time |
GETUTCDATE |
SYS_EXTRACT_UTC |
Convert
date part to name |
DATENAME |
TO_CHAR |
Convert
date part to number |
DATEPART
& DOW |
TO_CHAR |
Name of
the day of the week |
DATENAME |
TO_CHAR |
Integer
representing the day of the week |
DAY |
TO_CHAR |
Integer
representing the hour |
HOUR |
TO_CHAR |
Integer
representing the minute |
MINUTE |
TO_CHAR |
Integer
representing the month of the year |
MONTH |
TO_CHAR |
Name of the month of the year |
MONTHNAME |
TO_CHAR |
Integer
representing the seconds |
SECOND |
TO_CHAR |
Integer
representing the year |
YEAR |
TO_CHAR |
Convert string to date |
CAST |
TO_DATE |
Determine
if a value/expression is a valid date |
ISDATE |
TO_DATE |
Date from
date-time |
no equivalent |
TRUNC |
Time-zone
offset |
no equivalent |
TZ_OFFSET |
|
Data Mining Functions
Cluster
ID of the cluster with highest probability |
no equivalent |
CLUSTER_ID |
Degree of
confidence of membership of a row |
no equivalent |
CLUSTER_PROBABILITY |
Varray of
objects of possible clusters |
no equivalent |
CLUSTER_SET |
Feature
ID with highest coefficient value |
no equivalent |
FEATURE_ID |
Varray of
objects of all possible features |
no equivalent |
FEATURE_SET |
Value of
a given feature |
no equivalent |
FEATURE_VALUE |
Best
prediction for the specified model |
no equivalent |
PREDICTION |
Cost
measure of a given prediction |
no equivalent |
PREDICTION_COST |
XML with
model specific scoring |
no equivalent |
PREDICTION_DETAILS |
Probability
for a given prediction |
no equivalent |
PREDICTION_PROBABILITY |
Varray of
objects with all possible classes |
no equivalent |
PREDICTION_SET |
|
Environment Functions
Database
/ Schema Identifier |
DB_ID |
SYS_CONTEXT |
Database
Name |
DB_NAME |
SYS_CONTEXT |
Host
Identifier |
HOST_ID |
SYS_CONTEXT |
Workstation
Name |
HOST_NAME |
SYS_CONTEXT |
Local
language identifier |
@@LANGID |
SYS_CONTEXT |
Name of
language in use |
@@LANGUAGE |
SYS_CONTEXT |
Value with the named context namespace |
no equivalent |
SYS_CONTEXT |
User
Session ID |
@@SPID |
UID |
User name |
CURRENT_USER |
USER |
Schema ID |
SCHEMA_ID |
SYS_CONTEXT |
Schema name |
SCHEMA_NAME |
SYS_CONTEXT |
Username
in the current context |
SESSION_USER |
SYS_CONTEXT |
Value with the named context namespace |
no equivalent |
USERENV |
|
Miscellaneous Functions
Null BLOB |
no equivalent |
EMPTY_BLOB |
Null CLOB |
no equivalent |
EMPTY_CLOB |
Length of
an NCHAR column |
no equivalent |
NLS_CHARSET_DECL_LEN |
ID of NLS
character set |
no equivalent |
NLS_CHARSET_ID |
Name of
NLS character set from ID |
no equivalent |
NLS_CHARSET_NAME |
Numeric
identifier of current exception code |
SQLCODE |
SQLCODE |
Error
message of current exception code |
no equivalent |
SQLERRM |
Hierarchical
path of column from root to node |
no equivalent |
SYS_CONNECT_BY_PATH |
16 byte
GUID |
no equivalent |
SYS_GUID |
Function
that builds descending index values |
no equivalent |
SYS_OP_DESCEND |
Index
leaf block ID scan |
no equivalent |
SYS_OP_LBID |
|
Model Functions
Use left
side value on right side calculation |
no equivalent |
CV |
Iterate
through data |
no equivalent |
ITERATE |
Iterate a
set number of times through data |
no equivalent |
ITERATE_UNTIL |
Current
iteration number |
no equivalent |
ITERATION_NUMBER |
Returns
expr1 prior to execution |
no equivalent |
PRESENTNNV |
Returns
expr1 prior to execution |
no equivalent |
PRESENTV |
Reference
prior model values |
no equivalent |
PREVIOUS |
|
Null Handling Functions
Evaluate
one or both operands may be NULL |
no equivalent |
LNNVL |
Returns
NULL is expr1 and expr2 both NULL |
NULLIF |
NULLIF |
Convert
to string if NULL |
ISNULL |
NVL |
Substitute
if NULL or if NOT NULL |
IFNULL |
NVL2 |
Map NULL
for joins |
(not
relevant) |
SYS_OP_MAP_NONNULL |
|
Numeric Handling Functions
Absolute value |
ABS |
ABS |
Arc cosine |
ACOS |
ACOS |
Arc sine |
ASIN |
ASIN |
Arc tangent of n |
ATAN |
ATAN |
Arc tangent1 divided by the arc tangent2 |
ATN2 |
ATAN2 |
Average |
AVG |
AVG |
Compute
AND operation on bits |
no equivalent |
BITAND |
Smallest integer >= value |
CEILING |
CEIL |
First
non-null value |
COALESCE |
COALESCE |
Coefficient
of correlation |
no equivalent |
CORR |
Pearson's
coefficient of correlation |
no equivalent |
CORR_K |
Spearman's
Rho correlation coefficient |
no equivalent |
CORR_S |
Cosine |
COS |
COS |
Hyperbolic cosine |
COT |
COSH |
Number of
values |
COUNT |
COUNT |
Number of
non-null values |
COUNT_BIG |
COUNT |
Population
covariance |
no equivalent |
COVAR_POP |
Sample
covariance |
no equivalent |
COVAR_SAMP |
Cumulative
Distribution |
no equivalent |
CUME_DIST |
Degrees |
DEGREES |
no equivalent |
Rank of
row in an ordered group |
no equivalent |
DENSE_RANK |
Exponential value |
EXP |
EXP |
Row
ranked first using DENSE_RANK |
no equivalent |
FIRST |
Round down to nearest integer |
FLOOR |
FLOOR |
Largest
of multiple values |
no equivalent |
GREATEST |
Tests if
numeric conversion will work |
ISNUMERIC |
TO_NUMBER |
Row
ranked last using DENSE_RANK |
no equivalent |
LAST |
Smallest
of multiple values |
no equivalent |
LEAST |
Natural logarithm |
LOG |
LN |
Logarithm, base 10 |
LOG10 |
LOG |
Maximum
returned value |
MAX |
MAX |
Middle
value of the set |
MEDIAN |
MEDIAN |
Minimum
returned value |
MIN |
MIN |
Remainder
from modulus using floor |
MOD |
MOD |
Returns
alternate number if value not a number |
no equivalent |
NANVL |
Percent
ranking |
no equivalent |
PERCENT_RANK |
Inverse
distribution continuous dist. model |
no equivalent |
PERCENTILE_CONT |
Inverse
distribution discrete distribution model |
no equivalent |
PERCENTILE_DISC |
Returns
the value of Pi |
PI |
no equivalent |
Raise
value to exponent power |
POWER |
POWER |
Radians
from a numeric expression |
RADIANS |
no equivalent |
Random
Number |
RAND |
dbms_cryto
package |
Rank in a
group |
no equivalent |
RANK |
Linear
regression - avg of the independent var. |
no equivalent |
REGR_AVGX |
Linear
regression - avg of the independent var. |
no equivalent |
REGR_AVGY |
Linear
regression - non-null number pairs |
no equivalent |
REGR_COUNT |
Linear
regression - y intercept |
no equivalent |
REGR_INTERCEPT |
Linear
regression - coefficient of determination |
no equivalent |
REGR_R2 |
Linear
regression - slope of the line |
no equivalent |
REGR_SLOPE |
Linear
regression - auxiliary function |
no equivalent |
REGR_SXX |
Linear
regression - auxiliary function |
no equivalent |
REGR_SXY |
Linear
regression - auxiliary function |
no equivalent |
REGR_SYY |
Remainder
from modulus using round |
REMAINDER |
REMAINDER |
Round to
integer place |
ROUND |
ROUND |
Sign of number |
SIGN |
SIGN |
Sine |
SIN |
SIN |
Hyperbolic
sine |
no equivalent |
SINH |
Square |
SQUARE |
no equivalent |
Square root |
SQRT |
SQRT |
Exact probability test
for dichotomous variables |
no equivalent |
STATS_BINOMIAL_TEST |
Crosstabulation analysis of nominal variables |
no equivalent |
STATS_CROSSTAB |
Whether
two values are significantly different |
no equivalent |
STATS_F_TEST |
Kolmogorov-Smirnov function |
no equivalent |
STATS_KS_TEST |
Value
with the greatest frequency |
no equivalent |
STATS_MODE |
Mann Whitney test |
no equivalent |
STATS_MW_TEST |
One-way analysis of variance function |
no equivalent |
STATS_ONE_WAY_ANOVA |
measures significance of a difference of means |
no equivalent |
STATS_T_TEST |
Wilcoxon Signed Ranks test of paired samples |
no equivalent |
STATS_WSR_TEST |
Standard deviation |
STDDEV |
STDDEV |
Square
root of the population variance |
STDDEV_POP |
STDDEV_POP |
Cumulative
sample standard deviation |
STDDEV_SAMP |
STDDEV_SAMP |
Summation |
SUM |
SUM |
Tangent |
TAN |
TAN |
Hyperbolic
tangent |
no equivalent |
TANH |
Truncates
to specified decimal places |
TRUNCATE
& TRUNCNUM |
TRUNC |
Population
variance of a set |
VAR_POP |
VAR_POP |
Sample
variance of a set |
VAR_SAMP |
VAR_SAMP |
Variance
of an expression |
VARIANCE |
VARIANCE |
Construct
equiwidth histograms |
WIDTH_BUCKET |
WIDTH_BUCKET |
|
Object Functions
Object
reference of an argument |
no equivalent |
DEREF |
Creates a
REF to an object row |
no equivalent |
MAKEREF |
Returns a
REF of an object instance |
no equivalent |
REF |
Typeid of the most specific type of the operand |
no equivalent |
SYS_TYPEID |
Returns
object instance from an object table |
no equivalent |
VALUE |
|
String Handling Functions
Get the
ASCII value of a character |
ASCII |
ASCII |
Convert ASCII to character |
CHAR |
CHR |
First
non-null value |
COALESCE |
COALESCE |
Concatenate
strings |
STRING |
CONCAT |
Converts From One Character Set To Another |
no equivalent |
CONVERT |
Capitalize first letter of each word in string |
no equivalent |
INITCAP |
Inserts a string into another string |
INSERTSTR |
no equivalent |
Starting point of pattern in
a string |
CHARINDEX,
LOCATE & PATINDEX |
INSTR |
Starting point
in bytes of pattern in a string |
no equivalent |
INSTRB |
Starting point
in Unicode of pattern in a string |
no equivalent |
INSTRC |
Starting point
in UCS2 of pattern in a string |
no equivalent |
INSTR2 |
Starting point
in UCS4 of pattern in a string |
no equivalent |
INSTR4 |
Length of character string
in characters |
BYTE_LENGTH,
CHAR_LENGTH &
LENGTH |
LENGTH |
Length of character string
in bytes |
no equivalent |
LENGTHB |
Convert characters to
lower case |
LCASE
& LOWER |
LOWER |
Pad left side of character string |
SPACE |
LPAD |
Left trim
a string |
LTRIM |
LTRIM |
NLS initial
letter upper case |
no equivalent |
NLS_INITCAP |
NLS lower
case |
no equivalent |
NLS_LOWER |
String of bytes used to sort a string |
no equivalent |
NLSSORT |
NLS upper
case |
no equivalent |
NLS_UPPER |
Define
quote delimiters |
no equivalent |
QUOTE_DELIMITERS |
Regular
expression instring |
no equivalent |
REGEXP_INSTR |
Regular
expression replace |
no equivalent |
REGEXP_REPLACE |
Regular
expression substring |
no equivalent |
REGEXP_SUBSTR |
Concatenates
a specified number of times |
REPEAT
& REPLICATE |
no equivalent |
Replace
part of a string with a string |
REPLACE,
STR_REPLACE |
REPLACE |
Reverses
a character expression |
REVERSE |
REVERSE |
Similarity
between two strings |
SIMILAR |
UTL_MATCH
built-in Package |
Pad right side of character string |
SPACE |
RPAD |
Right
trim a string |
RTRIM |
RTRIM |
Phonetic representation of character string |
SOUNDEX |
SOUNDEX |
String
replacing a number of characters |
STUFF |
no equivalent |
String
Matching |
DIFFERENCE |
UTL_MATCH
built-in Package |
Substring
in characters |
LEFT,
RIGHT & SUBSTRING |
SUBSTR |
Substring
in bytes |
no equivalent |
SUBSTRB |
Substring
in Unicode characters |
no equivalent |
SUBSTRC |
Substring
in UCS2 |
no equivalent |
SUBSTR2 |
Substring
in UCS4 |
no equivalent |
SUBSTR4 |
Translate character string |
no equivalent |
TRANSLATE |
Translate character string
using character set |
no equivalent |
TRANSLATE
USING |
Change declared
type of an expression |
no equivalent |
TREAT |
Left and
right trim a string |
TRIM |
TRIM |
Convert characters to
upper case |
UCASE
& UPPER |
UPPER |
|
XML Handling Functions
Append value
to target XML as a child node |
no equivalent |
APPENDCHILDXML |
Deletes node(s) matched by
XPath expression |
no equivalent |
DELETEXML |
Levels in the path specified by
UNDER_PATH |
no equivalent |
DEPTH |
Does
specified node exist |
no equivalent |
EXISTSNODE |
Returns XMLType instance containing fragment |
no equivalent |
EXTRACT |
Returns a scalar value of the resultant node |
no equivalent |
EXTRACTVALUE |
Inserts value
to target XML as a child node |
no equivalent |
INSERTCHILDXML |
Inserts value
to target XML before named node |
no equivalent |
INSERTXMLBEFORE |
Relative
path that leads to resource |
no equivalent |
PATH |
Generates a URL of datatype DBURIType |
no equivalent |
SYS_DBURIGEN |
Aggregates
XML documents or fragments |
no equivalent |
SYS_XMLAGG |
Returns XMLType containing an XML
doc. |
no equivalent |
SYS_XMLGEN |
Returns XMLType instance with
updated value |
no equivalent |
UPDATEXML |
Returns an aggregated XML document |
no equivalent |
XMLAGG |
Generates a CDATA section |
no equivalent |
XMLCDATA |
Creates XML fragment
& expands resulting XML |
no equivalent |
XMLCOLLATVAL |
Generates an XML comment |
no equivalent |
XMLCOMMENT |
Concatenates
XML elements |
no equivalent |
XMLCONCAT |
Returns concatenation
of XML fragments |
no equivalent |
XMLFOREST |
Parses and generates an XML instance |
no equivalent |
XMLPARSE |
Generates
an XML processing instruction |
no equivalent |
XMLPI |
Returns
query results as XML |
no equivalent |
XMLQUERY |
Create new XML value
from version & properties |
no equivalent |
XMLROOT |
Returns
Varray of top level nodes |
no equivalent |
XMLSEQUENCE |
Creates a string/LOB containing the contents |
no equivalent |
XMLSERIALIZE |
Returns
query of XML results as relational data |
no equivalent |
XMLTABLE |
Applies
XSLT to XML instance |
no equivalent |
XMLTRANSFORM |
|
|
Data Types |
String (Character)
Types |
Fixed length string |
CHAR
(8K) |
CHAR
(2K) |
Fixed length string |
NCHAR
(8K) |
NCHAR
(2K) |
Variable length string |
CHAR
(8K) |
CHARACTER
(2K) |
Variable length string |
VARCHAR
(8K) |
VARCHAR2
(32K) |
Variable length string |
NVARCHAR
(8K) |
NVARCHAR2
(32K) |
Variable length string |
VARCHAR
(8K) |
STRING
(32K) |
Variable length string |
no equivalent |
LONG
(2GB) |
Variable length string |
no equivalent |
CLOB
(128 TB) |
? |
TEXT
(deprecated) |
? |
? |
NTEXT
(deprecated) |
? |
? |
IMAGE
(deprecated) |
? |
|
Numeric Data Types |
Integer |
BIT |
NUMBER(1,0) |
Integer |
TINYINT
(1 byte) |
SMALLINT,
INT, INTEGER, BINARY INTEGER, and PLS_INTEGER
(all up to 38 digits) |
Integer |
SMALLINT
(2 bytes) |
Integer |
INT
(4 bytes) |
Integer |
BIGINT
(8 bytes) |
Number |
DECIMAL
(1 byte) |
DEC,
DECIMAL,
NUMERIC, NUMBER
(up to 38 digits) |
Number |
NUMERIC
(2 bytes) |
Floating point number |
REAL |
REAL
(63 binary digits) |
Floating point number |
FLOAT |
FLOAT
(126 binary digits) |
Floating point numbers |
no equivalent |
DOUBLE_PRECISION
(126 binary digits) |
Floating point numbers
using native machine arithmetic |
no equivalent |
BINARY_FLOAT
(32 bit) |
Floating point numbers
using native machine arithmetic |
no equivalent |
BINARY_DOUBLE
(64 bit) |
Non-negative integers |
no equivalent |
NATURAL |
Not nullable
non-negative integers |
no equivalent |
NATURALN |
Only positive integers |
no equivalent |
POSITIVE |
Not nullable
non-negative integers |
no equivalent |
POSITIVEN |
-1, 0 or +1 only |
no equivalent |
SIGNTYPE |
|
Monetary Data Types |
|
SMALLMONEY |
(user
definable) |
|
MONEY |
(user
definable) |
|
Date, Interval,
Time,and Timezone Data Types |
Date-Time |
DATETIME
& TIMSTAMP |
DATE |
Date-Time (to 1 nanosecond) |
no equivalent |
TIMESTAMP |
Date-Time with timezone |
no equivalent |
TIMESTAMP
WITH TIMEZONE |
Date-Time with local
timezone |
no equivalent |
TIMESTAMP
WITH LOCAL TIMEZONE |
Interval between dates
in year and month |
no equivalent |
INTERVAL
YEAR TO MONTH |
Interval between dates
in day and second |
no equivalent |
INTERVAL
DAY TO SECOND |
|
Boolean |
Boolean TRUE / FALSE |
no equivalent |
BOOLEAN |
|
Binary Data Types |
Fixed length binary |
BINARY |
RAW
or LONG RAW |
Variable length binary |
VARBINARY |
RAW
OR LONG RAW |
|
LONGBINARY |
LONG
RAW |
|
(not
relevant) |
MLSLABEL |
|
BLOB
& IMAGE |
BLOB |
|
Row Identifiers |
|
ROWID |
ROWID |
|
(not
relevant) |
UROWID |
|
Polymorphic Data
Types |
Any named SQL type or
transient type |
no equivalent |
ANYTYPE |
An instance of a given
type, with data, plus a description of the type |
no equivalent |
ANYDATA |
Values of the data
instances can be of SQL built-in types as well as user-defined
types |
no equivalent |
ANYDATASET |
|
CURSOR |
REFCURSOR |
|
URI Data Types |
Store DBURIRefs |
no equivalent |
DBURIType |
Store URLs to external
web pages or to files |
no equivalent |
HTTPURIType |
An object type for
storing XML |
no equivalent |
URIType |
Expose documents in the
XML heirarchy |
no equivalent |
XDBURIType |
|
Spatial Types |
? |
no equivalent |
SDO_GEOMETRY |
? |
no equivalent |
SDO_GEORASTER |
? |
no equivalent |
SDO_TOPO_GEOMETRY |
|
Media Types |
Supports the storage and
management of audio data |
no equivalent |
ORDAudio |
Supports storage and
management of any type of media data, including audio, image and
video data |
no equivalent |
ORDDoc |
Supports the storage and
management of image data |
no equivalent |
ORDImage |
Compact
representation of the color, texture, and shape information of
image data |
no equivalent |
ORDImageSignature |
Supports the storage and
management of video data |
no equivalent |
ORDVideo |
Represents
a feature that characterizes an image by its average color |
no equivalent |
SI_AverageColor |
Encapsulates color
values |
no equivalent |
SI_Color |
Characterizes
an image by the relative frequencies of the colors exhibited by
samples of the raw image |
no equivalent |
SI_ColorHistogram |
List containing up to
four of the image feature |
no equivalent |
SI_FeatureList |
Most significant colors
of a rectangle |
no equivalent |
SI_PositionalColor |
Inherent
image characteristics such as height, width, and format |
no equivalent |
SI_Stillimage |
Size of repeating items
coarseness, contrast, and predominant direction |
no equivalent |
SI_Texture |
|
|
Miscellaneous |
T-SQL |
PL/SQL |
Derived Table |
In-line View |
No equivalent technology |
Bulk Insert |
SQL Server has a totally
different internal structure than Oracle has. In SQL Server, a table is basically a big
linked-list and the data blocks are essentially the leaf-blocks of the cluster index.
Those blocks are then doubly-linked back and forth so you can traverse the table in a full
table scan or in an index range scan. In fact, an
index range scan of the whole table is essentially (physically) the same as a full table
scan. I'm not sure exactly why, but SQL Server has always had trouble with corruption of
these link-list pointers.
Run DBCC to check (and fix) problems with these pointers. |
Wildcards
comments |