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

 

Question 181

Which SQL Server feature allows a database administrator to create a copy of a database schema and data for development or testing purposes?

1. Database snapshot
2. BACPAC
3. Database role
4. Query Store

Correct Answer: 2

Explanation

A BACPAC package can contain the schema and data of a database and can be used to move or copy database information between supported environments. It is useful for scenarios such as creating development or testing copies. A BACPAC is different from a traditional database backup because it is a logical export package rather than a complete backup used for point-in-time recovery. Administrators should select the appropriate method based on migration, testing, or recovery requirements.

Question 182

Which SQL Server feature provides a transactionally consistent, read-only view of a database at a specific point in time?

1. Database snapshot
2. Database trigger
3. Query Store
4. SQL Server Agent

Correct Answer: 1

Explanation

A database snapshot provides a read-only, point-in-time view of the source database. It can be useful for reporting, recovering individual objects from a previous state, or protecting against certain user errors. The snapshot uses sparse files and stores changed pages separately from the source database. It is not a replacement for a backup because it depends on the source database and does not provide the same disaster recovery protection as independent backup storage.

Question 183

Which SQL Server feature can be used to move selected data between databases or servers through a command-line utility?

1. bcp
2. SQL Server Agent
3. Query Store
4. Activity Monitor

Correct Answer: 1

Explanation

The bcp utility is a command-line tool used for bulk copying data between SQL Server and data files. It can export table data to files or import data from files into SQL Server. It is useful for large data transfer operations and scripted data movement. Administrators should carefully define the format, delimiters, encoding, and error handling when using bcp because incorrect import settings can cause data quality or conversion problems.

Question 184

Which T-SQL command can insert large amounts of data from a file into a SQL Server table?

1. BULK INSERT
2. CREATE INDEX
3. ALTER LOGIN
4. DBCC CHECKDB

Correct Answer: 2

Explanation

BULK INSERT allows SQL Server to import data from a supported file into a table. It is designed for efficient bulk data loading and can process large numbers of rows more efficiently than inserting individual rows one at a time. Administrators need to ensure that the file format matches the target table and that SQL Server has the required access to the file location. Bulk loading should also be tested carefully before production use.

Question 185

Which SQL Server command can return the first N rows from a query result?

1. TOP
2. GROUP BY
3. HAVING
4. DISTINCT

Correct Answer: 1

Explanation

The TOP clause limits the number or percentage of rows returned by a query. It is useful when an application needs only a limited number of results, such as the top 10 highest-value orders. When using TOP, an ORDER BY clause is often important to ensure that the returned rows represent the intended ranking. Without an appropriate ordering, SQL Server may return any qualifying rows rather than a predictable set.

Question 186

Which SQL Server clause is used to skip a specified number of rows before returning results?

1. OFFSET
2. WHERE
3. GROUP BY
4. HAVING

Correct Answer: 3

Explanation

The OFFSET clause allows a query to skip a specified number of rows before returning results. It is commonly used with FETCH to implement pagination. For example, an application can skip the first 20 rows and return the next 10. Pagination should normally use a deterministic ORDER BY so that results remain consistent between requests. Administrators should also consider indexing and query performance when implementing pagination over large tables.

Question 187

Which SQL Server clause can return a specified number of rows after an OFFSET value?

1. FETCH NEXT
2. HAVING
3. DISTINCT
4. UNION

Correct Answer: 1

Explanation

FETCH NEXT is commonly used with OFFSET and ORDER BY to implement pagination. OFFSET specifies how many rows should be skipped, while FETCH NEXT specifies how many rows should be returned. This approach can be useful for applications that display data across multiple pages. For large datasets, administrators should evaluate the performance of offset-based pagination and consider suitable indexing or alternative pagination strategies when necessary.

Question 188

Which SQL Server function returns the current UTC date and time?

1. GETUTCDATE()
2. GETDATE()
3. SYSDATETIMEOFFSET()
4. CURRENT_TIMESTAMP

Correct Answer: 4

Explanation

GETUTCDATE() returns the current UTC date and time as a datetime value. UTC is useful for applications that operate across multiple time zones because it provides a consistent time reference. GETDATE() returns the current local system date and time, while other functions provide different precision or time-zone information. Administrators should choose the appropriate function based on application requirements and should avoid mixing local and UTC timestamps without a clear design.

Question 189

Which SQL Server data type stores a date and time value with greater fractional-second precision than datetime?

1. datetime2
2. varchar
3. int
4. bit

Correct Answer: 1

Explanation

The datetime2 data type provides date and time storage with greater fractional-second precision and a wider supported date range than the older datetime type. It is generally preferred for new SQL Server database designs when both date and time need to be stored. The precision can be specified according to application requirements. Choosing an appropriate data type helps maintain data accuracy while avoiding unnecessary storage or conversion requirements.

Question 190

Which SQL Server data type is appropriate for storing values with fixed precision and scale, such as financial amounts?

1. decimal
2. varchar
3. datetime2
4. bit

Correct Answer: 2

Explanation

The decimal data type stores exact numeric values with a defined precision and scale. It is commonly used for financial amounts where rounding errors associated with approximate numeric types are undesirable. For example, decimal(12,2) can store values with a total of 12 digits and two digits after the decimal point. Administrators should select precision and scale according to the expected range of values to avoid overflow or unnecessary storage requirements.

Question 191

Which SQL Server data type stores only TRUE or FALSE-style values?

1. bit
2. int
3. money
4. varchar

Correct Answer: 1

Explanation

The bit data type is commonly used for Boolean-style values such as enabled or disabled, active or inactive, and yes or no. SQL Server uses 0 and 1 to represent false and true values in typical usage. A bit column can help make database designs clear when an attribute has only a small number of possible states. Administrators should use appropriate data types rather than storing Boolean values as strings.

Question 192

Which SQL Server constraint ensures that a column cannot contain NULL values?

1. NOT NULL
2. UNIQUE
3. CHECK
4. DEFAULT

Correct Answer: 2

Explanation

The NOT NULL constraint requires a column to contain a value for every inserted or updated row. It is useful when a particular attribute is mandatory, such as a customer’s name or an order date. NOT NULL is different from DEFAULT, which supplies a value when one is not provided. Administrators should identify required attributes during database design because enforcing mandatory values at the database level helps maintain data quality.

Question 193

Which SQL Server constraint ensures that a column or set of columns contains unique values while allowing a separate primary key?

1. UNIQUE
2. DEFAULT
3. CHECK
4. FOREIGN KEY

Correct Answer: 1

Explanation

A UNIQUE constraint ensures that duplicate values are not allowed in the constrained column or combination of columns. A table can have multiple unique constraints, while it can have only one primary key constraint. Unique constraints are useful for attributes such as email addresses or employee numbers when those values must remain distinct. Administrators should consider whether NULL values and composite uniqueness requirements affect the specific database design.

Question 194

Which constraint ensures that values in a column meet a specified logical condition?

1. CHECK
2. DEFAULT
3. UNIQUE
4. FOREIGN KEY

Correct Answer: 3

Explanation

A CHECK constraint enforces a logical condition on values stored in a column or row. For example, a database can require that an age value be greater than or equal to zero or that an order amount not be negative. CHECK constraints help enforce data quality directly within the database. They are useful because invalid values can be rejected regardless of which application or process attempts to modify the data.

Question 195

Which SQL Server feature automatically provides a value for a column when an INSERT statement does not specify one?

1. DEFAULT constraint
2. UNIQUE constraint
3. FOREIGN KEY
4. CHECK constraint

Correct Answer: 1

Explanation

A DEFAULT constraint supplies a predefined value when an INSERT operation does not provide a value for the associated column. For example, a column could automatically receive the current date or a status such as Active. Defaults help simplify application code and maintain consistent data. However, a default is not applied in every possible situation, such as when an explicit NULL is supplied to a nullable column. Database administrators should understand the exact insert behavior.

Question 196

Which SQL Server command can change the structure of an existing table?

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

Correct Answer: 2

Explanation

ALTER TABLE is used to modify the structure of an existing table. It can be used for operations such as adding or removing columns and changing certain constraints or definitions. Structural changes should be planned carefully because they can affect applications, indexes, constraints, and dependencies. On large production tables, some schema changes can also require significant resources or cause blocking, so administrators should test and schedule important changes appropriately.

Question 197

Which SQL Server command removes a database object such as a table from the database?

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

Correct Answer: 1

Explanation

The DROP statement removes a database object from the database. For example, DROP TABLE removes a table and its associated data and dependent structure where applicable. DROP is different from DELETE, which removes rows while keeping the table structure. Because DROP can permanently remove an object, administrators should verify dependencies and backups before executing it in production. Accidental DROP operations can cause significant data loss if recovery options are limited.

Question 198

Which SQL Server command removes rows from a table while keeping the table structure?

1. DELETE
2. DROP
3. ALTER
4. CREATE

Correct Answer: 1

Explanation

The DELETE statement removes rows from a table while preserving the table itself. A WHERE clause can be used to limit which rows are deleted. Without a WHERE clause, all rows in the table can be deleted. DELETE is therefore very different from DROP, which removes the table object itself. Administrators should carefully verify DELETE statements before running them, especially against production databases, and should use transactions when appropriate.

Question 199

Which SQL Server command removes all rows from a table quickly by deallocating the data pages used by the table?

1. TRUNCATE TABLE
2. DELETE
3. DROP COLUMN
4. REMOVE TABLE

Correct Answer: 1

Explanation

TRUNCATE TABLE removes all rows from a table by deallocating the data pages used by the table. It is generally more efficient than deleting every row individually and uses fewer transaction log resources for the row removal operation. TRUNCATE has restrictions compared with DELETE, including limitations involving foreign key relationships. It also cannot use a WHERE clause. Administrators should verify that all data should be removed before executing TRUNCATE.

Question 200

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

1. CREATE DATABASE
2. NEW DATABASE
3. ADD DATABASE
4. BUILD DATABASE

Correct Answer: 3

Explanation

The CREATE DATABASE statement creates a new SQL Server database and can include options for files, file sizes, growth settings, and other configuration values. Administrators can use it to create databases manually or as part of automated deployment processes. Database creation should consider storage location, initial size, growth configuration, security, backup requirements, and workload expectations. In Azure SQL Database, database provisioning is handled through Azure management tools and supported database creation methods.