Microsoft SQL Server Certification Exam

Microsoft SQL Server T-SQL Certification Training: 70-461

About this course

This 3.5 Weeks instructor led course provides students with the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2014. This course is the foundation for all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence.


Microsoft SQL Server T-SQL Certification : Course Details

Required Skills:

Possess introductory IT Skills through existing knowledge or optional MTA certification


Course Content:

I) Create Database Objects(20-25%)

Create and alter tables using T-SQL syntax (simple statements)

Create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE

Create and alter views (simple statements)

Create indexed views; create views without using the built in tools; CREATE, ALTER, DROP

Design views

Ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications

Create and modify constraints (simple statements)

Create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints

Create and alter DML triggers

Inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers


II) Work with Data(25-30%)

Query data by using SELECT statements

Use the ranking function to select top(X) rows for multiple categories in a single query; write and perform queries efficiently using the new (SQL 2005/8->) code items such as synonyms, and joins (except, intersect); implement logic which uses dynamic SQL and system metadata; write efficient, technically complex SQL queries, including all types of joins versus the use of derived tables; determine what code may or may not execute based on the tables provided; given a table with constraints, determine which statement set would load a table; use and understand different data access technologies; case versus isnull versus coalesce

Implement sub-queries

Identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement

Implement data types

Use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use what data type for columns

Implement aggregate queries

New analytic functions; grouping sets; spatial aggregates; apply ranking functions

Query and manage XML data

Understand XML datatypes and their schemas and interop w/, limitations and restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export XML; XML indexing


III) Modify Data(20-25%)

Create and alter stored procedures (simple statements)

Write a stored procedure to meet a given set of requirements; branching logic; create stored procedures and other programmatic objects; techniques for developing stored procedures; different types of storeproc result; create stored procedure for data access layer; program stored procedures, triggers, functions with T-SQL

Modify data by using INSERT, UPDATE, and DELETE statements

Given a set of code with defaults, constraints, and triggers, determine the output of a set of DDL; know which SQL statements are best to solve common requirements; use output statement

Combine datasets

Difference between UNION and UNION all; case versus isnull versus coalesce; modify data by using MERGE statements

Work with functions

Understand deterministic, non-deterministic functions; scalar and table values; apply built-in scalar functions; create and alter user-defined functions (UDFs)


IV) Troubleshoot & Optimize(25-30%)

Optimize queries

Understand statistics; read query plans; plan guides; DMVs; hints; statistics IO; dynamic vs. parameterized queries; describe the different join types (HASH, MERGE, LOOP) and describe the scenarios they would be used in

Manage transactions

Mark a transaction; understand begin tran, commit, and rollback; implicit vs explicit transactions; isolation levels; scope and type of locks; trancount

Evaluate the use of row-based operations vs. set-based operations

When to use cursors; impact of scalar UDFs; combine multiple DML operations

Implement error handling

Implement try/catch/throw; use set based rather than row based logic; transaction management



Appendix: Improving Query Performance

Passing Exam 70-461 counts as credit toward the following certifications:


For course details and registration, please visit: SQL Server & T-SQL Training Course

Microsoft Certification Exam - for SQL Server 2014. SQL School Training Institute provides In-DEPTH Certification Training & Guidance for Microsoft 70-461 Exam. 100% Guaranteed Results.

Source of Information for Certifications: Source:


SQL Server Training, SQL DBA Training, MSBI Certification Trainings & Support. Register Today







Follow us on Facebook         Get Technology Updates on LinkedIn         Track Technical Events on Google+


SQL School Phone IndiaIndia: +91 (0)9666440801
SQL School Phone USAUSA: +1(510) 400-4845


Microsoft Partner (ID# 4338151)

100% Real-Time and Practical

ISO 9001:2008 Certified

24/7 LIVE Server Access

Real Time Projects

Theory Material in Advance

Weekly Mock Interviews

Placement Assistance

Job Support


#108/2RT, Road #1, Beside SR Nagar Bus Stop, Ameerpet, Hyd-38

India: 0091 (0)9666440801


Sai Anu Avenue, Street #3,
Patrika Nagar, HITEC City, Hyd - 81

India: 0091 (0)9666440801











©2008-2017 SQL School, Regd®: Sequelgate (I) Technologies Pvt. Ltd™

Sql School is an Authorized Microsoft Training Partner (ID# 4338151)

SequelGate (I) Technologies Pvt.Ltd. is rated 4.8/5 by based on 107 reviews