Quiz-summary
0 of 10 questions completed
Questions:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
Information
Practice Questions
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
Results
0 of 10 questions answered correctly
Your time:
Time has elapsed
You have reached 0 of 0 points, (0)
Categories
- Not categorized 0%
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- Answered
- Review
-
Question 1 of 10
1. Question
In a given simple query and indexing scenario, you are required to run a query for a specific province (Texas) from a list of International Importers database. Which type of query performance would provide the most efficient results?
Correct
Although nonclustered and clustered index store data in a B-tree structure, nonclustered indexes include non-key columns to cover more queries. As nonclustered indexes can add a filter to include or exclude rows based on a column filter predicate, this reduces the number of rows the SQL Server has to read (clustered indexes do not have the option to filter).
Incorrect
Although nonclustered and clustered index store data in a B-tree structure, nonclustered indexes include non-key columns to cover more queries. As nonclustered indexes can add a filter to include or exclude rows based on a column filter predicate, this reduces the number of rows the SQL Server has to read (clustered indexes do not have the option to filter).
-
Question 2 of 10
2. Question
A highly selective query is defined as one that returns a subset of rows in a given table or a combination of given tables. This is achieved by using queries consisting of a “WHERE” clause or by joining them to a more compact table with a reduced number of matching rows (indicating a higher number of unique values). From the following, choose the correct option to search for an employee with the use of a highly selective query.
Correct
By using a highly selective query on the database only a single record is expected to match the value being searched for. I.e., Each employee has a unique Social Security Number (SSN), or Contact Number or ID Number, etc.
Incorrect
By using a highly selective query on the database only a single record is expected to match the value being searched for. I.e., Each employee has a unique Social Security Number (SSN), or Contact Number or ID Number, etc.
-
Question 3 of 10
3. Question
SQL constraints are usually applied before the data is loaded to specify rules for the data in a table. These constraints can be column/table level and limit the type of data that can go into a table. From the following list of constraints which is NOT usually defined before loading the data?
Correct
An index is usually not defined before loading the data and, as it is not generated when defining a foreign key constraint, it is recommended to manually enter a nonclustered index for all the foreign keys.
Incorrect
An index is usually not defined before loading the data and, as it is not generated when defining a foreign key constraint, it is recommended to manually enter a nonclustered index for all the foreign keys.
-
Question 4 of 10
4. Question
A user attempts to create a clustered columnstore index, however, the following error message is displayed: “The statement failed. Column ‘Picture’ has a data type that cannot participate in a columnstore index. Omit column ‘Picture’.”.
As clustered columnstore indexes do not support certain data types, from the following options select the data type that is supported by clustered columnstore indexes.Correct
Columnstore index cannot support certain data types, for example: “ntext, text and image”, “Sql_variant”, “Xml”, “Rowversion”, “CLR types (hierarchyid and spatial types)”.
Incorrect
Columnstore index cannot support certain data types, for example: “ntext, text and image”, “Sql_variant”, “Xml”, “Rowversion”, “CLR types (hierarchyid and spatial types)”.
-
Question 5 of 10
5. Question
When designing a storage solution for a database, difficulties are often faced in the initial stages due to lack of comparison to real-world workloads as benchmarks. However, several criteria such as typical workloads expected and the number of concurrent users are measured to estimate the expected performance. Which of the following is NOT from the measured criteria when designing an appropriate storage solution?
Correct
The volume of data determines the space required for initial storage and the expected rate of growth. The data retention policy is used to determining the amount of data that needs to be retained as an excess amount may bottleneck the performance. Whereas an operating system is not as detrimental when designing an appropriate storage solution as a Database can be designed on almost any OS.
Incorrect
The volume of data determines the space required for initial storage and the expected rate of growth. The data retention policy is used to determining the amount of data that needs to be retained as an excess amount may bottleneck the performance. Whereas an operating system is not as detrimental when designing an appropriate storage solution as a Database can be designed on almost any OS.
-
Question 6 of 10
6. Question
Most organizations utilize DAS (Direct Attached Storage) or SAN (Storage-Area Network) to base their storage subsystem SQL Servers uses. For DAS, the server is connected directly via a host bus adapter using a SATA connectivity or Fibre Channel. For SAN, the server is connected via an IP using SAN switches. Which of the following advantages for a DAS/SAN setup is incorrect?
Correct
For a larger pool of disks, SAN is a more scalable storage solution as it provides higher availably through its storage fabric. DAS is more suitable for smaller businesses as it does not require SAN switches and the cost of maintenance is cheaper.
Incorrect
For a larger pool of disks, SAN is a more scalable storage solution as it provides higher availably through its storage fabric. DAS is more suitable for smaller businesses as it does not require SAN switches and the cost of maintenance is cheaper.
-
Question 7 of 10
7. Question
When determining the disk storage technology for a database, the read and write operations across a disk are measured. More commonly known as Redundant Array of Independent Disks (RAID). The commonly recommended RAID levels for SQL Servers are RAID 5 and RAID 10. Which of the following statements does NOT define RAID 5/RAID 10 correctly?
Correct
RAID 5 provides some fault tolerance and more usable storage at the cost for each write operation while RAID 10 provides more fault tolerance and better read and write performance at the cost of usable storage.
Incorrect
RAID 5 provides some fault tolerance and more usable storage at the cost for each write operation while RAID 10 provides more fault tolerance and better read and write performance at the cost of usable storage.
-
Question 8 of 10
8. Question
A recent approach used in storage allocation designs a single pool consisting of all the set of disks in the SAN device. Logical disks are carved from this pool and every server connected to the SAN device is presented with the logical disks. From the following options, which of these is NOT an advantage for this method?
Correct
In the case of a segregated pool of disks, the latency is reduced as other server workloads do not interfere. However in this case, as the entire server accesses the same pool, latency and workload increase.
Incorrect
In the case of a segregated pool of disks, the latency is reduced as other server workloads do not interfere. However in this case, as the entire server accesses the same pool, latency and workload increase.
-
Question 9 of 10
9. Question
One key aspect often overlooked in data file placement is the storage of the backup file. Most of the time it is placed in the same volume as tempdb files and data. Which of the following options is incorrect when describing common practices related to Database Backup Files?
Correct
Often times companies have retention policies to purge very old backup files.
Incorrect
Often times companies have retention policies to purge very old backup files.
-
Question 10 of 10
10. Question
When a database’s performance scales up alongside the volume growth, the need for table partitioning arises. A table partition consists of 3 key features, which of the following is NOT a key feature in table partitioning?
Correct
Partition row does not exist as partition function defines the range of boundaries based on the values of the partition column.
Incorrect
Partition row does not exist as partition function defines the range of boundaries based on the values of the partition column.