Learning Mandarin: PinYin Sound Groups

In an earlier post, I described the use of Pīnyīn (拼音). It allows us to enter Chinese characters quickly, using a keyboard that's designed for Western languages like English.

When you first look at the characters though, you might not realize that there isn't a random pattern to them, the characters are constructed from particular groups of sounds.

The words are constructed from 声母(Shēngmǔ) or "initials" and 韵母(Yùnmǔ) or "finals".

The initials come from this list of single characters:

b, c, d, f, g, h, j, k, l, m, n, p, q, r, s, t, x, z

and this list of double characters:

ch, sh, zh

The finals come from this list of single characters:

a, o, e, i, u, ü 

and this list of double characters:

ai, ei, ao, ou, ui, iu, an, en, in, ün, er

and finally, this list of triple characters:

ang, eng, ing, ong

You'll find that all words are created from this list of initials and finals.

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 is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.

SQL: What on earth is Halloween protection?

If you've worked with SQL Server (or with databases in general) for any length of time, you will have heard someone refer to "Halloween protection".  It refers to a problem where update operations could inadvertently end up modifying a row more than once.

I saw someone who was pretty knowledgeable complaining about just this problem recently. He was using a dynamic cursor in SQL Server. The problem was that he was progressing along an index, but when a row was modified, it was relocated to a position in front of where he was processing, so it got processed again.

To avoid this, we need to ensure that any update operation reads the data first, and keeps a list of the values that were read, before starting to do the update. For standard updates, SQL Server does this automatically. But this guy had fallen foul of the problem by using a dynamic cursor. If he had used a static cursor (which would have had horrid performance), he would have been ok. It's gets  a copy of all the data before starting.

It's not all about cursors though. Let's look at how SQL Server handles this in typical updates with an example:

The query plan for that is quite straightforward:

If I clustered the table on the SalePrice though, that would be a problem, and you can see how the query plan changes. Note the additional query plan step:

Lots of queries have this extra table spool step, and for similar reasons. It's not exactly the same problem but look at this query's plan:

In most high-level languages, you can't just say A = B and B = A to swap values over, but it works just fine in SQL. That's because SQL Server has read the values, and has a copy of them from before the changes were made.

The name Halloween protection comes from when the problem occurred during research on the System R project at the IBM Almaden Research Center back in 1976. So the name comes from when it happened, rather than from anything about the problem itself. The researchers ( Don Chamberlin, Pat Selinger, and Morton Astrahan) ran a query to raise everyone's salary by 10 percent, only if they had a salary of under $25000. They were suprised to find that after the query had completed, no-one had a salary of less than that, because the rows had been updated more than once, until they reached the $25000 value.

The name has been used to describe this problem ever since.


Shortcut: Undock tabs and windows in SSMS to other screens

Like Visual Studio that it's based upon, SQL Server Management Studio (SSMS) is very flexible when working with query windows and tabs.

Most people realize that you can undock and move tabs and windows around. Usually they do that by accident and then realize that the Reset Window Layout option in the Window menu is helpful.

But one option I've found that many people don't seem to realize is that you can undock just a single query window and move it outside the bounds of SSMS. You can even place it across on another screen if you have multiple screens.

It then also appears separately in your taskbar in Windows:

You can later drag it back to redock it.

SDU Tools: Script SQL Server Table

In our free SDU Tools for developers and DBAs, we've added a lot of scripting tools. The tool that I'm describing today is one of the most sophisticated tools in our scripting options. It's ScriptTable.

It's very flexible. For example, it can change the name of the table, or the schema that it's in. It can force ANSI_NULLS and ANSI_PADDING on or off. It can change user-defined data types to their base types, change compression strategies, and more.

Have a bunch of pesky collation settings that you didn't want? It can remove them too, and it can let you choose things like the indents to be used during scripting.

You can see how to use it in the main image above. It's a procedure, that takes the following parameters:

@DatabaseName sysname -> Database name for the table to be scripted
@ExistingSchemaName sysname -> Schema name for the table to be scripted
@ExistingTableName sysname -> Table name for the table to be scripted
 @OutputSchemaName sysname -> Schema name for the output script (defaults to same as existing schema)
 @OutputTableName sysname -> Table name for the output script (defaults to same as existing table)
@OutputDataCompressionStyle nvarchar(10) -> must be one of SAME, NONE, ROW, PAGE (SAME uses whatever the table's first partition currently has)
@AreCollationsScripted bit -> Should all collations be scripted (default is 0 for no)
@AreUsingBaseTypes bit -> Should the table use the underlying base types instead of alias types (default is 1 for yes)
@AreForcingAnsiNulls bit -> Should the script include code to force ANSI_NULLS on (default is 1 for yes)
@AreForcingAnsiPadding bit -> Should hte script include code to force ANSI_PADDING on (default is 1 for yes)
@ColumnIndentSize -> How far should columns be indented from the table definition (defaults to 4)
@ScriptIndentSize -> How far indented should the script be? (defaults to 0)
@TableScript nvarchar(max) OUTPUT -> The output script

The script in the main image produced the following output:

I've mentioned that one key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

You can watch it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:


T-SQL 101: #2 What is T-SQL?

In the first blog post of this introductory series for learning T-SQL, I talked about what SQL is. That then raises the obvious question, about what T-SQL is.

Well the promise of SQL was that we'd have one language, and it would work with all databases. Did that end up working? Well, sort of.

Committees, even the ANSI committees, are notoriously hard to make progress in. Everyone has no doubt heard the joke about a camel being a horse designed by a committee.

Awesome image by Cal Engel
Awesome image by Cal Engel

One problem is that even when you have a standard, different people will implement it in different ways. It's impossible for the standard to be 100% clear, and so they'll read and implement the standard differently. That can make two products different, even if they both claim to have implemented the ANSI standard.

But the problem in the case of SQL was that people needed to do more things than the language supported, particularly in the early days. Rather than wait for the committee to come up with a standard way of doing things, each company created their own variation of SQL.

And so Oracle has PL/SQL; Postgres has pgsql; and SQL Server (which was originally derived from Sybase code) has T-SQL.

So T-SQL is a Microsoft-specific implementation of the SQL language, and it's used for working with SQL Server, which today, looks nothing like its Sybase origins.

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 Queries for SQL Server course is online, on-demand, and low cost.

Learning Mandarin: Electric words

I mentioned last week that the word for computer 电脑 (diànnǎo) was wonderful because it was literally "electric brain".  I tend to learn words in groups rather than individually, and the words related to electricity  (diàn) are fun.

I love the allusions that they bring forward, and I thought you might enjoy knowing a few of them.

电力 (Diànlì) or "electric power" means just what it says but is often used for just electricity.

电子 (Diànzǐ) could be translated like "electric child", so that one's a bit odd. It means "electronic" as an adjective, and "electron" as a noun.

电梯 (Diàntī) or "electric ladder" is an elevator.

电影 (Diànyǐng) is one of my favorites. It's close to "electric shadow" and means "movie".

电话 (Diànhuà) or "electric speech" is "telephone".

电视 (Diànshì) or "electric vision" is "television".

电信 (Diànxìn) or "electric letter" is "telecommunications".

A less common one today 电报 (Diànbào)  or "electric newspaper" is "telegram".

电池 (Diànchí) or "electric pool" is "battery".

电车 (Diànchē) or "electric vehicle" is "tram".

电灯 (Diàndēng) is literally "electric light".

电线 (Diànxiàn) or "electric line or pipe" is "wire".

Also, some the other way around like:

闪电 (Shǎndiàn) or "flash electricity" is "lightning".

And there are many, many more. I really love the way that many of these have been formed.


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 is Tutor Ming. If you decide to try it, click here and it's a bit cheaper for you, and for me.

Bring your reports and analytics to life. Learn SQL Server spatial now.

I love working with SQL Server spatial. Don't just use boring tables and charts. Spatial data can really help to bring your reports and analytics to life.  Don't assume spatial is just about maps. (It's not). Don't assume you don't have spatial data. (I'm sure you have addresses, locations, etc.)

We've been working hard lately on getting many of our popular in-person courses, converted to being available online and on-demand. We're really proud to now have SQL Server Spatial for Developers and DBAs available.

We wanted to give you an experience that's as close as possible to a classroom course, but right in your own location, and at a time that suits you.

It's a detailed course. It's not just a set of videos. There are hands-on labs that you can do (we supply the data), quizzes that you can complete, and once you're even more awesome, you can get a certificate of completion.

It's $195 USD but best of all, use the coupon GOSPATIAL at the checkout, and you can enrol for $95 USD. (Until the end of January) Just click on the enrol button (says $195), and then click Add Coupon on the next page.

Check it out here: https://training.sqldownunder.com/p/sqlserver-spatial-data

And check out all our courses here: https://training.sqldownunder.com

More courses are on the way very soon.

Shortcut: Tab groups in SQL Server Management Studio

In a previous post, I showed how you might use split windows to allow you to work on different parts of a single query at the same time.

But what if you need to work on two queries and see parts of both of them?

That's where tab groups can help you. You can create both vertical and horizontal groups. For me, the most useful is typically side-by-side vertically, for when I'm comparing two sections of code.

From the Window menu, I choose New Vertical Tab Group:

After I click that, as long as there are two or more queries open, I'll see this:

I then have two queries that I can work with side-by-side. A horizontal tab group places them one above the other.

In the image above, I had two queries in the left group, and one in the right group. You can also move queries between these groups. If I right-click the tab heading for one of the queries, I see this:

I can create yet another tab group based on this tab, or move it to the next tab group, or even close this tab group by moving all tabs within it to the next tab group.

SDU Tools: Script SQL Logins for SQL Server

In our free SDU Tools for developers and DBAs, we've added a lot of scripting tools. To script out SQL authenticated logins (as opposed to Windows logins), we have ScriptSQLLogins.

You can see how to use it in the main image above. It's a function, that takes a list of the logins to script. You can pass the value ALL or a comma-delimited list.

Importantly, as well as the login and the hashed password, it also scripts other details:

It includes the default database, and policy check status, but importantly, it also scripts the SID (security ID) associated with the login, so if you recreate it on another server, you'll have the same ID, and you won't end up with orphaned users. That's a problem that I see all the time, where users restore a database, recreate the logins, but the logins don't still have the ability to connect to the database. It's because the names are the same, but the SIDs are different. This tool avoids that issue.

One key advantage of having these scripting procedures and functions is that you incorporate them into other applications, and programmatically perform the scripting if you need to.

But if you just want to create a script, that works too.

You can see it in use in the main image above, and you can watch it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:


Opinion: Bad news is best delivered promptly and directly – don't BS

In the 1990's I was running a software company. We had a great sales guy who was also a great friend. One of the things that I loved about working with him, is that even if he didn't know how to solve a technical problem, he'd ask the questions that I led me to see a problem differently. I could then solve it. I wish every technical person was lucky enough to have someone like that.

Another thing that I loved about him though, is that he was awesome at delivering bad news. Mind you, I always hope that I don't need to deliver bad news. But sometimes it's inevitable, and it's a skill that we all need to learn. One lesson I clearly remember from him though, was:

Bad news doesn't get better with age.

Delaying telling someone bad news isn't going to ever make things better; and usually it makes things worse, often much worse.

Now apart from hesitating to tell people bad news, a trend that's really getting through to me lately is an even worse one.  It's where a layer of BS is added to the news, to try to obscure or soften the bad news.

Don't do this.

This makes the situation far worse. You are seen as being cagey about the bad news, and, you're also treating the customer or other party with contempt. This is never a good thing, yet I see it time and again. Where I see it most today, is from software as a service (SaaS) companies.

Let me give you an example. This week I received an email from a vendor, and it said:

"Based on feedback from customers like you, we’ve learned that the reporting features within the xxx Free plan weren’t easy to use and lacked some of the metrics needed to prove success with social. As these features weren’t being used by most customers, we will be removing analytics from your xxx plan in March 2019. … If you need feature-rich analytics, they’re available in our Professional, Team, Business, and Enterprise plans."

Really? People like me complained about the reporting and analytics in your free offering so much, that you're removing it?

I call BS.

I could be wrong, but I don't think it would take Einstein to decide that what they are really saying is that they don't have a compelling enough story to get people onto paid plans, and they need to make money, and so reporting and analytics won't be in the free offering any more.

So why not just say something like: "Hey, we know that the reporting in our free plan isn't great. We want to do it better but we can't afford to do it for free, so in future, the reporting and analytics will only be in the paid plans, and we hope you'll think they're so great that you'll want to sign up for a paid plan. To give you a taste of it, we'll give you a free trial of those features for the next month".

OK. I might be disappointed by that if I was using them, but I'd accept it.

But instead, how does the vendor couch the discussion? They justify it as being based on feedback from customers like me.

Immediately my internal BS-meter goes haywire. So as well as delivering bad news, I now feel like they think I'm stupid.

Don't do this. Just apologize, deliver the bad news and move on.

I hear this sort of thing all the time, and SaaS companies (large and small) do this regularly. They want to change something they've previously offered, the new option isn't as good for the customer (or at least at the same price), so they try to justify bad news as somehow coming from "feedback from customers like you". Almost every time I hear that phrase I think it's just not true.

Unless almost every customer reading your message would agree with what you're saying the customer feedback was, don't try to pretend that that's why you're making the change.

If you have to deliver bad news, just do it directly, and do it as soon as it's decided (Don't make the customers hear it second hand).