Syllabus of PHP & MyMicrosoft Online Training Course
Module 1: Create and alter tables
- 1. CREATE TABLE (Transact-SQL)
- 2. ALTER TABLE (Transact-SQL)
- 3. Differences between SQL Server temporary tables and table variables
- 4. Use Table-Valued Parameters (Database Engine)
- 5. Temporary Tables in SQL Server
- 6. Reasons to Avoid Triggers
- 7. OUTPUT Clause (Transact-SQL)
- 8. SQL Server Trigger Alternatives with the OUTPUT Clause
- 9. SQL Strategies for ‘Versioned’ Data
- 10. Yet Another SQL Strategy for Versioned Data
- 11. SQL Server Performance Tuning : Table Variable Vs. Temporary Tables
- 12. Specify Computed Columns in a Table
- 13. How to Partition an existing SQL Server Table
- 14. SQL Server Database Partitioning Myths and Truths
- 15. SQL Server Partitioned Tables with Multiple Filegroups for High Availability
- 16. Create a Database Schema
- 17. Ownership and User-Schema Separation in SQL Server
- 18. Changing SQL Server Collation After Installation
- 19. Identify SQL Server Instance and Database Collation Using T-SQL and SSMS
- 20. How to Change the COLLATION of a SQL Server Column
- 21. Partitioned Tables and Indexes
- 22. Tutorials DBA Dev BI Categories Events Columnstore index feature in SQL Server 2012
- 23. Identify the best tables for SQL Server 2016 Columnstore Index Migration
- 24. SQL Server Column Store Index Performance
- 25. Use Sparse Columns
- 26. SQL Server Sparse Columns Identifying Columns For Conversion
Module 2: Implement programming objects
- 1. SQL SERVER – Example of DDL, DML, DCL and TCL Commands
- 2. Understanding GRANT, DENY, and REVOKE in SQL Server
- 3. SQL SERVER – Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video
- 4. Troubleshoot Connecting to the SQL Server Database Engine
- 5. Troubleshooting SQL Server Connection Issues
- 6. Granting permission with the EXECUTE AS command in SQL Server
- 7. EXECUTE AS Clause (Transact-SQL)
- 8. How to configure SSL encryption in SQL Server
- 9. SQL Server 2012 Contained Database Feature
- 10. SQL Server Database Guest User Account
- 11. SQL Server Security Tips
- 12. Understanding SQL Server fixed server roles
- 13. Ownership chaining in SQL Server security feature or security risk
Module 3: Design the locking granularity level
- 1. SQL Server Concurrency: Locking, Blocking and Row Versioning
- 2. Deadlocking
- 3. Detecting and Ending Deadlocks)
- 4. SET TRANSACTION ISOLATION LEVEL
- 5. Demonstrations of Transaction Isolation Levels in SQL Server
- 6. SQL Server, Locks Object
- 7. SQL Server DBA Concurrency and Locking Interview Questions
- 8. Comparison of SQL Server Serializable and Snapshot isolation levels
- 9. SQL Server Locking and Blocking Tips
Module 4: Implement indexes
- 1. SQL Server Indexing Tips
- 2. Stairway to SQL Server Indexes
- 3. Clustered and Nonclustered Indexes Described
- 4. Querying SQL Server Index Statistics
- 5. Can I Use Statistics to Design Indexes? (Dear SQL DBA Episode 18)
Module 5: Implement data types
- 1. Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server
- 2. Auto generated SQL Server keys with the uniqueidentifier or IDENTITY
- 3. XML Data Type and Columns (SQL Server)
- 4. SQL Server Spatial Data Storage Tips
- 5. SQL CLR Data Types and Performance
- 6. CAST and CONVERT (Transact-SQL)
- 7. Data Types (Transact-SQL)
Module 6: Create and modify constraints
- 1. SQL Server Constraints Tips
- 2. table_constraint (Transact-SQL)
- 3. Blitz Result: Foreign Keys or Check Constraints Not Trusted
- 4. Controlling Constraint Checking by Bulk Import Operations
Module 7: Design and implement stored procedures
- 1. SQL Server Stored Procedure Tutorial
- 2. CREATE PROCEDURE (Transact-SQL)
- 3. Exporting to Excel Using a CLR Stored Procedure
- 4. Using Table Valued Parameters (TVP) in SQL Server
Module 8: Design T-SQL table-valued and scalar functions
- 1. CREATE FUNCTION (Transact-SQL)
- 2. Increase your SQL Server performance by replacing cursors with set operations
- 3. SQL Server Cursor Alternatives
- 4. Replacing Cursors with Set-Based SQL Queries – Part 1
- 5. Deterministic and Nondeterministic Functions
Module 9: Create, use, and alter user-defined functions (UDFs)
- 1. CREATE FUNCTION (Transact-SQL)
- 2. SQL Server Functions - User Defined UDF Tips
- 3. SQL Server CROSS APPLY and OUTER APPLY
- 4. Introduction to SQL Server CLR table valued functions
- 5. CLR String Sort Function in SQL Server 2005
- 6. Create CLR Functions
Module 10: Create and alter views
- 1. How to Partition an existing SQL Server Table
- 2. SQL Server Database Partitioning Myths and Truths
- 3. Create Partitioned Tables and Indexes
- 4. Create Indexed Views
Module 11: Design database objects
Module 12: Design tables
- 1. Design Patterns for Relational Databases
- 2. 11 important database designing rules which I follow
- 3. Database Normalization Basics
- 4. Database normalization
- 5. What does BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN mean?
- 6. Transaction Statements (Transact-SQL)
- 7. Auto generated SQL Server keys with the uniqueidentifier or IDENTITY
- 8. Clustered Indexes Based Upon GUIDs
- 9. Temporary Tables and Table Variables
- 10. Simplify Large Queries with Temporary Tables, Table Variables and CTEs
- 11. SQL Server Encryption
- 12. Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server
- 13. The Art of SQL Server FILESTREAM by Jacob Sebastian and Sven Aelterman
- 14. FILESTREAM (SQL Server)
- 15. FileTables (SQL Server)
- 16. FileTable Feature in SQL Server 2012
- 17. Getting started with SQL Server 2014 In-Memory OLTP
- 18. In-Memory OLTP (In-Memory Optimization)
Module 13: Design for concurrency
- 1. Microsoft SQL Server Coding for Maximizing Concurrency in Applications
- 2. SQL Server Concurrency: Locking, Blocking and Row Versioning
- 3. Understanding how SQL Server handles Service Isolation)
- 4. Improve SQL Server Efficiency by Switching to INSTEAD OF Triggers
- 5. SQL Server Triggers Tips
- 6. SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
Module 14: Design indexes
- 1. Stairway to SQL Server Indexes
- 2. SQL Server Indexing Tips
- 3. What is a SARG exactly? Training classes and knowledge retention!
- 4. XML Indexes (SQL Server)
- 5. Investigating the new Spatial Types in SQL Server
- 6. Spatial Indexes Overview
- 7. CREATE INDEX (Transact-SQL)
Module 15: Design data integrity
- 1. Primary and Foreign Key Constraints
- 2. Unique Constraints and Check Constraints
- 3. SQL SERVER – Difference Between Candidate Keys and Primary Key In Simple Words
- 4. SQL NULL Values
- 5. XML Schema Collections (SQL Server)
Module 16: Design for implicit and explicit transactions
- 1. Transaction Statements (Transact-SQL)
- 2. What is a transaction?
- 3. BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)
- 4. CATCH in SQL Server 2005 An Easier Approach to Rolling Back Transactions in the Face of an Error.
Module 17: Optimize and troubleshoot queries
Module 18: Optimize and tune queries
- 1. Data Conversion in SQL Server
- 2. SQL Server Execution Plans
- 3. Graphical Query Plan Tutorial
- 4. SQL Server JOIN Hints
- 5. Database Engine Tuning Advisor
- 6. Database Engine Tuning Advisor
- 7. dta Utility
- 8. Using a CTE with a T-SQL Pivot Statement
- 9. WITH common_table_expression (Transact-SQL)
- 10. SQL Server Queries With Hints
- 11. Query Hints (Transact-SQL)
- 12. SQL Server Semantic Search to Find Text in External Files
- 13. Optimize SQL Queries
Module 19: Troubleshoot and resolve performance problems
- 1. Monitor Resource Usage (System Monitor)
- 2. Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)
Module 20: Optimize indexes
- 1. Create Indexes with Included Columns
- 2. CREATE FULLTEXT INDEX (Transact-SQL)
- 3. Stairway to SQL Server Indexes
- 4. Rebuilding SQL Server indexes using the ONLINE option
Module 21: Capture and analyze execution plans
- 1. SQL Server Execution Plans
- 2. Display and Save Execution Plans
- 3. When to Breakdown Complex Queries
- 4. Huge transaction in Sql Server, are there any problems?
Module 22: Collect performance and system information
- 1. Performance Tuning with SQL Server Dynamic Management Views
- 2. SQL Server Database Engine Tuning Advisor for Performance Tuning
- 3. SQL Server Extended Events Sessions
- 4. Extended Events
- 5. Open Activity Monitor (SQL Server Management Studio)
- 6. Performance Analysis Using SQL Server 2008 Activity Monitor Tool
- 7. Getting started with SQL Server 2014 In-Memory OLTP