Floating FB popout byF5debug

Interview Questions and Answers on .Net Framework, OOPS, ASP.Net, C#.Net, SQL Server, WCF Series – Part 31

 
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
It applies to summarized rows (summarized 'with GROUP BY)
It applies to rows
Having can used only with the select statement, typically used with group by clause WHERE clause is used to impose condition on SELECT statement as well as single row function
Used after GROUP BY clause, when it is used without group by it work like where clause Used before GROUP BY clause


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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter