Developing Microsoft SQL Server Databases (MS-20464)
When you subscribe, you get:
Master Subscription plan gives you access to this course and over 1210 other popular courses
Now Only $2,999.00 Regular Price $3,499.00
Now Only $2,999.00 Regular Price $3,499.00/ Year
If you enroll in this course without the Master Subscription plan, you receive a Free Official Exam Voucher (excluding purchases using Training Vouchers / SATV) for the 70-464 Exam. This course does not include Exam Voucher if enrolled within the Master Subscription, however, you can request to purchase the Official Exam Voucher separately.
About this course:
This instructor-led, 5-day course introduces students to the SQL Server 2014 along with the explanations of logical table design, indexing and query plans. The course will also discuss about the development of database objects such as views and stored procedures, besides the parameters and functions.
Different common traits of procedure coding like indexes, concurrency, error handling and triggers will also be covered. Do see that the course is primarily designed for learners interested in SQL Server 2012 or SQL Server 2014. The course content will examine the updated features of SQL Server 2014 along with the crucial capabilities across the SQL Server data platform.
This Microsoft Training focused course also helps the students in their preparation for Microsoft 70-464: Developing Microsoft SQL Server Databases certification exam.
After the course completion, students should be able to:
Examine the complete SQL Server platform along with its important tools. The details will also cover editions, versions, fundamentals of network listeners, and understanding of services and service accounts.
Inquire the relevant data types which are to be used when designing tables, perform data conversion between data types, and develop alias data types.
Stay familiar with the great design trends regarding the SQL Server tables and should be able to develop tables through T-SQL. (Note: partitioned tables are not included in the content).
Implement important things including PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK and UNIQUE constraints, and examine the cascading FOREIGN KEY limitations.
Come up with suitable single column and composite indexes strategies.
Develop tables in the form of heaps with clustered indexes. Furthermore, do consider the design of a table and recommend a more relevant structure.
Go through and analyze the details of common elements taken from execution plans.
Create effectual non-clustered indexes.
Create and implement views
Develop and implement stored procedures.
Understand and work with table types, table valued parameters and incorporate the MERGE statement to develop stored procedures for updating data warehouses.
Develop and implement operations, both scalar and table-valued. (Also explain where both can result in some performance issues).
Conduct a fundamental inquiry of a deadlock situation and understand how the transaction isolation levels influences the application concurrency.
Utilize both traditional T-SQL error handling code and structured exception handling.
Develop and implement DML triggers
Understand the relevant uses for SQL CLR integration and execute an existing .NET assembly within SQL Server.
Store the schemas and XML data in SQL Server.
Facilitate simple queries on XML data in SQL Server.
The intended audience of this course is IT Professionals, specifically those who aim to acquire expertise on SQL Server 2014 product features and technologies for implementing a database.
While the secondary audience is professionals, mainly developers coming from other product platforms or previous versions of SQL Server looking to grow their skillset in the implementation of a SQL Server 2014 database.
This course mandates the learners to meet the following prerequisites:
Understanding about the writing of T-SQL queries
Knowledge of simple relational database concepts
Frequently Asked Questions
1. How will the Developing Microsoft SQL Server Databases (MS-20464) certification training benefit me in my current job role?
This training will help you in your Job’s current role by learning and getting hands on experience in the following areas:
- How to incorporate data files into databases.
- How to implement managed code in SQL Server.
- Learn how to deploy in-memory tables.
- Learn how to implement triggers to respond to data manipulation
- Designing and implementation of UDF (User Defined Functions)
- Writing and designing Stored Procedures.
- What are views and what importance does it have in SQL Server 2014
- Learn about Advanced Indexes and Implement them in your live databases to increase performance of your data
- Ensure your data’s Integrity through constraints.
2. What is the exam criteria for the course?
Following is the exam criteria/format of this course:
- Implement database objects (30-35%) topics from this area which includes scenario- based questions, MCQ’s, writing TSQL statements and Drag and Drop question and answers.
- 15 to 20% questions will be based on implementing programming objects such as Stored Procedures, User Defined Functions creation, create and alter views, implement error handling including Try…. Catch and, configure appropriate connection settings Expect scenario-based questions, MCQ’s and completing T-SQL Queries.
- Designing database object questions will be based on 25 to 30% weightage but, may vary. Designing of Tables, concurrency, indexes, data integrity and explicit and implicit transactions are some topics which you should know completely.
- From the last section, Optimize and trouble shoot queries questions percentage will be ranged from 25 to 30% but, it may vary as per Microsoft’s policy. Most important topics to cover are: Tuning and Optimizing Queries, Optimization of Indexes, Capture and Analyze execution plans, collect performance and system information.
3. What you will learn?
- SQL Server Database Introduction
- Designing and Implementing Tables
- Ensuring Data Integrity through constraints.
- Introduction to Indexes
- Advanced Indexing
- In-Memory Database capabilities
- Designing and Implementing Views
- Designing and Implementing Stored Procedures.
- Designing and Implementing User-Defined Functions
- Responding to Data Manipulation via Triggers
- Using In-Memory tables
- Implementing Managed Code in SQL Server.
- Incorporating Data Files into Databases.
4. Do I have to prepare anything before attending the Developing Microsoft SQL Server Databases (MS-20464) certification training course?
Before attending this training make sure you are well versed with the following:
- How to write Transact-SQL Queries
- What are Relational Databases.
5. What type of object/s cannot be created using Transact-SQL code?
A User-Defined datatype cannot be created by using Transact-SQL code. It can only be created by using .Net managed code. All the other object types can be either be created by using .Net managed code or by using T-SQL code.
6. What is the purpose of User Defined Data Type in SQL Server Managed Code?
The purpose of creating User-Defined Data type in SQL Server Managed Code is to extend the data type system that is available from within SQL Server. There is no equivalent method of doing this in Transact-SQL. User-Defined Data Types enable you to determine not only what data is stored, but also how the data type behaves.
7. What are Memory Optimized Tables in SQL Server 2014?
Memory Optimized Tables are defined as:
- C structs, compiled into DLLs, and loaded into memory
- Can be persisted as FILESTREAM data, or nondurable.
- Do not apply any locking semantics.
- Can be indexed by using Hash Indexes.
- Can be queries by using Transact-SQL.
- Can co-exist with disk-based tables.
- Cannot include some data types, including text, image, and nvarchar(max).
- Do not support identity columns or foreign key constraints.
8. Are there any alternatives to Triggers? If Yes, what are they?
Yes, there are various alternatives to triggers which are used by developer in situations where Triggers are not preferable. Following are some alternatives:
- Constraints for checking values
- Use defaults for values not supplied during inserts.
- Foreign Key constraints to check referential integrity.
- Computed and persisted computed columns
- Indexed views for precalculating aggregates.
Practice Certification Exam
|Subjects||IT Ops & Management|
|Learning Style||Virtual Classroom|
|Course Duration||5 Days|
|VPA Discount||VPA Discount|