Ranking function  help us assigning a rank value for each row in your result-set. They support the windowing the partition too. Books Online states that these functions are non-deterministic. These are the ranking functions available in SQL Server. RANK() Returns the rank of each row within a result set. The… Continue Reading Ranking Funtions – Explained

What is SARGability? The word SARGable comes from the term “Search ARGumentable”. This tells if an item can be found by using an index, provided such an index is available. Understanding sargability can really impact the  ability to write well-performing queries. If a WHERE clause is not sargable, the Query Optimizer… Continue Reading What makes a query SARGable?

What is the escape character in SQL? Assume you have to find the the names that contains single quote (‘), in this scenario single quote(‘) itself works as the escape character.  SELECT * FROM Person.Person WHERE LastName LIKE ‘%”%’ Consider the situation where you are tying to find records which… Continue Reading Escape Character – MS SQL

Often many ask, what is the difference between union and union all? Union operator combines multiple resultsets into one resultset. Union also removes the duplicates and returns a sorted result set. Union All  operator combine multiple resultsets into one resultset, but it does not eliminate the duplicate rows.  Since the duplicate… Continue Reading Union v/s Union All

How we can find the first and last day of any month? This is a very easy code, this function accepts a offset parameter that would help you to find last of any previous months, or coming months. CREATE FUNCTION getLastDayOfMonth (@dt DATE,@offset INT=0) RETURNS DATE AS BEGIN RETURN Dateadd(d,-1,Dateadd(mm,Datediff(m,0,Dateadd(month,1,@dt))+@offset,0)); END GO See this function in action… Continue Reading Find end date for a month