Performance tips- How to clean buffer to test performance TIP#28

 

Dear Friends,

To test the performance of a stored procedure we run the stored procedure several time.

You have observed when you run the stored procedure first time it took time but when you run next time it is faster. It just because the stored procedure /data is cached.

Now , due to this cache problem we got a wrong assumption that the stored procedure is fast but in actual it it taking cache data.

To assure that when we run the stored procedure it will run on new set of data not from buffer data SQL Server provided a option which is called “DROPCLEANBUFFER”

You can run this command as shown in below fig

Either way you can restart SQL Server services also .

It is one of the effective command by which we can evaluate of stored procedure performance.

Enjoy !!

Thanks

Rajat Jaiswal

How to delete duplicate records from a table TIP #27

 

This tip is one of my favorite. Many times you found this problem you table records are duplicate due to various reason and you want to rid of duplicate records.

Lets understand this with an example

Suppose, I have a table which content duplicate rows. In my example I took a temporary table variable with name “@tblDuplicateCountry” .

This table has 3 columns 1) ID int type and identity column 2) Code VARCAHAR(10) type 3) Country VARCHAR(100)

Now see in below snap to make duplicate entry in this table I wrote following statement

Now let’s check duplicate entry how many duplicate records exists for a particular country.

Now if you see above snap you will find that NEPAL not having duplicate entry rest all the country having duplicate records. India has 3 duplicate records while rest of the country having 2 duplicate record.

Now our next step is to delete these duplicate records and show all the non duplicate records.

To achieve this we are using self join as shown in below figure

If you see in above figure you will find, We used a inner join with DELETE and join is based on code ,Country & Id.

I hope this tip will help you somewhere in your work.

Enjoy !!!!

Regards,

Raj

How to Insert multiple values using a single statement tip #26

 

It is very interesting how to insert multiple values with a single statement.

Lets understand this with the help of below example.

Suppose you have a Country table and you want to insert seed (initial values) then you can write those statement like below

 

Now same values can be inserted with another one of the interesting way

So in the above snap you have seen that we can use either Values statement or UNION to insert multiple values with single Insert statement.

Enjoy !!!

Thanks

Raj

How to attach database MDF files with SQL Server tip #25

 

In this tip we will understand how to attach a database. I took Adventureworks2012 database Mdf file which we need to attach.

Now do follow steps

1) Open the sql server and right click on Database folder of object explorer window.

When you right click you will get a menu as shown below

2) When you click on Attach menu you will get a new window which is shown below

3) Now I click on Add button  and select the Mdf file which you want to attach in my case I added Adventuresworks2012.mdf file which is on D drive. Once I added that file I  got following screen as shown below

4) Now click on OK button below when you click it your database will stored (attached)  in SQL Server as shown in figure

Hope it will help you

Enjoy !!!

Security tips- How to encrypt Stored Procedure–TIP #24

 

Sometimes for security reason it is require to encrypt the objects which you have created (Suppose you have deployed your database on client end or shared hosting and you don’t want any other person to see your logic behind your stored procedure and function).

To achieve this you need to use a simple keyword encryption

In below example I have encrypted the proc_GetListUser

 

Now when someone try to see the content of stored procedure generally he/she will write following command

sp_helptext proc_GetListUser

When he/she  will run this command he will get encryption message not the stored procedure content.

 

 

Hope you will use this tip soon.

Cheers!

How to open SQL Profiler from command prompt–TIP # 23

 

To open SQL Profiler (right now I am talking about SQL Server 2012) you need to write Profiler.exe on run command as shown in below figure

for earlier version you can use profiler90.exe.

You can find various parameters option using Profiler.exe –? command

When you run this command you will find below screen.

Although It does not impact how you open profiler but it is good to know thing

Enjoy !!!