View Full Microsoft DP-800 Exam Dumps and Practice Test Dumps.

 

Question 21

Which SQL Server function returns the current date and time of the database server?

1. CURRENT_DATE()
2. GETDATE()
3. TODAY()
4. SERVERDATE()

Correct Answer: 2

Explanation

The GETDATE() function returns the current date and time from the SQL Server instance. It is commonly used when inserting records that need a timestamp or when comparing data with the current time. SQL Server also provides other date and time functions, including SYSDATETIME(), which provides higher precision. When designing applications, administrators should understand whether the required value should come from the database server’s clock or from the application server’s clock.

Question 22

Which SQL Server function returns only the current date without the time portion?

1. CURRENTDAY()
2. GETDATEONLY()
3. CAST(GETDATE() AS date)
4. TODAY()

Correct Answer: 3

Explanation

SQL Server does not provide a standard TODAY() function like some other platforms. One common way to return only the current date is CAST(GETDATE() AS date). The GETDATE() function first returns the current date and time, and the CAST converts it to the date data type. This is useful when the time portion is not needed, such as when filtering records based only on calendar dates.

Question 23

Which SQL Server function can return the number of characters in a string, excluding trailing spaces?

1. LEN()
2. CHARCOUNT()
3. SIZE()
4. STRINGCOUNT()

Correct Answer: 1

Explanation

The LEN() function returns the number of characters in a string while excluding trailing spaces. For example, LEN(‘SQL Server ‘) counts the meaningful characters without counting trailing spaces. This function is useful for validating text fields, checking data quality, and creating conditional logic. SQL Server also provides DATALENGTH(), which measures the number of bytes used to store the expression. The difference can be important when working with Unicode or fixed-length data.

Question 24

Which SQL Server function converts a string to uppercase?

1. UPPER()
2. CAPITALIZE()
3. TOUPPER()
4. UPCASE()

Correct Answer: 1

Explanation

The UPPER() function converts alphabetic characters in a string to uppercase. For example, UPPER(‘sql server’) returns SQL SERVER. It can be useful when formatting output or normalizing text for comparisons and reports. The original stored value is not changed unless the result is used in an UPDATE statement. SQL Server also provides LOWER() for converting text to lowercase. String functions are commonly used during data cleansing and transformation tasks.

Question 25

Which SQL Server function replaces one part of a string with another value?

1. CHANGE()
2. REPLACE()
3. SWAP()
4. SUBSTITUTE_TEXT()

Correct Answer: 2

Explanation

The REPLACE() function searches a string for a specified expression and replaces matching occurrences with another expression. For example, REPLACE(‘Microsoft SQL’, ‘SQL’, ‘Database’) produces Microsoft Database. This function is useful for cleaning and transforming text data. It can be used in a SELECT statement to generate transformed output or in an UPDATE statement to permanently change stored values. Administrators should test replacement logic carefully before updating production data.

Question 26

Which SQL Server expression can return a replacement value when an expression is NULL?

1. ISNULL()
2. NULLFIX()
3. IFNULLVALUE()
4. REPLACENULL()

Correct Answer: 1

Explanation

The ISNULL() function checks an expression and returns a replacement value when that expression is NULL. For example, ISNULL(PhoneNumber, ‘Not Provided’) returns the phone number when available and the text Not Provided when it is NULL. This is useful for reporting and handling missing values. SQL Server also supports COALESCE(), which can evaluate multiple expressions. Administrators should understand NULL behavior because NULL is different from an empty string or zero.

Question 27

Which operator is commonly used to search for a pattern within text in SQL Server?

1. MATCH
2. LIKE
3. SEARCH
4. PATTERN

Correct Answer: 2

Explanation

The LIKE operator is used to compare text against a pattern. Wildcards such as % and _ can be used to represent variable or single characters. For example, WHERE Name LIKE ‘Ali%’ finds names beginning with Ali. LIKE is useful for filtering text, but leading wildcard patterns such as %abc may prevent efficient use of some indexes. Query design and indexing should be considered when pattern searches are performed on large datasets.

Question 28

Which wildcard represents any sequence of zero or more characters in a SQL Server LIKE pattern?

1. _
2. ?
3. %
4. *

Correct Answer: 3

Explanation

The % wildcard in a SQL Server LIKE expression represents zero or more characters. For example, WHERE Name LIKE ‘A%’ matches values beginning with the letter A, while WHERE Name LIKE ‘%son’ matches values ending with son. The underscore _ represents exactly one character. Understanding SQL Server wildcard behavior is important when creating text filters. Wildcard searches should also be considered carefully for performance when working with large tables.

Question 29

Which SQL Server statement is used to create a new table?

1. NEW TABLE
2. MAKE TABLE
3. CREATE TABLE
4. ADD TABLE

Correct Answer: 3

Explanation

The CREATE TABLE statement creates a new table and defines its columns, data types, and optional constraints. For example, CREATE TABLE Employees (EmployeeID int, Name varchar(100)) creates a simple Employees table. Additional constraints such as primary keys, foreign keys, UNIQUE constraints, and CHECK constraints can be included. Good table design is important because data types and constraints affect data quality, storage, and query performance. Tables should be designed according to the application’s actual data requirements.

Question 30

Which SQL statement is used to add a new column to an existing table?

1. ALTER TABLE
2. UPDATE TABLE
3. MODIFY TABLE
4. CHANGE TABLE

Correct Answer: 1

Explanation

The ALTER TABLE statement is used to modify the structure of an existing table. For example, ALTER TABLE Employees ADD Email varchar(200) adds a new Email column. ALTER TABLE can also be used for other structural changes, such as modifying columns or adding constraints, depending on the operation. Structural changes should be tested before applying them to production databases because they can affect applications, indexes, constraints, and existing data.

Question 31

Which SQL Server statement removes a table and its definition from the database?

1. DELETE TABLE
2. REMOVE TABLE
3. DROP TABLE
4. CLEAR TABLE

Correct Answer: 3

Explanation

The DROP TABLE statement removes a table from the database, including its definition and data. This is different from DELETE, which removes rows while leaving the table structure in place. Dropping a table can also affect dependent objects and applications. Because it is a destructive operation, administrators should verify the table name and dependencies before executing it. In production environments, appropriate backup and change-management procedures should be followed before dropping important database objects.

Question 32

Which SQL Server constraint prevents duplicate values in a column or group of columns?

1. UNIQUE
2. CHECK
3. DEFAULT
4. VALIDATE

Correct Answer: 1

Explanation

A UNIQUE constraint prevents duplicate values in the specified column or combination of columns. It is useful when a value must be unique but is not necessarily the table’s primary key. For example, an Email column may have a UNIQUE constraint to prevent two users from using the same email address. SQL Server can create an index to enforce the uniqueness. The choice between a primary key and UNIQUE constraint depends on the role of the column in the table’s data model.

Question 33

Which SQL Server constraint can ensure that a value meets a specified condition?

1. RULE
2. CHECK
3. VALIDATE
4. CONDITION

Correct Answer: 2

Explanation

A CHECK constraint enforces a condition on values stored in a table. For example, CHECK (Salary >= 0) prevents negative salary values from being inserted. CHECK constraints help protect data integrity at the database level instead of relying only on application code. They are especially useful when multiple applications or users can modify the same database. Administrators should ensure that the condition matches the actual business requirement and does not accidentally reject valid data.

Question 34

Which SQL Server constraint automatically supplies a value when no value is provided during an INSERT?

1. DEFAULT
2. AUTO
3. FILL
4. VALUE

Correct Answer: 1

Explanation

A DEFAULT constraint provides a predefined value when an INSERT statement does not specify a value for the column. For example, a CreatedDate column could have a default of GETDATE(). This can reduce the amount of application code required and help ensure that important fields receive appropriate values. A DEFAULT does not replace an explicitly supplied value. Administrators should select sensible defaults and verify that they match the application’s data and business requirements.

Question 35

Which SQL Server isolation level provides the highest protection against dirty reads among the standard transaction isolation levels?

1. READ UNCOMMITTED
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

Correct Answer: 4

Explanation

SERIALIZABLE is the strictest standard SQL Server transaction isolation level. It provides the strongest protection against concurrent changes by using locking behavior that can prevent other transactions from modifying or inserting data that would affect the current transaction’s result. The stronger consistency can reduce concurrency and increase blocking compared with less restrictive isolation levels. Administrators should choose isolation levels based on application requirements because using the strictest level everywhere can negatively affect performance and scalability.

Question 36

Which SQL statement starts an explicit transaction in SQL Server?

1. START TRANSACTION
2. BEGIN TRANSACTION
3. OPEN TRANSACTION
4. CREATE TRANSACTION

Correct Answer: 2

Explanation

BEGIN TRANSACTION starts an explicit transaction in SQL Server. Multiple data modification statements can then be treated as a single unit of work. The transaction can be completed with COMMIT or undone with ROLLBACK. Transactions are important when several related changes must either all succeed or all fail. Administrators and developers should keep transactions reasonably short because long-running transactions can hold locks, increase blocking, and consume database resources.

Question 37

Which SQL statement permanently saves the changes made by the current transaction?

1. SAVE
2. APPLY
3. COMMIT
4. CONFIRM

Correct Answer: 3

Explanation

The COMMIT statement makes the changes in the current transaction permanent. Once committed, the changes are no longer undone by a normal ROLLBACK of that transaction. COMMIT is commonly used after all related operations have completed successfully. Transactions help maintain consistency when multiple operations must succeed together. Before committing important changes, applications should validate the required conditions and handle errors appropriately to avoid leaving related data in an inconsistent state.

Question 38

Which SQL statement reverses uncommitted changes in the current transaction?

1. UNDO
2. ROLLBACK
3. REVERT
4. CANCEL

Correct Answer: 2

Explanation

The ROLLBACK statement reverses changes made during the current transaction that have not yet been committed. It is useful when an operation fails or when validation shows that changes should not be saved. For example, an application can update several related tables and roll back all changes if one operation fails. ROLLBACK is an important part of transaction error handling. Administrators should understand transaction boundaries because changes made outside the transaction cannot normally be rolled back by it.

Question 39

Which SQL Server tool is commonly used to inspect the execution plan of a query and identify potential performance problems?

1. Query execution plan
2. Table designer
3. Object Explorer only
4. Database diagram

Correct Answer: 1

Explanation

A query execution plan shows how SQL Server intends to retrieve or modify data. It can reveal operations such as table scans, index seeks, joins, sorts, and other processing steps. Administrators can use execution plans to identify expensive operations and investigate whether indexes or query changes could improve performance. The estimated plan can be reviewed before execution, while an actual execution plan can provide information about what happened during execution. Execution plans are an important tool for SQL performance tuning.

Question 40

Which SQL Server feature can capture information about query activity and database performance for analysis?

1. Extended Events
2. SQL Formatter
3. Query Printer
4. Database Viewer

Correct Answer: 1

Explanation

Extended Events is a lightweight SQL Server monitoring and troubleshooting framework. It can capture events such as query activity, errors, waits, deadlocks, and other database events. Administrators can create sessions that collect specific information needed to investigate a performance or reliability problem. Compared with broad, continuous logging of everything, targeted Extended Events sessions can reduce unnecessary overhead. They are useful when an administrator needs evidence about what SQL Server is doing during a particular problem.