Azure-Sql-Db

There are a few things in IT that seem to rarely change. One is the use of CSV (comma-separated value) files. It’s a pretty basic file format and sometimes we see other delimiters like tabs (aka TSV files) or pipe symbols but these types of files are still everywhere.

So it’s hardly surprising that people want to work with them in T-SQL as well. In our free SDU Tools for developers and DBAs, we added two functions to help: NumberOfTokens and ExtractToken.

2019-09-25

When I’ve reviewing customer databases, I often come across tables that have been created by users, yet they are completely empty. Now that might be perfectly OK but sometimes, it’s a mistake that should be cleaned up.

So, in our free SDU Tools for developers and DBAs, we added a tool that can look for user tables that are empty. No surprise, it’s called ListEmptyTables.

It takes three parameters:

2019-09-18

Every now and again, I need to store IP address values in T-SQL and I want to check if the string that I’m passed is a valid IP address. So, in our free SDU Tools for developers and DBAs, we added a simple tool that works that out. It’s called IsIPv4Address.

You can tell by the name that it only works with IPv4 addresses, not IPv6.

Nothing complex. It takes a string, checks the format, and the range of octet values, and returns its verdict.

2019-09-11

I regularly run into situations where I need to execute a T-SQL command in each database on a server. The built-in Microsoft method is to call the unsupported sp_MSforeachdb, and there have been other methods over the years. None of them really worked the way that I wanted them to, so in our free SDU Tools for developers and DBAs, we added a tool that does just that. It’s called ExecuteCommandInEachDB.

2019-09-04

When I’m working with dates, I often need to calculate how many days there are in a specific date.

So, in our free SDU Tools for developers and DBAs, we added a simple tool that does just that. It’s called DaysInMonth.

It takes one parameter:

@Date date - the date to check

Nothing complex. It takes a date, and returns the number of days in the month that contains that date.

2019-08-28

In SQL Server, both logins (access to the server) and users (access to a database) have a name and a security ID (SID). This leads to problem situations where names might match but SIDs don’t match.

Common Problem

I’ve lost count of the number of times I’ve seen a user restore a database from another server, and then realize that the SQL Server login they need wasn’t present. Then, they create a new login and end up in a lousy situation, because the new login’s SID doesn’t match the SID of the user in the database.  I call this a mismatched SID issue.

2019-08-21

If you ask SQL Server developers how to calculate the length of a string in T-SQL, most would tell you immediately that you need to use the LEN() function. What most don’t realize though, is that it doesn’t actually work properly.

The T-SQL LEN() function ignores any trailing spaces in a string.

That’s not how a string length function should work, or how it works in most languages. So, in our free SDU Tools for developers and DBAs, we added a simple tool that works properly. It’s called StringLength.

2019-08-15

In my last SDU Tools post, I described a tool for calculating date dimension columns. While dates are often enough, in many data warehouses, you also need to allocate time periods across each day as well. To make that very easy, in our free SDU Tools for developers and DBAs, we added a tool called TimePeriodDimensionColumns.

This tool is a table-valued function that takes a time (actual time of the day), and the length of each time period, in minutes. In the example in the main image above, I’ve asked for 8:34PM and I’ve said that each period is 15 minutes long.

2019-08-07

When you’re building a data warehouse using SQL Server, one of the first things that most people start to design is a Date dimension. To make that really, really easy, in our free SDU Tools for developers and DBAs, we added a tool called DateDimensionColumns.

This tool is a table-valued function that takes a date, and returns a set of columns that many will find enough for a good Date dimension. You can see the list of returned columns in the main image above.

2019-07-31

When you look at a SQL Server build number (actually a “Product Version”, you’ll see they have a format like this:

14.0.3256.1

The first number is the major version number. The second number is the minor version number. The third number is the build number. The fourth number is the release number.

These numbers are a little awkward to work with directly in T-SQL so, in our free SDU Tools for developers and DBAs, we added a set of tools that can do that for you.

2019-07-24