[T-SQL Tuesday] Some code is born crap, some code achieves crapness and some code has crapness thrust upon it!

jamiet — Wed, 10 Aug 2011 15:04:00 GMT

I recently returned to a client at which I last worked back in 2006 and, as you might imagine, that gives me ample material for a blog post dedicated to Crap Code; conveniently the subject of the latest T-SQL Tuesday.

I first worked for this client back in 2004 and that is significant because back then we were writing code on SQL Server 2000. Ah, thems were the days!!! I smiled wryly to myself when I encountered the following code in a stored procedure that I once wrote:

--Simplified for convenience/demo
CREATE PROCEDURE [dbo].[usp_PersonInsert]
(
   @pXMLDataInsert     TEXT
)
AS
BEGIN
   DECLARE @vDoc            INT
   EXECUTE sp_xml_preparedocument @vDoc OUTPUT, @pXMLDataInsert
   SELECT
       ID, 
       Name
   INTO    #Insert
   FROM    OpenXML(@vDoc, 'NewDataSet/Table', 2 ) 
   WITH    (
       ID  INT     'ID',
       Name    VARCHAR(70) 'Name'
       )
   EXECUTE sp_xml_removedocument @vDoc
   INSERT INTO dbo.Person(ID,Name)SELECT ID,Name FROM #Insert
END

If you have never had the pleasure(!!!) of dealing with XML data prior to SQL Server 2005 then you're probably looking at this with a slightly confused look on your face wondering quite why we had to make this so complicated. TEXT datatype? OPENXML? System stored procs? What's in this @vDoc variable? On the other hand if you have had to deal with XML pre-2005 then right now you're probably nodding sagely to yourself and remembering the not-so-good-old-days!

No-one would ever write code like this today but back in 2004, before wonderous things like the XML datatype and table-valued-parameters, this was how one passed datasets into a stored procedure (well, its how we did it anyway). If any of my colleagues were to write this today they would probably be despatched home with a copy of Inside SQL Server 2008 : T-SQL Programming tucked under their arm and ordered not to come back until they could recite it ad infinitum but back in 2004, well, we thought we were pretty cool!

Times change, people change and coding practices change too. Code isn't born crap, it just tends to crapness the longer that it lives! Bear that in mind when coding today because in five years time you'll probably be coming back and having a little chuckle at it!

@Jamiet

Unambiguous date formats : T-SQL Tuesday #001

jamiet — Tue, 08 Dec 2009 20:23:41 GMT

One of the most commonly used data types in SQL Server is [datetime] which unfortunately has some vagaries around how values get casted. A typical method for defining a [datetime] literal is to write it as a character string and then cast it appropriately. The cast syntax looks something like this:

DECLARE @dt NVARCHAR(19) = '2009-12-08 18:00:00';

SELECT CAST(@dt AS datetime);

Unfortunately in SQL Server 2005 the result of the cast operation may be dependent on your current language setting. You can discover your current language setting by executing:

SELECT @@LANGUAGE

To demonstrate how your language setting can influence the results of a cast take a look at the following code:

ALTER  DATABASE tempdb

SET        COMPATIBILITY_LEVEL = 90 ; --Behave like SQL Server 2005

USE tempdb

GO

DECLARE @t TABLE (

      dateString  NVARCHAR(19)

);

INSERT @t (dateString)

VALUES ('2009-12-08 18:00:00') --'yyyy-MM-dd hh24:mi:ss'

,      ('2009-12-08T18:00:00') --'yyyy-MM-ddThh24:mi:ss'

,      ('20091208 18:00:00')   --'yyyyMMdd hh24:mi:ss'



SET        LANGUAGE french;

SELECT 'french' AS lang

,      DATENAME(MONTH,q.[dt]) AS mnth

,      q.[dt]

FROM   (

       SELECT  CAST(dateString AS DATETIME) AS dt

       FROM    @t

       )q;

SET        LANGUAGE us_english;

SELECT 'us_english' AS lang

,      DATENAME(MONTH,q.[dt]) AS mnth

,      q.[dt]

FROM   (

       SELECT  CAST(dateString AS DATETIME) AS dt

       FROM    @t

       )q;

We are taking the value which can be described in words as “6pm on 8th December 2009”, defining it in three different ways, then seeing how the @@LANGUAGE setting can affect the results. Here are those results:

Notice how the interpretation of the month can change depending on @@LANGUAGE. If @@LANGUAGE=’french’ then the string '2009-12-08 18:00:00' is interpreted as 12^th August 2009 (‘août’ is French for August for those that don’t know) whereas if @@LANGUAGE=’us_english’ it is interpreted as 8^th December 2009.

Clearly this is a problem because the results of our queries have a dependency on a server-level or connection-level setting and that is NOT a good thing. Hence I recommend that you only define [datetime] literals in one of the two unambiguous date formats:

yyyy-MM-ddTHH24:mi:ss
yyyyMMdd HH24:mi:ss

That was going to be the end of this blog post but then I found out that this behaviour changed slightly in SQL Server 2008. Take the following code (see if you can figure out what the results will be before I tell you):

ALTER  DATABASE tempdb

SET        COMPATIBILITY_LEVEL = 100 ; --Behave like SQL Server 2008

GO

USE tempdb

GO

SET        LANGUAGE french;

DECLARE    @dt NCHAR(10) = '2009-12-08 18:00:00'; --Ambiguous date format

SELECT CAST(@dt AS datetime) AS [ExplicitCast]

,      DATENAME(MONTH,@dt) AS [MonthFromImplicitCast]

,      DATENAME(MONTH,CAST(@dt AS datetime)) AS [MonthFromExplicitCast];

Here we are doing three different things with our nchar literal:

explicitly cast it as a [datetime]
extract the month name from the char literal using the DATENAME function (which results in an under-the-covers implicit cast)
extract the month name from the char literal using the DATENAME function after it has been explicitly casted as a [datetime]

Note that the compatibility level is set to SQL Server 2008 and @@LANGUAGE=’french’. Here are the results:

(Were you correct?)

Let’s take a look at what is happening here. The behaviour when we are explicitly casting as [datetime] hasn’t changed, our nchar literal is still getting interpreted as 12th August rather than 8th December when @@LANGUAGE=’french’. The [MonthFromExplicitCast] field is interesting though, it seems as though the implicit cast has resulted in the desired value of 8th December. Why is that?

To get the answer we can turn to BOL’s description of the DATENAME function syntax:

The implicit cast is not casting to [datetime] at all, it is actually casting to [date] which is a new datatype in SQL Server 2008. The new date-related datatypes in SQL Server 2008 (i.e. [date], [datetime2], [time], [datetimeoffset]) disregard @@LANGUAGE and hence we get behaviour that is more predictable and, frankly, better.

These new behaviours for SQL Server 2008 were unknown to me when I began this blog post so I have learnt something in the course of authoring it, I hope it has helped you too. No doubt someone somewhere is going to get nastily burnt by this at some point, make sure that it isn’t you by always using unambiguous date formats:

yyyy-MM-ddTHH24:mi:ss
yyyyMMdd HH24:mi:ss

regardless of which version you are on!

Don’t forget to check out other T-SQL Tuesday blog posts, see Adam Machanic’s blog post Invitation to Participate in T-SQL Tuesday #001: Date/Time Tricks for details!

@Jamiet

Search results matching tags 'T-SQL', 'T-SQL Tuesday', and 'sql server'

[T-SQL Tuesday] Some code is born crap, some code achieves crapness and some code has crapness thrust upon it!

Unambiguous date formats : T-SQL Tuesday #001