Voice of the DBA

Author: way0utwest

  • Ensuring Designs have Flexibility

    One of the biggest complaints from developers about RDBMSs is that they are inflexible and hard to change. This has led to many developers in the past using existing fields for a variety of purposes, sometimes putting data from multiple places into one field. In more recent times, this has led to adding or migrating data to new data stores, with the idea that the system can adapt easily to changing requirements.

    I ran across a post on system flexibility that somewhat illustrates the issue. The writing is a little hard to understand, but the author is thinking about the process of ordering food and how a system might need to handle some of the exceptions or additional changes. While the employees in a store might deal with this in an ad hoc manner, those of us building software need to think through the possible issues and account for (at least) some of them in code.

    Software typically has much more flexibility than we do at the database level, since anyone building software is usually building on top of the database system. However, those of us that design schemas need to ensure that we take care to consider the different ways that our data store will serve the needs of the end user and design in the proper structures needed to support the data that will be stored.

    In my experience, it’s important for the database developer to question the requirements of the customer. Just as a software developer might try and tease out additional features and functions that are needed, the database developer needs to carefully query the customer to ensure that there won’t be additional data that the customer assumes is being captured. We need to be aware of relationships and cardinality between the data elements, in order to better design a system that meets the current, and potential future, needs.

    Building and designing a database isn’t that hard, but it does take some attention to detail and a meticulous nature to think through how some real world situation is modeled. While I think small, frequent DevOps releases are a way to move forward, I’d also like to ensure that we capture as much detail as early as possible to minimize disruptions. After all, refactoring and changing the schema is harder in a database system than in an application software class.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Getting Parameters Out From a Stored Procedure–#SQLNewBlogger

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

    One of the lesser used and known features of T-SQL are the output parameters from a stored procedure. I used one of these recently, so I wanted to blog about it.

    Getting a String

    I was working on part of the 2018 Advent of Code, which is a great set of programming exercises for anyone. I typically build a procedure to solve each puzzle, since that’s a common way of capturing code. If there’s a single numeric result, a RETURN code works fine.

    In one puzzle, I needed to return a string. If you try this in a procedure, it won’t work.

    2018-12-27 10_04_01-SQLQuery6.sql - Plato_SQL2016.sandbox (PLATO_Steve (58))_ - Microsoft SQL Server

    Instead, I need another solution. I could certainly SELECT back my string, but in this case, I wanted to have this assigned to a variable. I could do that in a few ways, but decided the easiest was an OUTPUT parameter.

    To add an output parameter to my procedure, I first add my variable as a regular parameter.

    CREATE OR ALTER PROCEDURE dbo.StringTest
       @s VARCHAR(10)
    
AS
    
BEGIN
         SELECT @s = 'Some Code'
    
END
    
GO

    Next, I add the OUTPUT keyword after the type.

    CREATE OR ALTER PROCEDURE dbo.StringTest
       @s VARCHAR(10) = '' OUTPUT
    
AS
    
BEGIN
         SELECT @s = 'Some Code'
    
END
    
GO

    My call to the procedure should also include the OUTPUT keyword.

    DECLARE @result VARCHAR(10);
    
EXEC dbo.StringTest @s = @result OUTPUT;
    
SELECT @result;

    This works fine, allowing me to pass some value back to the caller.

    2018-12-27 10_08_40-SQLQuery6.sql - Plato_SQL2016.sandbox (PLATO_Steve (58))_ - Microsoft SQL Server

    Not something I use often, but if I need to get some singular value back, this works.

    SQLNewBlogger

    This post was started at 10:00am one morning. I got back to this sentence at 10:09. That was the entire setup of the code, capturing screen shots, and writing the post. Easy for you to do as well.

    Give this a try. How would you use an OUTPUT parameter?

  • Fun with Dates and Times

    The other day I got a Github issue that asked if we here at SQLServerCentral should set our dates as YYYYMMDD in the new SQLServerCentral site. The issue noted that there were some inconsistent dates. Article dates are in a similar format, YYYY/MM/DD, but we do have some DD MMM YYYY and a few places where I think the American MMBBYYYY has lived on.

    In any case, I made a joke on Twitter of asking if people were alright moving to YYYYMMDD. Most people were in favor, though someone asked about MMMM DD, YYYY, which is easily read. I have to say that I somewhat like that for reading, but as data, as something I’d work with, I prefer having the year month day format. I do want separators, and while I grew up using slashes ( as in YYYY/MM/DD), I know the ISO format is dashes (YYYY-MM-DD). ISO 8601 has dashes, so I decided to add that to the GitHub issue as a way of formatting.

    What about using month names? As I look through various sites, I find inconsistencies. Some use dates, like Jan 11, 2019. Others might use 11 Jan 2019 or 2019-01-11. I find that MSDN uses regional settings, so the en-us version of a page will have 01/11/2019, but if I change to the en-Gb version, I see 11/01/2019. That seems find as a UI enhancement, but I hope that the actual value is stored as 2019-01-11. We don’t need them time

    Or do we? There are times involved. While most of us might not care about whether something was published at 8am or 9am, we might care about where it was published and the time. Certainly 8am in Christchurch if far different from 8am in Honolulu. Do we store time zones? Or keep all dates in one? The logical thing is to keep dates in UTC and then adjust based on the client settings to display something that matches their time zone. We’ve been storing data in UTC, though the server time has been the Mountain time zone in the US for historical reasons. I don’t like that dependency, so I’m sure we’ll change that.

    Dates and times are both simple and not simple. There are decisions to be made, and far too many of us take them lightly. While the display is of secondary importance, it’s likely that we’ll often use this data in other systems, as a way of marking a change, or even as a way of ordering our data. Having consistent and known date time formats is important as a data professional. I hope you pay attention to how you gather and store this data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Measuring Effectiveness

    I’ve never been the guy that produced code the fastest, or the most code in a day. I haven’t been able to show that I work the most hours, or that I even keep great hours. I’m often late or erratic in the times I come and go from work. I don’t show that I am closing tickets in consistent times as I often will be slow to close new issues. I haven’t ever been the employee of the month.

    What I have been is effective. I get things done. I leave the organization and the job better than when I found it. I work to deliver value for the salary I’m paid. That isn’t something you can easily measure with many traditional metrics that people use, but there are ways to decide that I’m a good employee. It’s a little ephemeral and sometimes uncomfortable to have an employee that doesn’t seem to fit the same model as most other employees, but that doesn’t mean you wouldn’t want me working on your staff.

    This article on measuring DevOps tries to explain how you can determine if your DevOps process is working, which is a similar ephemeral way of working. When we implement DevOps, we don’t have large project plans, we don’t look forward to completing a system. We undertake a set of work, knowing that we don’t have an end date. We just keep doing work and getting things done that are needed. In this case, how can we measure if a project was on time, on budget, and finished?

    I’d argue, as would the article, that those aren’t good measurements. Those are the ways we would traditionally look at work, but in software, those aren’t often the things we want to look at. If we deliver software that meets some requirements, but users struggle to use it or complain, is it done? Most management would call it done, just like the airlines mark the plane as leaving on time when it pushes back from the gate. Standing on the runway for 45 minutes doesn’t count against that target, even though I’d call that leaving late.

    The article talks about picking things that bother a manager or team members about the software or their process. Some of these are easy, like the time to deliver a new server or recover from a failure. Others that I’ve found to be useful are the time to assemble a release or deploy software to an environment. While we can sometimes play with what these numbers measure by moving to smaller set of changes or altering our process, we’ll quickly find out if we have a lot of overhead in our work by adding these measurements.

    What about the software itself? Can I actually compare the time to deliver report a vs. report b? One might be much more complex than the other. That’s true, but we ought to average out delivery estimates over time, and we can certainly separate out query writing of the complex logic against the time required for formatting. In fact, it might be good to start to measure different parts of software delivery to find out if certain people are better at some parts, or if requirements from clients are causing unnecessary delays. I know I’ve certainly had some formatting complaints require more time to get right than the entire rest of the software.

    It can be hard to develop metrics that have outcomes and are truly actionable, but it’s a better way to determine if your team is improving. Relying on simple and traditional metrics is lazy, and allows for lots of argument and debate over whether a team is doing well or not. Tackling those specific items that are irritating to management or customers might take some work, but you’ll end up with a list of things that can be targeted for improvement and show progress that actually means something.

    Steve Jones

    The Voice of the DBA Podcast

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