Book Review: The Second Machine Age

One of my colleagues Orin Thomas is a prolific writer. I've lost count of how many detailed books he's written, and I've no doubt he's lost count as well. If you've worked in Microsoft-related IT for any length of time, I'm sure you'll have read one of his books, particularly if you've been involved in certification. You can see a partial list of his books here.

But the other thing that amazes me about Orin is that he's also a prolific reader. I can't believe how many books he gets through, and he's inspired me to get through way more. I have not the slightest doubt that being a good reader is a prerequisite for being a good writer. Every time I meet with Orin, he mentions books that I should read. I note them down, and slowly make my way through many of them.

Based on my interest in where I think society is heading, one of the books that Orin suggested was The Second Machine Age: Work, Progress, and Prosperity in a Time of Brilliant Technologies by Erik Brynjolfsson (Author), Andrew McAfee.

I grew up in an age where I wasn't worried at all about the future job market, and in the future of work itself. But the work and study that I've been doing in recent years has changed that. I really do see a period of major social disruption coming. I think if you're not seeing that, you're not really looking.

Whenever we've had disruption in the past, the scale has been much, much lower. "We don't have massive pools of unemployed scribes" is a comment that I often hear. But the disruption that I see coming is very different to what we've had in the past. Today, in my country, there is a major upset if a couple of hundred people lose their jobs at a car plant, even though that was entirely predictable since about 1992 when the government set the industry on a new direction.

In the future though, I can see single decisions sidelining a million people at a time. We're just not ready for that.

This book is an excellent source of material for you to consider. It goes through economic data and positions where technology is and where it's taking us. It argues that we're in the middle of a second era of staggering innovation, and that it will affect us even more than the first machine age did.

After convincing us of how amazing all the technology is, the authors then discuss how the increased prosperity is only shared by a small percentage of the population. I'd argue that it's heading towards a minute percentage. The prosperity won't be spread across the community, and particularly not to the people at the lower economic levels.

Bottom line?

This book is interesting and challenging and I'm glad Orin suggested it to me. I have a deep interest in these things, and this just helped add to that.

Greg's rating: 8 out of 10

Note: as an Amazon Associate I earn 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 🙂

Shortcut: Missing index details in SQL Server Management Studio

I've mentioned before that SQL Server Management Studio (SSMS) is a good tool for analyzing queries, as much as for executing them.

In SQL Server 2005, query plans had missing index details added. When a query plan was created, SQL Server recorded that it thought it could have executed the query better, if only you'd provided it with appropriate indexes. But at that point, the suggestions weren't very good, and the tools didn't show them.

In SQL Server 2008, the suggestions got better (eg: we weren't endlessly having suggestions to create non-clustered indexes for every column in the table), and SSMS now showed them clearly.

Here's an example query with an issue:

SQL Server thinks it's doing this work the hard way. Note that it suggests an impact of over 97% on running this query, just because an index is missing.

To find out what it wants, we right-click in the white area of the query plan:

The option of interest is Missing Index Details. When we click that to open it, the following appears:

Overall, the suggestions that this system makes aren't bad. They are far from perfect but if you don't know much about SQL Server indexing, these suggestions might make a good start. Just don't blindly follow large numbers of them. Someone with strong SQL Server skills can often come up with better suggestions than those currently offered by the tools.

Also, make sure you rename the index. I have come across indexes at customer sites where the index name is [<Name of Missing Index, sysname,>]. I wish I was joking.

Learn about indexing

Want to learn more about indexes? Take our online on-demand course now:  https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

SQL: Cursor types in SQL Server

When I'm running SQL Server Advanced T-SQL classes, we spend time discussing cursors. The general message is that most of the time, but not always, cursors are the wrong answer. The problem with cursors is that instead of telling SQL Server what you want it to work out (ie: declarative query), you are telling it how to work that out (ie: procedural query).

You'll hear people say to never use cursors. This is also incorrect. There are occasions where I do use cursors. Mostly this is when I'm building utilities that do things like scripting objects in the database. I'd almost never use a cursor though, in general data processing running against SQL Server. You don't want to be doing row by row processing against the server.

When we first review many systems, a strong code smell is lots of temporary tables and cursors. That tends to indicate that the author hasn't really understood set-based logic.

But if you have a situation where cursors are needed, it's important to know how the different types work. The different types of server cursor are:

Forward-Only Read-Only

This is the most basic type of cursor. When you open it, SQL Server executes the query and starts throwing data in the direction of the client as soon as it can. The client navigates by just asking for each row, one at a time. There's no option to go backwards, or to change the data (at least by using the cursor to change it).

Static

This option tells SQL Server to execute the query, throw a copy of all the data that it found into tempdb, and send a pointer back to the client. The client can then move forwards or backwards in the data, and it doesn't matter what other users do to the data, the client is using its own copy and is unaffected. This means that it doesn't suffer from Halloween-type issues. This is heavy work for the server and doesn't scale well.

Recently I saw a fellow MVP complaining about Halloween-type issues with other cursor types but thought he couldn't use Static cursors because he read that they don't support updates. It's true that you can't use a WHERE CURRENT OF clause to modify the data through the cursor, but there's nothing stopping you using a standard UPDATE based upon the primary key value if that was retrieved as part of the data.

Keyset

This is a lighter form of cursor. SQL Server executes the query, puts a copy of the keys into tempdb, and returns a pointer to the client. Much less data is going into tempdb. The client can then request forward or backward movement along the cursor, but each time they request data, SQL Server has to go to the original data to find the values to return, based upon the stored keys. If another user adds additional data, they won't see it because they already have the list of keys. If another user deletes data though, the client can have a problem. If they ask for the next row, the key might be in the cursor but the data might no longer exist. The client has to deal with that scenario. (This can cause errors on clients that don't cater for errors). Because this cursor is connected to the original data indirectly, it's considered an updatable cursor.

Dynamic

This cursor is the lightest from the point of view of the server. All that's stored at the server is the query and a pointer as to where it's up to. When the client asks to go forward, it looks up the next row and returns it. Same for backwards. The challenge with this type of cursor is that the client is exposed to all the vagaries of impacts from other users. As other users add or delete rows, interesting outcomes can occur. For example, you could be on one row, step forward once, then step backwards once, and not be on the same row that you started with, because someone else added or deleted a row. It's also an updatable cursor type.

 

 

SDU Tools: Show a number as text in SQL Server

Years ago, it was common to need to print checks (cheques) from computer systems. Fortunately checks have now pretty much disappeared in most countries. One of the challenges with printing a check, was that you needed to convert an amount of money into words. Even though checks are on the wane, it's still important to do that on some contracts, etc.  In our free SDU Tools for developers and DBAs,  we added a function NumberAsText to do just that.

You can see how to execute it in the main image above.

We decided not to assume that it's a monetary value, just a number, so you could easily turn it into a monetary value like this:

The text is returned in lower-case. It's easy to then use UPPER to force it to upper-case:

You could easily add a comma in between the dollars and the cents as well:

You can see it in action here:

We hope you find it useful.

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

http://sdutools.sqldownunder.com

Opinion: Get used to reading traces and logs before you need them

I used to do a lot of work at the operating system and network level. I was always fascinated watching people use network trace tools when they were trying to debug a problem. The challenge was that they had no idea what was normal activity on the network, and what wasn't.

The end result of this is that they'd then spend huge amounts of time chasing down what were really just red herrings.

When you don't know what normal activity looks like, everything looks odd.

Today, I see the same thing with traces of SQL Server activity, either using SQL Profiler (and/or SQL Trace), and Extended Events Profiler. I also see the same thing with insights data sent to Log Analytics, and the outcomes of many expensive SQL Server monitoring tools.

For example, if you are looking at a SQL Server trace, and you see a large number of sp_reset_connection commands. Is that an issue? When would it be an issue, and when is it just normal?

If I see an sp_reset_connection executed on a connection followed by a number of other commands, I know that the application is using connection pooling. If however, I see a bunch of those on the same connection, without any commands executed in between, I know that the application code is opening connections when it doesn't need to. Perhaps it should be opening the connection closer to where it decides if it needs it.

The key point is that it's really important that you learn to use these tools before you have a problem. You need to be able to recognize what's normal, and what isn't.

 

SQL: Fields and columns, what's in a name?

Old PC style databases and tools (like Access and DBase) and most 4GL databases tended to use the words Record and Field to describe the layout of tables. Relational SQL databases use the terms Row and Column. I endlessly hear discussions about if there's a difference, and where that lies.

Some will argue, for example, that a field is a data value within a single record or row. That would make a field more like a cell in Excel, than a column in Excel.

My take on this is that if I hear someone use "Record" and "Field", I don't lose the plot and want to correct them. I know what they meant, and that's all that matters.

The one exception to this though, is in training.

If you are teaching people to use SQL, please don't use Record and Field and instead use Row and Column. Using standard terms when teaching is important.

Learning Mandarin: Loan words can be tricky

In English, we often "borrow" words from other languages. The Chinese call these "loan words". For example, we might talk about a feeling of Déjà vu. Now we'll often write it without the original accents on the "e" and the "a" but we'll happily just use the French word in conversation.

Ironically, the French tend to do the opposite. They keep inventing words to fill in the gaps in their language. I've heard that this is causing them great difficulty in technical words and that their language academy is a long, long way behind in creating those words.

In Mandarin, a similar thing happens. And it's one of the things that confuses me when I'm reading Mandarin. Let's look at an example:

夏洛克  (Xià luòkè)

I normally read the first character as being related to summer. The second character is often a surname. It's also an old name of several rivers in Henan, Shaanxi, Sichuan, and Anhui. The third character is typically used for the word gram.

So when I read this, I'm thinking "summer, some name, gram". And it's only after a while I pronounce the whole thing and realize that it makes no sense at all as written. It's all about how it sounds. In this case, it was meant to be Sherlock (as in Sherlock Holmes).

I find names quite tricky to recognize to start with, but these transliterated words are especially tricky. But they are used extensively. It's not always names though. Here's another:

巧克力  (Qiǎokèlì)

Again when reading this, the words are like "skillful, gram, force or energy". But the word is chocolate.

And another:

酒吧  (Jiǔbā)

In this case, the first word is Chinese for alcohol, but the second word means bar in English.

So when reading Mandarin, and the words seem to make no sense at all together, one of the things I'm learning to do, is to step back and pronounce them, just in case the meaning of the characters isn't relevant, and only how they sound matters.

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.

Using the classic editor in WordPress 5.0

Well today WordPress on my blog site went up to version 5.0. I knew a new editor (Gutenberg) had been coming to replace the classic editor but I hadn't had time to try it. So when it did the upgrade, I happily let it go and install the new editor.

Then I tried to write a post.

Oh my goodness, that was just a horrid, horrid experience. I can see what they've tried to do but it literally took me about five times longer than normal to write a single post.

I actually like change. In fact I tend to thrive on it. I can even imagine how this might help build certain types of pages better. But for someone writing blog posts with a heading, a bunch of text and images, etc. I can't imagine what they were thinking.

Each paragraph has become a "block" and I kept finding the pop-up block headers getting in the way the whole time I'm editing. Perhaps the people who love this don't touch type, or they always write pages in order and don't jump around but I can't tell you how annoying it was.

Worse, it kept deciding that I needed new lines when I didn't ask for them. I'd put the cursor beside an open bracket on a line, click Control-V, and then find it had inserted a newline before the pasted data. I was endlessly editing out things that it pushed in.

And so on and so on.

I kept looking for the "how to get rid of the new editor" posts but the best option now seems to be to use the Classic Editor plugin. Let's just say that's been a godsend. And given the number of installs it already has, I don't think I'm alone on this one.

Recommended !

Book Review: Will It Fly? by Pat Flynn

I'm a fan of Pat Flynn. If you haven't listened to his Smart Passive Income podcast, and you have any interest in being self-sufficient without "working for the man", Pat's podcast would be a good start. Pat has people ask him about ideas though and he's put his ideas on how to work out if an idea is worth pursuing in his book: Will It Fly? How to Test Your Next Business Idea So You Don't Waste Your Time and Money.

People often have what they think are great ideas but they don't know if they really are good ideas or not. The lousy situation is where they then invest a great deal of money building/creating something, only to watch it fail miserably in the market.

Pat's book is designed to help to avoid that.

Pat is a thought leader in this part of the market. And he's done very, very well from his work. Ironically, it all started because he lost his "real" job. In this book, he helps you through the steps of what to do before launching (or particularly before investing heavily) in your business idea.

Entrepreneurship is hard, but it can also be very rewarding. That's a scary concept for many. So often, people live from pay to pay, or have such heavy commitments, that they feel they really have few options.

I can't say that I miss doing a "normal" job and I can't imagine ever wanting to do one again, no matter what happens. I wish this book had been available decades ago. It would have saved me a lot of time and effort along the way.

Bottom line?

This book won't help you decide if you should be an entrepreneur. But if you've decided to do so, this book is great material for anyone looking to start a business, launch a product, or even change tack in their current business.

Greg's rating: 9 out of 10

Note: as an Amazon Associate I earn 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 🙂

Shortcut: Compare query plans in SQL Server Management Studio

One of the advantages of SQL Server Management Studio (SSMS) is that it can be used to analyze queries, not just to execute them.

There are two basic types of query plan: estimated execution plans, and actual execution plans.

For a typical query, I can obtain the estimated execution plan, by hitting Ctrl-L, choosing the option in the Query menu, or clicking on the toolbar icon:

Let's do this for the following query:

We get the following plan output:

Now we might look at this plan and wonder if we exerted control over how the joins were performed, if we'd improve things. Perhaps we'd heard that merge joins were more efficient and thought SQL Server should have used those.

Now we can change the query like this:

Notice I've added the word MERGE between INNER and JOIN in this query. But how do we know what SQL Server thinks? We can get another estimated query plan but what we really want is to compare them.

When we obtain an estimated plan for multiple queries at once, SSMS shows us a comparison of the two, by showing us the proportion of the overall query for each part. (Perhaps we shouldn't force that change 😊)