Book Review: Sherlock Holmes and the Case of the Curly Haired Company (Mandarin Companion)

I've often heard that the best way to learn any language is to spend a lot of time reading the language, particularly books. I really think that's true. So, given my interest in learning Mandarin (Chinese), I wanted to spend more time reading the language.

Now the challenge is always that until you know enough language, it's hard to read books at all, and if you have to keep looking up all the words, that gets painful pretty quickly too. I've heard that ideally, you want to already know about 90% of the words. You want to already know almost all of the common words and need to look up the words that are harder for you.

Now the problem with that is that most books that I could read like that were designed for children, and it's hard to keep your interest going when you're just reading children's books.

So, I was really excited to come across a series of graded readers for Mandarin. The first book I read was Sherlock Holmes and the Case of the Curly Haired Company: Mandarin Companion Graded Readers Level 1 (Chinese Edition) by Renjun Yang (Adapter), Arthur Conan Doyle (Author), John Pasden (Editor).

Mandarin Companion Graded Readers

I was already familiar with John through his previous work on ChinesePod.com. I gather he's been the driving force in these readers. The description of the concept is as follows:

Mandarin Companion is a series of easy-to-read novels in Chinese that are fun to read and proven to accelerate language learning. Every book in the Mandarin Companion series is carefully written to use characters, words, and grammar that a learner is likely to know.

Level 1 is intended for Chinese learners at an upper-elementary level. Most learners will be able to approach this book after one to two years of formal study, depending on the learner and program. This series is designed to combine simplicity of characters with an easy-to-understand storyline which helps learners to expand their vocabularies and language comprehension abilities. The more they read, the better they will become at reading and grasping the Chinese language.

For those who can read some Chinese, this typical page should give you an idea of the level that the book uses:

This book is an adaptation of a Sherlock Holmes story. In the book, Holmes is called "Gao Ming" (or Tall & Clever). I was surprised how much fun the book was, and how they managed to keep the twists and turns in the plot.

Bottom line?

I thoroughly enjoyed reading this book. Although I found it a bit repetitive and simplistic in places, I'd say that the level was perfect for me. I just had a few words here and there that I needed to look up, and, very conveniently, in the Kindle version, they've highlighted words they suspect you might not know, and you just click them to go to a definition, and you can return directly to where you were reading. I'll be reading more of these.

Greg's rating: 9 out of 10

Note: as an Amazon Associate I earn (a pittance) from qualifying purchases but whether or not I recommend a book is unrelated to this. One day it might just help cover some of my site costs. (But given the rate, that's not really likely anyway).

SQL: Penetration Tests, SQL CLR, and Poor Advice

I was working in a bank a few weeks back. They were planning an upgrade from SQL Server 2008 R2 to SQL Server 2017. One of the things that I raised with them is why there were extended stored procedures that had been added to the master database. And they told me they were the result of a penetration test.

OK, so this had me intrigued.

The logic that was being applied was that the penetration testers required the bank to have SQL CLR disabled. No-one in the bank could tell me why; just that it was required to be off.

I could almost have accepted that, even though with SQL Server 2017's whitelisting options for SQL CLR assemblies, I'd really like to see them revisit that decision.

Because the bank wanted to use a 3rd party monitoring and backup tool for SQL Server, they had now chosen the option to use an extended stored procedure based system, instead of a SQL CLR based one.

Awesome image by Paul Hanaoka
Awesome image by Paul Hanaoka

What???

Sorry, that's just poor advice. Extended stored procedures run directly in the memory space of SQL Server itself. You do not want to be writing code there. Let's get something clear:

The SQL CLR procedures that you write will be safer than any extended stored procedure.

There have been so many stability issues over the years, caused by extended stored procedures. Microsoft's own advice tells you to do the opposite of this. They say:

"This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use CLR Integration instead."

An additional protection that appeared in SQL Server 2017 and later is that Code Access Security (CAS) is no longer used for SQL CLR procedures. Instead, you now should specifically whitelist individual assemblies that you need and trust.

If your penetration tester is happy with you replacing SQL CLR procedures with extended stored procedures, it's time for a new penetration tester.

 

 

SDU Tools: Script SQL Server Table as T-SQL Unpivot

Many of my friends have told me that they like how concise the PIVOT and UNPIVOT operators are, but they find the syntax confusing. I must admit that I wasn't a big fan of these when they were added in SQL Server 2008 as they didn't really add anything to the language that I couldn't have done with CASE statements, etc. and there were so many other parts of the language that needed work.

But they are part of T-SQL and it's useful to be able to use them. To make it easier to get started with the UNPIVOT syntax (the harder one), we added a procedure ScriptTableAsUnPivot to our free SDU Tools for developers and DBAs.

The procedure takes a bunch of parameters and is very, very flexible. It can script either a table or a view from the source table, and can rename it and can change the output schema, along with formatting options:

@SourceDatabaseName sysname -> Database name for the table to be scripted
@SourceTableSchemaName sysname -> Schema name for the table to be scripted
@SourceTableName sysname -> Table name for the table to be scripted
@OutputViewSchemaName sysname -> Schema name for the output script (defaults to same as existing schema)
@OutputViewName sysname -> View name for the output script (defaults to same as existing table with _Unpivoted appended)
@IsViewScript bit -> Is a view being created? If not, a query is created. (defaults to query)
@IncludeNullColumns bit -> Should columns whose values are NULL be output? (defaults to no)
@IncludeWHEREClause bit -> Should a WHERE clause be included (when output is not to a view)? (defaults to no) [NOTE: Added in v15]
@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)
@QueryScript nvarchar(max) OUTPUT -> The output script

In the main image above, you can see the output from executing the following script:

Note that in upcoming V15, the ID is always output with the column name AttributeID rather than the original name. If there are multiple primary key columns, they will be called AttributeID, AttributeID2, AttributeID3 and so on.

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

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

http://sdutools.sqldownunder.com

 

Opinion: Data ingestion and opposites

In a previous post, I discussed the way that adjectives have been replacing adverbs, and pondered about what had happened to "ly". For example:

Drive Safe

rather than:

Drive Safely

I had quite a bit of feedback on this, both on and offline. Language discussions are always busy. But another similar trend came up in a discussion that I recently took part in.

A friend asked that if you used the term:

Data Ingestion

was the opposite:

Data Exgestion

Now I know that most words that change an in to an opposite usually use e and not ex. For example ingress and egress (rather than exgress). This means that it would be more likely to be:

Data Egestion

Given that Ingestion normally refers more to food than anything else, it's hardly suprising that Egestion typically refers to what comes out of a backside, so it's probably not a great option, at least in common usage 🙂

Others suggested that the terms:

Data Import

Data Export

should just be used instead. While I agree, it's interesting to note that the above terms are often used as nouns. And that got me wondering about when we started using verbs as nouns.

I never hear anyone talk about a Data Ingest, as though it's a "thing", only about Data Ingestion an an act. But we talk about performing a Data Import, and treating it as both an action, and the act of performing the action, yet I rarely hear anyone say Data Importation when they are discussing the action.

Language is curious.

So what is the best opposite for Data Ingestion or is the term best avoided in the first place?

 

T-SQL 101: #20 Filtering rows to return with WHERE clause predicates

I showed earlier how we can retrieve data from a table using a SELECT clause. It can also be used to determine which columns are returned, and which table the data is being retrieved from. But we don't always want all the rows to be returned. The WHERE clause fixes that.

It's important to understand that the WHERE clause limits the rows returned to ones where the expression in the WHERE clause evaluates to a logical TRUE value.

In the example above, we're returning rows where the Size column contains the string value 370ml.

What often confuses users though is the opposite. If I write:

WHERE Size <> '370ml'

then I'm trying to get all the values except 370ml. Some rows might not have a Size specified i.e. they are NULL. You might expect them to be returned because they aren't equal to 370ml, however, the comparison:

NULL <> '370ml'

doesn't return TRUE. It returns NULL. And because we said that the WHERE clause only returns rows where the expression will be TRUE, those rows aren't returned.

This means that the WHERE clause:

WHERE Size <> '370ml'

will return only rows that have a value for Size and it isn't equal to 370ml.

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 – Exaggeration for emphasis – to Death !

Most languages have expressions that are really massive exaggerations and Mandarin is no different. One of my favorites is the way they say:

死了 (Sǐle) which is basically like "to death"

An example would be:

我饿死了。(Wǒ è sǐle.) which is literally "I hungry death" followed by 了 (le) which some people regard as "past tense" but really is an indication that a state has changed. This sentence basically means "I'm starving" and so the state change is that I wasn't starving but now I am.

There are several other common examples of "to death":

我累死了。(Wǒ lèi sǐle) lèi means tired. So this is really "I'm exhausted".

我害怕死了。 (Wǒ hàipà sǐle) is really scared to death. Curiously, Google Translate gives it as "I'm scared of death". I suspect they're wrong there.

While these work OK in English, a number of other common ones don't.

我忙死了。(Wǒ máng sǐle) Google says "I'm busy" but it's really "I'm really, really, really busy". We don't really say we're "busy to death".

它贵死了。(Tā guì sǐle) I don't quite get how "expensive" to death makes much sense but it's OK in Mandarin. This means "it's so very, very expensive".

它难吃死了。(Tā nán chī sǐle) is even stranger. It's basically "it tastes really bad".

我都急死了。(Wǒ dū jísǐle) Google says "I'm in a hurry" but this is more like "I'm really anxious" or perhaps "I'm really nervous".

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.

Book Review: The Science of Likability

A while back I purchased a number of Audible titles, thinking I'd listen to them as I traveled around. I grabbed a number of ones related to presentation and I thought I'd also check out some general self-improvement titles.

What I didn't realize, is although they had different titles, I'd basically bought a number of copies of essentially the same book, but with different titles. This was one of them.

The Science of Likability: 27 Studies to Master Charisma, Attract Friends, Captivate People, and Take Advantage of Human Psychology is a book by Partick King. This book is a 2017 update of a 2015 book that titled The Science of Likability: Charm, Wit, Humor, and the 16 Studies That Show You How to Master Them. 

However, it's not just this series of books. I was amazed how many other books by Patrick King I had inadvertently purchased. After listening to most of them, even though they all have different titles, so many basically deliver the same message.

And what is sad though, is that I didn't really find the message all that compelling in the first place. Many of the techniques that Patrick discussed seemed pretty cheesy to me. However, I'm sure there is an audience for this type of book. You only have to read the comments on Amazon to find how many people this sort of content helps.

I also have many introverted friends who I think would find many ideas in these books useful. If you are hesitant to join a group of people, or to enter a discussion, or to meet new people, or just to break the ice with someone you don't know, this could well be the book for you. While I find meeting and talking to new people quite invigorating, I do not underestimate how intimidated many people are about doing these things.

Bottom line?

I think I'm the wrong audience for Patrick's books. If however, you struggle with introversion and shudder when you think about talking to new people, etc. this book could well be for you.

Greg's rating: 5 out of 10

Note: as an Amazon Associate I earn (a pittance) from qualifying purchases but whether or not I recommend a book is unrelated to this. One day it might just help cover some of my site costs. (But given the rate, that's not really likely anyway).

Fixing Locking and Blocking Issues in SQL Server – Part 7 – Handling Deadlocks in T-SQL

This is part 7 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue
  • Part 2 covered the impact of RCSI
  • Part 3 looked at the impacts of indexing on locking and blocking
  • Part 4 looked at what deadlocks really are and how SQL Server handles them
  • Part 5 looked at how applications should handle deadlocks
  • Part 6 looked at how to avoid deadlocks in the first place

Today, though, I want to look at how to handle deadlocks if you must do that in T-SQL.

The Situation

OK, let's start with the following situation:

Your application is calling a stored procedure, and it regularly ends up being involved in deadlocks. To make this easy to demonstrate, let's create a table of data:


USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Products;
GO

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_dbo_Products PRIMARY KEY,
    ProductName nvarchar(50) NOT NULL,
    UnitPrice decimal(18,2) NOT NULL,
    OnHandQuantity decimal(18,3) NULL
);
GO

INSERT dbo.Products
(
    ProductID, ProductName, UnitPrice, OnHandQuantity
)
VALUES
    (1, N'Product 1', 12.35, 150),
    (2, N'Product 2', 14.30, 155),
    (3, N'Product 3', 15.80, 165);
GO

Then we'll create a stored procedure that can be used to convert a quantity of one product into another product. This can lead to deadlocks:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    UPDATE dbo.Products
        SET OnHandQuantity -= @FromProductQuantity
        WHERE ProductID = @FromProductID;

    WAITFOR DELAY '00:00:10';

    UPDATE dbo.Products
        SET OnHandQuantity += @ToProductQuantity
        WHERE ProductID = @ToProductID;

    COMMIT;
END;
GO

Note that I added a delay of 10 seconds between the updates to make it easy for us to generate a deadlock. So let's cause one now.

In one query window, execute the following:


-- Query window 1

USE tempdb;
GO

EXEC dbo.ConvertProduct
    @FromProductID = 2,
    @FromProductQuantity = 12.0,
    @ToProductID = 3,
    @ToProductQuantity = 1;

And at the same time, in another query window, execute a similar but different query:


-- Query window 2

USE tempdb;
GO

EXEC dbo.ConvertProduct
    @FromProductID = 3,
    @FromProductQuantity = 1,
    @ToProductID = 2,
    @ToProductQuantity = 14.1;

No surprise, we get the dreaded deadlock message in one window:

Adding Retry Logic

So how do we fix this? We can either change this procedure to have retry logic, or we can rename this procedure and call it from a wrapper procedure that has the deadlock retry logic.

Let's do the second option this time.

We'll recreate the procedure as ConvertProduct_Inner:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct_Inner
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRAN;

    UPDATE dbo.Products
        SET OnHandQuantity -= @FromProductQuantity
        WHERE ProductID = @FromProductID;

    WAITFOR DELAY '00:00:10';

    UPDATE dbo.Products
        SET OnHandQuantity += @ToProductQuantity
        WHERE ProductID = @ToProductID;

    COMMIT;
END;
GO

And then we'll add a wrapper procedure:


CREATE OR ALTER PROCEDURE dbo.ConvertProduct
    @FromProductID int,
    @FromProductQuantity decimal(18,3),
    @ToProductID int,
    @ToProductQuantity decimal(18,3)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @MAXIMUM_RETRIES int = 5;

    DECLARE @RemainingRetries int = @MAXIMUM_RETRIES;

    WHILE (@RemainingRetries &gt; 0) -- retry update for victim
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            EXEC dbo.ConvertProduct_Inner
                @FromProductID = @FromProductID,
                @FromProductQuantity = @FromProductQuantity,
                @ToProductID = @ToProductID,
                @ToProductQuantity = @ToProductQuantity;

            SET @RemainingRetries = 0;

            COMMIT;
        END TRY
        BEGIN CATCH
            IF (ERROR_NUMBER() = 1205) -- deadlock victim
            BEGIN
                SET @RemainingRetries -= 1;
                PRINT 'Warning: Deadlock occurred';
                PRINT 'Remaining retries: '
                      + CAST(@RemainingRetries AS varchar(20));
            END
            ELSE BEGIN
                SET @RemainingRetries = -1;
            END;
            IF XACT_STATE() &lt;&gt; 0
            BEGIN
                ROLLBACK TRANSACTION;
            END;
      END CATCH;
    END;
END;
GO

Now I'll execute both procedures again, and notice that both complete as expected. One has a warning about a deadlock that had occurred but was retried:

This is great, and is something we couldn't do before SQL Server 2005 so many people weren't used to doing it. However, while this will help at the T-SQL end, most other types of retries need to happen at the client end.

 

 

 

SDU Tools: List user access to Reporting Services content

I often need to document the access that various users have to content stored in SQL Server Reporting Services (SSRS). Surprisingly, I didn't find any built-in way to do that.

We had started to add some Reporting Services related options to our free SDU Tools for developers and DBAs, and, just for this, we added a new procedure RSListUserAccessToContent.

It takes two parameters:

@IsOrderedByUserName bit -> Is the output ordered by user (default yes else by item)
@RSDatabaseName sysname -> Reporting Services DB name (default is ReportServer)

The output has the following columns: UserName, RoleName, PathName, FolderName, ItemName, ItemType.

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

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

http://sdutools.sqldownunder.com

 

Opinion: Whatever happened to "ly" ?

In recent years, there's an odd trend that I've been noticing. Adverbs seem to be getting replaced by adjectives, and at an increasingly fast rate. I see signs that say things like this:

Drive Safe

Now when I was at school, we'd have been given a hard time for writing that. We'd have been told in no uncertain terms that it should have been:

Drive Safely

I was trying to work out if it was more of a US-based thing. I see it far more often in US-based writing, yet it's also happening in the UK, Australia, and others as well.

Puzzled by this, I was starting to wonder if it was just me, and, more importantly:

Whatever happened to "ly" ?

Turns out that I'm not alone in wondering this. This Quora discussion asked Why have so many people stopped using adverbs and instead use adjectives, such as "quick" instead of "quickly"?

The article argues that it's part of a very long-term trend, and that you notice it more as you age, and as you visit other places and come across other dialects.

More concerning(ly), this article asks: Is it poor style to use adverbs ending in "ly" in formal writing? Some commenting on the article make a more curious claim: Some grammarians consider "ly" ending adverbs as bad style in formal writing.

Now I don't see specific evidence to support that, and one person commented that it was primarily advice for sci-fi writers.

A recent article in the Guardian asks:  Where have all the adverbs gone? And how did they go?

The author says: Meanwhile, in everyday parlance in America, people are quite happy to do things "real quick". I hope that doesn't catch on here. There's plenty of time to bother saying "really quickly". 

At least it appears that I'm not the only one that's wondering where "ly" went. I'd love to hear your thoughts on it.