Enroll Now
Online Training
Corporate Training
Classroom
Microsoft SQL Server Developer
Training
SQL Server 2012/2016 Programming (SQL DEV) course carefully designed with in-
depth fundamentals and Database Design techniques required for aspiring Microsoft SQL
professionals. All Sessions are completely practical and real time.
Students learn the technical skills to write basic Transact-SQL queries for
Microsoft SQL Server 2012/2014/2016. This course is the foundation for all SQL
Server-related disciplines; namely, Database Administration, Database
Development and Business Intelligence.
COURSE OBJECTIVES:
After completing this course, students will be able to:Write SELECT
queries Query multiple tablesSort and filter data Describe the use of data types
in SQL Server Modify data using Transact-SQL Use built-in functions Group and
aggregate data Use subqueries Use table expressions• Use set operators Use
window ranking, offset and aggregate functions Implement pivoting and
grouping sets Execute stored procedures Program with T-SQL Implement error
handling Implement transactions
MS SQL Server is one of the industry’s leading and popular Microsoft data platforms
that can be utilized to develop mission critical apps for online transaction processing
(OLTP). Scalability, flexibility, high performance and availability are some of the best
characteristics of this DB. MS SQL Server is the least vulnerable database that can
provide advanced data security and protects data at rest and in motion.
The practical sessions throughout the course will help you to enhance your technical
skills and confidence. Their connections to the job world will surely help you achieve
your dream job. So start putting your sincere efforts into practice and grab the
wonderful opportunities.
TARGET AUDIENCE:
This course is intended for Database Administrators, Database Developers, and
Business Intelligence professionals. The course will very likely be well attended
by SQL power users who aren’t necessarily database-focused or plan on taking
the exam; namely, report writers, business analysts and client application
developers.
Prerequisites for SQL Server developer
- There is no pre requisites to learn SQL Server developer.
- If you are already have basic idea on SQL and RDBMS, that will be quite easy
for you to grasp the concepts. Otherwise, experts are here to help you from
the basics of SQL Server DB.
SQL Server Introduction & Installation
- SQL Server Configuration & Use
- SQL Server 2012 Installation
- Services and Firewall Configuration
- Authentication Modes & Usage
- Dedicated and Silent Installations
- SQL Server Installtion Features
- Using Management Studio (SSMS)
- Using Configuration Tools & SMO
Indexes and Query Tuning
- Table Indexes and Column Indexes
- Clustered and NonClustered types
- SORT_IN_TEMPDB and ONLINE options
- FILLFACTOR and PAD_INDEX options
- Execution Plans and Estimation Rules
- Indexed Queries and Performance
- Unique Indexes and Uses
- Working with Indexed Views
SQL Server 2012 Database Design
- SQL Database Architecture
- Database Creation using GUI
- Database Creation using T-SQL scripts
- DB Design using Files and File Groups
- File Locations and Size Parameters
- Dedicated Log Files and Locations
- Database Structure modifications
Stored Procedures and Benifits
- Why to use Stored Procedures?
- Types of Stored Procedures
- Use of Variables and Prameters
- SCHEMABINDING and ENCRYPTION
- INPUT and OUTPUT Parameters
- System level Stored Procedures
- Dynamic SQL and Parametrization
SQL Server Tables & Data
- 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 Usage
- Where to use 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
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
Basic Queries and JOIN 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
- Benifits 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 cursros in SPs and UDFs
Views and Row Data Security
- Benifit 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
Transaction Types and Realworld Scenario
- ACID Properties and Scope
- EXPLICIT Transaction Types
- IMPLICIT Transactions and options
- AUTOCOMMIT Transaction and Usage
- SAVEPOINT and Query Blocking
- Complex SPs with Transactions
- A Realworld Scenario and Code