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

Recently I was working with a customer on a drive space issue and the disk space alert function failed to alert DBAs about this on time. I was just amused why the alert didn’t work at all. After some research it turned out that the customer had recently added a… Continue Reading xp_fixeddrives ignores SAN mount points