1 / 17

Module 5: Data Access

Module 5: Data Access. Overview. Introduce database components involved in data access Introduce concepts of Transact -SQL and Procedural SQL as tools to access and manipulate data Discuss transactional management concepts. Relational Engine. SQL Messages. HTTP Messages.

colman
Download Presentation

Module 5: Data Access

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. Module 5:Data Access

  2. Overview • Introduce database components involved in data access • Introduce concepts of Transact -SQL and Procedural SQL as tools to access and manipulate data • Discuss transactional management concepts

  3. Relational Engine SQL Messages HTTP Messages RPCMessages Results SQLOS Deadlock Monitor Memory Manager Scheduler Lock Manager Synchronization Svcs Buffer Pool SQL CLR Hosting Layer SQL Manager CLR T-SQLExecution QueryOptimizer T-SQL CompilerAlgebraizer Parser Expression Svcs QueryExecution Execution Environment METADATA Interface Storage Engine

  4. Demonstration 1: Evaluate an Execution Plan In this demonstration you will see how to: • Provide tracing within SQL Server • Review the execution plan for simple queries • Compare execution plan to the trace profiler • Review execution plan

  5. Structured Query Language • Oracle and SQL Server are compliant with entry-level SQL-92 • Both support many of the core features of SQL-99 and SQL-2003 • Categories of SQL statements • Data Definition Language (DDL) • Data Manipulation Language (DML) • Transaction Control Statements • Session Control Statements • System Control Statements

  6. Data Definition Language (DDL) • Define and alter database structures • Using : • CREATE, ALTER and DROP • Access control: • GRANT and REVOKE • + DENY in SQL Server • Identifiers limits in characters • Oracle <= 30 characters • SQL Server <= 128 Unicode characters • Temporary tables <= 116 Unicode characters

  7. Data Manipulation Language (DML) • Standard Terminology with Each Other • C = INSERT • R = SELECT • U = UPDATE • D = DELETE • There are Differences Though • Oracle: IN lists can have up to 1000 values • SQL Server: IN lists have no limited on number of values • Syntax variations – Example: • Oracle: SELECT Field1 || Field2 FROM Table1 • SQL Server: SELECT Field3 + Field4 FROM Table2

  8. Demonstration 2: Transact–SQL In this demonstration you will see how to: • Generate a query • Declare and use variables and row constructors • Aggregate summary data

  9. Control Statements Control Statements in Oracle and SQL Server

  10. Procedural and Transaction - SQL • Both use “BEGIN … END” and “IF” for block and conditional structuring. • 1 Statement <> BEGIN … END • > 1 Statement = BEGIN … END • Oracle offers loop structures: • LOOP … END LOOP, • FOR … END LOOP, • WHILE … END LOOP • SQL Server offers the loop WHILE • Dynamic SQL • ORACLE - DBMS_SQL or EXECUTE IMMEDIATE • SQL Server - sp_executesql and EXEC()

  11. Cursors • Versatile navigation through data • Both support Fetching and Scrolling • SQL Server cursor types include: FIRST NEXT PRIOR LAST ABSOLUTE RELATIVE FORWARD_ONLY INSENSITIVE SCROLL READ_ONLY STATIC DYNAMIC FAST_FORWARD LOCAL GLOBAL KEYSET-DRIVEN

  12. Integrated Full-Text Searching • Storing Large Text Documents is on the rise • RDBMS – Not Equiped on their own to handle this.

  13. Tools to Ease Development Cycles • Tools for Creating Queries • Command Line • SQL *Plus for Oracle • SQLCMD for SQL Server • Graphical User Interfaces • SQL *Plus for Oracle • SQL Server Management Studio • Beyond the Integrated Tools • Visual Studio .NET • ToadSoft Toad • And others

  14. Error Handling • Servers always raise the errors • T-SQL dealings • @@Error • TRY..CATCH • Oracle has predefined system exceptions • NO_DATA_FOUND • TOO_MANY_ROWS • and so on • SQL Server internal error messages • Severity levels • View with sys.messages • Both DBs allow custom exceptions messages

  15. Query Optimization • Cost-Based Optimization • Execution Plans based on : • Access methods • Statistics • Hints • Plans can be viewed with:

  16. Transaction Management • Oracle transactions end with COMMIT or ROLLBACK • SQL Server is an implicit commit for each row. • Make Explicit by using: • BEGIN TRAN … COMMIT TRAN or • or • SET IMPLICIT_TRANSACTIONS ON • Distributed transactions modify data in more than one database in a distributed database environment • Two-phase commit provides ways to manage data consistency and data integrity in a distributed environment • Make Explicit by using: • BEGIN TRAN … COMMIT TRAN • or • SET IMPLICIT_TRANSACTIONS ON

  17. Review • We examined various components of the relational engine that parses, optimizes, and executes database calls and fetches data • We learned about the different types of statements that constitute the Structured Query Language (SQL) • We were introduced to procedural SQL offered by Oracle and SQL Server • We learned that the cost-based optimizer in Oracle and SQL Server performs SQL optimization that can be superseded by hints from the user • We learned about the concepts of local and distributed transactions and the two-phase commit

More Related