Which recovery model does not allow transaction log backups?
Mô hình khôi phục nào không cho phép sao lưu nhật ký giao dịch SIMPLE Recovery Mode FULL Recovery Model All models allow transaction log backups BULK LOGGED Recovery Model.
Different between Snapshot Isolation and Read Committed Snapshot Isolation is
Select one:
Sự khác biệt giữa Cách ly ảnh chụp nhanh và Cách ly ảnh chụp nhanh đã cam kết là
Snapshot Isolation writes pessimistic and Read Committed Snapshot Isolation writes optimistic Snapshot Isolation writes pessimistic and Read Committed Snapshot Isolation writes optimistic
Snapshot Isolation writes optimistic and Read Committed Snapshot Isolation writes optimistic
Snapshot Isolation writes optimistic and Read Committed Snapshot Isolation writes pessimistic.
__________occurs when a transaction reads the same row twice but receives different results each time..
Select one:
__________xảy ra khi một giao dịch đọc cùng một hàng hai lần nhưng mỗi lần nhận được kết quả khác nhau..
dirty read
nonrepeatable 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.
Select one:
Có một giao dịch cố gắng chọn dữ liệu từ một bảng. Truy vấn mất một thời gian rất dài. Lý do LỚN NHẤT khiến điều này xảy ra là gì? Hãy chọn câu trả lời đúng nhất.
Chọn một: An exclusive lock was already present
A shared lock was already present A schema lock was already present. An intent lock was already present.
_______isolation level not only acquiring locks for write operations but also by acquiring key-range locks for read
Select one:
_____ mức độ cô lập không chỉ thu được các khóa cho thao tác ghi mà còn bằng cách thu được các khóa trong phạm vi khóa để đọc
Repeatable Read
Serializable
Read Committed
Read Uncommitted.
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.
________allow concurrent transactions to read (select) a resource
Exclusive locks
Update locks
All of the mentioned
Shared locks.
Employee_id is primary key of employees table. There are 2 sessions below.
Session 1 :
BEGIN TRANSACTION
SELECT * FROM employees WITH (HOLDLOCK)
WHERE employee_id=100
WAITFOR DELAY '00:00:10'
UPDATE employees
SET salary = 10000
WHERE employee_id=101
Session 2:1
BEGIN TRANSACTION
SELECT * FROM employees WITH (HOLDLOCK)
WHERE employee_id=101
WAITFOR DELAY '00:00:10'
UPDATE employees
SET salary = 5000
WHERE employee_id=100 Repeatable Read
Dead lock
Phantom read
Read Committed.
The Update (U) lock is being placed on data row A, then another transaction requests a lock on this row and the lock succeeds. Choose the correct type of lock required
Khóa Cập nhật (U) đang được đặt trên hàng dữ liệu A, sau đó một giao dịch khác yêu cầu khóa trên hàng này và khóa thành công. Chọn đúng loại khóa cần thiết Exclusive Lock {X)
Shared Intent Key (IS)
Update Key (U)
Intent Exclusive Key (IX).
The Exclusive (X) lock is being placed on data row B, then another transaction requests a lock on this row. Select the required lock type for success
Khóa Độc quyền (X) đang được đặt trên hàng dữ liệu B, sau đó một giao dịch khác yêu cầu khóa trên hàng này. Chọn loại khóa cần thiết để thành công
No any successful request locks
Share Lock (S)
Intent Exclusive Key (IX)
Shared Intent Key (IS).
Choose the incorrect statement about Optimistic Isolation Levels
Use a technique called row versioning
Maintaining a new copy of a row in TempDB
Not acquiring lock for read operation but acquiring lock for write operation
Dramatically reduce contention on highly concurrent systems.
Select the reasons for the occurrence of Index Fragmentation DML statements occur and performing a page split. Misuse of the FILLFACTOR and PAD_INDEX settings. DELETE statements are issued All of the selects.
Which statement is false about Columnstore Indexes Columnstore index slices the rows of a table into chunk (called a rowgroup) Columnstore indexes store rows of data on data pages, known as a rowstore. Each column segment contains a header with metadata about the data within the segment Query is able to retrieve just the data pages of the column it requires.
Which backup strategy restores the database to point in time just before a disaster occurred. Filegroup Backups Full Backup Only Full and Transaction Log Backups Partial Backup.
What is the purpose of index in SQL server? It leads to perform fast searches. It leads to enhance the query performance. All of the selects are true. It leads to provide an index to a record.
When the data row is too large to store in one page, where will the sql server store the overflow data?
Khi hàng dữ liệu quá lớn không thể lưu trữ trong một trang, máy chủ sql sẽ lưu trữ dữ liệu tràn ở đâu? LOB_DATA IN_OVERFLOW_DATA IN_ROW_DATA ROW_OVERFLOW_DATA.
Which of the following is not an advantage of DBMS? Complexity Increased concurrency Sharing of data Improved security.
DBA can generate multiple Service Master Keys for each instance in Sql Server
DBA có thể tạo nhiều Khóa dịch vụ chính cho từng phiên bản trong Máy chủ Sql False TRUE.
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?
Loại chế độ xem nào cung cấp thông tin trạng thái máy chủ có thể được sử dụng để theo dõi tình trạng của phiên bản máy chủ, chẩn đoán sự cố và điều chỉnh hiệu suất? Information schema views System Views Management views Dynamic management views.
____________ occurs when a transaction reads the same row twice but receives different results each time.. |
____________ xảy ra khi một giao dịch đọc cùng một hàng hai lần nhưng mỗi lần nhận được kết quả khác nhau.. clean read phantom read nonrepeatable read dirty read.
Choose the correct action when a page is modified in the buffer cache
Chọn hành động chính xác khi một trang được sửa đổi trong bộ đệm đệm 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. Changes are written to the data file and the log cache at the same time.
Which statement is wrong about Fixed server roles? Fixed server roles can be assigned to login 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.
SQL Server 2019 databases have three kinds of file types associated with the
Cơ sở dữ liệu SQL Server 2019 có ba loại tệp được liên kết với Data files, Transaction log files and FILEGROUPS Data files, Transaction log files and SNAPSHOT files Data files, Transaction log files and FILESTREAM files Data files, Transaction log files and Index files.
Select true Log Truncate operations.
Chọn các hoạt động Cắt bớt nhật ký thực sự. All of mentions inactive portion of the transaction log is marked as reusable
phần không hoạt động của nhật ký giao dịch được đánh dấu là có thể sử dụng lại deletes inactive virtual log files
xóa các tệp nhật ký ảo không hoạt động before the log can be truncated, a checkpoint operation must occur.
trước khi có thể cắt bớt nhật ký, phải thực hiện thao tác điểm kiểm tra.
Following recovery model gives you the most protection against data loss
Mô hình khôi phục sau mang đến cho bạn sự bảo vệ tốt nhất khỏi mất dữ liệu Simple All of selects Bulk-logged Full recovery.
Table-level backups can be created in SQL Server True False.
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 crashe and database was failures. Which of the following actions does the DBA perform to restore the entire database Only restore full backup Restore full backup; all differential backups; all log backups. Restore full backup, differential backup on Tuesday. Restore full backup; differential backups on Monday,Tuesday; log backups on Tuesday.(.
Security at the level of the individual database includes
Bảo mật ở cấp độ cơ sở dữ liệu cá nhân bao gồm Login Only database users Login , database users and database roles Database users and database roles.
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. Partitioning is a performance optimization for large tables and indexes that splits the object horizontally into smaller units When querying partitioned data table always ask to read all partitions.
Choose the correct statements about the transaction log 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 Each new log record is written to the logical end of the log. All statements are true. Log records record the before and after images of the modified data.
Which statement is wrong about partition key? Partitioning key used to determine in which partition each row of the table should be placed. Partitioning Key can be a computed column 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.
Which statement is correct about the functions of file groups Allocation for system objects and tables always in the default filegroup There are two filegroup types in sql server 2019. Filegroups do not use a proportional fill strategy across all the files within each filegroup. Filegroups can be created to group data files together for administrative, data allocation, and placement purposes.
Choose the correct statement about Always Encrypted The client driver responsible for the encryption and decryption of data Column master key is stored in an external key store. Column master key is used to encrypt the column encryption keys. All statements are correct.
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 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 The data files consist of only one primary file and can have many secondary files.
How Non Clustered indexes points to the data? It never points to anything. It is used for pointing data rows containing key values. It directly points to data row. None of the above.
_________________ allow concurrent transactions to read (select) a resource Update locks All of the mentioned Exclusive locks Shared locks.
____________ contains data from only some of the filegroups in a database. copy-only backup data backup partial database backup.
Which statement is true about Transparent Data Encryption Transparent Data Encryption encrypts data pages and log file of a database using Database Master Key. 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. Database encrypted with TDE is the different size as it was before it was encrypted.
Which 3-layer architecture describes how the data is stored in the database?
Kiến trúc 3 lớp nào mô tả cách dữ liệu được lưu trữ trong cơ sở dữ liệu? External Level Internal Level Conceptual Level Physical level.
Which of the following is not a disadvantage of Cell-Level Encryption compared to Transparent Data Encryption Cell-Level Encryption allows to encrypt a single column, or even specific cells from a column. Performance overhead lager than the performance overhead associated with TDE Implementing cell-level encryption is a manual process (make code changes to applications) Size of the data is much larger after the data has been encrypted.
Which statement is false about Memory-Optimized Tables? Data in memory-optimized tables is stored as data rows in datapages A memory-optimized table can be durable or can be non-durable. All memory-optimized tables are mapped to a memory-optimized data-filegroup All changes to memory-optimized tables are stored by appending to active files.
What type of views provide an internal, system table-independent view of the SQL Server metadata?
Loại chế độ xem nào cung cấp chế độ xem nội bộ, độc lập với bảng hệ thống của siêu dữ liệu SQL Server? Information schema views Management views System Views Dynamic management views.
Which checkpoint task is wrong? Checkpoints flush dirty data pages from the buffer cache of the current database to disk Checkpoints writes a record marking the end of the checkpoint to the log file. The checkpoint records contain a list of all the active transactions that have modified the database. Checkpoints do not writes dirty log pages to disk.
Which fixed server roles allow create,alter,drop and restore any databases? serveradmin processadmin dbcreator securityadminchọn.
Employee_id is primary key of employees table.There are 2 sessions below
HINHF HINFH
Repeatable Read Phantom read Read Committed Dead lock.
Which of the following describes differential backup? A differential backup backs up only the information that is added since last log 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 adde Which backup strategy restores the database to point in timed since last full backup.
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.
Choose the wrong statement about the schema Schemas help to simplify the management of permissions Create table without specifying schema then table will be created in dbo schema Schemas is logical namespace for database objects Objects in different schemas cannot have the same name.
Which is fundamental unit of data storage in sql server? Page Row Extent Partition.
How many authentication modes are there in Sql Server?
Có bao nhiêu chế độ xác thực trong Sql Server? 3 modes: Windows authentication, User authentication and Mixed-mode authentication 2 modes: Windows authentication and Mixed-mode authentication. 2 modes: Windows authentication and User authentication. 1 mode : User authenticatiz.
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
Có một giao dịch cố gắng chọn dữ liệu từ một bảng. Truy vấn mất rất nhiều thời gian. Lý do LỚN NHẤT khiến điều này xảy ra là gì? Hãy chọn câu trả lời đúng nhất A schema lock was already present. An exclusive lock was already present An intent lock was already present. A shared lock was already present.
Which recovery model does not allow transaction log backups SIMPLE Recovery Model BULK LOGGED Recovery Model FULL Recovery Model All models allow transaction log backups.
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.
Choose the wrong statement related to Clustered Columnstore Indexes A row is deleted is not located in a deltastore, then row is physically removed. New rows are inserted into the table may temporarily be placed into deltastore Row being deleted is located in a deltastore, then it is immediately deleted, both logically and physically Row being deleted is located in a deltastore, then it is immediately deleted, both logically and physicallya.
Choose the wrong statement about pages and extents architecture
Chọn phát biểu sai về kiến trúc trang và phạm vi
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 Rows can span pages.
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.
Select one:
An exclusive lock was already present A shared lock was already present A schema lock was already present. An intent lock was already present.
Which statement is true about Transparent Data Encryption It is necessary to create a Database Master Key, Certificate when performing Transparent Data Encryption 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. Transparent Data Encryption encrypts data pages and log file of a database using Database Master Key.
Which of the following is not a disadvantage of Cell-Level Encryption compared to Transparent Data Encryption 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 Implementing cell-level encryption is a manual process (make code changes to applications). Performance overhead lager than the performance overhead associated with TDE.
Which statement is false about data partition?
Câu nào sai về phân vùng dữ liệu? Each partition can be stored on a separate filegroup. Partitioning is a performance optimization for large tables and indexes that splits the object horizontally into smaller units. There are two types of partitions: horizontal partitioning and vertical partitioning When querying partitioned data table always ask to read all partitions.
Select the reasons for the occurrence of Index Fragmentation
Chọn lý do xảy ra hiện tượng Phân mảnh chỉ mục DELETE statements are issued Misuse of the FILLFACTOR and PAD_INDEX settings. DML statements occur and performing a page split. All of selects.
Which statement is wrong about partition key? The columns in the partition key are the columns that queries will use as filter criteria. Partitioning Key can be a computed column 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.
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'); 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' 4 partitions including <='2017-12-31' ; >'2017-12-31' and <='2018-12-31' ; >'2018-12-31' and <'2019-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'.
Which statement is false about Memory-Optimized Tables? A memory-optimized table can be durable or can be non-durable. Data in memory-optimized tables is stored as data rows in data pages 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? It leads to provide an index to a record. It leads to enhance the query performance. It leads to perform fast searches. All of the selects are true.
Which one is true about clustered index? A data table can have many cluster indexes Clustered index key is only a single column that enforce uniqueness of each 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 Leaf level of a nonclustered index contains pointers to the data pages of the table 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.
Choose the wrong statement related to Clustered Columnstore Indexes New rows are inserted into the table may temporarily be placed into deltastore 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.
Which statement is false about Columnstore Indexes 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 index slices the rows of a table into chunk (called a rowgroup) Columnstore indexes store rows of data on data pages, known as a rowstore.
Program-data dependence is caused by: Sự phụ thuộc dữ liệu chương trình được gây ra bởi. file descriptions being stored in each database application. Data descriptions being stored on a server. Data descriptions being written into programming code. data cohabiting with programs.
Because applications are often developed independently in file processing systems:
Bởi vì các ứng dụng thường được phát triển độc lập trong các hệ thống xử lý file: the data is always non-redundant. unplanned duplicate data files are the rule rather than the exception. Data can always be shared with others. There is a large volume of file I/O.
A(n) is often developed by identifying a form or report that a user needs on a regular basis. enterprise view reporting document User view user snapshot.
A user view is: A) what a user sees when he or she looks out the window. B) a table or set of tables. C) a logical description of some portion of the database. D) a procedure stored on the server.
Backup can occur while the database is in ___________ state.
a.Offline
b.Restoring
c.All of the mentioned
dOnline.
|
