Last Updated: 4/24/2001 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows some differences in concepts and terminology between Oracle and MS SQL Server:
Concept/Term
Oracle
MS SQL Server
Database engine
database
database server
Database (collection of tables)
schema
database
Roles/Groups
roles
groups
Database adminstrator account, database owner
dba
sa, dbo
Data about the database
Data Dictionary - one per server
Database Catalog - one per database "master" database - one per server
Blocks and extents
blocks and extents
pages and extents
Network software
SQL*Net
Net-library
Data stream protocol
Transparent Network Substrate (TNS)
Tabular Data Stream (TDS)
Case sensitivity of names of tables, columns, etc.
case-insensitive
depends on character sort order, default is case-insensitive
Synonyms
supported
not supported
Readonly transaction
supported
not supported
--Fred
Data Types
Last Updated: 6/6/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows the corresponding data types in Oracle and MS SQL Server:
Data Type
Oracle
MS SQL Server
Fixed Length String
CHAR(n) - limit 2KB
CHAR(n), CHARACTER(n) - limit 255 (6.5) - limit 8KB (7.0)
Variable Length String
VARCHAR2(n), VARCHAR(n) - limit 4KB in a column - limit 32KB in a variable - VARCHAR is obsolete
VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n) - limit 255 (6.5) - limit 8KB (7.0)
Integer
INTEGER, INTEGER(n), SMALLINT
INTEGER (4 bytes), INT (4 bytes), SMALLINT (2 bytes), TINYINT (1 byte), BIT (1 bit)
Fixed Point
NUMBER, NUMBER(n), NUMBER(n,d), FLOAT, FLOAT(n), FLOAT(n,d)
NUMERIC, NUMERIC(n), NUMERIC(n,d), DECIMAL, DECIMAL(n), DECIMAL(n,d), DEC, DEC(n), DEC(n,d), MONEY, SMALLMONEY
Floating Point
DECIMAL
FLOAT, FLOAT(n), DOUBLE PRECISION, REAL,
Date
DATE
DATETIME, SMALLDATETIME, TIMESTAMP - TIMESTAMP auto-updated
Binary
RAW(n) - limit 255 bytes
BINARY(n), VARBINARY(n), BINARY VARYING(n) - limit 255 (6.5) - limit 8KB (7.0)
Large String
LONG, LONG VARCHAR - limit 2GB - limit one per table row CLOB - limit 4GB
TEXT - limit 2GB
Large Binary
LONG RAW - limit 2GB - limit one per table row BLOB - limit 4GB
IMAGE - limit 2GB
Multi-byte chars
NCHAR(n) NVARCHAR(n) NCLOB - same limits as CHAR, VARCHAR, CLOB
NCHAR(n), NATIONAL CHAR(n), NATIONAL CHARACTER(n) NVARCHAR(n), NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n) NTEXT, NATIONAL TEXT - same limits as CHAR, VARCHAR, TEXT
OS File
BFILE
<not supported>
Row Identifier
implicit ROWID column
(use an IDENTITY column)
Secure OS Label
MLSLABEL, RAW MLSLABEL
<not supported>
128-bit Unique Number (UUID, GUID)
<not supported>
UNIQUEIDENTIFIER (version 7.0 only)
--Fred
Limits
Last Updated: 6/14/2000 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows differences in limits of Oracle and MS SQL Server:
Description
Oracle
MS SQL Server
Columns per table
1000
250 (6.5) 1024 (7.0)
Row size
unlimited
1962 bytes (6.5) 8060 bytes (7.0) - includes pointers, but not data, for TEXT and IMAGE columns
LONG and LONG RAW columns per row
1 (must be last column)
unlimited (16-byte pointer per)
LOB, TEXT, and IMAGE columns per row
unlimited (16-byte pointer per)
unlimited (16-byte pointer per)
Clustered indexes per table
1
1
Non-clustered indexes per table
unlimited
249
Columns per index
16
16
Index row size
2K bytes
900 bytes
Identifier Length
30 chars
30 chars (6.5) 128 chars (7.0)
Tables per SELECT
unlimited
16 (6.5) 256 (7.0)
Source code per stored procedure
64KB (6.5) 250MB (7.0)
Data type limits
(see Data Types)
--Fred
Operators
Last Updated: 6/7/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
Most operators are the same in Oracle and MS SQL Server. Here are some that differ:
Last Updated: 6/7/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
Oracle and MS SQL Server offer many of the same built-in functions. For example, they both offer ABS, EXP, ROUND, UPPER, LOWER, AVG, COUNT, SUM, ASCII, etc. The following table shows some of the corresponding functions that don''''t have the same name. For a more complete list, see "Migrating Oracle Applications to SQL Server"
Description
Oracle
MS SQL Server
Smallest integer >= n
CEIL
CEILING
Modulus
MOD
%
Truncate number
TRUNC
<none>
Max or min number or string in list
GREATEST, LEAST
<none>
Translate NULL to n
NVL
ISNULL
Return NULL if two values are equal
DECODE
NULLIF
String concatenation
CONCAT(str1,str2)
str1 + str2
Convert ASCII to char
CHR
CHAR
Capitalize first letters of words
INITCAP
<none>
Find string in string
INSTR
CHARINDEX
Find pattern in string
INSTR
PATINDEX
String length
LENGTH
DATALENGTH
Pad string with blanks
LPAD, RPAD
<none>
Trim leading or trailing chars other than blanks
LTRIM(str,chars), RTRIM(str,chars)
<none>
Replace chars in string
REPLACE
STUFF
Convert number to string
TO_CHAR
STR, CAST
Convert string to number
TO_NUMBER
CAST
Get substring from string
SUBSTR
SUBSTRING
Char for char translation in string
TRANSLATE
<none>
Date addition
ADD_MONTH or +
DATEADD
Date subtraction
MONTHS_BETWEEN or -
DATEDIFF
Last day of month
LAST_DAY
<none>
Time zone conversion
NEW_TIME
<none>
Next specified weekday after date
NEXT_DAY
<none>
Convert date to string
TO_CHAR
DATENAME, CONVERT
Convert string to date
TO_DATE
CAST
Convert date to number
TO_NUMBER(TO_CHAR(d))
DATEPART
Date round
ROUND
CONVERT
Date truncate
TRUNC
CONVERT
Current date
SYSDATE
GETDATE
Convert hex to binary
HEXTORAW
CAST
Convert binary to hex
RAWTOHEX
CONVERT
If statement in an expression
DECODE
CASE ... WHEN or COALESCE
User''''s login id number or name
UID, USER
SUSER_ID, SUSER_NAME
User''''s database id number or name
UID, USER
USER_ID, USER_NAME
Current user
USER
USER
--Fred
Differences in SQL Syntax
Last Updated: 3/21/2001 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows the different syntax used in Oracle and MS SQL Server for the same SQL operations:
Description
Oracle
MS SQL Server
Left Outer Join
WHERE column1 = column2(+)
FROM table1 LEFT OUTER JOIN table2 ON table1.column1 = table2.column2
Note: The following syntax is also supported, but is no longer recommended: WHERE column1 *= column2
Right Outer Join
WHERE column1(+) = column2
FROM table1 RIGHT OUTER JOIN table2 ON table1.column1 = table2.column2
Note: The following syntax is also supported, but is no longer recommended: WHERE column1 =* column2
Full Outer Join
FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column2
SELECT without FROM
SELECT ''''hello world'''' FROM DUAL
SELECT ''''hello world''''
SELECT data into a table
CREATE TABLE AS SELECT ...
SELECT ... INTO
Intersection of 2 SELECTS
SELECT ... INTERSECT SELECT ...
SELECT ... WHERE EXISTS (SELECT ...)
Subtraction of 2 SELECTS
SELECT ... MINUS SELECT ...
SELECT ... WHERE NOT EXISTS (SELECT ...)
INSERT into a JOIN
INSERT INTO SELECT ...
Create a VIEW and INSERT INTO it.
UPDATE data in a JOIN
UPDATE SELECT...
Create a VIEW and INSERT INTO it.
UPDATE one table based on criteria in another table
<not supported>
UPDATE table FROM ...
DELETE rows from one table based on criteria in another table
<not supported>
DELETE FROM table FROM ...
DROP a column from a table
<not supported until Oracle 8i>
ALTER TABLE table_name DROP COLUMN column_name
Readonly VIEW
CREATE VIEW ... WITH READONLY
GRANT SELECT ...
Save point
SAVEPOINT
SAVE TRANSACTION
Table lock
LOCK TABLE...IN SHARE MODE
SELECT...table_name (TABLOCK)
Exclusive table lock
LOCK TABLE...IN EXCLUSIVE MODE
SELECT...table_name (TABLOCKX)
Reserving index space
PCTFREE=0
FILLFACTOR=100
Declaring a local variable
DECLARE varname type;
DECLARE @varname type
Initializing a local variable
DECLARE varname type := value;
<not supported>
Declaring a constant
DECLARE varname CONSTANT type := value;
<not supported>
Assigning to a
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
