Voice of the DBA

Category: Blog

  • Daily Coping 31 Mar 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to walk a different route today and see what you notice.

    I don’t walk a lot of places. I tend to drive to town, and usually park near where I need to go (gym or store). However, I did decide to try something different when I saw this tip.

    I was going out to the barn to check on my wife. Usually this is out the garage, turn right, walk to the barn. This time I went out the front door, turned left, and looped around the house. I went around the riding arena in the back, looking at the state of that area after a winter. I checked out things that might need fixing, some things that might get thrown away, checked the fence to see if deer or coyotes (or my dogs) had damaged it.

    A nice, easy walk, and a different view of a simple thing I often do. Just not in this way.

  • Daily Coping 30 Mar 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to cultivate loving kindness towards others today.

    Patience, understanding, empathy. Those have been more important in my life during the pandemic of the last year. I think it’s been especially important during those days when I don’t feel particularly happy. Really, when I’m grumpy.

    A bit over a week ago there was an active shooter in Boulder, CO. My middle son goes to the University there, and while he’s not often on campus, he is there. The store was about a mile from campus, though I doubt my son has ever been to that store. Still, a slightly scary time as the campus locked down while he was there.

    I wasn’t too worried about him, but the incident shook my wife. I needed to show understanding and kindness towards her scared, sad, and other feelings that day. I reminded myself, after a year of pandemic living, that my view isn’t others, and I need to accept and work with the way they feel.

  • Basic Fetch and Offset Experiments–#SQLNewBlogger

    I’ve never used the FETCH or OFFSET commands for pagination, but I have heard of them. I ran across them recently and decided to experiment a bit.

    One note: I have seen notes about performance, so before you do more than experiment, read about the issues (SQLPerformance, Use the Index, Luke)

    This is part of the ORDER BY clause, and this allows you to skip a number of rows and then also only get a certain number of rows in the result set. The basic syntax is:

    … ORDER BY XX
     OFFSET YYY ROWS
    FETCH {FIRST|NEXT} ZZZ ROWS

    This means, if I have a query link this one, I get the first ten rows with a 0 offset.

    SELECT 
      f.FlightDate, f.DepartureAirport, f.DestinationAirport
      FROM dbo.Flight AS f
      ORDER BY f.FlightDate
      OFFSET 0 ROWS
      FETCH FIRST 10 ROWS ONLY

    If I want the next 10, I can change the offset to 10.

    SELECT 
      f.FlightDate, f.DepartureAirport, f.DestinationAirport
      FROM dbo.Flight AS f
      ORDER BY f.FlightDate
      OFFSET 10 ROWS
      FETCH NEXT 10 ROWS ONLY

    The OFFSET must proceed the FETCH, and OFFSET can be 0. If I want to make this page, I need to ensure I change the value for OFFSET to skip the rows already returned. I can use variables here:

    DECLARE @offset INT = 2
    , @fetch INT = 4;

    SELECT 
      f.FlightDate, f.DepartureAirport, f.DestinationAirport
      FROM dbo.Flight AS f
      ORDER BY f.FlightDate
      OFFSET @offset ROWS
      FETCH FIRST @fetch ROWS ONLY

    This gets me the 3rd through 6th rows in my dataset. I’ve included a vertical partition here to let me test without having to remember which rows are which.

    2021-03-22 14_36_31-SQLQuery1.sql - ARISTOTLE_SQL2017.way0utwest (ARISTOTLE_Steve (57))_ - Microsoft

    This is a really basic look at the native way for paging through data, though beware the entire query runs and then the engine filters out data. This may or not be a big performance issues, but on large amounts of data it will be.

    SQLNewBlogger

    A quick look at a feature I ran across. I needed to test code for someone and verify it works, which means I needed to take 10 minutes and try a few queries. This entire post took my about 15 minutes to write and it gives me ideas for other posts.

  • Daily Coping 29 Mar 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to if you find yourself rushing, make an effort to slow down a little.

    “Life moves pretty fast. If you don’t stop and look around once in a while, you could miss it. “ – Ferris Bueller

    I think life starts to move really fast for me some times. If there was one good thing during the early part of the pandemic it was that life slowed down a bit. I cooked more, relaxed with family, and enjoyed not having a schedule.

    That got a bit boring, and while things have opened a bit, and life is busy again, I’m trying to remember to slow down and relax.

    This year, it’s just my wife and I, and the house is a bit quieter. We have less commitments, overall, and I’m trying to take advantage of that. When I’ve had a busy time, and feel rushed, I’ve tried a couple things lately.

    First, I enjoy guitar, and I’ve tended to play a bit before bedtime, but I’ve started to bring one into the office and I’ll take 5-10 minutes of a break to strum a little, maybe learn a few song, and just reset myself.

    The other thing I’ve start to do is prep dinner early. No particular time, but often when I feel stressed or so busy that I can’t think, I’ll walk to the kitchen and start to chop vegetables or gather things for a few minutes. I don’t get too far along, but it let’s me slow down and relax.

    Exercise is always good, but that sometimes feels like more work, and more rushing rather than slowing down, so I’m trying to find new things that help me reset my stress or busy mind by slowing down.

    I am looking forward to warmer weather when I can walk outside and watch my wife teach a bit for a few minutes.