Assessment Modules:
Module 1
An Introduction to Database Development
1-A
How many types of SQL Server Components are there?
1-B
How many types of SQL Server Tools are available in 2018?
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?
Back To Top
Module 2
Designing and implementing Tables
1-A
How many types of Normalization Forms are there?
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?
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?
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?
- D. A and C
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
- B. Foreign Keys
- C. Natural Keys
Back To Top
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?
Back To Top
Module 4
Ensuring Data Integrity Through Constraints:
1
How many types of Data Integrity are there?
1-A
This integrity defines the allowed values in columns:
1-B
This integrity defines relationships between tables
1-C
Integrity in which primary key uniquely identifies each row within a table
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 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
- C. A and D
2
Maximum number of NonClustered Indexes a table can have?
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?
- D. B and C
Back To Top
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:
2
What type of different workload formats does Database Engine Tuning Advisor does not accept?
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.
Back To Top
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 8
Designing and Implementing Views
1
Part1:How many types of views are there in SQL Server?
Part2a: How many types of views are there in SQL Server?
Part2b:How many types of User Defined views can you create in SQL Server?
Part3a:How many types of system views are there in SQL Server?
Part3bViews that provide information about the state of the SQL Server Database Engine are called as?
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 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?
Back To Top
Module 10
Designing and Implementing User Defined Functions:
1
How many types of Functions are there in SQL Server?
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 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:
Back To Top
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?
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?
Back To Top
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?.
- A. Potability
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?
Back To Top
