SDU Tools: Check if an IP address is valid using T-SQL

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.

Find out more

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

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

Opinion: Start meetings ontime – "give it a few minutes" is rude to other attendees

I attend a lot of online meetings nowadays, and I can't tell you how often at meeting that starts at 10 AM actually ends up starting at 10:05 AM or 10:10 AM to cater for people who are running late. Right now I'm in yet another meeting that hasn't started yet, as we're just "giving it a few minutes for stragglers to join".

Now it's a different story if there is a specific person who really is needed in the meeting, and they've let you know they are running a few minutes late. But I see this as routine in pretty much every meeting I attend. Meetings almost never start at the correct time.

Don't do this!

All you are doing is being rude to, and wasting the time of, the people who did turn up at the right time.

I spent a lot of time coaching kids playing baseball, softball, and soccer, and it was the same thing. I always made it very clear to all the parents that practice sessions would start and finish on time. Anything else is just rude to the people who make the effort to be on time.

I understand that things come up and people will be late. Sure. But meetings should run for the people who met with the timings, not for the people who didn't.

 

T-SQL 101: #34 Formatting your scripts for readability

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.

Here's an example of the core elements of a pretty standard style:

  • T-SQL keywords capitalized i.e. SELECT
  • Database object names PascalCased i.e. ProductGroups
  • Local variables start with @ and PascalCased i.e. @Size
  • Constants capitalized with underscores separating words i.e. MAXIMUM_INTEREST_RATE
  • Blocks indented i.e.

One common variation is to use what's called snake_case instead of PascalCase. The words are all lower-case with underscores between them. I've seen arguments that snake_case is actually easier to read than PascalCase.

For indentation, I tend to work with four spaces as the Indent. Some People use tabs for this . There's a strong argument between People who love spaces and People who love tabs. It's not really something that I feel religious conviction about but I've heard it argued that having tabs instead of spaces has two benefits:

  • People can adjust the tab size to suit themselves
  • Tabs work better for people with visual disabilities, particularly with screen-reading tools.

Now there are many, many other standard rules, you might want to apply encoding, but these will get you started.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Seven – a mixed bag – vital energy but ghosts and omens

This is the eighth in a series of posts where I'm looking at how the Chinese view numbers, often from a superstitious basis.

Seven

The Chinese character for seven (Qī) is shown on the right hand side of the main image above.

The number seven is a bit of a mixed bag, in terms of positive and negative feelings.

Seven is considered good for relationships, particularly because it sounds like 起 (Qǐ) which means "to get up". It also sounds like 气 (Qì) which can mean just something like "gas" but it often means "spirit" or "vital energy".

On the downside, it also sounds like 欺 (Qī) which is a word for bullying, cheating, or swindling someone. In Cantonese, the word chāt sound like the word chat which is often used for "penis".

Again on the positive side, the 七夕节 (Qīxì jié) festival is a bit like a Chinese Valentine's day, yet again on the negative side, the seventh month is the one that Chinese believes has ghosts and spirits rising from hell. There is even a ghost festival.

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.

Snowflake for SQL Server users – Part 5 – Editions and Security Features

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.

Note: That was a great change when the SQL Server team did the same back in SQL Server 2016 SP1.

There is no free edition like we have with SQL Server Express. Similarly, there's no free option for developers like we have with SQL Server Developer Edition. That's not surprising though, as they aren't the cloud provider, they are purchasing services from cloud providers. I find that the Standard edition is pretty low cost though: you only pay a fairly low amount for storage, and you only pay for compute when you use it. So that's not too bad.

The main difference between Standard and Premier is that the latter comes with premier support. So that's not a bad distinction from say development tasks, to production tasks. I'd rather see that as just a single edition, with support an optional extra over all editions.

Snowflake has a feature called Time Travel. This allows you to see what data looked like at earlier times. It's a bit like temporal tables but also quite different to it. I'll talk more about it in another post.

Standard and Premier both have one day of time travel though, and Enterprise edition takes you up to 90 days. I like to see that sort of feature used as a differentiator between editions. It mostly wouldn't require code changes when working with different editions.

Business Critical basically introduces more security. It adds HIPAA and PCI compliance, and the ability to use customer-managed encryption keys. I can't say that I love the idea of core compliance as a distinction between editions. Everyone's data is important to them. Customer managed keys are a good edition differentiator though.

Snowflake data gets encrypted anyway, and with a key that changes each month (for new data). But on lower editions, it doesn't get re-keyed. What Business Critical also adds is annual key rotation. Data that's a year old gets decrypted and re-encrypted with a new key.

VPS or Virtual Private Snowflake is for people who can't tolerate the idea of any sort of shared Snowflake infrastructure. The Snowflake team do a completely separate deployment of the whole Snowflake stack, just for each customer. It's super expensive (I heard it starts at over $50M AUD) and so I can't imagine too many customers using it, but I'm sure there will be a few, including right here in Australia.

I heard that VPS was only available on AWS at the time of writing, but I'm sure that will change. And I'm guessing if you front up with $50M+, and say you want it on Azure, it's unlikely they'd say no.

 

 

SQL: Try to avoid unnecessary abbreviations when naming objects

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).

Today's post is just a simple plea to ask you that when you're naming things, to avoid abbreviations that aren't necessary.

I'll give you a few examples of what I mean.

EOMONTH

SQL Server 2012 introduced a new function EOMONTH. It's End of Month. I recently wrote about how it does more than just take a date and give you the end of the month for that date. If you missed the discussion, that's here.

But what I want to talk about today is why on earth it's not called ENDOFMONTH, or perhaps better, END_OF_MONTH.

What's the real value in saving three characters from ENDOFMONTH to make it EOMONTH anyway? Keep in mind that the same version of SQL Server introduced a function DATETIMEOFFSETFROMPARTS, so it wasn't just about saving keystrokes.

When I asked the product group, I was told that they copied the name of the function that's in Excel. I really don't like the idea that the name of new SQL Server functions would be based on what someone came up with in Excel a long time ago.

Database Objects

Anyone got any idea what aptrx is as a table name? If you've been around a while, you might guess that it's Accounts Payable Transactions. But is there really any need to intentionally obscure the database like this, and not use a name like AccountsPayable.Transactions?

I'm sure this originated on older systems where short names were all you could have. I've worked on ancient systems where a table name needed to fit in 6 characters. Even today, I think Oracle is limited to 30. But most sensible database systems allow for longer names if you need them. Object names in SQL Server are of datatype sysname which is currently mapped to nvarchar(128). Length really isn't a justification anymore, and most modern UI's write the names out for you anyway, so it's not even a typing issue most of the time.

Shortened names provide no real benefit and add to maintenance and support costs.

 

 

 

SDU Tools: Execute a T-SQL command in each SQL Server database

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.

It takes the following parameters:

@DatabasesToInclude nvarchar(max) – this is a comma-delimited list of databases or the value 'ALL'
@IncludeSystemDatabases bit – Should system databases be included? (Most of the time, for me, they aren't)
@CommandToExecute nvarchar(max) – the T-SQL command to execute (default is SELECT DB_NAME(), @@VERSION;)

Find out more

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

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

 

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.

It's not.

SQL Language

My favorite over the years has been the SQL language. I cannot tell you how many times I've heard about its imminent death, and how we need a modern alternative.

And yet, time after time, even the products that claimed they didn't need a SQL layer seem to end up adding one.

I think this comes from a type of brashness that I see in many new developers where they think that the output of their last day's thinking is somehow vastly superior to all the thinking that's gone on in the industry over decades.

Guess what? It usually isn't.

It was pretty funny watching products like MongoDB appear and claiming to not need all that pesky ACID transaction stuff. And after a few disasters from the lack of it, then add it into the product as a feature.

Wow, who knew that transactions and consistency actually mattered in most applications?

We aren't all writing blog post or chat applications.

It was also pretty funny to see NoSQL get redefined as Not Only SQL.

Modern Data Warehouses

I like to keep across most data-related trends and products, even if I don't plan to use them. I want to be able to discuss them sensibly and understand the pros and cons of each.

I spend some time every single day, learning about data-related products, applications, and languages.

Over many years, I've seen what works and what doesn't when it comes to data warehouses, and have lost count of how many shiny new things were going to fundamentally change how we work. The put-down is always that you want a modern data warehouse, not one of those old ones that required hard work.

Guess what? Building a good data warehouse is hard work.

As an example, the next time you hear someone tell you that you should just apply your analytics tools directly over your transactional systems, at least stop and ask yourself how that would be a good idea.

In other areas of your life, I'm sure you have a built-in detector for snake oil salesman. It needs to be applied to technology too.

Image from Clark Stanley (public domain)

Hadoop

Yesterday I was amused reading this article by Derrick Harris on What happened to Hadoop?

When Hadoop first appeared, I spent quite a bit of time checking it out. Then when the SQL Server team became enamoured with it and added HDInsight (their flavor of it), I spent even more time checking it out. I even did the Big Data certification in the Microsoft Professional Program that I wrote about recently.

And the more and more I looked into it, and the more and more that I played around with it, I kept feeling Is that it?

I kept feeling like I was taking part in an Emperor's New Clothes skit.

There were certainly possible use cases for it, but almost every time that I saw it being forced into a solution, it was the wrong tool. So why did the technical people involved want to push it in? Either they just wanted to learn about the shiny new thing to enhance their CVs, or somehow they were bowled over by the hype.

I remember writing blog posts back in 2013 asking if most use of Big Data was mostly just Big Hype instead.

Tools aren't better just because they're considered modern. Don't get lost in the hype.

 

 

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.

You might also want to use inline comments to just temporarily remove a line of code from a script. For example, if you have a SELECT query that isn't returning what you expect, you might just comment out the WHERE clause to see if that's what the problem is.

Learning T-SQL

It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Six – smooth or slick

This is the seventh in a series of posts where I'm looking at how the Chinese view numbers, often from a superstitious basis.

Six

The Chinese character for six (Liù) is shown on the right hand side of the main image above.

The number six is usually placed into the more lucky than not category in Chinese. Once again, that's because it sounds like another word. The word 流 (Liú) is usually translated as "flow". Mostly it's in relation to things like streams.

However, it's considered lucky in terms of good things flowing to you.

In Cantonese, the word lok is similar to another word lok that means happiness or good fortune, so it's even more positive there.

Curiously, sixes are treated quite differently in Chinese to how they're treated in the West. 666 would be very lucky in Chinese, but is "the sign of the beast" in Western (largely Christian) cultures.

Learning Mandarin

I'll write more soon on the best methods for learning. If you want to get a taste for it in the meantime though, my current favorite site is iTalki, and my favorite teacher by far is Amy He. If you decide to try it, click here and it's cheaper for both you and me.