Voice of the DBA

Category: Blog

  • Unlimited Vacation

    Unlimited Vacation sounds good, and maybe it works, but I wonder how much vacation people really take at Red Frog. My guess is that it’s not far off from what most progressive companies give, something like 20 days a year.

    It’s a good perk to have, and I’m sure it doesn’t get abused since the people that abuse it get let go. The people that don’t abuse it might even shortchange themselves. I know it sounds like a company would have people taking 40, 50, maybe 100 days a year off, but the reality is that if someone does that you find yourself in one of two situations:

    • their work isn’t getting done and you fire them
    • their work is getting done and you ignore it.

    There are plenty of managers that might feel they should be getting more work from someone that can take 100 days a year off and still get their work done, but what type of attitude is that? You hire someone, expect them to get xx things done in a year for yyy dollars. If they get xx things done in 2/3 year while the person next to them takes a year, why complain? If others complain, tell them to just get their work done quicker (or learn how to work more efficiently).

    I get over 20 days, plus holidays, and I struggle to take it. I’m essentially in the same boat as the Red Frog employees in that I manage my own schedule, and can take off whenever I want if my work is done. I rarely take sick days, since I can work at home when I’m sick. I have a grinding, daily job that requires regular effort, so I can’t plan on a month project, take off 2 days in the middle and catch up later. I essentially run a newspaper, every day.

    However I could take unlimited vacation. I could potentially get my work done in 3 days every week, keeping enough items scheduled, to take two days a week off. I’m just not sure that it would be vacation since I’d be stressed during those 3 days.

    If you are a professional, I think most places will work with you to get the vacation you need, whether it’s tracked, booked, managed, or not. The key, in my opinion, is to set a schedule that works for you, and a set amount of work that justifies your salary and makes both you and your employer happy.

  • T-SQL Tuesday #26 – Second Changes with Date/Time

    TSQL2sDay150x150

    I missed the very first T-SQL Tuesday, so when this month’s topic of second chances came up, I decided to write that one.

    If you are unsure of what T-SQL Tuesday is, follow the link to this month’s topic to get the rules and description and then write a blog post.

    Date/Time Challenges

    I picked an easy one, but one that I continue to see asked in the forums by people new to SQL Server. I suspect we’ll see less questions over time as more people take advantage of the new DATE and TIME datatypes in SQL Server 2008 and later, but maybe not. Lots of people are still sure that they need to keep those items together.

    In any case, have you ever seen sales data like this:

    OrderID     OrderDate               CustomerID  OrderAmount
    ———– ———————– ———– ————
    1           1982-05-19 06:31:48.950 1           579040.5070
    2           1994-11-27 17:14:41.790 2           348808.5860
    3           1972-11-08 17:40:01.170 3           758992.3650
    4           1972-05-31 01:19:05.530 4           779853.1990
    5           1994-12-22 10:40:57.410 5           666173.8040
    6           1974-04-03 01:42:29.490 6           134218.2330
    7           1976-06-22 15:21:18.910 7           322938.6950
    8           1953-08-05 23:00:34.620 8           14169.7580
    9           1971-08-16 22:33:28.970 9           586057.3820
    10          2002-03-28 13:08:00.420 10          632785.0760

    Here’s some DDL, you can create your own data, but here are a few rows:

    CREATE TABLE SalesOrders
( OrderID INT IDENTITY(1,1) , OrderDate DATETIME , CustomerID INT , OrderAmount NUMERIC(10, 4) ) INSERT SalesOrders (OrderDate, CustomerID, OrderAmount) VALUES ( '1982-05-19 06:31:48.950', 1, 579040.5070), ( '1994-11-27 17:14:41.790', 2, 348808.5860), ( '1972-11-08 17:40:01.170', 3, 758992.3650), ( '1972-05-31 01:19:05.530', 4, 779853.1990), ( '1994-12-22 10:40:57.410', 5, 666173.8040)

    If I want to get all the sales in May of 1972, I get query them all like this:

    SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE OrderDate > '1972/5/1' AND OrderDate <= '1972/5/31'

    I get 4 rows back. That’s something people often write when they get input from a user. A user has a start and end edit box, they enter “1972/5/1’” in the start box (or use a calendar picker) and then enter “1972/5/31” in the other. I’m using ISO dates to make this clear, though in the US it would normally display like “5/1/1972” and in the UK as “1/5/1972”.

    However, that isn’t quite correct. If I run this query:

    SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE MONTH(OrderDate) = 5
  AND YEAR(OrderDate) = 1972

    I actually get 6 rows. The data rows for May 1972 are:

    OrderDate               OrderAmount

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

    1972-05-31 01:19:05.530 779853.1990

    1972-05-13 09:26:52.590 676848.9700

    1972-05-28 21:05:28.840 923425.0510

    1972-05-07 10:59:09.930 266079.6480

    1972-05-01 04:21:01.250 464241.6480

    1972-05-31 01:19:05.530 779853.1990

    What’s happening?

    If you look at the OrderDates for May 31, you see two values that have a time of 1:19:05am. Those are excluded from the query, which has an end date of “1972/5/31”. Why? This query:

    SELECT CAST( '1972/5/31' AS DATETIME)

    shows why. It returns:

    1972-05-31 00:00:00.000

    That’s midnight between the 30th and 31st, which is before 1:19:05am. When a datetime value is converted in SQL Server, without a time component, it defaults to the beginning of the day. That works great for the start date, not so good for the end date.

    Fixing this

    There are two real fixes here. Well, maybe more. You can query on the month and year, but those functions can disrupt indexes, so I don’t recommend them. The two main fixes are:

    • add a time component
    • add a day

    The first fix is the addition of the last time of the day to your query.

    SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE OrderDate > '1972/5/1' AND OrderDate <= '1972/5/31 23:59:59.997PM'

    In some type of code, it looks more like this:

    DECLARE @end DATETIME SELECT @end = '1972/5/31' SELECT @end = @end + '23:59:59.997' SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE OrderDate > '1972/5/1' AND OrderDate <= @end

    Assume the first select is actually coming from the user.

    The second fix is to add a day, and actually query like this, which is what I’d recommend:

     SELECT OrderDate
, OrderAmount
 FROM SalesOrders
 WHERE OrderDate > '1972/5/1' AND OrderDate < '1972/6/1'

    In this case, instead of querying for May 31, we move to June 1 (the next day) and then change the <= to a < only. This gets all orders occurring up until the end of May 31, but before June 1.

    Easy fixes, but so often there’s code that doesn’t allow for the time component. Take a minute and check your reports, and be sure you aren’t underreporting any data to your clients or customers.

    Also be sure to check out the new datatypes in SQL Server 2008 and later:

  • DBAs in Denver

    I have a friend looking for a production DBA in Denver, so if you’re interested, email me at SQLServerCentral.

    They need a production DBA in the South Denver area, backups, config, query tuning, replication, partitioning. SQL Server 2005-2008 R2.

  • Congrats, Yanni

    Sunday was an exciting morning for me and quite a few others in the SQL Server community. My morning was interrupted constantly as I was tied to my Twitter stream, looking for updates like this one:

    One of the updates I caught during my morning

    I had puppy duty, dinner to cook that required a trip to the store and 8 hours of cooking. I had a few chores to handle like horses to feed, and I found myself taking longer to do those things than I should have because I was constantly checking my phone. I wasn’t the only one as I saw 6 or8 other people in the SQL community, all cheering Yanni on. I’m sure there were others outside my twitter feed as well.

    We were all sending tweets that the Runmeter app will read to you over headphones. I’ve used the app and it’s cool when someone sends you a note when you’re running, even if it interrupts the music.

    I probably sent 20-30 tweets to try and help cheer Yanni on during her first marathon. I met Yanni last year, and I’ve been following her journey as she climbed a mountain, ran a half marathon (with other SQL people), and made the decision to run just a marathon, but a marathon and a half in a week. As someone that’s run a marathon, I can appreciate her effort, accomplishment, and I’m impressed.

    I’m also thrilled to be a part of the SQL Server community, which in many ways seems to be a tighter set of groups of people. Groups because we aren’t one big happy family, but we are a series of smaller ones that support each other, cheer each other one, defend each other, and laugh with each other. The biggest reason that I attend events is to meet people and spend time with them.

    Yanni is never without a smile on her face, and I’m sure part of it is the wonderful people who work with SQL Server in their spare time, when they’re not making life a little bit more interesting to each other, 140 characters at a time.

    I congratulate Yanni, and I urge you to do the same, with a comment on her marathon post.

    Update: This is the post race post.