Oracle vs. SQL Server

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 bring them to the attention of the PSOUG. Thank you.
SQL Server 2005	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

 

Related Topics

DB2

Informix

Sybase