What is Connection Pooling in SQL SERVER?

What is Connection Pooling in SQL SERVER?

Connection Pooling is a technique to allow multiple clients to make use of a cached set of reusable connection objects providing access to a database.

Opening and Closing database connections is an expensive process and hence connection pools improve the performance of execution of commands on a database for which we maintain connection objects in the pool. It empowers reuse of the same connection object to serve a number of client requests. Every time a client request is received, the pool is searched for an available connection object and it's highly likely that it gets a free connection object. Otherwise, either the incoming requests are queued or a new connection object is created and added to the pool. As soon as a request finishes using a connection object, the object is given back to the pool from where it's assigned to one of the queued requests. Since most of the requests are served using existing connection objects only so the connection pooling approach brings down the average time required for the users to wait for establishing the connection to the database.

normally Connection Pooling used in a web-based application where the application server handles the accountability of creating connection objects, adding them to the pool, assigning them to the incoming requests, taking the used connection objects back and returning them back to the pool. When a dynamic web page of the web-based application explicitly creates a connection to the database and closes it after use then the application server internally gives a connection object from the pool itself on the execution of the statement which tries to create a connection and on execution of the statement which tries to close the connection, the application server simply returns the connection back to pool.

Connection Pool are configurable means the maximum/minimum connections and maximum idle connections can be configured by the server administrator. On start up the server creates a fixed number of connection objects and adds them to the pool. Once all of these connection objects are exhausted by serving those many client requests then any extra request causes a new connection object to be created, added to the pool, and then to be assigned to server that extra request. This continues till the number of connection objects doesn't reach the configured maximum number of connection objects in the pool. The server keep on checking the number of idle connection objects as well and if it finds that there are more number of idle connection objects than the configured value of that parameter then the server simply closes the extra number of idle connections, which are subsequently garbage collected.

What is Sub-Queries in SQL Server?

Properties of Sub-Query

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

Type of Subqueries

• Single row subquery: Returns zero or one row.
• Multiple row subquery: Returns one or more rows.
• Multiple column subquery: Returns one or more columns.
• Correlated subqueries: Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
• Nested subqueries: Subqueries are placed within another subqueries.

Subqueries: Guidelines

• There are some guidelines to consider when using subqueries:
• A subquery must be enclosed in parentheses.
• A subquery must be placed on the right side of the comparison operator.
• Subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added in to a subquery. You can use an ORDER BY clause in the main SELECT statement (outer query) which will be last clause.
• Use single-row operators with single-row subqueries.
• If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.

What are the Advantages of Using Stored Procedures?

What are the Advantages of Using Stored Procedures?

Stored procedures provide performance benefits through local storage, precompiling the code, and caching. It offers security features that includes encryption and privilege limits that restrict users from modifying structure of stored procedure.

Advantages of using stored procedures are:

• Execution plan retention and reuse
• Query auto-parameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up
• Enhanced hardware and software capabilities
• Improved security
• Reduced development cost and increased reliability
• Centralized security, administration, and maintenance for common routines

Difference between UNION and UNION ALL command in SQL

Both UNION and UNION ALL is used to combine results of two SELECT queries, main difference between them is that UNION doesn't include duplicate record but UNION ALL does.

Another difference between them is that UNION ALL is faster than UNION but may look slow because it returns more data.

 UNION is very different than other SQL commands because it operates on data rather than columns. Anyway, answer of this question is simple, though both UNION and UNION ALL are used to combine result of two separate SQL queries on same or different table,  UNION does not keep duplicate record (a row is considered duplicate if value of all columns are same), while UNION ALL does. Since you mostly don't want duplicate rows, UNION is preferred over UNION ALL in reporting and application development. User should keep in mind that UNION ALL performance better than UNION because it doesn't have to remove duplicate. This keyword is supported by all major database e.g. Oracle, Microsoft SQL Server, MySQL etc.

Difference between UNION and UNION ALL command in SQL

1) Both UNION and UNION ALL are used to combine result of two separate SQL query, it could be on same table or different table but data should be same. 

2) Key difference between UNION and UNION ALL is that former will remove duplicates but later will keep them. In another words, UNION is equal to running distinct on output of UNION ALL. 

3) Due to above difference query execution time of UNION ALL is smaller than UNION, which means former runs faster than later. So if you want faster output and don't care of duplicates use UNION ALL.

4) Keep in mind that benefits gained by not removing duplicates can be easily wiped out by transferring more data over a poor bandwidth network connection. That's why in practice some time UNION ALL appear slower than UNION because it return lot of data with duplicates which require more time to travel from database server to client machine. So evaluate performance of UNION and UNION ALL case by case.

5) Another worth noting thing while using UNION and UNION ALL is that all queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. For example if result of query 1 has three column and result of query 2 has two column then you cannot combine them using UNION command.

Types of Locks in SQL Server.

Types of Locks in SQL Server.

SQL Server levers all locking choices. It selects which type of lock to use after it determines the query plan. You can also force the server to make certain locks more or less restrictive by including the holdlock, noholdlock, or shared keywords with your query.

SQL Server has two ranks of locking: page locks & table locks. Page locks are commonly less restrictive than table locks. A page lock locks all of the rows on the page; table locks lock entire tables.

There are three main types of locks that SQL Server.

Shared locks are used for operations that do not change or update data, such as a SELECT statement. Shared locks are compatible with other Shared locks or Update locks.

Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes. Update locks are compatible with Shared locks only

Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE. Exclusive locks are not compatible with other lock types.

The SELECT Statement sets the Shared lock on the page, because there are no another locks on this page.
The Update Statement set Exclusive lock, but it cannot make it before SELECT Statement will be finished, because Exclusive lock is not compatible with other lock types. So, Update Statement sets Update lock.

SQL Server 2000 supports the following Locking optimizer.

SQL Server 2000 supports the following Locking optimizer.
NOLOCK: NOLOCK is known as dirty reads. This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

HOLDLOCK: HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used.
Note: You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.

UPDLOCK: it instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.

TABLOCK: TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.

PAGLOCK: PAGLOCK is used by default. Directs SQL Server to use shared page locks.

TABLOCKX: It takes an exclusive lock on the table that is held until the end of the command or transaction.

READCOMMITTED: It Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.

READUNCOMMITTED: Equivalent to NOLOCK.

REPEATABLEREAD: it Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.

SERIALIZABLE: it Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.

ROWLOCK: Use row-level locks rather than use the coarser-grained page- and table-level locks.