Learning Mandarin: Eight – the luckiest number of all

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

Eight

The Chinese character for eight (Bā) is shown on the right hand side of the main image above.

The number eight is by far, the luckiest number.

It's luck comes from it's similar sound to 发(Fā) which is commonly used in 发财(Fā cái) which relates to good fortune. You'll hear it in New Year greetings with 恭喜发财 (Gōngxǐ fācái).

A double 8 looks like a character that's used to denote immense joy.

You'll see 8's everywhere in Asian communities because of this. You'll have a much easier time selling a house number 8 to a Chinese couple. Many airline flights from other countries and destined for China have many 8's in them.

The best example of how seriously this is taken is that when the Olympics was held in Beijing, it started on the 8th of August in 2008, at 8 minutes and 8 seconds past 8PM.

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 6 – Role Based Security

Security in Snowflake is quite different to what's implemented in SQL Server.

In SQL Server, objects have owners that can either be specific people, or as occurs when a dbo person creates an object, it is owned by the dbo role. In Snowflake, objects in the database also have owners, but the owners can't be users, they are always roles.

When you create a database, there are four pre-defined roles:

  • ACCOUNTADMIN
  • SECURITYADMIN
  • SYSADMIN
  • PUBLIC

The ACCOUNTADMIN role is the closest to what we have in SQL Server with the sysadmin role. The SYSADMIN role in Snowflake is much more limited. For example, if a user in the SALESPERSON role creates a table, a SYSADMIN role member cannot even see that table, unless the SYSADMIN role is added to the SALESPERSON role.

Now it's recommended that the SYSADMIN role is added to all other roles, to avoid this issue but there's no concept of a DDL trigger that would allow you to enforce that.

One at a time

Another very curious concept is that your security context can only be in one role at a time. That's quite unlike Windows, and SQL Server where you can do whatever any of the roles that you are in can do.

There is a USE Database like we have in SQL Server but there is also USE WAREHOUSE to decide which virtual warehouse will be used for compute costs, and USE ROLE to decide which role you are operating in. So commands that you execute are always in a context of:

  • Which database
  • Which warehouse
  • Which role

DDL commands look odd

While the concept of granting a permission to a role is familiar, and works much the same way, they also use the words GRANT and REVOKE for role membership.

I find that really odd. In my mind, roles are things that have members, and the DDL should relate to adding and removing members from roles.  Roles can be nested, and so if, for example, you want to add the role SALESPEOPLE to the role EMPLOYEES, you say:

GRANT EMPLOYEES to SALESPEOPLE;

So I can't say that I like the way that they've implemented the DDL commands for roles. I think there should be separate DDL for granting and revoking permissions from roles, and for adding and removing members of roles.

Schemas are also a bit odd

Another thing that I find odd is that when you grant a role permission to SELECT on a schema, it only applies that to objects that are currently in the schema. So that's just like a shortcut way to avoid granting to every object in the schema. Normally when I grant permissions at the schema level, it's for all items currently in the schema, and all objects that will ever be in the schema.

Curiously, Snowflake has an extra permission for that, where you need to separately tell it "oh, and all future objects too".

Transferring Ownership

To transfer ownership of an object, you need to:

  • Remove all permission grants
  • Change ownership
  • Re-establish all permission grants

If you delete a role, the role that you are currently executing in when you execute the delete, becomes the owning role for all the objects in the deleted role.

Checking current grants

You can check the grants that have been made by:

SHOW GRANTS TO ROLE
SHOW GRANTS TO USER

SQL: Lack of consistency in development says so much about your team

I wrote last week about how I don't like unnecessary abbreviations. It's all part of a desire to see higher quality code, because that costs less to maintain and support.

Another key issue that troubles me in development is when I see inconsistency.

Single Brain

When you have a team of people doing work, you need to endlessly try to make the outcome look like it's come from a single brain. And equally importantly, make it look like it's come from a single brain that cares about quality and attention to detail.

I might be a bit anal on these things, but let me give you a few simple examples of where I've seen this in SQL Server, one from ASP.NET, and one from Azure DevOps. I can give you many customer site examples, but these might be better as you might recognize them.

SQL Server Examples

When I saw the graph functionality added to SQL Server 2017, I noticed the new functions had names like NODE_ID_FROM_PARTS and EDGE_ID_FROM_PARTS. Now that's all well and good, but this is in the same product with functions like DATEFROMPARTS, DATETIMEFROMPARTS, etc. Now I prefer the underscores in the graph functions, but how does a single product end up with this sort of difference all over the place?

We're used to seeing Intellisense in SSMS for functions like this:

All good. But note how the Intellisense shows for this function that was added almost a decade ago:

Is Param1, Param2, etc. really the best the developer could do? But even that's better than the graph functions that I mentioned:

All the other functions have their own T-SQL documentation page. Not these ones. And an SSMS F1 search for help on them takes you to a generic page for help on the SSMS editor.

Another lesson is that even if you don't like a current scheme within a product, you don't just randomly change it to something you prefer. You continue with the scheme for the sake of the overall application.

In SQL Server, we already had tinyint, smallint, int, and bigint. We also already had smalldatetime, and datetime. So if you were going to introduce a new data type that had a bigger range of values, and higher precision than datetime, what would you call it? I'm hoping you would have said bigdatetime, and not datetime2 like we ended up with in the product.

ASP.NET Example

One of the biggest contributions of the .NET Framework was that it cleaned up most of the mess that had evolved in the underlying Windows 32/64 APIs. It didn't matter what the collection of things I was working on was (e.g. IPAddresses, SqlParameters, etc. ), I knew that I could call the Add() method to add a new one, and the AddRange() method to add several.

I remember sitting in a room when we were first shown the new ASP.NET membership system. While everyone else in the room seemed impressed about how easy it was to implement, I think I was the only one in the room looking at new methods like AddUserToRole() and AddRoleToUser() and wondering why on earth they weren't User.Roles.Add() and Role.Users.Add().

Azure DevOps Example

It even happens in UI. I was working in Azure DevOps the other day, and many of the buttons said OK but other buttons said Ok. How does this happen? Am I the only one that jars on when I see it?

I suspect these teams are lacking a group of cranky old guys who look at these things and say "no, have another go".

Attention to Detail

I spent time working as a customer engineer for Hewlett Packard in the early 1980's. They really did make amazing minicomputers that were a cut above anything else on the market, particularly in the commercial space. When we were doing work on those machines, we prided ourselves on the quality of the maintenance work as well.

When I was helping out in other offices though, every now and then, I'd start to work on a machine and find things like a few screws missing. The minute I saw that, I knew that the person who'd worked on it last, didn't share that same attention to detail. I then had an uneasy feeling about working on that machine, as I knew I could be working on something with unexpected issues.

What else wasn't put back together properly?

And it's the same with applications. If the UI or the APIs are inconsistent, what does that portray about the quality of what's under the surface?

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