Compress & Decompress–new Feature in SQL SERVER 2016 #2

This is another article in the series of SQL SERVER 2016 Journey . I am pretty much sure you might aware of Gzip Compression algorithm. If not then try  this link.


So, SQL SERVER 2016 introduce this two awesome functions for Compress & Decompress the data.

Before SQL SERVER 2016 version we have data compression feature like Page & Row compression (check Previous post for it Link )which is different then this column value compression.


In SQL SERVER 2016 Compress function,  data compression is done via GZIP algorithm and return VARBINARY(MAX).


Below is the simple syntax of Compress function


Compress (Expression)

Here Expression can be nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n)


Decompress function is just opposite of  compress function. It is used to decompress the value of VARBINARY which is converted using Compress function. The only tweak is you need to cast the output of Decompress function  in specific data type to make it readable (if using varchar ,nvarchar compression) .


below is the simple syntax of Decompress
Decompress (Compressed string)


Let’s understand this via an example as shown below .



In this example I have taken 3 tables with exact same schema & data


  1. 1) IndiandotnetFriends
  2. 2) IndiandotnetFriends_Compress
  3. 3) IndiandotneFriends_Decompress


You can see  snap in which we are inserting same data.

As the name suggested in first table normal data from Adventureworks’s person table.

In second table we are inserting compressed value of first Name  and in 3rd table we are inserting decompress value of First Name from the Compressed table.

Now, let’s check compress  & decompress table data



Now, Your might thinking that the output of both compress and decompress is not readable.

So you are right to make data readable of Decompress table we need to type cast.

See below snap for same.




Till now we know how to use this Compress & Decompress function. Now, let me share the benefit of using Compress. if you see below snap you will find that data length of compress is comparatively less than normal and decompressed data length .




Obviously, compression helps you somewhere in the overall performance of your application.

The good point is  you can pass the compress data to your .net application and decompress using GzipStream as well.


The only thing which we need to take care is type casting. Suppose your base column which compressed is VARCHAR then you need to typecast again in VARCHAR.


Now, next question is where we can use this functions. So,  we can use in compressing large object like binary data in which we save jpg, pdf , word document etc..


I hope you will be excited in using this function.


Please, share your input.


DROP IF Exists- A cool feature in SQL SERVER 2016

In the Series of SQL SERVER 2016 journey, this is our new article. In this article, we are sharing a new cool feature which introduced in SQL SERVER 2016 which is DROP IF EXISTS (DIE) .

In our development many times it happens that we need to drop a table and as a best practice we write the following syntax as shown in below figure


Now, in SQL SERVER 2016 the same task is super easy. You can write the following syntax to drop the table object



The best part is if suppose the object does not exist then  here will be no error execution will continue.

Let me share one more example of Dropping a stored procedure.


Similar, way we can write for following data objects and with the following syntax












View :-





I like this feature I am sure you will also like this.

Please, do share your feedback for blog post.

Enjoy !!

Unbelievable SQL SERVER 2016 Feature you should aware.


Although, I know I am bit late to share this thing on our blog but it says in Indian proverb “Der aai durust aai” means it’s OK you came late but you came that is more important.

Anyways, so you all might aware that Microsoft launched SQL SERVER 2016 officially in June 2016.

You can download the SQL Server 2016 via Link.

Obviously, this is a new revolution in SQL SERVER series. You will find many great features in this version.

This post is beginning to explore all those great features and we will do deep dive in all those features. In this, post we briefly introducing those features. so, without wasting time let me share a brief introduction.



Is this surprising to you ? Obviously, yes. As you might aware that most of the NO SQL database use either JSON or XML. As XML feature already exists in SQL SERVER so this was time for JSON. You can play with JSON in SQL SERVER 2016.


2)Always Encrypted :-

If you talk about security this one the best feature. Now, you are thinking what it means. So, It means that the data in the SQL SERVER reside always in encrypted format and SQL server can perform the operation on the encrypted data without decrypting it. The encryption key can be exist in some other system. With this, feature you can secure you ensure that your data is secure from the person like DBA / Developer as well. These guys also can’t see the actual data. Isn’t it neat ?


3) Row Level Security :-

This is another interesting feature which helpful especially to the developers  who needs to write extra code to check this. Let me explain this with an example suppose you have a sales team who do market research and you want to restrict that each sales manager can see only those data which entered by him only in such cases you don’t need to write specific condition in your code. It can be achieved by Row Level Security.


4) “R” in SQL SERVER :-

For the data scientist, it is a great NEWS. As Resolution Analytics is purchased by Microsoft and It is incorporated in SQL SERVER. You can run R analysis query in SQL Server.


5) Temporal Table:-

The Temporal table holds the old version of ROWS of a table. It means that it maintain a copy of the old rows in the table whenever there is an update on the main table.


6) PolyBase :-

With the help of this feature, you can access data which exist in Azure Blob or Hadoop cluster using the same SQL server. In the nutshell, we can say this is the technology which combines both relational & non-relational database in a single umbrella which is SQL SERVER. You can run the query directly on external data like Hadoop or Azure blob storage.


7) Stretch Database:-

I am pretty much sure by the name you can guess this feature. So, with the Stretch database you have can store your part of the data in the cloud which depends upon your need. You can say most recent transactional data you can store in your local environment and other old data you can store in Azure.


8)Query store :-

Another interesting feature to help you in identifying  performance drag using Query store. When you enable this feature it automatically captures a history of queries , plans,  and statics and retain them for review and resolve the performance issues.


9) Mobile report:-

As mentioned earlier this is the revolution in SQL SERVER 2016. In SSRS there are many important changes introduced. Now we can import Power BI report in SSRS and apart from this you can create a mobile report which you can run on Mobile.


Now, we started officially SQL SERVER 2016 tutorial series.


Moving forward we are going to discuss all these features in details and also the couple of new things which introduced in SQL SERVER 2016.



Happy VijayDashmi.

The Dummies’ Guide to How to solve Problems with Extension Method

I am pretty much sure that, You have used Extension method in your program. Did you think to create your custom Extension method ?

If not then this article help you to create your own Extension method step by step.

Before jumping to direct practice let’s understand first what is extension method. So, an Extension method is created to simplify the code. It is a static method which represent as instance method. It accepts “this” keyword as a parameter.

If you see below example image you are not going to surprise as you have used this type of method.


Now, let me tell you more about the Extension method. As you now aware it is a static method & it accept “this” as a parameter so just need to add a “.” dot symbol with variable and all the existing method will be appear.

The question come to our mind how to create our own Extension method. So, it is simple and easy. We need to create a static class first and then that static class we can create our own Extension method.


Once you added a class in your solution specify the class as a static class and a static method in the class. In below example I am using static method with name  “MyStringCharacterLength”  if you see the below snap you will find in the parameter we are using this which is must for an extension method. We are not doing any fancy with this method it is a simple logic of calculating the length of the string.


Now once the extension method is created we can use this method using by a “.” dot symbol. Isn’t it easy let me show you the code.


When we run this code it will return the length of the string which provided as a input.

I hope a basic now you have a basic understanding of extension method. You can create your various method to use this and reduce your efforts in cases where you have to write similar logic again and again.

I recommend below site from where you can download existing Extension method and  use it.

I hope you have a clear view of extension method now.


JavaScript can be easy with CoffeeScript a nice Idea

Recently, I got chance to view Coffee Script. If you are not aware what is Coffee script then don’t worry this post will help you to understand it.


I am pretty much sure once you work with this you will fall in love with this language.


So, without wasting time let me tell you what says

“CoffeeScript is a little language that compiles into JavaScript”


It means that it is a simple little language which convert the your simple coffee script statement in Java script. This conversion is seamless.  As I mentioned above Coffee Script is simple, easy to Understand , readable , maintainable and reliable.


Now, you are thinking it might be slow  so this is not the case. It is fast.


The only thing which we need to take care when we will working on this is White Space as it is very sensitive for white space. So , we need to do extra precautions when working on it.


I think we discuss lot of theory not it is time to do some practical.


Although, we can use Visual studio as well. In visual studio we have to add .Coffee extension’s file.

for this post demo I am using  Live coffee script IDE which is   


so, let’s open the Live IDE 



Or we can use  “Try coffeescript” tab as well




Now let’s understand it by few examples

So We don’t need to use semi colon “;” symbol for statement terminator CoffeeScript is using white-Space delimiter and also we don’t need to use curly braces “{}”  we can use indent or new line instead of “{}”.

Let’s understand this by an example suppose you want to write a JavaScript function with following features

1. Accept 2 parameter a & b

2. Add a &  b value and assign in a new variable which is C

3. Now add a condition If  C’s value is greater than 100 then show alert message “ more than 100”

4. if  C’s value is less than 100 then show alert message  “Less than 100”

5. If C’s value is equal to 100 then show alert message “ Equal to 100”


Now suppose if the above function we need to create in JavaScript then we need to write code as shown in below figure



if same thing you want to achieve in CoffeeScript then you need to write code as shown below. If you see there is no curly braces & semi colon.



I am pretty much sure after seeing above example you might be excited to know more about it. 

To make you excited let write a simple CoffeeScript below and then we will compare it with JavaScript code which we usually write.

Here our objective is to create a Vehicle class with constructor and with Mileage



Now to achieve same thing in JavaScript we need to write code as shown in below figure.



I am pretty much sure you are now super excited to know more about CoffeeScript.

I hope this tutorial might be helpful and interesting.



The Step by Step Azure Machine Learning is good Idea Part–3

In the previous post , we discussed about different algorithm types which help us in Machine Learning. Now, In this post, we will see how to work with Azure Machine learning studio.

So, let’s start with Step by Step how to use Microsoft Azure Machine learning.

Step1 :- You have to login into Microsoft Azure portal with your credential if you have not created yet then below is the link by which you can create your Azure account.

Step 2:- Click on Portal option of Machine learning as shown below. Here you have to click on “Create AN ML WORKSPACE” when you click on this option you will find the below the screen. You need to add workplace name and select the server location.

Once you done with above steps, you will find the below screen


Step 4:- You can open Azure ML studio by clicking Open Studio icon or you can click directly login into 

Once you successfully logged in you will get the following screen.


In this screen, you will see different options like existing experiments created by you, samples by Microsoft etc.

Step 5:- You can check existing samples as well which are on different data analysis algorithms.


Step 6:-  But, let’s not go with existing sample as we will try to create something from scratch.  For that when you click on new Experiment then you get the following screen.

There is 3 main part of the studio screen. In Left panel, you will find different data sources , filters, transformation,models,Options. You just need to drag-drop on the central panel which will be our working area where as the right side panel will show the properties.


for our test, we can use  already uploaded dataset or upload new dataset on which we want to create the model. In this example, we are using Adult Census income which is already available in dataset repository of Azure Studio.

Although, we can download same data from as well  and upload to Azure Studio back. If we open the link you will find different attributes like  age, sex, marital status, race country  etc.

Before going further, let’s understand  what we are going to do?

So, we are going to  predict the income and determine/predict  whether the income is more than $50,000  or not. The training model  is based on the known inputs like age,education, job type, marital status, race and  number of hours worked per week.

Just drag drop the data source (dataset) on the central panel.


You can visualize the data by right click on the central circle of each control. You can check data anytime in any step.


Step 7:- Now, this dataset  might content some missing data values and for better result or prediction we will clean these data. To achieve this we need to drag-drop the Missing data control and provide dataset inputs. Here from the property panel you can set any value and replace it with the missing data value.

In this example, we will set the value to 0 (as shown in the figure). Now the output of this missing value is a proper dataset on which we can do the analysis.


step 8: Once we have proper dataset then our first goal is to split the data using split control . Here splitting data divide our dataset into 2 part


1) Training data :- With the help of this data we will train the Machine

2) Validation data :- This data is used to check the accuracy of prediction which we will apply.

We can set the ratio of these two parts by the properties.

Step 9:- Here  we need to apply the algorithm. Assuming the prediction result set we are going to use Two-class classification model. If you see in the left panel there are many classifications. For this example Two-class Boosted decision tree.


Step 10: Now, we have data with us on this data, we have to apply the algorithm and teach the model how to evaluate. for this, we will drag drop the “Train Model” control from left panel to our designer panel .


Here we need to configure “Train Model” . For this just provide the output of Split data (Training data output) to the Train model and also output of two class Boosted decision control’s output to train model. Once you set both the control then your screen will look like as below


Step 10:- Now once we have provided the input data to Train Model next thing is to  provide the name of the column which we have to predict. for this click on Train Model. Check the right panel and select the column which you need to predict  by clicking Launch column selector.


Step 11:- So far we are good, we have created the trained model now it’s time to score the model and cross-check the accuracy of the algorithm which we have applied. To evaluate the model we will drag drop a Score Model from the left panel and connect with Train model and the other validation data output of split data. Once you are done with this just press save button and run the model. if everything works well then there would be a right checkbox appear on control.


Step 12:- Once everything with green checkbox check as shown in the figure .


We will check data visualization by right clicking on score model.


You will find 2 additional columns which are Scored Label & Score probabilities. These two columns provide scored value and probability percentage


Although, we can have different methods as well to achieve the same task and we can evaluate the result of these different methods by one more control which is Evaluate Module.  We have to drag-drop this module from left panel to designer and provide the Score model’s  output to the evaluate module.

Although, we have  only one training model with two class method but we can create another train model as well as compare them by providing output of the model to Evaluate module.


It provides a set of curves and metrics which we need to understand and choose the best train model if we are evaluating two train model.


Till now we have created a model. here are not going to save the model as a trained model and publish it we try to expose this model using a web service.


Step 13- Add output of web service dragging and dropping the web service output control and configure as shown below figure.


Step  14:- In the next step, we will publish the web service with the below button of the designer when you click on publish web service you will get the following screen after completion of the step.


Here just click on Test operation and you are good to go with Azure Machine learning experience.

I hope you will try yourself. Please , keep sharing your inputs.



The Step by Step Azure Machine Learning is good Idea Part–2

In the last post, we have discussed Machine learning. Now in this post, we will discuss some more detail about algorithms and trust me this is one of the most important objects which a Machine learning engineer should know.

These objects are nothing but the algorithms. As a data scientist or Machine learning engineer, the first and most important thing are we should know

what is the data ?

What the result is ?

what analysis do you need to apply to get the desired result or prediction?

I know this is pretty much clear but let me explain with an example. Suppose, we have students data with high school ‘s internal assignments and we need to predict if the student can be pass in the final exam or not.

Now let me give you a brief overview of some of the algorithm  types which we may require in Azure Machine Learning. Although, there are much more types ,subtypes available but will not go in deep. So, let’s start

1) Two- Class  Algorithm Type:-

We will apply this algorithm type when the prediction result in either Yes/No or true/false or 1/0. for example, a student can be pass or not.

2) Classification Algorithm Type:-

This is another algorithm type which help us to predict  answer like which Kabaddi team or cricket you will cheer or which political team you will vote.


3) Linear Regression  Algorithm Type:-

This is one of the common prediction methods which everyone applies Smile sometimes. for example, in office, you can predict an engineer’s salary range depending upon last few engineer’s salary, prediction of property selling amount range Like this plot might be from 20 lac- 25 lac depending on last few years property price.



4) Anomaly detection  Algorithm Type:-

By the name, it is clear we need to find anomalies. for example, you have to determine from a group of  white cows and black cow you need to find out odd color cow means black color cow.




I hope the all the above algorithm types is clear. In next post, we actually do the step by step Microsoft Azure Learning so don’t worry about that.

Please, provide your inputs

The Step by Step Azure Machine Learning is good Idea Part–1

Machine learning is not new in the market but nowadays  it is a buzz word everywhere. You might realize that there are lots of things happening in the Machine Learning.

Many big companies like Microsoft, Oracle, IBM,SAP and many other working in this area. They have provided Azure Machine Learning,Oracle Advanced Analytics, IBM SPS, SAP Predictive Analysis tools to work on it.

Before jumping into Azure Machine Learning directly let’s first understand basic of Machine Learning what exactly it is.

So, Machine learning is a way to understand the data pattern , recognize it and predict accordingly for future.  It helps in

1) Data Mining

2) Language Processing

3) Image recognition

and many other Artificial Intelligence  related stuff.

I know above statement is bit bookish so let me explain in Indiandotnet style .

Let’s say you are a teacher in  a school and you have quite experience in teaching. In each year you teach many students you also keep previous years data and some sort of basic detail of students.

When parent’s come to meet and wants to know the progress and whether he/she will pass in graduation or not. You simply do data analysis in your mind  whether that student doing study  or not (obvious), what his/her percentage in the last couple of exams or internal assessments , how he/she performed in previous class etc., then you give your prediction to the parents that their child does good or bad in the final exam or not.

Now, suppose instead of you there is a computer and parents asking the same question to the computer Now, a computer should provide the same answer as you give accurately or might be better.

For this, we need to feed enough data sample in the computer. If he has previous data samples by which he can analysis and predict accurately.

This overall exercise of processing data is part of Machine learning.

So, firstly you have to train the computer with providing the initial data which we can say training data. This is an iterative process.


Although, Machine learning Is more than this. Here, we are showing some more example where machine learning can help

1) Detecting  fraud credit card

2) Determine SPAM emails

3) Provide customer like to switch to competitor

4) Free text when typing etc. many more examples of machine learning.

There is 2 distinction in machine learning


1) Supervised Machine Learning :-

The Supervised learning means the value you want to predict is already exist in training data. Means the data already exist in the computer so data is labeled. The accuracy is high in such case.


2) Unsupervised machine Learning :-

So It is just opposite to Supervised Machine Learning. In this the predictive data not present in training data.

I hope now we have a basic understanding of Machine Learning. In next post, I will share step by step example of Azure machine learning.


Please, provide your inputs.

How Easy Automation testing of web application with Visual Studio – Part II

In the previous post, we saw how can we test a windows application using Coded UI. In this post, I am trying to describe how we can do automation testing of a web application .

So, let’s start step by step for

Automation testing of a web application.


Although, I am showing Automation testing in this post  but the project template  is more than that. I will describe in future posts for load testing & web performance related concept as well using this great tool but In this post, we are just concentrating automation testing of  a website.


Step1:- Open visual studio and add a new Project. This time, we are using “Web Performance and load test project”  as shown in below screen. Although, this template is self-descriptive.




Step 2: – When you click on this you will get the following screen.



Step 3:- Here, if you saw above image you will notice we have to click on recorder button. When you click this button, you will find internet explorer screen with record, pause & stop options as shown in below figure.




Step 4:- Here we have to provide the URL which we want to test. In this example, we are using and for a demo purpose, we are clicking on different menus and searching with a specific value. All our activities which we will perform will be recorded by the recorder and we can easily see those steps as well. As you can see in below figure.




Once we done with our basic test cases we have to press STOP button as shown in the above image. when we click STOP button our Internet explorer browser will be close and we will get all the recorded action in our Visual Studio IDE as shown in below image.



Step 5:- Now, in the next step we defiantly want to cross check all the action which we performed and recorded by the recorder in Internet Explorer. for this, we  just need to click on Run Test button  as shown in below figure.



Step 6:- When we Click run test option the IDE run all the recorded steps in the same sequence and if everything as per the expected result it will display “Passed” status for that particular recorded test case as shown in below figure.



I hope you like this simple way of recording your test steps and cross check it again and again by just pressing Run test button.


Although, Just wanted to share as mentioned in the introduction of this post as well. This project can do much more this but for now , I am showing a small part of it.


I hope you will use it soon. I appreciate your inputs for this post.


Enjoy !!

RJ !!!

How to make UI Testing Easy with CODED UI ( A Glimpse)

In the last post, we discussed how to be a smart developer in testing. To continue that smartness Smile I am sharing one of the best tools which are CODED UI. Smile


A CODED UI is again a record and play concept what I mean here that you can record steps of your test case and play again and again whenever you want.


I know I made a simple statement for CODED UI but it is much more than that and you have full control on it. You can write your own custom code in CODED UI.


Although, this post is just starting or a basic information of CODED UI but it has many more things which we will cover later in our posts.


To understand, It better let’s start a step by step process. In this step by step process, I am using Visual Studio 2015. 


The best part of CODED UI is that you can record test cases of windows & the web both the application.


Here we are taking the simple example of UI recording which is open a Calculator and perform the operation and another one is the open website in the browser and record the steps (in Next post).


Step 1:- Open Visual Studio 2015. Create New Project and select CODED UI project as shown in below image. Here if you there are other options as well like Unit Test Project, Web Performance, and Load Testing Project.

So, those have their own unique properties which we will discuss soon.



Step 2:- When you click the OK button in above window you will get project is opened in Visual studio with certain predefine features and a new pop option appeared as shown in below figure.

It has 2 options which are self-explanatory. We are going with default option which is “Record action, edit UI map or add assertions.



Step 3:-  When you click this option then on your machine near task bar you will find UIMAP Windows. Which is responsible for recording your action.



Step 4:- You can add Coded UI Test file by right clicking the project or add new Item option. This file contains all the recording steps. If the UImap is already open then you have to close it first and then add Coded UI test file.



Step 5: Once you added the file and UImap will appear then next step is start recording your steps of test cases. Here we are going to open the calculator and adding 2 numbers. so firstly click on the Record button of UIMap so ,it will start recording the screen. If you see below screen you will find the recording button change to the pause button.



Step 6:- Now we clicked on calculator button. It appears in front of us. We added first value 10 and then second value 20 and clicked on an equal button. then press pause button (as our first test case is completed). As the best practices we have to go step by step and record it but for this example, I am not following that. If you click the button nearby pause button as shown in below figure you will get all the recorded actions so far




Step 7:- Once you confirm with your Recording then you can click on Generate Code button and if you have any doubt then you can re-record or delete that particular recording. If you observe above steps I deleted Click  ‘1’ button explicitly. I have recreated recording then clicked on Generated Code button. When I clicked I got the following option. Here you need to Provide method name and then save it.



Step 8:- Now to check whether the added value result is correct or not whether we have to fail this test case or pass we have to click on the guy as shown in below figure



Now, this is bit tricky part you have drag cross arrow from this control to Calculator’s result window and highlight as show in the below figure.



Once this done then add Add Assertion by clicking the Add Assertion option as shown above image. When you click you will get the following screen



Once the above step is created Just click on Generate code once again. So that it will add Assertion logic as well.

So , far we have created a Coded UI test cases. Now , the challenge is how to cross check it. So here are  steps.

Click on Test Option from Visual Studio menu  click on Run option then click on  All Test case options.
You will find all the steps that you perform (recorded) earlier will re-run and after execution, you can cross check. The test explorer.

If all the steps of test cases pass then according to assertion logic our test case will pass. as shown in below figure.



In this way, We have captured a windows application test cases and record and played accordingly. In the next step, I will show you how easy to cross check web application as well.

Please, provide your input what you think of this article.


Happy weekend!