SQL Server Developer Course Outline

 

T-SQL Query

  • Basic Query Skills
    • SELECT/INSERT/UPDATE/DELETE
    • JOINS
    • UNION/UNION ALL
    • EXCEPT
    • INTERSECT
    • ETC…

 

Advanced T-SQL Query

  • Transact-SQL Data Types
    • System Data Type
    • User Defined Data Type
    • New Data Type in SQL 2005
      • XML
      • VARCHAR(MAX), NVARCHAR(MAX), VARBIANRY(MAX)
    • New Data Type in SQL 2008
      • Data and Time Type
      • Hierarchy Data Type
      • Spatial Data Type
  • Data Manipulation Language Statements
    • SQL 2005 Enhancement and New Features
      • CTE (Common Table Expression)
      • Table Variable
      • OUTPUT Clause
      • OVER Clause
      • ROW_NUMBER(), RANK
      • TOP N for INSERT and UPDATE and DELETE
      • APPLY
      • PIVOT
      • TABLESAMPLE
      • String Concatenation
      • Hierarchies Query
    • SQL 2008 Enhancement and New Features
      • Declare
      • Compound Assignment Operators
      • Table Value Type and Table Value Parameters
      • Enhanced CONVERT function
      • New Data Type Function
      • MERGE
      • GROUPING SETS
      • FILESTREAM Storage
      • Transaction-SQL Row Constructors

 

T-SQL Programming

  • View
  • Stored Procedure
  • UDF (User Defined Function)
  • Trigger
  • CURSOR

 

Advanced SQL Programming 

  • Create and deploy
    • CLR Stored Procedure
    • CLR User Defined Function
  • DDL Trigger
  • TRANSACTION and ISOLATION LEVELS
  • DYNAMIC SQL
  • SQL Injection
  • Query Notification
  • High Availability Consideration and impact to database applicaiton
    • Database Mirroring
    • Replication
    • Log Shipping
  • Exceptions and Error Handling
  • Source Code Control
  • UNIT TEST

 

Data Access Technology

  • SQL Native Client
  • ADO.NET and ADO.NET Extension
  • MARS (multiple active result sets)
  • HTTP Endpoints

 

SQL Server Performance

  • Query logic
  • Index
    • Heap, Clustered, NONClustered
    • Deep Dive Index
      • Cover index
      • Index Usage
      • Understand selective of index
      • Understand Execution Plan
      • Create Query Plan
      • Statistics
      • Index Maintenance and Tuning
  • Data Partition, index Partition
  • Caching Strategies for database application
  • Compilation/Recompilation, Statement level Compilation
  • Locking, blocking and deadlock
    • Lock Types
    • Lock Resources
    • Isolation levels
      • Transaction Level Snapshot Isolation
      • Statement Level Snapshot Isolation
  • Measure Database performance
    • Tools to use
    • Scripts to use
    • DMVs
  • Diagnose and Troubleshoot Performance

 

Database Design

·         Conceptual Design

·         Logical Design

·         Physical Design

 

Database Solution (with sample project)

·         Web Access through HTTP Endpoints

·         Notification Services

·          Service Broker

Q&A with brief introduction of SQL Server BI features.

 

More info button
(Let someone contact you) 

 

Last update:. Visits -- | Web site design and hosting by WECAN.ca CMS