Tag: T-SQL

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.

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.

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

Dynamic Data Masking is a feature that provides some pseudo-security features. This lets you return a portion of data while hiding other portions for unauthorized users. The classic example is preventing someone from seeing PII data if they are a customer service rep or other non-privileged user.

Note: THIS IS NOT A SECURITY function, though it is somewhat marketed and talked about it this way. I say pseudo-security, but be careful here. I have a larger article on why.

A Scenario

I have a simple table here. I’ll give you some DDL and DML.

create table DDMEmailTest
( MyID int not null identity(1,1) constraint DDMEmailTestPK primary Key
, MyName varchar(100)
, Email varchar(100)
, Salary int)
go
insert DDMEmailTest select 'Steve Jones', 'steve.jones@sqlservercentral.com', 200
insert DDMEmailTest select 'Bob Jones', 'bob.jones@acme.com', 300

Now, if I query this as a normal user, I see something like the image below. Note I can read the email address.

Limiting Access

I can prevent this from occurring by adding Dynamic Data Masking to the column. This is a column level feature, which doesn’t need activating. It’s in SQL Server 2016+ databases. You add masking with an  ALTER COLUMN like shown below. The email() function is built into SQL Server.

alter table DDMEmailTest ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

When I now query the data, I see this:

Users that are not admins, or have been granted the UNMASK permission will get masked data. This mask specifically is the first character and then the XXX@XXXX.com value.

Use it if this fits your scenario.

SQLNewBlogger

I was working with DDM to show something to another person and decided to throw this post together. I’d set up the scenario, so I just had to write. This was about 15 minutes of my day.

You could do the same thing, but explain how you might use this in your organization.