Voice of the DBA

Tag: T-SQL Tuesday

  • Data Presentation – T-SQL Tuesday #22

    TSQL2sDay150x150It’s T-SQL Tuesday again, with our host this month being Robert Pearl, of Pearl Knowledge Solutions. He chose the topic of Data Presentation for September.

    If you haven’t participated in a T-SQL Tuesday, it’s easy. Read the rules in Robert’s post and read the basic idea in Adam Machanic’s original invittation. Then write a blog post with the logo to the right in it and link it back to Robert’s post.

    That’s it.

    Formatting Data

    One of the things that DBAs and data professionals should learn is that formatting typically doesn’t belong in the database. The client, front end, or the tools used to extract the data should handle formatting, presenting the data as the client wants to see it.

    When you store numbers, you don’t want to store things like currency symbols. So in SQL Server I should see this:

    datapresent1

    and not this:

    datapresent2

    The former gives more flexibility, and the latter limits what you display (and requires character storage).

    The same thing goes for other data, for example, phone numbers. We don’t want to store our phone numbers like this:

    datapresent3

    If someone wants to see just the number, or they add in another format of number (say European), then you have inconsistencies. Also, it becomes harder to separate out issues. When you do separate out the number from the area code, how do you handle things if you have this:

    datapresent4

    That’s hard to handle. If all the phone numbers were numbers, it’s easier to handle. This allows one easy CASE statement based on length.

    datapresent5

    Or better yet, use better design and let the front end handle data presentation.

    datapresent6

    Doing It Right

    I guess I haven’t talked data presentation so much as how not to handle data presentation. I know that SQL Server can do things like ordering, formatting, combining or splitting strings, or more, but ultimately I think that’s not the right way to handle things.

    I’ve always viewed the database as the single bottleneck. It’s incredibly hard, and expensive, to scale a database server, while it’s easier to scale web servers, app servers, and client tools, and much less expensive. It’s even easier to scale developers and have them write more code to handle presentation on the front end instead of using SQL Server to do the work. I’d try to always push any presentation work to the client instead of the database server, just because of the workload and bottleneck on the server.

    It might seem like more work up front, and it will be, but it will be infinitely better than trying to re-write code or upgrade your database server later when the load becomes larger.

  • T-SQL Tuesday #19 – Disaster Recovery

    TSQL2sDay150x150This month Allen Kinsel hosts T-SQL Tuesday #19 with a disaster recovery theme. This is the blog party idea started by Adam Machanic where everyone that participates writes on a single theme.

    If you want to host, contact Adam. If you want to participate, read Allen’s post and write your own post and link it in.

    What’s a Disaster

    It’s interesting that this is the theme since I already had two posts on DR scheduled this week and am working on modifying my “Preparation for Disaster” presentation for SQL in the City.

    It’s hurricane season for Allen on the Gulf, and while hurricanes give you advance notice when they are coming, you have to be prepared. I’ve lived on the East coast, and we always were worried about getting hit in Virginia Beach. We never did get hit, but that didn’t stop us from being prepared and sometimes even initiating some of our disaster protocols.

    As a side note, we had an office in High Point, NC, and it actually got hit by a hurricane, or the remnants of one and lost power. I was in a downtown hotel at the time and woke up without power.

    The thing is large disasters are rare. You have very little chance of your data center getting hit by a hurricane, a tsunami as Japan recently had, a tornado such as the Northeast have experienced this year,  They happen regularly, but the chance of them hitting your data center is low. The chance of you “losing” a data center is low. It just doesn’t happen that often, and the complete loss of a data center might mean you have bigger things to worry about.

    However there are plenty of disasters that you are likely to experience. It’s possible that you could have a fire in your data center that affects the database server. Not likely, but it could happen. It’s more likely that you could have one of these:

    • an operator spill coffee on your server
    • someone trips over a power cord or fiber cable and brings down your system
    • a memory chip go bad and get in memory corruption.
    • it’s possible power goes out for some reason, even with a UPS.
    • It’s highly likely that a drive will fail that contains data you need.

    These are the types of disasters that you really have to prepare for, since they are possible, and even likely.

    What’s more likely are the “Whoops” disasters as I like to call them. It’s very likely that someone will import the wrong file, update all prices instead of one, or even more likely, a developer or DBA will run the wrong code on the wrong server. The most likely disaster is probably this one:

    drwhoops

     

    If the DBA hits “Execute” here, it’s a disaster. Not a big one, but if this is a critical table in a critical system, you might have an even bigger reaction from manangement than if a hurricane hit.

    You have to prepare for disasters, but don’t get caught up in worrying about the data center being destroyed. Those disasters are rare. Most of your preparation, your practice, your checks, have to be focused on the more likely disasters, which are often smaller in scale and focused on your database.

  • T-SQL Tuesday #18 – My CTE

    It’s time for T-SQL Tuesday again, and it’s number 18. Hard to believe it’s been a year and a half since Adam Machanic (blog | @AdamMachanic) thought of the idea. I’ve participated in most and it’s something I look forward to each month. This month Bob Pusateri hosts the party with the theme of CTEs. No, it’s not thermal unit of expansion, and I hated chemistry.

    My CTE

    When CTEs were introduced, I thought they were a great idea. They made it much easier to write complicated queries that might need derived tables. In the past, writing something like this was hard to read.

    SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
     FROM Production.Product p
      INNER JOIN ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                     FROM Production.ProductCostHistory ph
                       INNER JOIN Production.ProductInventory pri
                         ON ph.ProductID = pri.ProductID
                    WHERE StandardCost > 10
                 ) b
        ON p.ProductID = b.ProductID
      INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
      WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

    A CTE can make this much easier to keep track of, especially in places where you don’t want to create a view instead.

    WITH ProductCTE
    AS ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                     FROM Production.ProductCostHistory ph
                       INNER JOIN Production.ProductInventory pri
                         ON ph.ProductID = pri.ProductID
                    WHERE StandardCost > 10
    ) SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
     FROM Production.Product p
      INNER JOIN ProductCTE b
        ON p.ProductID = b.ProductID
      INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
      WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

    I know this isn’t a great example, but by moving subqueries to a CTE structure, the end query is easier to debug and read.

    Top X of a Group

    Suppose you have a small result set of something like this.

    CREATE TABLE Books
    ( BookID INT IDENTITY(1,1) , BookName VARCHAR(200) , Genre VARCHAR(50) , reads INT ) go INSERT Books SELECT 'Old Man''s War', 'Sci-Fi', 200
    INSERT Books SELECT 'Ender''s Game', 'Sci-Fi', 345
    INSERT Books SELECT 'Red Thunder', 'Sci-Fi', 143
    INSERT Books SELECT 'Quarter Share', 'Sci-Fi', 25
    INSERT books SELECT 'The Enemy', 'Thriller', 67
    INSERT books SELECT 'The Hunt for Red October', 'Thriller', 678
    INSERT books SELECT 'Bad Luck and Trouble', 'Thriller', 545
    INSERT books SELECT 'Game of Lions', 'History', 644
    INSERT books SELECT 'The Rise of Theodore Roosevelt ', 'History', 67
    INSERT books SELECT 'An American Life: The Autobiography', 'History', 267

    Suppose I wanted to top two books from each genre, ranked by reads. A TOP 2 won’t work because that doesn’t allow you to specify groups. However using ROW_NUMBER and an OVER clause in a CTE, this becomes an easy query.

    WITH BookRanks AS ( SELECT b.BookID
          , b.BookName
          , b.Genre
          , b.reads
          , ROW_NUMBER() OVER (PARTITION BY b.genre ORDER BY reads DESC) AS Counter FROM Books b
     ) SELECT bookID
          , Genre
          , reads
          , bookname
      from BookRanks
       WHERE counter <= 2

    That gives me an easy to read result set:

    bookID  Genre    reads bookname

    ——- ——– —– ———————————————————-

    8       History  644   Game of Lions

    10      History  267   An American Life: The Autobiography

    2       Sci-Fi   345   Ender’s Game

    1       Sci-Fi   200   Old Man’s War

    6       Thriller 678   The Hunt for Red October

    7       Thriller 545   Bad Luck and Trouble

    Older T-SQL Tuesday Topics

    Just a quick list of the past topics and the roundups.

  • T-SQL Tuesday #18 Coming Next Week

    Other than the week I hosted it, I’ve been caught off guard by T-SQL Tuesday most months. This time I caught Bob’s Twitter announcement about #18. The topic this month is CTEs, so you have a week to get things ready.