Voice of the DBA

Tag: T-SQL

  • Basic Cursors in T-SQL–#SQLNewBlogger

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

    Cursors are not efficient, and not recommended for use in SQL Server/T-SQL. This is different from other platforms, so be sure you know how things work.

    There are places where cursors are useful, especially in one-off type situations. I recently had a situation, and typed “CREATE CURSOR”, which resulted in an error. This isn’t valid syntax, so I decided to write a quick post to remind myself what is valid.

    The Basic Syntax

    Instead of CREATE, a cursor uses DECLARE. The structure is unlike other DDL statements, which are action type name, as CREATE TABLE dbo.MyTable. Instead we have this:

    DECLARE cursorname CURSOR

    as in

    DECLARE myCursor CURSOR

    There is more that is needed here. This is just the opening. The rest of the structure is 

    DECLARE cursorname CURSOR [options] FOR select_statement

    You can see this in the docs, but essentially what we are doing is loading the result of a select statement into an object that we can then process row by row. We give the object a name and structure this with the DECLARE CURSOR FOR.

    I was recently working on the Advent of Code and Day 4 asks for some processing across  rows. As a result, I decided to try a cursor like this:

    DECLARE pcurs CURSOR FOR SELECT lineval FROM day4 ORDER BY linekey;

    The next steps are to now process the data in the cursor. We do this by fetching data from the cursor as required. I’ll build up the structure here starting with some housekeeping.

    In order to use the cursor, we need to open it. It’s good practice to then deallocate the objet at the end, so let’s set up this code:

    DECLARE pcurs CURSOR FOR SELECT lineval FROM day4 ORDER BY linekey;
    OPEN pcurs
    ...
    DEALLOCATE pcurs

    This gets us a clean structure if the code is re-run multiple times. Now, after the cursor is open, we fetch data from the cursor. Each column in the SELECT statement can be fetched from the cursor into a variable. Therefore, we also need to declare a variable.

    DECLARE pcurs CURSOR FOR SELECT lineval FROM day4 ORDER BY linekey;
    OPEN pcurs
    DECLARE @val varchar(1000);
    FETCH NEXT FROM pcurs into @val
    ...
    DEALLOCATE pcurs

    Usually we want to process all rows, so we loop through them. I’ll add a WHILE loop, and use the @@FETCH_STATUS variable. If this is 0, there are still rows in the cursor. If I hit the end of the cursor, a –1 is returned.

    DECLARE pcurs CURSOR FOR SELECT lineval FROM day4 ORDER BY linekey;
    OPEN pcurs
    DECLARE @val varchar(1000);
    FETCH NEXT FROM pcurs into @val
    WHILE @@FETCH_STATUS = 0
      BEGIN
       ...
       FETCH NEXT FROM pcurs into @val
       END
    DEALLOCATE pcurs

    Where the ellipsis is is where I can do other work, process the value, change it, anything I want to do in T-SQL. I do need to remember to get the next row in the loop.

    As I mentioned, cursors aren’t efficient and you should avoid them, but there are times when row processing is needed, and a cursor is a good solution to understand.

    SQLNewBlogger

    As soon as I realized my mistake in setting up the cursor, I knew some of my knowledge had deteriorated. I decided to take a few minutes and describe cursors and document syntax, mostly for myself.

    However, this is a way to show why you know something might not be used. You could write a post on replacing a cursor with a set based solution, or even show where performance is poor from a cursor.

  • No Scalars with JSON_QUERY–#SQLNewBlogger

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

    I started to dig into JSON queries recently, and as I continued to experiment with JSON, this struck me as strange. Why is there a NULL in the result?

    2020-12-04 14_43_02-SQLQuery3.sql - ARISTOTLE_SQL2017.Compare2 (ARISTOTLE_Steve (58))_ - Microsoft S

    The path looks right. This appears to be somewhere I ought to get a result back. As I looked up the JSON_QUERY documentation, and it says I get an object or array back. I’d somewhat expect that position, while containing a single value, could be seen as an object of

    {“setter”}

    The fact that I need to know I have a single value here seems like poor design. If the document changes, perhaps someone might enter this:

    DECLARE @json NVARCHAR(1000)
         = N'
      {  "player": {
                  "name" : "Sarah",
                  "position" : "setter, DS"
                 },
        "team":"varsity" 
      }
    
';

    In this case, a JSON_VALUE would fail, while a JSON_QUERY wouldn’t work in the first example above. This means that I need to modify my code based on documents.

    I don’t like this, but I need to know this, so if you work with JSON, make sure you know how the functions work.

    SQLNewBlogger

    While writing the previous post, I changed one of the function calls and got the NULL. I had to fix things for the other post, but I kept the query and then spent about 10 minutes writing this one to show a little thought into the language.

    You can easily take something you are confused about, made a mistake doing, or wonder about and write your own post.

  • Basic JSON Queries–#SQLNewBlogger

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

    Recently I saw Jason Horner do a presentation on JSON at a user group meeting. I’ve lightly looked at JSON in some detail, and I decided to experiment with this.

    Basic Querying of a Document

    A JSON document is text that contains key-value pairs, with colons used to separate them, and grouped with curly braces. Arrays are supported with brackets, values separated by commas, and everything that is text is quoted with double quotes.

    There are a few other rules, but that’s the basic structure. Things can next, and in SQL Server, we store the data a character data. So let’s create a document:

    DECLARE @json NVARCHAR(1000) = N'
    {
      "player": {
                 "name" : "Sarah",
                 "position" : "setter"
                }
      "team" : "varsity"
    }
    '

    This is a basic document, with two key values (player and team) and one set of additional keys (name and position) inside the first key.

    I can query this with the code:

    SELECT JSON_VALUE(@json, '$.player.name') AS PlayerName;

    This returns the scalar value from the document. In this case, I get “Sarah”, as shown here:

    2020-11-21 14_58_17-SQLQuery3.sql - ARISTOTLE_SQL2017.Compare2 (ARISTOTLE_Steve (58))_ - Microsoft S

    I need to get the path correct here for the value. Note that I start with a dot (.) as the root and then traverse the tree. A few other examples are shown in the image.

    2020-11-24 14_49_16-

    These show the paths to get to data in the document.

    In a future post, I’ll look in more detail how this works.

    SQLNewBlogger

    After watching the presentation, I decided to do a little research and experiment. I spent about 10 minutes playing with JSON and querying it, and then another 10 writing this post.

    This is a great example of picking up the beginnings of a new skill, and the start of a blog series that shows how I can work with this data.

  • Strange T-SQL Operator Syntax

    I can’t remember where I saw this, but it made an interesting Question of the Day:

    select *

    from Sales

    where Profit !< 10000;

    I had never seen anything like this, in all my years of working in C, C++, Java, Lisp, APL, Pascal, Fortran, VB, C#, SQL, and more. However, there are apparently a few operators that I’ve never used:

    • !<
    • !>

    These are the not less than and not greater than.

    Weird, though I guess this makes sense. Personally, I think restructuring as greater than or equal to instead of not less than makes perfect sense.