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.

(Let someone contact you)