Comparison between Oracle and SQL Server SQL

Data Type comparison

 

Type name	Oracle	Sqlserver	Comparison
Character Data Type	Char	Char	All are fixed-length characters,OracleThe maximum size is 2 kb.Maximum length8 KB
Variable-length character data type	Varchar2	Varchar	OracleInsideMaximum lengthIt is 4 kb, which is in sqlserverMaximum length8 KB
Character Set-based fixed-length string	Nchar	Nchar	FormerMaximum length2kb laterMaximum length4 kb
Variable-length String Based on Character Set	Nvarchar2	Nvarchar	BothMaximum lengthAll are 4 kb
Date and Time Data Types	Date	There are two types: datetime and smalldatetime.	InOracleThe format is DMY and can be adjusted in sqlserser. The default format is mdy.
Numeric type	Number (P, S)	Numeric [P (, S)]	OracleP indicates the number of digits to the left of the decimal point, and s indicates the number of digits to the right of the decimal point. In sqlserver, p represents the sum of the digits on both sides of the decimal point, and s represents the digits on the right of the decimal point.
Numeric type	Decimal (P, S)	Decimal [P (, S)]	OracleP indicates the number of digits to the left of the decimal point, and s indicates the number of digits to the right of the decimal point. In sqlserver, p represents the sum of the digits on both sides of the decimal point, and s represents the digits on the right of the decimal point.
Integer type	Integer	Int	The storage size is 4 bytes in the same Integer type.
Floating Point Type	Float	Float
Real Number Type	Real	Real

SQLStatement comparison

Oracle	SQLServer
The Select statements are basically consistent. But there are the following differences: SQLServer not supportedOracleStart... Connect by statement. You can replace it with a stored procedure of sqlserver to do the same job. OracleIntersect and minus inSQLServer is not supported, but you can use SQL Server's exists and not exists statement to do the same job. OracleThe cost-based optimizer hints with special terms for performance optimization are notSQLServer support. We recommend that you delete it. InSQLPlease useSQLServer cost-based optimization. The Select syntax is as follows:
Subquery [for_update_clause]; Subquery: = select [hint] [All | distinct | unique] {* | {Expr [[as] c_alias] | schema. {table | View | snapshot }.*}[,... N]} * From {<query_table_expression_clause> [,... N]} [where_clause] [[group_by_clause | hierarchical_query] [,… N] [Where_clause] [[group_by_clause | hierarchical_query] [… N] {Union [all] | intersect | minus} (subquery)] [Order_by_clause] Query_table_expression_clause: = {[Schema.] {table {[partition (partition) | subpartition (subpartition)] [sample_clause]} | [@ dblink]} | {View | snapshot} [@ dblink] }}| (subquery [with_clause]) | table_collection_expression} [t_alias] Sample_clause: = Sample [block] {(sample_percent )} With_clause: = With {read only | check option [constraint]} Table_collection_expression: = Table {(collection_expression) [(*)]} Where_clause: = Where {condition | outer_join} Outer_join: = {Table1. column {= table2. column (+) | (+) = table2. column }} Hierarchical_query_clause: = [Start with condition] {connect by condition} Group_by_clause: = Group by {expr [,... N]} | [expr] [,… N] {cube | rollup} (expr [,... N])} [having condition] Order_by_clause: = Order by {expr | position | c_alias} [ASC | DESC] [nulls first | nulls last]} [,… N] For_update_clause: = For update [of {[schema.] {table | view}. column }[,... N] [Nowait]	Select select_list [into new_table] From table_source [where search_condition] [group by group_by_expression] [having search_condition] [order by order_expression [ASC | DESC] syntax Select statement ::=< query_expression> [order by {order_by_expression | column_position [ASC | DESC]} [,... n] [compute {AVG | count | max | min | sum} (expression )}[,... n] [by expression [,... n] [for {browse | XML {raw | auto | explicit} [, xmldata] [, elements] [, binary base64]}] [Option (<query_hint> [,... n])] <query expression >::={ <query specification >|( <query expression>)} [Union [all] <query Specification> | (<query expression>) [... n] <Query Specification> :: = select [All | distinct] [{top integer | top integer percent} [with ties] <select_list> [into new_table] [from {<table_source> }[,... n] [where <search_condition>] [group by [all] group_by_expression [,... n] [With {cube | rollup}] [having ]
Insert inOracle And SQL Server is basically the same, with the following differences: SQLServer's transactSQLLanguage supports inserts into tables and views, but does not support insert operations into select statements. IfOracleContains inserts into select statements and needs to be changed. SQLServer's transactSQLProvided by values_list ParameterSQL-92 standard keyword default inOracleIs not supported. SQLA very useful transact in serverSQLOption (execute procedure_name) is used to execute a procedure and import the output results to a target table or view.OracleIs not supported. The insert syntax is as follows:
Insert [hint] into table_expression_clause [( [,... N])] {values_clause | subquery }[,... N]; dml_table_expression_clause ::=< BR >{{ [schema.] {table {[partition (partition) | subpartition (subpartition)] | @ dblink }}| {View | snapshot} [@ dblink]} | (subquery [with_clause]) | table_collection_expression} [t_alias] subquery: see subquery with heavy select syntax. with_clause ::=< br> with {read only | check option [constraint]} table_collection_expression: = table (collection_expression) [(+)] values_clause ::=< br> values ({expr | subquery}) [returning_clause] returning_clause ::=< br> returning {expr }[,... N] into {data_item }[,... N]	Insert [into] {table_name with (<table_hint_limited> [... n]) | view_name | rowset_function_limited} {[(column_list)] {values ({default | null | expression }[,... n]) | derived_table | execute_statement }}| default values <Table_hint_limited >::={ fastfirstrow | holdlock | paglock | readcommitted | repeatableread | rowlock | serializable | tablock | tablockx | updlock}
Delete and updateOracleAndSQLServer is basically consistent
Delete Syntax: Delete [hint] [from] table_expression_clause [where_clause] [returning_clause]; dml_table_expression_clause ::=< BR >{{ [schema.] {table {[partition (partition) | subpartition (subpartition)] | @ dblink }}| {View | snapshot} [@ dblink]} | (subquery [with_clause]) | table_co Llection_expression} [t_alias] subquery: see subquery with heavy select syntax. with_clause :: = with {read only | check option [constraint]} table_collection_expression :: = TABLE (collection_expression) [(*)] where_clause :: = where condition returning_clause ::=< br> returning {expr }[,... N] into {data_item }[,... N]	Delete [from] {table_name with (<table_hint_limited> [... n]) | view_na | rowset_function_limited} [from {<table_source>} [,... n] [Where {<search_condition> | {[current of {[Global] cursor_name} | cursor_variable_name}] [Option (<query_hint> [,... n])] <Table_source >::= table_name [[as] table_alias] [With (<table_hint> [,... n])] | view_name [[as] table_alias] | rowset_function [[as] table_alias] | derived_table [as] table_alias [(column_alias [,... n])] | <joined_table> <Joined_table >::=< table_source> <join_type> <table_source> On <search_condition> | <table_source> cross join <table_source> | <joined_table> <Join_type >::= [inner | {left | right | full} [outer]}] [<join_hint>] Join <Table_hint_limited >::={ fastfirstrow | holdlock | paglock | readcommitted | repeatableread | rowlock | serializable | tablock | tablockx | updlock} <Table_hint >::={ index (index_val [,... n]) | fastfirstrow | holdlock | nolock | paglock | readcommitted | readpast | readuncommitted | repeatableread | rowlock | serializable | tablock | tablockx | updlock} <Query_hint >::={ hash | order} group | {Concat | hash | merge} Union | fast number_row | force order | maxdop | robust plan | keep plan}
Update Syntax: Update [hint] table_expression_clause set_clause [where_clause] [returning_clause];	Update {table_name with ( [... n]) | view_name | rowset_function_limited} set {column_name = {expression | default | null} | @ variable = expression | @ variable = column = expression }[,... n]
dml_table_expression_clause ::=< BR >{{ [schema.] {table {[partition (partition) | subpartition (subpartition)] | @ dblink }}| {View | snapshot} [@ dblink]} | (subquery [with_clause]) | table_collection_expression} [t_alias] subquery: see subquery with heavy select syntax. with_clau SE: = with {read only | check option [constraint]} table_collection_expression: = table (collection_expression) [(+)] set_clause ::=< br> set {{{ ({column }[,... N]) = (subquery)} | column = {expr | (subquery )}}[,... N]} | value (t_alias) = {expr | (subquery)} where_clause :: = where condition returning_clause ::=< br> returning {expr }[,... N] into {data_item }[,... N]	{[From {<table_source> }[,... n] [where <search_condition>]} [where current of {[Global] cursor_name} | cursor_variable_name}]} [Option (<query_hint> [,... n])] <Table_source >::= table_name [[as] table_alias] [With (<table_hint> [,... n])] | view_name [[as] table_alias] | rowset_function [[as] table_alias] | derived_table [as] table_alias [(column_alias [,... n])] | <joined_table> <Joined_table >::=< table_source> <join_type> <table_source> On <search_condition> | <table_source> cross join <table_source> | <joined_table> <Join_type >::= [inner | {left | right | full} [outer]}] [<join_hint>] Join <Table_hint_limited >::={ fastfirstrow | holdlock | paglock | readcommitted | repeatableread | rowlock | serializable | tablock | tablockx | updlock} <Table_hint >::={ index (index_val [,... n] | fastfirstrow | holdlock | nolock | paglock | readcommitted | readpast | readuncommitted | repeatableread | rowlock | serializable | tablock | tablockx | updlock} <Query_hint> :: = {hash | order} group | {Concat | hash | merge} Union | {loop | merge | hash} join | fast number_rows | force order | maxdop | robust plan | keep plan}