& Sql server fundas with Rajat Jaiswal

August 22, 2014

Get Random records on each fetch using TABLESAMPLE TIP #34


As discussed in previous tip which is TIP #33 we can get random result set by using NEWID function.

Now same can be achieve by using one more option which is called TABLESAMPLE.

We have following syntax for it


For example I am using same Adventureworks database and fetching top 10 records from Person table which should be different each time.

So, I will write following select query 

SELECT TOP(10) FirstName,Lastname,MiddleName from Person.Person TABLESAMPLE(10 Percent)


SELECT TOP(10) FirstName,LastName, MiddleName From Person.Person TABLESAMPLE(1000 Rows)

When , I run it I got following records.


So this is another a way of  fetching random numbers.

Enjoy !!!


August 21, 2014

Get different records or random order at each time when you fetch record by newId() TIP#33

Hello friends,

Sometimes your project require whenever a page load or user search then each time you need to show a random order or we can say different records or order.

To achieve this we can use following command

SELECT * FROM yourtableName order by NewID()

in below snap I am fetching person table of Adventureworks database



So each time when I press F5 I get different records or order.

The only thing which need to remember here NEWID() make your fetch slow on large collection so the best idea is use it with selected record set.

I hope this will help you somewhere enjoy.


Rajat Jaiswal

August 13, 2014

Easy way to find result set of a stored procedure without running it- TIP #32


Sometimes , We need to know what is the structure of stored procedure ? , How many columns will be return ?

So to determine this we have easy stored procedure provided by SQL Server which is sp_describe_First_Result_Set.

Below is the template to run it. You can provide your stored procedure name as variable

Execute sp_Describe_First_Result_set ‘ProcedureName’

In below snap I used Adventureworks database and try to find what will be the output columns of the stored procedure “uspgetBillOfMaterials”

As you see in below image after running above query we got all the result set columns


I hope this tip help you somewhere.

Enjoy !!!

August 6, 2014

Performance tips :- Different reports to analyze your SQL Server who is making it slow. TIP#31

Dear Friends,

One of the really important part of any database is performance. Everyone complaining your system is slow , your database performance is slow but why it is slow ?

Most of the time we are not aware what is the source?  Which SQL statement creating problem?

Lets understand this thing why our SQL Server is slow ?

Which Query is culprit of making our server slow ?

Which query eating CPU ?

All these question is easily answered by SQL Server itself but most of the time we are not aware of this.

SQL Server provides a unique option which is reporting. These reports has several option like

1) TOP Queries by TOTAL CPU

2) TOP Queries by TOTAL IO  and many more as shown in below figure

You can get this option by right click on SQL server Instance as shown in below figure.


Now you can select any reports provided in the option for example when I select TOP queries by total CPU time then I get following report


So, If you see in above image you will get top CPU consuming statement.

With the help of these reports you at least aware where you need to work more.

So be prepare for that.

I hope this tip will help you to dig your performance dragger and make your customer happy by resolving those panic statements.


Enjoy !!!

Rajat Jaiswal

August 5, 2014

Import the data in main table from another table if that data not exist in main table Tip #30


Dear Friends,

Sometimes we get task like, we need to import data in a target table from a source table and the condition is that the data which we are going to import should not exist in target table .

To achieve this we have two ways

1) Left outer join

2) Except clause

Lets understand this by an example

Suppose in my database I have a table with name tblUser  which has few columns and few rows as shown below


Now suppose I have a table with name @tblToBeImport which has some data related to Username & Password.

In this table some rows having same username & password which already exist in our source table as shown in figure


Now our motive is to first fetch those data from tblToBeImport (source table) which need to be insert in tblUser table (target table)

It means if username & password already exists in tbluser then we will not insert it else we will create a new entry.

Here , We are using below left outer join to find the rows which not exists in tblUser but exists in tblToBeImport

In below snap you will find there we applied Left outer join to achieve this


SELECT tmp.username,Tmp.uPassword
FROM @tblToBeImport tmp
LEFT OUTER JOIN tblUser u ON u.UserName = tmp.username
             AND u.Password = tmp.uPassword

Now, same can be achieve using EXCEPT clause  as shown in below figure


SELECT username,
FROM @tblToBeImport
FROM [dbo].[tblUser]       

Now we have selected data which not exists in tbluser but exists in @tblToBeImport  we can easily insert it in tbluser table.

Now my personal experience Left outer join is very slow on large table comparatively EXCEPT clause.

I prefer to user Except clause .


July 30, 2014

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

Filed under: DENALI,Sql server,SQL SERVER 2008 - R2,SQL SERVER 2014 — indiandotnet @ 4:11 am
Tags: ,


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 Smile .

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

Enjoy !!


Rajat Jaiswal

July 20, 2014

Understanding of new VNext web project

Filed under:,Visual Studio 2014,VNext — indiandotnet @ 4:19 pm
Tags: ,


Dear Friends,

As discussed in last post VNext launched. Now I downloaded VNext to test how to use it.

Let’s understand it step by step

Now when you will try to open new project window. You will find 4 new project template with name VNext as shown below in fig



I tried ASP.NET vNext Web Application.

Now I found a new structure of  Web application and surprised that in next project there is no web.Config file instead on it I found 3 different files

which are

1) Config.json

2) project.json

3) Startup.cs


Now lets understand these files one by one

1) Config.json :- Config.json  by the name it is clear that it will keep all the configuration detail Like your database connection string and etc will reside in this file. see below config.json’s snap


2) Project.json : Project.json file contents all the reference of the project the best part of it as you add the entries in this file it will automatically reflect your project reference. I personally like this  miracle Smile


3) Startup.cs:-

It is something like your console application where you will provide startup file.This file is same like those feature which will startup all the things


Now let see actual physical project structure. You will find a new file which is Kproj.

The Kproj is new type file. “K” is new feature introduce. New compiler is used.


I will explain more features in next post.

Till than enjoy.

Thanks & Best Regards,

Rajat Jaiswal

July 13, 2014

VNext – A new version of Visual Studio (VS-2014)

Filed under:,VNext — indiandotnet @ 4:36 pm
Tags: , , ,


Recently, Microsoft launched a new version of Visual Studio which is VS 2014 (VNext). Although I am not always happy with such a quick versions by Microsoft but this time it will a great NEWS and give a strong competition to all the open source competitors.

VNext is a open source. With VNext you will get MVC 6 , .NET framework 4.5.3.

You can download  the VNext from following link

In VNext you will found a lot change. Major highlights

1) Project solution change

2) New configuration file

3) New dependency (reference) utility

4)Roslyn Compiler

5) web.Config removed

6)”K” factor

7) Next Generation “JIT”

8)Deployment changes

There are many more things which we will discuss later on.

Till Enjoy VNext.

Thanks & Best Regards,

Rajat Jaiswal

June 24, 2014

FIFA world cup 2014 with Angular.js & ASP.NET – Part- II

Dear Friends,
In last post part 1 ,we learnt how to use Angular.js.
Now in this post we are moving further as I shared we will do some more interesting thing here.
In this demo I have created same type of JSON as we have created in last post but it has some more values like
MatchDate: ‘Localtime’,
MatchNumber: ‘Match64′,
GroupName: ‘Final’,
Stadium: ‘Maracanã-EstádioJornalistaMárioFilho’,
Venue: ‘RioDeJaneiro’,
TeamName1: ‘W61′,
TeamCode1: ‘W61′,
TeamName2: ‘W62′,
TeamCode2: ‘W62′

Thanks to  for providing the information.

I created the JSON from’s data (I hope they will not mind it.)
Now as mention earlier, it is exactly same as we did in last post it has module, controller and groups properties.
In this we added two more features which is Order By & filter.
Firstly order by expression. So we have used order by expression in ng-repeat.

We have sorted this with matchDate with Order by expression as shown in below image

When you run it then your result will be in order by MatchDate

Now what if you want to sort it by reverse date order then it is also very simple you just need to add “-“minus sign in front of MatchDate once you will do this you will get following output.


Now we are interested in some specific information like particular group, particular match, and particular team so in such case we require Filter extension property.
In current example we have taken a textbox which has property ng-model. Now this ng-model value is passed in ng-repeat with filter option as shown in below image

Now when you type value in textbox you will get filtered result on the screen as


I hope you enjoy it.

Soon I will share code with a other example.


Thanks & Enjoy !!!


June 12, 2014

FIFA world cup 2014 with Angular.js & ASP.NET – Part- I

Filed under: AngularJs,,SPAs(Single Page Applications) — indiandotnet @ 5:40 pm
Tags: , , , ,

Dear Friends,
Today FIFA world cup 2014 is going to start, So I just thought to make something which have colors of FIFA world cup 2014 with my learning.
In this post I am sharing how to create a simple Angular.Js page in ASP.NET which give you FIFA 2014 Group Information.
Step 1:- Add a new project of ASP.NET and add Angular.Js in the project you can download Angular.js from here.
Or you can add angular js from NUGET package also.

Step 2:- Now add as JavaScript file. I have added myApp.js file in project
Now we need to create a JSON for groups as shown below
Step 3:- Add reference of Angular.js and myApp.js in your project,

Step 4:- Now next step is to add an Angular module to add this we need to write following line

var app = angular.module(‘App’, []);

Step 5: Once Module is added now we need to add a controller in this controller we need to declare a variable and assign JSON object which we have declare earlier.


Step 6: OK cool now your myAPP.js work is done.

Now move to design view of page. In this firstly assign the module to page
Currently I have assign this module in body tag.
Note: all the Angular related property started here with ng-
So in body tag I have written following property as shown in fig

Step 7:- Once we have added the module now we assign controller to a div in which we need JSON data of FIFA world cup 2014, So it can be added using ng-controller as shown in below fig

Step 7:- Now if you see JSON data it has multiple group like GROUP A , GROUP B etc so we require ng-repeat attribute of Angular.js. Tthis ng-repeat will reference controller’s variable which we have assigned in myapp.js.
Now for each group data we will create a div which have team name, team code, and team flag in that group. For this we need to write following code.


The best thing here is to access the property or value you just need to write curly barces {} and within this the property which you want to access.

Now when you run it. It will look like something like below screen.


In next post we will show Match schedule with Angular.Js in

Enjoy FIFA :)
Rajat Jaiswal

Next Page »

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 232 other followers