Query Performance – Function doesn’t use index seek

Indexes play an important role in SQL query performance, but improper knowledge of how indexes work, can lead to degraded performance. In this article I will throw some light on one such practice. We all know indexes help in select statements and make other statements (insert, update, delete) slower. At the same time if we use a function on indexed column in where statement, it does not use the index and hence query performs as it does not have a index. Lets look into real time example

Step 1. Create a table Person :

CREATE TABLE [dbo].[Person](
[ID] [char](800) NULL,
[FirstName] [char](2000) NULL,
[LastName] [char](3000) NULL,
[City] [char](500) NULL

Step 2. Insert some random data :

INSERT INTO Person (ID,FirstName,LastName,City)
ELSE ‘Mahesh’ END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN ‘New Delhi’
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN ‘Chennai’
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN ‘Hyderabad’
ELSE ‘Bangalore’ END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

Step 3.  Execute a select statement with where clause on City :

WHERE  City = ‘Chennai’

Note : Till now we do not have any index on City column.

Following is the execution plan

Execution Plan Without Index

Look at the query execution plan, SQL Server did a table scan which is expected because there is no index on the City column.

Step 4. Create a non clustered index on City column :

ON Person (City);

Step 5. Execute following select statement :

WHERE  City = ‘Chennai’

Notice the execution plan (below) now, SQL Server is using index seek.

Execution Plan With Index

Step 6. Now lets execute a select query which uses function on column name in where clause :

WHERE CONVERT(Varchar(500), City) = ‘CHENNAI’

Below is the Query Execution Plan. SQL Server is using Scan which contributes to 29% of the total cost of query whereas in step5, it used Seek which contributed to only 5% of the total cost.

Execution Plan With Index With Function

Conclusion :

So in summary , the above steps show that SQL server was able to perform a seek operation on City column but when a function is used on the column it was not able to perform seek and hence it performed scan operation.


Effective Scrum Master’s Dos and Don’ts

Scrum is a framework with just three roles (Product owner, Scrum master and Team), three artifacts (Product backlog, Sprint backlog and Burn-down chart) and a few ceremonies (Sprint planning, sprint review, sprint retrospective and daily stand up). Its easier to learn but way more difficult to practice. Adopting scrum is not just a change of titles but mindset. In this article I will focus on the role of a scrum master. Being only a good scrum master is not enough, effective is the goal. Below are the Dos and Don’ts for a scrum master to be effetive

Scrum Master Dos :

  1. Facilitate the product owner to maintain and prioritize the backlog items.
  2. Facilitate team members with any additional training or coaching required.
  3. Makes sure that team delivers the  true value to the business.
  4. Inspires the team members to own the tasks.
  5. Allow and help team members to make decisions.
  6. Removes any impediments holding the team back to achieve their goals.
  7. Helps team to become self-organised.
  8. Shields team from external interference.
  9. Acts as a servant leader.
  10. Should be willing to improve continuously.
  11. Handles only one team. If you  are a scrum master of more than one team, the commitment cannot be the same.

Scrum Master Don’ts :

  1. Own the decisions on product backlogs from product owner’s behalf.
  2. Make estimates on team’s behalf.
  3. Assign the tasks to the team members
  4. Try to manage the team.
  5. Make changes to team in middle of sprint.
  6. Accept backlog changes in middle of sprint.
  7. Make commitments on behalf of the team.

Scrum master does not control the team. He acts as a facilitator and works on the concept of servant leadership.

I hope this will help someone. Please add your thoughts in comments.

Impact of sp_ prefix on stored procedure performance

Naming conventions are integral part of a quality code but sometimes we need to pay additional care while deciding on the naming patterns. There are two type of programmers, one who prefix everything and the other who don’t. In world of SQL server, the former group is further divided in two parts, one who use sp_ prefix for naming their stored procedures and others who use something else (usp_ or short app name). The recommendation has been to avoid sp_. In this article I will explain the reasons for this recommendation with supported data from my experiments.

sp_ does not stand for Store Procedure

The myth is sp_ stands for stored procedure, infact it means System Stored Procedure. SQL server has some system stored procedures defined in master database. These procedures starts with sp_ prefix and are accessible from any other database. One such example is sp_helptext. If you want to have the text of a stored procedure execute sp_helptext‘<SP Name>’.

Performance impact

To understand the impact on performance lets create a database say DummyDb. Create a table DummyTable and insert 100 rows

–Create database



–Create Table

CREATE TABLE [dbo].[DummyTable](

       [Id] [intNULL,


[varchar](100) NULL



–Insert 100 rows

DECLARE @Count int;

SET @Count = 100;



       INSERT INTO DummyTable VALUES (@Count, ‘Name_’+ CONVERT(varchar(100),@Count));

       SET @Count = @Count1;



Now lets create two stored procedures GetEmployee
and sp_GetEmployee

–Create GetEmployee




       SELECT ID, Name FROM DummyTable



–Create sp_GetEmployee




       SELECT ID, Name FROM DummyTable




Lets execute both the stored procedures

1. Execute GetEmployee

DECLARE @Count int;

SET @Count = 500;



       exec GetEmployee

       SET @Count =



 I executed the above statement three times and it took 38, 41 and 38 seconds which means it comes to be 117/1500 = 0.078 seconds per execution (Although it will not be same every time, but 1500 is a good sample for comparison).


DECLARE @Count int;

SET @Count = 500;



       exec sp_GetEmployee

       SET @Count =



 I executed the above statement three times and it took 40, 41 and 41 seconds which means it comes to be 122/1500  = 0.081 seconds per execution (Although it will not be same every time, but 1500 is a good sample for comparison).

The above data clearly prooves the sp_ prefix degrades the performance of stored procedure.

Why is it slow?

Whenever SQL server gets an command to execute a stored procedure with sp_ as a prefix, it first scans all the system stored procedures. If it doesn’t find a matching procedure there, then it scans the user defined stored procedures. This additional time causes the performance hit.

Understanding Agile Manifesto

In February 2001, seventeen software developers met at ski resort in Utah with the objective to find better ways to develop softwares. In their discussion they found consensus on four major values, now known as “Agile Manifesto”. All of us who practice agile, might have these values on our tips but its always good to periodically look at these values and check what they mean to us today.Agile Process

The four values of Agile Manifesto :

1. Individuals and interactions over processes and tools
2. Working software over comprehensive documentation
3. Customer collaboration over contract negotiation
4. Responding to change over following a plan

That is, while there is value in the items on the right, we value the items on the left more.

One good way to look into manifesto is, it defines preferences, not alternatives, encourages focus on particular area but does not eliminates others. It does not eliminate value in items on the right, it just encourages focus on the ones at left which can lead to a better software quality with high success rate. Lets discuss the each value in a bit of detail.

Individuals and interactions over process and tools : 

Softwares are built by team of people and to do that effectively they need to work together. As with the time processes and sophisticated tools were developed, focus shifted from individuals to tools and processes. Vendors are selling tools and processes as a solution. But all along it is still people who develops solutions. There is an old saying “A fool with a tool is still a fool”. So to me this point means  that the tools and processes should be minimum needed for a given situation. The focus should be more on individuals and interaction between them. For example QA team should not be isolated from dev team and similarly any other team committed to the delivery of product.

Working software over comprehensive documentation:

In a traditional waterfall model, customer is asked to explain the requirements and document it. Then the implementation team develop and test the software as per the documented requirements. Virtually its impossible for customer (infact anyone) to document the software they need. Even if it is, the requirements may change with the time and at the time of delivery customer might have different requirements as per the market at that time. The agile approach of building increments of ship-able product is very effective. At the end of every sprint customer can see the product and hence can come up with more idea or improvements he need for next sprint. This also builds the confidence of customer. But still there is some minimum documentation required. The question is, how much ? Agile says minimum documentation should be required for any software.

 Customer collaboration over contract negotiation:

Sometimes this statement is misunderstood as “we don’t need contracts and all collaboration is informal” which is not true. To me it means, our contracts should be flexible to accommodate the changes. Even with fixed price projects we can have flexible contracts. The changes can be discussed in sprint planning meeting and approvals can be taken for those.

Responding to change over following a plan:

Plans are made so complex and detailed that they are difficult to modify when change occurs. In agile process changes can be accommodated easily because it has simple artifacts like burndown charts. Progress can be tracked in agile process as well and its more transparent than waterfall model.


The whole Agile Manifesto is centered on delivering better software. Although waterfall model’s intentions were also the same, but it just didn’t happen. We focused on tools and processes so much that the real values were getting lost somewhere. Agile tries to fill that gap.

There are also examples where people have forgotten the values on right completely. This is also improper choice.

Remember, delivering a high quality software is still very hard.

AIB Roast – A Proud Moment Or A National Shame?

AIB roast is in controversy now. Some people liked it and some didn’t. Some of them even raised a voice against it. Complaint lodged and the video taken out of the youtube but still available on torrent. On twitter #AIBRoast and #AIBNationalShame both were top trends. I too expressed my views on it and some got offended to it. I questioned the morality and responsibility of AIB hosts. In response, I received a no of replies asking , do I never abuse in my life and never use these so called fancy and cool words?

Answer to your question is a big YES. Yes I abuse, I use these words and so you do. But you can not compare me with the people present at AIB roast. People like Karan Johar, Deepika Padukon, Sonakshi Sinha, Ranveer, Arjun kapoor, Raghu, Tanmay, Alia Bhatt and the list goes on. These are the people whom the youth of the country follow. You can check their followers on twitter, facebook pages and other social networks. And can do the same on my accounts and see the difference. So when you have millions of followers, there comes a social and moral responsibility on you. A responsibility to take the youth (your followers) into a right direction. When I abuse and use these words in my friend circle, it doesn’t impact anyone. The only impacted people are my friends with whom I use these words. Moreover I (or You) dont use this filthy language in front of our parents. Neither we use it in front of kids of our family, be it our cousins, brother/sister, nephew/niece or our own children even if they are above 18. We don’t do that because we know they follow us and what we say or do impacts them directly. We don’t talk vulgar in public places. When you ask me this question in reply to my tweet against AIB Roast’s filthy language, its like someone asking you how many scores did you made in reply to your tweet on criticizing Virat Kohli for not performing good in a crucial series. We criticize only those who have a bigger circle of influence. I hope it makes my point clear.


Another point, there were prominent women present at the show. Deepika padukon, Alia Bhatt, Sonakshi Sinha. These are the ladies who talk a lot about woman empowerment and women’s respect. What they conveyed through their presence in such a show where jokes were cracked about women body parts, made fun of Parineeti Chopra and Aisha Takia in a way vulgar manner. Had it been done by a political person or someone else, Parineeti would have busted out and lodged an FIR. But here she chose to keep quite and the reason is well known to everyone.

Most of the people are supporting it as a freedom to speech and at the same time they are getting offended to people who talk against it. Dont you take it too as a freedom of speech. If you believe that someone should be allowed to use filthy language on the name of freedom of speech, don’t you think criticizing it is also a freedom of speech.

Flaws of common review / appraisal process

Bell Curve

Next Recommended Read :- Feminism Over Humanity

As the title is very straight forward, without going deeper into the context I will directly list down the flaws I have noticed in a common appraisal system followed by most of the companies.

  • Bell curve fitting : Bell curve methodology assumes that all the employees in a company can be divided into three parts a) 15% top performers b) 70% average performers and c) 15% non performers. This methodology is followed from a small team to organisation level. Every team gets divided into these three categories. It compels the rater to a forced ratings instead of a fair one. What if every team member performed well? Rater has to put someone in category C, even if that employee has performed better than the top performer of some other team.
  • Lack of 360 degree feedback :  Its all about manager giving feedback for subordinates but not the reverse. Suppose an employee A reports to B and 10 employees report to A. Feedback of A is collected from B but not from 10 employees who report to him. A’s appraisal shouldn’t completely depend on what B thinks of him, but also on what 10 employees reporting to him thinks of his abilities and leadership skills.
  • Setting over hard working employees as benchmarks : – There are some very hard working employees in every team or company who works more than normal hours at cost of their personal time. Either they love to work for those extra hours or they do it to stand out of the crowd. Managers generally support these people and set the similar benchmarks for others as well. Hence a person working in normal hours gets lesser rating then over hard working employee. To improve his rating he also starts working more than normal hours and hence spoiling the work environment for others who wants to enjoy their personal time. Working more than the contract hours should not be encouraged by managers. And if someone is working with his own choice, others shouldn’t be impacted because of that. Appraisal of everyone shouldn’t be impacted just because someone is working beyond the limits because of his personal interest.
  • Employee in need vs employee who did well : This statement is true for almost every organisation. On papers, review is to be done for the past (a year for annual, 3 months for quarterly review process) performance of employee, but actually it happens the other way. If a person is having future dependency, he is made happy by giving him good review, whereas, if a person has performed extraordinary in last year but doesn’t have much dependencies for coming time period, his efforts are generally ignored.
  • Seeing leaves as a crime : If someone takes leaves for a week or two, its considered as a crime and it affects the final review a lot.
  • No second review : There are no second reviews on final rating. Even if management is convinced that a candidate deserves a better rating, its not changed because most of the time its not there in the policy . This looks unfair.
  • Working on multiple projects : If a person has worked under two different managers in a financial year, he generally doesn’t get a good rating irrespective of his work done. The manager he worked with in past, thinks he has no dependencies on the person, so he gives an average rating (The reason for this is the bell curve again, he has to keep his current team members happier than the former). The manager, he is working with currently also takes care of the people working with him from a long time hence sacrificing rating of this new person. Here both the managers need to understand that rating should be given on the basis of how he performed in the given time period for which he worked.

Click to read it on LinkedIn

Feminism Over Humanity

I will start this blog post by putting up a question , does feminism hurt humanity in any possible form or way? Before we look for its answer, let me share a couple of incidents from my own experience.

Next Recommended Read :- Can education alone save our environment?

Recent one I experienced last night while traveling back from my office to home in Delhi metro. I boarded into the coach just next to the reserved one (for females). I had a look into the female coach and found 15-20 unoccupied seats. Then I noticed the coach I was standing in and found 10-15 females sitting there and almost equal no of male passengers standing. I just went near to a girl and politely asked her if she can move to reserved coach , so that i can have a seat too. But she refused to move. I didn’t argue further and kept standing even though a lot of thoughts (or arguments) were going on in mind. At next stop a passenger deboarded and I got space to sit. Incidentally there was a girl sitting next to me. She was listening to music  through earphones. Firstly I hesitated a little, but somehow I managed to disturb her and politely requested her if she can move to the ladies coach to vacate her seat for someone else. First she looked a little confused but then she realized what i meant and agreed to move. I thanked her, a male passenger took the seat and he thanked me for convincing the girl.

The first girl who refused to move had a mindset of like what if front coach is reserved, I am a girl and I have right to occupy any seat in metro. Of course she is right legitimately, but what she didn’t realize was the humanity factor. She was definitely missing the helping mindset.

Another incident again I witnessed in Delhi metro. This time I was not a part of it , but I watched it very closely. An old man (easily above 60) was sitting on a ladies seat. At next station a healthy and energetic lady (around 25-30) entered and asked him to vacate the seat. I had a thought to offer my seat to him but before that another guy did the same. Who was the more needy in this case? of-course the old man needed it more than the lady. But as it was read “Reserved for ladies”, he had to vacate it. Legitimately she is perfectly right, but isn’t it inhuman? At-least for me it is inhuman. Someone might have different point of view here, but this is what I think. After reading next couple of paras you will find the reason for this point of view.

Now I will come to the main agenda of this blog post. Some females are over feminist. They understand feminism as a way to feel and prove themselves superior to men. Going with the above example , such a female will capture a reserved seat at any cost, but at the same time she is not ready to vacate her seat for the one who is more needy. In my views this is not feminism, this is what I call a warped feminism.

Feminism Versus Warped Feminism

Dictionary defines feminism as “the advocacy of women’s rights on the ground of the equality of the sexes”.  First para of wikipedia () reads “Feminism is a collection of movements and ideologies aimed at defining, establishing, and defending equal political, economic, cultural, and social rights for women. This includes seeking to establish equal opportunities for women in education and employment. A feminist advocates or supports the rights and equality of women.”

On the other hand, warped feminism is about demanding extra favor, demanding additional reservations and then using these  as they want. But that should not be the way to move on.

Feminism is not about using your rights above humanity. Its not about reservation for ladies. Although even I support female reservations because bringing the big change in male dominant society is not that easy. I respect the necessary reservations made for females. But at the same time I hope one day they will vanish .I hope that day will come very soon when we will not need any more reservations to make our females feel safe , feel equal to men in cultural, social and economic rights.

Feminism is not about gluing to the seat reserved for ladies, its about offering your seat to someone who is more needy (mostly aged people) than you. When you do so, it s reflects that you are as strong as a male passenger and that is feminism (being equal to males). Feminism is about building a free and equal society for both men and women. Its about building a world where women will not need female only coaches. It about creating an environment where females will feel safe without any special security arrangements and measures. Its about empowering females in such a way that they do not need any additional favor.

Does Feminism Hurt Humanity?

Now I will come back to the question with which I started my blog post. According to me the answer is a straight No. Feminism does not hurt humanity, its the warped feminism that hurts. Its the wrong interpretation of the word feminism that hurts the humanity. So it is very important for all of us to interpret the definition of feminism correctly and then use it.

Read it on linkedin here 

Why Strings Are Immutable In Dot Net?

Next Read : An Extensive Examination Of ArrayList in C#

Most of the developers know that strings in .Net are immutable. But a very few knows the reason behind this behavior.  I will try to explain the same in this article.

Before diving into the reason, let me first explain what do we mean by immutable?

What do we mean by Immutable strings? 

The dictionary meaning of immutable is “unchanging over time or unable to be changed”. This means once a value is assigned to String object, it can never be changed. Yes, you read it correctly. Consider the following code:


Output of this code will be.


Though it seems as if we just changed the value of myString from “abc” to “abcdef” and then to “abcdefghijkl”, but we really didn’t! Lets try to understand it. In first step, a new string object is allocated on the heap with value of “abc” and myString points to this memory location. At step no 2 (myString += “def”;), a new string object is allocated on heap with value of “abcdef” and myString now points to this new memory location. But the string “abc” still exists on heap. So we actually sit with two string objects on the heap, even though we’re only referencing one of them. Continuing same way, at the end of this code we will have four string objects, with only one object referenced and other three unused. The following memory allocation diagram of above code will make things more clear.

Memory Allocation of immutable strings
Memory Allocation of immutable strings

Now we will move on to the context of why.

Why strings are immutable in dot net?

Designers of .Net decided to implement immutable text strings. They have multiple reasons for this architecture. If programmers have multiple string variables with same value, it will avoid allocating memory for same string value multiple times. It will allocate memory to string once and all the variables will point to the same memory block.  Consider the following block of code.

same string

Memory allocation for this code will look like this:

Memory Allocation of Same String
Memory Allocation of Same String

If strings were mutable, changing the value of str1 would have changed the value of str2 and str3 also, which is unwanted.

Second, immutable strings eliminates race conditions in multi threaded applications. Any text amendment causes creation of a new variable so there is no need to set up the lock to avoid conflicts while multiple threads simultaneously access text. In some cases, those race conditions could be used to mount security attacks.  For example, you could satisfy a FileIOPermission demand with a string pointing to an publicly accessible section of the file system, and then use another thread to quickly change the string to point to a sensitive file before the underlying CreateFile occurs.

Another reason for string immutability is the well adapted use of strings as keys in hashtables. The objects on which the hash values are computed must be immutable to make sure that the hash values will be constant in time.

Another cool thing about string immutability is that even though System.String is a class, string objects get compared with equivalence, as a value type. This is possible because we can consider that the identity of an immutable object is its state. Consider following piece of code:

String Comparison

Even thought str1 and str2 reference 2 different objects ,the above code returns true.

StringBuilder : An alternative to avoid creation of unused strings

As we saw in figure “Memory Allocation of immutable strings”, there are unused strings allocated in memory. Its because of the way string behaves. If a code makes thousands of operations on string , heap will have thousands of unused string objects leading to unwanted memory wastage. Fortunately we can avoid this by using StringBuilder class. In my next article I will discuss about this class.

I hope you must have enjoyed the article. Please leave your comments and feedback in comments section at bottom. If you have any doubt or query, please feel free to ask in comments. Thanks for reading.

Click to read it on LinkedIn

Next Read : An Extensive Examination Of ArrayList in C#

An Extensive Examination Of ArrayList in C#

Next Read : Why Strings Are Immutable In Dot Net?

We all use Arrays in c# and other programming languages. Array creates some limitations on design. First, Arrays are homogeneous i.e. you can store only one type of elements. Secondly, when using arrays you must specifically allocate a certain number of elements. Often developers want something more flexible – specially for uncertainty in size of collection. The .Net Framework Base Class Library provides such a data structure called ArrayList located in System.Collections Namespace.

ArrayList is nothing but a Heterogeneous and Self Re-Dimensioning Array :

Elements of different types can be added to the ArrayList. Further, we do not have to concern ourselves with redimensioning the ArrayList. All of this is handled automatically for us. An example of the ArrayList in action can be seen in the code snippet below.

Array List Example
Array List Example

Behind The Scenes :

Behind the scenes the ArrayList uses System.Array of type Object. An object array can hold elements of any type Since all types are derived from Object (either directly or indirectly). By default the size of this array is 16, although it can be defined in constructor or by assigning capacity property. Elements can be added to ArrayList using Add() Method. Behind the scenes , Add() method first compares the no of elements in array with its capacity. If adding the new element causes the count to exceed the capacity, the array is redimensioned and the capacity is automatically doubled.

Performance :

ArrayList provides some additional flexibility compared to array, but this flexibility comes at cost of performance, majorly if you store value types. The ArrayList’s internal array is of object type, so every value type is boxed and stored on heap and each ArrayList element is a reference to a boxed value type. When you access a value type element it is unboxed before you can use it.

The boxing and unboxing, along with the extra level of indirection that comes with using value types in an ArrayList, can hamper the performance of your application when using large ArrayLists with many reads and writes.

ArrayList Data Structure Memory Allocation
The ArrayList contains a contiguous block of object references

The above diagram shows the memory allocation for ArrayList.

The sel-redimensioning of ArrayList should not cause a performance degradation if compared to array. Because you can turn off self-redimensioning by specifying the initial capacity in constructor. If you dont know the exact size, you may have to re-size even with array also when the number of elements inserted increases the size of array.

Memory Allocation on Redimensioning:

Why the size of ArrayList gets doubled when it gets redimensioned? Its a classic computer science  problem to find out how much extra memory should be allocated when running out of space in some buffer.

One option is to allocate just one more element in the array when redimensioning. i.e. if the initial size of array was 10 and when adding 11th element, resize the array to 11. This approach conserves most of the memory but becomes very costly as redimensioning is required at insertion of every additional element.

Second option is to redimension the array 100 or 200 times larger than the current size. i.e. if array is initially allocated 10 element, before inserting 11th element resize it to 1000 elements. This approach greatly reduces the redimensioning overhead , but , if only a few more elements needs to be added, the extra allocated space is wasted.

So after trying various options, the true compromise is to just double the size of array when it becomes exhausted. This is the precise approach that ArrayList takes and its all done automatically for us.

Summary :

  1. ArrayList internally uses array of object type.
  2. ArrayList provides more flexibility than simple array.
  3. Precise size of ArrayList can be set in constructor or by capacity property. By default its 16.
  4. While adding, if no of elements in array exceeds its capacity, array is redimensioned to double of its current size.
  5. Boxing and Unboxing of value types degrades performance of arraylist.

Next Read : Why Strings Are Immutable In Dot Net?


Twitter Reacts To Brazil’s Humiliating Defeat Against Germany

Germany vs Brazil Soccer World Cup
Germany vs Brazil Soccer World Cup

The country of 200 million people had hoped to win the title at home and erase memories of its 1950 World Cup defeat to Uruguay in Rio de Janeiro. But instead Brazil suffered the worst defeat in its 100-year footballing history, falling 7-1 to Germany at the Mineirao Stadium in the southeastern city of Belo Horizonte.

The game created a new record as being the most talked about on twitter as the rate of tweets went up while the host nation crashed out of the tournament. By the time the match ended at 7-1, the entire social media was full of comments and humour regarding Brazil’s record-breaking loss.

Here is the list of some tweets from public reaction to the historic semi final