slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
<Insert Picture Here> PowerPoint Presentation
Download Presentation
<Insert Picture Here>

play fullscreen
1 / 59

<Insert Picture Here>

161 Views Download Presentation
Download Presentation

<Insert Picture Here>

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. <Insert Picture Here> Comparing SQL Server and Oracle

  2. Comparing Oracle and SQL ServerSimilarities • Similar Schema Objects (tables, views) • Similar Datatypes • Referential Integrity • Check Constraints / Rules • Transaction Support • Triggers and Stored Subprograms • SQL Access to System Catalogs

  3. Comparing Data Types

  4. Comparing Data Types

  5. Comparing Oracle and SQL ServerOrganization Main Differences: • Organization • Terminology • Connection Models • Transactional and Isolation Models • Temporary Tables • Application programming • Stored Subprograms • Utilities (Bulk Loading)

  6. Oracle database: Collection of schemas Stored in tablespaces Central schema: SYSTEM SQL Server database = Oracle schema Comparing Oracle and SQL Server What is a database? Oracleinstance= SQL Server server (Database plus processes) Memory Memory Processes Processes Master, model,msdb, tempdb Database 1 Database 2 Database 3 SYSTEM Schema 1 Schema 2 Schema 3

  7. Oracle Database Tablespace Segment Extent Block SQL Server Database Filegroup Extent (64 KB fixed) Page (8 KB fixed) Comparing Storage Structures

  8. SQL Server Storage Structures • Fundamental storage unit: Page (8 KB fixed) • Basic unit to allocate space to tables and indexes: Extent (64 KB fixed) Database OS file: Primary data file Secondary data file Filegroup Log file

  9. Oracle Storage Structures • Fundamental storage unit: Block • A logical block consists of one or more OS blocks. • The size of a logical block is defined by an initialization parameter. Logical Physical Data file Tablespace Segment Extent OS block Block

  10. Comparing Oracle and SQL ServerTerminology Main Differences: • Organization • Terminology • Connection Models • Transactional and Isolation Models • Temporary Tables • Application programming • Stored Subprograms • Utilities (Bulk Loading)

  11. Differences in Terminology • Oracle spfile(auto managed binary) = SQL Server sysconfig • Oracle v$, USER_TABLES = SQL Server sp_ stored procedures, sysxxx tables • Oracle has schemas/tablespaces = SQL Server databases/devices • Oracle has redo buffer cache, redo logs for archiving = SQL Server transaction log • Oracle has UNDO space for read consistency = no equivalent in SQL Server* (SS2K5) • Oracle SQL*PLUS (/) = SQL Server ISQL (go)

  12. Connecting to the Database • With multiple databases in SQL Server, you use the following command to switch databases: • With only one database in Oracle, you issue one of the following commands to switch schemas: • OR SQL> Use hr SQL> CONNECT hr/hr; SQL> ALTER SESSION SET CURRENT_SCHEMA=HR;

  13. Comparing Schema Objects • Oracle schema objects not available in SQL Server: • Database link • Profile • Materialized view • Sequence (SQL Server: Serial data type) • Synonym • SQL Server rule, integrity, and default are implemented as constraints of Oracle tables.

  14. Naming Database Objects • Names must be from 1 to 30 bytes long with these exceptions: • Names of databases are limited to 8 bytes. • Names of database links can be as long as 128 bytes. • Nonquoted names cannot be Oracle-reserved words. • Nonquoted names must begin with an alphabetic character from your database character set.

  15. Naming Database Objects • Nonquoted names can contain only: • Alphanumeric characters from your database character set • The underscore (_) • Dollar sign ($) • Pound sign (#) • No two objects can have the same name within the same namespace. • MS Tip: OMWB assists with resolving naming conflicts.

  16. Comparing Oracle and SQL ServerConnection Models Main Differences: • Organization • Terminology • Connection Models • Transactional and Isolation Models • Temporary Tables • Application programming • Stored Subprograms • Utilities (Bulk Loading)

  17. Differences in Connection Models • The Oracle server is “connection-based”. It offers: • Multiple active result-sets per connection • Only one connection needed • Multiple sessions per connection • Multiple transactions per session • Distributed database access via database links • SQL Server is “stream-based”. It offers: • One active result-set per connection • Typically several connections used

  18. Handling Result Sets • SQL Server automatically put resultsets in stream • Returns data in Tablular Data Stream format (TDS) • Multiple resultsets possible • Oracle provides cursor variables • Client receives cursor variable • Cursor Variable is a handle to server side memory resident cursor • Client fetches as much of data as desired • Multiple Cursor Variables easily accommodated • Can pass Cursor Variable to other clients or servers

  19. Comparing Oracle and SQL ServerTransaction & Isolation Models Main Differences: • Organization • Terminology • Connection Models • Transactional and Isolation Models • Temporary Tables • Application programming • Stored Subprograms • Utilities (Bulk Loading)

  20. Comparing Transactional Models

  21. Transactional Models • Oracle supports always full Isolation Model • Only committed data visible to other users • Allows repeatable reads • SQL Server allows several modes • SET TRANSACTION ISOLATION LEVEL … for each transaction • Uses BROWSE mode (timestamp) to detect update conflicts (optimistic locking) Transaction Handling

  22. Transactional Models Transaction Handling • Oracle has implicit Transactions • All SQL statements are transaction controlled • No BEGIN TRANSACTION - a new transaction begins at the end of the previous one • Transaction ends at COMMIT or ROLLBACK • Nested Transactions could be defined via SAVEPOINT • SQL Server programmers use explicit Transactions • Programmers may explicitly use BEGIN, END TRANSACTION and control COMMIT/ROLLBACK manually. • If not in an explicit transaction, each statement auto-commits after execution • Nested Transactions do not commit but may rollback

  23. Comparing the Transaction Models • Key differences between the transaction models:

  24. Beginning a Transaction SQL Server begins transactions explicitly: The Oracle database begins transactions implicitly: SQL> BEGIN TRANSACTION 2 INSERT INTO regions VALUES (5, ‘Southeast Asia’) 3 INSERT INTO countries VALUES (‘VN’, ‘Vietnam’, 5) 4 COMMIT TRANSACTION SQL> INSERT INTO regions VALUES (5, Southeast Asia’); 2 INSERT INTO countries VALUES (‘VN’,‘Vietnam’, 5); 3 COMMIT;

  25. Ending a Transaction SQL Server always commits statements if outside an explicit transaction: With Oracle you always need to COMMIT or ROLLBACK SQL> INSERT INTO regions 2 VALUES (6, ‘South America’) 3 INSERT INTO countries 4 VALUES (‘PE’, ‘Peru’, 6) Transaction #1 Transaction #2 SQL> INSERT INTO regions 2 VALUES (6, ‘South America’); 3 COMMIT; 4 INSERT INTO countries 5 VALUES (‘PE’, ‘Peru’, 6) 6 COMMIT; Transaction #1 Transaction #2

  26. Comparing Isolation Levels • Isolation levels supported by SQL Server and Oracle: * * Read Only Transactions only

  27. Comparing Oracle and SQL ServerTemporary Tables Main Differences: • Organization • Terminology • Connection Models • Transactional and Isolation Models • Temporary Tables • Application programming • Stored Subprograms • Utilities (Bulk Loading)

  28. Temporary Tables • SQL Server: • Local temporary tables, names beginning with # • Global temporary tables, names beginning with ## • Not compatible with Oracle’s naming conventions • Options in Oracle: • Temporary ANSI-style (global temporary) tables • Multitable joins (optimized internally) • Materialized views • PL/SQL tables

  29. Temporary Tables CREATE GLOBAL TEMPORARY TABLE emp_temp( eno NUMBER, ename VARCHAR2(20), sal NUMBER) ON COMMIT DELETE ROWS; INSERT INTO emp_temp VALUES( 101,’Inga’,1000); SELECT count(*) FROM emp_temp; • Data exists only for the duration of a transaction or session • Data only visible within a single transaction or session • No redo generated, only undo • Data segments created in a user’s temporary tablespace

  30. Comparing Oracle and SQL ServerProgramming Main Differences: • Organization • Terminology • Connection Models • Transactional and Isolation Models • Temporary Tables • Application programming • Stored Subprograms • Utilities (Bulk Loading)

  31. Migrate a table with an IDENTITY column • Oracle doesn't support the IDENTITY attribute. If you want an auto-incrementing column in Oracle, then create a sequence and use that sequence in a trigger associated to the table

  32. Migrate a table with an IDENTITY column • SQL Server version • Create the Table • Insert Row SQL> CREATE TABLE Friend ( 2 FriendID INT IDENTITY PRIMARY KEY NOT NULL, 3 Name VARCHAR(50), 4 PhoneNo VARCHAR(15)DEFAULT ‘Unknown Phone’) SQL> INSERT INTO Friend (Name, PhoneNO) 2 VALUES (‘Mike’,’123-456-7890’);

  33. Migrate a table with an IDENTITY column • Oracle version • Create the Table • Insert Row SQL> CREATE TABLE Friend ( 2 FriendID NUMBER PRIMARY KEY NOT NULL, 3 Name VARCHAR(50), 4 PhoneNo VARCHAR(15)DEFAULT ‘Unknown Phone’) SQL> INSERT INTO Friend (Name, PhoneNO) 2 VALUES (‘Mike’,’123-456-7890’);

  34. Migrate a table with an IDENTITY column • Oracle version cont. • Create the Sequence • Create the Trigger SQL> CREATE SEQUENCE SEQ; SQL> CREATE OR REPLACE TRIGGER FRIEND_AUTO_NUMBER 2 BEFORE INSERT ON Friend 3 FOR EACH ROW 4 BEGIN 5 SELECT SEQ.NEXTVAL INTO :NEW.FriendID FROM DUAL; 6 END;

  35. Null Handling Semantics • SQL Server interprets the empty string as a single blank space. • Oracle interprets the empty string as NULL value. Rewrite to use a single blank space and not the empty string. SQL> SELECT customer_id, date_of_birth 2 FROM customers 3 WHERE cust_email = ‘’ SQL> SELECT customer_id, date_of_birth 2 FROM customers 3 WHERE cust_email = ‘ ’

  36. SQL Comparison

  37. Object Name Changes • The way to reference a table or view in a SQL statement is different: • SQL Server • database_name.owner_name.table_name • Oracle • user_schema.table_name • Example accessing other schema:

  38. Displaying Information about an object • In Oracle use the SQL*Plus DESCRIBE command to display the structure of a table. Microsoft SQL Server Oracle SP_HELP table_name DESCRIBE table_name

  39. SELECT Statement: FROM Clause In SQL Server, FROM clause is optional. In Oracle, FROM clause is required. SQL> SELECT getdate() SQL> SELECT sysdate FROM dual;

  40. SELECT Statement: SELECT INTO Clause In SQL Server, SELECT INTO clause is used. In Oracle, if the table exists, rewrite using the INSERT INTO clause. SQL> SELECT cust_first_name, cust_last_name 2 INTO contacts 3 FROM customers SQL> INSERT INTO contacts 2 SELECT cust_first_name, cust_last_name 3 FROM customers;

  41. SELECT Statement: SELECT INTO Clause cont. In SQL Server, SELECT INTO clause is used. In Oracle, if the table does not exist, rewrite using the Create table as select clause. SQL> SELECT cust_first_name, cust_last_name 2 INTO contacts 3 FROM customers SQL> CREATE contacts AS 2 SELECT cust_first_name, cust_last_name 3 FROM customers

  42. SELECT Statement: Column Alias In SQL Server, example using column alias: In Oracle, column alias is placed after the column name SQL> SELECT email = cust_email 2 FROM customers SQL> SELECT cust_email email 2 FROM customers;

  43. SELECT Statement: TOP nClause In SQL Server, TOP clause is gives you the top n rows retrieved in the result set. In Oracle, you must do a subselect and use ROWNUM SQL> SELECT TOP 5 empname, total_com FROM emp ORDER BY total_com SQL> SELECT * FROM (SELECT empname, total_com FROM emp ORDER BY total_com )WHERE ROWNUM < 6

  44. INSERT Statement • In SQL Server, the INTO clause is optional. • In Oracle, the INTO clause is required. SQL> INSERT regions 2 VALUES (202, ‘Southeast’) SQL> INSERT INTO regions 2 VALUES (202, ‘Southeast’);

  45. UPDATE statement • SQL Server example: • Rewrite in Oracle: SQL> UPDATE inventories 2 SET quantity_on_hand = 0 3 FROM inventories i, product_information p 4 WHERE p.product_id = p.product_id 5 and product_status=‘planned’ SQL> UPDATE inventories 2 SET quantity_on_hand = 0 3 WHERE product_id IN (SELECT product_id 4 FROM product_information 5 WHERE product_status = ‘planned’);

  46. DELETE statement • SQL Server: • Rewrite in Oracle: SQL> DELETE FROM inventories 2 FROM inventories i, product_information p 3 WHERE i.product_id = p.product_id 4 AND supplier_id = 102066 SQL> DELETE FROM inventories 2 WHERE product_id IN (SELECT product_id 3 FROM product_information 4 WHERE supplier_id = 102066);

  47. Operators • Examples of operator differences:

  48. Built-In Functions • Both SQL Server and Oracle have proprietary built-in functions:

  49. Data Type Conversion • SQL Server uses the CONVERT function to convert data types. • Replace with the appropriate Oracle equivalent function: SQL> SELECT CONVERT(char, GETDATE()) SQL> SELECT TO_CHAR(sysdate) 2 FROM dual;