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 |