Query for finding the Execution time of SPs

June 7, 2010

Query for finding the Execution time of SPs

DECLARE @V3 DATETIME
DECLARE @V1 DATETIME
DECLARE @V2 DATETIME
SET @V1= GETDATE()
SELECT CONVERT(TIME,@V1) START_TIME
EXEC SP_NAME @PARAM= ”
SET @v2=GETDATE()
SELECT CONVERT(TIME,@V2) END_TIME
SELECT @V3=@V2-@V1
SELECT CONVERT(TIME,@V3) DIFF

The above query will give an accurate result with start time,end time,execution time…..


Database Migration

April 21, 2010

Migration of Database

One of the usual tasks done by the DB developers is DB Migration. Migration is nothing but transfer of data from one database to another. Its not always easy if the data need to be migrated to be appended on the target database.
Appending means nothing but add the unavailable data from first database in to target database.

Steps for migration

1. First step for migration understands the Source database. The process includes drawing of a simple Entity relationship diagram for the source database. There will be an important table with an important column which spread across the database. May be more than one table. Our first duty is to identify such tables and the relations. Also we need to identify the static tables. which may be migrated manually .And identification of identity columns and turning on the IDENTITY_INSERT property also important.

2. On next step we have to create an SP for migration. Most important variable which can be taken as the parameter. Select all the columns from the related tables can be put into temporary tables .Dont forget to add conditions while putting the values in temporary tables.
After that we can write the insert statements. and last we can drop the temporary tables.

For eg.

CREATE PROCEDURE PRO_NAME
(
PARAMETER1
)
AS
SELECT
COL1,
COL2….
INTO #TEMPTABLE
FROM SOURCE TABLE
WHERE CONDITIONS

SELECT
COL1,
COL2….
INTO #TEMPTABLE
FROM PARENT TABLE
WHERE CONDITIONS

INSERT INTO DEST TABLE
(
COL1,
COL2….
)
SELECT
COL1,
COL2….
FROM #TEMPTABLE
WHERE CONDITIONS

DROP ALL TEMPEROTY TABLES

Test it with a single value check whether any truncations happenings on the output

3. We can automate the migration using cursors

SELECT col1 INTO temp FROM source

DECLARE @V1 VARCHAR(200)
DECLARE @V2 VARCHAR(200)
DECLARE CUR CURSOR
FOR SELECT col1 FROM # temp
OPEN CUR
FETCH NEXT FROM CUR INTO @V1
WHILE(@@FETCH_STATUS=0)
BEGIN
exec sp @V1
FETCH NEXT FROM CUR INTO @V1
END
CLOSE CUR
DEALLOCATE CUR

4. We may need to write more than one SP for migration process.

Make sure that all data is migrated successfully .Dont forget to take statistics also.


BulkInsert Process

April 12, 2010
Bulkinsert process

Introduction
Usually if we want to Tranfer a text file into a DB table,There are two ways
1.Using SSIS
2.Using SQL
1.Using SSIS
We can use BulkInsert Task or Simple Dataflow task for this process.
2.Using SQL
We can use bcp,Openrowset or Bulkinsert
Scope of this document explains the use of Bulkinsert command.
Usually we prefer Bulkinsert because it is Faster and we can include many functions while Dataloading
The various steps included in the Dataloading are
1.Creation of Temperory or dummy table
2.Data loading to the Temperory table
3.Creation of actual table
4.Transfering data from Temperory table to Actual table
5.Droping of all temperory objects
1.Creation of Temperory or dummy table
This process includes the creation of Temperory table.Here temperory table means the actual physical table.It is not
advisable to use #table if we are loading Huge data.While using the Bulkinsert task we have to remove all Identity column
or default column if it is not available in the row data.And it is advisable to give large values(eg. varchar(1000)
for the first and last columns because there may be unusable vacant space at the end of each row.
2.Data loading to the Temperory table
Once temperory table is created we can load the data in to the Temperory table.We can have look on the Source data before
loading(If the data is more than 1 GB i recomend Jujuedit to view the content)
In normal case we use direct script to load the data
ie for eg.
———————————————
BULK INSERT TEST_TABLE
FROM ‘Test_data.txt’
WITH
(
FIELDTERMINATOR =’,’,
ROWTERMINATOR = ‘\n’
)
—————————————————
This is the script we use in Ideal situations like..If the row data dont have any header. or the row data generated
not by a unix machine where ROWTERMINATOR is not ‘\n’.
FIELDTERMINATOR is nothing but the character used to seperate each fields in the given examle its a comma.
ROWTERMINATOR is nothing but the entity which seperates each row in ideal case it will be newline character ‘\n’
we can enforce identity ,Unique constraint,primary key constraint or NOT NULL constraint by including KEEPIDENTITY
or CHECK_CONSTRAINT inside the bracket.
But we may not be lucky in all the cases.If the data is generated by a Unix based machine then the ideal query will
not work.So we have to use a special dynamic query
ie for eg.
———————————————————
declare @sql nvarchar(4000)
set @sql =
‘BULK INSERT Dest_table
FROM ”\\Loc ”
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ”|”,
ROWTERMINATOR = ”’ + nchar(10) + ”’ ,
LASTROW=3355072
)’
select @sql
exec(@sql)
————————————————————–
Here FIRSTROW is specified because it will skip the header from the rowdata(Otherwise it will show error)
In certain cases LASTROW may be included if the last row of rawdata damaged.we can add IDENTITY or other constraint
like Default can be added to the table
eg scripts
——Adding Identity—————————
ALTER TABLE tbl
ADD tbl_ID bigint IDENTITY(1,1) NOT NULl
——Adding Defaults———————-
ALTER TABLE tbl
ADD RS_STATUS CHAR(1)
CONSTRAINT DF_tbl_Status DEFAULT ‘Y’ NOT NULL
ALTER TABLE tbl
ADD RS_PROCESS_DATE DATETIME
CONSTRAINT DF_CDR_PROCESS_DATE DEFAULT GETDATE() NOT NULL
——-Adding Primary Key—————-
ALTER TABLE tbl
ADD CONSTRAINT CUS_KEY PRIMARY KEY(tbl_ID)
After this process we will get the complete table.But we want to arrange the columns and column space
3.Creation of actual table
Creation of actual table include the process of creating the required table.
the columns should be arranged in the required order and column space should be managed.
4.Transfering data from Temperory table to Actual table
Now we can transfer data from the Dummy table to actual table.
eg.
INSERT INTO
(
COL1,COL2…….
)
SELECT
COL1,COL2…….
FROM DUMMY_TABLE
5.Droping of all temperory objects
After tranfering all data to the actual table we can drop the Dummy table

Bulkinsert process
Introduction
Usually if we want to Tranfer a text file into a DB table,There are two ways
1.Using SSIS2.Using SQL
1.Using SSIS
We can use BulkInsert Task or Simple Dataflow task for this process.
2.Using SQL
We can use bcp,Openrowset or Bulkinsert
Scope of this document explains the use of Bulkinsert command.
Usually we prefer Bulkinsert because it is Faster and we can include many functions while Dataloading
The various steps included in the Dataloading are
1.Creation of Temperory or dummy table
2.Data loading to the Temperory table
3.Creation of actual table
4.Transfering data from Temperory table to Actual table
5.Droping of all temperory objects

1.Creation of Temperory or dummy table
This process includes the creation of Temperory table.Here temperory table means the actual physical table.It is not advisable to use #table if we are loading Huge data.While using the Bulkinsert task we have to remove all Identity columnor default column if it is not available in the row data.And it is advisable to give large values(eg. varchar(1000)  for the first and last columns because there may be unusable vacant space at the end of each row.
2.Data loading to the Temperory table
Once temperory table is created we can load the data in to the Temperory table.We can have look on the Source data beforeloading(If the data is more than 1 GB i recomend Jujuedit to view the content)In normal case we use direct script to load the data
ie for eg.———————————————
BULK INSERT TEST_TABLEFROM ‘Test_data.txt’WITH(FIELDTERMINATOR =’,’,ROWTERMINATOR = ‘\n’)—————————————————
This is the script we use in Ideal situations like..If the row data dont have any header. or the row data generated not by a unix machine where ROWTERMINATOR is not ‘\n’.FIELDTERMINATOR is nothing but the character used to seperate each fields in the given examle its a comma.ROWTERMINATOR is nothing but the entity which seperates each row in ideal case it will be newline character ‘\n’we can enforce identity ,Unique constraint,primary key constraint or NOT NULL constraint by including KEEPIDENTITYor CHECK_CONSTRAINT inside the bracket.
But we may not be lucky in all the cases.If the data is generated by a Unix based machine then the ideal query willnot work.So we have to use a special dynamic query
ie for eg.———————————————————declare @sql nvarchar(4000)set @sql =‘BULK INSERT Dest_tableFROM ”\\Loc ”WITH(FIRSTROW = 2,FIELDTERMINATOR = ”|”,ROWTERMINATOR = ”’ + nchar(10) + ”’ ,LASTROW=3355072)’select @sqlexec(@sql)————————————————————–
Here FIRSTROW is specified because it will skip the header from the rowdata(Otherwise it will show error)In certain cases LASTROW may be included if the last row of rawdata damaged.we can add IDENTITY or other constraintlike Default can be added to the table
eg scripts ——Adding Identity—————————ALTER TABLE tblADD tbl_ID bigint IDENTITY(1,1) NOT NULl
——Adding Defaults———————-ALTER TABLE tblADD RS_STATUS CHAR(1)CONSTRAINT DF_tbl_Status DEFAULT ‘Y’ NOT NULL
ALTER TABLE tblADD RS_PROCESS_DATE DATETIMECONSTRAINT DF_CDR_PROCESS_DATE DEFAULT GETDATE() NOT NULL
——-Adding Primary Key—————-ALTER TABLE tblADD CONSTRAINT CUS_KEY PRIMARY KEY(tbl_ID)
After this process we will get the complete table.But we want to arrange the columns and column space
3.Creation of actual table
Creation of actual table include the process of creating the required table.the columns should be arranged in the required order and column space should be managed.
4.Transfering data from Temperory table to Actual table
Now we can transfer data from the Dummy table to actual table.
eg.
INSERT INTO (COL1,COL2…….)SELECTCOL1,COL2…….FROM DUMMY_TABLE
5.Droping of all temperory objects
After tranfering all data to the actual table we can drop the Dummy table


Use of Switchoffset Function in SQL Server

November 6, 2009

 

–Creating a test table
CREATE TABLE TB
(
FG DATETIME
)
–Insert an element
INSERT TB
SELECT GETDATE()
SELECT * FROM TB
–convert in to Indian offset time(means +5.30)
SELECT TODATETIMEOFFSET(FG,’+05:30′) DATETIME_OFFSET INTO #TEMP
FROM TB
SELECT * FROM #TEMP
–Convert in to Eastern US Time using Switchoffset
SELECT SWITCHOFFSET(DATETIME_OFFSET,’-04:00′) EASTERN_TIME_US FROM #TEMP

–Creating a test table
CREATE TABLE TB(FG DATETIME)–Insert an elementINSERT TBSELECT GETDATE()
SELECT * FROM TB–convert in to Indian offset time(means +5.30)SELECT TODATETIMEOFFSET(FG,’+05:30′) DATETIME_OFFSET INTO #TEMPFROM TB
SELECT * FROM #TEMP–Convert in to Eastern US Time using SwitchoffsetSELECT SWITCHOFFSET(DATETIME_OFFSET,’-04:00′) EASTERN_TIME_US FROM #TEMP

 


Simple but Useful

October 26, 2009
/*SIMPLE BUT USEFUL*/
/*CREATING TABLE A1*/
CREATE TABLE A1
(CL1 CHAR)
–DROP TABLE A1
/*INSERTING ELEMENTS TO A1*/
INSERT INTO A1
SELECT ‘A’
UNION ALL
SELECT ‘B’
UNION ALL
SELECT ‘C’
UNION ALL
SELECT ‘D’
UNION ALL
SELECT ‘E’
/*CREATING TABLE A2*/
CREATE TABLE A2
(CL1 CHAR)
–DROP TABLE A2
/*INSERTING ELEMENTS TO A2*/
INSERT INTO A2
SELECT ‘K’
UNION ALL
SELECT ‘L’
UNION ALL
SELECT ‘C’
UNION ALL
SELECT ‘D’
UNION ALL
SELECT ‘E’
/*GETTING ALL ELEMENTS OF A1 WITH CORRESPONDING MATCH IN A2*/
SELECT * FROM A1 A
LEFT OUTER JOIN A2 B
ON(A.CL1=B.CL1)
/*GETTING ELEMENTS ONLY IN A1 TABLE */
SELECT * FROM A1 A
LEFT OUTER JOIN A2 B
ON(A.CL1=B.CL1)
WHERE B.CL1 IS NULL
/*SQL SERVER 2008 SHORTCUT…USAGE OF EXCEPT*/
SELECT * FROM A1
EXCEPT
SELECT * FROM A2

/*SIMPLE BUT USEFUL*/

/*CREATING TABLE A1*/

CREATE TABLE A1

(CL1 CHAR)

–DROP TABLE A1

/*INSERTING ELEMENTS TO A1*/

INSERT INTO A1

SELECT ‘A’

UNION ALL

SELECT ‘B’

UNION ALL

SELECT ‘C’

UNION ALL

SELECT ‘D’

UNION ALL

SELECT ‘E’

/*CREATING TABLE A2*/

CREATE TABLE A2

(CL1 CHAR)

–DROP TABLE A2

/*INSERTING ELEMENTS TO A2*/

INSERT INTO A2

SELECT ‘K’

UNION ALL

SELECT ‘L’

UNION ALL

SELECT ‘C’

UNION ALL

SELECT ‘D’

UNION ALL

SELECT ‘E’

/*GETTING ALL ELEMENTS OF A1 WITH CORRESPONDING MATCH IN A2*/

SELECT * FROM A1 A

LEFT OUTER JOIN A2 B

ON(A.CL1=B.CL1)

/*GETTING ELEMENTS ONLY IN A1 TABLE */

SELECT * FROM A1 A

LEFT OUTER JOIN A2 B

ON(A.CL1=B.CL1)

WHERE B.CL1 IS NULL

/*SQL SERVER 2008 SHORTCUT…USAGE OF EXCEPT*/

SELECT * FROM A1

EXCEPT

SELECT * FROM A2


BULK INSERT

July 3, 2009
BULK INSERT TEST_TABLE
FROM ‘Test_data.txt’
WITH
(
FIELDTERMINATOR =’,’,
ROWTERMINATOR = ‘\n’
)
Bulk Insert is a method of inserting Text data or Row data in to Sql table directly.
For inserting that we need to specify the Destination table(Here TEST_TABLE)
Source data(Here Test_data.txt), FIELDTERMINATOR(For inserting to each particular element to respective columns) and
ROWTERMINATOR(For terminating each Row).
BULK INSERT
BULK INSERT TEST_TABLE
FROM ‘Test_data.txt’
WITH
(
FIELDTERMINATOR =’,’,
ROWTERMINATOR = ‘\n’
)
Bulk Insert is a method of inserting Text data or Raw data in to Sql table directly.
For inserting that we need to specify the Destination table(Here TEST_TABLE)
Source data(Here Test_data.txt), FIELDTERMINATOR(For inserting to each particular element to respective columns) and
ROWTERMINATOR(For terminating each Row).

Use of OUTPUT ….

July 2, 2009
/*Create one SP for multiplicating two values.And passed the result into the variable @v2*/
CREATE PROCEDURE SP_REF
–DECLARE @V1 VARCHAR(50)
@V2 BIGINT OUTPUT
AS
SELECT @V2=@V2*@V2
RETURN
–drop procedure SP_REF
/*Passed the result of the variable in to @v1 and executed.*/
DECLARE @V1 VARCHAR(50)
SELECT @V1=10000
EXEC SP_REF @V1 OUTPUT
PRINT ‘RESULT:’+@V1
/*Create one SP for multiplicating two values.And passed the result into the variable @v2*/
CREATE PROCEDURE SP_REF
–DECLARE @V1 VARCHAR(50)
@V2 BIGINT OUTPUT
AS
SELECT @V2=@V2*@V2
RETURN
–drop procedure SP_REF
/*Passed the result of the variable in to @v1 and executed.*/
DECLARE @V1 VARCHAR(50)
SELECT @V1=10000
EXEC SP_REF @V1 OUTPUT
PRINT ‘RESULT:’+@V1

Follow

Get every new post delivered to your Inbox.