An analysis of Oracle and SQL Server

Oracle|server
The T-SQL is the language engine of Plsql, while Oracle's language engine is the same. Both of these query languages extend the ANSI SQL-92 standard to provide additional support. Almost all of the applications you create use these supplemental features. This article describes the most common, non-standard Oracle extensions, along with how to transform these extensions for use in SQL Server environments.

Selection of columns

When executing a data query with Plsql, the FROM clause is required, as is the case with SQL Server. The SELECT statement must select the data table for. There is a special table dual within the Oracle database. The dual table is an actual table in Oracle that any user can read and is often used in a select that does not have a target table. The dual table is created by Oracle along with the data dictionary, and all users can access the table by name dual. This table has only one column dummy, which is defined as the VARCHAR2 (1) type, with a row value of X. Selecting data from the dual table is often used to compute a constant expression through a SELECT statement, and because dual has only one row of data, the constant returns only once.

Oracle's dual query looks like this:
SELECT ' x ' from dual
The equivalent SQL Server query is the following:
SELECT ' x '

The following are null-related knowledge, using the dual:
Sql> Select 1 from dual where null=null;
No records found.
Sql> Select 1 from dual where null= ';
No records found.
Sql> Select 1 from dual where ' = ';
No records found.
Sql> Select 1 from dual where null is null;
1
---------
1
Sql> Select 1 from dual where NVL (null,0) =NVL (null,0);
1
---------
1
View current Connected Users
Sql> Select User from dual;

View current date, time
Sql> select Sysdate from dual;

Connection

Oracle Use | | Symbol as a connector, and SQL Server's connector is a plus sign: +.

The Oracle query looks like this:
Select ' Name ' | | ' Last Name ' from TableName

The corresponding SQL Server query looks like this:
Select ' name ' + ' last Name '


Digital Trade-offs

There is a trunc function in the Oracle database that returns n bits of M-bit decimal digits, or 0 digits if I omit M. The value of M can be negative, indicating that the number of m digits is truncated to the left of the decimal point.

You can use round or floor under SQL Server.

The following is an Oracle query:
Select TRUNC (15.79,1) "Truncate" from DUAL;

The following is a version of SQL Server for similar queries:
SELECT ROUND (15.79, 0) rounded, ROUND (15.79, 0,1) truncated
SELECT FLOOR (ROUND (15.79, 0)), FLOOR (ROUND (15.79, 0,1))


Digital conversion

Oracle's To_char function converts n-bit number data types to VARCHAR2 data types, with an optional numeric format.

SQL Server returns character data after a numeric conversion through the STR function. However, the function does not have a convenient format parameter.

Oracle queries are as follows:
SELECT To_char (123.45, 99999999999999) from tab
SELECT to_char (expiry_date, ' ddmonyyyy ') from tab

The following is a SQL Server version of the query:
SELECT STR (123.45, 14)
SELECT STR (Round (123.455, 2), 12,2)
SELECT CAST ((CONVERT (varchar (), expirydate,), ', ') as varchar (9))


Length and Len

The following is an Oracle query:
Select Length (' Sqlmag ') "Length in characters" from DUAL;

The above query is written under SQL Server:
Select LEN (' Sqlmag ') "Length in Characters"


Date

Both of these systems have their own current date and time formats.

Oracle acquisition date and in the following manner:
Sysdate

SQL Server is like this:
GETDATE ()

You can use all kinds of grammar to manipulate dates. The following code formats the month in the Oracle Date value (the return date plus n months):
Select add_months (sysdate,12) from dual

SQL Server completes the same functionality as follows:
Select DateAdd (Mm,12,getdate ())

The subtraction of the data is also different. The following code subtracts data directly from Oracle:
SELECT sysdate-add_months (sysdate,12) from dual

This is done by SQL Server:
SELECT DateDiff (DD, GetDate (), DateAdd (Mm,12,getdate ())

Reference:
Builder.com