- 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.
|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|
- 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.
- 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.
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[ad]
- 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.
- 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.
- 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.
- 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.