Primary Key & Foreign Key Constraints

USE Rohan

GO

CREATE TABLE FILES

(

FILEID INT CONSTRAINT PK_FLID PRIMARY KEY,

FILENAME VARCHAR (10)

);

GO

CREATE TABLE SUPPORTINGFILES

(

SUPPORTID INT CONSTRAINT PK_SID PRIMARY KEY,

FILEID INT CONSTRAINT FK_SUPPORTFILES_FID FOREIGN KEY REFERENCES FILES(FILEID)

);

GO

·         When we are creating a table, we have to be pretty clear that which column needs to be a primary key.

·         If we are creating the primary key and that was referenced by another column as a foreign key

in another table , then we cannot drop the table which has a primary key. See below

             “Msg 3726, Level 16, State 1, Line 2

  Could not drop object 'FILES' because it is referenced by a FOREIGN KEY constraint.”

·         If you want to drop the table(FILES table) which has a primary key the first thing you have to do is drop the foreign key constraint(CONSTRAINT FK_SUPPORTFILES_FID in SUPPORTINGFILES table)

ALTER TABLE SUPPORTINGFILES

DROP CONSTRAINT FK_SUPPORTFILES_FID

ALTER TABLE SUPPORTINGFILES

DROP COLUMN FILEID

·         If you want to drop the foreign key column(FILEID in SUPPORTINGFILES table) first you have to drop the constraint and then you can drop the column

ALTER TABLE SUPPORTINGFILES

DROP CONSTRAINT FK_SUPPORTFILES_FID

Working with Concurrency Problems – (Dirty, Non Repeatable & Phantom Reads)

Dirty Reads:

The transaction which is not yet committed and read by the second transaction is called Dirty Read.

This happens when the transaction isolation level is set to Read Uncommitted.

Since the first transaction is not yet committed it may update the record, but the second transaction reads it.

The Images below tells you exactly what is happening.

1) The Transaction has started

2) The Session 1 is updating a record of ID = 2 with a time delay of 8, mean while session2 read the

Uncommitted data

3) The Transaction Ended

USE Rohan

GO

IF OBJECT_ID('dbo.Movies') IS NOT NULL

DROP TABLE dbo.Movies

GO

CREATE TABLE Movies(ID Int,Name Varchar(20),Genre Varchar(20))

INSERT INTO dbo.Movies (ID,Name,Genre)

VALUES(1,'Shark','Documentery'),(2,'WildAnimal','Thriller')

Session 1 Image:

Session2 Image :

Non Repeatable Reads:

This happens when the transaction isolation level is set to Read Committed or less than that (Read Uncommitted).

This happens when session1 retrieves a row and session2 updates the same row and when session reruns the same row the results will be different.

USE Rohan

GO

IF OBJECT_ID('dbo.Movies') IS NOT NULL

DROP TABLE dbo.Movies

GO

CREATE TABLE Movies(ID Int,Name Varchar(20),Genre Varchar(20))

INSERT INTO dbo.Movies (ID,Name,Genre)

VALUES(1,'Shark','Documentery'),(2,'WildAnimal','Thriller')

Session1:

Session 2:

Session 1:

Observe the Second result different than the first result, which shows clearly Non Repeatable Reads

Phantom Reads:

This happens when the transaction isolation level is set to Repeatable Read or less than that (Read Uncommitted or Read Committed).

This is Similar to Non Repeatable reads with a slight change, the change is that Session1 has selected some range of rows

Ex: Where Id in (1, 2) and in the given range if there is any update by the session2 then it’s called as phantom reads.

USE Rohan

GO

IF OBJECT_ID('dbo.Movies') IS NOT NULL

DROP TABLE dbo.Movies

GO

CREATE TABLE Movies (ID Int, Name Varchar(20),Genre Varchar(20))

INSERT INTO dbo.Movies (ID,Name,Genre)

VALUES(1,'Shark','Documentery'),(2,'WildAnimal','Thriller')

Session 1:

Session2:

Session1:

If we see the first result contains only two rows, whereas the second result contains three results

MSBI - SSIS - How To Load DT_NTEXT,DT_TEXT,DT_IMAGE Into Excel from Oledb Source

Load an image into the SQL Server table .

Open bids to load the image into excel destination.

In BIDS Open Data flow task












Drag Oledb Source and edit the Oledb source editor as below




Drag data flow conversion transformation

In Data type select  DT_TEXT , now this is a non unicode ,let's make it unicode for Excel


Now Open Derived column transformation and edit the derived column transformation.

In datatype select Unicode text stream.


Drag flat file destination to the data flow task.

Open flat file destination editor and select New

Select Delimted and Press ok, now edit the flat file destination editor.



In Flat file destination editor , give the description name


Now give the file name , by browsing the excel sheet .

NOTE : the excel sheet should already exist when you are working with a flat file destination.

Select column names in the first datarow and check unicode , now press ok

Now Select Columns Tab and select advanced tab.

now create a column by selecting NEW and name it as Image and press ok

Now Select Mappings and drag derived column to the image like below and press ok.

Just open the excel sheet .