Voice of the DBA

Author: way0utwest

  • GUI or CLI

    There’s a debate over on Scott Hanselman’s blog about the GUI v CLI in the comments for his Azure CLI post. It’s a blend of people complaining about one or the other, mixed with a few (I think) rational people that recognize the GUI and CLI are both valuable. In fact, there’s one person that mentions SQL Server and the SSMS GUI that can generate scripts for later use.

    I like the command line, but sometimes it’s hard. I don’t remember the syntax for a variety of commands, including various T-SQL ones, because I don’t use them very often. I may find myself depending on Google or BOL for a quick refresher, but often I’ll just use use SSMS if I can to pick some items in the GUI, click Script, and then examine the code before executing it.

    I really don’t mind someone wanting to use the GUI the vast majority of the time they’re working, but someone should be comfortable with a CLI interface. If there is a task that need repeating multiple times, then I really want everyone using the GUI. It doesn’t matter how you’ve written the code, executing something from a CLI is much more reliable than having to click around a GUI, consistently and quickly, over and over.

    I’d like to think that most computer professionals these days are able to use a CLI, even if they aren’t too comfortable. The growth of PoSh as a wrapper around so many features and functions in the MS stack certainly contributes to this, as well as the showcase of much code as code, not as images from a GUI. Github and collaboration, as well as more and more samples of code should mean that many people are comfortable working with code and executing it from some CLI.

    I’m sure there are plenty of exceptions. Someone that works mostly with SQL Server might be happy running code in a query window, but use SSMS for everything else, and maybe not even be aware that they can build things like SQL Agent Jobs from a CLI. I get it, the GUI is quick and easy. I use it for jobs, for Extended Events, and various other tasks where the code is complex and cumbersome. However, if something is easier from a command line, I like using it. Perhaps that’s why my console of choice, ConEmu, is always just a CTRL+~ away.

    Steve Jones

     

  • GUI or CLI

    There’s a debate over on Scott Hanselman’s blog about the GUI v CLI in the comments for his Azure CLI post. It’s a blend of people complaining about one or the other, mixed with a few (I think) rational people that recognize the GUI and CLI are both valuable. In fact, there’s one person that mentions SQL Server and the SSMS GUI that can generate scripts for later use.

    I like the command line, but sometimes it’s hard. I don’t remember the syntax for a variety of commands, especially PowerShell where I use tab completion constantly. I don’t even remember options for many T-SQL functions, because I don’t use them very often. I may find myself depending on Google or BOL for a quick refresher, but often I’ll just use use SSMS if I can to pick some items in the GUI, click Script, and then examine the code before executing it.

    I really don’t mind someone wanting to use the GUI the vast majority of the time they’re working, but someone should be comfortable with a CLI interface. If there is a task that need repeating multiple times, then I really want everyone using the CLI. It doesn’t matter how you’ve written the code, executing something from a CLI is much more reliable than having to click around a GUI, trying to be consistent and quick, over and over. The CLI just works better.

    I’d like to think that most computer professionals these days are able to use a CLI, even if they aren’t too comfortable. The growth of PoSh as a wrapper around so many features and functions in the MS stack certainly contributes to this, as well as fact we seee so much code as code, not as images from a GUI. Github and collaboration, as well as lots of code samples in articles should mean that many people are comfortable working with code and executing it from some CLI.

    I’m sure there are plenty of exceptions. Someone that works mostly with SQL Server might be happy running code in a query window and use SSMS for everything else. They might not even be aware that they can build things like SQL Agent Jobs from a CLI. I get it, the GUI is quick and easy. I use it for jobs, for Extended Events, and various other tasks where the code is complex and cumbersome. However, if something is easier from a command line, I like using it. Perhaps that’s why my console of choice, ConEmu, is always just a CTRL+~ away.

    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.

  • Getting the tSQLt Run Adapter working in Visual Studio 2017

    Last year I heard about the tSQLt test Adapter for Visual Studio from Ed Elliot. I’ve been wanting to try it, but various items got in the way. Finally I had the chance to play and it worked well in Visual Studio 2015, but I needed it in VS 2017. Fortunately Ed had a tSQLt Run Adapter beta for Visual Studio 2017, but I had a few issues. This is a debugging post.

    I downloaded the file and ran setup. Since this can cause issues with VS 2015, I unchecked that box. Unfortunately, I think I messed up my VS 2015 project. No matter, we’ll forge on.

    I had a Readyroll project where I was doing some work. In following some of the work at Redgate from a developer, I set up a new test project according to the tutorial. I got through and no tests.

    Hmmm.

    The .runsettings file is set in the root of my solution, as shown here:

    The contents are:

    <?xml version="1.0" encoding="utf-8"?>
<RunSettings>
  <TestRunParameters>
    <Parameter name="TestDatabaseConnectionString" value="Data Source=.\SQL2016;Initial Catalog=PartsUnlimitedDB;Integrated Security=True;" />
    <Parameter name="IncludePath" value="Tests" />
  </TestRunParameters>
</RunSettings>

    My local instance is .\SQL2016, a named instance, and I have a PartsUnlimitedDB database on this instance.

    Here the file is selected:

    When I run all tests, I get this:

    I heard from Ed that I needed to have the name “tests” in the filename, so I changed that. Here’s the test and the file name

    Now I see my tests in the test explorer. Success!

    Just to check a few things, let’s try another file. Here I’ll use a shorter name, though still descriptive.

    And again, success.

    That felt strange, but some back and forth with Ed showed me that the IncludeFile filter in the .runsettings file needs to be set to some value. In the default file I used, it’s set to “tests”. If I change it to test, and include a new test, then I things still work:

  • Using tSQLt to Find Min/Max Times

    I love tSQLt. It’s a good way to write tests that can determine if your code is actually working. Since I’m a fan of unit testing, I think using tests to verify your logic is great. What’s excellent with tSQLt is that I can verify a number of cases at once.

    I ran across this post asking for help with a query. Given the sample data and results, I wrote this proc and test. In the test, my “Act” is calling a proc I wrote that executes the first post’s query.

    CREATE OR ALTER PROCEDURE RunTimeTests
AS
BEGIN
    SELECT
        Taskid,
        MIN(StartTime),
        MAX(EndTime),
        DATEDIFF(MINUTE, MIN(StartTime), MAX(EndTime))
    FROM TimeTests
    GROUP BY Taskid;
END;
GO
EXEC tsqlt.NewTestClass @ClassName = N'tTimeTests'
GO
CREATE OR ALTER PROCEDURE [tTimeTests].[test calculation min max time from timetests]
AS
BEGIN
    -- assemble
    EXEC tsqlt.FakeTable @TableName = N'TimeTests', @SchemaName = N'dbo'

    INSERT into TimeTests
        VALUES 
        (1, '2017-02-23 09:48:47.413',NULL ),
        (1, '2017-02-23 09:50:47.413', '2017-02-23 10:59:47.413' ),
        (1, '2017-02-23 09:49:47.413',Null ),
        (2, '2017-02-23 10:40:47.413','2017-02-23 11:55:47.413' ),
        (2, '2017-02-23 10:39:47.413', NULL ),
        (2, '2017-02-23 10:11:47.413','2017-02-23 11:30:47.413')

    CREATE TABLE tTimeTests.Expected
    ( taskid INT, Mindtime DATETIME2(3), maxtime DATETIME2(3), Minutes int)

    INSERT tTimeTests.Expected
     VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
          , (2, '2017-02-23 10:39:47.413', '2017-02-23 11:55:47.413', 76)   

    SELECT *
     INTO tTimeTests.Actual
      FROM tTimeTests.Expected
      WHERE 1 = 0;
    -- act
    INSERT tTimeTests.Actual EXEC RunTimeTests;

    -- assert
    EXEC tsqlt.AssertEqualsTable
     @Expected = N'tTimeTests.Expected', @Actual = N'tTimeTests.Actual', @Message = N'Incorrect times'
    
END

    When I run this, it easily verifies the answer that the data is incorrect from the poster.

    2017-02-24 13_08_32-SQL Test - Microsoft SQL Server Management Studio

    If I change my expected results:

        INSERT tTimeTests.Expected
     VALUES (1, '2017-02-23 09:48:47.413', '2017-02-23 10:59:47.413', 71)
          , (2, '2017-02-23 10:11:47.413', '2017-02-23 11:55:47.413', 104)

    and re-run the test, it succeeds.

    2017-02-24 13_09_53-SQL Test - Microsoft SQL Server Management Studio

    Now, does this mean the developer wouldn’t make this mistake? After all, if you think you should be getting those results, you will struggle with the query.

    It doesn’t help there. However, it does help if you modify this code later and start to have strange results. This also means that I can add in more rows to the data, even more cases, and determine if the procedure still works. If I’m trying to cover a dozen cases, it’s much easier to re-run a tSQLt test than manually looking through results.

    Give tsqlt a try. It’s free, and if you have the SQL Toolbelt, you can get a GUI with SQL Test for executing your tests.