sql-oracle and SQL Server differences

1.string Contact operator

SQL Server use + or contact (SQL Server 2012)

In Oracle, you can also use the contact, but can not used + to the contact string, you should use | |;

2.oracle date time is different with SQL Server date.

SQL Server Date just has a date part, not contains time part.

Oracle date format has a date and time part.

This is a big difference.

But how do you use the ORACLE SQL Developer, do not modify the default settings you query

Select sysdate from dual; is not see the time part of you can use

Select to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

You can clearly see the time and date sections, of course, if you change the configuration, or use other database query tools,

Perhaps you can see the time section, for example, the D bever I use can be seen directly.

Timestamp in Oracle has the DateTime2 type in the same peer SQL Server , which contains timezone and a certain magnitude of second value.

Time Conversion in Oracle uses to_date (Datestring,dateformat);

For example : to_date (' 1990-11-12 ', ' yyyy-mm-dd ');

3.oracle not support table alias with AS.

So you can write the following statement.

Select * from table as alias;

If you add a derived fieldto query, Oracle requires an alias. Otherwise you will get an error. as follows:

Select rownum,* from HR. countries;

RePort error:ora:00936, missing expression.

Change to use as follows:

Select rownum,c.* from HR. Countries C;

hash functions in 4.oracle and SQL Server,

functions such as checksum (binary_checksum) and hashbytes are supported in SQL Server, as described in MSDN.

functions such as Ora_hash (10g) are supported in Oracle

Select Owa_opt_lock. CHECKSUM (RID | | rowname),

Ora_hash (RID | | rowname,1024,1),

Ora_hash (to_char (RID) | | rowname,1024,1),

Dbms_utility.get_hash_value (RID | | rowname,1,1024)

From Test;

share only one usage experience,Oracle , if you use the H-ash function for data comparison, it is recommended to use Ora_hash,

The first is because it is supported by the system itself, does not require a third-party package, does not require additional permissions, when compared to the content of millions of times,

It is much faster than the computational speed of the Owa_opt_lock.checksum function.

The top in 5.sqlserver is replaced with rownum<=n in Oracle .

the character comparison in the Ora CLE is case-sensitive by default, and SQL Server is configured by default and is case insensitive.

the length of the object (column name, etc.) in the Ora CLE cannot exceed 30.

If the column name contains spaces, you can use "" and use [] in SQL Server;

6. NULL is represented in Oracle when a string is empty , but differsin SQL Server .

INSERT into Test (Rid,rowname) VALUES (3, ');

Query in SQL Server using SELECT * from test where rowname= ' or len (rowname) = 0;

in Oracle , this query cannot find a value, but it needs to be used as follows:

SELECT * FROM test where rowname is null;

7. Other differences

because SQL Server expands the standard SQL, called TSQL, there is a lot of power.

For example it's The CTE is allowed to be updated directly, so there are times when you write a CTE of logic logic and finally update the values directly.

This notation is not allowed in Oracle.

so when you update (delete) some records of a table in Oracle, but you want to refer to more than one table, it's more cumbersome,

you need to write the update (delete) ... where exists (not exists) this way.

If you need to update with other table values, it is very uncomfortable to write a subquery after the set .

later , the emergence of the merge , to some extent, alleviate the discomfort, but still less than the use of SQL Server .

sql-oracle and SQL Server differences