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:
    • 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:
    • 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:
    • String concatenation string1 || string2 string1 + string2

    --Fred

  • Built-In Functions

    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"
    • 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:
    • 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

    上一页  [1] [2] [3]  下一页

    打印本文 打印本文 关闭窗口 关闭窗口
    Oracle 和 MIcrosoft SQL 的不同
    作者：武汉SEO闵涛  文章来源：敏韬网  点击数2827  更新时间：2009/4/22 22:05:26  文章录入：mintao  责任编辑：mintao
    rver

    1. Concepts and Terminology

      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 ServerData Type Oracle MS SQL ServerDescription Oracle MS SQL ServerDescription Oracle MS SQL ServerDescription Oracle MS SQL ServerDescription Oracle MS SQL Server
    打印本文 打印本文 关闭窗口 关闭窗口