1 / 59

<Insert Picture Here>

<Insert Picture Here>. Comparing SQL Server and Oracle. Comparing Oracle and SQL Server Similarities. Similar Schema Objects (tables, views) Similar Datatypes Referential Integrity Check Constraints / Rules Transaction Support Triggers and Stored Subprograms SQL Access to System Catalogs.

beau-johnson
Download Presentation

<Insert Picture Here>

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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;

More Related