Voice of the DBA

Author: way0utwest

  • Implicit Time Conversions – #SQLNewBlogger

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

    I was trying to work with times recently and needed to get the current time. I thought, well, Getdate(), or better yet, SysDateTime() will give me a date and time, but what about the time?

    A simple experiment showed it’s easy:

    DECLARE
    @t TIME,
    @t1 TIME;
SELECT @t = SYSDATETIME(), @t1 = GETDATE();
SELECT @t, @t1;

    I got this:

    2017-02-24 12_34_19-SQLQuery2.sql - (local)_SQL2016.PartsUnlimited_Grant (PLATO_Steve (59))_ - Micro

    Quick, easy, and what I suspected would work. If you need to work with times, you can easily cast a datetime value to a TIME to strip the date, or just assign the values to a time.

    CREATE TABLE TimeTest
(t TIME)
GO
INSERT TimeTest
SELECT top 10
 CreationDate
 FROM dbo.Posts
 GO
 SELECT top 10
  *
  FROM dbo.TimeTest
GO
DROP TABLE TimeTest

    This code takes a datetime column and just inserts the time into the new table.

    SQLNewBlogger

    Literally about 3 minutes of my day to write this. When you learn something, write it down.

  • Detecting Issues

    Here’s a simple question: how are more of your application issues detected, by people or systems? I bet most of you initially think of your monitoring systems and the automated messages or pages that are sent out regularly as detecting most, or even all, of your problems. Have you stopped to think how many times a phone call lets you know about an issue? Do you consider the ways in which a code review or human tester brings up a concern?

    I try to think about all software problems, both the ones that reach production and the ones that are prevented early. If I catch a SELECT * in a view during development, I can prevent a problem months later when a table adds a column and no one refreshes the view in production. Those potential issues that never get to the customer are wins for me, and I think this is something we should be proud of as software engineers and testers.

    Can you move the numbers, though? Is there a way to find problems before people find them? I think there is, and it’s with better monitoring and better testing. For monitoring, we need better, and more, instrumentation that measures what we expect, looks for deviations, and (low level) alerts someone. This is an area where I think machine learning and better analysis will help. Those ML models can be hard to setup, so I’m hoping that some individuals or projects will start some work here. Microsoft is doing some of this in Azure, and I hope they share some knowledge with us.

    Testing is really the way to catch more issues before humans do. We’ve known this for decades in software development, but so many developers have been resistant to the idea of building some sort of formal test for their code. It’s not fun, it’s hard to maintain, and really, it’s just hard for most people to start writing tests.

    I think things are getting better with testing frameworks that make building and executing tests easier. We have frameworks for all major application languages, and even quite a few for T-SQL. We’ve also learned more about the types of tests to write, which type to ignore, and how to avoid building so many brittle tests that testing is more work than coding features. If you know nothing about testing, you owe it to yourself to spend a little time learning about unit testing and practicing writing tests.

    Now that we are collecting more and more data about our applications, we have the opportunity to really build software that better meets the goals and needs of our customers. However, we have to take advantage of this data, and the advances in testing, to ensure that we build the best software we can.

    Steve Jones

    The Voice of the DBA Podcast

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

  • How Do You Use SQL Clone?

    On Monday, Redgate released v1.0 of SQL Clone, and I’m excited that the product is finally available. I’m excited because this has been a long time coming, but it’s worth the wait.

    You can certainly download SQL Clone and try it out, but I’d also say you should check out this virtual tour to see how the product works. It makes sense to me, but I’ve been testing it for months.

    I’ve also written a few pieces about how SQL Clone plays with TDE (part 1 and part 2), and there’s plenty more at the SQL Clone site. Check it out today and see how you can easily build dev/test environments, at full size, without using all the disk space.

  • TSQLTuesday.com

    I’ve hosted a page on this blog that has all the T-SQL Tuesday blog invitations and roundups linked. I did this mainly because I was trying to ensure I have hosted a few, I wanted to know what others had chosen. Since I didn’t see a good list, I created one.

    And I’ve maintained it, which seems crazy since it’s been 6+ years.

    Recently on a Twitter conversation someone suggested getting a central site, using a real domain. I thought that was a good idea, since my page isn’t necessarily the most intuitive item to find.

    Somehow I ended up getting a domain transfer from Brent Ozar and getting talked into hosting the site. It’s live, now at:

    TSQLTuesday.com

    I did this with a simple Azure website, using WordPress. That’s another story, and I should blog about it, but for now, the site is live and running there. I’ve added Adam and Brent as administrators, so they can help maintain things.

    I’m also open to suggestions on how to better organize things. I’d like to get a page for each entry, but we’ll have to see how that works.