Student Portal

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


Arrow
Assessment Modules:
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. Using Window Ranking, Offset and Aggregate Functions

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

 
Module 1
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.
2

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

3

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.


Back To Top
 
 
Module 2
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.
2

Which of the following statements are true about Unique Identifiers?

3-A

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

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?

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.


Back To Top
 
 
Module 3
Module 3

Advanced Table Designs

1

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!


Back To Top
 
 
Module 3
Module 4

Ensuring Data Integrity Through Constraints:

1

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

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

3

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.



Back To Top
 
 
Module 3
Module 5

Introduction to Indexes

1

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

2

Maximum number of NonClustered Indexes a table can have?

A. Infinite
Incorrect.
B. 1
Incorrect.
C. 999
Correct!
D. None of the above
Incorrect.
3

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?



Back To Top
 
 
Module 2
Module 6

Designing Optimized Index Strategies

1

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!
2

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!
3

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!


Back To Top
 
 
Module 2
Module 7

ColumnStore Indexes

1

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

2

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

3

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:



Back To Top
 
 
Module 2
Module 8

Designing and Implementing Views

1

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

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

3

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:

Part2: Disadvantages:



Back To Top
 
 
Module 2
Module 9

Designing and Implementing Stored Procedures:

1

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

2

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!


Back To Top
 
 
Module 2
Module 10

Designing and Implementing User Defined Functions:

1

How many types of Functions are there in SQL Server?

A. 4
Correct!
B. 3
Incorrect.
C. 5
Incorrect.
D. 6
Incorrect.
2

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

3

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



Back To Top
 
 
Module 2
Module 11

Responding to Data Manipulation Via Triggers

1

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

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

2

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

3

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!


Back To Top
 
 
Module 2
Module 12

Responding to Data Manipulation Via Triggers

1

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

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

3

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!


Back To Top
 
 
Module 2
Module 13

Implementing Managed Code in SQL Server

1

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

2

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

3

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!


Back To Top
 
 
Module 2
Module 14

Storing and Querying XML Data in SQL Server

1

Part 1: SQL Server has a native datatype for storing XML data. Which can be used for variables, columns and parameters in databases. Which of the following is true about XML Data?

Part 2: Which of the following is not true about XML Namespaces?

2

Part 1: You want to store XML data, and you know that you can enable any XML (typed vs untyped). In Which of the following situations XML typed data should be used?

Part 2: XML indexes are used to improve the performance of XQuery-based queries. There are two types of XML indexes: Primary XML and Secondary XML indexes. You can construct three types of Secondary XML indexes to help answer specific XQuery queries rapidly. The three types of Secondary XML indexes are: Path, Value and Property Indexes. Select all the options which are correct about each type:

3

The FOR-XML clause in SQL Server is used to enable the Transact-SQL SELECT statement to return data in an XML format. It can be configured to return attributes, elements, and/or schema that are required by client applications.
Part 1: For XML have 4 different modes. Which of the following is/are not the mode of FOR XML clause?

A. RAW
Incorrect.
B. AUTO
Incorrect.
C. IMPLICIT
Correct!
D. PATH
Incorrect.

Part2: When using Raw mode queries which of the following is not true?

Part 3: From the options below, one option describes Auto Mode, one of the four modes in FOR XML clause. Select the correct one:



Back To Top
 
 
Module 2
Module 15

Storing and Querying Spatial Data in SQL Server

1

SQL Server supports many measurement systems directly. When we specify a spatial data type in SQL Server, we also specify the measurement system used. It is done by associating a spatial reference ID with the data. Which of the following statements below about Spatial Reference Identifiers are true. Choose all that apply.

2

Which existing SQL Server data type could you use to store, but not directly process, raster data?

A. Varchar
Incorrect.
B. Varbinary
Correct!
C. String
Incorrect.
D. None of the above
Incorrect.
3

Which of the following statements about Spatial Data Formats are true?



Back To Top
 
 
Module 2
Module 16

Storing and Querying BLOBs and Text Documents in SQL Server

1

There are some technical challenges when integrating BLOBs with a database. And the way data is handled changes significantly. Among the factors listed below which one is not a technical challenge faced when integrating BLOBs with a database:

2

When you consider using a FILESTREAM, which of the following statements are true?

3

Let’s assume you have a full-text index on a table, How many types of searches against the character-based data can you execute?

A. 5
Incorrect.
B. 3
Incorrect.
C. 6
Correct!
D. None of the above
Incorrect.


Back To Top
 
 
Module 2
Module 17

SQL SERVER Concurrency

1

There are two types of approaches to resolving data conflicts during concurrent operations by two different users. One approach is Pessimistic and the other one is optimistic concurrency. With regards to Optimistic approach, which of the following statements are not correct when choosing optimistic concurrency model for a system?

2

Part 1a: Which of the following Transaction isolation levels are of lowest level in SQL Server?

A. Read Uncommitted
Correct!
B. Read Committed
Incorrect.
C. Repeatable Read
Incorrect.
D. Serializable
Incorrect.

Part 1b: Which of the following Transaction isolation levels are highest in SQL Server?

A. Serializable
Correct!
B. Repeatable Read
Incorrect.
C. Read Committed
Incorrect.
D. None of the above.
Incorrect.
3

Part1a: Although Row Versioning based isolation levels have some benefits but, it can cause some issues as well. Which of the following statements describes issues with row versioning-based isolation levels?

Part1b: If Transactions can be defined as A logical unit of work, made up of one or more Transact-SQL statements (Atomicity, Consistency, Isolation and Durability. Which one of the following statements describe Atomicity?



Back To Top
 
 
Module 2
Module 18

Performance and Monitoring

1

Extended Events is the successor to SQL trace and SQL Profiler. Its architecture is based on Extended events engine which provides capabilities such as user defined session and other component of the architecture is a package, which defines the objects available to a session. User defined session performs some activities which includes:

A. Collecting the event
Incorrect.
B. Writes to Target
Incorrect.
C. A and B
Correct!
D. All of the above
Incorrect.
2

Which of the following statements will enable Live Query Statistics for all sessions in Activity Monitor?

3

Which of the following statements about Tempdb are not correct/false?

Part1b: If Transactions can be defined as A logical unit of work, made up of one or more Transact-SQL statements (Atomicity, Consistency, Isolation and Durability. Which one of the following statements describe Atomicity?



Back To Top
 
  • Home
  • Practice Exam - Developing Microsoft SQL Databases

Practice Exam - Developing Microsoft SQL Databases

Be the first to review this course

QuickStart is now offering you assessment questions to better aid in certification. 100% of the questions are real questions from a recent version of the test you will take.
$99.00

QuickStart is now offering you assessment questions to better aid in certification. 100% of the questions are real questions from a recent version of the test you will take.

More Information:

  • Learning Style: On Demand
  • Learning Style: Practice Exam
  • Difficulty: Beginner
  • Course Duration: 1 Hour
  • Course Info: Download PDF
  • Certificate: See Sample

Need Training for 5 or More People?

Customized to your team's need:

  • Annual Subscriptions
  • Private Training
  • Flexible Pricing
  • Enterprise LMS
  • Dedicated Customer Success Manager

Course Information

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)

Outline

Hit button to validate captcha