Daily Coping 22 Apr 2021

Posted on April 22, 2021 by way0utwest

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 explore a new area today, however small.

I’m lucky in that I live on land and have been able to go outside for the last year if I need to get out of the office. I’m also lucky in the Colorado has great weather and is built around being outside in many areas, so that even while locked down, I could find places to visit or walk without being near people.

I like exploring, but really I like driving and exploring. I’ve loved having cars throughout my life and the freedom of driving somewhere. I decided to use a car to do a little exploring.

With some extra time, rather than drive straight to the gym, I looped around the North and East, going through some neighborhoods that used to be fairly deserted and sparse, but now had been developed and built up more. I missed the fields, but saw new houses, families enjoying themselves, and a changing world.

It was a nice break for me, short at 15-20 minutes, but a change from the routine of life, which is needed these days.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 22 Apr 2021

Basic OFFSET–#SQLNewBlogger

Posted on April 21, 2021 by way0utwest

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.

2021-04-19 13_56_15-SQLQuery5.sql - ARISTOTLE.DMDemo_5_Prod (ARISTOTLE_Steve (61))_ - Microsoft SQL

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.

2021-04-19 13_58_58-SQLQuery5.sql - ARISTOTLE.DMDemo_5_Prod (ARISTOTLE_Steve (61))_ - Microsoft SQL

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

2021-04-19 13_59_40-SQLQuery5.sql - ARISTOTLE.DMDemo_5_Prod (ARISTOTLE_Steve (61))_ - Microsoft SQL

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.

Posted in Blog | Tagged , , | 1 Comment

Daily Coping 21 Apr 2021

Posted on April 21, 2021 by way0utwest

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 get active by singing today (even if you can’t)

I can’t sing. My wife and kids say I do fine if we’re not actually trying to hit the notes. However, I love music. My mobile devices have been important for me as music players with other features being secondary. I still miss my iOS devices a bit as they were superior music players.

Today, I went old school on Spotify, a Classic Rock Workout, playing loud in my office while I do a little busy work. I can semi-listen and sing along with a few songs as well. A few of the ones that get me going:

  • Gimme Shelter
  • Come Together
  • Layla
  • Baba O’Riley
  • Purple Haze

You can guess the artists, or check out the playlist.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 21 Apr 2021

Daily Coping 20 Apr 2021

Posted on April 20, 2021 by way0utwest

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 relax today with some yoga, tai chi, meditation or a quiet walk.

My choice is yoga. I try to do this 3-4 times a week, some in the gym, some at home. The gym is getting busier, so I’m doing a bit more at home. My usual choice is Yoga with Tim, who I’m enjoyed taking some medium level workouts from over the year. My wife got me started, and he has a few nice 30 day challenges to get you going.

However, a friend got me started on Yoga with Adriene, which is more renewing, meditative, and relaxing to me. Not as hard a workout, but refreshing. I decided to pick up a video today and take it easy.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 20 Apr 2021