SQL Server MCQ

Test your knowledge with important SQL Server MCQ and their applications. These MCQs are beneficial for competitive exams too. Explore 30 + more SQL Server MCQs at Bissoy

• _________ uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache.
  ক

  pool_name

  খ

  plan_handle

  গ

  sql_handle

  ঘ

  none of the mentioned
• What is the syntax for DBCC FREEPROCCACHE with all the optional parameters in T-SQL? b) DBCC FREEPROCCACHE c) DBCC FREEPROCCACHE d) None of the mentioned
  ক

  DBCC FREEPROCCACHE

  খ

  ( { plan_handle | sql_handle | pool_name } )

  গ

  ( { plan_handle | sql_handle | pool_name } )

  ঘ

  ( { plan_handle | sql_handle | pool_name } )
• The conditions that invalidate a plan include which of the following?
  ক

  An explicit call to sp_compile

  খ

  Executing a stored procedure using the WITH COMPILE option

  গ

  Changes made to a table or view referenced by the query

  ঘ

  All of the mentioned
• The maximum size for all caches is a function of the ________ size.
  ক

  RAM

  খ

  Buffer pool

  গ

  Hard disk

  ঘ

  None of the mentioned
• Which of the following SQL Server SQL Statistics counter represents number of auto-parameterization attempts per second?
  ক

  Batch Requests/sec

  খ

  Failed Auto-Params/sec

  গ

  Forced parameterizations/sec

  ঘ

  Auto-Param Attempts/sec
• Use to bind an integer variable to the first parameter marker in an SQL statement.
  ক

  SQLParameter

  খ

  SQLBind

  গ

  SQLBindParameter

  ঘ

  All of the mentioned
• The use of parameters, including parameter markers in _______ applications, can increase the reuse of execution plans.
  ক

  OLE DB

  খ

  ADO

  গ

  ODBC

  ঘ

  All of the mentioned
• The _________ database stores basic configuration information for the server.
  ক

  Master

  খ

  Msdb

  গ

  Tempdb

  ঘ

  Model
• Which of the following system database occupies more space and memory?
  ক

  Master

  খ

  Msdb

  গ

  Tempdb

  ঘ

  Model
• How many types of system databases are present in SQL Server 2008?
  ক

  3

  খ

  4

  গ

  5

  ঘ

  6
• Which of the following database is used by SQL Server Agent?
  ক

  Pubs

  খ

  Msdb

  গ

  Tempdb

  ঘ

  Model
• Which database is used as template for all databases?
  ক

  Northwind

  খ

  Master

  গ

  Tempdb

  ঘ

  Model
• Point out the wrong statement related to master database.
  ক

  They are used to provide details of an backup plans

  খ

  Master records the information for SQL Server temporarily

  গ

  Master records the initialization information for SQL Server

  ঘ

  None of the mentioned
• Which is the most important system database?
  ক

  Northwind

  খ

  Master

  গ

  Tempdb

  ঘ

  All of the mentioned
• Which of the following holds temporary data?
  ক

  Northwind

  খ

  Master

  গ

  Tempdb

  ঘ

  Msdb
• Which of the following is not a system database?
  ক

  Northwind

  খ

  Master

  গ

  Tempdb

  ঘ

  All of the mentioned
• The supported storage types for data files are ___________
  ক

  RAM

  খ

  Hard Storage

  গ

  SMB File Share

  ঘ

  None of the mentioned
• Which is the minimum processor speed for SQL Server 2012?
  ক

  1.4 GHz

  খ

  2.4 GHz

  গ

  3.4 GHz

  ঘ

  4.4 GHz
• Which of the following component is not installed by SQL Server setup 2012 required by the product?
  ক

  .NET Framework 3.5 SP11

  খ

  Named Pipes

  গ

  SQL Server Setup support files

  ঘ

  All of the mentioned
• Which of the following operating system is supported by SQL Server 2014?
  ক

  Windows Server 2012

  খ

  Windows 8 RTM

  গ

  Windows Vista SP2

  ঘ

  All of the mentioned
• Which of the following is a feature of SQL Server 2012 R2 Enterprise?
  ক

  In memory database

  খ

  Hot-add RAM

  গ

  Database partitioning

  ঘ

  All of the mentioned
• Point out the wrong statement related to SQL Server 2014.
  ক

  SQL Server 2014 CTP 1 is pre-release software and should be installed only on a clean machine

  খ

  SQL Server 2014 CTP 1 is a non-production release and should be installed and used in production environments

  গ

  SQL Server 2014 CTP 1 is only available in the English language

  ঘ

  None of the mentioned
• Which of the following is a feature of SQL Server 2014?
  ক

  Column oriented database

  খ

  Support for 32-bit and 64-bit processors

  গ

  In memory database

  ঘ

  All of the mentioned
• Which components of SQL Server 2014 require 1182 Mb?
  ক

  SSIS

  খ

  SSAS

  গ

  Client components

  ঘ

  SSRS
• What is SQL Server 2012 R2 Hard Disk Space Requirement for database engine?
  ক

  811 Mb

  খ

  711 Mb

  গ

  611 Mb

  ঘ

  511 Mb
• The main memory requirements for SQL Server 2008 includes ___________
  ক

  Minimum:512 MB

  খ

  Minimum:1 GB

  গ

  Maximum:252 MB

  ঘ

  Maximum:512 MB
• Which is the minimum processor speed for SQL Server 2008?
  ক

  1.4 GHz

  খ

  2.4 GHz

  গ

  3.4 GHz

  ঘ

  4.4 GHz
• What does the SQL Server setup program require?
  ক

  Named Pipes

  খ

  Shared Memory

  গ

  Microsoft Windows Installer 4.5

  ঘ

  All of the mentioned
• Which of the following operating system is Supported by IA-64 Edition?
  ক

  Windows Server 2003 R2 SP2 64-bit Itanium Datacenter

  খ

  Windows Server 2003 SP2 64-bit x64 Datacenter

  গ

  Windows Server 2003 SP2 64-bit x64 Enterprise

  ঘ

  Windows Server 2003 SP2 64-bit x64 Standard
• Which of the following is not a feature of SQL Server 2008 R2 Enterprise?
  ক

  Hot-add CPU

  খ

  Hot-add RAM

  গ

  Database partitioning

  ঘ

  In memory database