Voice of the DBA

Tag: SQLNewBlogger

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

I ran across a question recently on querying an XML document. While I think XML is a pain and it’s not the future, there is a lot of it out there that you might need to deal with in a database. Legacy stuff will be there for awhile.

In any case, someone was struggling with this code.
```
DECLARE @x XML = 

'<?xml version="1.0" encoding="UTF-8"?>
    <PartyID>
     <PartyID>147</PartyID>
     <CampaignID>
       <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>2</TicPosition>
     </CampaignID>
     <CampaignID>
       <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>13</TicPosition>
     </CampaignID>
   </PartyID>'
SELECT

Data.Col.value('(./PartyID)[1]', 'int') As Party_ID,

Data.Col.value('(./CampaignID)[1]' , 'int') As Campaign_ID,

Data.Col.value('(./Arc)[1]', 'varchar(1)') As Arc,

Data.Col.value('(./TicPosition)[1]', 'varchar(10)') As TicPosition

FROM @x.nodes('/PartyID/CampaignID') As Data(Col)
```
The person got results where the Party_ID was NULL. Some of you might get what’s wrong, but it’s a question of understanding your context.

In this case, the FROM clause helps us understand this. When we specify the node() method, we choose a path in the document. The path we pick is PartyID/CampaignID. This puts us here in the document:
```
    <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>2</TicPosition>
     </CampaignID>
     <CampaignID>
       <CampaignID>1</CampaignID>
       <Arc>A</Arc>
       <TicPosition>13</TicPosition>
     </CampaignID>
```
If we are trying to specify paths on the current position with the period (.), we can only see these values. There is no PartyID here.

However, similar to a folder navigation from the command line, if I use two periods (..), I move up one level. From here, I can get the PartyID. Therefore, my code is:

SQLNewBlogger

As soon as I saw this question, I knew the issue. It was a good reminder to me to watch the path, which is why I thought this was a good thing to post about. It cements this in my memory.

In 10 minutes, I did this, just as you could.
May 10, 2021
Basic OFFSET–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The other day I saw an article on the OFFSET clause in a SELECT. I had seen this come out and looked at it briefly in SQL Server 2012, but hadn’t done much with it.

NOTE: if you use this, be sure you read about potential performance problems and solutions.

The basic structure of this clause is that it is a part of the ORDER BY section of a query. After the column ordering, I can enter OFFSET and a value, which will skip those rows. I can optionally enter a number of rows to fetch.

The structure is:
```
<query>
ORDER BY col1, col2
OFFSET n ROWS FETCH NEXT 10 ROWS ONLY
```
This code:
```
WITH myTally(n)

AS

(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)

)

SELECT *

FROM myTally

ORDER BY n
```
Will get me numbers from 1 to 100, each in a separate row. A tally table, with partial results shown in this image.

If I change this, and add an OFFSET, I can skip some rows. For example, I can skip 7 rows by adding that clause, as shown below.

If I only want a certain number, say 6 rows, I add the FETCH clause.

This is useful for pagination, saving some network bandwidth, and less buffer space on the client. Not necessarily helping the query processor, but it does make it easy for developers and with small result sets (and source table sizes), this is nice.

It’s a fairly easy clause to use, but it can still require the full work on the server for looking through data, so be sure you read the link in the note above.

SQLNewBlogger

I was testing some code I’d seen from someone and it occurred to me to document the process a bit. I used a tally table, and wrote this around a couple of my experiments.

You can do this as well, show some learning, testing, understanding of code in ten minutes.
April 21, 2021
The PowerShell Basics If Statement–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is a fairly simple construct, but I keep looking up the syntax if I haven’t written anything for a couple of weeks, which does happen. I’m hoping this quick post will help me remember the structure.

Parenthesis and Braces

The general structure is simple. It’s like this:
```
if ($a -eq 1) {
# do something   
}
```
This structure has the test expression inside the parenthesis and then any statements to execute, one or more, inside braces. Fairly simple, as long as you remember the –eq, –gt, –lt, etc.

If you have an ELSE, then you add that next with the braces again.
```
if ($a -eq 1) {
# do something   
}
else {
# do something else
}
```
That is easy to remember, as long as you use one language. I’ve been working more with Python, which is where I think I get confused.

SQLNewBlogger

This was about the 5th or 6th time I looked up the syntax, so I stopped and wrote this. It took only about 10 minutes to do this, no need to do more than mock up code, but show how this works.
April 14, 2021
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.

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.
March 29, 2021