Voice of the DBA

Tag: T-SQL

T-SQL Tuesday #149–Advice about T-SQL to a Younger Me

It’s that time of the month again, when we have the T-SQL Tuesday blog party. This month we have a new host, Camilia Henrique with an invitation on advice you would give to your younger self, but in the area of T-SQL. That’s a good one, and it focuses on a technical skill that many data professionals need.

Participating in T-SQL Tuesday is a good way to practice your writing, your skills, and show your thoughts on some aspect of your job. Even if you miss the party one month, feel free to write a post later and link it to the main post on the T-SQL Tuesday site.

Practice, Practice, Practice

I learned SQL first as someone working with dBase, FoxPro, and Clipper. I moved to SQL Server and T-SQL later, with a basic grounding on how to query for simple sets of data, but I quickly learned performance can matter a lot.

As I’ve tried to keep up with the language, one of the things that has struck me is that T-SQL can be complex to structure because it’s relatively simple with few keywords. You can unintentionally create cross joins or row-by-row (RBAR) structures that perform poorly and waste resources on your system.

I’ve found that practicing with programming exercises and solving problems to be the best way to improve my skills. I don’t do it enough these days, and when I answer questions on SQLServerCentral, invariably one of the experts there will post a solution that performs better and I learn something new.

If I were talking to myself, I’d say to look more deeply into APPLY and the OVER() clause right now. Make sure you understand tally tables and their uses, and work through exercises like the Advent of Code, Exorcism, Project Euler, or something similar. Heck, just answer questions in a forum for yourself, with your own solution.

Learn how to create queries that can efficiently gather together, filter, and aggregate your data in ways that are helpful for clients without taxing server hardware. In the era of cloud computing and pay for data moved or data processed somehow, this is an invaluable skill.

April 12, 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
Finding the First Day of the Year–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

While working on the question from Monday, I had to do a bit of date math. I remember this blog post from Lynn Pettis, and every new year I think of it.

I decided to decode the question a bit and work through the T-SQL myself as a good exercise for explaining what happens.

Here’s the code (setup and query):
```
CREATE TABLE dbo.Resolution
( ResolutionDate DATETIME
, ResolutionText VARCHAR(200)
)
GO
INSERT dbo.Resolution
(
     ResolutionDate,
     ResolutionText
)
VALUES
   ('2020-01-01 0:00', 'Do not travel by airplane this year'),
   ('2021-01-01 0:00', 'Go on vacation on a plane'),
   ('2022-01-01 0:00', 'Visit a new country')
GO
SELECT ResolutionText FROM  dbo.Resolution
WHERE ResolutionDate = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) , 0)
```
In this code, the final query is designed to find the first day of the current year. Here are a few examples:

How does this work? Let’s decode things.

Digging Into the Algorithm

Let’s start with a simple thing. I use a 0 for a parameter in the DATEADD and DATEDIFF. What does that mean? Well, let’s go with the YEAR() function. If I use a 0 there, I see the base year in SQL Server, which is 1900.

This doesn’t mean I can’t use other years, but this is the basis for calculations. What if I add to this? I can add one, and I see a different date.

This is the key. I’ve gone from 0 to 1901-01-01-00:00:00. Let’s see the difference from this year, well last year when I wrote this, to 0.

The result above shows me 121. Which makes sense. 1900 to 2021 is 121 years. Now, when I use the dateadd, and add 121 to 0, I get the first day, actually the first DATETIME moment, of the current year.

I get 2021-01-01 00:00:00.

I can change the GETDATE() to any date time of any year, and this code returns the first moment of that year, essentially stripping off the other parts.

SQLNewBlogger

I was working on something and used a trick I learned from someone else. I decided to write this post, which only took about 15 minutes to write. The demo was simple, and I just broke apart the code, slowly putting each section in its own SELECT and then explaining it.

This is a good example of how to structure a blog post based on some knowledge you have and use in other work. You should try this.
January 5, 2022