logo
  • Home
  • About
  • Training
  • Programming
  • CS
  • IT
  • IS
  • ECE
  • EEE
  • EE
  • Civil
  • Mechanical
  • Chemical
  • Metallurgy
  • Instrumentation
  • Aeronautical
  • Aerospace
  • Biotechnology
  • Agriculture
  • MCA
  • BCA
  • Internship
  • Contact

SQL Server Multiple Choice Questions | MCQs | Quiz

SQL Server Interview Questions and Answers
Pratice SQL Server questions and answers for interviews, campus placements, online tests, aptitude tests, quizzes and competitive exams.

Get Started

•   SQL Server Basics
•   Management Studio - 1
•   Management Studio - 2
•   Data Types
•   Built in Functions
•   Basic SQL - 1
•   Basic SQL - 2
•   Basic SQL - 3
•   Joins
•   Views
•   Data Aggregation
•   Powershell
•   Windowing
•   Ranking
•   Subqueries
•   Modifying Data - 1
•   Modifying Data - 2
•   Constraints
•   Hierarchies
•   XML Data Working - 1
•   XML Data Working - 2
•   XML Data Working - 3
•   Xquery
•   Full Text Search
•   Data Definition Language - 1
•   Data Definition Language - 2
•   Data Definition Language - 3
•   Transact SQL
•   Procedural Flow
•   Variables
•   Cursor
•   Error Handling
•   Stored Procedure - 1
•   Stored Procedure - 2
•   Builtin Stored Procedures
•   User Defined Functions - 1
•   User Defined Functions - 2
•   DML Triggers
•   DDL Triggers
•   Dynamic SQL
•   SQL Injection
•   Bulk Operations
•   Distributed Queries
•   ADO.NET
•   LINQ - 1
•   LINQ - 2
•   Service Broker
•   Replication
•   Integration Services - 1
•   Integration Services - 2
•   Sql Server Access
•   Data Management
•   Policy Based Management
•   Recovery
•   Backup
•   Database Maintenance
•   SQL Server Agent
•   Transferring Database
•   Database Snapshots
•   Log Shipping
•   Database Mirroring
•   Clustering
•   Authentication
•   Securables
•   Data Cryptography
•   Row Level Security
•   Data Audit
•   Schema Audit
•   Performance Monitoring
•   Profiling
•   Wait States
•   Extended Events
•   Change Tracking
•   Change Data Capture
•   SQL Audit
•   Data Warehouse
•   Query Execution Plan
•   Indexing Strategies - 1
•   Indexing Strategies - 2
•   Query Plan Reuse
•   Transaction - 1
•   Transaction - 2
•   Locking
•   Blocking
•   Transaction Log
•   Data Compression
•   Partitioning
•   Resource Governor
•   Business Intelligence
•   Analysis Services - 1
•   Analysis Services - 2
•   Analysis Services - 3
•   MDX - 1
•   MDX - 2
•   Reporting Services - 1
•   Reporting Services - 2
•   Excel Data Analysis
•   Specifications - 1
•   Specifications - 2
•   System Databases

Best Reference Books

SQL Server Books
« Prev Page
Next Page »

SQL Server Questions and Answers – Dynamic SQL

Posted on August 23, 2017 by staff10

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Dynamic SQL”.

1. Which of the following is way to build dynamic sql statements ?
a) Writing a query with parameters
b) Using sp_executesql
c) Using EXEC
d) All of the mentioned
View Answer

Answer: d
Explanation: SQL Server offer three ways of running a dynamically built SQL statement.

2. Point out the correct statement :
a) ODBC has the call ExecDirect
b) Few database systems provide the facilities for running SQL code directly against the database engine
c) The SQL code is not stored in the source program, but rather it is generated based on user input
d) None of the mentioned
View Answer

Answer: c
Explanation: The SQL code can include determining not only what objects are involved, but also the filter criteria and other qualifiers that define the set of data being acted on.

3. Dynamic SQL Statements in SQL Server can be easily built using :
a) Cursor
b) Stored procedure
c) Function
d) All of the mentioned
View Answer

Answer: b
Explanation: Dynamic SQL is a term used to mean SQL code that is generated programatically (in part or fully) by your program before it is executed.

4. The calling syntax for sp_executesql is as follows:
a) sp_execute <@stmt> [<@param1 data_type>,<@param2 data_type>, …].
b) sp_sql <@stmt> [<@param1 data_type>,<@param2 data_type>, …].
c) sp_executesql <@stmt> [<@param1 data_type>,<@param2 data_type>, …].
d) sp_executesql [<@param1 data_type>,<@param2 data_type>, …].
View Answer

Answer: c
Explanation: Using sp_executesql to run dynamic statements gives us a couple advantages over EXEC that are worth noting.

5. Point out the wrong statement :
a) The @stmt parameter in sp_executesql is a Unicode string containing valid SQL commands
b) The input type @Type is passed as the first parameter to sp_executesql
c) We can specify the parameters for both input and output in sp_executesql
d) None of the mentioned
View Answer

Answer: b
Explanation: The output type @retType is passed as the second parameter to sp_executesql.

6. Below Code is procedure for dynamic SQL using ___________ parameter.

CREATE PROCEDURE GetArticle 
               @ArticleID INT 
        AS 
        SELECT ArticleTitle, ArticleBody FROM 
        Articles
        WHERE ArticleID = @ArticleID GO -

a) input and output
b) input
c) output
d) All of the mentioned
View Answer

Answer: b
Explanation: The output of an SP could also be returned in an output parameter.

7. The Dynamic SQL Queries in a variable are __________ until they are executed.
a) Compiled
b) Parsed
c) Checked for errors
d) All of the mentioned
View Answer

Answer: d
Explanation: A Dynamic SQL is needed when we need to retrieve a set of records based on different search parameters.

8. The basic syntax for using EXECUTE command:
a) SP_EXECUTE(@SQLStatement)
b) EXEC_SQL(@SQLStatement)
c) EXECUTE(@SQLStatement)
d) All of the mentioned
View Answer

Answer: c
Explanation: EXECUTE command is demonstrated using :

SET @SQLQuery = 'SELECT * FROM tblEmployees
WHERE EmployeeID = ' + CAST(@EmpID AS NVARCHAR(10))
EXECUTE(@SQLQuery)

.

9. Which of the following is a disadvantage of dynamic SQL ?
a) Stored procedure can not cache the execution plan for this dynamic query
b) Stored procedure can cache the execution plan for this dynamic query
c) Flexability in your code that you can not get with standard SQL
d) All of the mentioned
View Answer

Answer: a
Explanation: Stored procedure can not cache the execution plan for this dynamic query. So, for complex queries you will lose a the performance boost that you usually gain with stored procedures.

10. Which of the stored procedure used for dynamic SQL is prone to attacks ?
a) xp_executesql
b) executesql
c) sp_execute
d) sp_executesql
View Answer

Answer: d
Explanation: sp_executesql executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically.Run time-compiled Transact-SQL statements can expose applications to malicious attacks.

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.

« Prev Page - SQL Server Questions and Answers – DDL Triggers
» Next Page - SQL Server Questions and Answers – SQL Injection
« SQL Server Questions and Answers – DDL Triggers
SQL Server Questions and Answers – SQL Injection »

Deep Dive @ Sanfoundry:

  1. C# Programming Examples on Functions
  2. C Programming Examples on Computational Geometry Problems & Algorithms
  3. Simple C Programs
  4. LISP Questions and Answers
  5. C# Questions and Answers
  6. C# Programming Examples on Networking
  7. MySQL Database Questions and Answers
  8. Oracle Database Questions and Answers
  9. Dynamic Programming Problems and Solutions
  10. SQL Server Questions and Answers
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He is Linux Kernel Developer and SAN Architect and is passionate about competency developments in these areas. He lives in Bangalore and delivers focused training sessions to IT professionals in Linux Kernel, Linux Debugging, Linux Device Drivers, Linux Networking, Linux Storage & Cluster Administration, Advanced C Programming, SAN Storage Technologies, SCSI Internals and Storage Protocols such as iSCSI & Fiber Channel. Stay connected with him below:
LinkedIn | Facebook | Twitter | Google+

Best Careers

Developer Tracks
SAN Developer
Linux Kernel Developer
Linux Driver Developer
Linux Network Developer

Live Training Photos
Mentoring
Software Productivity
GDB Assignment
Sanfoundry is No. 1 choice for Deep Hands-ON Trainings in SAN, Linux & C, Kernel Programming. Our Founder has trained employees of almost all Top Companies in India such as VMware, Citrix, Oracle, Motorola, Ericsson, Aricent, HP, Intuit, Microsoft, Cisco, SAP Labs, Siemens, Symantec, Redhat, Chelsio, Cavium, ST-Micro, Samsung, LG-Soft, Wipro, TCS, HCL, IBM, Accenture, HSBC, Mphasis, Tata-Elxsi, Tata VSNL, Mindtree, Cognizant and Startups.

Best Trainings

SAN I - Technology
SAN II - Admin
Linux Fundamentals
Advanced C Training
Linux-C Debugging
System Programming
Network Programming
Linux Threads
Kernel Programming
Kernel Debugging
Linux Device Drivers

Best Reference Books

Computer Science Books
Algorithm & Programming Books
Electronics Engineering Books
Electrical Engineering Books
Chemical Engineering Books
Civil Engineering Books
Mechanical Engineering Books
Industrial Engineering Books
Instrumentation Engg Books
Metallurgical Engineering Books
All Stream Best Books

Questions and Answers

1000 C Questions & Answers
1000 C++ Questions & Answers
1000 C# Questions & Answers
1000 Java Questions & Answers
1000 Linux Questions & Answers
1000 Python Questions
1000 PHP Questions & Answers
1000 Hadoop Questions
Cloud Computing Questions
Computer Science Questions
All Stream Questions & Answers

India Internships

Computer Science Internships
Instrumentation Internships
Electronics Internships
Electrical Internships
Mechanical Internships
Industrial Internships
Systems Internships
Chemical Internships
Civil Internships
IT Internships
All Stream Internships

About Sanfoundry

About Us
Copyright
TOS & Privacy
Jobs
Bangalore Training
Online Training
SAN Training
Developers Track
Mentoring Sessions
Contact Us
Sitemap
© 2011 Sanfoundry