{"id":11817,"date":"2026-09-14T12:22:25","date_gmt":"2026-09-14T12:22:25","guid":{"rendered":"https:\/\/www.examlabs.com\/certification\/?p=11817"},"modified":"2026-09-14T12:22:25","modified_gmt":"2026-09-14T12:22:25","slug":"microsoft-dp-800-practice-test-questions-and-exam-dumps-part-2-q21-q40","status":"publish","type":"post","link":"https:\/\/www.examlabs.com\/certification\/microsoft-dp-800-practice-test-questions-and-exam-dumps-part-2-q21-q40\/","title":{"rendered":"Microsoft DP-800 Practice Test Questions and Exam Dumps Part 2: Q21\u2013Q40"},"content":{"rendered":"

View Full\u00a0Microsoft DP-800 Exam Dumps<\/a>\u00a0and Practice Test Dumps.<\/b><\/h2>\n

 <\/p>\n

Question 21<\/b><\/h3>\n

Which SQL Server function returns the current date and time of the database server?<\/b><\/p>\n

    \n
  1. CURRENT_DATE()<\/span><\/li>\n
  2. GETDATE()<\/span><\/li>\n
  3. TODAY()<\/span><\/li>\n
  4. SERVERDATE()<\/span><\/li>\n<\/ol>\n

    Correct Answer: 2<\/b><\/p>\n

    Explanation<\/b><\/p>\n

    The <\/span>GETDATE()<\/span> 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 <\/span>SYSDATETIME()<\/span>, 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.<\/span><\/p>\n

    Question 22<\/b><\/h3>\n

    Which SQL Server function returns only the current date without the time portion?<\/b><\/p>\n

      \n
    1. CURRENTDAY()<\/span><\/li>\n
    2. GETDATEONLY()<\/span><\/li>\n
    3. CAST(GETDATE() AS date)<\/span><\/li>\n
    4. TODAY()<\/span><\/li>\n<\/ol>\n

      Correct Answer: 3<\/b><\/p>\n

      Explanation<\/b><\/p>\n

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

      Question 23<\/b><\/h3>\n

      Which SQL Server function can return the number of characters in a string, excluding trailing spaces?<\/b><\/p>\n

        \n
      1. LEN()<\/span><\/li>\n
      2. CHARCOUNT()<\/span><\/li>\n
      3. SIZE()<\/span><\/li>\n
      4. STRINGCOUNT()<\/span><\/li>\n<\/ol>\n

        Correct Answer: 1<\/b><\/p>\n

        Explanation<\/b><\/p>\n

        The <\/span>LEN()<\/span> function returns the number of characters in a string while excluding trailing spaces. For example, <\/span>LEN(‘SQL Server ‘)<\/span> 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 <\/span>DATALENGTH()<\/span>, which measures the number of bytes used to store the expression. The difference can be important when working with Unicode or fixed-length data.<\/span><\/p>\n

        Question 24<\/b><\/h3>\n

        Which SQL Server function converts a string to uppercase?<\/b><\/p>\n

          \n
        1. UPPER()<\/span><\/li>\n
        2. CAPITALIZE()<\/span><\/li>\n
        3. TOUPPER()<\/span><\/li>\n
        4. UPCASE()<\/span><\/li>\n<\/ol>\n

          Correct Answer: 1<\/b><\/p>\n

          Explanation<\/b><\/p>\n

          The <\/span>UPPER()<\/span> function converts alphabetic characters in a string to uppercase. For example, <\/span>UPPER(‘sql server’)<\/span> returns <\/span>SQL SERVER<\/span>. 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 <\/span>UPDATE<\/span> statement. SQL Server also provides <\/span>LOWER()<\/span> for converting text to lowercase. String functions are commonly used during data cleansing and transformation tasks.<\/span><\/p>\n

          Question 25<\/b><\/h3>\n

          Which SQL Server function replaces one part of a string with another value?<\/b><\/p>\n

            \n
          1. CHANGE()<\/span><\/li>\n
          2. REPLACE()<\/span><\/li>\n
          3. SWAP()<\/span><\/li>\n
          4. SUBSTITUTE_TEXT()<\/span><\/li>\n<\/ol>\n

            Correct Answer: 2<\/b><\/p>\n

            Explanation<\/b><\/p>\n

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

            Question 26<\/b><\/h3>\n

            Which SQL Server expression can return a replacement value when an expression is NULL?<\/b><\/p>\n

              \n
            1. ISNULL()<\/span><\/li>\n
            2. NULLFIX()<\/span><\/li>\n
            3. IFNULLVALUE()<\/span><\/li>\n
            4. REPLACENULL()<\/span><\/li>\n<\/ol>\n

              Correct Answer: 1<\/b><\/p>\n

              Explanation<\/b><\/p>\n

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

              Question 27<\/b><\/h3>\n

              Which operator is commonly used to search for a pattern within text in SQL Server?<\/b><\/p>\n

                \n
              1. MATCH<\/span><\/li>\n
              2. LIKE<\/span><\/li>\n
              3. SEARCH<\/span><\/li>\n
              4. PATTERN<\/span><\/li>\n<\/ol>\n

                Correct Answer: 2<\/b><\/p>\n

                Explanation<\/b><\/p>\n

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

                Question 28<\/b><\/h3>\n

                Which wildcard represents any sequence of zero or more characters in a SQL Server LIKE pattern?<\/b><\/p>\n

                  \n
                1. _<\/span><\/li>\n
                2. ?<\/span><\/li>\n
                3. %<\/span><\/li>\n
                4. *<\/span><\/li>\n<\/ol>\n

                  Correct Answer: 3<\/b><\/p>\n

                  Explanation<\/b><\/p>\n

                  The <\/span>%<\/span> wildcard in a SQL Server <\/span>LIKE<\/span> expression represents zero or more characters. For example, <\/span>WHERE Name LIKE ‘A%’<\/span> matches values beginning with the letter A, while <\/span>WHERE Name LIKE ‘%son’<\/span> matches values ending with <\/span>son<\/span>. The underscore <\/span>_<\/span> 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.<\/span><\/p>\n

                  Question 29<\/b><\/h3>\n

                  Which SQL Server statement is used to create a new table?<\/b><\/p>\n

                    \n
                  1. NEW TABLE<\/span><\/li>\n
                  2. MAKE TABLE<\/span><\/li>\n
                  3. CREATE TABLE<\/span><\/li>\n
                  4. ADD TABLE<\/span><\/li>\n<\/ol>\n

                    Correct Answer: 3<\/b><\/p>\n

                    Explanation<\/b><\/p>\n

                    The <\/span>CREATE TABLE<\/span> statement creates a new table and defines its columns, data types, and optional constraints. For example, <\/span>CREATE TABLE Employees (EmployeeID int, Name varchar(100))<\/span> 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.<\/span><\/p>\n

                    Question 30<\/b><\/h3>\n

                    Which SQL statement is used to add a new column to an existing table?<\/b><\/p>\n

                      \n
                    1. ALTER TABLE<\/span><\/li>\n
                    2. UPDATE TABLE<\/span><\/li>\n
                    3. MODIFY TABLE<\/span><\/li>\n
                    4. CHANGE TABLE<\/span><\/li>\n<\/ol>\n

                      Correct Answer: 1<\/b><\/p>\n

                      Explanation<\/b><\/p>\n

                      The <\/span>ALTER TABLE<\/span> statement is used to modify the structure of an existing table. For example, <\/span>ALTER TABLE Employees ADD Email varchar(200)<\/span> 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.<\/span><\/p>\n

                      Question 31<\/b><\/h3>\n

                      Which SQL Server statement removes a table and its definition from the database?<\/b><\/p>\n

                        \n
                      1. DELETE TABLE<\/span><\/li>\n
                      2. REMOVE TABLE<\/span><\/li>\n
                      3. DROP TABLE<\/span><\/li>\n
                      4. CLEAR TABLE<\/span><\/li>\n<\/ol>\n

                        Correct Answer: 3<\/b><\/p>\n

                        Explanation<\/b><\/p>\n

                        The <\/span>DROP TABLE<\/span> statement removes a table from the database, including its definition and data. This is different from <\/span>DELETE<\/span>, 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.<\/span><\/p>\n

                        Question 32<\/b><\/h3>\n

                        Which SQL Server constraint prevents duplicate values in a column or group of columns?<\/b><\/p>\n

                          \n
                        1. UNIQUE<\/span><\/li>\n
                        2. CHECK<\/span><\/li>\n
                        3. DEFAULT<\/span><\/li>\n
                        4. VALIDATE<\/span><\/li>\n<\/ol>\n

                          Correct Answer: 1<\/b><\/p>\n

                          Explanation<\/b><\/p>\n

                          A <\/span>UNIQUE<\/span> 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.<\/span><\/p>\n

                          Question 33<\/b><\/h3>\n

                          Which SQL Server constraint can ensure that a value meets a specified condition?<\/b><\/p>\n

                            \n
                          1. RULE<\/span><\/li>\n
                          2. CHECK<\/span><\/li>\n
                          3. VALIDATE<\/span><\/li>\n
                          4. CONDITION<\/span><\/li>\n<\/ol>\n

                            Correct Answer: 2<\/b><\/p>\n

                            Explanation<\/b><\/p>\n

                            A <\/span>CHECK<\/span> constraint enforces a condition on values stored in a table. For example, <\/span>CHECK (Salary >= 0)<\/span> 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.<\/span><\/p>\n

                            Question 34<\/b><\/h3>\n

                            Which SQL Server constraint automatically supplies a value when no value is provided during an INSERT?<\/b><\/p>\n

                              \n
                            1. DEFAULT<\/span><\/li>\n
                            2. AUTO<\/span><\/li>\n
                            3. FILL<\/span><\/li>\n
                            4. VALUE<\/span><\/li>\n<\/ol>\n

                              Correct Answer: 1<\/b><\/p>\n

                              Explanation<\/b><\/p>\n

                              A <\/span>DEFAULT<\/span> 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 <\/span>GETDATE()<\/span>. 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.<\/span><\/p>\n

                              Question 35<\/b><\/h3>\n

                              Which SQL Server isolation level provides the highest protection against dirty reads among the standard transaction isolation levels?<\/b><\/p>\n

                                \n
                              1. READ UNCOMMITTED<\/span><\/li>\n
                              2. READ COMMITTED<\/span><\/li>\n
                              3. REPEATABLE READ<\/span><\/li>\n
                              4. SERIALIZABLE<\/span><\/li>\n<\/ol>\n

                                Correct Answer: 4<\/b><\/p>\n

                                Explanation<\/b><\/p>\n

                                SERIALIZABLE<\/span> 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.<\/span><\/p>\n

                                Question 36<\/b><\/h3>\n

                                Which SQL statement starts an explicit transaction in SQL Server?<\/b><\/p>\n

                                  \n
                                1. START TRANSACTION<\/span><\/li>\n
                                2. BEGIN TRANSACTION<\/span><\/li>\n
                                3. OPEN TRANSACTION<\/span><\/li>\n
                                4. CREATE TRANSACTION<\/span><\/li>\n<\/ol>\n

                                  Correct Answer: 2<\/b><\/p>\n

                                  Explanation<\/b><\/p>\n

                                  BEGIN TRANSACTION<\/span> 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 <\/span>COMMIT<\/span> or undone with <\/span>ROLLBACK<\/span>. 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.<\/span><\/p>\n

                                  Question 37<\/b><\/h3>\n

                                  Which SQL statement permanently saves the changes made by the current transaction?<\/b><\/p>\n

                                    \n
                                  1. SAVE<\/span><\/li>\n
                                  2. APPLY<\/span><\/li>\n
                                  3. COMMIT<\/span><\/li>\n
                                  4. CONFIRM<\/span><\/li>\n<\/ol>\n

                                    Correct Answer: 3<\/b><\/p>\n

                                    Explanation<\/b><\/p>\n

                                    The <\/span>COMMIT<\/span> statement makes the changes in the current transaction permanent. Once committed, the changes are no longer undone by a normal <\/span>ROLLBACK<\/span> 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.<\/span><\/p>\n

                                    Question 38<\/b><\/h3>\n

                                    Which SQL statement reverses uncommitted changes in the current transaction?<\/b><\/p>\n

                                      \n
                                    1. UNDO<\/span><\/li>\n
                                    2. ROLLBACK<\/span><\/li>\n
                                    3. REVERT<\/span><\/li>\n
                                    4. CANCEL<\/span><\/li>\n<\/ol>\n

                                      Correct Answer: 2<\/b><\/p>\n

                                      Explanation<\/b><\/p>\n

                                      The <\/span>ROLLBACK<\/span> 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.<\/span><\/p>\n

                                      Question 39<\/b><\/h3>\n

                                      Which SQL Server tool is commonly used to inspect the execution plan of a query and identify potential performance problems?<\/b><\/p>\n

                                        \n
                                      1. Query execution plan<\/span><\/li>\n
                                      2. Table designer<\/span><\/li>\n
                                      3. Object Explorer only<\/span><\/li>\n
                                      4. Database diagram<\/span><\/li>\n<\/ol>\n

                                        Correct Answer: 1<\/b><\/p>\n

                                        Explanation<\/b><\/p>\n

                                        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.<\/span><\/p>\n

                                        Question 40<\/b><\/h3>\n

                                        Which SQL Server feature can capture information about query activity and database performance for analysis?<\/b><\/p>\n

                                          \n
                                        1. Extended Events<\/span><\/li>\n
                                        2. SQL Formatter<\/span><\/li>\n
                                        3. Query Printer<\/span><\/li>\n
                                        4. Database Viewer<\/span><\/li>\n<\/ol>\n

                                          Correct Answer: 1<\/b><\/p>\n

                                          Explanation<\/b><\/p>\n

                                          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.<\/span><\/p>\n

                                           <\/p>\n","protected":false},"excerpt":{"rendered":"

                                          View Full\u00a0Microsoft DP-800 Exam Dumps\u00a0and Practice Test Dumps.   Question 21 Which SQL Server function returns the current date and time of the database server? CURRENT_DATE() GETDATE() TODAY() 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1648,1647],"tags":[],"_links":{"self":[{"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/posts\/11817"}],"collection":[{"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/comments?post=11817"}],"version-history":[{"count":1,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/posts\/11817\/revisions"}],"predecessor-version":[{"id":11818,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/posts\/11817\/revisions\/11818"}],"wp:attachment":[{"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/media?parent=11817"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/categories?post=11817"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.examlabs.com\/certification\/wp-json\/wp\/v2\/tags?post=11817"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}