Learning Mandarin – it all happened suddenly

Another group of words that are probably best learned as a set are the ones that are related to things happening suddenly. And you'll notice they are similar in one way.

突然 (Túrán) is basically "suddenly" but can also be used for "abruptly" or "unexpectedly".

忽然 (Hūrán) is also "suddenly" or "all of a sudden". But the difference is that it's a bit softer. 突然 (Túrán) can be intended to be quite harsh or jarring. 忽然 (Hūrán) might be "sudden but not unexpected".

竟然 (Jìngrán) can also be "suddenly" but it tends to indicate more of a surprise that has happened, so more like "unexpectedly".

居然 (Jūrán) is also "suddenly" or "unexpectedly" and is closer in meaning to 竟然 (Jìngrán) but 居然 (Jūrán) is softer than 竟然 (Jìngrán).

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. 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 16 – Primary and Foreign Key Constraints

The Usual Situation

In general database terminology, a primary key is one or more columns that can be used to identify a particular row in a table. The key needs to be unique, and it can't be null. An example would be a CustomerID or CustomerKey in a Customers table.

A foreign key is one or more columns that refer to a key in another table. A common example would be a CustomerID column in an Orders table. Foreign keys can be nullable and are checked when they contain a value.

Foreign Keys in Data Warehouses

I like to see foreign keys in data warehouses. The most common objection to them is performance. And yet when I ask those same people if they've ever tested it, I'm usually told that they haven't but that their brother's friend's cousin read it somewhere on the Internet.

Don't be that person. 

I also hear "the app does that", and so on. Yet, almost every time I check a substantial data warehouse for consistency, when it's run for a while without foreign keys being checked, I invariably find problems. When I show them to people, I then hear "oh yep, we had that bug a while back…" and so on, but there's almost always an issue.

Even if the app checks the data, other apps touching the same data might not. And what if your ETL (or ELT) processes have bugs? You need to find out about it immediately.

Primary and Foreign Keys in Snowflake

I wish it wasn't so, but while you can define primary and foreign keys on tables in Snowflake, note this:

Yep, they are ignored. They are not checked at all.

Same deal for unique constraints. (Mind you, unique constraints are really a broken concept in SQL Server as well. While they are checked, SQL Server only allows a single row where the key is null. That's not good either).

And that's why there are people complaining in the Snowflake forums about issues caused by duplicate primary keys.

I really think this aspect of the product needs to be reconsidered and I encourage the Snowflake team to do so. For many sites I work at, this single aspect would be a showstopper.

 

For an index to all posts in this series, see the first post here.

 

 

 

Learning Mandarin: Saying Not- the difference between 没 (Méi) and 不 (Bù)

In English, the word not delivers the opposite meaning to other words, usually to verbs. So He is here becomes He is not here, and I do like it becomes I do not like it. But in Mandarin, there appear to be two words that are used in much the same way.

不 (Bù) and 没 (Méi) and both used to indicate "not". And unlike in English, they go before the verb.

Even though I've been learning Mandarin for the best part of a decade now, my teachers are still often correcting me when I've used the wrong one of these. So which should be used when?

Some rules

The simplest first rule is that if you're using the verb 有 (Yǒu), it's always 没有 (Méiyǒu). A really simple question in Mandarin is about whether or not you have something: 有没有？ (Yǒu méiyǒu?) is literally "have not have" but is used for "Do you have (it)?".

If you have things that sound like habitual actions, these invariably are reversed with 不 (Bù). For example: 我不喝啤酒 (Wǒ bù hē píjiǔ) is "I don't drink beer".

Most adjectives (particularly descriptive ones) are also reversed by using 不 (Bù). For example: 她不高 (Tā bù gāo) is "She is not tall".

There is also a sense of tense with these two words:

我不吃 (Wǒ bù chī) is "I don't eat" or more likely "I will not eat".

我没吃 (Wǒ méi chī) is more like "I didn't eat". (Note the past tense).

And not quite the opposite

It's interesting that when you combine 不 (Bù) with 错 (Cuò), you produce something that's much more positive than in English. Google translates 它很不错 (Tā hěn bùcuò) as "It is very good". In English "not bad" isn't so positive.

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. 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.

SQL: Why is my C drive thrashing when the databases aren't on it?

There was a poster on a forum recently who was puzzled about disk activity on his system. He had created his database files on non-system drives, yet when he checked the activity on the C drive, it was constantly thrashing. As SQL Server was the only application of any note on the system, he was wondering what on earth was going on.

There are things in the OS that will write to the drive but I think the most likely candidate is that tempdb is still on the OS drive.

In recent versions of SQL Server, if you don't change the location of tempdb, it'll end up where the SQL Server programs are installed, and that's usually on the system drive.

Moving tempdb to another drive is easy and is described in this article.

There is a specific example near the bottom of the page that shows how to move it.

There is one more possibility that immediately comes to mind, and that's if there's not enough memory on the system and the Windows OS page file is thrashing, but that's far less likely.

SDU Tools: Truncate trailing zeroes in SQL Server T-SQL

Often when I convert a decimal number to a string in T-SQL, I want the simplest version of the number. I really don't want any trailing zeroes (i.e. at the end of the number).

In our free SDU Tools for developers and DBAs, we added a function to do just that: TruncateTrailingZeroes.

The function just takes one parameter: the number to be processed.

You can use our tools as a set or as a great example of how to write functions like these.

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

 

Book Review: The Future of Capitalism by Paul Collier

I've mentioned before that Orin Thomas tends to give me many good book recommendations. The Future of Capitalism by Paul Collier is another of Orin's recommendations. I notice from the cover that it's also highly recommended by Bill Gates. So it had to be worth a look.

I ended up listening to this via an Audible audio book. It's quite a long book at around 9 hours 26 minutes.

Overall, I loved this book. I'd have to say though, that it took me quite a while to get into it. I just didn't find the first chapters all that compelling. But strangely, I then really did get into it.

The future of work and how it will affect society is a real passion for me. I'm both anxious and excited about what's coming. I just wish I was 40 years younger, to be in a better position to see this all play out. Collier, though, targets capitalism itself directly.

There are so many aspects of how the world currently does (or often does not) work that deeply concern me. Our current form of capitalism is of benefit to an ever-decreasing percentage of people. I remember reading elsewhere about how most of the people in the USA see themselves as "middle class" yet most of them are far from it.

Collier sees much of the current system to be inherently broken, and I agree.

What is different about this book is the way that Collier offers direct, and quite practical, pragmatic suggestions for how to solve many of the current problems.

Bottom line?

I really enjoyed this book. If you don't find the start of it all that compelling, I'd encourage you to keep at it. It's worth it.

Greg's rating: 8 out of 10

T-SQL 101: #44 Trimming whitespace from strings in SQL Server

I often need to remove spaces from the front or back (or both) of strings. For a long time, the two functions we had for that were LTRIM (for left trim) and RTRIM (for right trim).  You can see them in the first two SELECT statements here:

For as long as I've used SQL Server though (also a long time), people have been asking for a TRIM function. Most developers were tired of endlessly writing LTRIM(RTRIM()) to do that. In SQL Server 2017 though, we did finally get a TRIM function. It trims both the left and right hand sides of a string.

Now while these functions are excellent and do what they need to, the thing that they don't do is remove other whitespace characters. If you need a function to do that, I'd suggest you look at our TrimWhitespace() function in our free SDU Tools for developers and DBAs. It removes all common whitespace characters including Unicode specific ones.

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: Sets of words – positions and directions

I find that learning whole sets of words is useful, rather than just trying to learn words in isolation. A good example is that when I was learning colors, I'd just learn as many colors as I could, and I'd just look around me in whatever room I was in, and try to name the colors that I saw.

A similar useful set is positions and directions. These are also good to learn as a set:

左边 (Zuǒbiān) is to the left

右边 (Yòubiān) is to the right

前面 (Qiánmiàn) is in front

后面 (Hòumiàn) is behind

上面 (Shàngmiàn) is on top (or above)

下面 (Xiàmiàn) is under (or below)

旁边 (Pángbiān) is next to (or beside)

对面 (Duìmiàn) is across from (or opposite)

Learning Mandarin

I'll write more soon on the best methods for learning Mandarin. 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 15 – Table types

Snowflake has a richer set of options for how tables are created, than we have in SQL Server.

CREATE TABLE Variants

As well as a CREATE TABLE statement as you would already be familiar with in SQL Server, Snowflake offers these variants:

CREATE TABLE tablename AS SELECT

This is basically similar to a SELECT INTO in SQL Server. It executes the SELECT query and creates a table from the results (including the data).

CREATE TABLE tablename LIKE

This is an interesting variant. It creates a table with the same schema as an existing table but without any data i.e. it creates a new empty table based upon the design of another table.

CREATE TABLE tablename CLONE

This is another interesting variant. It clones one table to create another table. It's similar to the LIKE option but also includes all the data.

Table Types

Permanent

Permanent tables are standard table types that are pretty much the same as the equivalents in SQL Server.

TEMPORARY

This is similar to a temporary table in SQL Server. The table and its data are retained until the end of the user's session. The syntax supports a concept of LOCAL TEMPORARY and GLOBAL TEMPORARY but these options have no affect. A standard TEMPORARY table is created.

Note that TEMPORARY can be abbreviated to TEMP, and has a synonym of VOLATILE.

TRANSIENT

These tables aren't dropped at the end of a user session and stay until someone drops them. They are also visible to all users.

A key difference with them though is that they don't offer the same level of protection as standard tables. They are more like an eventually-consistent table where you might lose data if the system fails. They should only be used for data that can easily be recreated if needed.

 

For an index to all posts in this series, see the first post here.

 

 

SQL: Sending email to an operator rather than a recipient in SQL Server

I was answering a question on a forum the other day, and the person asking the question was puzzled about how to send an email to an operator (i.e. msdb operator) rather than to just any other email address.

The reason he wanted to do that was that he wanted to use the Agent operator system to define a name. He didn't want to hard-code a recipient name in his code. By having it as an operator, it could be changed later, independent of the code.

Doing that isn't so hard. You just need to retrieve the operator's email address from msdb.dbo.sysoperators before sending the mail. Here's an example:

And then send the email like this:

I hope that helps someone.