SQL Server Introduction & Installation
- SQL Server 2012 & 2014 Installation
- Service Accounts & Types, Usage
- Authentication Modes & Usage
- Instance Configurations Options
- SQL Server Features & Purpose
- Using Management Studio (SSMS)
- Configuration Tools & Use
- Naming Conventions & Collation
Indexes and Query Tuning
- 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 and NonClustered Indexes
SQL Server 2012 Database Design
- SQL Server Database Architecture
- Database Creation using GUI
- Database Creation using T-SQL scripts
- DB Design using Files and File Groups
- File locations and Size parameters
- DB Log Files and Placement
- Database Structure modifications
Stored Procedures and Benefits
- Why to use Stored Procedures?
- Types of Stored Procedures
- Use of Variables and parameters
- SCHEMABINDING and ENCRYPTION
- INPUT and OUTPUT parameters
- System defined Stored Procedures
- Dynamic SQL Queries and parameterization
SQL Server Tables & Queries
- SQL Server Database Tables
- Table creation using T-SQL Scripts
- Naming Conventions for Columns
- Single Row and Multi-Row Inserts
- Table Aliases, Column Aliases & Usage
- Table creation using Schemas
- Basic INSERT, UPDATE, DELETE
- SELECT queries and Schemas
User Defined Functions and Joins
- Where to use Functions?
- Scalar Valued Functions
- Types of Table Valued Functions
- SCHEMABINDING and ENCRYPTION
- System Functions and Joins
- Date Functions, Time Functions
- String and Operational Functions
- ROW_COUNT, GROUPING Functions
Data Validations and Constraints
- Need for Data Integrity
- Table creation using Constraints
- NULL and IDENTITY properties
- UNIQUE KEY Constraint and NOT NULL
- PRIMARY KEY Constraint & Usage
- CHECK and DEFAULT Constraints
- Naming Composite Primary Keys
- Disabling Constraints & Other Options
Triggers and Memory Limitations
- Why to use Triggers?
- DML Triggers and Performance impact
- INSERTED and DELETED memory tables
- Triggers for Audit operations
- Triggers for Data Sampling
- Database Triggers and usage
- Server Triggers and limitations
- Triggers for Bulk Operations
JOINS and Sub Queries
- Variants of SELECT statement
- Use of WHERE, IN and BETWEEN
- ORDER BY, GROUPING, HAVING
- Use of Predicates and TOP
- Sub Queries and Nested Queries
- DELETE FROM SELECT query variants
- INSERT INTO … SELECT statement
Cursor Variables and Programming
- Benefits of Cursor variables
- Cursor declaration and life cycle
- STATIC and DYNAMIC cursors
- SCROLL and FORWARD_ONLY cursors
- LOCAL and GLOBAL cursors
- KEYSET Cursors and usage
- Embedding Cursors in SPs and UDFs
Views, Joins and Queries
- Benefit of Views in SQL Database
- Views on Tables and Views
- SCHEMABINDING and ENCRYPTION
- Issues with Views and ALTER TABLE
- Common System Views and Metadata
- Common Dynamic Management views
- Working with JOINS inside views
Transactions & Realworld Scenario
- Transactions and Use
- ACID Properties and Scope
- EXPLICIT Transaction types
- IMPLICIT Transactions and options
- AUTOCOMMIT Transaction and usage
- SAVEPOINT and Query Blocking
- Complex SPs with Transactions
 |
SQL School |  |
contact@sqlschool.com |  |
USA: +1 (510) 400 – 4845 | |
India: +91 (0) 9666440801 / +91 (040) 64577244 |
Post by SQL School.
