Question 301 – What is the use of Having and Where Clause in SQL?

• HAVING is just an additional filter to ‘Where’ clause.
• First SQL server filters the rows using WHERE conditions and then performs group by on remaining rows and then filters the rows again with HAVING.
• If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.
• You can’t use HAVING unless you also use GROUP BY.
• HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Question 302 – What is the difference between Having and Where Clause in SQL?

Having

Where

Question 303 – What is the Local Temporary Table?

• They are created using same syntax as CREATE TABLE except table name is preceded by ‘#’ sign.
• When table is preceded by single ‘#’ sign, it is defined as local temporary table and its scope is limited to session in which it is created.

Question 304 – What is the Global Temporary Table?

• Syntax difference between global and local temporary table is of an extra ‘#’ sign. Global temporary tables are preceded with two ‘#’ (##) sign. Following is the definition.
• In contrast of local temporary tables, global temporary tables are visible across entire instance.
• Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.

Question 305 – What is the difference between @@Error and @@Rowcount ?

To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

Question 306 – What is a Service Broker in SQL?

• It is a unique feature in Microsoft SQL Server 2005 which is used to by internal or external processes to send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML).
• Service Broker not only brings asynchronous, queued messaging to database applications but significantly expands the state of the art for reliable messaging.
• The key components of SQL Server Service broker are
• Queue stores the messages for a particular service.
• Dialog is a conversation between two services.
• Conversation group is a group of related conversations.
• Every conversation belongs to exactly one conversation group.
• Activation specifies a sp that will handle messages destined for a particular service.

Question 307 – What is Database Mirroring in SQL?

• Database mirroring involves two copies of a single database that typically reside on different computers.
• One copy of the database is currently available to clients which are known as the principal database.
• Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database.
• Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.
• Database mirroring Increases data protection.
• Database mirroring Increases availability of a database.
• Database mirroring improves the availability of the production database during upgrades.
• Database mirroring cannot be used on system databases.

Question 308 – What is Extended Stored Procedure in SQL?

• An extended stored procedure is a function within a DLL that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.
• Question 309 – How to increase the Performance of a Query in SQL?
• Know the performance and scalability characteristics of queries.
• Write correctly formed queries.
• Return only the rows and columns needed.
• Avoid expensive operators such as NOT LIKE.
• Avoid explicit or implicit functions in WHERE clauses.
• Use locking and isolation level hints to minimize locking.
• Use stored procedures or parameterized queries.
• Minimize cursor use.
• Avoid long actions in triggers.
• Use temporary tables and table variables appropriately.
• Limit query and index hints use.
• Fully qualify database objects.

Question 310 – What is an Execution Plans in SQL?

• It helps to check how the query runs background to fetch the data’s
• Guidelines considered for execution plan
• Evaluate the query execution plan.
• Avoid table and index scans.
• Evaluate hash joins.
• Evaluate bookmarks.
• Evaluate sorts and filters.
• Compare actual versus estimated rows and executions.

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.