Trắc Nghiệm Khoá Học SQL Server Phân 2

Question 1

What are special data structures associated with tables or views that help speed up the query?

Accepted Answer

Indexes

Question 2

Which type of index sorts and stores the data rows of a table or view in order based on the index key?

Question 3

In SQL Server, what are the storage units where data is stored known as?

Question 4

How many bytes does a page header take up at the beginning of a data page?

Question 5

What is the recommended file extension for primary data files in SQL Server?

Question 6

What is a table without a clustered index called?

Accepted Answer

Heap

Question 7

Which type of index is suited to cover queries that select from a well-defined subset of data using a predicate?

Question 8

In the B-Tree structure of an index, what are the bottom nodes called?

Answer

Root Nodes

Answer

Intermediate Nodes

Answer

Branch Nodes

Question 9

What statement is used to disable an index?

Question 10

Which system stored procedure allows you to rename any user-created object such as an index?

Question 11

What is a special type of stored procedure executed automatically when an attempt is made to modify data in a table?

Question 12

Which triggers execute when a table or a view is created, modified, or deleted using CREATE, ALTER, or DROP statements?

Question 13

When a record is updated, which tables are used by the UPDATE trigger to copy the original and new records?

Accepted Answer

Inserted and Deleted tables

Question 14

What type of trigger is executed in place of the INSERT, UPDATE, or DELETE operations?

Answer

AFTER Trigger

Answer

DDL Trigger

Answer

Logon Trigger

Question 15

Which stored procedure is used to retrieve trigger definitions?

Question 16

What function returns a Boolean value specifying whether an UPDATE or INSERT action was performed on a specific column?

Question 17

DDL and DML triggers can be nested up to how many levels?

Question 18

At what level are LOGON triggers created?

Answer

Database level

Answer

Table level

Answer

Schema level

Question 19

What happens to triggers defined on a table when the table is dropped?

Question 20

Which DML trigger is executed on completion of INSERT, UPDATE, or DELETE operations?

Question 21

What is a collection of one or more Transact-SQL statements sent to the server as one unit from an application?

Question 22

What keyword is used to declare variables in the body of a batch?

Accepted Answer

DECLARE

Question 23

Which statements provide a way to have an alias for a database object that may exist on a remote or local server?

Question 24

If a user has not provided a value at the time of declaration, what are variables initialized with?

Question 25

Which statement enforces a condition on the execution of a Transact-SQL statement?

Question 26

Which type of function always returns the same result each time it is called with a definite set of input values?

Question 27

Which textual data format is used for exchanging data in modern Web and Mobile applications and supported in SQL Server?

Answer

XML

Answer

CSV

Answer

YAML

Question 28

Which clause is used to format SQL Server data or results of SQL queries as JSON?

Question 29

Which Transact-SQL clause is used to define a window within a query resultset?

Question 30

Which ranking function spreads rows in an ordered partition into a given number of groups?

Accepted Answer

NTILE

Question 31

What are the four properties a logical unit of work must exhibit to qualify as a transaction?

Accepted Answer

Atomicity, Consistency, Isolation, Durability (ACID)

Question 32

Which transaction mode is the default for the SQL Server Database Engine?

Question 33

What system function returns the number of BEGIN TRANSACTION statements that occur in the current connection?

Question 34

Which statement marks the end of a successful implicit or explicit transaction?

Answer

BEGIN TRANSACTION

Answer

ROLLBACK TRANSACTION

Answer

SAVE TRANSACTION

Question 35

What lock mode prevents access to resources by concurrent transactions during INSERT, UPDATE, or DELETE operations?

Question 36

What type of error is detected by SQL Server before beginning the execution process?

Accepted Answer

Syntax Error

Question 37

Which Transact-SQL construct is used to handle exceptions effectively at the back end?

Question 38

Which function returns the number of the error that caused the CATCH block to run?

Question 39

Which statement starts error processing for a session and displays a custom error message?

Question 40

Which function returns the line number at which the error occurred in the TRY...CATCH block?

Accepted Answer

ERROR_LINE

Question 41

What feature enables your SQL Server instance to process Transact-SQL queries that read data from external data sources like Hadoop?

Question 42

What is the logical group of SQL Database Engine, PolyBase Engine, and PolyBase Data Movement Service called in PolyBase Architecture?

Question 43

In SQL Server 2022 PolyBase enhancements, which API is utilized for seamless integration with object storage?

Accepted Answer

S3 REST API

Question 44

Which of the following is a prerequisite to install PolyBase in SQL Server 2022?

Question 45

To query data in a Hadoop data source via PolyBase, what must you define to use in Transact-SQL queries?

Accepted Answer

An external table

Question 46

What SQL Server feature simplifies performance troubleshooting by helping quickly to find performance differences caused by query plan changes?

Question 47

Which state is the Operation Mode (Requested) set to when enabling Query Store via SSMS?

Question 48

In Query Store architecture, what messages are sent to the Query Store during query processing?

Accepted Answer

Compile Message and Execute Message

Question 49

Which of the following is a new Query Store feature in SQL Server 2022?

Question 50

Which system stored procedure forces a plan for a particular query in Query Store?

Question 51

What does IQP stand for in SQL Server 2022?

Question 52

What compatibility level must be enabled to explicitly make a database eligible for IQP in SQL Server 2022?

Question 53

Which IQP feature assists SQL Server in improving the accuracy of query execution plans by providing the estimated number of rows?

Question 54

Which feature optimizes resource usage by identifying inefficiencies in running repeated queries in parallel?

Question 55

What is a limitation of the Parameter Sensitive Plan (PSP) Optimization?

Question 56

Machine Learning in SQL Server refers to the integration of which languages with the SQL database engine?

Question 57

Which framework facilitates SQL Server to execute external Machine Learning scripts?

Question 58

What kind of statistical method depicts the relationship between dependent and independent variables to predict future values?

Question 59

In SQL Server 2022, which approximate percentile aggregate function returns an interpolated value from the dataset?

Question 60

Which feature represents a refinement of the Memory Grant Feedback (MGF) feature by precisely determining optimal memory grants?

Accepted Answer

Percentile Grant Feedback