Study material for exam 70-464 Developing Microsoft SQL Server Databases

By: Daniel Calbimonte   |   Updated: 2016-12-23   |   Comments (4)   |   Related: More > Professional Development Certifications

Problem

I would like to know what books, videos, links and advice you would recommend to pass the Developing Microsoft SQL Server Databases (Exam 70-464)?

Solution

In this tip, we will provide links to articles, books and other training material to help you pass the 70-462 exam.  Also check out these other tips related to these exams: 70-463, 70-466 and 70-462.

What is the Microsoft 70-464 certification exam?

This is exam is related to T-SQL related to advanced features about this query language.

Is this exam oriented to developers?

It is not only developers, it also includes useful topics for DBAs too.

What are the requirements to take this exam?

Anyone can take this exam. However, it is recommended for professionals with 1 or more years of experience in SQL Server.

Can I take this exam directly or there is previous exam?

You can take it directly, but we recommend taking 70-461 first. Specially if you are not an expert.

What books do you recommend for this exam?

Unfortunately, there are no Microsoft Training Kit books for this exam. However, here are some books that might be useful:

1. T-SQL Querying (Developer Reference) this is my favorite book for this exam. It will help you a lot with the 70-461 and 70-464 exam.
2. Microsoft SQL Server 2014 Query Tuning & Optimization is a great book for the last topic of the exam about Optimizing and Troubleshooting Queries.
3. Hexa Marathon Guide: MCSA/MCSE 70-464 is a book with some questions to practice for the exam. We recommend this book when you are almost ready to take the exam.
4. Microsoft SQL Server 2012 Internals (Developer Reference) is a great book, but it is for advanced users. I recommend you read this one after the other 3. In many chapters it exceeds the knowledge required to pass the exam, but it does not cover all the exam topics.
5. SQL Server Execution Plans, Second Edition by Grant Fritchey is the best book to cover the execution plan topic. Reading this book exceeds the knowledge required for this topic.
6. SQL Server Concurrency: Locking, Blocking and Row Versioning will help you in the topic related to design for concurrency. It exceeds the knowledge required for this topic.

Which courses do you recommend for this exam?

The following courses will help you pass the exam:

• Course 20464D: Developing Microsoft SQL Server 2014 Databases
• MVA: Developing Microsoft SQL Server Databases
• SQL Server - TSQL (for CLR, Transactions and locks)
• Practice Lab for Microsoft 70-464
• SQL Server: Transact-SQL Common Table Expressions (CTE)
• SQL Server: Common Query Tuning Problems and Solutions
• Microsoft MCSE SQL Server 2012 70-464
• COURSE OUTLINE: SQL SERVER 2014 INTERNALS
• Advanced T-SQL Querying, Programming and Tuning for SQL Server 2012, 2014 and 2016

Which simulation exams do you recommend?

• 70-464: Developing Microsoft SQL Server 2012 Databases Microsoft Official Practice Test
• Microsoft Practice Exam for 70-464 MSCert: Developing Microsoft SQL Server Databases1
• Practice Exam for Microsoft 70-461
• Microsoft Practice Exam for 70-461 MSCert: Querying Microsoft SQL Server 2012/2014

Here are other recommended links to read?

Implement database objects

• Create and alter tables
  • CREATE TABLE (Transact-SQL)
  • ALTER TABLE (Transact-SQL)
  • Differences between SQL Server temporary tables and table variables
  • Use Table-Valued Parameters (Database Engine)
  • Temporary Tables in SQL Server
  • Reasons to Avoid Triggers
  • OUTPUT Clause (Transact-SQL)
  • SQL Server Trigger Alternatives with the OUTPUT Clause
  • SQL Strategies for ‘Versioned’ Data
  • Yet Another SQL Strategy for Versioned Data
  • SQL Server Performance Tuning : Table Variable Vs. Temporary Tables
  • Specify Computed Columns in a Table
  • How to Partition an existing SQL Server Table
  • SQL Server Database Partitioning Myths and Truths
  • SQL Server Partitioned Tables with Multiple Filegroups for High Availability
  • Create a Database Schema
  • Ownership and User-Schema Separation in SQL Server
  • Changing SQL Server Collation After Installation
  • Identify SQL Server Instance and Database Collation Using T-SQL and SSMS
  • How to Change the COLLATION of a SQL Server Column
  • Partitioned Tables and Indexes
  • Tutorials DBA Dev BI Categories Events Columnstore index feature in SQL Server 2012
  • Identify the best tables for SQL Server 2016 Columnstore Index Migration
  • SQL Server Column Store Index Performance
  • Use Sparse Columns
  • SQL Server Sparse Columns Identifying Columns For Conversion
• Implement programming objects
  • SQL SERVER – Example of DDL, DML, DCL and TCL Commands
  • Understanding GRANT, DENY, and REVOKE in SQL Server
  • SQL SERVER – Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video
  • Troubleshoot Connecting to the SQL Server Database Engine
  • Troubleshooting SQL Server Connection Issues
  • Granting permission with the EXECUTE AS command in SQL Server
  • EXECUTE AS Clause (Transact-SQL)
  • How to configure SSL encryption in SQL Server
  • SQL Server 2012 Contained Database Feature
  • SQL Server Database Guest User Account
  • SQL Server Security Tips
  • Understanding SQL Server fixed server roles
  • Ownership chaining in SQL Server security feature or security risk
• Design the locking granularity level
  • SQL Server Concurrency: Locking, Blocking and Row Versioning
  • Deadlocking
  • Detecting and Ending Deadlocks)
  • SET TRANSACTION ISOLATION LEVEL
  • Demonstrations of Transaction Isolation Levels in SQL Server
  • SQL Server, Locks Object
  • SQL Server DBA Concurrency and Locking Interview Questions
  • Comparison of SQL Server Serializable and Snapshot isolation levels
  • SQL Server Locking and Blocking Tips
• Implement indexes
  • SQL Server Indexing Tips
  • Stairway to SQL Server Indexes
  • Clustered and Nonclustered Indexes Described
  • Querying SQL Server Index Statistics
  • Can I Use Statistics to Design Indexes? (Dear SQL DBA Episode 18)
• Implement data types
  • Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server
  • Auto generated SQL Server keys with the uniqueidentifier or IDENTITY
  • XML Data Type and Columns (SQL Server)
  • SQL Server Spatial Data Storage Tips
  • SQL CLR Data Types and Performance
  • CAST and CONVERT (Transact-SQL)
  • Data Types (Transact-SQL)
• Create and modify constraints
  • SQL Server Constraints Tips
  • table_constraint (Transact-SQL)
  • Blitz Result: Foreign Keys or Check Constraints Not Trusted
  • Controlling Constraint Checking by Bulk Import Operations

Implement programming objects

• Design and implement stored procedures
  • SQL Server Stored Procedure Tutorial
  • CREATE PROCEDURE (Transact-SQL)
  • sys.dm_os_volume_stats (Transact-SQL)
  • Exporting to Excel Using a CLR Stored Procedure
  • Using Table Valued Parameters (TVP) in SQL Server
• Design T-SQL table-valued and scalar functions
  • CREATE FUNCTION (Transact-SQL)
  • Increase your SQL Server performance by replacing cursors with set operations
  • SQL Server Cursor Alternatives
  • Replacing Cursors with Set-Based SQL Queries – Part 1
  • Deterministic and Nondeterministic Functions
• Create, use, and alter user-defined functions (UDFs)
  • CREATE FUNCTION (Transact-SQL)
  • SQL Server Functions - User Defined UDF Tips
  • SQL Server CROSS APPLY and OUTER APPLY
  • Introduction to SQL Server CLR table valued functions
  • CLR String Sort Function in SQL Server 2005
  • Create CLR Functions
• Create and alter views
  • How to Partition an existing SQL Server Table
  • SQL Server Database Partitioning Myths and Truths
  • Create Partitioned Tables and Indexes
  • Create Indexed Views

Design database objects

• Design tables
  • Design Patterns for Relational Databases
  • 11 important database designing rules which I follow
  • Database Normalization Basics
  • Database normalization
  • What does BEGIN TRAN, ROLLBACK TRAN, and COMMIT TRAN mean?
  • Transaction Statements (Transact-SQL)
  • Auto generated SQL Server keys with the uniqueidentifier or IDENTITY
  • Clustered Indexes Based Upon GUIDs
  • Temporary Tables and Table Variables
  • Simplify Large Queries with Temporary Tables, Table Variables and CTEs
  • SQL Server Encryption
  • Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server
  • The Art of SQL Server FILESTREAM by Jacob Sebastian and Sven Aelterman
  • FILESTREAM (SQL Server)
  • FileTables (SQL Server)
  • FileTable Feature in SQL Server 2012
  • Getting started with SQL Server 2014 In-Memory OLTP
  • In-Memory OLTP (In-Memory Optimization)
• Design for concurrency
  • Microsoft SQL Server Coding for Maximizing Concurrency in Applications
  • SQL Server Concurrency: Locking, Blocking and Row Versioning
  • Understanding how SQL Server handles Service Isolation)
  • Improve SQL Server Efficiency by Switching to INSTEAD OF Triggers
  • SQL Server Triggers Tips
  • SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• Design indexes
  • Stairway to SQL Server Indexes
  • SQL Server Indexing Tips
  • What is a SARG exactly? Training classes and knowledge retention!
  • XML Indexes (SQL Server)
  • Investigating the new Spatial Types in SQL Server
  • Spatial Indexes Overview
  • CREATE INDEX (Transact-SQL)
• Design data integrity
  • Primary and Foreign Key Constraints
  • Unique Constraints and Check Constraints
  • SQL SERVER – Difference Between Candidate Keys and Primary Key In Simple Words
  • SQL NULL Values
  • XML Schema Collections (SQL Server)
• Design for implicit and explicit transactions
  • Transaction Statements (Transact-SQL)
  • What is a transaction?
  • BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)
  • TRY...CATCH in SQL Server 2005 An Easier Approach to Rolling Back Transactions in the Face of an Error

Optimize and troubleshoot queries

• Optimize and tune queries
  • Data Conversion in SQL Server
  • SQL Server Execution Plans
  Graphical Query Plan Tutorial
  • SQL Server JOIN Hints
  • Tutorial: Database Engine Tuning Advisor
  • SQL Server Query Plan Analysis: The 5 Culprits That Cause 95% of Your Performance Headaches
  • Database Engine Tuning Advisor
  • dta Utility
  • Using a CTE with a T-SQL Pivot Statement
  • WITH common_table_expression (Transact-SQL)
  • SQL Server Queries With Hints
  • Query Hints (Transact-SQL)
  • SQL Server Semantic Search to Find Text in External Files
  • How To: Optimize SQL Queries
• Troubleshoot and resolve performance problems
  • Monitor Resource Usage (System Monitor)
  • Correlate a Trace with Windows Performance Log Data (SQL Server Profiler)
• Optimize indexes
  • Create Indexes with Included Columns
  • CREATE FULLTEXT INDEX (Transact-SQL)
  • Stairway to SQL Server Indexes
  • Rebuilding SQL Server indexes using the ONLINE option
• Capture and analyze execution plans
  • SQL Server Execution Plans
  • Display and Save Execution Plans
  • When to Breakdown Complex Queries
  • Huge transaction in Sql Server, are there any problems?
• Collect performance and system information
  • Performance Tuning with SQL Server Dynamic Management Views
  • SQL Server Database Engine Tuning Advisor for Performance Tuning
  • SQL Server Extended Events Sessions
  • Extended Events
  • Open Activity Monitor (SQL Server Management Studio)
  • Performance Analysis Using SQL Server 2008 Activity Monitor Tool
  • Getting started with SQL Server 2014 In-Memory OLTP

Next Steps

• Get ready and start studying!

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips