Microsoft Transact-SQL Programming

Relational Database Fundamentals

• Overview of Relational Database Concepts
• Relational Databases and Relational Database Management Systems
• SQL, DML and DDL Languages

Manipulating Query Results

• Using Row Functions
  • Character
  • Numeric
  • Date and Time
  • Data Conversion
• Using Column Functions
• Handling NULLs
• Aggregating Results using GROUP BY
• Restricting Groups with the HAVING Clause

Writing Basic SQL Queries

• Displaying Table Structures
• Column Datatypes
• SELECTing Column Data FROM a Table
• SELECT DISTINCT Values
• COUNTing Rows
• Restricting Rows using the WHERE Clause
  • Using Operators
  • Mathematical
  • Logical
  • Comparison
  • Concatenation
  • Set Operators
• Sorting Results Using ORDER BY
• Joining Multiple Tables
• Using Column and Table Aliases
• Pseudo Columns

Advanced Query Techniques

• Outer Joins
• Joining a Table to Itself
• Subqueries
• Hierarchical Queries
• Using the PIVOT operator
• Tips for Developing Complex SQL Queries

Manipulating Table Data Using SQL's Data Manipulation Language (DML)

• INSERT into Tables
• UPDATE Table Data
• DELETE from Tables
• TRUNCATE Tables
• Controlling Transactions
  • COMMIT
  • ROLLBACK
  • SAVEPOINT

Creating a Database

• Database Development Methodology Overview
• Building a Logical Data Model
  • Identifying Entities and Attributes
  • Isolating Keys
  • Relationships between Entities
  • Creating Entity-Relationship Diagrams
• Transforming to Physical Design
  • Migrating Entities to Tables
  • Selecting Primary Keys
  • Defining Columns
  • Enforcing Relationships with Foreign Keys
• Constructing the Database using DDL
  • Creating Tables, Indexes, Constraints and Views
  • Using Table and Column COMMENTs
• Facilitating Development Using CASE Tools

Managing Database Objects

• Dropping Tables, Indexes and Views
• ALTERing Tables, Indexes and Views
• Enabling and Disabling Constraints
• Managing Security

Performance Tuning

• Writing Efficient SQL
• EXPLAIN Query Analysis
• Indexing for Performance
• Index-Organized Tables
• Clustering

Stored Procedures

• Definition and Benefits of Use
• CREATE PROCEDURE
  • Syntax
  • Variables and Parameters
  • Code Examples
• Temporary Tables
• Control of Program Flow
• ALTER and DROP PROCEDURE
• Implementation Differences

User-Defined Functions

• Definition and Benefits of Use
• CREATE FUNCTION
  • Syntax
  • RETURN Clause and the RETURNS Statement
  • Scalar vs. Table Functions
• Comparison with Stored Procedures
• Returning Scalar Values and Tables
• ALTER and DROP FUNCTION
• Using CROSS APPLY

 

Triggers

• Definition and Benefits of Use
• Alternatives (e. g., Constraints)
• CREATE TRIGGER
  • Syntax
  • Trigger Types
• "Inserted" (or "NEW") and "Deleted" (or "OLD") Tables
• Event Handling and Trigger Execution
• Nested and Recursive Triggers
• ALTER and DROP TRIGGER

Complex Queries

• Improving SQL Queries with Common Table Expressions (CTE’s)
• Building User Reports with Multi-Table Joins
• Allowing Users to Build SQL Queries Dynamically

T-SQL Code Constructs

• Exploiting the Programming Features of T-SQL
  • Conditional Constructs
  • Looping Constructs
• Building Multi-batch Scripts
• Invoking System Functions
• Declaring Variables Inside Scripts
• Using Variables in Scripts
• Using Temporary Tables in Scripts
• Correctly Handling Errors
  • Error Handling
  • Debugging
  • Code Blocks
  • Predefined System Variables
• Using the SQLCMD Utility

Working with Data Types and Functions

• Effective use of Data Types in SQL
  • String
  • Numeric
  • Time/ Date
  • Other
• How to Convert Data
• Converting Expressions Using the CAST Function
• Controlling Formatting with the CONVERT Function
• Creating Conditional Tests with the CASE Function
• Substitution of Non-null Values with the COALESCE Function
• Replacing Nulls with the ISNULL Function
• Analyzing Data Points Using Ranking Functions

Transactions and Locking

• Effective use of Transactions
• Using Isolation Level
• Implementing Data Integrity with Transactions
  • COMMIT
  • ROLLBACK
  • SAVEPOINT
• Maximizing Concurrency with Correct use of Locking
  • Read Locks
  • Write Locks
  • Shared Locks
• Increasing throughput by Choosing Appropriate Transaction Isolation
• Preventing Deadlocks

More Stored Procedures

• Designing a Stored Procedure
  • Input Parameters
  • Output Parameters
  • Default values
• Implementing Exception Handling
• Adding Fault Tolerant Transaction Logic
• Designing Functions
• Building Triggers
• DDL Triggers
• INSTEAD OF Triggers
• AFTER Triggers

Advanced Views

• Designing Views
• Updating through a View
• Inserting Records through a View
• Deleting through a View
• Indexed Views and Performance Applications

Working with XML

• Benefits of XML
• Working with XML Documents
• Working with XML Schemas
• Storing Data in XML Data Type
• Working with XML Methods
  • Query
  • Exist
  • Value
  • Modify
  • Nodes
• Producing XML from SELECT Statements Using the FOR XML Clause
• Validating XML using XML Schemas

Security in SQL Server

• Overview of SQL Server Security Architecture
• Security Implementation Changes in SQL Server 2005 and 2008
• Organizing Tables with Schemas
• Granting Permissions
• Grouping Users into Roles
• Preventing SQL Injection Attacks