Azure-Sql-Db

In our free SDU Tools for developers and DBAs, we have a large number of useful functions. The topic for today is two simple ones: StartOfMonth and EndOfMonth.

SQL Server 2012 added a function EOMONTH. It returns the end of the month date for any given date. I really, really don’t like abbreviations like this. There is no need to save three characters.

I asked why it wasn’t ENDOFMONTH but was told that EOMONTH was the name of the function in Excel. It’s sad to think that’s the guiding principle for T-SQL function names. I also don’t get why sometimes the words in the names are separated by underscores, and other times they aren’t. Surely END_OF_MONTH would be more readable.

2019-04-03

Blocking is a popular topic in SQL Server. Users are often trying to work out what’s blocking what. In our free SDU Tools for developers and DBAs, we have a tool to make that very easy to see. It’s called ShowCurrentBlocking.

ShowCurrentBlocking takes one following parameter:

@DatabaseName (sysname) is the database to report on

It returns two sets of data.

The first rowset is a list of all objects that are currently blocked, and the last query that was run on the connection for each of those objects.

2019-03-28

In our free SDU Tools for developers and DBAs, we have a number of functions and procedures that help you work with tables and indexes. One question that often arises is about how big tables are. Our tool ListUserTableSizes helps with that.

SQL Server Management Studio (SSMS) has a report that shows you user table sizes, but people often want a list of sizes that they can get programmatically, perhaps for a monitoring tool, or some sort of automation.

2019-03-20

In our free SDU Tools for developers and DBAs, a number of the string formatting functions have been quite popular, and we keep getting requests for even more.

Today’s post highlights another two of these. TrainCase is words with the first letters capitalized, then separated by underscores. KebabCase has dashes as separators. It’s named because it looks like a kebab.

You can see them (and some others) in action in the main image above, and in this video:

2019-03-13

In our free SDU Tools for developers and DBAs, a number of the string formatting functions have been quite popular, and we keep getting requests for even more.

Today’s post highlights two of these. Screaming Snake Case is words in capitals separated by underscores. Sponge Bob Snake Case has underscore separated words (hence the “snake case” part) with alternating capitals. (It’s just for fun).

You can see them (and some others) in action in the main image above, and in this video:

2019-03-06

On occasions, I need to work with XML data in SQL Server T-SQL and one problem is that I need to be able to encode and decode strings, using the XML encoding standards. For example, my strings might include % characters or < or > characters, etc.

So we added a pair of tools (XMLEncodeString and XMLDecodeString) our free SDU Tools for developers and DBAs.

You can see them in action in the main image above, and in this video:

2019-02-27

In a previous SDU Tools post, I described how to calculate the columns required for a date dimension. Another related tool that we have in our free SDU Tools for developers and DBAs, is a function that creates the columns needed for a time period dimension. It’s called TimePeriodDimensionColumns.

You supply the starting time, and the number of minutes per time period,  and it supplies the output columns.

You can see it in action in the main image above, and in this video:

2019-02-20

Calculating the columns required for a date dimension using T-SQL is a pretty common request from SQL Server developers. So we decided to make it really easy to do. In our free SDU Tools for developers and DBAs, there is a table-valued function called DateDimensionColumns, for just this purpose.

You supply a date, and the month that your financial year starts in, and it supplies the output columns.

You can also easily combine it with our DatesBetween function to get dimension columns for a range of dates:

2019-02-13

To celebrate Chinese New Year this week, I thought I should write about some options that we recently added to our free SDU Tools for developers and DBAs, for working with Chinese calendar concepts.

Let’s start with the basic one: when is Chinese New Year? We added a function called DateOfChineseNewYear. You just supply our year number (Gregorian calendar), and it will tell you when Chinese New Year is.

You can see it in use in the image above, along with the much more cute function that tells you what the Chinese Zodiac animal is for the year. It’s called ChineseNewYearAnimalName.

2019-02-08

In our free SDU Tools for developers and DBAs, we’ve added a lot of scripting tools. To script out Windows authenticated logins (as opposed to SQL logins), we have ScriptWindowsLogins.

You can see how to use it in the main image above. It’s a function, that takes a list of the logins to script. You can pass the value ALL or a comma-delimited list.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

2019-02-06