Sql-Server

When you calculate an aggregate, the default is that it applies to the entire table, but you might not want that.

For example, I might want to calculate the longest shelf life for products. But I want to calculate that for each size of product.

If we look at the first example, we have added a GROUP BY clause to our query, and it returns exactly that. The output is on the left hand side below the query. For each size (determined by the GROUP BY), the maximum of the shelf life is returned.

2021-03-01

I was pleased to get sent a copy of Benjamin Nevarez’s new book High Performance SQL Server. I’ve known Benjamin for a long time. He’s a very skilled SQL Server professional, and you’ll see him at conferences around the world. (Or at least once Covid is tackled more completely).

And the technical reviewer for the book is another very skilled old friend in Mark Broadbent. So my expectations were high for the new edition of this book.

2021-02-26

I had a question from Dale Kerr the other day about whether we had a tool in our SDU Tools collection, that calculated the day of the week (i.e. Tuesday, Thursday) for a given day of the year, across a range of years.

We don’t have a specific tool for that, but the a CTE returning the list of years makes it easy.

(UPDATE: SDU Tools version 21 will have WeekdayAcrossYears that implements this)

2021-02-25

Like most developers, I often need to check if a string value is a valid number or a valid date (or datetime). In T-SQL, the functions provided for this are ISNUMERIC and ISDATE. These are pretty generic functions though, and I almost never use them now. I believe you’re now much better off using TRY_CAST instead.

“Numeric” is a fairly vague concept. Which type of number are we checking for?

2021-02-23

I mentioned in a previous post that COUNT was an aggregate. The other common aggregates are shown in this table, and no surprise what they do.

SUM adds up or totals the values.

AVG calculates the average of the values.

MIN works out the minimum value.

MAX works out the maximum value.

But if you’ve started to think about how SQL Server works, you might be wondering about what happens with NULLs.

2021-02-22

A while back, I got a request to add a new function to our free SDU Tools for developers and DBAs. The user wanted to be able to find a particular day of the week, in a given week. So for example, if I give you a date of 30th April 2021, what is the Thursday in that week? So we’ve added a new function WeekdayOfSameWeek.

It takes two parameters:

2021-02-19

I’m a fan of SQL Server Express edition. It’s a fabulously functional free database that lets you work with up to 10GB of data per database. One aspect that’s commonly misunderstood though, is that it also supports a limited version of SQL Server Reporting Services.

There are many scenarios where Express edition is the perfect fit. The Microsoft sales and marketing teams have had an ongoing strange relationship with Express edition because it’s free. Much of the documentation tried to suggest that it was for hobbyists, and para-professionals. But Express edition has a great role to play in professional systems too. For example, if you needed a small database to run on a series of point of sale devices, it could well be the right answer, particularly if it’s then connected to one of the higher editions as a central SQL Server system.

2021-02-16

In previous posts, I looked at how to read data from a table. Now, we need to look at how we do calculations on the data in the table.

The most basic calculation we might do is to count the number of rows in the table. The first example above does that.

What about the asterisk?

But also notice that is has an asterisk in the query. Some people worry about the asterisk being in their queries as usually having an asterisk isn’t a good idea. In fact, some customers have automated systems for checking code, and the automated system might complain about the asterisk.

2021-02-15

Some time back, we added DateOfEasterSunday to our free SDU Tools for developers and DBAs. Given it was the Christian Easter Sunday, almost immediately, I got a request for the Greek Orthodox Easter. That date isn’t of course just the Greek one, so we’ve added a new function DateOfOrthodoxEaster.

It’s based on a concept from Antonios Chatzipavlis. Thanks !

It takes a single parameter:

@Year int - the year to find the date for

2021-02-12

In a previous post, I showed how to use CAST and CONVERT. What I didn’t mention before though, is what happens when the conversion will fail. If I try to convert the string ‘hello’ to an int, that just isn’t going to work. Of course, what does happen, is the statement returns an error.  Same thing happens if I try to convert the 30th February 2016 to a date. There aren’t 30 days in February. Again, an error will be returned.

2021-02-10