Voice of the DBA

Author: way0utwest

  • Quick Tips–SQL Prompt Custom Aliases

    I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

    Custom Aliases

    SQL Prompt can automatically create aliases for tables. However, as I’ve worked on different systems, I’ve often found that development teams like to use specific aliases consistently to ensure that everyone can easily read the code and understand which tables are being queried.

    Suppose I decide that I have these tables:

    • Product
    • Product Details
    • Orders
    • OrderDetails

    I often use these tables in queries in my system, and I want to have consistent aliases. Right now, I could have these two queries with the default SQL Prompt alias settings:

    aliases20

    Note that the Orders table has “O” as an alias in the first query, but “O2” as an alias in the second query. This isn’t an issue when I’m writing a query, but when I revisit this code in a month or two and add an enhancement, it can be tricky.

    What I’d like to do is ensure I had consistent aliases for my tables, so that every developer always knows that “o” is Orders and “od” is OrderDetails. I want these aliases

    • Product – P
    • Product Details – PD
    • Orders – O
    • OrderDetails – OD

    I can do that in SQL Prompt with custom aliases. Let me go back to the Options dialog and select Aliases.

    aliases_21

    I’ve already added an item in the Custom aliases section for the product table. However I can click New (highlighted above) and I’ll get a little dialog.

    aliases22

    I repeat this process for each table, and soon I have all four entered.

    aliases_23

    Now I can rewrite my query. I start typing each table, and once it’s highlighted, click “tab”. When I get done, I have:

    aliases_24

    A quick way to ensure that all of your tables are consistently aliased, no matter in which order you type things.

    aliases_25

    You can see a complete list of SQL Prompt tips at Redgate.

  • Data Decisions or Instinct?

    Most of us that are data professionals think the best way to make decisions is to use data to justify some course of action. We look for patterns in data, some guidance that the information we have will lead us to make the best choice for our organization. Google has talked about making data driven decisions as a part of their success and they think more organizations should do this. Any number of other companies also use data to power their BI systems and dashboards that help their employees make better choices.

    That seems in contrast to this piece from the Harvard Business Review that says that great decisions don’t start with data. It talks about using stories and emotions, with a few key facts sprinkled in, to help sell ideas and get decisions made. On one hand I agree that stories help to sell decisions, but I often have found that successful salespeople use this technique to deceive and convince by plucking emotional heartstrings, and using relatively little data.

    In my mind, the best way to make decisions is to go with your instincts, but while examining and understanding the data. You can’t discard data, especially when it presents strong patterns. However data can be deceiving when we don’t carefully examine the ways in which it’s put together. An average doesn’t always reflect the actual value of a set of numbers, especially when we don’t also understand the range, standard deviation, and count of values.

    We also have to realize where we do and don’t have experience and expertise in some subject. We should certainly look to data to guide us and perhaps even justify our decisions, but we can’t forget that the human brain is still an important part of any computational exercise. We need employees that you use their judgement, in collaboration with data, to make the best decision for our organizations.

    Steve Jones

    The Voice of the DBA Podcast

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

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • T-SQL–Converting Seconds to Time

    I was working on a small piece of code the other day that was calculating the seconds for an event. I had a function that returned me the seconds as an integer. That’s good, but I wanted to get that value back in minutes and seconds. The scale wasn’t so large as to worry about hours, or days (I hope).

    In any case, I could certainly do some math. Takes seconds, divide by 60 to get minutes, and then take the remainder and add that as seconds, concatenate, convert to time. Crazy.

    There’s an easier way using CONVERT.

    DECLARE @s INT
SELECT
    @s = 325
SELECT
    @s
  , CONVERT(TIME, DATEADD(SECOND, @s, 0));

    I can just add the seconds to the 0 time, which is midnight, and I’ll get the time back in the right datatype.

    This code gives me 5:25, which is correct. Five minutes and 25 seconds.

    If I increase the numbers, say into hours, I can take 4325 like this:

    DECLARE @s INT
SELECT
    @s = 4325
SELECT
    @s
  , CONVERT(TIME, DATEADD(SECOND, @s, 0));

    And get this;

    time

  • DevOps

    The DevOps movement is supposed to promote a closer collaboration between developers, testers, operational people, and really anyone else that must help software gets build and deployed. The developers need to coordinate their needs and requirements with operational staff. They should work early on with any staff performing testing to help them ensure that bugs are caught quickly and fewer slip through to production. At the same time, operational staff should be responsive  and helpful in all the environments software moves through. Operational staff should help build and automate the configuration of development and testing environments, ensuring that systems are built in a repeatable fashion as well as the same way in each stage of the software development pipline.

    However, is devops killing the developer? If developers are becoming responsible for testing, that’s bad. If developers must learn to function as a DBA as well, then I certainly expect they’ll write less code. If developers need to ensure the software can be installed on production systems and train operations staff, then I suspect they aren’t very happy with their jobs.

    But that’s not DevOps. If that’s how your organization runs its development, it doesn’t understand DevOps. Developers should be leaning on and learning from, as well as teaching other staff. When I read that post, I think the developer has missed the idea of DevOps. It isn’t that developers need to become completely versed and responsible for the full stack. It’s that they should be working with everyone involved in the process the entire time that software is being developed, using the specialized knowledge each person has.

    DevOps isn’t something new. I’ve worked in teams that worked like this in the past, but we never had a name for what we did. We just thought we were part of the same team. I’ve also worked in organizations with walls between all groups. That wasn’t a team, and we had lots of four letter words for that style of process.

    DevOps is a good term and a good idea for software development. If a company implements it well, then it works and people are happier with the way software is produced. If they aren’t, then the process hasn’t been configured correctly.

    Steve Jones

    The Voice of the DBA Podcast

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

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.