SQL Server MCQ (Multiple Choice Questions)

Here are 1000 MCQs on SQL Server (Chapterwise).

1. What is SQL Server?
a) SQL Server is a relational database management system
b) SQL Server is a software whose main purpose is to store and retrieve data
c) SQL Server is a highly secure server and does not allow any database file manipulation during execution
d) All of the mentioned
View Answer

Answer: a
Explanation: Microsoft’s SQL Server is a relational database management system (RDBMS). It was created largely to compete with the MySQL and Oracle databases. The standard SQL (Structured Query Language) language, ANSI SQL is supported by SQL Server. SQL Server is a highly secure server and does not allow any database file manipulation during the execution process.

2. When was the first version of Microsoft SQL Server released?
a) 1991
b) 1990
c) 1988
d) 1983
View Answer

Answer: c
Explanation: Microsoft released its first version of SQL Server in the year of 1988. This version was developed jointly by Microsoft and Sybase for the OS/2 platform.

3. Which of the following is a free database software running free SQL Server technology?
a) SQL Server Web
b) SQL Server Enterprise
c) SQL Server Workgroup
d) SQL Server Express
View Answer

Answer: d
Explanation: SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine.

4. Which of the following companies originally worked together to create and market the first version of SQL Server?
a) Sybase
b) Ashton-Tate
c) Microsoft
d) All of the Mentioned
View Answer

Answer: d
Explanation: First version of SQL Server was designed for the OS/2 platform and was developed jointly by Microsoft and Sybase along with Ashton-Tate.

5. What is the Codename for SQL Server 2012?
a) Katmai
b) Denali
c) Hekaton
d) Kilimanjaro
View Answer

Answer: b
Explanation: Codenames for SQL Server 2005, 2008, 2008 R2, 2012, 2014 are Yukon, Katmai, Kilimanjaro, Denali and Hekaton respectively.
advertisement
advertisement

6. Which was the first version of SQL Server to introduce in-memory capability?
a) SQL Server 2005
b) SQL Server 2008
c) SQL Server 2012
d) SQL Server 2014
View Answer

Answer: d
Explanation: SQL Server 2014 provides a new in-memory capability for tables that can fit entirely in memory (also known as Hekaton).

7. Which of the following data type is not present in SQL Server?
a) geography
b) hierarchyid
c) boolean
d) bit
View Answer

Answer: c
Explanation: SQL Server doesn’t have a Boolean data type, at least not by that name. To store True/False, Yes/No, and On/Off values, use the bit data type. It accepts only three values: 0, 1, and NULL.

8. Which database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators?
a) temp
b) model
c) msdb
d) master
View Answer

Answer: c
Explanation: MSDB Database stores information related to database backups, DTS packages, Replication, SQL Agent information, SQL Server jobs.

9. SQL Server has mainly how many types of views?
a) four
b) three
c) one
d) two
View Answer

Answer: d
Explanation: In SQL Server we have two types of views-System Defined Views and User Defined Views.

10. Which of the following is not a SQL Server INFORMATION_SCHEMA view?
a) sys.dm_exec_connections
b) INFORMATION_SCHEMA.KEY_COLUMN_USAGE
c) INFORMATION_SCHEMA.DOMAIN_CONSTRAINTS
d) INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
View Answer

Answer: a
Explanation: The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database. These views can be found in the master database under Views / System Views and be called from any database in your SQL Server instance.

11. Which of the following joins are SQL server default?
a) Inner
b) Equi
c) Outer
d) None of the Mentioned
View Answer

Answer: a
Explanation: SQL inner joins are the default joins in SQL Server. Inner query joins only the rows that are matching.

12. Which of the following is not a class of constraint in SQL Server?
a) UNIQUE
b) NOT NULL
c) CHECK
d) NULL
View Answer

Answer: d
Explanation: In SQL Server, NOT NULL specifies that the column does not accept NULL values.

13. Which of the reasons will force you to use the XML data model in SQL Server?
a) Order is inherent in your data
b) Your data is sparse or you do not know the structure of the data
c) Your data represents containment hierarchy
d) All of the Mentioned
View Answer

Answer: d
Explanation: In SQL Server, the XML data model is a good choice if you want a platform-independent model in order to ensure the portability of the data by using structural and semantic markup.

14. What is the hybrid model in SQL Server?
a) Using XML with views
b) Using XML with triggers
c) Combination of relational and XML data type columns
d) Combination of relational and non relational data type columns
View Answer

Answer: c
Explanation: Hybrid model may yield better performance in that you have more control over the indexes created on the relational columns and locking characteristics.
advertisement

15. Which of the following feature of SQL Server was used before XML technology for semi structured data?
a) In memory database
b) Stored Procedure
c) Dynamic management views
d) None of the mentioned
View Answer

Answer: b
Explanation: You need to create a stored procedure to process incoming XML from a partner before SQL Server 2005.

16. Which of the following language is used for procedural flow in SQL Server?
a) Control-of-flow language
b) Flow language
c) Control language
d) None of the mentioned
View Answer

Answer: a
Explanation: Transact-SQL provides special words called control-of-flow language that control the flow of execution of Transact-SQL statements in SQL Server.

17. Exception handling is possible in SQL Server using _____________
a) FINAL
b) FINALLY
c) THROW
d) All of the mentioned
View Answer

Answer: c
Explanation: Exception handling is possible in SQL Server using THROW. It generates an error message and initiates error processing for the session.

18. Which of the following stored procedure is already defined in SQL Server?
a) System defined Procedure
b) CLR Stored Procedure
c) Extended Procedure
d) User Defined Stored Procedure
View Answer

Answer: a
Explanation: System defined Procedure is already defined in SQL Server. System defined Procedure procedure starts with the sp_ prefix.

19. DML triggers in SQL Server is applicable to _____________
a) Update
b) Delete
c) Insert
d) All of the mentioned
View Answer

Answer: d
Explanation: In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations.
advertisement

20. User defined function in SQL Server can return ____________
a) Result set
b) Scalar value
c) Set of values
d) All of the mentioned
View Answer

Answer: d
Explanation: SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value.

21. Which of the stored procedure is used to test the SQL injection attack?
a) xp_reg
b) xp_write
c) xp_regwrite
d) all of the mentioned
View Answer

Answer: c
Explanation: xp_regwrite writes an arbitrary value into the Registry (undocumented extended procedure) and it is used to test the SQL injection attack.

22. Which is the default field terminator for bulk insert in SQL Server?
a) Brackets
b) Parenthesis
c) Full stop
d) Comma
View Answer

Answer: d
Explanation: In SQL Server, Field Terminator is used to delimit columns in the source file. There are some cases where I have seen “|” also. “,” can be part of Name. So in that case the bulk insert may fail.

23. Which of the following is the main feature of dotConnect for SQL Server?
a) Supports the latest versions of SQL Server
b) Extra data binding capabilities
c) Ability of monitoring query execution
d) All of the mentioned
View Answer

Answer: d
Explanation: dotConnect for SQL Server includes base-class-based provider model, provider factories, connection string builder, metadata schemas, asynchronous commands, pooling enhancements, batch update support, provider-specific types, server enumeration, database change notification support and so on.

24. Which of the following is used to overcome the Replication in SQL Server?
a) Availability problems
b) Maintenance problems
c) Performance problems
d) All of the mentioned
View Answer

Answer: d
Explanation: Replication allows us to maintain the same database with multiple copies at different locations.

25. When you _______ an Access table from a SQL Server, SSMA restores the original Access table and its data.
a) unlink
b) import
c) link
d) all of the mentioned
View Answer

Answer: a
Explanation: If you have linked the Access tables to one SQL Server instance, and then you want to change the links to another instance, you must relink the tables.

26. Which of the following SQL Server tools is used to enhance data management and governance?
a) SSDT
b) OBEII
c) SSIT
d) All of the mentioned
View Answer

Answer: a
Explanation: SSDT stands for SQL Server Data Tools and is available for data management and quality.

27. Backing up your SQL Server database is essential for _______ your data.
a) replication
b) protecting
c) preventing
d) none of the mentioned
View Answer

Answer: b
Explanation: SQL Server offers many options for creating backups.

28. Which of the following is the instance of SQL Server that is your production server in log shipping?
a) secondary server
b) primary server
c) secondary database
d) primary database
View Answer

Answer: b
Explanation: Once the monitor server has been configured, it cannot be changed without removing log shipping first.

29. Which of the following pair of technology is supported in SQL Server?
a) Mirroring and Clustering
b) Mirroring and Log Shipping
c) Mirroring and Replication
d) All of the mentioned
View Answer

Answer: d
Explanation: Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

30. Which of the following is the biggest disadvantage of SQL Server Authentication mode?
a) Allows SQL Server to support Web-based applications where users create their own identities
b) Allows SQL Server to support older applications and applications provided by third parties that require SQL Server Authentication
c) SQL Server Authentication cannot use Kerberos security protocol
d) None of the mentioned
View Answer

Answer: c
Explanation: Windows Authentication uses Kerberos security protocol.

31. Which of the following operation requires to run the SQL Server Setup on the node?
a) Update
b) Add
c) Remove
d) All of the mentioned
View Answer

Answer: d
Explanation: To update or remove a SQL Server failover cluster, you must be a local administrator with permission to login as a service on all nodes of the failover cluster.

32. Which of the following tool is used for purpose of data auditing for SQL Server only?
a) Idera
b) ApexSQL
c) SQL Ninja
d) SQL Audit
View Answer

Answer: b
Explanation: ApexSQL Audit is a tool for auditing SQL Server data changes specifically for Microsoft SQL Server.

33. Which of the following is the benefit of SQL Server Profiler?
a) Correlating performance counters to diagnose problems
b) Capturing the series of Transact-SQL statements that lead to a problem
c) Finding and diagnosing slow-running queries
d) All of the mentioned
View Answer

Answer: d
Explanation: Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services.

34. Which of the following catalog view is used for SQL Server Extended Events?
a) sys.server_event_session_actions
b) sys.server_sessions
c) sys.server_event_sess
d) all of the mentioned
View Answer

Answer: a
Explanation: sys.server_event_session_actions returns a row for each action on each event of an event session.


Chapterwise Multiple Choice Questions on SQL Server

SQL Server MCQ - Multiple Choice Questions and Answers

Our 1000+ MCQs focus on all topics of the SQL Server subject, covering 100+ topics. This will help you to prepare for exams, contests, online tests, quizzes, viva-voce, interviews, and certifications. You can practice these MCQs chapter by chapter starting from the 1st chapter or you can jump to any chapter of your choice.
  1. Laying the Foundation
  2. Manipulating Data with Select
  3. Beyond Relational
  4. Creating the Physical Database Schema
  5. Developing with SQL Server
  6. Data Connectivity
  7. Enterprise Data Management
  8. Security
  9. Monitoring and Auditing
  10. Performance Tuning and Optimization
  11. Business Intelligence
  12. SQL Server Specifications
  13. System and Sample Databases

1. SQL Server MCQ on Laying the Foundation

The section contains multiple choice questions and answers on sql server basics, management studio, data types, built in functions, joins, views, data aggregation and powershell.

  • SQL Server Basics
  • SQL Server Management Studio – 1
  • SQL Server Management Studio – 2
  • Data Types in SQL Server
  • SQL Server Built in Functions
  • Basic SQL – 1
  • Basic SQL – 2
  • Basic SQL – 3
  • Joins in SQL Server
  • Views in SQL Server
  • Aggregation of Data
  • SQL Server Powershell
  • 2. SQL Server MCQ on Manipulating Data with Select

    The section contains SQL Server questions and answers on windowing, ranking, subqueries, data modification and constraints.

  • SQL Server Windowing
  • SQL Server Ranking
  • SQL Server Subqueries
  • Modifying Data – 1
  • Modifying Data – 2
  • SQL Server Constraints
  • 3. SQL Server Multiple Choice Questions on Beyond Relational

    The section contains SQL Server MCQs on hierarchies, xml data working, xquery and full text search.

  • Hierarchies
  • Working With XML Data – 1
  • Working With XML Data – 2
  • Working With XML Data – 3
  • Xquery
  • Full Text Search
  • 4. SQL Server MCQ on Creating the Physical Database Schema

    The section contains SQL Server multiple choice questions with answers on data definition language.

  • Data Definition Language (DDL) – 1
  • Data Definition Language (DDL) – 2
  • Data Definition Language (DDL) – 3
  • 5. Multiple Choice Questions on Developing with SQL Server

    The section contains SQL Server questions and answers on transact sql, procedural flow, variables, cursor, error handling, stored procedures and builtin stored procedures, user defined functions, dml and ddl triggers, dynamic sql and sql injection.

  • Transact SQL
  • Procedural Flow
  • SQL Server Variables
  • SQL Server Cursor
  • Error Handling
  • SQL Server Stored Procedure – 1
  • SQL Server Stored Procedure – 2
  • Builtin Stored Procedures
  • SQL Server User Defined Functions – 1
  • SQL Server User Defined Functions – 2
  • DML Triggers
  • DDL Triggers
  • Dynamic SQL
  • SQL Injection
  • 6. SQL Server MCQ on Data Connectivity

    The section contains SQL Server MCQs on bulk operations, distributed queries, ado.net, linq, service broker, replication, integration services and sql server access.

  • SQL Server Bulk Operations
  • Distributed Queries
  • ADO.NET
  • LINQ – 1
  • LINQ – 2
  • Service Broker
  • SQL Server Replication
  • SQL Server Integration Services – 1
  • SQL Server Integration Services – 2
  • Access With Sql Server
  • 7. SQL Server Multiple Choice Questions on Enterprise Data Management

    The section contains SQL Server multiple choice questions on enterprise data management, policy based management, recovery, backup, database maintenance, sql server agent, transferring database, database snapshots, log shipping, database mirroring and clustering.

  • Enterprise Data Management
  • Policy Based Management
  • SQL Server Recovery
  • SQL Server Backup
  • Database Maintenance
  • SQL Server Agent
  • Transferring Database
  • Database Snapshots
  • Log Shipping
  • Database Mirroring
  • SQL Server Clustering
  • 8. SQL Server MCQ on Security

    The section contains SQL Server Interview questions and answers on authentication, securables, data cryptography and row level security.

  • SQL Server Authentication
  • Securables
  • Data Cryptography
  • Row Level Security in SQL Server
  • 9. SQL Server Multiple Choice Questions on Monitoring and Auditing

    The section contains SQL Server MCQ Questions on data and schema audit, performance monitoring, profiling, wait states, extended events, change tracking and data capture, sql audit and management data warehouse.

  • SQL Server Data Audit
  • SQL Server Schema Audit
  • SQL Server Performance Monitoring
  • SQL Server Profiling
  • SQL Server Wait States
  • Extended Events
  • Change Tracking
  • Change Data Capture
  • SQL Audit
  • Management Data Warehouse
  • 10. SQL Server MCQ on Performance Tuning and Optimization

    The section contains SQL Server multiple choice questions and answers on query execution plan, indexing strategies, query plan reuse, transactions, locking and blocking, transaction log, data compression, partitioning and resource governor.

  • Query Execution Plan
  • Indexing Strategies – 1
  • Indexing Strategies – 2
  • Query Plan Reuse
  • SQL Server Transaction – 1
  • SQL Server Transaction – 2
  • SQL Server Locking
  • SQL Server Blocking
  • Working With Transaction Log
  • Data Compression
  • SQL Server Partitioning
  • SQL Server Resource Governor
  • 11. SQL Server Multiple Choice Questions on Business Intelligence

    The section contains SQL Server Interview questions and answers on business intelligence, analysis and reporting services, mdx and excel data analysis.

  • SQL Server Business Intelligence
  • SQL Server Analysis Services – 1
  • SQL Server Analysis Services – 2
  • SQL Server Analysis Services – 3
  • SQL Server MDX – 1
  • SQL Server MDX – 2
  • SQL Server Reporting Services – 1
  • SQL Server Reporting Services – 2
  • Analyzing Data With Excel
  • 12. Multiple Choice Questions on SQL Server Specifications

    The section contains MCQs on sql server specifications.

  • SQL Server Specifications – 1
  • SQL Server Specifications – 2
  • 13. SQL Server MCQ on System and Sample Databases

    The section contains SQL Server multiple choice questions and answers on system databases.

  • SQL Server System Databases
  • If you would like to learn "SQL Server" thoroughly, you should attempt to work on the complete set of 1000+ MCQs - multiple choice questions and answers mentioned above. It will immensely help anyone trying to crack an exam or an interview.

    Wish you the best in your endeavor to learn and master SQL Server!

    SQL Server Online Test:

    Important Links:

    advertisement
    Manish Bhojasia - Founder & CTO at Sanfoundry
    Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

    Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.