Asp.net & Sql server fundas with 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

resultset

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.

Reports

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

TotalCPU

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

tblUserStructure

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

importtable

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

LeftOuterjoin

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

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

Except

 
SELECT username,
       uPassword
FROM @tblToBeImport
EXCEPT
SELECT[UserName]
      ,[Password]
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 .

Enjoy!!

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

DROP_CLAN_BUFFER

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 !!

Thanks

Rajat Jaiswal

July 20, 2014

Understanding of new VNext web project

Filed under: Asp.net,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

Vnext_WebApplication

 

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

New_Project_File

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

Config-json

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

project_Json

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

Startup

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.

Kproj

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: Asp.net,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

http://www.visualstudio.com/en-us/downloads/visual-studio-14-ctp-vs

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′

JSONformat
Thanks to Fifa.com  for providing the information.

I created the JSON from FIFA.com’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

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

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

matchdate_with_ReveseOrder

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

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

Filter_Fifa_Result

I hope you enjoy it.

Soon I will share code with a other example.

 

Thanks & Enjoy !!!

Rajat

June 12, 2014

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

Filed under: AngularJs,Asp.net,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.
https://angularjs.org/
Or you can add angular js from NUGET package also.

Json
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,

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

Fotballngcontroller

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.

angular_FootBall_Repeat_Group

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.

Group_Vision

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

Enjoy FIFA :)
Regards
Rajat Jaiswal

May 27, 2014

GET all columns of a table–TIP #4

 

To Get all columns information of a table we can use following command

GO

SELECT st.name, sc.name,sc.is_identity AS IdentityColumn,
sc.is_nullable AS NullableColumn,
sc.max_length as colmaxlength,
sty.name as columnType
FROM sys.columns sc
INNER JOIN sys.tables st ON st.object_id  = sc.object_id
INNER JOIN systypes sty on sty.xtype = sc.system_type_id
ORDER By st.name,sc.name
GO

sp_Columns_Detail_Rajat_indiandotnet

Or we can use following statement

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE
FROM [INFORMATION_SCHEMA].[COLUMNS]

sp_column_Details_2

Enjoy Smile !!!

May 25, 2014

sp_help a way to know detail tip #2

To determine table detail like column name & data types for each individual table ,Index, primary key, foreign keys etc,  sp_help is one of the best way.

Syntax of sp_help is very simple as shown below

sp_help tableName

GO

sp_help_table_name_Rajat_Jaiswal_Indiandotnet

Instead of writing  sp_help tablename you can get same information by highlighting table and press ALT+ F1

Note: if you just right sp_help and run it then all the objects of database will be listed down as a result.

sp_helpDB

Enjoy Smile !!!

Next Page »

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 232 other followers