Wednesday , November 18 2026
Breaking News

Microsoft SQL Server Certification Exam Answers 2026 (All 100 Questions)

Microsoft SQL Server Certification exam answers 2026: This page lists all 100 questions of the official Microsoft SQL Server Certification exam by Itronix Solutions, each with its four answer options and the correct answer highlighted in green. Use it to revise every topic before you attempt the test and earn your verifiable certificate.

About this certification: MSSQL is a suite of database software published by Microsoft. It includes a relational database engine, which stores data in tables, columns and rows.

Certification Microsoft SQL Server Certification
Provider Itronix Solutions
Category Programming & Development
Total questions 100 multiple-choice questions
Marks 1 mark per question (100 marks)
Passing score 80% (80 out of 100)
Retake Available if you do not pass
Certificate Verifiable online certificate

Take the exam: Microsoft SQL Server Certification on Itronix Solutions. See every other Itronix exam in our complete list of Itronix certification answers.

Microsoft SQL Server Exam Questions and Answers

Questions 1 to 25

1. Which of the following is a free database software running free SQL Server technology?

A. SQL Server Express ✔
B. SQL Server Workgroup
C. SQL Server Enterprise
D. SQL Server Web

Correct answer: A. SQL Server Express

2. What is the Codename for SQL Server 2012?

A. Katmai
B. Denali ✔
C. Hekaton
D. Kilimanjaro

Correct answer: B. Denali

3. Which of the following data type is not present in SQL Server?

A. geography
B. hierarchyid
C. boolean ✔
D. bit

Correct answer: C. boolean

4. Which database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators?

A. master
B. temp
C. model
D. msdb ✔

Correct answer: D. msdb

5. MS SQL Server is a ?

A. Relational database ✔
B. Personal database
C. Centralised database
D. Distributed database

Correct answer: A. Relational database

6. Which of the following statement are true about SQL Server?

A. It is both GUI and command based software
B. It is platform dependent
C. It is also an ORDBMS
D. All of the above ✔

Correct answer: D. All of the above

7. Which programming language is used to developed MS SQL?

A. C
B. C++
C. Both A and B ✔
D. Java

Correct answer: C. Both A and B

8. Which of the following is an Advantages of Instances (SQL Server)?

A. To separate security privileges
B. To reduce temporary database problems
C. To reduce cost
D. All of the above ✔

Correct answer: D. All of the above

9. What does the abbreviation DBMS stand for?

A. Database Management System. ✔
B. Database Manipulation Software.
C. Digital Base Mapping System.
D. Data Borrowing and Movement Software.

Correct answer: A. Database Management System.

10. The advantages of Standard Query Language (SQL) include which of the following in relation to GIS databases?

A. It uses a pseudo-English style of questioning.
B. It is good at handling geographical concepts. ✔
C. It is widely used.
D. It is simple and easy to understand.

Correct answer: B. It is good at handling geographical concepts.

11. Which of the following are characteristics of an RDBMS?

A. Keys may be unique or have multiple occurrences in the database.
B. It cannot use SQL
C. Tables are linked by common data known as keys ✔
D. Data are organized in a series of two-dimensional tables each of which contains records for one entity.

Correct answer: C. Tables are linked by common data known as keys

12. Which of the following are issues to be considered by users of large corporate GIS databases?

A. The need for concurrent access and multi-user update.
B. The need for multiple copies of the same data and subsequent merging after separate updates.
C. The need to manage long transactions.
D. The need for multiple views or different windows into the same databases. ✔

Correct answer: D. The need for multiple views or different windows into the same databases.

13. The benefits of a standard relational language include which of the following?

A. Reduced training costs ✔
B. Increased dependence on a single vendor
C. Applications are not needed.
D. All of the above

Correct answer: A. Reduced training costs

14. Which of the following do you need to consider when you make a table in SQL?

A. Data types
B. Primary keys
C. Default values
D. All of the above ✔

Correct answer: D. All of the above

15. SQL query and modification commands make up a(n) ________ .

A. HTML
B. DML ✔
C. XML
D. DDL

Correct answer: B. DML

16. Which one of the following sorts rows in SQL?

A. SORT BY
B. ALIGN BY
C. ORDER BY ✔
D. GROUP BY

Correct answer: C. ORDER BY

17. Which Is The Subset Of SQL Commands Used To Manipulate Oracle Database Structures, Including Tables?

A. Data Definition Language(DDL) ✔
B. Data Manipulation Language(DML)
C. Both Of Above
D. None

Correct answer: A. Data Definition Language(DDL)

18. Which Operator Performs Pattern Matching?

A. EXISTS Operator
B. LIKE Operator ✔
C. BETWEEN Operator
D. None Of These

Correct answer: B. LIKE Operator

19. In SQL, Which Command(s) Is(are) Used To Change A Table’s Storage Characteristics?

A. CHANGE TABLE
B. MODIFY TABLE
C. ALTER TABLE ✔
D. All Of The Above

Correct answer: C. ALTER TABLE

20. In SQL, Which Command Is Used To SELECT Only One Copy Of Each Set Of Duplicable Rows

A. SELECT DIFFERENT
B. SELECT UNIQUE
C. SELECT UNSAME
D. SELECT DISTINCT ✔

Correct answer: D. SELECT DISTINCT

21. Microsoft SQL Server is:

A. A NoSQL database
B. A spreadsheet
C. A web browser
D. A relational database management system from Microsoft ✔

Correct answer: D. A relational database management system from Microsoft

22. The SQL dialect used by SQL Server is:

A. T-SQL (Transact-SQL) ✔
B. PL/SQL
C. HiveQL
D. PL/pgSQL

Correct answer: A. T-SQL (Transact-SQL)

23. SQL Server Management Studio (SSMS) is used to:

A. Edit photos
B. Manage and query SQL Server databases ✔
C. Write Word documents
D. Host websites

Correct answer: B. Manage and query SQL Server databases

24. Azure Data Studio is:

A. A database engine
B. A cross-platform tool for working with SQL Server and Azure SQL ✔
C. A VM
D. A CDN

Correct answer: B. A cross-platform tool for working with SQL Server and Azure SQL

25. SQL Server editions include:

A. Enterprise, Standard, Express and Developer ✔
B. Basic, Pro and Ultra
C. Home and Office
D. Lite and Max

Correct answer: A. Enterprise, Standard, Express and Developer

Questions 26 to 50

26. SQL Server Express is:

A. The most expensive edition
B. A reporting tool only
C. A cloud-only edition
D. A free edition with resource limits ✔

Correct answer: D. A free edition with resource limits

27. The system database that stores server-level metadata is:

A. master ✔
B. model
C. msdb
D. tempdb

Correct answer: A. master

28. tempdb is used for:

A. Logins only
B. Permanent user data
C. Temporary objects and intermediate results ✔
D. Backups only

Correct answer: C. Temporary objects and intermediate results

29. The model database is:

A. A reporting database
B. The template used when creating new databases ✔
C. Read-only data
D. A backup

Correct answer: B. The template used when creating new databases

30. msdb is used by:

A. Temporary tables
B. SQL Server Agent for jobs, alerts and backup history ✔
C. Logins
D. User tables only

Correct answer: B. SQL Server Agent for jobs, alerts and backup history

31. SQL Server Agent is used to:

A. Encrypt disks
B. Schedule and automate jobs ✔
C. Store data
D. Run queries manually only

Correct answer: B. Schedule and automate jobs

32. A SQL Server database consists of:

A. Only images
B. Data files (.mdf/.ndf) and log files (.ldf) ✔
C. Only .txt files
D. Only XML files

Correct answer: B. Data files (.mdf/.ndf) and log files (.ldf)

33. The transaction log records:

A. Only logins
B. Only backups
C. All transactions and database modifications for recovery ✔
D. Only errors

Correct answer: C. All transactions and database modifications for recovery

34. Recovery models in SQL Server include:

A. Fast and Slow
B. Simple, Full and Bulk-logged ✔
C. Basic and Advanced
D. Hot and Cold

Correct answer: B. Simple, Full and Bulk-logged

35. The Full recovery model allows:

A. No log backups
B. Only full backups
C. Point-in-time restore using log backups ✔
D. No recovery

Correct answer: C. Point-in-time restore using log backups

36. Backup types in SQL Server include:

A. Full, differential and transaction log backups ✔
B. Only full
C. Only snapshots
D. Only incremental

Correct answer: A. Full, differential and transaction log backups

37. A differential backup contains:

A. Only log records
B. Only schema
C. Changes since the last full backup ✔
D. All data always

Correct answer: C. Changes since the last full backup

38. SELECT TOP 10 * FROM Sales returns:

A. Rows where id is 10
B. The first 10 rows from Sales ✔
C. An error
D. 10 columns

Correct answer: B. The first 10 rows from Sales

39. OFFSET and FETCH NEXT are used for:

A. Paging query results ✔
B. Creating indexes
C. Joining tables
D. Deleting rows

Correct answer: A. Paging query results

40. GETDATE() returns:

A. The current date and time ✔
B. The user name
C. The database name
D. A fixed date

Correct answer: A. The current date and time

41. DATEADD(day, 7, GETDATE()) returns:

A. The current week
B. The day number 7
C. Seven days ago
D. The date seven days from now ✔

Correct answer: D. The date seven days from now

42. DATEDIFF(day, start, end) returns:

A. A new date
B. The number of days between two dates ✔
C. A string
D. The month name

Correct answer: B. The number of days between two dates

43. ISNULL(column, 0) returns:

A. Always NULL
B. The column value, or 0 if it is NULL ✔
C. Always 0
D. The column name

Correct answer: B. The column value, or 0 if it is NULL

44. CONVERT and CAST are used to:

A. Delete data
B. Change data types ✔
C. Create tables
D. Grant permissions

Correct answer: B. Change data types

45. LEN(‘SQL’) returns:

A. 4
B. 2
C. 0
D. 3 ✔

Correct answer: D. 3

46. CHARINDEX finds:

A. The length
B. The ASCII code
C. The last character
D. The position of a substring within a string ✔

Correct answer: D. The position of a substring within a string

47. STRING_AGG concatenates:

A. Tables
B. Indexes
C. Databases
D. Values from rows into a single string with a separator ✔

Correct answer: D. Values from rows into a single string with a separator

48. IDENTITY(1,1) on a column:

A. Creates a foreign key
B. Allows NULLs
C. Auto-generates incrementing values starting at 1 ✔
D. Encrypts values

Correct answer: C. Auto-generates incrementing values starting at 1

49. NVARCHAR differs from VARCHAR because it:

A. Cannot store text
B. Stores Unicode characters ✔
C. Is fixed length always
D. Stores only numbers

Correct answer: B. Stores Unicode characters

50. The UNIQUEIDENTIFIER type stores:

A. Dates
B. Money
C. GUID values ✔
D. Integers

Correct answer: C. GUID values

Questions 51 to 75

51. A PRIMARY KEY constraint:

A. Enforces unique, non-null identification of rows ✔
B. Allows NULLs
C. Allows duplicates
D. Is optional decoration

Correct answer: A. Enforces unique, non-null identification of rows

52. A FOREIGN KEY constraint enforces:

A. Data type only
B. Uniqueness only
C. Referential integrity between tables ✔
D. Sorting

Correct answer: C. Referential integrity between tables

53. A CHECK constraint:

A. Deletes rows
B. Joins tables
C. Restricts values to meet a condition ✔
D. Creates an index

Correct answer: C. Restricts values to meet a condition

54. A clustered index:

A. Is a separate copy of all data
B. Is always non-unique
C. Determines the physical order of rows in a table ✔
D. Cannot exist with a primary key

Correct answer: C. Determines the physical order of rows in a table

55. A table can have how many clustered indexes?

A. Unlimited
B. Two
C. One ✔
D. None ever

Correct answer: C. One

56. A non-clustered index:

A. Replaces the table
B. Reorders the table physically
C. Is a separate structure with pointers to data rows ✔
D. Cannot be created

Correct answer: C. Is a separate structure with pointers to data rows

57. Included columns in a non-clustered index help to:

A. Cover queries and avoid lookups ✔
B. Increase table size only
C. Change data types
D. Delete data

Correct answer: A. Cover queries and avoid lookups

58. Index fragmentation can be fixed by:

A. Restarting Windows
B. Adding RAM only
C. Rebuilding or reorganising indexes ✔
D. Deleting the database

Correct answer: C. Rebuilding or reorganising indexes

59. An execution plan shows:

A. Backup history
B. How SQL Server will execute a query ✔
C. Server hardware
D. User permissions

Correct answer: B. How SQL Server will execute a query

60. A table scan reads:

A. Only indexed rows
B. Only one row
C. Every row in a table ✔
D. No rows

Correct answer: C. Every row in a table

61. An index seek is generally:

A. The same as a scan
B. Impossible
C. Always slower
D. More efficient than a scan for selective queries ✔

Correct answer: D. More efficient than a scan for selective queries

62. Statistics in SQL Server help the optimiser to:

A. Store backups
B. Encrypt data
C. Estimate row counts and choose efficient plans ✔
D. Grant access

Correct answer: C. Estimate row counts and choose efficient plans

63. A view is:

A. A backup
B. An index
C. A saved SELECT query that behaves like a virtual table ✔
D. A physical copy of data

Correct answer: C. A saved SELECT query that behaves like a virtual table

64. A stored procedure is:

A. A view
B. A table
C. A login
D. A precompiled set of T-SQL statements ✔

Correct answer: D. A precompiled set of T-SQL statements

65. Parameters in stored procedures help to:

A. Reuse logic and reduce SQL injection risk ✔
B. Store files
C. Remove security
D. Increase errors

Correct answer: A. Reuse logic and reduce SQL injection risk

66. A user-defined function returns:

A. Only text
B. Only errors
C. A scalar value or a table ✔
D. Nothing

Correct answer: C. A scalar value or a table

67. A trigger runs:

A. Automatically in response to INSERT, UPDATE or DELETE events ✔
B. Only at login
C. Only at backup time
D. Only manually

Correct answer: A. Automatically in response to INSERT, UPDATE or DELETE events

68. AFTER triggers execute:

A. After the triggering statement completes ✔
B. Before the statement
C. Instead of the statement
D. Never

Correct answer: A. After the triggering statement completes

69. INSTEAD OF triggers are often used on:

A. Backups
B. Indexes
C. Views to control modifications ✔
D. Logins

Correct answer: C. Views to control modifications

70. BEGIN TRANSACTION, COMMIT and ROLLBACK are used to:

A. Grant permissions
B. Schedule jobs
C. Control transactions ✔
D. Create tables

Correct answer: C. Control transactions

71. TRY…CATCH in T-SQL is used to:

A. Create loops
B. Create indexes
C. Define tables
D. Handle errors ✔

Correct answer: D. Handle errors

72. @@ROWCOUNT returns:

A. The current user
B. The number of rows affected by the last statement ✔
C. The server version
D. The total rows in a table

Correct answer: B. The number of rows affected by the last statement

73. A CTE is defined with:

A. WITH ✔
B. DEFINE
C. DECLARE TABLE only
D. TEMP

Correct answer: A. WITH

74. A recursive CTE is useful for:

A. Images
B. Encryption
C. Backups
D. Hierarchical data such as org charts ✔

Correct answer: D. Hierarchical data such as org charts

75. Window function RANK() assigns:

A. Row counts only
B. Random numbers
C. Ranks with gaps for ties ✔
D. Consecutive ranks without gaps always

Correct answer: C. Ranks with gaps for ties

Questions 76 to 100

76. ROW_NUMBER() assigns:

A. A unique sequential number to each row in a partition ✔
B. Ranks with ties
C. Totals
D. Averages

Correct answer: A. A unique sequential number to each row in a partition

77. PIVOT in T-SQL turns:

A. Columns into indexes
B. Row values into columns ✔
C. Tables into views
D. Data into backups

Correct answer: B. Row values into columns

78. MERGE in T-SQL can:

A. Only select
B. Only delete
C. Only insert
D. Insert, update and delete in one statement based on a source ✔

Correct answer: D. Insert, update and delete in one statement based on a source

79. Temporary tables prefixed with # are:

A. Global to all sessions always
B. Local to the session ✔
C. Views
D. Permanent

Correct answer: B. Local to the session

80. Table variables are declared with:

A. NEW TABLE
B. WITH TABLE
C. CREATE TEMP
D. DECLARE @t TABLE (…) ✔

Correct answer: D. DECLARE @t TABLE (…)

81. A login in SQL Server is:

A. A view
B. A database table
C. A server-level principal used to connect ✔
D. A backup

Correct answer: C. A server-level principal used to connect

82. A database user is:

A. A backup file
B. An index
C. A server role
D. A principal mapped to a login within a database ✔

Correct answer: D. A principal mapped to a login within a database

83. Fixed database role db_datareader allows:

A. Full admin
B. Reading all user tables in the database ✔
C. Backups only
D. Writing data

Correct answer: B. Reading all user tables in the database

84. GRANT, DENY and REVOKE manage:

A. Permissions ✔
B. Transactions
C. Backups
D. Indexes

Correct answer: A. Permissions

85. Windows authentication uses:

A. Email
B. Active Directory or Windows accounts ✔
C. SQL usernames only
D. No authentication

Correct answer: B. Active Directory or Windows accounts

86. Transparent Data Encryption (TDE) encrypts:

A. Network traffic only
B. Query results on screen
C. Database files at rest ✔
D. Passwords only

Correct answer: C. Database files at rest

87. Always Encrypted protects:

A. Backups only
B. Logs only
C. Indexes
D. Sensitive column data so the server never sees plaintext ✔

Correct answer: D. Sensitive column data so the server never sees plaintext

88. Always On Availability Groups provide:

A. Only reporting
B. Only indexing
C. High availability and disaster recovery with replicas ✔
D. Only backups

Correct answer: C. High availability and disaster recovery with replicas

89. Log shipping provides:

A. No recovery
B. Encryption
C. Real-time sync always
D. Warm standby by restoring log backups to a secondary server ✔

Correct answer: D. Warm standby by restoring log backups to a secondary server

90. SSIS (SQL Server Integration Services) is used for:

A. Reporting only
B. Web hosting
C. Machine learning only
D. ETL and data integration ✔

Correct answer: D. ETL and data integration

91. SSRS (SQL Server Reporting Services) is used for:

A. Creating and delivering reports ✔
B. Security
C. ETL only
D. Database backups

Correct answer: A. Creating and delivering reports

92. SSAS (SQL Server Analysis Services) provides:

A. Email
B. OLAP cubes and tabular analytical models ✔
C. Web servers
D. Backups

Correct answer: B. OLAP cubes and tabular analytical models

93. Azure SQL Database is:

A. A file share
B. An on-premises only product
C. A NoSQL store
D. A managed cloud version of SQL Server’s engine ✔

Correct answer: D. A managed cloud version of SQL Server’s engine

94. Deadlocks in SQL Server occur when:

A. Two sessions block each other waiting for locks ✔
B. Backups complete
C. Indexes exist
D. Queries run fast

Correct answer: A. Two sessions block each other waiting for locks

95. Isolation level READ COMMITTED prevents:

A. Phantom reads always
B. Dirty reads ✔
C. All locking
D. All concurrency

Correct answer: B. Dirty reads

96. The main purpose of SQL Server is to:

A. Store, manage and query relational data reliably ✔
B. Design websites
C. Edit images
D. Host email only

Correct answer: A. Store, manage and query relational data reliably

97. Columnstore indexes are optimised for:

A. Logins
B. Backups
C. Analytical queries over large tables ✔
D. Single-row lookups only

Correct answer: C. Analytical queries over large tables

98. Query Store in SQL Server captures:

A. Backups
B. Images
C. Query plans and runtime statistics over time ✔
D. User passwords

Correct answer: C. Query plans and runtime statistics over time

99. NOLOCK hints can cause:

A. Dirty reads of uncommitted data ✔
B. Faster backups
C. Encryption
D. Perfect consistency

Correct answer: A. Dirty reads of uncommitted data

100. Database Mail in SQL Server allows:

A. Hosting websites
B. Editing reports
C. Sending emails from the database engine ✔
D. Receiving web traffic

Correct answer: C. Sending emails from the database engine

How to Pass the Microsoft SQL Server Exam

  • Read every question carefully; most options look similar, so look for the most precise answer.
  • Revise the questions above topic by topic rather than memorising letters, because option order can change in the live test.
  • You need 80 correct answers out of 100 to pass, so aim to understand at least 85 to stay safe.
  • If you do not clear it on the first attempt, review the questions you missed and use the retake option.

Frequently Asked Questions

How many questions are in the Microsoft SQL Server Certification exam?

The exam has 100 multiple-choice questions, each worth 1 mark.

What is the passing score for the Microsoft SQL Server Certification?

You need at least 80% (80 out of 100) to pass and receive the certificate.

Can I retake the Microsoft SQL Server Certification exam?

Yes. If you score below 80%, you can retry the test after reviewing the topics.

Is the Itronix certificate verifiable?

Yes. Every certificate issued by Itronix Solutions has a unique ID that can be checked on the Itronix verify-certificate page.

More Programming & Development Certification Answers

Disclaimer: These answers are provided for learning and revision. Questions and answers are checked regularly; if you find a mistake, let us know in the comments.

About Priya Dogra

Priya Dogra runs priyadogra.com, where she shares free certification courses, internships, jobs, scholarships and exam preparation guides for Indian students and freshers. Follow her on YouTube, LinkedIn and Instagram for the latest opportunities.

Check Also

Unix/ Linux Certification exam answers 2026

Unix/ Linux Certification Exam Answers 2026 (All 100 Questions)

All 100 Unix/ Linux Certification exam questions with correct answers highlighted - updated 2026.

Leave a Reply

Your email address will not be published. Required fields are marked *