Voice of the DBA

Tag: T-SQL

Where are Sequences?–#SQLNewBlogger

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

I created a few sequence objects lately to test some things. Since I tended to create and use these, I knew the names. Coming back a few days later, with new queries, I wondered where these were stored. This post shows a few things I learned.

SSMS

Where would you think Sequences are stored. They are a weird object, with the NEXT VALUE FOR syntax being used. This feels DDL-ish. However, if we look at the Object Explorer, there aren’t a lot of choices here.

The area that seems most promising is Programmability, and when I check here, I see them.

If I look at the properties, I can see the settings, which is good. I especially like seeing the current value.

T-SQL

Since this is an object, it ought to be query-able from T-SQL. sys.objects is a good place to start, and when I query, I see this:

Good, but not a lot of information here. With SQLPrompt, I do see there is another choice, sys.sequences.

If I query this DMV, I now see the same information from sys.objects at the top of the result set.

If I scroll, I can see more of the metadata I am looking for. Last_used_value is important, though I wonder what caching does here. A post for another day.

ADS

Azure Data Studio has an Object Explorer, but it’s not as useful, as you can see. No options to get data. In ADS, I’d query the DMV.

SQLNewBlogger

This was a quick post, as I tried to find information. Once I did, which took just a minute, I decided to spend 5-10 minutes assembling this post and trying the various tools to get information.

A quick look at how I find the information. You could focus a small post in this way on some learning effort you make.

June 17, 2020
Creating a New Sequence–#SQLNewBlogger
I wrote about sequences in an editorial recently and decided to start using them. Creating one turned out to be surprisingly easy.

I had a table I’d been logging some data in, with a PK, but no natural key or identity bound to the table. Instead, this was low volume (1x per day) and I just manually adjusted the PK value.

I decided to create a sequence. As I did this, I found it interesting and consulted the BOL page for some ideas on the options.

The first part is simple. A name and datatype.
```
CREATE SEQUENCE dbo.MyKey as INT
```
That makes sense. Next, I need a starting value. In my case, I had 7 values in the table, so I added this:
```
START WITH 8
```
That gets me what I need. The last part I added was the INCREMENT BY clause. In my case, 1 works fine. My code:

The last query is how you get the next value. The NEXT VALUE FOR phrase can be used in various places, but that’s for another day.

If I needed to bind values, I could use the MINVALUE or MAXVALUE, which I’ll look at in a different post. I could also control caching and cycle values if I needed to.

SQLNewBlogger

I actually started a post on binding this to a column and then decided to add this short post in about 5 minutes.
June 3, 2020
Using WAITFOR to Test Slow Code–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One thing I needed to do lately was test how a calling program would respond to slow code. I decided to use a normal query, but add a WAITFOR() to simulate different delay levels. However, I had to look up the syntax for the call and I decided to blog about it.

WAITFOR DELAY

The WAITFOR call isn’t a function. I’ve written about that before. Instead, this is listed as a control-of-flow element in the language.

I have a rough idea of how this works. I use
```
WAITFOR DELAY
```
as the basic syntax. SQL Prompt helps me here, since looking this up is a pain and slow. I know there is a time element in here, but I always worry I’ll pick the wrong time and then sit for a long time testing.

The key is this format: hh:mm[[:ss].mss]

To delay for 2 minutes, I write this:
```
WAITFOR DELAY '00:02:00'
```
That gives me what I need, and using a string variable, I can control this delay programmatically.

SQLNewBlogger

This is a good example of learning a piece of code and writing about it. Short, simple, show how this one works in T-SQL. Add the why, show some code, and results. How you use it, and what you learned.

This was a 10 minute post.
May 27, 2020
TSQL for Beginners–#RGCommunityCircle
As a part of the Redgate Community Circle, giving back in this strange time, each of the advocates for Redgate is teaching a free course. We’re doing this live each week, according to this schedule:
- Mondays – Intro to SSRS with Kathi Kellenberger
- Tuesdays – Query Performance Tuning with Grant Fritchey
- Wednesdays – Basic T-SQL with Kendra Little
- Thursdays – Live Stairways, DAX and Power BI with me.
I decided to watch a class of each of the others’ work and write about it. This post looks at the first class from Kendra Little on basic T-SQL.

Basic T-SQL

I know a lot of T-SQL, but I also don’t know a lot. I’m good, maybe above average, but not a great query writer. I learn all the time from others, and often find something interesting in basic presentations. I’m also always looking to see how others teach basic topics to improve my own skills.

Kendra has done an interesting thing with putting her course on GitHub. That’s a great idea, though for mine, I am following articles on SQLServerCentral. Having a good outline and resources for students is a good idea, and everyone ought to be able to use a VCS, including GitHub, so I like putting pages here and code in a repo.

Why is that good? This is what I did to start following Kendra’s class:

Now I can open the first script in ADS. On top of seeing how Kendra teaches, I get to practice some ADS stuff.

I like the doorstop at the end, and I’ll have to think about that for my demo scripts. I’ve accidentally run the entire script too many times.

Kendra walks through a number of query concepts with a series of basic SELECT statements, including an emoji for a column alias. I’m glad she lets you know which items are not recommended.

The first course is helping you understand some of the ways in which you can get data from tables. You do need to have a slight understanding of what a table is, but Kendra helps you learn some of the strange ways that you can put together a statement. There is a lot to cover, but this should help someone that wants to learn some basics get started.

There is also homework, included as part of the scripts in the course. The questions are at the bottom, and called out. A good way to get you to practice. I should do this for my work in places as well.

Pass this course along to someone that needs to get better at T-SQL. It’s not too fast or hard, and will let them be sure they have a grounding in good ways to write queries.
May 8, 2020