SQL

SQL (Structured Query Language)

What is the difference between where and having clause?

Where Clause is a restriction statement you use where clause to restrict data being accessed from the database. Where clause is used before the result is retrieve, but having Clause is used after retrieving the data. Having Clause is a kind of filtering statement. You always use where clause in preference to having clause if possible.

What is the transaction?

Transaction is a series of data manipulation operations mostly triggered from the client and that must be committed into the database as a whole operation.

All transaction most followed ACID rules.

What is an ACID rule?

Atomic, consistent, isolated, durability

Atomic: all or none operations execute.

Consistent: after the operation executive database is inconsistent state as it was before the execution. 

Isolated: appropriate locks are placed on the shared data.

Durability: data must be firmly resides on a hard disc

What is normalization and de normalization why you sometimes required de normalization? 

Normalization means removing redundancy information from the table. Normalization usually involves dividing the data into multiple tables.

Denomination means allowing redundancy in the table. Denomination reduces the number of joints required for the data processing.

What is the difference between in and exists?

When you are using in for checking condition SQL server does the whole table scan, but in case of exist as soon as the engine finds required row it will stop executing query and go further table scanning.

What is the relational database management system?

It is one database management system that maintains data records and indexes in the table.

It has capacity to recombine the data items from the different files providing powerful tool for data uses.

What is the difference between primary and unique key?

Both enforce uniqueness on the column on which they are applied.

By default primary key creates a clustered index and Unique create non clustered index on the column on which their applied.

Primary key does not allow null value but unique key allows only one value.

What is the difference between delete and truncate command?

Delete removes rows from the table with or without where clause, truncate removes all the rows from the table without a where clause. Delete can activate trigger, truncate can’t activate trigger.

What is the sub query, explain properties?

A query instead query is a query.

A sub query select statement can stand alone; it is not dependent on the statement in which it is nested.

A sub query must and closes in the parentheses.

A sub query must put in the right hand side of the comparator operator.

A sub query cannot have order by clause.

A query can have more than one sub query.

Where are SQL server username and password stored?

In master database, syslogins table

What is an execution plan?

It is the road map that graphically or textually shows data retrieval methods. It is very helpful tool for the developers to understand the performance of query and stored procedures.

What is the difference between varchar and varchar(2)?

Both supports strings but varchar(2) supposed alphanumerical strings.

Varchar: 2000 bytes, Varchar(2) : 4000 bytes

What is a trigger?

It is a statement that executes automatically as a side effect of modifications in the database.

You cannot call trigger explicitly. Triggers cannot have return values.

Can you use commit or rollback in side a trigger?

No, because these are the parts of trigger in SQL statements.

How to copy table structure and data into new table?

SELECT * INTO “newtable” FROM “oldtable”, this will not copy any indexing.

What is SQL injection?

It is an attack on database when attacker executes an authorised SQL commands.

SQL injection attacks are used to steal information from the database.

You can avoid these attacks by applying strong input validations.

How many types of triggers are there?

There are 4 types of triggers. Insert, Delete, Update and instead of

What is constraint?

It enforces limitations on the data than can be entered into a particular column of the table

Ex: unique key, primary key, foreign key, not null and check

What is the maximum size of a row?

8060 bytes

What is implicit transaction and explicit transaction?

Implicit transaction auto commit, there is no beginning or ending of a transaction.

Explicit transaction has beginning ending and rollback of the transaction.

How to change database name in SQL server?

Exec sp_rename ‘olddatabasename’, ‘newdatabasename’

What is a cursor in SQL server?

It is a set of rows together with a pointer that identifies the current row.

What is the #tmp table?

It gets created system database temp tables.

Data in this #tmp tables is available only in current scope.

Generally these tables cleaned up automatically when the current procedure goes out of scope. However we should be manually dropped the stable once we have done with it.

What is a table variable?

It is just like declaring variables in SQL. It is not physically stored in the hard disk rather stored in memory. It is useful for storing less than 100 records. There is no need to drop this table as it will automatically drop when the scope is finishes.

What is the maximum limit for a foreign key?

253

How many tables can use in single select statement?

256

What is the maximum limit for primary key?

900 bytes

What is the use of trancount in SQL?

It returns a number of active transactions for the current connection.

What is a tuple?

It is an instance of the data relational database.

What is the check constraint?

Check constraint specify a condition that enforces for each row of the table on which it is defined.

Can we create a foreign key without primary key?

Yes, you can create foreign key on unique key also.

What are the differences between varchar and char?

Varchar, if a string is less than that of the maximum length it is stored without extra characters.

Char, if a string is less than that of the maximum length it gets padded with the extra characters so that its length is set to the defined length.

Varchar is used when strings are not of the fixed length. E.g. name.

Char is used for a fixed length string. E.g. pin code or ZIP code.

What is a row number in SQL?

It returns the row number in a given result set.

What is the difference between local and global temporary table?

Local temporary tables created with single hash i.e. #

Global temporary tables created with double hash i.e. ##

Local temporary tables exist only for the duration of a connection.

Global temporary tables are visible to all sessions.

A global temporary table gets drop, when the sessions that created ends and all other sessions stops fetching it.

Can we use truncate command on a table which is referred by foreign key?

No, because of referential integrity

What is a referential integrity in SQL?

To avoid logical corruption of data, we need to maintain relationship on most of the database. We can enforce such referential integrity through foreign key constraint.

What type of language is select command?

DML - data manipulation language

Can we use print in a function?

No, you cannot

What is the use of “waitfor” SQL?

Supposed to execute one query now and one query sometime then you can use “waitfor”.

What are the maximum parameters you can pass in a stored procedure?

2100

What are the magic tables?

Sometimes we need to know about the data which is being inserted and deleted by the triggers in the database. With the insertion and deletion of data, table named “inserted” and “deleted” gets created in the SQL server, which contains the modified, deleted data, these tables are called as magic tables.

What is the sign in SQL?

Returns the positive (+1), zero (0), or negative (-1) values

How can you insert multiple rows together in a single insert statement?

INSERT INTO City (CityId, CityName) VALUES (‘1’,’Pune’), (‘1’,’Mumbai’)

What is the difference between dynamic SQL and stored procedure?

Dynamic SQL is a bunch of statements that dynamically constructed at the runtime and not stored in the database. Whereas are stored procedures are stored in a database in compiled form.

What is an identity column in a table in SQL?

Set IsIdentity=”Yes” or “No” in a column

If set as “yes”, then the column becomes auto increment column.

There can be only one identity column per table.

What are the wild characters in SQL?

“%“and “_” are the wild characters in SQL.

How can you pick up random records in SQL?

ORDER BY NEWID()

What is the difference between count and count Big?

Both gives the total number of rows in the table, count gives int value, countbig gives bigint value.

What is the difference between varchar and nvarchar?

Varchar 8 bit; nvarchar 16 bit, varchar character string; nvarchar string with symbols,

varchar faster; nvarchar slower,

What is the difference between Union and union all?

Union select only distinct values; union all can have duplicate values.

Union output is in sorted order; Union all output is not in sorted order

What is the difference between stored procedure and function?

Stored procedures store in a compiled format, function compiled at runtime.

Insert, update, delete is possible with the store procedure, it is not possible in function.

Stored procedure may or may not return values, functions has to return a scalar value or table value. To run store procedure you need to use execute command, you cannot execute functions with the execute command.

Stored procedures have both input and output parameters, functions have only input parameters.

What are keys?

Key allows us to identify set of attributes and distinguish entities from each other.

What are the types of keys in SQL?

Super key, candidate key, primary key, foreign key, unique key, compound key and alternate key

Super key: It is a set of one or more attributes that allows identifying uniquely an entity set.

Ex. Table named “Customer” with columns “CustomerID”, “CustomerName” and “CustomerStreet” “CustomerID” is super key as it is sufficient to distinguish one customer from another customer “CustomerName” cannot be a super key because two customers can have same names.

Also combination of “CustomerID” and “CustomerName” can be a super key.

Candidate key: A super key for which no proper subset is a super key such minimal super keys are called as candidate keys. In above example the combination of “CustomerName” and “CustomerStreet” is a super key. Thus we have three super keys like “CustomerID”, combination of “CustomerID” and “CustomerName” and combination of “CustomerName” and “CustomerStreet”

Here “CustomerID” and combination of “CustomerName” and “CustomerStreet” are candidate keys

But combination of “CustomerID” and “CustomerName” cannot be candidate key because “CustomerID” itself is a candidate key.

Primary key: It does not allow any duplicate values and also does not allow any null value.

Primary key should be chosen such that its attribute value never really changes.

Foreign key: foreign key refers one table into another table, generally represents primary key of one table as a foreign key into another table.

Unique key: unique key also enforces uniqueness on the column on which it is applied but it allows only one null value.

Compound or composite key: It consists of two or more attributes

Alternate key: It is any candidate key which is not selected to be a primary key.

How to increase SQL server performances?

Every index increases the time it takes to perform insert, update and delete so  the number of indexes should not be very much,

Try to use maximum 4 to 5 index,

Try to keep your indices as narrow as possible, this will reduce the size of index and reduce the number of reads required to read the index.

Try to create indexes on the column that have integer value rather than character values.

Clustered index are more preferable than the non-clustered index.

Try to use much possible where clause filters.

Select only those fields which are really required.

Joins are expensive in terms of time and always try to join on index fields.

What is indexing?

Indexing is used for faster search or retrieves data faster from the various tables.

What are the types of index?

Cluster index and non-cluster index.

Clustered index: In cluster index the leaf level is actually the data page. Table with the cluster index data is physically sorted on the data page in ascending order.

Non cluster index: In non-clustered index the leaf levels contains the key values not the actual data, This key values map to the pointers or clustering keys that locate rows in the data pages.

The implementation of non-clustered index depends on whether the data of pages of the table are managed as a heap or as a clustered index.

Some more facts about indexing

Table without indexing is called as heap.

You can have maximum one clustered index on the table.

Cluster index is a good for range searches.

Non-cluster index is good for random searches.

Error handling in SQL

Use TRY CATCH methods

BEGIN TRY

BEGIN TRANSACTION

                /* sql statements */

COMMIT TRANSACTION

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION;

    DECLARE @ErrorNumber INT = ERROR_NUMBER();

    DECLARE @ErrorLine INT = ERROR_LINE();

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

    DECLARE @ErrorState INT = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH

What is normalisation explain different levels of normalisation?

Normalisation, usually involve dividing data into multiple tables. Normalisation of data is nothing but removing redundant information from the table.

Explain different levels of normalisation?

First normal form (1NF), Second normal form (2NF), Third normal form (3NF)

First normal form (1NF):

For the table to be in first normal form data must be broken in the smallest unit as possible.

Ex: We have table named “Customer” and columns are “CustomerID”, “CustomerName”, “City1”, “City2”, “Unit”, “Quantity” and “Total”

For first normal form;

Step 1: columns City1 and City2 are repeating so remove one column,

Step 2: you can break “CustomerName” into “FirstName” and “LastName”

Now table columns are as

“CustomerID”, “FirstName”, “LastName”, “City”, “Unit”, “Quantity” and “Total”

Second normal form (2NF):

Step 1: a table is said to be in second normal form when it meets all the conditions of 1NF,

Step 2: Move redundant data to another table.

Step 3: Create relationship between these table using foreign keys.

Third normal form (3NF): In about example the total is a product of unit and quantity remove total from the table.

Now table columns are

“CustomerID”, “FirstName”, “LastName”, “City”, “Unit” and “Quantity”

What are e r diagrams?

Entity relationship diagram shows the relationship between various tables in the database.

How many types of relationship exist in the database designing?

3 types one to one, one to many, many to many

One to one: is a very simple

One to many: ex a customer can have multiple sales, so there exist one to many relationship between customer and sales table.

Many to many: In a company one employee can have multiple skills like java, C#, Asp.net etc. and also one skill belongs to many employees.

Explain Indexing in detail

Indexing database is really similar to the indexing a book. There are two types of indexes

Clustered index and non-clustered index

Always prefer cluster index and non-clustered index.

If you create a primary key on a table by default it will create a cluster index on the table.

You can have maximum one clustered index per table.

Non-cluster index is useful for the columns that I have repeated values.

E.g. Account type column in a bank database may have 10 million rows, but the distinct values of account type may be between 10 & 15.

Note: non-cluster index doesn't get created automatically, you have created it manually.

Drawbacks of indexing: It increases the database. When you create an index, it will then create a new structure like table but it is index table. So if you have a bunch of indexes on the table then it will increase the size or space.

Your search query may be faster but insert, update, delete query will be slower, and reason for that as a new data coming into table the index table has to be updated also.

Very important note: indexes are a lot of our trial and error depending on the database designing SQL queries and database size.

What is output parameter?

Output parameters are the normal stored procedure parameters that mark with the keyboard output or out.

Output parameter allows you to capture the output of the store procedure in a local variable.

Use output parameter when you know you are dealing with single row scalar values.

What is the difference between output parameter and return value?

Output parameters are the normal stored procedure parameters that mark with the keyboard output or output.

Output parameters allow us to capture the output of the store procedure in a local variable.

Return value: Whenever stored procedure finishing execution it always returns a value,

By default this return value is zero. Use return statement to return the value.

Note: you can always return integer value to restore return type.

What is the CLR stored procedure?

We usually face a problem in stored procedure when we need to implement some complicated lodging within, in many cases we found C# or VB classes are more help full to implement complex logic. So Microsoft added a feature called “CLR stored procedures” to deal with.

In short CLR stored procedures are the dot net objects which are run in the memory of database.

What are the advantages of CLR stored procedure?

CLR stored procedure can replace a standard stored procedure that contains a complex logic and rules and which is a tough task in standard stored procedure.

It is convenient for some programmers are stored procedure can be written in C# and VB clasess.

It not only includes store procedure but also includes functions and triggers.

Drawbacks of CLR stored procedures?

CLR stored procedure should not be used to replace simple queries, cause in such standard stored procedures can give better result.

Deployment may be difficult in some scenarios.

One should always consider CLR stored procedure before going for extended stored procedure.

What is a view in SQL?

View is a virtual table, which contains columns from one or more tables.

It doesn't contain any data directly. It is a database objects.

When to use view?

When you when you have a complex queries that used in many places in stored procedure or function etc. View will be used as a security mechanism in the web applications.

When we use an original table in a web application, the hacker may drop the table.

When you want to hide particular columns of table from the developer or from users.

Along with the security another advantage is data abstraction, as the end user is not aware of all the data in table.

What are the types of views?

3 types Normal or Standard view, Index or Permanent view and Partition view

Normal or Standard view: is used most frequently used by the developers. ex.

CREATE VIEW viewName

AS

SELECT * FROM tableName

In this example the view is only one table.

Index view:  The indexed view can be created with the schema binding option.

You can create index on the table, you cannot use top, distinct, union, order by and aggregate functions.

In case of index view when you retrieve data from the table, it will execute like a normal table.

What is partition view?

This view will execute like a normal view. It will work across database and across the server.

There are two types of partition view. Local partition view and global partition view.

Local partition view: can be created with the same server but different databases

Global partition view: works across the server.

Some more facts about view

You we cannot pass parameters to give.

View doesn't occupy memory.

View cannot be relied on temporary tables.

We cannot use order by clause in view directly, but the condition is we have to include top or for XML clauses.

Execution of DML is possible for view if the view uses only one table.

What will happen when you execute a stored procedure inside the same stored procedure?

It will generate an error that stored procedure nesting level exceed limit 32.

What are computed or calculated columns in SQL?

Computed columns are the columns that can be used to store the calculation result based upon some other columns of the table. e.g

CREATE TABLE Customer (CustomerId INT, FName Varchar(10), LName Varchar(10),

FullName AS (FName + ‘ ’+ LName))

Here “FullName” is out computed column.

Note: we cannot insert or update in a computed column.

What are the advantages of using computed column?

Using computed columns enable to save some calculation logic of such computed values in database, as a result this will reduce your every time extra coding that you have to do when required the computed item.

Some more facts about computed columns

Computed columns can use other columns in the same table as well as using other table values via user defined functions.

Computed columns are actually not physically created on the table unless they are defined as persist computed columns, think of a computed column as a virtual column as they are not physically store in the table, but they are calculated every time whenever they are referred in a SQL statement.

It you want to create an index on a computed column or if you want store computed columns to store in the database instead of calculating every time, you should define it as persist.

A computed column is just like ordinary table columns that can be used in select, where and order by clauses.

We cannot insert or update in computed column directly.

A computed column is computed from an expression, but this expression cannot be a sub query only scalar expressions are allowed.

You cannot create persisted computed column using user defined functions.

What makes a CTE different from a Derived Table/Subquery? When to use it?

Common Table Expression (CTE) was introduced in SQL Server 2005.

A CTE is a temporary result set similar to a derived table which is not stored as an object.

This result set lasts only for the duration of the query.

You can reference a CTE within a SELECT, INSERT, UPDATE or DELETE statement.

 //Derived Table

SELECT StudentId, StudentName From

(

    SELECT StudentId, StudentName FROM Student

) tmp

// CTE:

;WITH Cte AS

(

    SELECT StudentId, StudentName FROM Student

)

SELECT StudentId, StudentName FROM Cte

CTE has few advantages over a derived table

 A CTE can make a reference to itself. An example of self-referencing is Recursion.

CTE can be used recursively whereas a sub-query cannot. With a sub-query, if you need to use the results more than once, you have to duplicate the query.

A CTE can be referenced multiple times in the same query.

A CTE is easy to implement when compared to complex queries which involves several sub-queries.

What is the Difference between ROW_NUMBER(), RANK() and DENSE_RANK()?

All of these three functions are used to calculate RowID for the result set returned from a query but in a slightly different way.

Row_Number()

This function will assign a unique id to each row returned from the query.

SELECT Col_Value,ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'RowID'

FROM myTable

Note : As we notice, each and every row has a unique ID.

Col_Value           RowID

A                             1

A                             2

A                             3

B                             4

B                             5

C                             6

C                             7

Rank()

This function will assign a unique number to each distinct row, but it leaves a gap between the groups.

Hide   Copy Code

SELECT Col_Value, Rank() OVER (ORDER BY Col_Value) AS 'RowID'

FROM myTable

Note: As we can see, rowid is unique for each distinct value, but with a gap.

Col_Value           RowID

A                             1

A                             1

A                             1

B                             4

B                             4

C                             6

C                             6

Dense_Rank()

This function is similar to Rank with only difference, this will not leave gaps between groups.

SELECT Col_Value, DENSE_RANK() OVER (ORDER BY Col_Value) AS 'RowID'

FROM myTable

Note: As we can see, rowid is unique for each distinct value, with no  gap.

Col_Value           RowID

A                             1

A                             1

A                             1

B                             2

B                             2

C                             3

C                             3

What is a transaction ?

Transaction is a group of commands that change the data stored in a database.
Transaction is treated as a single unit of work.
Databases are used by many users and aplications at the same time.
This means there are concurrent transactions running all the time on a database.

Allowing concurrent transactions is essentials for performance. But keep in mind 
they may introduce consurrency issues when two or more transactions are working with 
the same data at the same time.

Common Concurrency Problems.
Dirty Reads
Lost Updates
Nonrepeatable Reads
Phantom Reads


SQL Server Transaction Isolation Levels
ReadUncommited
ReadCommitted
Repeatable Read
Snapshot
Serializable

	Dirty Reads	Lost Update	Nonrepeatable Reads	Phantom Reads
Read Uncommited	Yes	Yes	Yes	Yes
Read Committed	No	Yes	Yes	Yes
Repeatable Read	No	No	No	Yes
Snapshot	No	No	No	No
Serializable	No	No	No	No

Dirty Reads
A dirty read happens when one transaction is permitted to read data 
that has been modified by another transaction that has not yet been committed.
In most cases this would not cause a problem. However, if first transaction is roll 
back after the second reads the data. the second transaction
has a dirty data that does't exists anymore.


Read Committed:
The default transaction isolation level is read committed
That means it will not read until transaction committed i.e. transaction 2 will wait untill
transaction 1 commited
EX:

--TRANSACTION 1

BEGIN TRAN
UPDATE tblInventry SET Qty = 9
WAITFOR DELAY '00:00:15'
ROLLBACK TRAN

--TRANSACTION 2
SELECT * FROM tblInventry  -- wait (block) for transaction 1 to committed


Read Uncommited:
You can read uncommited data we have to use Read Uncommited
EX:

-- TRANSACTION 1

BEGIN TRAN
UPDATE tblInventry SET Qty = 9
WAITFOR DELAY '00:00:15'
ROLLBACK TRAN

-- TRANSACTION 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM tblInventry

Note: Here the output is 9 before commiting the query it is a dirty read.
Once data gets commited it will read 10 again.
Read Uncommited transaction isolation level is the only isolation level 
that has dirty read side effect.
Another way to read dincommited data is 
SELECT * FROM tblInventry (NOLOCK)


Lost Update Problem:
Lost update Problem happens when 2 transactions read and update same data.
EX

--TRANSACTION 1

BEGIN TRAN
DECLARE @ItemStock INT
SELECT @ItemStock = Qty FROM tblInventry 
WAITFOR DELAY '00:00:15'
SET @ItemStock = @ItemStock - 1

UPDATE tblInventry SET Qty = @ItemStock

PRINT @ItemStock 
COMMIT TRANSACTION


--TRANSACTION 2

BEGIN TRAN
DECLARE @ItemStock INT
SELECT @ItemStock = Qty FROM tblInventry 
WAITFOR DELAY '00:00:01'
SET @ItemStock = @ItemStock - 2

UPDATE tblInventry SET Qty = @ItemStock

PRINT @ItemStock 
COMMIT TRANSACTION

Note: Transaction2 committed after 15 sec so final Qty we get is 9 which is wrong
it should be 7
Thus we face a lost update problem.
This lost update problem occures in ReadUncommited and ReadCommitted isolation level
Other higher isolation levels we don't have this lost update problem.

Repeatable Read:
Lets set // SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 

--TRANSACTION 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN
DECLARE @ItemStock INT
SELECT @ItemStock = Qty FROM tblInventry 
WAITFOR DELAY '00:00:15'
SET @ItemStock = @ItemStock - 1

UPDATE tblInventry SET Qty = @ItemStock

PRINT @ItemStock 
COMMIT TRANSACTION

--TRANSACTION 2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN
DECLARE @ItemStock INT
SELECT @ItemStock = Qty FROM tblInventry 
WAITFOR DELAY '00:00:01'
SET @ItemStock = @ItemStock - 2

UPDATE tblInventry SET Qty = @ItemStock

PRINT @ItemStock 
COMMIT TRANSACTION


Here Transaction 1 completed successfully but transaction 2 fails
We get error
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


Non Repeatable Read:
It happens on etransaction reads data twice and another transaction update that data in between the first and second read of transaction 1

--TRANSACTION 1

BEGIN TRAN
SELECT Qty FROM tblInventry
-- do some work
WAITFOR DELAY '00:00:010'
SELECT Qty FROM tblInventry
COMMIT TRANSACTION


--TRANSACTION 2

UPDATE tblInventry SET Qty = 5
Output of Transaction 1 is like
10
5  -- side effect of inbetween transaction 2 

To fix this set the isolation level of transaction 1

--TRANSACTION 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN
SELECT Qty FROM tblInventry
-- do some work
WAITFOR DELAY '00:00:010'
SELECT Qty FROM tblInventry
COMMIT TRANSACTION

--TRANSACTION 2

UPDATE tblInventry SET Qty = 5
Output of Transaction 1 is like
10
10  -- no side effect of inbetween transaction 2 


Phantom Reads:
Phantom reads happens when one transaction executes a query twice and it gets a different
number of rows in the result set each time.This happens when a second transaction inserts a new row that matches the where clause of the 
query executed by first transaction.

--TRANSACTION 1

BEGIN TRAN
SELECT * FROM Employees  
-- do some work
WAITFOR DELAY '00:00:010'
SELECT * FROM Employees -- one additional record as a side effect of transaction 2
COMMIT TRANSACTION

--TRANSACTION 2

INSERT INTO Employees (EmployeeId, EmployeeName)
VALUES (3,'XYZ')


To fix the Phantom read problem we can eighter use snapshot or serializable isolation levels
serializable isolation levels:

--TRANSACTION 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM Employees 
-- do some work
WAITFOR DELAY '00:00:010'
SELECT * FROM Employees -- No effect of Transaction 2
COMMIT TRANSACTION


--TRANSACTION 2

INSERT INTO Employees (EmployeeId, EmployeeName)
VALUES (3,'XYZ')
Note: Here transaction 2 will block and 
it will allow to continue only after transaction 1 has completed.

Difference between Repeatable Read and serializable