Tag: syndicated

  • Come Shake My Hand at PASS

    Last year Andy Warren thought about trying to help first timer attendees at the PASS Summit with a little networking event. Two years ago he brought Don Gabor in for a networking pre-con that was very popular, and wanted to try something new. So we rented Lowell’s out down at Pikes Place Market and invited people down. We weren’t sure how it would turn out, but we decided to risk some of our money and see.

    It was a smashing success, with dozens, maybe even a hundred people there. I got to meet quite a few people for the first time, which always makes me smile. I am sure the same thing happened to many other people and the networking was great.

    We decided to do it again, and we’ve reserved Lowell’s again from 6pm-9pm on Monday, October 10, 2011. Anyone is welcome, and it’s a short walk from the convention center down to the famous Pikes Place Market.

    Register today if you’re coming

    We ask that you register so we can plan the staff with the restaurant. It doesn’t cost anything, but you are responsible for your own food and beverage bill.

    Come say hi, meet some new people, and shake my hand. I’d be happy to meet you.

    We are sponsoring this as part of The Mentoring Experiment.

  • Computed Columns and CASE

    I wrote about computed columns recently, but I didn’t realize that a logical expression could be used here, such as a case statement, and misspoke in this thread on SSC. Fortunately someone corrected me and I decided to blog and test this a bit.

    Why an expression?

    Suppose you had a table like this:

    CREATE TABLE OrderDetail
    ( OrderID INT
    , ProductID INT
    , Qty INT
    , OrderDate DATETIME
    , ShipDate DATETIME
    , STATUS INT
    )
    

    I record orders, but I don’t necessarily have a ship date when the order is placed. There are many cases where a user enters an order and some back end system later calculates a shipdate based on the supply situation, or even waits to update this field when the order ships. It’s entirely possible I have data in this table like this:

    computecol2

    Suppose I then have a status table with these values:

    computecol3

    Here is my logic. If the shipdate is null, then the order has not shipped and is in the status of ordered. If the shipdate is filled in, the order is shipped. If the shipdate is null, and the order date is more than 2 weeks old, the order is late. In our example, if the date is 11/5/2011, then the order #4 is late.

    We can easily join to the Status table to display this for the client, but we are depending on some process running every day to check for late orders. Otherwise how does row 4 get marked as late? When the order is entered, it’s not late, and we certainly don’t want to wait until the client checks on their order status to mark it as late.

    If we had a computed column, we could easily handle this, and display late orders for a report, or even for a other queries. I could change the definition of the status column to be this:

    CREATE TABLE OrderDetail
    ( OrderID INT
    , ProductID INT
    , Qty INT
    , OrderDate DATETIME
    , ShipDate DATETIME
    , STATUS AS CASE
           WHEN shipdate is NULL AND orderdate < DATEADD( dd, -7, GETDATE()) THEN 3 
           WHEN shipdate is NOT NULL THEN 2 
           ELSE 1
       end
     )
    GO

    Now the status I get is based on the other values in the row and is correct, regardless of my application logic.

    NOTE: This is a contrived example, and I don’t like this since I am assuming the status logic doesn’t change. In a real system, I would probably prefer to use the actual status in the column if I did this, as shown here:

    CREATE TABLE OrderDetail
    ( OrderID INT
    , ProductID INT
    , Qty INT
    , OrderDate DATETIME
    , ShipDate DATETIME
    , STATUS AS CASE
           WHEN shipdate is NULL AND orderdate < DATEADD( dd, -7, GETDATE()) THEN 'Late' 
           WHEN shipdate is NOT NULL THEN 'Shipped'
           ELSE 'Ordered'
       end
     )
    

    A Better Example

    A better example of where this might be used would be in the case where I might have a variable calculation. For example, in many Internet businesses in the US, you do not collect sales tax if your product is not being sold inside the state in which your company is located.

    If I sold horse products from my ranch, located in CO, I would have to collect sales tax for sales shipped to CO, but not those shipped elsewhere. So perhaps I’d have a table like this:

    CREATE TABLE OrderDetail
    ( OrderID INT
    , ProductID INT
    , Qty INT
    , Price NUMERIC( 10, 2)
    , St CHAR(2)
    , TaxRate NUMERIC( 12, 4)
    , LineTotal AS CASE
           WHEN st = 'CO' THEN (Qty * price) + (Qty * price * TaxRate) 
           ELSE (Qty * price)
       end
     )
    GO
    INSERT dbo.OrderDetail
            ( OrderID ,
              ProductID ,
              Qty ,
              Price ,
              St ,
              TaxRate
            )
    VALUES  ( 1 , -- OrderID - int
              1 , -- ProductID - int
              10 , -- Qty - int
              10 , -- Price - numeric
              'AZ' , -- St - char(2)
              .1  -- TaxRate - numeric
            )
    INSERT dbo.OrderDetail
            ( OrderID ,
              ProductID ,
              Qty ,
              Price ,
              St ,
              TaxRate
            )
    VALUES  ( 1 , -- OrderID - int
              1 , -- ProductID - int
              10 , -- Qty - int
              10 , -- Price - numeric
              'CO' , -- St - char(2)
              .1  -- TaxRate - numeric
            )
    
    
    SELECT OrderID, qty, Price, St, TaxRate, LineTotal
     FROM dbo.OrderDetail
     

    Useful?

    I’m not sure if computed columns are terribly useful. To me they strike of hard coding logic into a schema that I’m not sure belongs, but if you find them useful, you do have the option of doing so, and including logical expressions with CASE.

  • Help with testing Denali, SQL Server 11

    Earlier this week, Dan Jones, one of the Microsoft employees on the SQL Server team, wrote a blog post asking about testing the next version of SQL Server, Denali. Dan is trying to ensure that there are not surprises from customers because of a bug that wasn’t caught in testing.

    I know, I know, how can Microsoft, with billions of dollars being spent on this product miss a bug. It’s easy, coordination is hard, and despite the hundreds, or thousands of machines testing SQL Server, it’s not possible to hit every combination. People stress the software in all kinds of unusual ways, and it’s good to get some testing.

    So, if you have some free time at work, and can space some hardware, even a VM, get get the CTP and install it. Upgrade one of your databases, and run a workload. Even a profiler trace and see if things error out. Run the new SSMS tools and report any issues on Connect. As Dan notes, Connect sends bugs right into the Team Foundation Server that the SQL Server developers use.

    There’s even a Feedback Challenge, where you can win a prize if you get moving now.

    If you’re worried about installing Denali, there’s a nice post from Andrew Fryer on installation.

  • Feedback from SQL Saturday #64 in Baton Rouge

    I did my session on The Top Ten SQL Server Skills You Need for the first time in Baton Rouge at SQL Saturday #64. This was my first time doing this session, which I hate doing at a large event. I had hoped to get to a user group and get this scheduled, but I didn’t have time or the freedom to get it scheduled in a busy summer.

    So I went through a dozen practice sessions and hoped for the best. Once again I was packing in a lot of information into an hour, really 55 minutes, and moving quickly.

    The session was busy, with the auditorium holding about 100 people. I went through the list, and made it through on time. With a clock on the wall, I could watch and keep myself to about 5 minutes per section. I’m blogging about the ten things over time, so most of the info will get out here, and the deck is available from the event site.

    I ended up with 71 evaluations, which was amazing. Usually I get much less, but with this event at a college, and with a beginner session, perhaps that isn’t surprising. The ratings across all categories averaged above 4, and an overall rating of 4.7. I guess I did OK, and am looking forward to presenting this at the PASS Summit in October.

    Once again, not many comments, but a few constructive ones:

    • Maybe more time per slide to make better notes – I can understand this, though I did not at the beginning that I was moving fast and the deck would be available. Perhaps I need to mention this a few times during the session for latecomers or to reinforce this.
    • I was expecting something more in-depth – This is a hard one. It’s always a battle to try and get enough content in there and cover it in depth. One of the things that one hour sessions don’t do well is go very in depth into a topic unless it is very narrowly focused. I’ll have to think about what I could change here, but I’m not sure I can do a lot. I was mostly trying to trigger the idea that each of these skills is something you need to work on and use in your job.
    • Speaker volume dips from time to time – Usually I’m a loud talker, but it was a large room and I was trying to turn from side to side. Have to keep this in the front of my mind as something to watch out for.

    The rest of the comments were very positive, which is nice and helps make it worthwhile.

    I’ve enjoyed my three years in Baton Rouge and looking forward to going back next year if I can.