The Bit Bucket

Opinion - Modern isn't a synonym for Better

I’ve been in the IT industry a long time. I see trends come and go. (Mostly they go). At this point, I think I’m an OK judge of what’s going to fly and what isn’t. (Far from perfect but OK).

One thing that always puzzles me though is the way the word Modern is used as a put-down for things that aren’t modern, as though it’s a synonym for the word Better.

2019-09-03

T-SQL 101: 33 Adding comments to your T-SQL scripts

It’s really important when you’re writing SQL Server code (or T-SQL in particular) that you add comments to the code where something isn’t obvious to someone who’s reading it.

Here’s an example of comments being used:

There are two ways that comments can be added:

If you put a double-dash on a line, anything after it is a comment.

If you need to comment a block of code, you can put a /* to start the comment and */ to end it. I’m not a fan of block comments in T-SQL because you can’t nest one comment inside another comment. With the double-dash method, that’s not a problem.

2019-09-02

Snowflake for SQL Server users - Part 4 - T-Shirt Sizing

I mentioned in my last post in this series, that the Compute layer of Snowflake is basically made up of a series of Virtual Warehouses (VWs).  Each VW is an MPP (massively parallel processing) compute cluster that can comprise one or more compute nodes.

The number of nodes in the compute cluster is called its “size” and the sizing options are made to resemble T-Shirt sizing, as you can see in the main image above.

2019-08-30

SQL: Think that T-SQL EOMONTH() just returns the end of month? Think again!

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

SDU Tools: Calculate number of days in a month using T-SQL

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

Opinion: Make sure monitoring isn't causing your SQL Server performance issue

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

T-SQL 101: 32 Repeating T-SQL batches with GO n

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

SDU_FileSplit - Free utility for splitting CSV and other text files in Windows

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

Snowflake for SQL Server users - Part 3 - Core Architecture

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

SDU Tools: Create SQL Server Login with SID from a database

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