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.

SQL interview Questions

 SQL interview Questions

• Precedence order for data types
• How to name DB Objects – Best Practices
• Self join in SQL
• Delete duplicate records from a table
• Sql Constraint
• SQL index
• Common Table Expression (CTE)
• What is a Cursor
• Triggers in SQL Server
• Stored Procedures IN SQL
• User Defined Functions IN SQL
• Difference between Stored Procedures and User DefinedFunctions
• Types of SQL Server Functions 
• SQL Server data types
•  Backup/Copy a table in SQL using query 
• SQL Injection
• Checkpoint in SQL Server
• Table Alias Vs Column Alias in sql server

• Types of Locks in SQL Server

• What is the difference between UNION and UNION ALL?
• What are the Advantages of Using Stored Procedures?
• What is Sub-Queries in SQL Server?
• What is Connection Pooling in SQL SERVER?

Table Alias Vs Column Alias in sql server

Table Alias Vs Column Alias in sql server

Sql Server offers an option to give temporary alias name for the Table and it’s Column Names in the query. In that way we can give a meaning full alias to the Tables. And if two tables are joined both have the same column name, then we have to write two part column names i.e. [Table Name].[Column Name] otherwise Sql server gives an ambiguous column name error. If table name is too long it looks to awkward, so better give a alias name to the table and use this alias name in the Two part column name order to avoid ambiguity.

Table Alias Name. In this Query for Emp_Details Table the alias name specified is Emp in the FROM clause. Because of this we can access the table column names by prefixing Emp. 

SELECT Emp.Name, Emp.Phone FROM dbo.Emp_Details Emp

Column Name Alias. In this Query for Emp_Details Table the alias name specified is Emp in the FROM clause. Because of this we can access the table column names by prefixing Emp.  And for the Name column we are specifying the alias name as ‘Emp Name’ and for Phone column the alias name is ‘Phone Number

SELECT Emp.Name AS 'Emp Name', E.Phone AS [Phone Number] FROM dbo.Employee AS Emp

Checkpoint in SQL Server

Checkpoint in SQL Server

Checkpoint is an internal process that writes modified pages from Buffer Cache to Physical disk, apart from this it also writes the log records from log buffer to physical file. Writing of such pages from buffer cache to data file is also known as Hardening of modified pages. It is a dedicated process and runs automatically by SQL Server at specific intervals. SQL Server runs checkpoint process for each Database individually.

Checkpoint helps to reduce the recovery time for SQL Server in the event of unexpected shutdown or system crash and system Failure.

In SQL Server there are four types of Checkpoints:

Automatic: The most common checkpoint which runs as a process in the background to make sure SQL Server Database can be recovered in the time limit defined by the Recovery Interval – Server Configuration Option.

Indirect: newly added to SQL Server 2012 runs in the background but to meet a user-specified target recovery time for the specific Database where the option has been configured. Once the Target_Recovery_Time for a given database has been selected this will override the Recovery Interval specified for the server and avoid Automatic Checkpoint on such DB.

Manual: runs like any other T-SQL statement, once you issue checkpoint command also run to its completion. Manual Checkpoint runs for your current Database Only. You can also specify the Checkpoint_Duration which is optional, this duration specifies the time in which you want your checkpoint to complete.

Internal: As a user you can’t control Internal Checkpoint. Issued on specific operations such as:

1. Shutdown initiates a Checkpoint operation on all databases except when Shutdown is not clean (Shutdown with nowait)

2. If the recovery model gets changed from Full\Bulk-logged to Simple.

3. While taking Backup of the Database.

4. If your DB is in Simple Recovery model, checkpoint process executes automatically either when the log becomes 70% full, or based on Server option-Recovery Interval.

5. Alter Database command to add or remove a data\log file also initiates a checkpoint.

6. Checkpoint also takes place when the recovery model of the DB is Bulk-Logged and a minimally logged operation is performed.

7. DB Snapshot creation.