Interview Questions Part-4 | What is the Difference between a HAVING clause and a WHERE clause in SQL Server | What is Connection Pooling and why it is used | What are the Authentication Modes in SQL Server | What is the difference between UNION and UNION ALL | What is Identity in SQL Server

What is the Difference between a HAVING clause and a WHERE clause in SQL Server? 

They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query, whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query

What is Connection Pooling and why it is used?

To minimize the cost of opening and closing connections, ADO.NET uses an optimization technique called connection pooling.

The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

What are the Authentication Modes in SQL Server?

Windows mode and Mixed Mode – SQL & Windows.

What is the difference between UNION and UNION ALL in SQL Server? 

The Union operator is used to return the result set of two or more select statements. It will return only distinct values from the tables (Read more Here).

Union ALL operator is used to return the result set of two or more select statements including duplication values (Read more Here)

What is Identity in SQL Server?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email