CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle vs. SQL Sybase

Version 10.2 vs. 15.0.1
 
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

Dimension 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

Assembly Library
 

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

Schema Schema
 

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
 
Sybase Oracle
Locks are a limiting resource Unlimited locks
Lock escallation No lock escalation
Reads block writes, writes block reads Reads do not block writes, writes do not block reads
Autonumering identity column Sequence Objects
All table triggers are AFTER BEFORE and AFTER triggers
All table triggers are STATEMENT TRIGGERS Triggers can be statement or row level
All pages are 8K Blocks can be 2K, 4K, 8K, 16K, or 32K
All extents 64K Extents in any multiple of the block size
No equivalent Create or Replace Syntax
Single Operating System: Windows Windows, Linux, UNIX, OS/390 Sun Sequent  VAX-VMX DEC MVS
Triggers commit independent of triggering event Triggers can not commit or rollback independently
 
Related Topics
DB2
Informix
SQL Server
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [81 users online]    © 2010 psoug.org