Learning Mandarin: Relative times for weeks

I wrote in a previous post how relative times work for days. (Similar to how we say tomorrow, yesterday, day before yesterday, day after tomorrow, etc. in English).

Well the options for weeks are a bit different to the ones for days. To start with, it's worth noting that there are two common words for weeks:

星期 (Xīngqí) is literally "star period" but means week. It's pronounced like "shing chee".

(Zhōu) also means week but can mean many other things as well.

I commonly hear (Zhōu) used in 周末 (Zhōumò) which is pronounced somewhat like "Joe Moore" would be in English.

So let's look at the relative references:

这个星期 (Zhège xīngqí) is pronounced like "jer ger shing chee" and means "this week". Similarly you could use 这个周 (Zhège zhōu), and you might even leave out the middle character (ge). I've also seen 本星期 (Běn xīngqí) used for "this week".

Last week is 上个星期 (Shàng gè xīngqí) is pronounced like "shung ger shing chee". Curiously the first character means "up" not "last".

And based on that, it's not surprising that 下个星期 (Xià gè xīngqí) which is pronounced like "shaa ger shing chee" means "next week". The first character means "down" not "next".

Often you'll just see people say 下周 (Xià zhōu) which is shorter and to the point.

Chinese is wonderfully concise at times, and 上上个星期 (Shàng shàng gè xīngqí) is a good example. It's the week before last. You can also just write that as 上上周 (Shàng shàng zhōu).

And no surprise, for the week after next, we can just say 下下个星期 (Xià xià gè xīngqí), or more commonly, the beautifully short 下下周 (Xià xià zhōu).

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 – A Christmas Present

I've had a lot of great feedback on my series of blog posts about Snowflake, and explaining the concepts for an existing SQL Server audience.

Just out for Christmas is a new free eBook for our SDU Insiders. I'm preparing a three part series on Snowflake for SQL Server users:

  • Part 1: Core Concepts (available now)
  • Part 2: SQL Language (coming soon)
  • Part 3: Administration (sometime after that :-))

You can get part one now! And did I mention free? (for SDU Insiders)

You'll find it here:

http://snowflakeforsqlserver.sqldownunder.com

Enjoy !

SQL: Violation of SQL Server UNIQUE KEY constraint but entry doesn't already exist

One issue that comes up time and again in the forums is when an INSERT statement fails with a violation of a UNIQUE or PRIMARY KEY constraint but when the user checks the existing table, the value that's being complained about isn't already in the table.

The Symptom

For example, a table might have primary key values of 2, 12, and 14.

When an INSERT is performed, there is an error telling you that you can't insert a duplicate key value of say 15.

You check the table and there is no row with a primary key value of 15.

The same issue can happen with UPDATE statements.

What Usually Causes This

Whenever you see this, the issue is almost always that the duplicate values exist in the data that you're trying to insert, and that's why it can't insert.

In the example above, it would be because the input rows contain two or more rows where the primary key value is 15.

Often this will be caused by a join that is producing more rows than planned.

 

 

Happy Christmas 2019 to all my readers

Hi folks,

2019 has been another really big year. The number of readers of this blog has really increased in the last year and I'm pleased you're here as one of them.  You make it all worthwhile.

Thanks to so many who reached out during the year, with wonderful insights and ideas.

Just wanted to wish you all the best for the holiday and Christmas season, from here down under.

Christmas time is often a period where we think back about those we've lost. The older you get, the more there are to remember. They live on in your hearts but life is all too brief. I hope you all stay safe and cherish your loved ones.

Not everyone finds Christmas a happy time. Keep an eye out for people that need extra help during this season.

Greg

 

T-SQL 101: #49 Using REPLICATE to repeat strings in SQL Server

REPLICATE is an easy function to work with. It takes a string and a number of times you want it repeated. Here's an example:

In this case, I've asked it to replicate (i.e. duplicate many times) an A character. I've asked for 5 of them. I put an X at each end so you could see the effect more clearly.

There are many things this can be used for, but for example, if I wanted to draw a line with 100 dashes, I could just ask for REPLICATE('-', 100).

Extra-long Strings

One challenge with this function though, is that the output data type is the same as the input data type. So even though I can have strings in T-SQL of up to 2GB in size, varchar was limited to 8000 characters. This means that if I wrote

I would only get 8000 characters.

But if I forced it to varchar(max), like this:

then I would get 10,000 A's.

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: Relative times for days

In English, we have a few ways of referring to time, relative to the current time. Mandarin is similar, and has a few patterns.

For days:

(Tiān) is day (although it can relate to the heavens, God, and much more)

Although 每个天 (Měi gè tiān) is literally "every (or each) day", doubling-up the character as 天天 (Tiāntiān) can also be used for every day

今天 (Jīntiān) is today

昨天 (Zuótiān) is yesterday

明天 (Míngtiān) is tomorrow (and roughly translates to "bright day")

前天 (Qiántiān) is the day before yesterday (and roughly translates to "in front day)

后天 (Hòutiān) is day after tomorrow (and roughly translates to "rear (or perhaps behind) day"

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 20 – Encryption within Snowflake

As with most products today, Snowflake has substantial handling of encryption to protect client data.

All editions claim to provide "Always-on enterprise grade encryption in transit and at rest".

In Transit

First, for connections, HTTPS and TLS 1.2 are used throughout the system.

If the customer is using external stages (holding locations in cloud-storage accounts), the data can be encrypted while stored in the stage, and then travel encrypted into Snowflake. To do that, you create an "encrypted stage" where you tell Snowflake the master encryption key (i.e. the client side key) when creating the stage:

This is useful in allowing you to create an end-to-end encryption while ingesting data into Snowflake.

At Rest

Regarding "at rest", I've previously described how micropartitions are used to store data in data files. These data files are then encrypted (using AES-256) before being stored.  But how it's handled changes with different editions of Snowflake.

There are four levels of keys used:

  • The root key
  • Account master keys
  • Table master keys
  • File keys

With Standard and Premier editions, a different file key is used each month (aka key rotation). So a single "active" key is used to encrypt your first month's data, then a different key is used to encrypt the next month's data, and so on. Previous keys that are no longer "active" (aka retired) are only used for decryption.

Annual Rekey

Enterprise edition and above offer "Annual rekey of all encrypted data". I was a bit puzzled about how that worked at first. I imagined that once a year, all the existing data would get decrypted and rekeyed. I was thinking that would be quite an expensive operation on a large data warehouse.

What happens instead, is that when any key has been retired for a year, any data encrypted by it is decrypted and re-encrypted using a new key. So if you have those editions, if you store data this month, it will have a single key. In a year and one month's time, it will get rekeyed. And again in two years and one month's time, and so on.

Business Critical

The Business Critical edition takes things further. As well as providing  HIPAA and PCI compliance, it offers "Tri-Secret Secure using customer-managed keys". In this case, Snowflake combines the key that you provide with a Snowflake-maintained key to create a composite master key.

As well as having the obvious benefits in letting you manage access to the data more closely, you can immediately remove all access if needed (i.e. closing an account, or recovering from a breach).

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

Training: Give yourself a Christmas present – learn something new

It's that time of year where people give each other presents. Why not give yourself a present and learn something new while you've got a chance?

Our latest course release is: SQL Server Integration Services for Developers and DBAs.

You'll find it, and our other courses here now:

https://training.sqldownunder.com/

  • 4 Steps to Faster SQL Server Applications (Free)
  • Protecting SQL Server Data with Encryption
  • Writing T-SQL Queries for SQL Server (Low cost)
  • SQL Server Reporting Services for Developers and DBAs
  • SQL Server Spatial for Developers and DBAs
  • SQL Server Indexing for Developers
  • SQL Server Service Broker for Developers and DBAs
  • SQL Server Service Broker for Developers and DBAs
  • Working with SQL Server Replication

And many more coming soon.

 

SDU Tools: Script User Defined Server Role Permissions in SQL Server

As part of our free SDU Tools for developers and DBAs, we have many scripting functions. SQL Server allows you to create your own server roles, and generally, you should do this instead of using the fixed server roles, as it lets you allocate just the required permissions. To allow scripting the permissions that have been assigned to the roles, we've added a tool called ScriptUserDefinedServerRolePermissions.

It's a function and doesn't require any parameters.

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

 

SQL Down Under Podcast Show 77 with guest Rob Sewell

I'm pleased to announce the release of another SQL Down Under podcast.

Show 77 features Cloud and Datacenter Management MVP Rob Sewell discussing the popular Powershell dbatools.

In the show, Rob discusses the background of the tools, how they've evolved, how to install them, and how you can get involved in contributing to them and other open source projects.

Rob and Chrissy Lemaire (the initial author of the tools) have written a new book on the tools and you can find it here:

If you want to purchase the book, or in fact anything from our friends at Manning, our podcast listeners can use a permanent discount code of podsqldu19.

You'll find the show here: http://podcast.sqldownunder.com

I hope you enjoy it and find it useful.