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

 

Question 41

Which SQL Server object is commonly used to store a precompiled set of SQL statements and procedural logic?

1. View
2. Stored procedure
3. Index
4. Synonym

Correct Answer: 2

Explanation

A stored procedure is a database object that contains one or more SQL statements and can include procedural logic. It can accept parameters, perform data modifications, return results, and handle conditional logic. Stored procedures are useful for centralizing database operations and reducing repeated SQL code in applications. They can also help control permissions because users may be given permission to execute a procedure without receiving direct access to underlying tables. Good parameter handling is important for security and reliability.

Question 42

Which SQL Server command can display the definition of a stored procedure?

1. sp_helptext
2. showprocedure
3. procedure_definition
4. GET PROCEDURE

Correct Answer: 1

Explanation

sp_helptext can display the text definition of programmable objects such as stored procedures, views, and functions when the caller has appropriate permissions. It is useful when administrators need to inspect existing database logic. The procedure definition can help identify joins, filters, parameters, and other operations that may affect behavior or performance. Administrators should avoid changing production code directly without proper testing and change management.

Question 43

Which SQL Server object can return a scalar value or a table based on parameters and defined logic?

1. Trigger
2. Function
3. Constraint
4. Index

Correct Answer: 2

Explanation

A SQL Server function is a programmable database object that returns a value or a table. Scalar functions return a single value, while table-valued functions return a result set that can be queried like a table. Functions are useful for reusable calculations and data transformations. However, some functions can introduce performance overhead when used extensively, especially in large queries. Administrators should understand how a function is executed and consider its impact when tuning database workloads.

Question 44

Which type of SQL Server function returns a table that can be used in a query’s FROM clause?

1. Scalar function
2. Table-valued function
3. Trigger function
4. Aggregate-only function

Correct Answer: 2

Explanation

A table-valued function returns a table result and can be referenced in a query much like a regular table or view. It can accept parameters and provide reusable query logic. Table-valued functions are useful when a query needs a parameterized result set. SQL Server supports inline table-valued functions and multi-statement table-valued functions, which have different performance characteristics. Choosing the appropriate type can help maintain both reusable database logic and good query performance.

Question 45

Which SQL Server command can display information about a database object such as its columns and related properties?

1. sp_help
2. object_info
3. show_object
4. describe_object

Correct Answer: 1

Explanation

The sp_help system stored procedure provides information about database objects. Depending on the object, it can display details such as columns, data types, indexes, constraints, and other properties. It is useful when an administrator needs to quickly inspect an unfamiliar table or other database object. This can help during troubleshooting and development without requiring the administrator to manually query multiple system catalog views.

Question 46

Which system catalog view contains information about columns in database objects?

1. sys.columns
2. sys.fields
3. sys.table_columns
4. sys.object_fields

Correct Answer: 1

Explanation

The sys.columns catalog view contains metadata about columns for objects in the database. It can be joined with other catalog views, such as sys.tables and sys.objects, to obtain more detailed information. System catalog views are useful for database administration, auditing, documentation, and automation. Instead of relying only on graphical tools, administrators can query these views to inspect database metadata and create scripts that work consistently across environments.

Question 47

Which SQL Server catalog view provides information about tables in the current database?

1. sys.database_tables
2. sys.tables
3. sys.objects_tables
4. sys.tablelist

Correct Answer: 2

Explanation

The sys.tables catalog view provides information about user tables in the current database. It can be joined with other system catalog views to inspect columns, indexes, constraints, and related metadata. For example, administrators can query sys.tables when they need to find tables matching a naming pattern. System catalog views are especially useful for automation because they allow administrators to retrieve database structure using T-SQL rather than manually inspecting objects through a graphical interface.

Question 48

Which SQL Server command can show currently running requests and their sessions?

1. sp_who2
2. show_sessions
3. session_list
4. sp_running

Correct Answer: 1

Explanation

sp_who2 is a commonly used system stored procedure for viewing information about SQL Server sessions and processes. It can help administrators identify active sessions, blocking situations, login names, databases, and other details. Although modern monitoring methods may provide more detailed information, sp_who2 remains useful for quick investigation. When a database appears slow, checking active sessions and blocking can help determine whether another workload is causing delays.

Question 49

Which SQL Server dynamic management view can provide information about currently executing requests?

1. sys.dm_exec_requests
2. sys.dm_running_tasks
3. sys.dm_active_queries
4. sys.dm_query_sessions

Correct Answer: 1

Explanation

sys.dm_exec_requests provides information about requests currently executing in SQL Server. It can help administrators inspect request status, wait information, CPU time, elapsed time, and other details. This is useful when investigating long-running queries, blocking, or resource-related problems. Dynamic management views are powerful troubleshooting tools, but access may require appropriate permissions. Administrators often combine request information with session and SQL text information to understand what a workload is doing.

Question 50

What is blocking in SQL Server?

1. A failed backup
2. One session waiting because another session holds a conflicting lock
3. A deleted database
4. A disabled login

Correct Answer: 2

Explanation

Blocking occurs when one SQL Server session holds a lock that prevents another session from accessing a resource in the way it requires. The waiting session remains blocked until the conflicting lock is released or the situation changes. Blocking can be normal for short periods, but excessive or long-lasting blocking can cause application delays. Administrators should identify the blocking session, understand the transaction involved, and determine why the lock is being held for so long.

Question 51

Which SQL Server feature can automatically detect certain deadlock situations and provide diagnostic information?

1. Extended Events
2. Database diagrams
3. SQL Agent alerts only
4. Object Explorer

Correct Answer: 1

Explanation

Extended Events can capture deadlock information and provide useful details about sessions, resources, and statements involved. A deadlock occurs when two or more transactions wait for resources held by each other, creating a cycle that cannot continue. SQL Server detects the condition and chooses a transaction as the deadlock victim. Administrators can analyze the captured information to understand the cause and redesign transactions or access patterns to reduce the chance of repeated deadlocks.

Question 52

Which SQL Server isolation behavior allows a transaction to read data that another transaction has modified but not yet committed?

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

Correct Answer: 3

Explanation

READ UNCOMMITTED allows a transaction to read data that may not yet be committed by another transaction. This can result in dirty reads, meaning the application may see values that are later rolled back. The advantage is reduced locking and potentially better concurrency, but the data may not be reliable for important operations. Administrators should use this isolation level only when the application’s requirements can tolerate inconsistent reads.

Question 53

Which SQL Server feature provides row-versioned reads without requiring readers to block writers in many situations?

1. Snapshot isolation
2. Table locking
3. Exclusive locking
4. Schema binding

Correct Answer: 1

Explanation

Snapshot isolation uses row versions to provide transactions with a consistent view of data without requiring the same level of blocking between readers and writers as traditional locking-based approaches. SQL Server stores row versions in a version store so readers can access the appropriate version of a row. Snapshot-based approaches can improve concurrency, but they consume additional resources. Administrators should understand version-store usage and workload behavior before enabling or changing isolation settings.

Question 54

Which SQL Server feature can automatically maintain historical versions of rows for temporal table queries?

1. Temporal tables
2. History triggers only
3. Archive indexes
4. Versioned views

Correct Answer: 1

Explanation

SQL Server temporal tables can automatically maintain historical versions of rows. A system-versioned temporal table keeps current data and historical data so administrators can query how records looked at earlier points in time. This is useful for auditing, reporting, and tracking changes. SQL Server manages the history based on system time, reducing the need to build custom triggers for basic history tracking. Administrators should still plan storage because historical records can grow significantly over time.

Question 55

Which SQL Server feature can help protect sensitive column values by encrypting data in the database?

1. Always Encrypted
2. SQL Formatter
3. Query Encryption
4. Table Compression

Correct Answer: 1

Explanation

Always Encrypted is a SQL Server feature designed to protect sensitive data by keeping selected column values encrypted. Encryption and decryption can occur through supported client drivers, reducing the ability of database administrators or other unauthorized parties to view plaintext values. This is useful for sensitive information such as financial or personal data. Implementing Always Encrypted requires careful planning because encrypted columns can have limitations for searching, indexing, and supported operations.

Question 56

Which SQL Server security principle gives a user only the permissions needed to perform their job?

1. Open access
2. Least privilege
3. Shared administration
4. Full control

Correct Answer: 2

Explanation

Least privilege means giving users and applications only the permissions they actually need. This reduces the possible impact of compromised credentials, accidental changes, and misuse of database resources. In SQL Server, administrators can use roles and specific permissions rather than giving users broad administrative rights. For example, an application may need permission to execute certain stored procedures without needing direct access to every table. Least privilege is a core principle of secure database administration.

Question 57

Which SQL Server database role provides broad administrative permissions across the SQL Server instance?

1. db_datareader
2. db_owner
3. public
4. db_executor

Correct Answer: 2

Explanation

The db_owner role provides extensive permissions within a specific database, allowing members to perform most database-level operations. It should be assigned carefully because users in this role have much broader privileges than ordinary application users. The public role applies to all database users but has much more limited permissions by default. Roles such as db_datareader and db_datawriter provide narrower access. Using the least-privilege approach helps reduce unnecessary administrative access.

Question 58

Which SQL Server permission allows a user to execute a stored procedure?

1. EXECUTE
2. RUN
3. CALL
4. PROCESS

Correct Answer: 1

Explanation

The EXECUTE permission allows a user or role to execute programmable objects such as stored procedures and certain functions. Granting EXECUTE on a stored procedure can be part of a secure database design where applications interact with data through controlled procedures instead of receiving direct table permissions. This supports least privilege and can centralize business logic. Administrators should grant permissions at the narrowest practical scope rather than giving users unnecessary database-wide privileges.

Question 59

Which command can grant a SQL Server permission to a user or role?

1. ALLOW
2. GRANT
3. PERMIT
4. AUTHORIZE

Correct Answer: 2

Explanation

The GRANT statement gives a specified permission to a database principal such as a user or role. For example, GRANT SELECT ON dbo.Customers TO SalesRole allows members of SalesRole to read the Customers table. Permissions can be granted at different levels depending on the security design. Administrators should avoid granting broader permissions than necessary. Regular permission reviews are also important because users may accumulate access over time as their responsibilities change.

Question 60

Which SQL Server statement removes a previously granted permission from a user or role while leaving the principal available?

1. DENY
2. DELETE
3. REVOKE
4. REMOVE

Correct Answer: 3

Explanation

The REVOKE statement removes a previously granted or denied permission from a principal. It does not delete the user or role. DENY is different because it explicitly prevents a permission, and its behavior can take precedence over some grants depending on the security context. Administrators should understand the SQL Server permission hierarchy before changing access. Regularly reviewing and removing unnecessary permissions supports least privilege and reduces security risk.