• Rod at work - Wednesday, June 28, 2017 8:00 AM

    Just curious to know, do other people here declare columns as VARCHAR and then stuff in XML or JSON?

    No, if I'm storing XML, I definitely use the xml datatype. From my perspective, storing XML in a varchar is like storing a numeric or date in a varchar. I just slows processes down when you want to do "simple" tasks. You can't SUM a varchar, SQL server has to implicitly cast it, which means an overhead. XML is the same, and if you want to use XQuery, you'll need to cast it first (which is an unneeded overhead).

    I've not used JSON in a SQL environment. I've dabbled with it briefly when experimenting with Python on Linux, but that's it. I can, however, see the need (or want) for it. JSON is now at least readable and producable in SQL 2016, but it isn't a data type. Unfortunately, I don't know the true strength/weaknesses of JSON to have an opinion on if the data type will bring much in.

    The date CAST(... AS <Type>, <FORMAT>) function feels very much like using FORMAT(CAST value AS date),'dd-MM-yyyy'), which you can already do in SQL. I'll admit, however, that FORMAT is not quick and could probably do with some work if SQL Server introduce something along the lines of CAST(value AS date,'dd-MM-yyyy'). Otherwise, you're still better off sticking with CONVERT.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk