Sql-Server

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

While it might be obvious that it’s important to format your T-SQL code for readability, it might be less obvious that there’s no agreed standard for how to format that code.

Everybody has their own style. The main thing. People will tell you all the time is just to be consistent. But then they’ll tell you they don’t like the format you’ve used.

Take a reasonable style and then to just keep applying it.

2019-09-09

Like most products, Snowflake comes in a number of editions, and you can see the current editions in the main image above. (Keep in mind that they could always change at any time and to check their site for the current options).

First thing I need to say is that I really like the way that most of the SQL code surface is pretty much identical across editions. I wish that was complete coverage but it currently doesn’t include materialized views.

2019-09-06

There’s an old joke in computing about how you can spend 90% of the time on a project working out what to name things, and end up without time for doing the work.

Phil Karlton is credited with having said: There are only two hard problems in Computer Science: cache invalidation and naming things.

I really liked Jeff Atwood’s or Leon Bambrick’s update though: There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors. (Can’t work out who said it first).

2019-09-05

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

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

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

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