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.
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
In our free SDU Tools for developers and DBAs, we have a lot of procedures and functions for checking system and database configuration details. One that we were asked for a function for, was the ability to check if the LockPagesInMemory (LPIM) setting was enabled, so we added that.
The function is called IsLockPagesInMemoryEnabled.
It takes no parameters, and returns a bit to indicate if the value is enabled or not.
2021-02-05
I was doing some consulting recently and reviewing a developer’s code. I was surprised to find extensive use of global temporary tables. Let me start by saying this: global temporary tables are almost never what you should be using.
When I asked why he’d used them, he told me that he wanted the temporary tables that he created in his procedure, to be available to other procedures that were run from within the procedure. So in PROC-A, he was running PROC-B and PROC-C. A temporary table was created in PROC-A and he wanted to be able to use it in PROC-B and PROC-C.
2021-02-04
Sometimes we need to determine whether a string is a date or whether it is a number.
In the first example above, I’m asking if the string ‘20190229’ is a valid date. You can see from the response (0) that it isn’t. That’s because even though it’s a valid date format, February in 2019 doesn’t have a 29th day. It’s not a leap year.
The value returned from the ISDATE function is a zero or a 1. Curiously, the return value is of data type int. You’d think that a function that starts with Is and tests something would return a bit data type instead. But that’s just one of the curiosities of T-SQL.
2021-02-01
I’ve had a lot of good feedback about the options that we’ve provided in our free SDU Tools for developers and DBAs for formatting strings. Someone recently asked for a format with all capitals and dashes in between. I realised we didn’t have that, and we’ve added it. Generally, in the industry, this is referred to as Cobol Case.
So, we added a function CobolCase.
It takes one parameter:
2021-01-29
We all use IN when writing queries:
SELECT ProductID, ProductName, Color, Size
FROM dbo.Products
WHERE Size IN ('370ml', '220ml');
A challenge comes up though, when you want to find pairs of values using IN. For example, if I have the following pairs of values:
Size Color 370ml Blue 370ml Red 220ml Blue
How do I find those when using IN?
Other databases do allow you to have pairs:
SELECT ProductID, ProductName, Color, Size
FROM dbo.Products
WHERE (Size, Color)
IN (('370ml', 'Blue'), ('370ml', 'Red'), ('220ml', 'Blue'));
I wish T-SQL had that option but it doesn’t. It would be particularly useful when those values in the IN clause are coming from a sub-query.
2021-01-26