ERASED TEST, YOU MAY BE INTERESTED ON
DBMS TEST by octopeer 🐙
COMMENTS |
STATISTICS |
RECORDS
|
|---|
TAKE THE TEST
|
Title of test:
DBMS TEST by octopeer 🐙 Description: do the test with a happy face Author: octopeer Other tests from this author Creation Date: 06/01/2024 Category: Others Number of questions: 50 |
Share the Test:
New Comment
No comments about this test.
Content:
|
Which is fundamental unit of data storage in SQL server?
Partition Page Extent Row. When the data row is too large to store in one page, where will the SQL server store the overflow data? ROW_OVERFLOW_DATA IN_ROW_DATA LOB_DATA IN_OVERFLOW_DATA. Choose the correct action when a page is modified in the buffer cache. Changes are written to the data file and the log cache at the same time. Changes are immediately written to the log cache as log records. Changes are immediately written to the datafiles without writing to the log cache. Changes are immediately written to the datafiles before writing in the log cache. Select false statement about SQL server data files Data File header page is the first page that contains information about the attributes of the file. The data files consist of only one primary file and can have many secondary files. The main data file contains only the database structures and the secondary files contain the data of the database. Log files contain information that is used for transaction recovery purposes. Which statement is correct about the functions of file groups Filegroups do not use a proportional fill strategy across all the files within each filegroup. There are two filegroup types in SQL server 2019. Allocation for system objects and tables always in the default filegroup. Filegroups can be created to group data files together for administrative, data allocation, and placement purposes. Choose the wrong statement about pages and extents architecture Rows can span pages. There are two types of extents is uniform extents and mixed extents Page is the fundamental unit of data storage and same size - 8 KB Extent is a collection of eight physically contiguous pages. Choose the correct statements about the transaction log All statements are true. Tail of the log is section of log file from first log record to the last-written log record that must be present for a successful database-wide rollback. Log records record the before and after images of the modified data. Each new log record is written to the logical end of the log. Which checkpoint task is wrong? Checkpoints flush dirty data pages from the buffer cache of the current database to disk Checkpoints do not write dirty log pages to disk. The checkpoint records contain a list of all the active transactions that have modified the database. Checkpoints writes a record marking the end of the checkpoint to the log file. SQL Server 2019 databases have three kinds of file types associated with the Data files, Transaction log files, and SNAPSHOT files Data files, Transaction log files, and FILESTREAM files Data files, Transaction log files, and Index files Data files, Transaction log files, and FILEGROUPS. Select true Log Truncate operations. Deletes inactive virtual log files Inactive portion of the transaction log is marked as reusable All of mentions Before the log can be truncated, a checkpoint operation must occur. Which statement is false about data partition? There are two types of partitions: horizontal partitioning and vertical partitioning Each partition can be stored on a separate filegroup When querying partitioned data table always ask to read all partitions Partitioning is a performance optimization for large tables and indexes that splits the object horizontally into smaller units. Which statement is wrong about partition key? Partitioning key used to determine in which partition each row of the table should be placed. The partition key is not required to be a subset of the clustered index key. The columns in the partition key are the columns that queries will use as filter criteria. Partitioning Key can be a computed column. What is the result of the partition function below? CREATE PARTITION FUNCTION (Date) AS RANGE LEFT FOR VALUES ('2017-12-31', '2018-12-31', '2019-12-31'); 3 partitions including >='2017-12-31' and <'2018-12-31' ; >='2018- 12-31' and <'2019-12-31' and >='2019-12-31' 4 partitions including <='2017-12-31' ; >'2017-12-31' and <='2018- 12-31' ; >'2018-12-31' and <'2019-12-31' ; > '2019-12-31' 2 partitions including >='2017-12-31' and <'2018-12-31' ; >='2018- 12-31' and <'2019-12-31' 4 partitions including <'2017-12-31' ; >='2017-12-31' and <'2018-12- 31' ; >='2018-12-31' and <'2019-12-31' ; >= '2019-12-31'. Which statement is false about Memory-Optimized Tables? Data in memory-optimized tables is stored as data rows in data pages A memory-optimized table can be durable or can be non-durable. All changes to memory-optimized tables are stored by appending to active files All memory-optimized tables are mapped to a memory-optimized data filegroup. What is the purpose of index in SQL server? All of the selects are true. It leads to provide an index to a record. It leads to enhance the query performance. It leads to perform fast searches. Which one is true about clustered index? Clustered index key is only a single column that enforce uniqueness of each row in the table A data table can have many cluster indexes Clustered index is a Heap structure, SQL Server can determine the pages of the table is by reading the IAM pages Data pages of a table to be logically stored in the order of the clustered index key. Which is true about Nonclustered Indexes? All of the selects are true. A data table can have many Nonclustered indexes. Index key of the nonclustered index includes all columns that need to be accessed during a query, then SQL Server do not need to access the underlying table. Leaf level of a nonclustered index contains pointers to the data pages of the table. How Non clustered indexes points to the data? It never points to anything. It directly points to data row. It is used for pointing data rows containing key values. None of the above. Which statement is false about Columnstore Indexes? Columnstore index slices the rows of a table into chunk (called a rowgroup). Query is able to retrieve just the data pages of the column it requires. Each column segment contains a header with metadata about the data within the segment. Columnstore indexes store rows of data on data pages, known as a rowstore. Which of the following is not a job of a DBA? Performing upgrades of the database and software to new release levels. Managing the database's storage structures. Managing users and security. Device Management. Which 3-layer architecture describes how the data is stored in the database? External Level Internal Level Conceptual Level Physical Level. Which of the following is not an advantage of DBMS? Complexity Increased concurrency Sharing of data Improved security. Which of the following is not a task of a DBMS? Authorization Services Create database, control write data to data files, access data Backup and Restore Services Control read and write storage devices. What type of views provide an internal, system table-independent view of the SQL Server metadata? Information schema views Management views System Views Dynamic management views. What type of views provide server state information that can be used to monitor the health of the server instance, diagnose problems, and tune performance? Information schema views System Views Management views Dynamic management views. Employee_id is primary key of employees table. There are 2 sessions below. Execute session 1 before, then execute session 2. What will happen? Select one: Repeatable Read Phantom read Read Committed Dead lock. _________________ allow concurrent transactions to read (select) a resource Update locks All of the mentioned Exclusive locks Shared locks. Which statement is false about Optimistic Isolation Levels? Optimistic Isolation Levels use a technique called row versioning. There are two levels of optimistic isolation Maintaining a new copy of a row in TempDB for uncommitted transactions every time the row is updated. Acquiring locks for either read or write operations. . ____ isolation level not only acquiring locks for write operations but also by acquiring key-range locks for read Repeatable Read Serializable Read Committed Read Uncommitted. _____ occurs when a transaction reads the same row twice but receives different results each time nonrepeatable read dirty read phantom read clean read. There has a transaction that attempts to select data from a table. The query takes a very long time. What is the MOST LIKELY reason why this would happen? Please select the best answer. A shared lock was already present An exclusive lock was already present An intent lock was already present A schema lock was already present. _________ contains data from only some of the filegroup in a database copy-only backup database backup data backup parial. Backup can occur while the database is in_______ state Online Offline Restoring All of the mentioned. Table-level backups can be created in SQL Server True False. Following recovery model gives you the most protection against data loss Full recovery Bulk-logged Simple All of selects. DBA of ABC company perform the following tasks: Taking a full backup once per week, at 02:00 on a Sunday. Taking differential backup on a nightly basis at 01:00 Taking transaction log backups every half hour. At 01:15 on a Tuesday, server was crashed and database was failures. Which of the following actions does the DBA perform to restore the entire database? Select one: Restore full backup; all differential backups; all log backups. Only restore full backup Restore full backup, differential backup on Tuesday. Restore full backup; differential backups on Monday,Tuesday; log backups on Tuesday. Which backup strategy restores the database to point in time just before a disaster occurred. Full Backup Only Full and Transaction Log Backups Partial Backup Filegroup Backups. Which of the following describes differential backup? A differential backup backs up all the information on the disk to an offline device such as tape A differential backup backs up only the information that is added since previous differential backup A differential backup backs up only the information that is added Which backup strategy restores the database to point in timed since last full backup A differential backup backs up only the information that is added since last log backup. Which recovery model does not allow transaction log backups FULL Recovery Model BULK LOGGED Recovery Model SIMPLE Recovery Model All models allow transaction log backups. Which of the following is not a disadvantage of Cell-Level Encryption compared to Transparent Data Encryption Implementing cell-level encryption is a manual process (make code changes to applications) Performance overhead larger than the performance overhead associated with TDE Size of the data is much larger after the data has been encrypted Cell-Level Encryption allows to encrypt a single column or even specific cells from a column. DBA can generate multiple Service Master Keys for each instance in SQL Server True False. Choose the correct statement about Transparent Data Encryption A. Database encrypted with TDE is the different size as it was before it was encrypted Application developers need to modify their code to access the data It is necessary to create a Database Master Key, Certificate when performing Transparent Data Encryption Transparent Data Encryption encrypts data pages and log file of a database using Database Master Key. Choose the correct statement about Always Encrypted Column master key is used to encrypt the column encryption keys Column master key is stored in an external key store The client driver responsible for the encryption and decryption of data All statements are correct. Choose the wrong statement about the schema A. Schemas is logical namespace for database objects Schemas help to simplify the management of permissions Objects in different schemas cannot have the same name Create table without specifying schema then table will be created in dbo schema. Security at the level of the individual database includes Login Only database users Database users and database roles Login, database users and database roles. Which statement is wrong about Fixed server roles? A. Fixed server roles allow to assign database-level permissions Fixed server roles allow to assign instance-level permissions Sysadmin is fixed server roles that perform all operations on the server Fixed server roles can be assigned to login. Which fixed server roles allow create, alter, drop and restore any databases? serveradmin securityadmin processadmin dbcreator. How many authentication modes are there in SQL Server? 3 modes: Windows authentication. User authentication and Mixed mode authentication 2 modes: Windows authentication and Mixed-mode authentication 1 mode: User authentication 2 modes: Windows authentication and User authentication. Choose the wrong statement related to Clustered Columnstore Indexes A. A row is deleted is not located in a deltastore, then row is physically removed. Updating a row marks the row as being logically deleted and inserts a new row into a deltastore. Row being deleted is located in a deltastore, then it is immediately deleted, both logically and physically. New rows are inserted into the table may temporarily be placed into deltastore. Select the reasons for the occurrence of Index Fragmentation Misuse of the FILLFACTOR and PAD_INDEX settings. DELETE statements are issued All of selects DML statements occur and performing a page split. |
Report abuse