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
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
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
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
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
Explanation: Codenames for SQL Server 2005, 2008, 2008 R2, 2012, 2014 are Yukon, Katmai, Kilimanjaro, Denali and Hekaton respectively.
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
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
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
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
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
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
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
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
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
Explanation: Hybrid model may yield better performance in that you have more control over the indexes created on the relational columns and locking characteristics.
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
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
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
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
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
Explanation: In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations.
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
- Laying the Foundation
- Manipulating Data with Select
- Beyond Relational
- Creating the Physical Database Schema
- Developing with SQL Server
- Data Connectivity
- Enterprise Data Management
- Security
- Monitoring and Auditing
- Performance Tuning and Optimization
- Business Intelligence
- SQL Server Specifications
- 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.
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.
|
|
|
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.
|
|
|
4. SQL Server MCQ on Creating the Physical Database Schema
The section contains SQL Server multiple choice questions with answers on data definition language.
|
|
|
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.
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.
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.
8. SQL Server MCQ on Security
The section contains SQL Server Interview questions and answers on authentication, securables, data cryptography and row level security.
|
|
|
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.
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.
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.
12. Multiple Choice Questions on SQL Server Specifications
The section contains MCQs on sql server specifications.
|
|
|
13. SQL Server MCQ on System and Sample Databases
The section contains SQL Server multiple choice questions and answers on system databases.
|
|
Wish you the best in your endeavor to learn and master SQL Server!
SQL Server Online Test:
Important Links:
- SQL Server Books
- MySQL Multiple Choice Questions
- DBMS Multiple Choice Questions
- RDBMS Multiple Choice Questions
- Computer Science Multiple Choice Questions
