Voice of the DBA

Tag: SQLNewBlogger

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
Custom Quarantine PoSh Prompt
Since I’ve been quarantined at home and working in my office, every day is the same. I can’t go to the gym, or coach, or go to the restaurant, or much of anything. As a result, I regularly, forget what day it is.

Yes, it’s on my phone, but that’s not always open and visible. It’s on my watch, but I have to swing up my wrist and hope it lights up. Or reorient my wrist to see it.

I have Rainmeter running, which is nice, and I have a summary in the upper corner of my screen, but often I have something covering this.

The time is in the toolbar, but I sometimes hide toolbars to record things. Basically, I forget what day it is and I’m looking for a few hints I can use to make this easier. As I play with PowerShell regularly, I thought it would be good to update my prompt slightly.

Plus, it would be good PoSh practice.

Setting a profile. I’ve seen a few posts on customizing the prompt, and they all say I need a function, called prompt, in my profile. I found this article, which gave me a good idea. Put the time in the prompt.

I then had to figure out how to get to my profie. It turns out “notepad $PROFILE” in a PoSh prompt will get you there. If you don’t have a profile, you’ll see this:

Say yes, and then add some code. I had to look up the Format options and experiment a bit to decide how I wanted things to look.
```
function prompt {
        $lastResult = Invoke-Expression '$?'
        if (!$lastResult) {
                Write-Host "Last command exited with error status." -ForegroundColor Red
        }
        Write-Output "${msg}$(
                # Show time as 12:05PM and day of week
                Get-Date -Format "dddd HH:mm"
                # Show current directory
        ) $(Get-Location)> "  
}
```
I saved this and now when I get a new PoSh prompt, I get this:

Now I easily know the day and time when I’m working in a PowerShell terminal. This also shows up in my Visual Studio Code terminal as well, since that’s just a PoSh environment.

SQLNewBlogger

This was a quick thing I did in about 5 minutes to customize my environment, and help me be more productive. A happy employee works smoother.

I decided to show off how I did this and spend a quick 10 minutes writing down and documenting what I did.
May 29, 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
Connecting to SSAS–#SQLNewBlogger

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

In a previous post, I installed SSAS on a machine. Now I need to connect to it. This post covers a quick, how do I do that.

Do connect in SSMS, you can use the Object Explorer. If you click the down arrow on the Connect button, you’ll see choices.

If you pick SSAS, you get a similar connection dialog. The SSAS instance is named the same as your base instance. In this case, I had a named instance, so I enter the same name as I’d use for the database engine.

There’s not much to see, but I’m connected. My installation is working.

I could also connect with a query window. The toolbar has a couple choices.

For MDX:

For DAX:

For either one of these, the connection process is the same.

SQLNewBlogger

After the post where I installed SSAS, I needed to test it. Rather than add that to the post, I ran through the steps, grabbed sceenshots, and then wrote this post as a second one.

Focus on one thing, not multiple things, in a post.

May 20, 2020