PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle vs. SQL Server
Version 11.1 vs. 2008
 
Note: The purpose of this page is to try to objectively map capabilities between two different products not to misrepresent either. If you are aware of errors or omissions please contact us and let us know. Thank you.
 
SQL Server 2008 Oracle 10gR2
Verbiage
Instance Database
Instance
Database Schema
User User
System & User Databases
master
model
tempdb
msdb
Schemas
SYS and SYSTEM schemas
SYS and SYSTEM schemas
temporary tablespace
stored in SYS schema
Storage Concepts
Row
Page
Extent (always 64K)
-
Primary Datafile
-
FileGroup
-
Storage Concepts
Row
Block
Extent (user defined sizing)
Segment
Data File
Tempfile
Tablespace (usable by multiple schemas)
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.
- Real Application Cluster
Cluster Server Data Guard
Standby Server Standby Server
Bulk Insert SQL*Loader
Primary Data File System Data File
Secondary Data File Combination of Data Files
Log File Log Files Not Used For Rollback

Note: The log file concept and architecture are completely different
Truncate Transaction Logs No Equivalent: Not An Issue
Logical File Name Tablespace
 
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)
Foreign Key On Delete Cascade On Delete Cascade
Foreign Key Set Null On Delete Set Null
no equivalent Deferrable
no equivalent RELY / NORELY
no equivalent VALIDATE / NOVALIDATE
Check Check
NULL / NOT NULL NULL / NOT NULL
no equivalent Read Only
no equivalent REF (Nested Table Constraint)
 

Database Linkages

Linked Server Database (DB) Link
 

Dimension

no equivalent Dimension
 

Functions (user defined)

Aggregate Function
Function Function
Table Function 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
no equivalent Bitmap Join
no equivalent Cluster
Clustered Index Index Organized Table
no equivalent Compressed
Descending Descending
Filtered Duplicates Oracle Normal B*Tree Functionality
can be imitated with a computed column 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

Indexed View Materialized Views (multiple types)
 

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
 

Rules

deprecated CHECK Constraint
 

Schemas

Schema Schema
 

Surrogate Key Generator

no equivalent Sequence
Identity no equivalent
 

Synonyms

Synonym Private Synonym
no equivalent Public Synonym
Creatable for limited object types Creatable for all object types
 

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
no equivalent Hash Partitioned Table
no equivalent List Partitioned Table
with Create Partition Function
and Create Partition Schema		 Range Partitioned Table
no equivalent Subpartitioned Table
Temporary Table not required due to MVCC
no equivalent XML Table
no equivalent PCTUSED
FILLFACTOR PCTFREE
no equivalent INITRANS
no equivalent MAXTRANS
 

Types

Type Type
User Defined Table Type (limited equiv) Type Header
no equivalent Type Body with Methods
no equivalent Object
no equivalent VArray
 

Views

View View
Check Option Check Option
ORDER BY (only with TOP clause) 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

Limited: Look up HEIRARCHYID Data Type CONNECT
no equivalent CONNECT BY
ISDESCENDANT OF CONNECT BY PRIOR
GETANCESTOR CONNECT BY ROOT
GETLEVEL LEVEL

Conditions

ALL ALL
AND AND
ANY ANY
BETWEEN BETWEEN
no equivalent DEPTH
no equivalent EMPTY
no equivalent EQUALS_PATH
EXISTS EXISTS
IN INFINITE
no equivalent IS A SET
no equivalent IS ANY
no equivalent 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
SOME 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
EXCEPT MINUS
UNION UNION
UNION ALL UNION ALL

String Operators

+ (concatenation) || (concatenation)

Unary Operators

+ (positive) + (positive)
- (negative) - (negative)
 
Triggers

DDL Triggers

ALL SERVER DATABASE
ALTER ALTER
no equivalent ASSOCIATE STATISTICS
no equivalent AUDIT
no equivalent COMMENT
CREATE CREATE
DATABASE SCHEMA
DENY (not irrelevant)
no equivalent DDL
no equivalent DISASSOCIATE STATISTICS
DROP DROP
GRANT GRANT
no equivalent NOAUDIT
no equivalent RENAME
REVOKE REVOKE
no equivalent SUSPEND
no equivalent TRUNCATE
UPDATE STATISTICS ANALYZE

Instead-Of Triggers

INSTEAD-OF TRIGGER INSTEAD-OF TRIGGER

System Triggers

no equivalent DATABASE
no equivalent SCHEMA
AFTER LOGON 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
with encryption native compilation and wrap
 
Functions

Analytic Functions

Running average AVG AVG
Coefficient of correlation no equivalent CORR
Running count by partition COUNT & COUNT_BIG 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 MAX MAX
Minimum value by partition MIN MIN
Divides an ordered dataset into buckets NTILE NTILE
Rowset partitioning OVER OVER
Calculates the value of r-1/rows-1 no equivalent PERCENT_RANK
An inverse distribution function no equivalent PERCENTILE_CONT
An inverse distribution function no equivalent 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 ROW_NUMBER ROW_NUMBER
Sample standard deviation STDEV STDDEV
Square root of the population variance STDEVP STDDEV_POP
Cumulative sample standard deviation no equivalent STDDEV_SAMP
Cumulative running total SUM SUM
Population variance of a set VARP VAR_POP
Sample variance of a set no equivalent VAR_SAMP
Variance of an expression VAR 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  FILESTREAM BFILENAME
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 NCHAR COMPOSE
One character set to another no equivalent CONVERT
Unicode string to a string no equivalent DECOMPOSE
Char containing hexadecimal digits to raw no equivalent HEXTORAW
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
Convert RAW to CHAR no equivalent RAW_TO_CHAR
Raw into a hexadecimal containing string no equivalent RAWTOHEX
Convert RAW to NCHAR no equivalent RAW_TO_NCHAR
Raw into a hexadecimal containing 'N' string no equivalent RAWTONHEX
Converts RAW to variable length string no equivalent RAW_TO_VARCHAR2
Raw into a hexadecimal 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
Casts an array as a table no equivalent TABLE
An SCN to its timestamp equivalent not relevant TIMESTAMPTOSCN
Convert to BINARY_DOUBLE data type CONVERT TO_BINARYDOUBLE
Convert to BINARY_FLOAT data type CONVERT TO_BINARYFLOAT
Convert to CHAR or VARCHAR2 data type STR TO_CHAR
Convert to CLOB data type no equivalent TO_CLOB
Convert to DATE data type no equivalent 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 no equivalent TO_NUMBER
Multi-byte to corresponding single byte no equivalent TO_SINGLE_BYTE
Convert to TIMESTAMP data type no equivalent 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
Integer value based on Unicode standard UNICODE no equivalent
String to UTF8 or UTF16 no equivalent UNISTR

Date-Time Functions

Date addition DATEADD +
Date subtraction DATEDIFF -
Add a month no equivalent ADD_MONTHS
First non-null value COALESCE COALESCE
Current date and time (low precision) GETDATE
CURRENT_DATE		 CURRENT_DATE
Current date and time (high precision) no equivalent CURRENT_TIMESTAMP
Current date and time (low second) GETDATE SYSDATE
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 no equivalent MONTHS_BETWEEN
Time zone conversion no equivalent NEW_TIME
First weekday after date no equivalent NEXT_DAY
Rounds date to unit specified  no equivalent ROUND
Current session's time zone no equivalent SESSIONTIMEZONE
Coordinated universal time GET_UTC_DATE SYS_EXTRACT_UTC
Convert date part to name DATENAME TO_CHAR
Convert date part to number DATEPART TO_CHAR
Integer representing the day of the week DAY TO_CHAR
Integer representing the month of the year MONTH 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 @@ERROR & ERROR_NUMBER 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 NEWID 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 no equivalent 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_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
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 no equivalent MEDIAN
Minimum returned value MIN MIN
Remainder from modulus using floor use MODULO (%) operator 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
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 no equivalent 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 STDEV STDDEV
Square root of the population variance STDEVP STDDEV_POP
Cumulative sample standard deviation no equivalent STDDEV_SAMP
Summation SUM SUM
Tangent TAN TAN
Hyperbolic tangent no equivalent TANH
Truncates to specified decimal places no equivalent TRUNC
Population variance of a set no equivalent VAR_POP
Sample variance of a set no equivalent VAR_SAMP
Variance of an expression VAR VARIANCE
Construct equiwidth histograms no equivalent 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 (expression + expression) CONCAT
Converts From One Character Set To Another no equivalent CONVERT
Capitalize first letter of each word in string no equivalent INITCAP
Starting point of pattern in a string CHARINDEX & 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 DATALENGTH or LEN LENGTH
Length of character string in bytes no equivalent LENGTHB
Convert characters to lower case 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
Replace part of a string with a string STUFF REPLACE
Reverses a character expression REVERSE REVERSE
Pad right side of character string SPACE RPAD
Right trim a string RTRIM RTRIM
Phonetic representation of character string SOUNDEX SOUNDEX
Difference between the SOUNDEX values 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
Character data converted from numeric data STR TO_CHAR
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 no equivalent TRIM
Convert characters to upper case 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)
Variable length string TEXT (deprecated) CLOB
Variable length string NTEXT (deprecated) CLOB
 
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 numbers FLOAT FLOAT
Floating point numbers REAL REAL 
(63 binary digits)
Floating point numbers no equivalent DOUBLE_PRECISION 
(126 binary digits)
Floating point numbers no equivalent FLOAT
(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 (low precision) SMALLDATETIME & DATE DATE (to 1 sec)
Date-Time (high precision) DATETIME & DATETIME2 TIMESTAMP (to 1 nanosecond)
DATETIMEOFFSET TIMESTAMP WITH TIMEZONE
no equivalent TIMESTAMP WITH LOCAL TIMEZONE
no equivalent INTERVAL YEAR TO MONTH
no equivalent INTERVAL DAY TO SECOND
hh:mm:ss.nnnnnnn TIME EXTRACT(TIMESTAMP)
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
  LONG RAW
(not relevant) MLSLABEL
IMAGE BLOB
Row Identifiers
UNIQUEIDENTIFIER no equivalent
(not relevant) 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 SQL_VARIANT 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 XML URIType
Expose documents in the XML hierarchy 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.
No equivalent issue

Wildcards

Wildcards
 
SQL Server Oracle
Locks are a limiting resource Unlimited locks
Lock escalation No lock escalation
Default: 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
NOTE: There is an exception to this- if you use PRAGMA AUTONOMOUS_TRANSACTION a trigger can commit and rollback. Even if PRAGMA AUTONOMOUS_TRANSACTION doesn't work to commit or rollback the statement that fired the trigger, it can commit other DML written inside it.
 
Related Topics
DB2
Informix
Sybase
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----