Question 271 – What are the Cursor Alternatives?

• Set based logic
• SQL Server Integration Services (SSIS) or Data Transformation Services (DTS)
• WHILE loop
• COALSCE
• sp_MSforeachdb – Run The Same SQL Command Against All SQL Server Databases
• sp_MSforeachtable
• CASE expression – Using the CASE expression instead of dynamic SQL in SQL Server

Question 272 – What are the Limitations of Cursors?

• Cursor requires a network roundtrip each time it fetches a record, thus consume network resources.
• While data processing, it issues locks on part of the table, or on the whole table.

Question 273 – What is a SubQuery?

• Subquery or Inner query or Nested query is a query in a query.
• A Subquery is usually added in the WHERE Clause of the sql statement.
• Used when we know how to search a value using SELECT statement, but don’t know the exact value.
• Subqueries are an alternate way of returning data from multiple tables.
• Subqueries can be used with the following sql statements along with the comparison operators like =, <, >, >=, <= etc. SELECT, INSERT, UPDATE, DELETE

Question 274 – What are the Properties of Subquery?

• It must be enclosed in the parenthesis.
• It must be put in the right hand of the comparison operator.
• It cannot contain an ORDER-BY clause.
• A query can contain more than one sub-query.

Question 275 – What are the Types of Subquery?

• Single-row sub query, where the sub query returns only one row.
• Multiple-row sub query, where the Subquery returns multiple rows.
• Multiple column sub query, where the sub query returns multiple columns.

Question 276 – What is a Correlated Subquery?

• A query is called correlated sub query when both the inner query and the outer query are interdependent.
• For every row processed by the inner query, the outer query is processed as well.
• The inner query depends on the outer query before it can be processed.
• Correlated SubQuery can be said to be dependent on the outer query.
• SELECT p.product_name FROM product p WHERE p.product_id = (SELECT o.product_id FROM order_items o WHERE o.product_id = p.product_id);

Question 277 – What are the Properties of Correlated Subquery?

• Can nest many queries you want but it is recommended not to nest more than 16 Subqueries in oracle.
• If a Subquery is not dependent on the outer query it is called a non-correlated Subquery.

Question 278 – What are the different Authentication options and Authentication Modes in SQL Server?

• Authentication Types – Windows and SQL Server Authentication are the two types available.
• Authentication Mode Types – Windows Authentication mode and Mixed Mode are two modes available

Question 279 – What is a Windows Authentication Mode?

• It allows a user to connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account.
• When using ‘Windows authentication mode’ you can only use Windows authentication to connect to SQL Server.

Question 280 – What is a Mixed Authentication Mode(Windows Authentication and SQL Server Authentication)?

• Mixed Mode allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication.
• When using ‘Mixed mode’ you can use either ‘Windows authentication’ or ‘SQL Server authentication’ to connect to SQL Server 2005

Disclaimer – F5debug Interview Questions & Answers Series:

You may recopy extracts from these pages (“the material”) to individual third party websites or to any intranet websites, but only if:

You acknowledge www.f5debug.net as the source of the material. Such acknowledgment should include reference to www.f5debug.net in the copy of the material and should also include “© Karthikeyan Anbarasan, www.f5debug.net “. You inform the third party that these conditions apply to him/her and that he/she must comply with them.