Voice of the DBA

Tag: T-SQL

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

  • T-SQL Tuesday #136–The Datatype Blog

    tsqltuesdayIt’s that time of the month again, and this time it’s an interesting topic. The invitation is from Iceland, where Brent Ozar has relocated for the foreseeable future. I’m slightly jealous, and wish I could go visit. I enjoy winter, and the pictures he’s posted look amazing. Definitely a bucket list trip for me.

    However, this month, he’s asking about data types. Are there some you love or hate, and I’ve got a thought on this. In case you wonder, there is a list, broken into types. Apparently MS went into a “categorize everything” frenzy in the docs, which is OK, but I often don’t intuit the way they’ve broken things down. I wish they kept a long list on a page somewhere that was easy to find.

    Naming Confusion

    It has been deprecated, but the timestamp type is still around. It’s not in the list, but it is mentioned as a synonym for rowversion. This is a unique binary number in each database, which is often used to detect changes in a row. If you have two people editing a row, and a change updates a rowversion column, then each can detect if that value is different from the original one. Handy in terms of client side conflict resolution, which can prevent last-writer-wins scenarios for applications.

    I haven’t seen it used lately, but in the 90s and early 2000s, I often saw code that checked this before letting a user make an update in some data entry application. However, this was often a “timestamp” column, which was constantly confusing to me as a DBA or developer. I kept thinking I’d get some sort of datetime stamp in there, rather than a binary value.

    This shouldn’t be a problem in the future, as timestamp isn’t really doc’d, though timestamp can be found on Google searches. 

    The other reason I dislike this type is that we can’t change it to rowversion. An ALTER TABLE … ALTER COLUMN doesn’t work.

  • Logging Messages with Raiserror – #SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I recently ran across some people discussing how to log some information in a script. One person was using PRINT, which I often use for quick checks, but someone else noted the RAISERROR works well, and you can customize messages.

    For example, I can have this type of script:

    DECLARE @d VARCHAR(20);

    -- do stuff
    

    SELECT @d = CAST( SYSDATETIME() AS VARCHAR(20));
    
RAISERROR('%s - something happened at this time', 0, 1, @d)

    This allows me to add information into an error message. I can certainly construct @d with other stuff and then use that in PRINT, but I could get out of order messages. If  I add NOWAIT, I can ensure my messages get returned immediately.

    There are lots of options with RAISERROR, which I still use in place of THROW at times. While I like THROW, I think it doesn’t always give me the options I want for error handling, such as logging to the Windows lots.

    SQL NewBlogger

    When I saw this, I realized that I didn’t know, or remember, some of the ins and outs of RAISERROR, so I spent a few minutes looking through docs and playing with the code. I then wrote this quick post to help me remember a bit more.

    Short and quick is a good way to structure posts. I didn’t walk about all the options or ways I can use things. I’ll do some of that in another post.

  • 2020 Advent of Code–Day 3

    This series looks at the Advent of Code challenges.

    As one of my goals, I’m working through challenges. This post looks at day 3.

    Part 1

    Day 3 was tough. The explanation isn’t great, at least, I didn’t get it at first. Essentially you have a map, and then you have some slope. The first part has a slope of right 3, down 1. If you move, assuming the upper left is (1,1), the next spot is 2, 4. That’s if you count going down as positive.

    Here’s the map:

    ..##.......
    
#...#...#..
    
.#....#..#.
    
..#.#...#.#
    
.#...##..#.
    
..#.##.....
    
.#.#.#....#
    
.#........#
    
#.##...#...
    
#...##....#
    
.#..#...#.#

    If I count each space, then there is a period (.) in this (2,4) space. If it’s a tree, then there is a hash (#) there. We are trying to count trees before we hit bottom.

    The trick I missed is that the map we’ve given repeats. It repeats to the right as often as needed to get to the bottom.

    This is really a coordinate problem, counting each down as we move, and repeating the map. The trick often in a short width here is to do the math to wrap around from the right to left if you run out of room.

    In SQL, I used a loop. I didn’t spend a ton of time, but couldn’t see a good way to avoid this as I need this to be readable, and I need this to keep working through the map. Here’s the code:

    WHILE @currentrow <= @rows
      BEGIN
        SELECT @currentcol += @right
        IF @currentcol > @width 
          SELECT @currentcol = @currentcol - @width
        SELECT @currentrow += @down;

       SELECT @trees = @trees + CASE
           WHEN SUBSTRING(dataval, @currentcol, 1) = '#' THEN 1
           ELSE 0
        end
         FROM day3
         WHERE rowkey = @currentrow
      END
    
SELECT @trees AS TreeCount

    I move, count the value if there’s as tree, and then continue moving through the next rows. The WHERE clause orients the rows.

    It worked. I go the right answer here.

    Part 2

    In part 2, this changes to checking a number of sloops and then multiplying the results together. In terms of my code, this is really a repetitive way of running the code again. I could have used different variables and checked multiple slopes at once, but I was busy.

    In python, I did something similar. I essentially calculated the next X and Y position, and then looped through the file. Each time the Y matched the current row, I checked for the matching #. If it matched, increment.

    Once I matched the correct Y, I incremented X and Y.