Oracle Database - Microsoft SQL Server Comparison

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This page is to objectively map capabilities between two different products not to misrepresent either. If you are aware of errors or omissions please bring them to my attention. When you do so please provide working code or document links that can be used to verify the information provided. Thank you.

This page has not been updated extensively since Oracle 11gR2 and SQL Server 2008 except with respect to the changes introduced by Oracle's Container Database architecture
 
Basic Concepts and Verbiage
SQL Server Oracle Database
Shared Nothing Architecture Shared Everything Architecture
Instance Database & Instance
Database Container
No Equivalent Application Root Container
Schema Schema
User User
Master SYS and SYSTEM schemas in the SYSTEM and SYSAUX tablespace
Model PDB$SEED
Resource PDB$ROOT
tempdb Temporary Tablespace and Temp Tablespace Groups
msdb SYS and SYSTEM schemas, SYSTEM and SYSAUX tablespaces
Log File Log Files (only used for recovery ... never rollback)
Log File Undo (undo tablespace)
Truncate Transaction Logs No Equivalent, Would Break The Product
 
Processes
SQL Server Oracle Database
No Similar Concept ARCn (Archiver)
LazyWriter: Writes dirty blocks DBWn (DBWriter): Never writes dirty blocks
 
Storage Concepts
SQL Server Oracle Database
Logical File Name Tablespace
Row Row
Page (must be 8K) Block (configurable to 2K, 4K, 8K, 16K, 32K)
Extent (must be 64K) Extent (configurable to any multiple of the block size)
No Similar Concept Segment
Primary Data File Data File
Secondary Data File No Similar Concept (all data files are equal)
FileGroup (mapped to a single database) Tablespace (mapped a single container)
No Similar Concept Default Tablespace
No Similar Concept Tablespace Group

Note: The the way primary and secondary datafiles are mapped in SQL Server does not relate to how data files are mapped in Oracle. Also different is that in Oracle, storage at the logical (tablespace) level or physical (data file) level is not owned. Rather privileges, defined as a quotam can be granted to any user/schema defined within the container.

Note: SQL Server has a very different internal structure than Oracle. In SQL Server, a table is basically a 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 comparable issue exists within Oracle.
 
Features
SQL Server Oracle Database
Derived Table Inline-View
No Equivalent Edition Based Redefinition
Bulk Insert SQL*Loader
No Equivalent Bulk Insert
Cluster Server (missing many Data Guard features) Data Guard Physical Standby
Standby Server Logical Data Guard
No Equivalent Active Data Guard
No Equivalent Data Guard Snapshot Standby
No Equivalent Flashback Archive
No Equivalent Flashback Database
No Equivalent Flashback Drop
No Equivalent Flashback Query
No Equivalent Flashback Table
No Equivalent Flashback Transaction
No Equivalent Flashback Version
No Equivalent Transaction Backout
 
Options
SQL Server Oracle Database
No Equivalent Technology Real Application Clusters
 
Object Types
SQL Server Oracle Database
Clusters
No Equivalent Cluster By Hash
No Equivalent Cluster By Index
No Equivalent Cluster Range Partition
No Equivalent Sorted Hash Cluster
Constraints
Primary Key Primary Key
Unique Key Unique Constraint
Foreign Key Referential (Foreign Key) Constraint
Foreign Key On Delete Constraint Foreign Key On Delete Cascade
Foreign Key Set Null Foreign Key On Delete Set Null
Check Constraint Check Constraint
NULL / NOT NULL NULL / NOT NULL
No Equivalent Deferrable Constraints
No Equivalent RELY / NORELY
No Equivalent VALIDATE / NOVALIDATE
No Equivalent READ ONLY
No Equivalent REF (Nested Table) Constraint
Database Instance Links
Linked Server Database (DB) Link
DDL Event Triggers
ALL SERVER DATABASE
ALTER ALTER
No Equivalent ASSOCIATE STATISTICS
No Equivalent AUDIT
No Equivalent COMMENT
CREATE CREATE
DATABASE SCHEMA
DENY Not Relevant To The Security Model
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
Dimensions
No Equivalent Dimension
Functions: User Defined
Aggregate Function
Function Function
Table Function Pipelined Table Function
IN Parameter IN Parameter
OUT Parameter OUT Parameter
No Equivalent IN OUT Parameter
Parameter Defaults Parameter Defaults
No Equivalent Function Result Cache
With Encryption Wrap
Result Set Groupings
GROUP BY GROUP BY
No Equivalent GROUPING ID
GROUPING GROUPING
GROUPING SETS GROUPING SETS
ROLLUP ROLLUP
CUBE CUBE
Indexes
Index B*Tree Index
No Equivalent Bitmap Index
No Equivalent Bitmap Join Index
No Equivalent Cluster
Clustered Index Index Organized Table
No Equivalent Compressed Index
Descending Index Descending Index
Filtered Index Duplicates Oracle Normal B*Tree Functionality
Can be imitated with a computed column Function Based Index
Global Index Global Index
Local Index Local Index
No Equivalent No Segment (Virtual) Index
Non-Unique Index Non-Unique Index
No Equivalent Reverse Index
No Equivalent REF Index
Instead Of Triggers
Instead Of Trigger Instead Of Trigger
Libraries
Assembly Library
Materialized Views
Indexed View Multiple Varieties of Materialized View
No Equivalent Materialized View Log
Operators: User Defined
No Equivalent Operator
No Equivalent Overloading
Packages
No Equivalent Package Specification (Header)
No Equivalent Package Body
No Equivalent Initialization Section
No Equivalent Overloading
No Equivalent Pragma Serially Reusable
No Equivalent Wrap
Procedures: User Defined
Procedure Procedure
IN Parameter IN Parameter
OUT Parameter OUT Parameter
No Equivalent IN OUT Parameter
Parameter Default Parameter Default
No Equivalent NOCOPY Compiler Hint
No Equivalent AUTHID (Definer or Invoker Security Privileges)
No Equivalent JAVA Procedure
No Equivalent Javascript Procedure
With Encryption Wrap
Rules
No Equivalent Evaluation Contexts
No Equivalent Rules
No Equivalent Rule Sets
Surrogate Key Generation
Identity Identity
No Equivalent Sequence
Synonyms
Private Synonym Synonym
No Equivalent Public Synonym
Creatable For Limited Object Types Creatable For All Object Types
System Event Triggers
No Equivalent Database
No Equivalent Schema
No Equivalent After Clone
No Equivalent After DB Role Change
After Logon After Logon
No Equivalent After Server Error
No Equivalent After Set Container
No Equivalent After Startup
No Equivalent After Suspend
No Equivalent Before Logoff
No Equivalent Before Set Container
No Equivalent Before Shutdown
No Equivalent Before Unplug
Tables
Table Heap Table
Temporary Table No Equivalent Functionality
Cluster Index Index Organized Table
Column Default Column Default
Ledger Table Equivalent Functionality with Unified Auditing
No Equivalent Block Chain Table
No Equivalent Compressed Table
No Equivalent External Table
No Equivalent Global Temporary Table (On Commit Delete Row)
No Equivalent Global Temporary Table (On Commit Preserve Row)
No Equivalent Immutable Table
No Equivalent Nested Table
No Equivalent Hash Partitioned Table
No Equivalent Interval Partitioned Table
No Equivalent JSON Table
No Equivalent List Partitioned Table
No Equivalent Object Table
No Equivalent Private Temporary Table
with Create Partition Function and Schema Range Partitioned Table
No Equivalent Reference Partitioned Table
No Equivalent Subpartitioned (Composite) Table
No Equivalent System Partitioned Table
No Equivalent Read Only Table
No Equivalent XML Table
No Equivalent XML Binary Table
No Equivalent PCTFREE
No Equivalent PCTUSED
No Equivalent INITRANS
No Equivalent MAXTRANS
Computed Column Virtual Column
No Equivalent Virtual Column Partitioned Table
Table Triggers
No Equivalent Before Delete Statement Level
No Equivalent Before Insert Statement Level
No Equivalent Before Update Statement Level
No Equivalent Before Delete Row Level
No Equivalent Before Insert Row Level
No Equivalent Before Update Row Level
After Delete After Delete Statement Level
After Insert After Insert Statement Level
After Update After Update Statement Level
No Equivalent After Delete Row Level
No Equivalent After Insert Row Level
No Equivalent After Update Row Level
No Equivalent FOLLOWS Clause
No Equivalent OF Clause
No Equivalent PRECEDES Clause
No Equivalent REFERENCING Clause
No Equivalent WHEN Clause
No Equivalent Compound Trigger
With Encryption Wrap
Types (User Defined)
No Equivalent Type Specification
No Equivalent Type Body
No Equivalent Methods
No Equivalent Objects
No Equivalent VARRAYs
No Equivalent Inheritance
No Equivalent Polymorphism
Views
View View (Relational)
Check Option Check Option
ORDER BY (only with TOP clause) ORDER BY
SELECT DISTINCT not allowed SELECT DISTINCT allowed
No Equivalent Object Views
 
Data Types
Description SQL Server Oracle
String (Character)
Fixed length string CHAR (8K) CHAR(2K)
Fixed length string NCHAR (8K) NCHAR(2K) or CHAR(n*2)
Fixed length string CHAR (8K) CHARACTER (2K)
Variable length string VARCHAR (8K) VARCHAR2 (32K)
Variable length string NVARCHAR (8K) NVARCHAR2(32K) or VARCHAR2(n*2)
Variable length string VARCHAR (8K) STRING (32K)
Variable length string No Equivalent LONG (2GB)
Variable length string NVARCHAR[MAX] NCLOB (128TB)
Variable length string TEXT CLOB (128TB)
Variable length string VARCHAR(MAX) CLOB (128TB)
Numeric
Integer BIGINT (8 bytes) NUMBER(19,0) and PLS_INTEGER
Integer BIT NUMBER(1,0) and PLS_INTEGER
Integer INT (4 bytes) NUMBER(10,0) and PLS_INTEGER
Integer INTEGER (4 bytes) NUMBER(10,0) and PLS_INTEGER
Integer SMALLINT (2 bytes) NUMBER(6,0) and PLS_INTEGER
Integer TINYINT (1 byte) NUMBER(3,0) and PLS_INTEGER
Number DECIMAL (1 byte) DEC, DECIMAL, NUMERIC, NUMBER
(up to 38 digits)
Number NUMERIC (2 bytes) DEC, DECIMAL, NUMERIC, NUMBER
(up to 38 digits)
Floating Point Numbers FLOAT FLOAT
Floating Point Numbers REAL FLOAT or 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
Money
Windows-centric formatted display type SMALLMONEY NUMBER(10,4)
Windows-centric formatted display type MONEY NUMBER(19,4)
Date, Interval, Time, Timestamp, Timezone
Date-Time (low precision) SMALLDATETIME & DATE DATE (to 1 sec) or TIMESTAMP(3)
Date-Time (high precision) DATETIME & DATETIME2 TIMESTAMP (to 1 nanosecond)
Date-Time with Timezone (hh:mm:ss.nnnnnnn) DATETIMEOFFSET TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE
Date-Time with Local Timezone (hh:mm:ss.nnnnnnn) No Equivalent TIMESTAMP WITH LOCAL TIMEZONE
Date-Time Interval (precision mo) No Equivalent INTERVAL YEAR TO MONTH
Date-Time Interval precision ss) No Equivalent INTERVAL DAY TO SECOND
Time (hh:mm:ss.nnnnnnn) TIME EXTRACT(TIMESTAMP)
Boolean
Boolean TRUE / FALSE No Equivalent BOOLEAN
Binary
Fixed length binary BINARY BLOB or RAW
Variable length binary IMAGE BLOB or RAW (8GB)
Variable length binary VARBINARY (8K) RAW (2K) or LONG RAW (8GB)
Up to 128TB VARBINARY[MAX] BLOB (128TB)
16 byte binary value UNIQUEIDENTIFIER RAW(16)
Available in Trusted Oracle only No Equivalent MLSLABEL
Identifier
Base 64 string IOT row identifier Not Relevant UROWID
Base 64 string heap table row identifier Not Relevant ROWID
Object Identifier (OID) No Equivalent REF
MultiMedia
DICOM Medical Imaging No Equivalent DICOM
Audio Data No Equivalent ORDAudio
Any type of media data No Equivalent ORDDoc
Image data No Equivalent ORDImage
Image color, texture, & shape No Equivalent ORDImageSignature
Video data No Equivalent ORDVideo
Characterizes an image by its average color No Equivalent SI_AverageColor
Encapsulates color values No Equivalent SI_Color
Sampled raw image relative color frequencies 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 (height, width, format) No Equivalent SI_Stillimage
Repeating item's coarseness, contrast, predominant direction and size No Equivalent SI_Texture
Polymorphic
Result Set Pointer (handle) CURSOR REFCURSOR
Instance of a Type, with data and type description SQL_VARIANT ANYDATA
Any named SQL Type or transient Type No Equivalent ANYTYPE
Values can be SQL or user-defined Types No Equivalent ANYDATASET
Spatial
Geometry No Equivalent SDO_GEOMETRY
Georaster No Equivalent SDO_GEORASTER
Topography No Equivalent SDO_TOPO_GEOMETRY
Uri
Object type for storing XML XML XMLType
Access Data Stored in the Database No Equivalent DBUriRefs
Uri Type Referencing Database Objects No Equivalent DBUriType
HTTP Implementation of Uri Type No Equivalent HTTPUriType
Uri Type Referencing Oracle XML DB Objects No Equivalent XDBUriType
 
Built-In Operators
Description SQL Server Oracle
Arithmetic
Add + +
Subtract - -
Multiply * *
Divide / /
Modulus % (modulo) mod
Power power function ** (power)
Assignment
Value Assignment = :=
Bitwise
bitwise AND & & and utl_raw.bit_and
bitwise OR | | and utl_raw.bit_or
bitwise exclusive OR ^ ^ and utl_raw.bit_xor
bitwise NOT ~ ~
Left Shift No Equivalent <<
Right Shift No Equivalent >>
Zero Fill Right Shift No Equivalent >>>
bitwise Complement No Equivalent utl_raw.bit_complement
Comparison
equal to = =
greater than > >
less than < <
greater than or equal to >= >=
not less than !< No Equivalent
less than or equal to <= <=
not greater than !> No Equivalent
not equal to <> or != <> or != or =^
Conditions
Match all ALL ALL
Matches both AND AND
Match one or more ANY or SOME ANY or SOME
Within a range BETWEEN BETWEEN
Nested levels specified No Equivalent DEPTH
- No Equivalent EMPTY
XML resource found in the specified path No Equivalent EQUALS PATH
Defined in the list of one or more values EXISTS EXISTS
Defined in the list of one or more values IN IN
Floating Point No Equivalent INFINITE
Determines whether the nested table is a set No Equivalent IS A SET
Model Clause No Equivalent IS ANY
- No Equivalent IS NULL
- No Equivalent IS OF ONLY
- No Equivalent IS OF TYPE
Model Clause No Equivalent IS PRESENT
Wildcard Match LIKE LIKE
Member of a Nested Table No Equivalent MEMBER OF
Floating Point No Equivalent NAN
Dissimilar NOT NOT
One or the other OR OR
Relative Path in the specified condition No Equivalent PATH
A specified nested table of another nested table No Equivalent SUBMULTISET OF
Number of nested levels to search No Equivalent UNDER_PATH
Date
Add No Equivalent +
Subtract No Equivalent -
Hierarchical
- Limited: Look Up HEIRARCHYID Data Type CONNECT
Recursive Match No Equivalent CONNECT BY
Following level match ISDESCENDANT OF CONNECT BY ROOT
Prior level match GETANCESTOR CONECT BY PRIOR
Heirarchy level GETLEVEL LEVEL
Recursive Common Table Expression Recursive Common Table Expression Recursive Common Table Expression
Multiset
- No Equivalent MULTISET
- No Equivalent MULTISET EXCEPT
- No Equivalent MULTISET INTERSECT
- No Equivalent MULTISET UNION
Set
Non-Distinct Rows INTERSECT INTERSECT
Non-Distinct Rows Including Duplicates No Equivalent INTERSECT ALL
Distinct Rows EXCEPT MINUS
Distinct Rows Including Duplicates No Equivalent MINUS ALL
All Unique Rows UNION UNION
All Rows Including Duplicates UNION ALL UNION ALL
String
Concatenation + ||
Unary
Negative - -
Positive + +
 
Built-In Functions
Description SQL Server Oracle
Analytic
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
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 STDVP 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
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
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
Convert 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
RAW to CHAR No Equivalent RAW_TO_CHAR
Raw to hexadecimal containing string No Equivalent RAWTOHEX
RAW to NCHAR No Equivalent RAW_TO_NCHAR
Raw into a hexadecimal containing 'N' string No Equivalent RAWTONHEX
RAW to variable length string No Equivalent RAW_TO_VARCHAR2
Raw to 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
RAW to NUMBER No Equivalent SYS_OP_RAWTONUM
Cast array to table No Equivalent TABLE
An SCN to its timestamp equivalent Not Relevant TIMESTAMPTOSCN
Cast to BINARY_DOUBLE CONVERT TO_BINARYDOUBLE
Cast to BINARY_FLOAT CONVERT TO_BINARYFLOAT
Cast to String STR TO_CHAR
Cast to CLOB data type No Equivalent TO_CLOB
Cast to DATE No Equivalent TO_DATE
Cast to Day-to-Second Interval No Equivalent TO_DSINTERVAL
LONG or LONG RAW to LOB No Equivalent TO_LOB
Single byte to corresponding multi-byte No Equivalent TO_MULTI_BYTE
Cast to NCHAR No Equivalent TO_NCHAR
Cast to NCLOB No Equivalent TO_NCLOB
Cast to NUMBER 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
Data Mining
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
Date-Time
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
Cast date part to name DATENAME TO_CHAR
Cast 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
Cast 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
Environment
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 USERENV
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
Null Binary Large Object No Equivalent EMPTY_BLOB
Null Character String Large Object 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
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
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 No Equivalent SYS_OP_MAP_NONNULL
Numeric
Absolute Value ABS ABS
Arc Cosine ACOS ACOS
Arc Sine ASIN ASIN
Arc Tangent of n ATAN ATAN
Arc Tangent1 divided by 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 COSH COSH
Cotangent COT No Equivalent
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 a set No Equivalent MEDIAN
Minimum returned value MIN MIN
Remainder from modulus using floor 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 No Equivalent POWER
Radians from a numeric expression RADIANS No Equivalent
Random Number RAND dbms_cryto  & dbms_random packages
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 No Equivalent VAR_POP
Sample Variance No Equivalent VAR_SAMP
Variance of an expression VAR VARIANCE
Construct equi-width histograms No Equivalent WIDTH_BUCKET
Object
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
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 or ||
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 Equivalent8 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 Equivalent4 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 Equivalent4 TRIM
Convert characters to upper case UPPER UPPER
XML
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 QUERY (FLWOR) 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
 
Miscellaneous
SQL Server Oracle Database
Row level locks are a limiting resource Unlimited row level locks
Lock escalation Lock escalation is impossible
Default: Reads block writes, writes block read Reads never block writes, writes never block reads. Reads cannot be blocked.
No Equivalent (must drop and recreate) Create Or Replace for functions, packages, procedures, synonyms, and views
Windows and Linux Windows, Linux, zLinux, Solaris, AIX, HP/UX
Triggers commit independent of triggering event Triggers can not commit or rollback independently except as autonomous transactions
No Equivalent Compiler Pragmas
No Equivalent Block/Page Recovery from Backup

Related Topics
Database Security
Built-in Functions
Built-in Packages
Database Security
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx