SQL Server Training and Certification Course

Indexes play a major role in allowing SQL Server to retrieve data from a database securely and effectively. It also improves the performance of queries. Before going to implementing indexes, we need to know how they work and how they can be constructed from multiple columns. This module discusses several index types available in SQL Server, table structures that do not have indexes, designing and implementing views.

• Need for Indexes & Usage?
• Indexing Table & View Columns
• Index SCAN and SEEK
• INCLUDED Indexes & Usage
• Materializing Views (storage level)
• Composite Indexed Columns & Keys
• Indexes and Table Constraints
• Primary Keys & Non Clustered Indexes

This section describes the design and implementation of stored procedures. The participant will be able to design and create stored procedures, control execution content of stored procedures, and execute stored procedures that use parameters.

• Why to use Stored Procedures?
• Types of Stored Procedures
• Use of Variables and parameters
• SCHEMABINDING and ENCRYPTION
• INPUT and OUTPUT parameters
• System level Stored Procedures
• Dynamic SQL and parameterization

User-defined functions allow modular programming, faster execution, and reduces network traffic. This module teaches the types of user-defined functions, valid statements in a function, schema binding, and encryption. You'll also learn to design and implement user-defined functions.

• Scalar Valued Functions
• Types of Table Valued Functions
• SCHEMABINDING and ENCRYPTION
• System Functions and usage
• Date Functions
• Time Functions
• String and Operational Functions
• ROW_COUNT
• GROUPING Functions

This module describes what DML triggers are and how they drive data integrity, several types of triggers available in SQL Server, and how to define them in the database.

• Why to use Triggers?
• DML Triggers and Performance impact
• INSERTED and DELETED memory tables
• Data Audit operations & Sampling
• Database Triggers and Server Triggers
• Bulk Operations with Triggers

A cursor is a database object that retrieves data from result sets. In this section, we will show you the Cursor lifecycle, types of cursors, and how to use the SQL Server cursor to process a result set.

• Cursor declaration and Life cycle
• STATIC
• DYNAMIC
• SCROLL Cursors
• FORWARD_ONLY and LOCAL Cursors
• KEYSET Cursors with Complex SPs

Transactions are a sequence of operations performed on a single unit. They possess various properties and abbreviated as ACID (atomicity, consistency, durability, and isolations) properties. In this module, you'll explore transaction types and learn to implement them with real-time scenarios.

• ACID Properties and Scope
• EXPLICIT Transaction types
• IMPLICIT Transactions and options
• AUTOCOMMIT Transaction and usage
• SAVEPOINT and Query Blocking
• Complex SPs with Transactions
• Linked Server and DTC operations

Mindmajix offers advanced SQL Server interview questions and answers along with SQL Server resume samples. Take a free sample practice test before appearing in the certification to improve your chances of scoring high.