Voice of the DBA

Author: way0utwest

  • Unstructured Data

    Is unstructured data a bad term? I saw some data professionals complaining about this, saying all data is structured. That’s usually true. A CSV, even a ragged one has structure. XML and JSON have structure, even if it might vary node to node. Certainly our relational tables are structured and some formats can be rigidly mandated between organizations (like EDI). Even data in PDF, Word, MP3, MP4 or other audio/video mediums is structured in that we know the format.

    Given that, is it a misnomer to use the term, unstructured, when describing flexible formats, such as XML? Is it OK for a PDF? I have had a presentation called Unstructured Data in SQL Server. This is primarily about FileStream, FileTable, and searching those objects. In the talk, I classify data in known formats as structured. These would be SQL Server tables and similar objects. At any point in time, we know what all data in the table looks like, even though we can have NULLs or missing data in rows.

    I call XML and JSON semi-structured formats. We can certainly determine the format for any node or section, but we wouldn’t know without querying or examining the data. It’s semi-structured in that there is a hierarchy, but the structured from section to section (essentially row to row) can vary. There can even be depths to hierarchies that vary. In many ways, that makes these great formats for flexibility in data exchange.

    I tend to view data in Word, PDF, MP4s, as unstructured. We don’t necessarily know where the data is, or how to separate it. We can get pages in Word or PDF, but those can vary and don’t necessarily help us extract information. They are XML, but the XML tags don’t relate to the content, unlike many other XML documents. Scenes or tracks in audio/video files might be separators, but those aren’t necessarily helpful in gathering information. Instead, we need other tools that can help deal with that data, finding words, concepts, or more inside of the binary stream.

    I like the term unstructured data because it helps me understand where the information is. While the tables in a database might be full of nonsensical information in some rows, or be poorly designed with data combined into text fields, at least I know where the fields are. Actually, in that case, I’d argue the data in varchar(max) text fields is really unstructured. You might disagree, but give me a better term to describe there the information is stored in a data format.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Basic FORMATting– #SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I saw the addition of FORMAT() to the T-SQL language, but didn’t play with it much. Recently it appeared in some code, and decided to experiment a bit. I had assumed this was mainly for dates, but it’s a general format/culture function that handles numbers as well.

    On the doc’s page, there are the basic description of the parameters, which are NVARCHAR(), so passing in VARCHAR() causes an implicit conversion. It shouldn’t be much, but there are already performance penalties (see Aaron Bertrand’s piece), so don’t add to the overhead.

    One good thing to note is that if you pass in invalid formats or cultures, a NULL is returned. Since the format and culture strings aren’t completely intuitive, this might be a source of issues in your code.

    This is a neat function, relying on CLR formatting rules. That means I can do fun things like:

    DECLARE @i int = 5000;

SELECT FORMAT(@i, N'USD$#');

    Which returns:

    USD$5000

    Or even:

    DECLARE @i INT = 5000
;
SELECT  FORMAT(@i, N'# dahlahs')
;
GO

    Which gives me:

    5000 dahlahs

    There are lots of formats, and certainly lots of nuances to numeric formatting strings. It’s worth reading up if you plan to use this, but again, beware of performance. I’d avoid using this if the data size is large, maybe more than a few hundred rows.

    After all, the database server is a shared resource, and using this CPU to handle simple formatting may not be the best use of your system.

  • All Day DevOps Slides

    The conference will upload the slides to Slide Share at some point, but if you want to get them now, here are the slides from my talk:

    Including Database in DevOps – AllDayDevOps.pptx

    If you  have questions, drop a note at AllDayDevOps.Slack.com in the #modern-infrastructure channel. I’m way0utwest.

  • Redgate at Summit 2017

    I’ll be heading out to the PASS Summit next week, spending Halloween in a conference center with a lot of other geeks. I know some of you won’t make it, and we’ll miss you, but I understand.

    I’m meeting my colleagues from Redgate there, including the new editor of Simple Talk, Kathi Kellenberger. We’ll be at the event all week and we look forward to chatting with you about databases, DevOps, and more. We’ve also got a few events during the week that we hope you’ll attend.

    Stop by the Redgate Booth in the expo center for a demo, or just to chat about SQL Server.

    Adopting a DevOps Process for Your Database

    I’ve got a session on Wednesday, Nov 1, at 1:30 that will provide an overview of how you might rub some DevOps on your database development. I’ll use some of the Redgate tools, but really I want you convince you that a smoother, more reliable database development process is possible.

    Lower your risk, make your deployments reliable and repeatable. Come join me, see how the process can work, and ask lots of questions. I’ll have lots of answers.

    How DevOps for the Database Helps with Compliance

    Immediately after my DevOps talk, I’ll be joining Grant Fritchey and Richard Macaskill to talk about DevOps and compliance. We are seeing more and more organizations bound by rules and regulations that require them to be careful about who can see their production data and how it’s stored. This will mean that more of us need to be careful about how we manage data.

    With the move to DevOps and releasing changes more often, this can present some challenges for data professionals. However, DevOps can actually help with compliance if done right. Join Grant, Richard, and I for a discussion of the challenges and potential solutions.

    Redgate Rocks

    If you are coming to the Summit, make sure you stop by the Redgate booth to pick up your #RedgateRocks ribbon for entrance to our party Thursday night. I’ll be there with the Redgate crew to celebrate the last evening of the Summit at 1927 Events.

    This is your chance to get out of the Conference Center, head towards the ocean and have a good time in downtown Seattle with us.