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

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

While preparing the demo for my previous post “Optimize for ad hoc workloads – SQL 2008“, I encountered an issue and that’s the topic for this blog. I executed the sp_configure followed by RECONFIGURE and it returned me an error. sp_configure ‘optimize for ad hoc workloads’, 1; reconfigure The error message stated the ad hoc updates… Continue Reading Ad hoc update to system catalogs is not supported

SQL Server 2008 had introduced a new feature that helps optimizing ad hoc workloads. SQL server generates an execution plan for each and every statement that’s executed. The reason is that the SQL server would be able to re-use the execution plan when the statement run next time. However, there… Continue Reading Optimize for ad hoc workloads – SQL 2008

SQL 2012 introduced two new analytical functions LEAD and LAG that would help accessing a preceding and subsequent rows in a query. Consider the subsequent example, we have to find the employees who were absent for two consecutive days   EmployeeID        SigninDate   1 2014-01-03 1 2014-01-04 1… Continue Reading LEAD and LAG functions in SQL 2012