Voice of the DBA

Tag: T-SQL

  • Visualizing the Tally Table

    I was reading Dwain Camps’ article on Time Slots and thought it was a very interesting solution to a problem I’ve had a few times. Getting time slots inside of a period that I want to query. If you have a similar need, or want to learn more, I’d urge you to read the article.

    It’s always easier to join to a set of data that matches what you need than to try and filter out other rows. SQL excels at joins, so whenever possible you want to join to data. As such, when I was looking at Dwain’s code, I thought the way he listed the tally table was very interesting. I’ve seen plenty of these generated, but I hadn’t run across someone spelling it out in comments. In case you are wondering, Dwain had code like this:

    WITH Tally (n) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based
    -- Returns exactly 86400 rows (number of seconds in a day)
    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows 
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows
),                                                                          -- = 86,400 rows

    That’s a great visualization, and one I plan on using in the future. It makes it easy to see what’s being generated and at what scale.

    For example, I can easily do this:

    SELECT
        ROW_NUMBER() OVER ( ORDER BY ( SELECT
                                            NULL
                                     ) )
    FROM
        ( VALUES ( 0), ( 0) ) a ( n )                   -- 2 rows

    which returns a single column table with the values 1 and 2 in it. Two rows.

    Let’s say I want to now build a list of 12 rows. I could do this a few ways. One is to multiple 2 x 6 and get 12.

    SELECT
   ROW_NUMBER() OVER (ORDER BY ( SELECT NULL))
 FROM
   ( VALUES (0), (0) ) a(n)         -- 2 rows
   CROSS JOIN 
   ( VALUES (0), (0), (0), (0), (0), (0) ) b(n);  -- x 6
;

    Or I could give myself more flexibility to add and remove data with comments by doing factorials. How about 2 x 3 x 2 = 12?

    SELECT
   ROW_NUMBER() OVER (ORDER BY ( SELECT NULL))
 FROM
   ( VALUES (0), (0) ) a(n)         -- 2 rows
   CROSS JOIN 
   ( VALUES (0), (0), (0) ) b(n)  -- x 3
   CROSS JOIN 
   ( VALUES (0), (0) ) c(n)  -- x 2
;

    That gives me the same result: 12 rows. Of course, I can easily expand this quickly to thousands of rows.

    The technique isn’t anything new, but the visualization is interesting, and to me, this is much easier technique to see and understand when you run into it in code. Right away I know I’m generating xx rows and I can easily see how to grow or shrink the number of I have the need.

  • Changing NULL to NOT NULL – Dealing with Data

    I wrote recently on how to alter a column in SQL Server from NULL to NOT NULL in a simple way. However I didn’t cover some of the cases where you have data in the column, or other restrictions that you might need to deal with. In this post, I want to look at a few options if you have data in the table.

    Let’s take my SimpleTable and add data:

    INSERT Simpletable VALUES (1, ‘A‘, 1) , (2, ‘B‘, 0) , (3, ‘C‘, NULL) , (4, ‘D‘, NULL) ;

    Now I have 4 rows. If I run this:

    ALTER TABLE dbo.SimpleTable ALTER COLUMN Status TINYINT NOT NULL;

    I get this error:

    Msg 515, Level 16, State 2, Line 2

    Cannot insert the value NULL into column ‘Status’, table ‘Sandbox.dbo.SimpleTable’; column does not allow nulls. UPDATE fails.

    The statement has been terminated.

    Essentially, SQL Server can’t mark this column as NOT NULL because there are NULLs in there. What can I do here?

    UPDATE the Table

    The only thing I can do is to change the data. I can run a query that removes all the NULLs in the column by setting them to some value. That’s what I’ll do here. I can choose a value here and run an UPDATE statement. Something like this:

    UPDATE dbo.SimpleTable SET Status = 0 WHERE status IS NULL;

    Once I do that, I can easily run my ALTER and it succeeds.

     

  • Refactoring Mistakes Are Why We Write Tests

    I wrote a short piece the other day trying to show how one can use tSQLt to test code. It’s a simple test built against a user defined function. It works well and when the test is run, it passes.

    Here was my code:

    ALTER function [dbo].[calculateEstimateOfReadingTime] ( @value varchar(max) )
returns int
as
    begin
      declare
        @ret as int = 1
      , @i as int = 1;
      while @i <= len(@value)
        begin
          if substring(@value, @i, 1) = ' '
            begin
              set @ret = @ret + 1;
            end
          set @i = @i + 1;
        end
      return @ret / 250;
;    end

    Someone in the comments pointed out that we can more efficiently refactor this code to :

    ALTER FUNCTION [dbo].[calculateEstimateOfReadingTime] 
( 
    @value varchar(max) 
)
RETURNS int
AS
BEGIN
RETURN (
    SELECT LEN(@value) - LEN(REPLACE(RTRIM(@value), ' ', '')) + 1
)
END

    However when I run the test, I get these results:

    functiontesta

    That’s not good, but that’s why we test.

    I could easily see someone refactoring the code, finding a more elegant method of rewriting this code and after running some quick tests, they check this in to source control (hopefully) and maybe deploy it to production. Hopefully QA catches this, but wouldn’t we want to notice this in development?

    The refactored code misses a divide by 250.

    Write tests, use them to catch mistakes. These simple ones slip through at times and are what make deployments really, really stressful.

  • Why Test Table MetaData Tests with tSQLt

    I wrote at SQLServerCentral about using tSQLt to check table metadata. In essence we are testing the API of our table. However, since the table could change, and may need to, what’s the value of having a test fail if the table changes?

    In my mind, I don’t necessarily want to have table structure tests for all my tables. After all, developers need to have flexibility to work with and change tables in our applications. If it’s a pain for a developer to change every table, because a test fails and they have to go change the test, that’s an issue.

    There’s also the problems of a developer changing a table, changing the test, and then having everything pass, without passing along information that a schema change was made.

    I would limit the API tests for a metadata to those tables that are important, with the caveat that anytime someone fails a metadata test, they need to inform the team.

    But Steve, isn’t every table important?

    Yes and no. Certainly all tables should be important to the application in some way, but really many of them are contained in the application. If changes are made, it’s not necessarily a problem to change other objects to catch up to the table change. However, some tables may cross teams or applications and they are an issue.

    As an example, I have lots of tables in the SQLServerCentral database.

    tablemetadata_1

    If the Blogs table, or the Articles table changes, then we need to alter stored procedures and possible ASP.NET code for our application. In fact, in this list, pretty much all of these tables could be changed by a developer without a large impact, assuming they’re going to look at the other objects or code affected.

    However the table highlighted, the emails table, along with a few others, are important. These tables not only support SQLServerCentral, the web app, they are also called by our emailer process, which is a completely separate application. In essence, these tables are the opposite of a microservice. They’re shared.

    If someone wants to change the Emails table, I want to be sure that others are informed. In fact, I might choose to include a note in the test header that various groups need to be informed or that the table affects another application. In that case, before a developer went to change the test, they might at least have a chance or noting this has far reaching implications.

    tablemetadata_2

    It’s not a perfect solution, but it does help. The other thing I could do is limit access to metadata tests for various tables/views and merely call these tests in a CI, or other automated, process. That way failures would be public, and a variety of people could be informed, preventing a developer from making changes without a discussion.

    As I mentioned, I wouldn’t do this for all tables. In fact, I’d limit this to particularly sensitive tables that might require lots of rework if they were changed. We want to speed development, and ensure code works, not slow developers down.