T-SQL: Get Last Day of Month

One of my early blog posts highlighted how to get the first day of the current month. Well, another common task is get to the last day of a month and i'm (reasonably) pleased to see that SQL Server 2012 has an inbuilt function to achieve this.

In versions previous to SQL2012, this problem can be solved by running the following:

DECLARE @InputDate DATE = GETDATE() 

SELECT DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,@InputDate)+1,0))


However, we can now just use the inbuilt function EOMONTH which should make coding easier to read.

DECLARE @InputDate DATE = GETDATE()

SELECT EOMONTH(@InputDate)


-- 2012-02-29 



You can also specify an offset to the function which will allow you to look at months x number of months from your inputdate which I can certainly see being useful.

DECLARE @InputDate DATE = GETDATE()

SELECT EOMONTH(@InputDate, 4)


-- 2012-06-30

Note how the return type is a DATE and not a DATETIME.  Interestingly, Books Online suggests that the return type is "start_date or datetime2(7)"  although my tests suggest it always returns DATE.

T-SQL: INSERT with XML shredding is very slow

Back in 2010, I came across a performance bug with SQL2008 SP1. When shredding XML using the nodes query, the performance is fine with a SELECT but when you want to INSERT the data to a table/temptable/tablevariable the performance becomes very poor.

The query I was having problems with was something like this (Thanks for the sample data wBob!):

DECLARE @xml XML;
-- Spin up some test data 
WITH cte AS 
( 
SELECT 65 x 
UNION ALL 
SELECT x + 1 
FROM cte 
WHERE x < 90 ) 

SELECT @xml = (
    SELECT CHAR( a.x ) + ' & co.' AS "name"
    a.x AS "value"
    'FIELD' AS "paramdata/field"
    FROM cte a 
      CROSS JOIN cte b 
      CROSS JOIN cte c
    FOR XML PATH('root'), TYPE
    ) 

DECLARE @ConfigData TABLE (ID INT, Name NVARCHAR(255), Value NVARCHAR(255), ParamData XML)

INSERT INTO @ConfigData (ID, Name , Value, ParamData) 
SELECT 1,
    tbl.cols.value('name[1]', 'varchar(1000)'),
    tbl.cols.value('value[1]', 'varchar(1000)'),
    tbl.cols.query('./paramdata[1]')
FROM @XML.nodes('//root') AS tbl(cols) 
GO

I raised this with MS via Connect (#562092) and went on to implement my own workaround using the following CLR shredding function:

public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "tvf_clr_FillParameterData",
        TableDefinition = "Name nvarchar(255), Value nvarchar(255), ParamData nvarchar(255)")]
    public static IEnumerable tvf_clr_ParameterDataShredder(SqlXml parameterData)
    {
        XmlDocument document = new XmlDocument();
        document.LoadXml(parameterData.Value);
        return document.SelectNodes("//parameter");
    }

    public static void tvf_clr_FillParameterData(object row, out string outName, out string outValue, out string outParamData)
    {
        XmlNode document = (XmlNode)row;

        outName = document.SelectSingleNode("name").InnerXml;
        outValue = document.SelectSingleNode("value").InnerXml;

        outParamData = null;
        if (document.SelectSingleNode("paramdata") != null)
            outParamData = document.SelectSingleNode("paramdata").OuterXml;
    }
};
 
This served me well and solved the performance issue and all was fine. Until now.

The thing is, this method doesn't handle decoding of escape characters in XML, so any &s that exist in the XML string being shredded will be returned as &:amp. Not ideal. My first thought was to just implement a decoding function within the CLR routine but the main candidates use System.Web which is not available within SQL Server. I eventually stumbled upon this post from Jonathan Keyahias which provided a sql safe Encoding implementation and I was going to use this as a basis for writing my own decoding function. While waiting the 20mins for Visual Studio 2008 to open, it occured to me to revisit the Connect case and fortunately, there were a number of workarounds there, and a combination of the solutions the most effective.

INSERT INTO @ConfigData (ID, Name , Value, ParamData)
SELECT     1,
    tbl.cols.value('(name/text())[1]', 'nvarchar(255)'),
    tbl.cols.value('(value/text())[1]', 'nvarchar(255)'),
    tbl.cols.query('./paramdata[1]') 
FROM @xml.nodes('//root') AS tbl(cols) 
OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

The above simple modification to my SQL INSERT statement and performance was acceptable and more importantly, the decoding of the escaped characters was fine. Saved me a lot of work.

T-SQL: SQL2012 Code Snippets

I've never really bought into the idea of object templates in SQL Server Management Studio and in my experience, not many other database professionals have either!

Just recently however, I've been getting a increasingly frustrated at the non-standard development pattern of database objects and (just as importantly) the lack of documentation for procedures and functions. I've written in the past about using Extended Properties to help version your database and this could easily be extended to document objects too. However, in a previous role I picked up quite a nice habit of adding documentation headers to each procedure and function which gives some basic information to anyone looking at the procedure to give them a headstart in debugging/understanding. A heading may look like this:


/*
created by: Richard Brown
date created: 02/02/2012
description: this procedure just returns information about customers
debug:
   declare @custid = 1
   exec dbo.getcustomers @custid

*/

Encouraging developers to do this in practice is a real problem and this is where code snippets can come in. There are numerous default snippets available in SQL2012 management studio and you can access them by hitting CTRL-K, CTRL-X and the tabbing to the appropriate snippet:

Here you can see there are already a few default stored procedure snippets including a "create a simple stored procedure" snippet. You can create/add new snippets as you see fit but for my purposes, I want to just modify the existing snippets so they include my header as default.

Take a note of the location of the snippets by navigating to Tools/Code Snippets Manager:

You can then find the .snippet file and make the appropriate modifications. Its just an xml file and for my needs I just needed to make the following change:

<Code Language="SQL">
            <![CDATA[
/* 
created by: 
date created:
description:
debug:

*/
CREATE PROCEDURE $SchemaName$.$storedprocname$ 
    @$Param1$ $datatype1$ = $DefValue1$,
    @$Param2$ $datatype2$ 
AS
    SELECT @$Param1$,@$Param2$ 
RETURN 0 $end$]]>
        </Code> 

When I hit the snippet function, I now get the updated template for my stored procedure. Hoepfully, this will encourage the devs to follow the standards.