The Bit Bucket

In a recent post, I wrote how the T-SQL TRIM() function was more than just an LTRIM(RTRIM()) and that it took me a while to realize that. Well today, I found another one that I hadn’t noticed before: EOMONTH.

I was writing a SELECT clause for a query, when the Intellisense popped up:

And suddenly I noticed “Param2” and wondered what on earth the second parameter was.

Now you might notice that the Intellisense isn’t very helpful on this. It gives you no hint what the parameters are. In fact, for many of the features added to T-SQL in SQL Server 2012, the Intellisense is really poor. Here’s another example:

2019-08-29

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

There’s a well-known effect that’s often described in physics as the Observer effect. The argument is that whenever you measure things, you invariably alter them. The simple example given in Wikipedia is that it’s pretty hard to check the pressure of a tire (tyre) on a car, without letting at least some air out.

The same effect also happens in IT systems. A simple example is that to observe something, you might add logging or auditing, and the work to output those slows down the primary work that you are doing.

2019-08-27

In my last T-SQL 101 post, I mentioned that GO was just a word that’s used to separate batches. In fact, it’s not a SQL word at all. If you actually send the word GO to the server, it wouldn’t know what you’re talking about.

I mentioned that the word is only understood by the program that you type the script into. SSMS also allows us to make use of another additional trick in relation to GO.

2019-08-26

When I was doing some Snowflake training recently, one of the students in the class asked what utility they should use on Windows for splitting a large file into sections. They wanted to split files for better bulk loading performance, to be able to use all available threads.

On Linux systems, the split command works fine but the best that most people came up with on Windows was to use Powershell. That’s a fine answer for some people, but not for everyone.

2019-08-23

The first thing to understand about Snowflake is that it has a very layered approach. And the layers are quite independent, including how they scale.

Cloud Provider Services

The lowest level isn’t part of Snowflake; it’s the services that are provided by the underlying cloud provider. As a cloud native application, Snowflake is designed to use services from the cloud provider that they are deployed to, rather than providing all the services themselves. At present, that means AWS or Microsoft Azure. Deployment on Google’s cloud platform is in preview at this time.

2019-08-22

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

I might be a bit anal about this but I really dislike clutter all over my computer’s screen desktop. You’ll notice above that I keep it empty. If there’s something on my desktop, it’s something that I’m working on really briefly. If I need to do a presentation, I might have a single folder called Desktop that I’ll sweep anything on the actual desktop into.

There are two things that drive me crazy in terms of desktop clutter though:

2019-08-20

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