MS SQL Server MCQs (Multiple-Choice Questions)

1. What is Microsoft SQL Server?

1. A relational database management system
2. A spreadsheet application
3. An operating system
4. A web browser

Answer: A) A relational database management system

Explanation:

Microsoft SQL Server is a relational database management system (RDBMS) used to store, retrieve, manage, and process structured data.

2. Which language is primarily used to interact with SQL Server databases?

1. HTML
2. T-SQL
3. CSS
4. XML

Answer: B) T-SQL

Explanation:

Transact-SQL (T-SQL) is Microsoft's extension of SQL and is the primary language used for querying and programming SQL Server.

3. Which SQL Server component is responsible for executing queries and managing database operations?

1. Database Engine
2. SQL Server Browser
3. SQL Server Agent
4. SQL Server Profiler

Answer: A) Database Engine

Explanation:

The SQL Server Database Engine provides the core services for storing, processing, and securing data and executing T-SQL queries.

4. Which tool provides a graphical environment for managing SQL Server and writing queries?

1. SQL Server Management Studio (SSMS)
2. Microsoft Paint
3. Windows Media Player
4. Microsoft Word

Answer: A) SQL Server Management Studio (SSMS)

Explanation:

SQL Server Management Studio (SSMS) is a Microsoft tool used to connect to SQL Server, manage databases, execute queries, and perform administrative tasks.

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

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

Answer: B) CREATE DATABASE

Explanation:

The CREATE DATABASE statement creates a new SQL Server database.

6. Which statement is used to change the current database context in a T-SQL session?

1. USE
2. CHANGE
3. SWITCH
4. OPEN

Answer: A) USE

Explanation:

The USE statement changes the database context for the current connection.

7. Which statement is used to retrieve data from a SQL Server table?

1. GET
2. FETCH TABLE
3. SELECT
4. READ

Answer: C) SELECT

Explanation:

SELECT retrieves rows and columns from one or more tables, views, or other rowset sources in SQL Server.

8. Which clause filters rows before grouping in a SELECT query?

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

Answer: B) WHERE

Explanation:

The WHERE clause filters individual rows before the grouping operation is performed.

9. Which clause is used to sort query results?

1. SORT BY
2. ORDER BY
3. GROUP BY
4. ARRANGE BY

Answer: B) ORDER BY

Explanation:

ORDER BY sorts the result set according to one or more expressions in ascending or descending order.

10. Which keyword removes duplicate rows from a SELECT result?

1. UNIQUE
2. DISTINCT
3. REMOVE
4. FILTER

Answer: B) DISTINCT

Explanation:

DISTINCT eliminates duplicate rows from the result set based on the selected columns.

11. Which clause groups rows having the same values in specified columns?

1. GROUP BY
2. ORDER BY
3. PARTITION BY
4. COLLECT BY

Answer: A) GROUP BY

Explanation:

GROUP BY combines rows into groups so aggregate functions such as COUNT, SUM, AVG, MIN, and MAX can be applied to each group.

12. Which clause filters groups after GROUP BY has been applied?

1. WHERE
2. HAVING
3. FILTER
4. QUALIFY

Answer: B) HAVING

Explanation:

HAVING filters grouped results, commonly using aggregate expressions such as COUNT or SUM.

13. What does COUNT(*) return?

1. The number of columns
2. The number of rows
3. The number of indexes
4. The number of tables

Answer: B) The number of rows

Explanation:

COUNT(*) counts rows in the result set, including rows containing NULL values in individual columns.

14. Which aggregate function calculates the arithmetic mean of numeric values?

1. MEAN()
2. AVG()
3. AVERAGE()
4. MIDDLE()

Answer: B) AVG()

Explanation:

AVG() calculates the arithmetic average of the non-NULL values in the specified expression.

15. Which function returns the largest value in a set?

1. TOP()
2. MAX()
3. LARGEST()
4. HIGH()

Answer: B) MAX()

Explanation:

MAX() returns the maximum value of an expression among the rows being evaluated.

16. Which function returns the smallest value in a set?

1. MIN()
2. LOW()
3. SMALLEST()
4. BOTTOM()

Answer: A) MIN()

Explanation:

MIN() returns the minimum value of an expression among the rows being evaluated.

17. Which statement adds new rows to a table?

1. ADD
2. INSERT
3. APPEND ROW
4. CREATE ROW

Answer: B) INSERT

Explanation:

The INSERT statement adds new rows to a table or another target that supports insertion.

18. Which statement modifies existing rows?

1. CHANGE
2. MODIFY
3. UPDATE
4. ALTER ROW

Answer: C) UPDATE

Explanation:

UPDATE changes values in existing rows that satisfy its search condition.

19. Which statement removes selected rows from a table?

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

Answer: B) DELETE

Explanation:

DELETE removes rows that satisfy its WHERE condition. Without a WHERE clause, it can remove all rows from the target table.

20. Which statement removes a table definition and its data?

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

Answer: C) DROP TABLE

Explanation:

DROP TABLE removes the table object and its associated data from the database.

21. Which join returns only rows that satisfy the join condition in both tables?

1. LEFT JOIN
2. FULL JOIN
3. INNER JOIN
4. CROSS JOIN

Answer: C) INNER JOIN

Explanation:

An INNER JOIN returns rows for which the join condition matches between the participating tables.

22. Which join returns all rows from the left table and matching rows from the right table?

1. LEFT JOIN
2. INNER JOIN
3. RIGHT JOIN
4. CROSS JOIN

Answer: A) LEFT JOIN

Explanation:

A LEFT JOIN returns all rows from the left input and matching rows from the right input. Unmatched right-side columns contain NULL values.

23. Which join returns all rows from both participating tables, matching them where possible?

1. INNER JOIN
2. FULL OUTER JOIN
3. LEFT JOIN
4. CROSS JOIN

Answer: B) FULL OUTER JOIN

Explanation:

A FULL OUTER JOIN returns matching rows plus unmatched rows from both sides, using NULLs where a matching row does not exist.

24. What does a CROSS JOIN produce?

1. Only matching rows
2. A Cartesian product of the input rowsets
3. Only unmatched rows
4. Only duplicate rows

Answer: B) A Cartesian product of the input rowsets

Explanation:

A CROSS JOIN combines every row from one input with every row from the other input.

25. Which physical join algorithm is commonly effective when a small input has an appropriate index on the join column?

1. Nested Loops
2. Cartesian Join
3. Union Join
4. Sort Join

Answer: A) Nested Loops

Explanation:

A Nested Loops join can be effective when one input is relatively small and the other input has an appropriate index that allows efficient matching.

26. Which operator combines the results of two queries and removes duplicate rows?

1. UNION
2. MERGE
3. COMBINE
4. JOIN

Answer: A) UNION

Explanation:

UNION combines the result sets of compatible queries and removes duplicate rows. UNION ALL retains duplicates.

27. Which operator combines query results while retaining duplicate rows?

1. UNION ALL
2. UNION DISTINCT
3. MERGE ALL
4. JOIN ALL

Answer: A) UNION ALL

Explanation:

UNION ALL concatenates compatible result sets without removing duplicate rows.

28. Which operator returns rows from the first query that are not returned by the second query?

1. EXCEPT
2. DIFFERENCE
3. MINUS ALL
4. NOT INSET

Answer: A) EXCEPT

Explanation:

EXCEPT returns distinct rows from the first query that are not also returned by the second query.

29. Which operator returns distinct rows common to both query results?

1. COMMON
2. INTERSECT
3. INNERSET
4. JOINSET

Answer: B) INTERSECT

Explanation:

INTERSECT returns distinct rows that occur in both result sets.

30. Which keyword is commonly used for pattern matching in SQL Server?

1. MATCH
2. LIKE
3. PATTERN
4. SEARCH

Answer: B) LIKE

Explanation:

LIKE performs pattern matching against character expressions, commonly using wildcards such as % and _.

31. In a LIKE pattern, what does the percent sign (%) represent?

1. Exactly one character
2. Zero or more characters
3. Only numeric characters
4. A space character

Answer: B) Zero or more characters

Explanation:

In SQL Server LIKE patterns, % matches a string of zero or more characters.

32. In a LIKE pattern, what does the underscore (_) wildcard represent?

1. Zero or more characters
2. Exactly one character
3. Only a numeric character
4. An entire word

Answer: B) Exactly one character

Explanation:

The underscore wildcard represents a single character in a LIKE pattern.

33. What does the IS NULL predicate test?

1. Whether a value is zero
2. Whether an expression evaluates to NULL
3. Whether a value is an empty string only
4. Whether a column contains duplicate values

Answer: B) Whether an expression evaluates to NULL

Explanation:

IS NULL checks whether an expression contains the SQL NULL value. Equality comparisons such as = NULL should not be used for this purpose.

34. Which function returns the first non-NULL expression from its arguments?

1. ISNULL
2. COALESCE
3. NULLIF
4. FIRSTNULL

Answer: B) COALESCE

Explanation:

COALESCE evaluates its expressions in order and returns the first one that is not NULL.

35. Which SQL Server function accepts two expressions and returns the replacement value when the first expression is NULL?

1. ISNULL
2. NULLIF
3. COALESCEONLY
4. REPLACE_NULL

Answer: A) ISNULL

Explanation:

ISNULL checks an expression and returns the replacement value when that expression evaluates to NULL.

36. Which function returns NULL when its two expressions are equal?

1. NULLIF
2. ISNULL
3. COALESCE
4. NULLMATCH

Answer: A) NULLIF

Explanation:

NULLIF returns NULL when its two expressions are equal; otherwise, it returns the first expression.

37. Which T-SQL function returns the current date and time of the SQL Server system?

1. NOW()
2. GETDATE()
3. CURRENT_TIME_ONLY()
4. SERVERDATE()

Answer: B) GETDATE()

Explanation:

GETDATE() returns the current database system date and time as a datetime value.

38. Which SQL Server function returns the current date and time with a datetime2 value and higher fractional-second precision than GETDATE()?

1. SYSDATETIME()
2. GETDATEONLY()
3. SERVERTIME()
4. CURRENTDATETIME()

Answer: A) SYSDATETIME()

Explanation:

SYSDATETIME() returns the current system date and time as a datetime2 value with greater fractional-second precision than GETDATE().

39. Which function can return a portion of a date such as year, month, or day?

1. DATEPART()
2. DATESECTION()
3. GETPART()
4. DATEXTRACTONLY()

Answer: A) DATEPART()

Explanation:

DATEPART() returns an integer representing the specified datepart of a date value.

40. Which function adds a specified number of date units to a date?

1. DATEADD()
2. ADD_DATE()
3. DATEPLUS()
4. INCREMENTDATE()

Answer: A) DATEADD()

Explanation:

DATEADD() adds a specified number of units such as days, months, or years to a date expression.

41. Which statement is used to create a table?

1. BUILD TABLE
2. CREATE TABLE
3. NEW TABLE
4. MAKE TABLE

Answer: B) CREATE TABLE

Explanation:

CREATE TABLE defines a new table and its columns, data types, and constraints.

42. Which constraint uniquely identifies each row in a table?

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

Answer: B) PRIMARY KEY

Explanation:

A primary key uniquely identifies rows in a table and does not allow NULL values in its key columns.

43. What is the purpose of a foreign key?

1. To enforce a relationship between related tables
2. To encrypt all table data
3. To automatically create indexes on every column
4. To generate random values

Answer: A) To enforce a relationship between related tables

Explanation:

A foreign key references a key in another table and helps enforce referential integrity between related data.

44. Which constraint prevents duplicate values in a column or set of columns?

1. UNIQUE
2. CHECK
3. DEFAULT
4. IDENTITY

Answer: A) UNIQUE

Explanation:

A UNIQUE constraint prevents duplicate key values in the constrained column or combination of columns.

45. Which constraint restricts values according to a Boolean expression?

1. CHECK
2. DEFAULT
3. UNIQUE
4. IDENTITY

Answer: A) CHECK

Explanation:

A CHECK constraint requires inserted or updated values to satisfy a specified logical condition.

46. What is the purpose of a DEFAULT constraint?

1. To supply a value when an insert does not provide one for the column
2. To make a column the primary key
3. To prevent all NULL values
4. To create a clustered index

Answer: A) To supply a value when an insert does not provide one for the column

Explanation:

A DEFAULT constraint supplies a predefined value when an INSERT statement does not specify a value for that column, subject to the column and statement conditions.

47. What does the IDENTITY property commonly provide?

1. Automatically generated numeric values for a column
2. Automatic encryption of a column
3. Automatic foreign-key creation
4. Automatic table deletion

Answer: A) Automatically generated numeric values for a column

Explanation:

The IDENTITY property generates numeric values according to a defined seed and increment. It is commonly used for surrogate key columns.

48. Which SQL Server data type is designed to store variable-length Unicode character data?

1. VARCHAR
2. NVARCHAR
3. CHAR
4. BINARY

Answer: B) NVARCHAR

Explanation:

NVARCHAR stores variable-length Unicode character data and is commonly used when Unicode text must be supported.

49. Which data type is appropriate for storing an exact numeric value with defined precision and scale?

1. FLOAT
2. DECIMAL
3. REALTEXT
4. VARCHAR

Answer: B) DECIMAL

Explanation:

DECIMAL, also known as NUMERIC, stores fixed-precision and fixed-scale numeric values.

50. Which SQL Server data type stores a Boolean-like value using 0, 1, or NULL?

1. BOOLEAN
2. BIT
3. BOOL
4. LOGICAL

Answer: B) BIT

Explanation:

SQL Server uses the BIT data type for Boolean-like values, commonly represented as 0 or 1, and it can also contain NULL.

51. What is a view in SQL Server?

1. A virtual table defined by a query
2. A physical backup file
3. A database server instance
4. A transaction log

Answer: A) A virtual table defined by a query

Explanation:

A view is a database object defined by a SELECT query. A standard view stores the query definition rather than an independent copy of the result data.

52. Which statement creates a view?

1. CREATE VIEW
2. MAKE VIEW
3. NEW VIEW
4. DEFINE VIEW ONLY

Answer: A) CREATE VIEW

Explanation:

CREATE VIEW defines a view based on a SELECT statement.

53. What is an indexed view?

1. A view with an index created on it after meeting SQL Server requirements
2. A view that cannot contain a SELECT statement
3. A view used only for backups
4. A temporary table

Answer: A) A view with an index created on it after meeting SQL Server requirements

Explanation:

An indexed view is a view for which an index has been created. SQL Server imposes specific requirements on the view definition and indexing process.

54. What is a stored procedure?

1. A named collection of T-SQL statements stored in the database
2. A physical database file
3. An index type
4. A transaction isolation level

Answer: A) A named collection of T-SQL statements stored in the database

Explanation:

A stored procedure is a programmable database object containing T-SQL statements that can be executed by name or through other mechanisms.

55. Which statement creates a stored procedure?

1. CREATE PROCEDURE
2. CREATE METHOD
3. MAKE PROCEDURE
4. NEW PROCEDURE

Answer: A) CREATE PROCEDURE

Explanation:

CREATE PROCEDURE defines a stored procedure in SQL Server.

56. Which keyword is used to declare a local T-SQL variable?

1. DECLARE
2. DEFINE
3. VAR
4. LOCALIZE

Answer: A) DECLARE

Explanation:

T-SQL uses DECLARE to define local variables, such as DECLARE @Total INT;.

57. Which syntax correctly declares an integer variable in T-SQL?

1. DECLARE @Count INT;
2. DECLARE Count AS INTEGER;
3. VAR @Count INTEGER;
4. CREATE @Count INT;

Answer: A) DECLARE @Count INT;

Explanation:

T-SQL local variables are prefixed with @ and are declared using DECLARE followed by the variable name and data type.

58. Which statement can assign a value to a T-SQL variable?

1. SET
2. ASSIGN ONLY
3. PUT
4. VALUE

Answer: A) SET

Explanation:

SET can assign a value to a local T-SQL variable, for example SET @Count = 10;.

59. Which SQL Server object can return a scalar value or a table result based on its definition?

1. User-defined function
2. Index
3. Constraint
4. Database file

Answer: A) User-defined function

Explanation:

SQL Server supports user-defined functions, including scalar-valued and table-valued functions.

60. Which type of user-defined function returns a table?

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

Answer: A) Table-valued function

Explanation:

A table-valued function returns a table result and can be used in queries as a table expression.

61. What is a trigger in SQL Server?

1. A special type of stored procedure that automatically executes in response to specified events
2. A database backup file
3. An index maintenance command
4. A query execution plan

Answer: A) A special type of stored procedure that automatically executes in response to specified events

Explanation:

Triggers are special programmable objects that execute automatically when supported events occur, such as INSERT, UPDATE, or DELETE operations.

62. Which trigger type executes after a DML operation has completed?

1. AFTER trigger
2. BEFORE trigger
3. PRE trigger
4. START trigger

Answer: A) AFTER trigger

Explanation:

An AFTER trigger executes after the triggering DML statement has successfully completed its relevant action.

63. Which trigger type is commonly used to intercept an INSERT, UPDATE, or DELETE operation before the operation completes?

1. INSTEAD OF trigger
2. AFTER trigger
3. BEFORE ONLY trigger
4. PRECOMMIT trigger

Answer: A) INSTEAD OF trigger

Explanation:

An INSTEAD OF trigger executes instead of the triggering operation, allowing custom logic to control what happens when the event occurs.

64. Which logical tables are available inside a DML trigger for accessing affected rows?

1. inserted and deleted
2. new and old only
3. current and previous
4. source and target

Answer: A) inserted and deleted

Explanation:

SQL Server DML triggers can use the inserted and deleted logical tables to access row versions associated with the triggering operation.

65. What is a Common Table Expression (CTE) in SQL Server?

1. A temporary named result set defined within the execution scope of a statement
2. A permanent physical table
3. A database backup
4. A type of index

Answer: A) A temporary named result set defined within the execution scope of a statement

Explanation:

A CTE is defined using WITH and provides a named query expression that can be referenced by the statement immediately following it. Recursive CTEs can also reference themselves.

66. Which keyword begins a Common Table Expression?

1. WITH
2. CTE
3. USING
4. DEFINE

Answer: A) WITH

Explanation:

A CTE begins with the WITH keyword, followed by the CTE name and its query definition.

67. What is a recursive CTE useful for?

1. Querying hierarchical or recursively related data
2. Creating database backups
3. Creating indexes automatically
4. Changing server authentication mode

Answer: A) Querying hierarchical or recursively related data

Explanation:

Recursive CTEs can repeatedly execute a query expression and are commonly used for hierarchical structures such as employee-manager relationships.

68. Which clause is used with window functions to define how rows are divided into groups for calculation?

1. PARTITION BY
2. GROUP WINDOW
3. WINDOW GROUP
4. SEGMENT BY

Answer: A) PARTITION BY

Explanation:

PARTITION BY divides rows into logical partitions within an OVER clause, allowing a window function to calculate results separately for each partition.

69. Which function assigns sequential row numbers within each partition?

1. ROW_NUMBER()
2. ROW_INDEX()
3. SEQUENCE_NUMBER()
4. NUMBER_ROWS()

Answer: A) ROW_NUMBER()

Explanation:

ROW_NUMBER() assigns a sequential integer to rows within each partition according to the ordering specified in its OVER clause.

70. What is a key difference between RANK() and ROW_NUMBER()?

1. RANK() can assign the same rank to tied rows, while ROW_NUMBER() assigns distinct sequential numbers
2. ROW_NUMBER() cannot use ORDER BY
3. RANK() can only be used with text columns
4. They always produce identical results

Answer: A) RANK() can assign the same rank to tied rows, while ROW_NUMBER() assigns distinct sequential numbers

Explanation:

RANK() gives tied rows the same rank and leaves gaps after ties, while ROW_NUMBER() assigns a distinct sequence number to each row according to the specified ordering.

71. Which function assigns the same rank to tied rows without leaving gaps in the ranking sequence?

1. DENSE_RANK()
2. ROW_NUMBER()
3. RANK_GAP()
4. GROUP_RANK()

Answer: A) DENSE_RANK()

Explanation:

DENSE_RANK() assigns the same rank to tied rows and does not leave gaps between ranking values.

72. Which clause defines the ordering of rows for a window function?

1. ORDER BY inside OVER()
2. SORT BY outside SELECT
3. WINDOW ORDER
4. RANK BY

Answer: A) ORDER BY inside OVER()

Explanation:

Window functions use ORDER BY within the OVER clause to define the logical order in which rows are evaluated.

73. Which statement starts an explicit transaction in T-SQL?

1. BEGIN TRANSACTION
2. START DATABASE
3. OPEN TRANSACTION
4. CREATE TRANSACTION

Answer: A) BEGIN TRANSACTION

Explanation:

BEGIN TRANSACTION starts an explicit transaction in T-SQL.

74. Which statement permanently commits the changes made by a transaction?

1. COMMIT TRANSACTION
2. SAVE TRANSACTION
3. APPLY TRANSACTION
4. END DATABASE

Answer: A) COMMIT TRANSACTION

Explanation:

COMMIT TRANSACTION makes the changes performed within the transaction permanent, subject to the transaction's execution context.

75. Which statement rolls back a transaction?

1. UNDO TRANSACTION
2. ROLLBACK TRANSACTION
3. REVERSE TRANSACTION
4. CANCEL DATABASE

Answer: B) ROLLBACK TRANSACTION

Explanation:

ROLLBACK TRANSACTION reverses the transaction's changes according to the transaction and savepoint scope.

76. Which statement creates a transaction savepoint?

1. SAVE TRANSACTION
2. CREATE SAVEPOINT
3. MARK TRANSACTION
4. CHECKPOINT TRANSACTION

Answer: A) SAVE TRANSACTION

Explanation:

SAVE TRANSACTION establishes a savepoint within a transaction to which a partial rollback can be performed.

77. Which isolation level prevents dirty reads by requiring a transaction to read committed data?

1. READ COMMITTED
2. READ UNCOMMITTED
3. NO LOCK
4. UNSAFE READ

Answer: A) READ COMMITTED

Explanation:

READ COMMITTED prevents a transaction from reading data that another transaction has modified but not committed.

78. Which isolation level permits dirty reads?

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

Answer: B) READ UNCOMMITTED

Explanation:

READ UNCOMMITTED allows a transaction to read data that may have been modified by another transaction but not yet committed.

79. Which isolation level provides the strictest standard locking-based isolation among the traditional SQL Server isolation levels?

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

Answer: D) SERIALIZABLE

Explanation:

SERIALIZABLE provides the strongest traditional transaction isolation by preventing phenomena such as phantom reads through stronger locking behavior.

80. What is a deadlock?

1. A situation where two or more transactions wait for resources held by one another
2. A completed transaction
3. A disabled database
4. A failed backup file

Answer: A) A situation where two or more transactions wait for resources held by one another

Explanation:

A deadlock occurs when transactions form a cycle of resource dependencies. SQL Server detects deadlocks and chooses one transaction as the victim so processing can continue.

81. What is an index primarily used for?

1. Improving data access performance for suitable queries
2. Encrypting database backups
3. Replacing every table constraint
4. Storing stored procedures

Answer: A) Improving data access performance for suitable queries

Explanation:

Indexes provide data access structures that can allow SQL Server to locate qualifying rows more efficiently for suitable queries, although indexes also introduce storage and maintenance costs.

82. What is a clustered index?

1. An index that determines the logical order of rows in the table's data pages
2. An index that can only contain text data
3. An index that exists only in temporary tables
4. An index that stores only NULL values

Answer: A) An index that determines the logical order of rows in the table's data pages

Explanation:

A clustered index organizes the table's data rows according to the clustered index key. A table can have only one clustered index.

83. How many clustered indexes can a standard SQL Server table have?

1. One
2. Two
3. Ten
4. Unlimited

Answer: A) One

Explanation:

A table can have only one clustered index because the data rows can have only one physical ordering structure.

84. What is a nonclustered index?

1. A separate index structure containing keys and row locators
2. A backup copy of the database
3. A table without columns
4. A transaction log record

Answer: A) A separate index structure containing keys and row locators

Explanation:

A nonclustered index is stored separately from the table's data rows and contains index keys plus information used to locate the corresponding rows.

85. Can a SQL Server table have multiple nonclustered indexes?

1. Yes
2. No
3. Only one per database
4. Only one per schema

Answer: A) Yes

Explanation:

A table can have multiple nonclustered indexes. However, excessive indexing can increase storage requirements and the cost of INSERT, UPDATE, and DELETE operations.

86. What is a covering index?

1. An index that contains all columns needed by a particular query
2. An index that covers an entire database backup
3. An index that encrypts table data
4. An index that contains only primary keys

Answer: A) An index that contains all columns needed by a particular query

Explanation:

A covering index contains the key and included columns necessary for a query, allowing SQL Server to satisfy the query from the index without needing additional access to the base table for those columns.

87. Which option adds non-key columns to a nonclustered index?

1. INCLUDE
2. ADD COLUMN
3. COVER
4. STORE

Answer: A) INCLUDE

Explanation:

The INCLUDE clause adds non-key columns to a nonclustered index, which can help create a covering index without making those columns part of the index key.

88. What does index fragmentation generally indicate?

1. Logical ordering of index pages has become less optimal
2. The database has no tables
3. All queries are invalid
4. The transaction log is empty

Answer: A) Logical ordering of index pages has become less optimal

Explanation:

Index fragmentation can occur as data changes cause pages to become less logically ordered or less densely packed. Appropriate index maintenance depends on the level and type of fragmentation and workload.

89. Which statement can rebuild an index?

1. ALTER INDEX ... REBUILD
2. RECREATE INDEX ONLY
3. BUILD INDEX NOW
4. RESET INDEX

Answer: A) ALTER INDEX ... REBUILD

Explanation:

SQL Server supports ALTER INDEX ... REBUILD for rebuilding indexes and can perform additional operations depending on the specified options and SQL Server edition/version.

90. What is the Query Optimizer responsible for?

1. Choosing an execution strategy for a query
2. Creating user passwords
3. Backing up all databases automatically
4. Changing table names

Answer: A) Choosing an execution strategy for a query

Explanation:

The SQL Server Query Optimizer evaluates possible execution strategies and selects an execution plan based on estimated costs and available information.

91. What is an execution plan?

1. A representation of how SQL Server plans to execute a query
2. A backup schedule
3. A database security policy
4. A table definition

Answer: A) A representation of how SQL Server plans to execute a query

Explanation:

An execution plan describes the operators and strategy SQL Server uses or intends to use to execute a query.

92. Which system view can provide information about databases on a SQL Server instance?

1. sys.databases
2. sys.tables_only
3. system.database_list
4. master.database_catalog

Answer: A) sys.databases

Explanation:

sys.databases is a catalog view that provides information about databases accessible on the SQL Server instance.

93. Which system catalog view provides information about tables in the current database?

1. sys.tables
2. sys.objects_only_tables
3. database.tables
4. system.tables_list

Answer: A) sys.tables

Explanation:

sys.tables provides metadata about user tables and related table properties in the current database.

94. Which database is primarily used by SQL Server to store system-level information and metadata?

1. master
2. tempdb
3. model
4. msdb

Answer: A) master

Explanation:

The master database contains system-level information such as server configuration metadata, login information, and information about databases.

95. Which SQL Server system database is used for temporary objects and intermediate results?

1. master
2. tempdb
3. model
4. msdb

Answer: B) tempdb

Explanation:

tempdb is used for temporary tables, table variables in relevant execution contexts, worktables, version-store activity, and other temporary database-engine operations.

96. Which system database provides the template used when creating new databases?

1. model
2. master
3. tempdb
4. msdb

Answer: A) model

Explanation:

The model database acts as a template for new databases. Objects and settings placed in model can influence newly created databases.

97. Which system database is commonly associated with SQL Server Agent jobs and other scheduling-related information?

1. msdb
2. master
3. tempdb
4. model

Answer: A) msdb

Explanation:

The msdb database stores information used by SQL Server Agent and other SQL Server components, including jobs and schedules.

98. Which database file normally stores the transaction log?

1. .mdf
2. .ndf
3. .ldf
4. .bak

Answer: C) .ldf

Explanation:

SQL Server transaction log files normally use the .ldf extension. Primary data files commonly use .mdf, while secondary data files commonly use .ndf.

99. Which backup type contains all data needed to restore the database to the point represented by that backup, subject to the backup and recovery chain?

1. Full database backup
2. Transaction log backup only
3. File-name backup
4. Index backup

Answer: A) Full database backup

Explanation:

A full database backup contains a complete backup of the database data needed as a base for restoration. Additional differential or log backups may be required depending on the desired recovery point.

100. Consider the following SQL Server query:

WITH RankedEmployees AS
(
    SELECT
        EmployeeID,
        EmployeeName,
        Department,
        Salary,
        ROW_NUMBER() OVER
        (
            PARTITION BY Department
            ORDER BY Salary DESC
        ) AS RowNum
    FROM Employees
)
SELECT EmployeeID, EmployeeName, Department, Salary
FROM RankedEmployees
WHERE RowNum = 1;

What does this query return?

1. The highest-paid employee from each department
2. The highest-paid employee from the entire company only
3. Every employee sorted by salary
4. The lowest-paid employee from each department

Answer: A) The highest-paid employee from each department

Explanation:

The CTE calculates ROW_NUMBER() separately for each Department because of PARTITION BY Department. Within each department, employees are ordered by Salary in descending order, so RowNum = 1 identifies the highest-paid employee in each department. This combines a CTE with a window function to solve a common top-per-group query pattern.