Exam 70-464: Developing Microsoft SQL Server Databases Practice Test
Exam 70-464 practice centers on developing SQL Server database solutions with Transact-SQL and database development tools. Its practice questions sample work such as shaping queries, defining functions, changing table schemas, managing transactions, and preparing database objects for controlled deployment. These tasks call for choosing SQL constructs that preserve correct results and support maintainable database changes.
Exam overview
About the Exam 70-464: Developing Microsoft SQL Server Databases Exam
SQL Server development involves decisions about how queries express relationships, summarize data, and calculate values across ordered rows. The practice bank includes examples involving grouping and aggregation, existence checks, window functions, and string handling. It also samples database programming choices, including selecting a function type for reusable logic and retrieving an identity value without confusing it with values created by triggers. Schema and deployment work appears through table alteration, sequence use, transaction error handling, and database project packaging. Working through these situations can help sharpen the ability to match a database requirement to an appropriate T-SQL feature, reason about scope and side effects, and distinguish query behavior from object lifecycle and deployment concerns.
Sample Practice Questions
Review a selection from this exam's published US Practice Tests practice bank.
A developer wants to make a multi-statement change and ensure that an error causes the transaction to be rolled back rather than accidentally committed. Which setting can make a transaction uncommittable after certain runtime errors?
Show answer
Correct answer: ASET XACT_ABORT ON causes many runtime errors to terminate and roll back the current transaction, helping prevent partially applied transactional work. It does not replace appropriate TRY/CATCH and transaction handling in every scenario.
A database developer needs a reusable query that returns the same result for the same input values and performs no data modification. Which function type is generally appropriate?
Show answer
Correct answer: AA scalar-valued user-defined function can accept inputs and return a single value, making it suitable for reusable calculations that do not modify database state. Triggers respond to data changes, jobs schedule tasks, and roles group permissions.
A database project needs to keep object definitions in source control and apply schema changes through repeatable deployments. Which artifact is commonly used by SQL Server database tooling to package a compiled database model?
Show answer
Correct answer: BA DACPAC packages a database schema model and is commonly used by database deployment tools to compare and publish schema changes. A BACPAC is primarily a schema-and-data export package.
A query performs a calculation over rows ordered within a partition, and the result should use the previous row's value without a self-join. Which function family is intended for this kind of access?
Show answer
Correct answer: ALAG and LEAD are window functions that access a value from a preceding or following row in the specified window ordering. Aggregate functions summarize values, while the other functions address null handling or string searching.
A query needs to include rows from one table only when a related row exists in another table, and it does not need columns from the related table. Which construct is designed to express this test without multiplying results when multiple related rows exist?
Show answer
Correct answer: AEXISTS tests whether the correlated subquery returns at least one row. It preserves each qualifying outer row once, even if several matching inner rows exist. A join can multiply outer rows when the relationship is one-to-many.
A developer needs to assign a unique, increasing number to rows across multiple tables while allowing values to be requested independently of an insert. Which SQL Server object is designed for this?
Show answer
Correct answer: AA sequence is a schema-bound object that generates numeric values independently of a particular table and can be referenced by multiple tables or statements.
A database developer needs to split a delimited string into rows using a built-in function available in supported SQL Server versions. Which function is intended for this purpose?
Show answer
Correct answer: ASTRING_SPLIT converts a delimited string into a table of substrings. Availability and optional ordering behavior depend on SQL Server version and compatibility level. STRING_AGG combines values, while the other functions serve different string-related purposes.
How to use this practice test
The free practice session draws up to 20 random questions from the 50-question bank. Answer each scenario by identifying the SQL Server feature or T-SQL construct that best fits the stated requirement. Results appear after the session, giving you a chance to review which database concepts need closer attention.
Preparation tips
- Compare EXISTS with joins when the requirement is to test for a related row without returning its columns or multiplying results.
- Practice choosing among ranking, aggregate, and offset window functions, especially when calculations depend on row order within a partition.
- Review transaction error behavior, including how XACT_ABORT can affect whether a transaction remains committable after a runtime error.
- Distinguish identity columns from sequences, and compare scope_identity with other identity retrieval functions when triggers are present.
- Work through database project deployment concepts, including how a compiled database model is packaged for repeatable schema deployment.