Sql Server
-
· Understanding the Foundations of T-SQL
· Understanding Logical Query Processing
· Using the FROM and SELECT Clauses
· Working with Data Types and Built-in Functions
· CASE Expression and Related Functions
· Reviewing the Use of Functions
· Analyze the Data Types in the Sample Database
· Filtering and sorting Data
· Using the ORDER BY Clause to Sort Data
· Filtering Data with TOP and OFFSET-FETCH
· Filtering and Sorting Performance Recommendations
· Using Joins. Cross Joins, Inner Joins, Outer Joins, Multi-Join Queries
· Using Subqueries, Table Expressions and the APPLY Operator Subqueries
· Using Set Operators
· UNION and UNION ALL, INTERSECT, EXCEPT
· Grouping and Windowing
· Writing Grouped Queries
· Working with a Single Grouping Set
· Working with Multiple Grouping Sets
· Pivoting and Unpivoting
· Using Window Functions, Aggregate Functions, Ranking Functions, Offset Functions
· Improving Data Analysis Operations
· Querying full-text Data
· Creating Full-Text Catalogs and Indexes
· Full-Text Search Components
· Creating and Managing Full-Text Catalogs and Indexes
· Using the CONTAINS and FREETEXT Predicates
· Using the Full-Text and Semantic Search Table-Valued Functions
· Using the Full-Text Search Functions
· Check the FTS Dynamic Management Views and Backup and Restore of a Full-Text Catalog and Indexes
· Querying and Managing XML Data
· Returning Results As XML with FOR XML
· Querying XML Data with XQuery, XQuery Basics, FLWOR Expressions
· Using the XML Data Type
· Using the XML Data Type for Dynamic Schema
· Creating tables and enforcing Data integrity
· Creating and Altering Tables
· Choosing Table Indexes
· Using Constraints, Primary Key, Unique, Foreign Key, Check Constraints, Default
· Working with Table Constraints
· Designing and creating views, inline functions and synonyms
· Designing and Implementing Views and Inline Functions
· Creating a Synonym
· Comparing Views, Inline Functions, and Synonyms
· Inserting, updating, and Deleting Data
· Inserting Data (INSERT VALUES), (INSERT SELECT), (INSERT EXEC), (SELECT INTO)
· Updating Data, UPDATE Statement
· UPDATE Based on Join
· Deleting Data DELETE Statement,
· TRUNCATE Statement
· DELETE Based on a Join, DELETE Using Table Expressions
· Other Data Modification Aspects
· Using the Sequence Object and IDENTITY Column Property
· Using the IDENTITY Column Property
· Merging Data, Using the MERGE Statement
· Using the OUTPUT Option
· Working with the OUTPUT Clause
· INSERT with OUTPUT
· DELETE with OUTPUT
· UPDATE with OUTPUT
· MERGE with OUTPUT P
· Providing an Improved Solution for Generating Keys
· Implementing transactions, error handling, and Dynamic SQL
· Managing Transactions and Concurrency, Types of Transactions
· Basic Locking
· Transaction Isolation Levels
· Implementing Error Handling
· Detecting and Raising Errors
· Using Dynamic SQL, using sp_executesql
· Designing and implementing T-SQL routines
· Designing and Implementing Stored Procedures
· Executing Stored Procedures, Developing Stored Procedures
· Implementing Triggers, DML Triggers, AFTER Triggers, INSTEAD OF Triggers
· Implementing User-Defined Functions
· Table-Valued UDFs
· Using tools to analyze Query performance
· Getting Started with Query Optimization
· Query Optimization Problems and the Query Optimizer
· SQL Server Extended Events, SQL Trace, and SQL Server Profiler
· Using SET Session Options and Analyzing Query Plans, Execution Plans
· Using Dynamic Management Objects
· Implementing indexes and statistics
· Heaps and Balanced Trees
· Implementing Non Clustered Indexes
· Implementing Indexed Views
· Using Search Arguments
· Supporting Queries with Indexes
· Understanding Statistics
· Auto-Created Statistics
· Understanding cursors, sets, and temporary tables
· Evaluating the Use of Cursor/Iterative Solutions
· Iterations for Operations That Must Be Done Per Row
· Using Temporary Tables vs. Table Variables
· Performance Improvement Recommendations for Cursors and Temporary Objects
· Understanding further Optimization aspects
· Using Parameterized Queries and Batch Operations
· Optimizer Hints
· Query Optimization
· Analyze Execution Plans and Force Plans