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:
@InputString - the string to be formatted
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
If you are using a string that was provided by another system, it might not be in the format that you were hoping it would be. So for example, if I have this string that says 2/29/2016, I know that’s a US date format, but if it was only 5/4/2016, there’s no way I could just easily tell that.
Conversion functions use your session settings to determine how to convert the values. When I’m converting strings to dates (and/or times), I certainly don’t want to change my settings so that I run with US configuration.
2021-01-25
I spend a lot of time doing data conversion work. It’s hard to imagine how much of that relates to working with dates and times. A requirement that I had recently was to find the nearest weekday to a given date i.e. find me the closest Thursday to a supplied date.
The NearestWeekday function in our free SDU Tools for developers and DBAs, does this.
It takes two parameters:
@TargetDate date - the date that we’re aiming for @DayOfWeek int - Sunday = 1, Monday = 2, Tuesday = 3, etc. (values from 1 to 7)
2021-01-22
Louis Davidson is an old friend of mine. He’s an awesome Data Platform MVP, a database architect and a prolific author. Louis was on an earlier podcast and I was so pleased to invite him back on the show.
SQL Down Under show 81 features Louis discussing his new book, technical writing, relational DB design, and graph DB applications. Louis’ new book is this huge 1154 page volume:
You’ll find the podcast with Louis (and all our other podcasts) here: SQL Down Under Podcast
2021-01-19
I wish SQL Server had more metadata associated with columns in tables, and with objects in general. The closest thing we have to that is the use of extended properties.
In other databases that I’ve worked with, there are richer properties held for each column. For example in Progress, as well as the normal data type and nullability, there were options like:
and much more. This type of thing drastically cut down the amount of time it took to build applications using the database, and provided a great level of consistency.
2021-01-19
In a previous post about CAST and CONVERT, I mentioned that when you use CAST, you don’t get the option to specify the format you want to use. SQL Server offers an extension to the SQL standard with CONVERT and it lets you do just that.
CONVERT has an option parameter that lets you apply a style to the data type conversion that you’re doing.
In the example shown above, I’m converting the current date and time (based upon the SYSDATETIME() function) to a varchar(8), using style 112.
2021-01-18