Voice of the DBA

Tag: SQLNewBlogger

Getting a Day Difference in PowerShell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

SSMS froze on me the other day. Actually, it lightly responded to some things, but the window wouldn’t redraw and I couldn’t see the query window. I could see the results pane, but couldn’t get the app to respond.

I wanted to get the difference between two dates, and wasn’t sure, so I quickly searched. I tried assigning the date to a variable, but this creates a string:
```
$start = “2020-03-11”
```
With a couple searches, I learned I can use Get-Date to get a date variable. In this case, I’d do this:
```
$start = Get-Date -Date "2020-03-11"
```
If I did that with two dates, I could get the difference. Here’s a screen show that shows I get the result in a variety of different time slices.

If I wanted just days, I could do this:
```
($end - $start).Days
```
That returns just the 712.

I also learned I could shortcut this with a TimeSpan type.

New-TimeSpan -Start “2020-03-11” -End (Get-Date)

I get the same spread of time parts as the image above, or I can enclose this all in parenthesis and then call the “Days” property to get that value.

SQL NewBlogger

I hadn’t done much with date and time in PoSh, and after seeing an article from an author, I investigated a bit more. This was a part of what I tried to do, albeit as a response to something not working as expected.

Good to know how to work with dates, as I can see this being a part of many PoSh scripts that might clean up old files or otherwise take action based on time values.

You could write this post in about 10-15 minutes and show how you use PoSh to work with date and times.
February 21, 2022
Checking if a database has a master key–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve been working with encryption in SQL Server for a long time, and have delivered quite a few presentations on the topic. Recently I was updating some code and wanted to check if a database had a master key created in it. This post shows how to do that.

The DMK (Database Master Key) is a construct that lives inside a database and provides the basis for encrypting other keys. It is a symmetric key, but created with the CREATE MASTER KEY DDL.

Information about this key is stored in a couple of places. First, it appears in sys.symmetric_keys, with the name “##MS_DatabaseMasterKey##”. You hsould see this with the AES_256 algorithm.

You can also query the sys.databases DMV for the is_master_key_encrypted_by_server c0lumn, if you keep the defaults. If you run this
```
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
```
then the sys.databases DMV will show 0, even though you still have a master key, as shown below.

SQLNewBlogger

A quick post. I was updating code to make it cleaner and realized I needed to add a check for the key. In the past, I’ve just ignored the error, but I took the chance here to refactor things and also produce a quick post.
January 26, 2022
Creating a new User-Defined Data Type–#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 on user-defined data types, which I hadn’t worked with in a long time, so I took a minute to investigate. I wrote this post about an interesting language item.

User-defined table types let you add a new type that can be used anywhere you would use a base, or normal, data type. This means if I want to have a type of US zip codes, perhaps limiting the base zip to 5 numbers, I can create a type that is limited to 5 digits.

If I want to create a new user-defined table type, I’d have thought I did something like this:
```
CREATE TYPE dbo.USZipCode AS VARCHAR(5)
```
However that doesn’t work.

The AS structure is used in many places, but not here. Instead, we use a FROM structure. This means I’d do this:
```
CREATE TYPE dbo.USZipCode FROM VARCHAR(5)
```
This gives me a type I can use in CREATE TABLE statements, stored procedures, and more. Anywhere I’d use the varchar(5), I could do this instead:
```
CREATE TABLE dbo.AddressTable

( AddressID INT NOT NULL

, AddressValue VARCHAR(100)

, AddressZip USZipCode

)
```
This let’s me use a type that is more intuitive, I guess. I don’t find these that useful in most places. In fact, it’s a little confusing. If I were a new developer, is this a 5 or 9 (or 10) digit field? Is it numeric or string? It’s not easy to determine this. I don’t find these that useful.

SQL New Blogger

I was doing other work, but I saved a bit of code and then spent about 10-15 minutes to write up this post. This one shows less about what I learned, and more about what I think.

Always good to show to a prospective interviewer.
January 19, 2022
A Little Current Date Arithmetic in Powershell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a fun post on Twitter recently asking days until retirement. I wrote this code:
```
DECLARE @YearsToRetire INT = 11;

SELECT DATEDIFF (DAY, GETDATE (), (DATEADD (YEAR, @YearsToRetire, GETDATE ())));
```
I thought that wasn’t bad, but then I wondered, how would I do this in PowerShell? I knew there had to be a way, so I googled and ran into this article. That showed me Get-Date and the AddYears() method. So I tried this:

That didn’t work well. I ended up storing the date in a variable and then things worked. However, I was bothered. I can pipe and combine things, why didn’t this work?

Then I thought about something. I ought to enclose the Get-Date inside something. I tried parens, like this:

(Get-Date).AddYears(11)

That worked great. As I get more familiar with PoSh, I can figure more things out myself, and quicker.

SQLNewBlogger

Once I figured out the way to do this, I spent about 5 minutes on this post. Actually 6 minutes with this part added.

I took something I figure out and wrote this up quickly. I even added a great line at the end, that I figured it out and my work with the language is paying dividends.

A great little type of post that would highlight your blog.
January 12, 2022