Voice of the DBA

Author: way0utwest

  • The Travel Review

    I got a note from United this week that summarized my travel for 2017. I’ve been feeling a bit itchy as I haven’t had any trips scheduled for 2018 so far. Strange for me, but looking over the summary, I’m glad.

    Last year I flew 44 times with United, for 81k miles. I had 25 domestic flights and 19 international ones. That’s kind of crazy. Of course, outside of Denver, London is my most visited location.

    For hotels, I logged 26 stays with Hilton that totaled 62 nights. That was low because I got stuck in a few other hotels at events early in the year. Plus I think I had 8 or 9 Air BnB nights. A lot of time away from home.

    I’m a numbers person, so it’s neat to see the summaries from the services I use.

    The one big number for me that shows me I traveled too much? Total workouts for the year: 265. That’s way too low.

  • Disable or Drop

    When I started working with SQL Server and Windows, it seemed that the administrative side of many actions was limited. We could add and drop many items, but that was it. Relatively few tools allowed setting limits or disabling them, which was a pain. After all, I’d gotten used to setting up accounts for vendors and contractors in Active Directory, often disabling them when they weren’t in use. I couldn’t do this in SQL Server for many objects, which was a pain and an administrative burden to reset them up when troubleshooting issues. This was pre-PowerShell and .NET when any SMO access was a project in and of itself.

    These days SQL Server has done a great job of adding in the ability to pause or disable many objects. We’ve had the ability to lock out an account for many versions, which is a great way to setup a vendor tech support account when it’s needed. This is especially important for security these days, as we may want to be sure that we prevent access by any suspect accounts. Disabling them allows us to prevent their use, but keep all their rights and permissions in the even they are valid accounts.

    We can disable indexes, which can be useful as a precursor to deleting them at some point. We can disable triggers, which is incredibly useful when you are testing or debugging actions on a table. We can disable Extended Events, audits, and more. All of these are useful actions for a developer or administrator, if you use them.

    When things go wrong, we’re often stressed and pressed for time. If there are issues with a system, many of us make snap decisions, which might fix the problem or make the situation worse. Even in those cases where we fix an issue, deleting or dropping objects might cause is extra work later. My question for you is what’s your first reaction? When you need to make a quick change to remove something, security access, an index, etc., do you drop or disable?

    I would hope that you disable, as this removes the effects but keeps the object in the system with associated meta data. Rebuilding permissions or trying to get the old trigger code is a pain (since few people use a VCS, please start doing this). It’s possible that you won’t even be able to get things reset back up in the same manner. That might be fine, but it’s not ideal as new code should be tested, and in a crisis, it likely won’t be looked at too closely. At least the previous version of the code was tested in production.

    Build the habit to disable, not drop, and I think you’ll be glad you did.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Job Interviews: What is Normalization?

    This is part of a series that looks at interview questions that you might be asked.

    One of the cornerstones of a relational database is normalization. This is a database design technique that is used to decide how we might distribute data among tables and columns. If someone were to ask you “what is normalization” in your next job interview, what would you say? How do you answer this question?

    Depending on the position, and your experience, perhaps you’d talk about this in practical terms, with an example. Maybe you have a scenario you use to explain the concepts. I tend to lean towards an e-commerce type database, with orders, customers, and products. That’s an easy concept for people to grasp. I can then give examples of how normalization might change the way we store data.

    Do you there are various forms of normalization? We can talk about first, second, third, fourth, or other normal forms of the database. Perhaps you can explain these from memory? Maybe you know the academic definitions. Maybe you have your own description. I certainly think if the position may involve designing tables that you should be able to talk about the differences between these forms and determine what form a particular database is in.

    My answer would include quick explanations of how I would avoid repeating groups, columns not dependent on the PK, and how sometimes the third normal form starts to impact performance because of the number of joins required for simple queries. I would also be ready to expand on these topics, explaining what a PK is and how to choose one.

    This is a complex topic,and I doubt an interviewer would expect anyone to provide a thirty minute lecture, which wouldn’t completely cover the topic. Instead, I would think that if an interviewer is interested in this topic, they will ask probing questions to determine if you understand the concepts. This means you can’t just memorize the definition. You should ensure that if I have you a table, you could break it down into the entities for 1st, 2nd, and 3rd normal form. Perhaps you should read a few basic articles to get some understanding. Maybe you need to delve a little deeper into design and the anomalies that normalization attempts to mitigate.

    Prepping for an interview shouldn’t be a cram session to learn more, but really a review of concepts you understand. Make sure you have some examples to explain your thoughts and practice a few of these questions with a friend by giving them an answer and listening to their feedback.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Advent of Code 2017 Day 2–#SQLNewBlogger

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

    This is day 2 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. Here is a look at Day 2, which deals with checksums of rows. However, it’s not just rows, but rows and columns.

    Part I

    In part 1, we are given a series of numbers in rows and columns, essentially a spreadsheet. We need to look at each row and compare all the values in columns.

    This is slightly tricky for T-SQL. The input is a series of text values, so I cheated slightly and inserted those as rows into a table (I like tables). If I were taking this as a programming item, I’d have an import process, so I’ll do that here inline.

    I have some idempotent logic here as I was testing and didn’t want to recreate tables and inserts, nor did I want to comment/uncomment things in/out.

    IF NOT EXISTS( SELECT name FROM sys.sysobjects AS s WHERE name = ‘Day2’)
    CREATE TABLE Day2
    ( DataRow VARCHAR(1000)
    )
    GO
    IF (SELECT COUNT(*) FROM dbo.Day2 AS d) = 0
    INSERT Day2 VALUES
    ( ‘179    2358    5197    867    163    4418    3135    5049    187    166    4682    5080    5541    172    4294    1397’),
    ( ‘2637    136    3222    591    2593    1982    4506    195    4396    3741    2373    157    4533    3864    4159    142’),
    ( ‘1049    1163    1128    193    1008    142    169    168    165    310    1054    104    1100    761    406    173’),
    ( ‘200    53    222    227    218    51    188    45    98    194    189    42    50    105    46    176’),
    ( ‘299    2521    216    2080    2068    2681    2376    220    1339    244    605    1598    2161    822    387    268’),
    ( ‘1043    1409    637    1560    970    69    832    87    78    1391    1558    75    1643    655    1398    1193’),
    ( ’90    649    858    2496    1555    2618    2302    119    2675    131    1816    2356    2480    603    65    128′),
    ( ‘2461    5099    168    4468    5371    2076    223    1178    194    5639    890    5575    1258    5591    6125    226’),
    ( ‘204    205    2797    2452    2568    2777    1542    1586    241    836    3202    2495    197    2960    240    2880’),
    ( ‘560    96    336    627    546    241    191    94    368    528    298    78    76    123    240    563’),
    ( ‘818    973    1422    244    1263    200    1220    208    1143    627    609    274    130    961    685    1318’)

    The input might be different for you, so you’d have to do your own work.

    Splitting a row of data into more rows seems like something easier done with the STRING_SPLIT function, so I’ll use that. This will give me a series of values from the table. I decided to use an inline TVF for this, since it’s a bit easier to read.

    The trick here is that the data is tab separated, and \t doesn’t work in SQL Server. However, CHAR(9) works, so let’s use that. Here’s the function:

    CREATE FUNCTION AdventChecksum ( @input NVARCHAR(200))
    RETURNS TABLE
    AS RETURN
    SELECT diff = MAX(CAST(myint AS INT)) – MIN(CAST(myint AS INT))
      FROM (SELECT myint = CAST(value AS INT) FROM STRING_SPLIT(@input, CHAR(9))) AS ss
    GO

    I need to get the difference between the largest and smallest values. STRING_SPLIT will return strings, so I cast this to an INT, then use MAX() and MIN() for the row, subtract one from the other, and return that.

    Now I have a way to get a row checksum, so let’s get the entire table. We’ll do that with a CROSS APPLY, and then sum up all the values returned:

    SELECT SUM(b.diff)
      FROM day2 a
      CROSS APPLY dbo.AdventChecksum (a.DataRow) b

    I guess it works, because I got the right value from a test set, and the puzzle shows as solved. Smile

    Part II

    Part II adds a twist. Now I don’t need to just compare two values, I actually need to see if any of the values are equally divisible by the other. This means I need to compare all values against each other.

    The STRING_SPLIT() function still works well here, however, I need all combinations of the values divided by each other. An evenly divisible set of numbers would have this pseudocode:

    INT(a) / INT(b) = FLOAT(a) / FLOAT(b)

    Or, there’s another way to look at this. The remainder is 0, so the remainder of the values, using the modulo (%) function, is 0.

    One last trick, each number is divisible by itself, so let’s avoid those. The instructions don’t mention the possibility that the only values evenly divisible are matches, so we’ll assume that’s not the case. Checking inputs, this appears to be OK.

    I’ll create a new function, where I CROSS JOIN the STRING_SPLIT to get all combinations.

    CREATE FUNCTION AdventChecksum2 ( @input NVARCHAR(200))
    RETURNS TABLE
    AS RETURN
    SELECT divmatch = CASE WHEN (CAST(a.value AS INT) % CAST(b.value AS INT)) = 0 AND a.value <> b.value
            THEN (CAST(a.value AS INT) / CAST(b.value AS INT))
          ELSE 0
          end
           FROM STRING_SPLIT(@input, CHAR(9)) a
       CROSS JOIN STRING_SPLIT(@input, CHAR(9)) b
    GO

    Now I cross apply as in Part I and sum the values, which works. The puzzle is solved.