Voice of the DBA

Author: way0utwest

  • The Hidden Patterns in Data

    There’s a great TED talk on statistics, and one I’d recommend you watch. However, it’s not so much the topic and data presented, but the way in which it’s presented and broken down that becomes interesting. If we ignore the topic, and just look at the analysis, a few  things stand out that I’d apply to business.

    The first noticeable thing for me was the breaking down of the large bubbles into smaller ones. I think that all too often we try to boil down information to a single, or very limited number, of values. While the overall average or graph of a set of data can be helpful, it can also be distorted by outliers in the underlying data. Seeing the data broken part into quintiles helps to analyze how closely the overall average matches up with the data.

    The other item that I think sometimes get lots of analysis of business data is the change across time. We often look at year over year, or last period’s values, but we don’t dive much deeper into the changes over time. Seeing a series of numbers on a page works well for some people, but the animated visual can be helpful in discerning a pattern or making a decision based on data.

    This is a great look at statistics, and it makes me think many of these techniques can be handy in looking at something like server performance metrics and deciding if we need more (or less) resources, or even if we have good candidates for consolidation. I’m looking forward to trying to adapt these tools for my own uses.

    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.

  • 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.

  • Who Am I?

    I saw Brent Ozar updating his About Me slide, and thought it was interesting. Not sure if I like what he’s done, but it’s different, which usually attracts attention.

    I updated mine last year, with some help from the people at Red Gate. Here’s my slide.

    2015-05-26 18_30_25-CI for Databases.pptx - Microsoft PowerPoint

    No description of jobs, what I’ve done, my age, company, etc. No MVP or other awards or certifications.

    I wasn’t sure about this early on, but I like it. I can tailor the talk about me to the situation, making it short or long. I also include the information on the right at the end of the deck as the last slide, so people can grab it there.

    I’m not sure if this works, but I have seen a bit more traffic to my blog and more Twitter/LinkedIn activity.

    I also get some fun comments when I’m actually wearing this shirt at a talk.

  • 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.