Voice of the DBA

Tag: syndicated

DATEADD Truncates the Number Parameter: #SQLNewBlogger
This was an interesting thing I saw in a Question of the Day submission. I hadn’t thought about the issue, but apparently DATEADD truncates values rather than rounding them. I’m not sure why that is the case, but it is.

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

The Scenario

Imagine that I have someone enter a value for the number of hours to include in a report. I enter 5 and the report divides this in half to go back 2.5 hours and forward 2.5 hours. I run this code at the top of my code block:
```
DECLARE @hours NUMERIC(4, 2) = 5;
DECLARE @start DATETIME, @end datetime
SET @start = DATEADD (hour, -@hours / 2, GETDATE ())
SET @end = DATEADD (hour, @hours / 2, GETDATE ())
```
Now, what do you think are the resulting start and end times? I’d assume this works and the function sorts out how much of an hour is .5 or .4 or whatever.

Here’s the interesting result. Look at the time interval in the end result.

It’s 4. I entered 5 hours, but I get 4 hours. I bet a lot of us would let this bug slip through as reading the datetimes we’d miss this wasn’t actually 5 hours.

Apparently DATEADD actually truncates a non-integer value. The parameter notes that the 2nd parameter, the number to add to the date value, resolves to an integer. It also notes that DATEAD truncates, not rounds, values that have a decimal fraction.

Those are two very important distinctions. That could result in calculations that are way off from what people expect if you are trying to include data in a query and you are trying to do parts of time. You might need to separately calculate all your different date/time parts.

If you need to do fractional work with dates, you can’t use DATEADD.

To me that seems lacy, but is it? Let me know.

SQL New Blogger

This is a short example of something that a person pointed out to me, and I never knew. I decided to make a quick test (the code above) and then write about this. I could have included other examples, or shown how this might mess up different situations in my code.

You could do the same thing in 30 minutes or less and point out an interesting piece of knowledge that your future employers might find interesting. They might even want to interview someone that learns things like this.
March 19, 2025
Can an AI Help Me Find a Job?

I’m not looking for a job, but I ran across an article about using AI tools for a job search. So I decided to try it out. I followed a couple of those prompts and get some results. Here is a set of things I did.

Salary Calculators

I ran this prompt from the article: What are some of the best salary calculators for a Data/SQL Server professional? Provide a list of your top 4 recommendations and the pros and cons of each one. Provide any helpful links. Format as a table.

The results? Mixed. Here is what I got from my local Deepseek model. Note, I didn’t get a table.

From Claude:

From Perplexity

Are these helpful? Well, I knew about GlassDoor and the BLS. Didn’t know Robert Half has a calculator. If I had done this a few years ago, I would have Googled this. When I did, I saw Payscale, Zippia, and CompTia. Ziprecruiter had hourly salaries, US News had a link. None of the ones in the table were there.

Both the Payscale link (Google) and Robert Half gave me similar results. Is the AI more helpful? It gave me a few things to think about, but it didn’t find some of the top Google links. Not sure what to think about that.

My Worth

I decided to ask this prompt: I am a senior database administrator with over 20 years of experience working with SQL Server in large and small companies, handling a variety of operational responsibilities. I have developed and implemented high availability solutions, automated server builds, understand DevOps style database development and GitOps management of servers. I have managed replication, built ETL pipelines, and implemented role-based access controls. What is the market value of my skill set?

The local model didn’t really help here. It gives me some generic things to think about and consider, and some ideas of where to look, but no real links.

Claude:

Perplexity:

Adding Skills

From claude:

And perplexity:

Those are interesting ideas. I’m sure I could follow this up and get more details on some of these and perhaps kickstart my learning. These are areas an AI might be helpful to help guide you or develop a plan. Certainly a friend might do this, but it’s tedious work. Getting some links, assembling a plan, etc.

Claude builds a detailed plan on the right side and describes it on the left The plan has a lot of links to documentation for tools below the phases:

Preplexity gives me a plan, with links as footnotes.

Negotiation

I used this prompt as is from the article: I am making $98,000 with 3 weeks of PTO now. I want to ask for an 8% raise and an additional week of PTO. I also want to work from home one more day a week, especially if I can’t get any extra PTO. What guidance do you have for making this request? What other rate negotiation best practices should I keep in mind?

Claude: a decent plan to get prepare.

Perplexity: very similar results

This isn’t a lot different from advice I’d give, but I wouldn’t write this all down. If someone told me this, I might forget some things. A plan is always good, and the AI has given me a decent plan. I might copy this and run it by friends, who are more likely to mark it up or add to it rather than build the whole thing.

It’s an assistant to getting started and a pretty good one.

A Cover Letter

These are a pain, and I always struggle to get one moving. Here are the results from this prompt: Given my experience, help me write a cover letter to apply for a DBA job

Claude: Again, an explanation on the left and a result I can start with on the right. It’s a bit wordy and AI-like, but I can adjust that.

Perplexity: Simpler, but effective.

Note, I didn’t paste anything else in, the AI remembered my experience from earlier in the chat. That’s way, way, way better than working through a search engine.

Summary

For most of us, (I hope) searching for a job is a rare thing. It’s always a pain, and it can be stressful. It’s also easy not to be organized and forget things. A GenAI can assist you with more patience than your friends or family might have. It can give you a good start, but please don’t just copy/paste this stuff or follow it blindly. Ask fellow humans what they think of these recommendations.

The GenAI models are trained on lots of data, and they can function as a sounding board, but they aren’t bringing creativity, and your answers will look a lot like other people’s answers. Lots of people will use an LLM to help them here, so make sure you tweak things to stand out a bit.

And build your brand, and let GenAI help you. Don’t let it write blog posts, but it can sketch things out that you edit and clean up. That editing and cleanup skill will help you in interviews and jobs, so don’t forget to polish the final product yourself. Let the GenAI be an assistant that gets you started and that’s it.

March 17, 2025
The Book of Redgate: What’s Great about Redgate?

“I’m sick of hearing about Red Gate.”

The first article in the book has this title, which might seem strange, but the short piece then talks about how many Redgaters, as we call ourselves, love working for the company and tell our friends how great a place this is to work.

The question it asks is why is Redgate great? It’s not the benefits, the gatherings, the fun things, the inside jokes. It’s not even the open, collaborative way or working, the no BS no politics attitude. It’s not anything that’s easy to put into words.

It’s really the culture, which is hard to describe. It’s like a family, which is similar to what I felt at J. D. Edwards as well. We have good and bad, we have disagreement and arguments, but overall we’re all in this together.

We’ve grown since then, and it’s a different place, but it’s still a great place to work and one that I hope I stay with until I retire.

I have a copy of the Book of Redgate from 2010. This was a book we produced internally about the company after 10 years in existence. At that time, I’d been there for about 3 years, and it was interesting to learn a some things about the company. This series of posts looks back at the Book of Redgate 15 years later.

March 14, 2025
Tally Table Alternatives: #SQLNewBlogger
We published an article recently at SQL Server Central on Tally Tables in Fabric from John Miner. In it he showed how this can be efficient. A day after he published it, he sent me an addendum to note that GENERATE_SERIES was available in Fabric and that could be used.

I ran a few tests last week, but as I read the comments on John’s article, I realized that there were 3 ways of setting up these tally tables that I’ve used and thought I’d summarize them a bit in this post. There’s a fourth way, but I haven’t used it.

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

Method 1 Using System Tables

The first method, which I saw Jeff Moden use many years ago involves reading from system tables. The code typically looks like this:
```
SELECT ROW_NUMBER () OVER (ORDER BY
                              (SELECT NULL))
FROM
   sys.all_columns ac1
   CROSS JOIN sys.all_columns ac2;
```
Since this table has 12000+ rows in it, the cross join is 12k * 12 k, which is a lot. The row_number() function gives you sequential numbers in a list.

This code works, but I can never remember which table and it does read from disk (or memory) to get the values. I suspect it’s slightly slower in lots of code than the other methods, but perhaps not enough to go and refactor old code.

Method 2 Using CTEs

The method I’ve liked to use is with CTEs. I have a SQL Prompt snippet set up with tt to give me this code.
```
WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
)
SELECT n
FROM myTally
```
This CTE has two 10 row “tables” that give me 100 rows (10*10). If I needed more, I can copy/paste the “cross join” line and change the b to a c and I’ve got 1000 rows. Repeat that until you don’t need more rows.

This is simple code, it’s in a snippet for me, and easy to expand. I’m not reading from anything and I can set the size as small or large as needed.

Method 3 Using GENERATE_SERIES

The last method is just a select from the GENERATE_SERIES() function. I can give it the number of rows, so this gives me 100 rows.
```
SELECT value FROM GENERATE_SERIES(1, 100, 1) AS gs;
```
I haven’t used this because I’m often on SQL 2019, not 2022, thought that likely should change.

In any case, this works well for getting a large number of rows, and has the advantage of me being able to set a starting point, so if 1 isn’t appropriate, I can start at 7 or 29 or anything else. I can also set a step to skip some numbers.

I like that this is less code and built in as a function, but only in SQL Server 2022+

Summary

I haven’t given any reason to pick any of these over the other. The post from last week shows that GENERATE_SERIES seems to be slightly faster, but that wasn’t really a comprehensive performance test. I like both method 2 and 3, and in modern version I’d lean towards using method 3 as it’s built in and less code.

I’ll do a performance test elsewhere and write a bit about GENERATE_SERIES and the options available.

SQL New Blogger

This post took me about ten minutes to write, as the code is simple and the longest part was really copy/pasting links and code from SSMS or articles. The rest was quick and easy.

This is a short post that can showcase your learning, and your thinking about different methods. I’ve given a few examples of that above.
March 12, 2025