SQL Server Fundamentals

Course Name: SQL Server Fundamentals
Course Duration : 4 weeks

Course Content
Overview
What is SQL Server
Application
Using SSMS
Drop a Database
Add a Database
Using T-SQL
sqlcmd
sqlps
T-SQL Language
Metadata
T-SQL Example
Scripting Objects
Linq To Sql
Linq to Sql Example
SMO
Analysis and Reporting
SSAS
SSIS
SSRS
Summary
References
Using Group By and Having
Overview and Examples
Basic Group By
Using Aggregate Functions and Having
Trimming Input And Summary
CTE’s and Ranking
CTE and Ranking Overview
Common Table Expressions
Demo: CTE
Top
Demo: Top
Ranking
Row Number
Demo: Row Number
Rank and DenseRank
Demo: Rank and DenseRank
NTile
Demo: Ntile
Partitioning
Demo: Partitioning
Aggregate Partitions
Demo: Aggregate Partitions
Summary
Hierarchies
Title
Overview
Hierarchyid/Tree
Why use hierarchyid?
Hierarchyid properties
Demo: Basic Properties
Demo: Manually finding descendants
Finding Descendents
Demo: IsDescendantOf
Adding Nodes
Demo: Adding nodes between
Demo: Adding nodes before/after
Demo: Adding hierarchyid column to table
Demo:Incrementally adding hierarchyid to table
GetReparentedValue
Demo: Moving node
Depth & Breadth
Demo: Indexes and triggers
Summary
Managing Query Plans
Overview
Query Plans
Query Optimizer
Plan Reuse
Example Plan Cache
Example Stored Procedure Plan
Ad hoc Expressions
Example Ad hoc Optimization
Example Ad hoc Off
Parameterization
Example Parameterization
Example ADO.NET Parameters
Plan Guides
Kinds of Plan Guides
Example Plan Guide
Managing Plan Guides
Example Managing Plan Guides
Example Plan Guide From Handle
Example Object Plan Guide
Example Optimize For
Example Template Plan Guide
Summary
Recursion, Pivoting, and Sampling
Overview
Recursive Query
Numeric Recursion Example
Tabular Recursion Example
Aggregate Recursion Example
Pivot
Cross-Tab Example
Group By Equivalent
Unpivot
Entity Attribute Value
Entity Attribute Example
Sampling
Sampling Example
Summary
Security I
Overview
Databases and Security
Authentication and Authorization
Principals
Example Managing Principals
Example Server Principals
Example Database Principals
Example Role Principals
Objects
Authorization and Permissions
Covering Permissions
Example Authorizing Principals
Example Using Permissions
Example Impersonation
Groups and Roles
Example Groups and Roles
Summary
References
Security II
Overview
Schemas
Schemas Example
Schemas Summary
Roles
Server Roles
Database Roles
Roles Example
Roles Summary
Execution Context
Changing Execution Context
Execution Context Scope
Execution Context Example
Execution Context Summary
Summary