Individuals

Learn how you can get certified, advance your career, and get promoted with our self-paced subscription of over 900 self-paced courses.

Learn More

Certification Exam Prep Questions For
Developing Microsoft SQL Databases (MS-20762)

QuickStart is now offering you assessment questions for Developing Microsoft SQL Databases (MS-20762). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice assessment to better aid in certification. 100% of the questions are real questions from a recent version of the test you will take for Developing Microsoft SQL Databases (MS-20762).

Assessment Modules:

1. An Introduction to Database Development

2. Designing and implementing Tables

3. Advanced Table Designs

4. Ensuring Data Integrity Through Constraints:

5. Introduction to Indexes

6. Designing Optimized Index Strategies

7. ColumnStore Indexes

8. Designing and Implementing Views

9. Designing and Implementing Stored Procedures

10. Designing and Implementing User Defined Functions

11. Responding to Data Manipulation Via Triggers

12. Responding to Data Manipulation Via Triggers

13. Using Window Ranking, Offset and Aggregate Functions

14. Storing and Querying XML Data in SQL Server

15. Storing and Querying Spatial Data in SQL Server

16. Storing and Querying BLOBs and Text Documents in SQL Server

17. SQL SERVER Concurrency

18. Performance and Monitoring

Module 1

An Introduction to Database Development

1-A

How many types of SQL Server Components are there?

A. 4: Incorrect.

B. 3: Correct!

C. 6: Incorrect.

D. None of the above: Incorrect.

1-B

How many types of SQL Server Tools are available in 2018?

A. 5: Correct!

B. 4: Incorrect.

C. 2: Incorrect.

D. None of the above: Incorrect.

This component is used to orchestrate the movement of data between SQL Server components and other external systems. Traditionally used for extract, transform and load (ETL) operations. Choose the option below which best describes the description above

A. SQL Server Database Engine: Incorrect.

B. Integration Services(SSIS): Correct!

C. Reporting Services(SSRS): Incorrect.

D. None of the above.: Incorrect.

How many number of instances of SQL Server Database Engine can you install on a Single server?

A. 4: Incorrect.

B. 10: Incorrect.

C. N(Infinite): Correct!

D. None of the above: Incorrect.

Module 2

Designing and implementing Tables

1-A

How many types of Normalization Forms are there?

A. 3: Correct!

B. 2: Incorrect.

C. 5: Incorrect.

D. None of above: Incorrect.

1-B

This Normalization form eliminates repeating groups in individual tables. In Addition to this, it creates a separate table for each set of related data. Furthermore, It identifies each set of related data by using a primary key

Which Normalization Form is this?

A. FIRST NORMAL FORM: Correct!

B. SECOND NORMAL FORM: Incorrect.

C. THIRD NORMAL FORM: Incorrect.

D. None of the above: Incorrect.

Which of the following statements are true about Unique Identifiers?

A. Data type which is typically used for storing GUID values: Incorrect.

B. Common error is to store GUIDs as strings: Incorrect.

C. Storage capacity is essentially a 128-bit integer, but standard integer arithmetic is not supported.: Incorrect.

D. All the above: Correct!

3-A

Following are the steps when you are creating a schema except one:

A. Schemas are created using a Create Schema Command: Incorrect.

B. Schemas sometimes have owners during creation and sometimes not: Correct!

C. Objects contained within schemas have owners: Correct!

D. Objects and permissions are specified when the schema is created.: Incorrect.

3-B

Which of the following objects cannot be stored in a schema?

A. Table: Incorrect.

B. Function: Incorrect.

C. Database Role: Correct!

D. None of the above: Incorrect.

4-A

A primary key uniquely identifies each row within a table and candidate key could be used to identify a row. Which of the following is false about Candidate Key?

A. Must be Unique and can be Null: Incorrect.

B. Can involve multiple columns: Incorrect.

C. It can be changed anytime: Incorrect.

D. A and C: Correct!

4-B

Keys which are references between tables and can also be self-referenced. Also, multiple type of this keys can exist in one table. Because of these keys rows cannot be deleted or updated without cascading options.

Among the options given below, what type of key is it?

A. Primary Keys: Incorrect.

B. Foreign Keys: Correct!

C. Natural Keys: Incorrect.

D. All the above: Incorrect.

Module 3

Advanced Table Designs

You are partitioning data and using Partition function. Which of the following data types are not allowed in a partition function?

A. Text: Incorrect.

B. xml: Incorrect.

C. Image: Incorrect.

D. All the above: Correct!

Module 4

Ensuring Data Integrity Through Constraints:

How many types of Data Integrity are there?

A. 5: Incorrect.

B. 7: Incorrect.

C. 3: Correct!

D. None of the above: Incorrect.

1-A

This integrity defines the allowed values in columns:

A. Domain Integrity: Correct!

B. Entity Integrity: Incorrect.

C. All the above: Incorrect.

D. None of the above.: Incorrect.

1-B

This integrity defines relationships between tables

A. Domain Integrity: Incorrect.

B. Entity Integrity: Incorrect.

C. Referential Integrity: Correct!

D. None of the above: Incorrect.

1-C

Integrity in which primary key uniquely identifies each row within a table

A. Entity Integrity: Correct!

B. Referential Integrity: Incorrect.

C. Domain Integrity: Incorrect.

D. None of the above: Incorrect.

While implementing Domain Integrity, Default Constraints plays an important role. Which of the following aspects of Default Constraints are Essential?

A. Providing default values for columns: Incorrect.

B. If no column value is provided, it is used: Incorrect.

C. Production of Data compatibility with the data type for the column: Incorrect.

D. All the above: Correct!

Sequences are another way of creating values for insertion into a column as sequential numbers. Select from the options below which best describes about Sequences when implementing Referential and Entity Integrity.

A. Sequences are not tied to any specific table. You could use a single sequence to provide key values for a group of tables: Incorrect.

B. Sequences are cyclic. They can return to a low value when a specified value has been exceeded: Incorrect.

C. NEXT VALUE FOR clause is used for retrieving the values from sequences.: Incorrect.

D. All the above: Correct!

Module 5

Introduction to Indexes

Indexes in SQL Server plays very important role when it comes to performance and memory optimization. What does Index Depth Means? Choose all the correct options

A. Number of Levels within the index: Incorrect.

B. A measure of how many rows are returned compared to the total number of rows: Incorrect.

C. A and D: Correct!

D. Common misconception that indexes are deep.: Incorrect.

Maximum number of NonClustered Indexes a table can have?

A. Infinite: Incorrect.

B. 1: Incorrect.

C. 999: Correct!

D. None of the above: Incorrect.

SQL Server automatically creates statistics on Indexes as it needs to have knowledge of the layout of the data in a table or index before it optimizes and executes queries. Among the queries listed below which 2 queries can update statistics on a specific table and on a specific index?

A. EXEC sp_updatestats;: Incorrect.

B. UPDATE STATISTICS Production.Product;: Incorrect.

C. UPDATE STATISTICS Production.Product PK_Product_ProductID;: Incorrect.

D. B and C: Correct!

Module 6

Designing Optimized Index Strategies

There are many options we can select in Query Store. Among many of the options we have Operation Mode settings. What other options are available we can select? Choose the one which is incorrect:

A. OFF: Incorrect.

B. READ_ONLY: Incorrect.

C. READ_WRITE: Incorrect.

D. None of the above: Correct!

What type of different workload formats does Database Engine Tuning Advisor does not accept?

A. Plan Cache: Incorrect.

B. XML File: Incorrect.

C. SQL Profiler: Incorrect.

D. None of the above: Correct!

There are few type of Plans in SQL Server that is used to show the query performance. Among them one is Estimated Execution Plan and the other is Actual Execution Plan. From the description below choose all which describes both plans. You can also choose Neither, two or All as well.
• Creates a plan but does not execute the Query
• Estimated Number of Rows based on Statistics.
• Includes actual number of rows returned
• If different, statistics are out of date or missing.

A. Actual Execution Plan: Incorrect.

B. Estimated Execution Plan: Incorrect

C. None of the Above: Incorrect

D. All the above: Correct!

Module 7

ColumnStore Indexes

Which of the following Statements best describes, why Column Store Indexes Performs well?

A. Columns often store matching data—for example, a set of States enabling the database engine to compress the data better. This compression can reduce or eliminate any I/O bottlenecks in your system, while also reducing the memory footprint.: Incorrect.

B. High compression rates improve overall query performance because the results have a smaller in-memory footprint.: Incorrect.

C. Instead of processing individual rows, batch execution also improves query performance. This can typically be a performance improvement of around two to four times because processing is undertaken on multiple rows simultaneously.: Incorrect.

D. All the Above: Correct!

Below statements describes characteristics of NonClustered and Clustered ColumnStore Indexes. Choose all the options which are not true.

A. NonClustered ColumnStore Indexes contains some or all columns: Incorrect.

B. It is not necessary for Clustered ColumnStore Indexes to contain all columns: Correct!

C. NonClustered ColumnStore Indexes uses more space than just a row: Incorrect.

D. None of the above: Incorrect.

You have to create a ColumnStore in-memory table. Below is the query through which you can create one:

CREATE TABLE InMemoryAccount (accountkey int NOT NULL,
accountdescription nvarchar (50),
accounttype nvarchar (50),
unitsold int,
CONSTRAINT [PK_NC_InMemoryAccount] PRIMARY KEY NONCLUSTERED([accountkey] ASC),
INDEX CCI_InMemoryAccount CLUSTERED COLUMNSTORE)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

As the query is created now you want to make sure it works. For the query to work, which of the following is not mandatory, such that, without it the query will not work:

A. DURABILITY = SCHEMA_AND_DATA: Incorrect.

B. WITH (MEMORY_OPTIMIZED = ON: Incorrect

C. Memory Optimized FileGroup: Incorrect

D. unitsold int: Correct!

Module 8

Designing and Implementing Views

Part1:How many types of views are there in SQL Server?

A. 2: Correct!

B. 3: Incorrect.

C. 1: Incorrect.

D. 5: Incorrect.

Part2a: How many types of views are there in SQL Server?

A. System Catalog Views: Incorrect.

B. Dynamic Management Views: Incorrect.

C. Compatibility Views: Incorrect.

D. None of the above: Correct!

Part2b:How many types of User Defined views can you create in SQL Server?

A. Views or standard views: Incorrect.

B. Indexed views: Incorrect.

C. Partitioned views: Incorrect.

D. All of the above: Correct!

Part3a:How many types of system views are there in SQL Server?

A. 4: Correct!

B. 2: Incorrect.

C. 6: Incorrect.

D. 3: Incorrect.

Part3bViews that provide information about the state of the SQL Server Database Engine are called as?

A. Dynamic Management Views: Incorrect.

B. System Catalog Views: Correct!

C. Compatibility Views: Incorrect.

D. None of the above: Incorrect.

We can update the data through views, but we must ensure following conditions are met:

A. The view includes column from only one table and the columns directly reference the table columns (inclusive of Aggregations or computations): Incorrect.

B. The updates comply with the base table constraints and WITH CHECK option prevents data being inserted that does not comply with the view definition: Correct!

C. All the above: Incorrect.

D. None of the above: Incorrect.

A nested view has some advantages as well as disadvantages.

In Part1, below mentioned are advantages of nested views from which you have to choose all which are correct.

In Part2 choose all the answers which you think are incorrect about disadvantages of nested views

Part1: Advantages:

A. Once a view is written it cannot be used like a table: Correct!

B. Once a view is tested it can be tested again: Incorrect

C. When a view is documented it can also be used just like a table.: Incorrect

D. None of the above.: Incorrect

Part2: Disadvantages:

A. Can be/But not always face problems maintaining tangled code: Incorrect.

B. Difficulty in Debugging but, queries performs in an efficient manner: Incorrect.

C. None of the above: Incorrect.

D. All of the above: Correct!

Module 9

Designing and Implementing Stored Procedures:

Which of the following is true about Stored Procedures. Mark all options which are correct.

A. Can be created in managed code or Transact-SQL statement.: Correct!

B. Cannot have input parameters: Incorrect.

C. Cannot have output parameters: Incorrect.

D. All the above: Incorrect.

Parameter-Sniffing problem occurs when stored procedure is executed. SQL Server attempts to reuse query execution plans from one execution of a stored procedure to the next. Although this is mostly helpful in some circumstances when stored procedure uses different execution plan for different sets of parameters. But SQL Server provides 4 resolutions to these problems. Which of the following is not a solution for Parameter-Sniffing issue?

A. WITH RECOMPILE: Incorrect.

B. sp_recompile: Incorrect.

C. EXEC WITH RECOMPILE: Incorrect.

D. None of the above: Correct!

Module 10

Designing and Implementing User Defined Functions:

How many types of Functions are there in SQL Server?

A. 4: Correct!

B. 3: Incorrect.

C. 5: Incorrect.

D. 6: Incorrect.

Some functions are deterministic, some are Built-in functions, and some are nondeterministic. Given below explains what both of these functions are. Choose all which explains the correct definition of each function

A. Deterministic Functions: May return different results given a specific input: Incorrect.

B. Nondeterministic Functions: Always return the same result given the same input and the same database state): Incorrect.

C. Built-in Functions: Are always deterministic: Incorrect.

D. None of the above: Correct!

Which of the following statements are not correct about Inline Table-Valued Functions?

A. Can be seen as a parameterized view: Incorrect

B. Function has a body which have BEGIN AND END: Correct!

C. Returns a single result set.: Incorrect

D. None of the above.: Incorrect

Module 11

Responding to Data Manipulation Via Triggers

Part1: Which of the following statements about After Triggers are not true?

A. Fire after the evet t which they relate: Incorrect.

B. Are treated as part of the same transaction as the statement that triggered them: Incorrect.

C. Can roll back the statement that Triggered them (and any transaction of which that statement was part of): Incorrect.

D. None of the above: Correct!

Part2:Which of the following statements about Instead of triggers are true?

A. Make it possible to execute alternate code, unlike a BEFORE trigger in other database engines.: Incorrect.

B. Are often used to create updatable views with more than one base table.: Incorrect.

C. All the above: Correct!

D. None of the above: Incorrect.

Choose all the statements that are correct with regards to After Insert Triggers

A. The Inserted virtual table holds a copy of the rows which were previously inserted.: Incorrect.

B. When the table is updated, After Insert trigger fires.: Incorrect.

C. New rows are added to both the base table and the inserted virtual table when an After-Insert Trigger fires: Correct!

D. All of the above: Incorrect.

Triggers are useful in many situations and are sometimes necessary to handle complex logic. However, triggers are sometimes used in situations where alternatives might be preferable. Choose all the alternative approaches below that cannot be used instead of using Triggers:

A. Checking Values: Incorrect

B. Defaults: Incorrect

C. Foreign Keys: Incorrect

D. None of the above: Correct!

Module 12

Responding to Data Manipulation Via Triggers

If you want to convert your tables or any specific table to memory-optimized tables, Memory Optimization Advisor Performs this process in fixed number of steps. How many steps does it take to convert the table into memory-optimized table?

A. 4: Incorrect.

B. 3: Incorrect.

C. 6: Correct!

D. 9: Incorrect.

Choose all the statements below which are not true about natively compiled stored procedures.

A. Contain one atomic block and will always succeed: Incorrect.

B. Offer greater speed but less efficiency: Incorrect.

C. Cannot access memory optimized tables: Incorrect.

D. All of the above: Correct!

In order to create a natively compiled stored procedure certain steps are followed, one of the step from those steps are Creating Procedure Statement. Which of the following options you must use in your CREATE PROCEDURE STATEMENT?

A. NATIVE_COMPILATION: Incorrect

B. SCHEMABINDING: Incorrect

C. EXECUTE AS: Incorrect

D. All the Above: Correct!

Module 13

Implementing Managed Code in SQL Server

Which of the following objects of SQL server cannot be used in SQL Server?

A. User-Defined functions (scalar and table-valued): Incorrect.

B. Stored Procedures: Incorrect.

C. Triggers (DML and DDL): Incorrect.

D. None of the above: Correct!

When considering using managed code in SQL server, which of the following factors are important?.

A. Potability: Incorrect.

B. Maintainability: Incorrect.

C. Using Three-Tier Architecture (Database, Mid and Presentation Tier): Incorrect.

D. All of the above: Correct!

SQL Server Data tools was first introduced inn SQL Server 2012.It is also integrated into Visual Studio. Main purpose of the SSDT is to develop, debug and refactor database code in addition to developing transact-SQL and CLR managed code. SSDT provides various templates for different SQL Server objects, some of them are listed below. Only one of the object is not Which is?

A. Aggregates: Incorrect

B. Stored Procedures: Incorrect

C. Table-Valued Expressions: Incorrect

D. None of the Above: Correct!

Lab Access	No
Learning Style	Self-Paced Learning
Learning Type	Practice Exam
Difficulty	Beginner
Course Duration	1 Hour
Language	English

By Roles or Function

By Technologies

Individuals

Teams

Sales

Support

Other

Certification Exam Prep Questions For Developing Microsoft SQL Databases (MS-20762)

Module 1

An Introduction to Database Development

Module 2

Designing and implementing Tables

Module 3

Advanced Table Designs

Module 4

Ensuring Data Integrity Through Constraints:

Module 5

Introduction to Indexes

Module 6

Designing Optimized Index Strategies

Module 7

ColumnStore Indexes

Module 8

Designing and Implementing Views

Module 9

Designing and Implementing Stored Procedures:

Module 10

Designing and Implementing User Defined Functions:

Module 11

Responding to Data Manipulation Via Triggers

Module 12

Responding to Data Manipulation Via Triggers

Module 13

Implementing Managed Code in SQL Server

Module 14

Storing and Querying XML Data in SQL Server

Module 15

Storing and Querying Spatial Data in SQL Server

Module 16

Storing and Querying BLOBs and Text Documents in SQL Server

Module 17

SQL SERVER Concurrency

Module 18

Performance and Monitoring

Practice Exam - Developing Microsoft SQL Databases

Self-Paced

Practice Exam

Beginner

1 Hour

Course Info

Certificate

Outline

More Information

Reviews

About Our Experts

Solutions

Company

Resources & Help

Social

We're Here For You

Certification Exam Prep Questions For
Developing Microsoft SQL Databases (MS-20762)