Foreign Key Refers the Columns of Same Table

Introduction

We all know about the Foreign Key constraint. Here in this article, we are not going to discuss about the definition or implementation concept of Foreign Key. But here we are trying to discuss about the Foreign key that refers the another a column of same table.

What is That?

EMPID	EMPNAME	DESIGNATION	MANAGERID
101	Sudip Das	Manager	101
102	Joydeep Das	Group Lead	101
103	Sukamal Jana	Group Lead	105

Here EPID is the Primary Key and the MANAHERID is the foreign key which refers the EMPID of the same table.

In the above situation Employee ID 101 is in manager position, so its Manager ID is same 101. But for Employee ID 102 the Manager ID is 101 and we can insert data without any error.

But in case of Employee ID 103 if we provide the Manager ID 105 it gives us an error as no such employee id (105) is not present and Foreign key gives us an Error over there.

How we implement That

Step-1 [ The Base Table with Foreign Key References ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEEMASTER', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEEMASTER];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEEMASTER]

    (

        EMPID        INT          NOT NULL PRIMARY KEY,

        EMPNAME      VARCHAR(50)  NOT NULL,

        DESIGNATION  VARCHAR(50)  NOT NULL,

        MANAGERID    INT          NOT NULL,

        CONSTRAINT FK_MANAGERID_tbl_EMPLOYEEMASTER FOREIGN KEY(MANAGERID)

        REFERENCES [dbo].[tbl_EMPLOYEEMASTER](EMPID)

      );

Here just look at the definition of foreign key specially the REFERENCES section.

Step-2 [ Insert Firs Record ]

INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]

       (EMPID, EMPNAME, DESIGNATION, MANAGERID)

VALUES (101, 'Sudip Das', 'Manager', 101);

GO

Here we not find any error as Manager ID is the Same as the Employee ID and Foreign key Satisfied.

Step-3 [ Insert Second Record ]

INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]

       (EMPID, EMPNAME, DESIGNATION, MANAGERID)

VALUES (102, 'Joydeep Das', 'Group Lead', 101);

GO

Here we do not get any error as Manager ID 101 is present in the table and Foreign key satisfied.

Step-4 [ Insert Third Record ]

INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]

       (EMPID, EMPNAME, DESIGNATION, MANAGERID)

VALUES (103, 'Sukamal Jana', 'Group Lead', 104);     

GO

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_MANAGERID_tbl_EMPLOYEEMASTER". The conflict occurred in database "PRACTICE_DB", table "dbo.tbl_EMPLOYEEMASTER", column 'EMPID'.

The statement has been terminated.

Here it gives error as Manager ID 104 is not present in the Table.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Isolation Level of MS SQL Server

Introduction

Before understanding the Isolation Level of Microsoft SQL Server, we must recall the first class of Relational Database Management system (RDBAMS) and the ACID property of RDBMS.

A	Atomic
C	Consistency
I	Isolation This property means that each transaction is executed in isolation from others, and that concurrent transactions do not affect the transaction.
D	Durability

Types of Isolation Level in MS SQL Server

There are five type of Isolation level in MS SQL Server

1.    Read Committed (The Default Isolation Level of MS SQL Server)

2.    Read Uncommitted

3.    Repeatable Read

4.    Serializable

5.    Snapshot

Before further proceed with Isolation level we have to clear understanding about two things

Dirty Reads

This is when we read uncommitted data, when doing this there is no guarantee that data read will ever be committed.

Phantom Reads

This is when data that we are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different.

To Check the Current Isolation Level

DBCC useroptions

Read Committed Isolation Level

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

-- Query-1 of Session -1

BEGIN TRAN 

     UPDATE tbl_Test SET Col1 = 2; 

     WAITFOR DELAY '00:00:10'; 

ROLLBACK

--Query-2 of Session -2

SELECT * FROM tbl_Test;

In the above example   the Query-2 is waiting for Query-1 to complete. When the Query-1 is completed then the Query-2 retrieves records.

Read Uncommitted Isolation Level

In this Isolation level, a transaction can read the data which is modified by some other transactions but still not committed. This Isolation level do not issue shared locked to prevent data modification which is read by some other transactions. Also it is not issue exclusive locks to prevents transactions from reading data which is modified from other transaction by not committed yet. It may results in problem like dirty read, lost updates, phantom reads etc. It is the least restrictive Isolation level.

-- Query-1  of Session - 1

BEGIN TRAN 

     UPDATE tbl_Tests SET Col1 = 2; 

     WAITFOR DELAY '00:00:10'; 

ROLLBACK

-- Query-2 of Session - 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT * FROM tbl_Tests;

Here in this example Query-2 is not waiting for Query-1. Query-2 returns records without waiting for Query-1 my cause of Dirty data.

Repeatable Read Isolation Level

This isolation level is higher than the previous two mention isolation level and it does not allows any transaction to read a data that is being modified by some other transaction but not committed yet. Also it doesn't allow any transaction to modify a data if it is being read by some other transaction until the transaction reading that data complete its operation.  This way it eliminates the dirty read and Repeatable reads problem but it doesn't eliminates the Phantom reads.

 --Query - 1  of Session - 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 

BEGIN TRAN 

     SELECT * FROM tbl_Tests; 

     WAITFOR DELAY '00:00:10'; 

     SELECT * FROM tbl_Tests;   

ROLLBACK

--Query - 2  of Session - 2

UPDATE tbl_Tests  SET Col1 = -1

In the above example Query-1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query (Query-2) was forced to wait for Query-1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.

Serializable Isolation Level

This Isolation level do not allow any transaction to read the data unless the other transactions completed their data modification operation. Also it doesn't allow other transactions to modify the data until the current transaction completed its read operation. This isolation level allows a transaction to acquire a read lock (if only read operation) or write lock (for insert,delete,update) for the entire range of records that the transaction is going to affect.

--Query - 1 of Session -1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRAN 

     SELECT * FROM tbl_Tests; 

     WAITFOR DELAY '00:00:10'; 

     SELECT * FROM tbl_Tests;

ROLLBACK

--Query - 2 of Session -2

INSERT INTO tbl_Tests(Col1,Col2,Col3) 

VALUES (100,100,100);

In the above example we will see that the insert in Query-2 waits for Query-1 to complete before it runs eradicating the chance of a phantom read.

If we change the isolation level in Query-1 to repeatable read, we will see the insert no longer gets blocked and the two seleclt statements in Query-1 return a different amount of rows.

Snapshot Isolation Level

In this isolation level, a transaction recognise only data which is committed before the start of the transaction. Any modification of the data after the transaction is begin,  is not visible to any statements of the currently executing transaction. It is like a snapshot of data, given to each transaction. It is done with the help of row version where a separate version of each modified row is maintain in the temp db database dedicated to the transactions. This  isolation level eliminates dirty reads, lost updates, repeatable reads and Phantom reads problem.

ALTER DATABASE myDb 

SET ALLOW_SNAPSHOT_ISOLATION ON;

Hope you like it.

Posted by: MR. JOYDEEP DAS

 

Understanding the Execution Plan [ When Table JOIN occurs Part-III ]

Introduction

Continuing our journey here we see some others operators exist in our execution plan. Here is the query and Execution Plan that we are working from couple of weeks.

Example Query

SELECT e.JobTitle, a.City,
       p.LastName + ', ' + p.FirstName AS EmployeeName

FROM   HumanResources.Employee AS e

       INNER JOIN Person.BusinessEntityAddress AS bea

                       ON e.BusinessEntityID =bea.BusinessEntityID

       INNER JOIN Person.Address a ON bea.AddressID =a.AddressID

       INNER JOIN Person.Person AS p
             ON e.BusinessEntityID = p.BusinessEntityID;

Execution Plan

Compute Scalar

First of all it is not a join operation. As it is covered in our Execution Plan, so we must discuss about it. Here we see the properties of the Compute Scalar.

It is represent a operation named Scalar, generally used for calculation purpose. In our case the alias Employeename = ContactLastname + Conatct.FirstName with comma operators between them. If we look at the property, it is not a 0 cost operators (0.001997).

If we look at the property Expr1008 and click at the ellipsis on the right side of the property page this will open the expression.

Related Reference

Understanding the Execution Plan [ When Table JOIN occurs Part-II ]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table_25.html
Understanding the Execution Plan [ When Table JOIN occurs Part-I ]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-execution-plan-when-table.html
Understanding of Execution Plan – III - C [ The OPERATORS]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-c.html
Understanding of Execution Plan – III - B [ The OPERATORS ]	http://sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-b.html
Understanding of Execution Plan [What happened When SQL statement Execute]	http://www.sqlknowledgebank.blogspot.in/2014/06/understanding-of-execution-plan-what.html
Understanding of Execution Plan – II [Reuse of the Execution Plan]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-ii.html
Understanding of Execution Plan – III - A [ The OPERATORS ]	http://www.sqlknowledgebank.blogspot.in/2014/10/understanding-of-execution-plan-iii-the.html

Summary

In the next level we have to more discuss about our execution plan. So this series will be continued for some more articles. Please be with us.

Hope you like it.

Posted by: MR. JOYDEEP DAS

WINDOW Function

Introduction

The function named WINDOW belongs to SET Function and it applies to the set of rows. SQL Server has had only a partial implementation up to now, but it is coming in SQL 2012. Without going to deeper in WINDOW function we straight go to an Example to understand it in better way.

Step – 1 [ Create a Base Table ]

IF OBJECT_ID(N'dbo.tbl_EXAMPLETABLE', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EXAMPLETABLE];

   END

GO

CREATE TABLE [dbo].[tbl_EXAMPLETABLE]

     (

        IDNO      INT    NOT NULL,

        VALUE     INT    NOT NULL

     );

Step-2 [ Inserting Some Records in Base Table ]

INSERT INTO  [dbo].[tbl_EXAMPLETABLE]

         (IDNO, VALUE)

VALUES (1, 10),(1, 20),(1, 30),

       (2, 20),(2, 30),(2, 40);

SELECT IDNO, VALUE FROM [dbo].[tbl_EXAMPLETABLE];

IDNO        VALUE

----------- -----------

1           10

1           20

1           30

2           20

2           30

2           40

(6 row(s) affected)

Step-3 [ Now try to Group by ]

SELECT   IDNO,

         SUM(VALUE) AS [SUM VALUE],

         AVG(VALUE) AS [AVG VALUE]

FROM     [dbo].[tbl_EXAMPLETABLE]

GROUP BY IDNO;

IDNO        SUM VALUE   AVG VALUE

----------- ----------- -----------

1           60          20

2           90          30

(2 row(s) affected)

Step – 4 [ Now we want the Output Like this ]

IDNO       VALUE      SUM VALUE   AVG VALUE

------------------------------ -----------

1          10         60         20

1          20         60         20

1          30         60         20

2          20         90         30

2          30         90         30

2          40         90         30

Step – 5 [ Is it Possible Before SQL 2012 ]

Yes it is but takes some efforts.

SELECT   a.IDNO,

         a.VALUE,

         b.[SUM VALUE],

         b.[AVG VALUE]

FROM     [dbo].[tbl_EXAMPLETABLE] As a

         INNER JOIN

            (SELECT   IDNO,

                      SUM(VALUE) AS [SUM VALUE],

                   AVG(VALUE) AS [AVG VALUE]

             FROM     [dbo].[tbl_EXAMPLETABLE]

             GROUP BY IDNO)AS b ON a.IDNO = b.IDNO;           

IDNO        VALUE       SUM VALUE   AVG VALUE

----------- ----------- ----------- -----------

1           10          60          20

1           20          60          20

1           30          60          20

2           20          90          30

2           30          90          30

2           40          90          30

(6 row(s) affected)

Step – 6 [  What’s makes Easy in WINDOW function in SQL 2012 ]

We Just use the OVER() Clause

SELECT   IDNO,

         VALUE,

         SUM(VALUE) OVER() AS [SUM VALUE],

         AVG(VALUE) OVER() AS [AVG VALUE]

FROM     [dbo].[tbl_EXAMPLETABLE];

IDNO        VALUE       SUM VALUE   AVG VALUE

----------- ----------- ----------- -----------

1           10          150         25

1           20          150         25

1           30          150         25

2           20          150         25

2           30          150         25

2           40          150         25

(6 row(s) affected)

Step-7 [ We can use Partition By clause within OVER() ]

SELECT   IDNO,

         VALUE,

         SUM(VALUE) OVER(PARTITION BY IDNO) AS [SUM VALUE],

         AVG(VALUE) OVER(PARTITION BY IDNO) AS [AVG VALUE]

FROM     [dbo].[tbl_EXAMPLETABLE];

IDNO        VALUE       SUM VALUE   AVG VALUE

----------- ----------- ----------- -----------

1           10          60          20

1           20          60          20

1           30          60          20

2           20          90          30

2           30          90          30

2           40          90          30

(6 row(s) affected)

Hope you like it.

Posted by: MR. JOYDEEP DAS