Question 1: AUTO_CLOSE Database Option
What is the primary function of the AUTO_CLOSE option in a SQL Server database?
A) Automatically back up the database when it's not in use.
B) Automatically close the database when the last user disconnects.
C) Automatically increase the database size when it's full.
D) Automatically repair corrupt database files.
Answer:
B) Automatically close the database when the last user disconnects.
Question 2: AUTO_SHRINK Database Option
What is a consequence of enabling AUTO_SHRINK on a SQL Server database?
A) Improved database performance due to optimized storage.
B) Periodic reduction of database files size, potentially causing fragmentation.
C) Automatic backup of the database at regular intervals.
D) Increased security by encrypting idle database files.
Answer:
B) Periodic reduction of database files size, potentially causing fragmentation.
Question 3: Filegroups in SQL Server
What is a primary benefit of using multiple filegroups in a SQL Server database?
A) Enhanced database security through data encryption.
B) Reduced need for database backups.
C) Improved performance and data management.
D) Automatic compression of database files.
Answer:
C) Improved performance and data management.
Question 4: Partitioning in SQL Server
What is the main purpose of partitioning large tables in SQL Server?
A) To encrypt specific parts of the table for security.
B) To compress the table for efficient storage.
C) To improve performance and manageability by dividing the table into smaller pieces.
D) To automatically back up the table.
Answer:
C) To improve performance and manageability by dividing the table into smaller pieces.
Question 5: Full-Text Indexing
In SQL Server, what is the primary use of Full-Text Indexing?
A) To compress text data for efficient storage.
B) To enable advanced text search capabilities within the database.
C) To automatically translate text data into multiple languages.
D) To create backups of text data at regular intervals.
Answer:
B) To enable advanced text search capabilities within the database.
Question 6: Contained Databases
What is a key feature of a contained database in SQL Server?
A) The database can be easily replicated across multiple servers.
B) It relies solely on external resources for its metadata and user information.
C) The database includes all settings and metadata needed to define the database.
D) It automatically compresses data to save disk space.
Answer:
C) The database includes all settings and metadata needed to define the database.
Question 7: Data Compression
What does Page Compression in SQL Server primarily help with?
A) Encrypting data pages for enhanced security.
B) Reducing storage space requirements by compressing full data pages.
C) Increasing the speed of data retrieval from disk.
D) Automatically backing up data pages.
Answer:
B) Reducing storage space requirements by compressing full data pages.
Question 8: Deadlocking
What is a deadlock in SQL Server?
A) A scenario where a database lock is permanently stuck.
B) A situation where two or more transactions permanently block each other by each holding a
lock on a resource the other needs.
C) An error that occurs when the database runs out of memory.
D) A condition where a database transaction is rolled back due to a timeout.
Answer:
B) A situation where two or more transactions permanently block each other by each holding a
lock on a resource the other needs.
Question 9: SQL Server Profiler
What is the primary use of SQL Server Profiler?
A) To automate the backup process of databases.
B) To monitor, analyze, and troubleshoot database activities.
C) To compress large database files for efficient storage.
D) To encrypt database files for security purposes.
Answer:
B) To monitor, analyze, and troubleshoot database activities.
Question 10: FileStream Feature
What is the primary benefit of the FileStream feature in SQL Server?
A) It enhances the performance of SQL queries.
B) It allows storing and managing unstructured data (like files) in the SQL Server database.
C) It automatically encrypts file-based data for security.
D) It provides a built-in antivirus scanning for stored files.
Answer:
B) It allows storing and managing unstructured data (like files) in the SQL Server database.
Question 11: ALTER DATABASE Command
What does the following SQL Server command do?
ALTER DATABASE [SampleDB] SET READ_ONLY;
A) It deletes the database named SampleDB.
B) It renames the database SampleDB to READ_ONLY.
C) It sets the database SampleDB to read-only mode, preventing data modifications.
D) It creates a new read-only database named SampleDB.
Answer:
C) It sets the database SampleDB to read-only mode, preventing data modifications.
Question 12: DBCC SHRINKFILE Command
What is the purpose of the DBCC SHRINKFILE command in SQL Server?
A) To increase the size of a database file.
B) To repair corrupted database files.
C) To reduce the size of a database file.
D) To back up a database file to a specified location.
Answer:
C) To reduce the size of a database file.
Question 13: CREATE INDEX Command
Which of the following is a correct use of the CREATE INDEX command in SQL Server?
A) CREATE INDEX idx_name ON Table(Column1, Column2);
B) CREATE TABLE idx_name INDEX (Column1, Column2);
C) INDEX CREATE idx_name ON Table(Column1, Column2);
D) CREATE idx_name INDEX ON Table(Column1, Column2);
Answer:
A) CREATE INDEX idx_name ON Table(Column1, Column2);
Question 14: BACKUP DATABASE Command
What does the following command do in SQL Server?
BACKUP DATABASE [SampleDB] TO DISK = N'C:\Backups\SampleDB.bak';
A) It restores the SampleDB database from the specified backup file.
B) It creates a backup of SampleDB and stores it on disk at the specified path.
C) It moves the SampleDB database files to a new location on disk.
D) It checks the integrity of the SampleDB database backup file.
Answer:
B) It creates a backup of SampleDB and stores it on disk at the specified path.
Question 15: RECONFIGURE Command
In SQL Server, what is the purpose of the RECONFIGURE command following sp_configure?
A) To delete the configuration settings specified by sp_configure.
B) To display the current configuration settings.
C) To apply the configuration changes made by sp_configure.
D) To reset the server configuration to its default settings.
Answer:
C) To apply the configuration changes made by sp_configure.
Question 16: Dynamic SQL Execution
Which command is used to execute dynamic SQL in SQL Server?
A) EXECUTE sql_string;
B) RUN sql_string;
C) EXECUTE IMMEDIATE sql_string;
D) CALL sql_string;
Answer:
A) EXECUTE sql_string;
Question 17: Changing Database Recovery Model
What is the correct command to change the recovery model of a SQL Server database to
SIMPLE?
A) ALTER DATABASE SampleDB SET RECOVERY SIMPLE;
B) MODIFY DATABASE SampleDB RECOVERY SIMPLE;
C) CHANGE DATABASE SampleDB SET RECOVERY = 'SIMPLE';
D) UPDATE DATABASE SampleDB SET RECOVERY_MODE = SIMPLE;
Answer:
A) ALTER DATABASE SampleDB SET RECOVERY SIMPLE;
Question 18: Enabling Query Store
Which command enables the Query Store feature for a database in SQL Server?
A) ALTER DATABASE SampleDB SET QUERY_STORE = ON;
B) ENABLE QUERY_STORE ON DATABASE SampleDB;
C) ALTER DATABASE SampleDB SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
D) UPDATE DATABASE SampleDB SET QUERY_STORE_ENABLED = TRUE;
Answer:
C) ALTER DATABASE SampleDB SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
Question 19: Database Snapshot Creation
What is the correct syntax to create a database snapshot in SQL Server?
A) CREATE SNAPSHOT OF SampleDB AS SampleDB_Snapshot;
B) CREATE DATABASE SampleDB_Snapshot ON (NAME = SampleDB, FILENAME =
'SampleDB_Snapshot.ss');
C) CREATE DATABASE SampleDB_Snapshot AS SNAPSHOT OF SampleDB;
D) SNAPSHOT DATABASE SampleDB AS SampleDB_Snapshot;
Answer:
C) CREATE DATABASE SampleDB_Snapshot AS SNAPSHOT OF SampleDB;
Question 20: Advanced Index Rebuilding
Which command rebuilds an index in SQL Server and also updates the statistics with a
specified sample size?
A) ALTER INDEX ALL ON SampleTable REBUILD WITH (STATISTICS_SAMPLE = 10);
B) REBUILD INDEX SampleIndex ON SampleTable WITH FULLSCAN;
C) ALTER INDEX SampleIndex ON SampleTable REBUILD; UPDATE STATISTICS SampleTable
SampleIndex WITH SAMPLE 10 PERCENT;
D) ALTER INDEX SampleIndex ON SampleTable REORGANIZE WITH (STATISTICS_SAMPLE = 10
PERCENT);
Answer:
C) ALTER INDEX SampleIndex ON SampleTable REBUILD; UPDATE STATISTICS SampleTable
SampleIndex WITH SAMPLE 10 PERCENT;
Question 21: Impact of AUTO_SHRINK on Performance
What is a potential negative impact of enabling AUTO_SHRINK on a SQL Server database?
A) It can lead to increased fragmentation of the database files.
B) It encrypts the database, leading to slower access times.
C) It disables all database indexes, reducing query performance.
D) It significantly increases CPU usage due to continuous monitoring.
Answer:
A) It can lead to increased fragmentation of the database files.
Question 22: Considerations for Partitioning Large Tables
In SQL Server, what is a key consideration when partitioning large tables?
A) Partitioning always improves the performance of all types of queries.
B) Partitioning can simplify management and improve performance but may require careful
query tuning.
C) Partitioning is only effective for tables with BLOB data types.
D) Partitioning automatically archives old data to reduce table size.
Answer:
B) Partitioning can simplify management and improve performance but may require careful
query tuning.
Question 23: Full-Text Indexing Applicability
For which scenario is Full-Text Indexing most beneficial in SQL Server?
A) When you need to enforce foreign key constraints.
B) When you need to optimize performance for numeric calculations.
C) When you need to perform complex searches on text-based data.
D) When you need to reduce the physical storage of the database.
Answer:
C) When you need to perform complex searches on text-based data.
Question 24: FileStream Feature Usage
In which scenario is the FileStream feature particularly useful in SQL Server?
A) When storing and managing large numbers of small transactions.
B) When storing and managing large unstructured data, like documents or images.
C) When optimizing query performance on indexed columns.
D) When implementing row-level security on tables.
Answer:
B) When storing and managing large unstructured data, like documents or images.
Question 25: Impact of Contained Databases
What is a significant benefit of using contained databases in SQL Server?
A) They automatically encrypt all data, improving security.
B) They reduce database size through automatic compression algorithms.
C) They make database migration easier by isolating the database from the instance.
D) They increase query performance by optimizing the execution plan.
Answer:
C) They make database migration easier by isolating the database from the instance.
