Database Simplified Headline Animator

Database Simplified

Sunday 10 June 2012

Bad Performance Of View In SQL Server

In this post I am going to describe the problem I encountered in a view, which may affect your view performance badly. Create tables from following script

If(OBJECT_ID('PerfTest1') is Not null)
Drop Table PerfTest1 Create Table PerfTest1(Id int Identity(1,1),Code Int,CodeName Char(30)) Insert Into PerfTest1 Values(1,'Archo'),(2,'Halie'),(3,'Raiye'),(4,'Amity')
Go 300 If(OBJECT_ID('PerfTest2') is Not null)
Drop Table PerfTest2 Create Table PerfTest2(Id int Identity(1,1),Code Int,CodeName Char(40)) Insert Into PerfTest2 Values(120,'LIVE'),(99,'ACTION'),(100,'SLOW'),(200,'PROCESS'),(200,'OUTDOOR')
Go 300

Create Following View

Create View Vw_PerfUnion
Select CodeName From PerfTest1
Select CodeName From PerfTest2

Run Following Query and Check the Execution Plan

Select * from Vw_PerfUnion Where CodeName='Live'


Its obvious that table scan will take since no index has been create on tables. Now lets create index on these tables.


Create NonClustered Index IX_PerfTest1 On PerfTest1(CodeName)
Go Create NonClustered Index IX_PerfTest2 On PerfTest2(CodeName)

Now Run Query Again and Check Execution Plan

Select * from Vw_PerfUnion Where CodeName='Live'


As you can see in above screen shot even after creating index on table PerfTest1 Table Scan is taking Place. This is due to the Datatype mismatch between Two Query in the view. Check the datatype of PerfTest1(CodeName) and PerfTest1(CodeName) Fields they are Char(30) and Char(40) respectively. Now Lets alter the Datatype of PerfTest1 Table. To alter field we need to drop index we have created on that field.

Drop Index PerfTest1.IX_PerfTest1
Alter Table PerfTest1 Alter Column CodeName Varchar(40)
Create NonClustered Index IX_PerfTest1 On PerfTest1(CodeName)

Now run the Query and Check Execution Plan


Sunday 3 June 2012

Restore Database From Snapshot

This is another way of restoring database to a time when snapshot was taken but it has certain restrictions lets see what are those ?

  • Database should have only one snapshot.
  • Cannot restore files removed after taking snapshot.
  • Transaction log backup chain is broken So make sure you take full backup before restore.
  • Restore From snapshot will drop all full text catalogs.

You must understand that restoring database from snapshot is meant to bring your database to stage when snapshot was taken it cannot fix your data corruption issues.

Note : If you have multiple snapshot then you  must delete all the snapshot except one you want to revert to.

Syntax :

USE Master

During restore operation both actual database and snapshot data will be unavailable (i.e. Restoring Mode).

Tuesday 29 May 2012

Import Text File To SQL Server

This blogs describes how flat files or text files can be imported to SQL Server with defining field information in a format file (xml file). Let’s see how ?

Download Sample Text File

So You can download the text file from the given link or if you have your own flat/text file with you then copy file and put it into the folder. For me its “D:\dbsimplified”

Now You must be knowing fields(FieldName) available in your text file. Since my flat/text file contact 8 fields so I have to put description of each field in xml file (The Format File). You can look at the content of xml file

<?xml version="1.0"?>
<BCPFORMAT xmlns="" xmlns:xsi="">
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <COLUMN SOURCE="1" NAME="EmployeeId" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="Blank_Column" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="EmployeeName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Company" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="5" NAME="Email" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="6" NAME="City" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="7" NAME="OtherInfo" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="Level" xsi:type="SQLNVARCHAR"/>

Every fields is defined with datatype, length and terminator , Terminator can vary, in your case it may to Tab separated or Pipe(|) separated or etc. In my case its Tab separated so I mentioned( \t ).

You must copy your format definition into a file and save it as .xml extension. In my case I have saved it as D:\Format.xml

Now use following OpenRowSet Command to Fetch Data From Flat/Text file to SQL Server

Syntax " :

SELECT * FROM  OPENROWSET(BULK  'Flat/Text File Name and Path'  ,FormatFile='Format File Path and Name') as Tab

Let’s run the query

SELECT * FROM OPENROWSET(BULK 'D:\dbsimplfied\UploadData.txt' ,FormatFile='D:\FORMAT.xml') as Tab


Output :