The Bit Bucket

The image below shows a T-SQL script.

It’s an example of contents of a file that you could have on your disk so the entire thing could be called MyProgram.sql. Inside this script file, we’ve got a few different types of things happening here.

The word script applies to all of the contents of the file.

You can see the word GO there a number of times. It’s a special word that’s not actually a T-SQL keyword. It’s a batch separator. It only has meaning to the tool you’re using to execute queries, like SQL Server Management Studio or Azure Data Studio.

2019-08-19

Over the last few months, one of my Kiwi buddies (and fellow member of both the MVP and Microsoft Regional Director programs) Reza Rad has been organizing a bunch of us to write a book that’s a collection of ideas from a number of MVPs. It’s the Power BI MVP Book.

There are a whole lot of authors from a whole lot of different countries: Reza Rad, Anil Maharjan, Indira Bandari, Liam Bastick, Ken Puls, Jesus Gil, Thomas LeBlanc, Ike Ellis, Matt Allington, Leila Etaati, Markus Ehrenmüller, Ashraf Ghonaim, Eduardo Castro, Manohar Punna, Treb Gatte, Gilbert Quevauvilliers, Michael Johnson, Shree Khanal, Asgeir Gunnarsson, Greg Low, Gogula Aryalingam.

2019-08-16

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 recent years, I’ve done a lot of work in software houses (Microsoft calls them ISVs or Independent Software Vendors). Many of these software houses have worked out that they won’t be able to just keep selling their on-premises applications because their customers are asking for cloud-based solutions.

And more importantly, the customers want the software houses to manage the applications rather than themselves. So, many of the software houses start trying to turn their on-premises applications into Software as a Service (SaaS) applications.

2019-08-15

I’ve seen a lot of discussion lately about diversity and inclusion, and rightly so. It’s incredibly important. But there are two points that I want to draw attention to. The first is that language matters. And the second is that we shouldn’t be generalizing about people, based upon either their physical characteristics, or their race, religion, sexual preferences, etc.

Language that singles out people based on these things is regularly called out. But one that’s often missed is age.

2019-08-13

Take a look at the following query:

I’ve asked for a list of the databases from the sys.databases view. But rather than executing it against whichever database I was already connected to, I’ve said USE master; to change to the master database.

The command will be sent to the master database instead of any other database that I might have been connected to. At that point, master becomes my “current database”.

2019-08-12

A few months back, I started noticing that many of our clients had started to mention Snowflake.

In recent years, I’ve been in lots of planning and architectural meetings where there was already a presumption that AWS was being used rather than Azure. I put that down to a great selling job by the AWS people who got corporate IT folk locked into large enterprise agreements early. And so no matter what the technical question is, the answer will be something that runs on AWS.

2019-08-09

I’ve got a number of clients in the superannuation (aka retirement fund) industry. At many of these sites, there is a need to be able to do this:

• Using a web site, members must be able to see their own data pretty much 24x7
• Some latency is ok i.e. it might be ok for a member to see data up to yesterday, or up a point in time a few hours ago
• Refreshing the website data is not incremental, truncating the tables and reloading them is required

The challenge is: what happens if a member looks at the data during the time it’s being refreshed?

2019-08-08

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

Over the years, I’ve been noticing which apps users love and which ones they don’t like, or even hate. And I’ve decided that one of the biggest indicators of this is about how well they tolerate mistakes by users.

Users tend to learn to use apps in three ways:

• They watch or read some instructional material (this is actually the least common)
• Someone else shows them how to use the app (this is really common)
• They just experiment with the app (this is actually the most common)

How well do your apps allow people to experiment?

2019-08-06