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 …
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 …
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 …
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) …
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 …
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 …
To make your SQL server secure one of the option is to hide the instance from network. To achieve this you need to do following things 1) Go to Configuration tool folder and select configuration manager of SQL server as shown in below fig 2) Once Configuration manager is open select SQL Server Network …
It is just a simple tip nothing fancy. On command prompt you just type SSMS and hit OK. Your SQL server Management studio will be open. You can get other option also by typing ssms/? . You will get below options Now if you want to login to particular SQL Server Instance you can choose …
Although, I did not get chance to use this facility in my projects but it is good to know feature. Suppose you want looping and want to insert data in table then in such situation you can use this GO lopping feature. In below example We are inserting data into debug table with GO statement …
Sometimes, we need to understand what exactly going on with particular statement, stored procedure why it is slow? To check performance of statement what exactly going on we also interested how many logical reads, physical reads a particular statement taking. To achieve this we need to write SET STATISTICS IO ON For exampleGOSET STATISTICS IO …