• 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.