The Bit Bucket - Thoughts from Data Platform MVP and Microsoft RD

Learning Mandarin: What is Singles' Day?

What's the biggest retail event in the world now? Black Friday? Christmas? If you didn't answer Singles' Day, read on.

One thing that learning Mandarin has done for me, has been to open my eyes to many aspects of Chinese culture, but it's also made details of Chinese technology and business much more approachable. One curious business phenomena is Singles' Day.

Chinese Singles' Day was also called Bachelors' Day and had its origins back in 1993 at Nanjing University. But Jack Ma from Alibaba really made this fly. He promoted it as a form of anti-Valentines Day. China has many unmarried singles who now have significant disposable incomes, and Singles Day is a day that celebrates their pride in being single. The date chosen was 11/11 (November 11 or as many Chinese say about this event "double one double one" or "four singles").

Basically rather than feeling bad that no-one is buying them something on a day like Valentines Day (which is becoming somewhat popular in China), it's a day where singles spend money on themselves. Yes, true consumerism.

From a business and technology perspective, Singles Day is fascinating. This year, Alibaba made their first billion US dollars worth of sales, in the first 7 minutes.

Read that number carefully. At the time, they were processing over 290,000 sale transactions per second. Nothing else on the planet comes even into the vicinity of that. And they ended up doing over $30 billion USD in sales for the day.

And Alibaba's rival JD.com also did over $20 billion USD in sales.

While you might not have come across Singles Day yet, I suspect that you will. In Australia and several other Western countries, it'll be slower to take off, as 11th November is Remembrance Day. But I am already seeing it happening even in these countries.

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.

Book Review: Exactly what to say: The Magic Words for Influence and Impact

I'm trying a number of different categories of books lately, because I'm getting through far more than previously. One category I thought it would be interesting to pursue where the conversation/influence areas. One that caught my eye was Exactly what to say: The Magic Words for Influence and Impact by Phil M Jones.

This was quite interesting but I'm not sure if I liked it or not.

In this book, Jones takes you through a number of situations, where people often say the wrong thing and make things worse, or where they don't even know what to say, and he gives formulas for how to structure responses that turn the situation back around the way you want, to keep heading to the right outcome.

I have no doubt he's right in what he says.

But having been on the receiving end of many of these discussions, generally as part of fairly strong sales routines, even though it might have helped the seller move in the direction they want, I'm pretty resistant to much of this, and can generally tell when I'm being played or manipulated, and that's what many of these techniques make me feel.

Do I think these types of tactics work well across the broad community? Yes, probably.

Jones has covered quite a bit of ground in the book, and many different situations, and you can tell that he's put a lot of thought into the responses. It might even be a US vs Australian thing, but I still think many would feel very manipulated by the techniques being applied to them. Reading it felt like I was in a highly-motivated selling techniques class, even though the subjects covered were much wider than selling-related ones.

Bottom line: If you deal with the general public and need to find an appropriate way to respond to a variety of situations, you might find this useful. It felt a bit too slick for my liking but Jones is clearly good at what he does.

Greg's rating: 6 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: Set SQLCMD mode for all new query windows in SSMS

SQLCMD mode changes how queries are executed in SQL Server Management Studio (SSMS). When using this mode, you can work with options that aren't normally part of SQL Server T-SQL scripts.

Some installation scripts also require SQLCMD mode and will fail if it's not enabled.

Here's an example of running a query against 3 servers within the same script:

First we open a new query window, then on the Query menu, we choose SQLCMD Mode.

Then I execute the following query:

Note the :CONNECT command is used to connect to another server.

Because everything else works pretty much the same, and you get a whole lot of additional options, you might choose to open all your new queries in SQLCMD mode. That's easy to do.

In Tools, Options, Query Execution, SQL Server, then General, there is a checkbox to enable this.

SDU Tools: List Mismatched Database Collations in SQL Server

Anyone who's worked with SQL Server for any length of time has probably run into collation issues. They will have seen something like this:

SQL Server Error: Cannot resolve the collation conflict

In many cases, it will be a difference between the collation in tempdb and their own database, they've created temporary tables, and tried to join them to tables in their database. And it goes bang !

While you can fix it in the query by specifying a COLLATE clause, it's a pain in the neck. If you use temp tables, you really should have this when working with them so your application isn't dependent upon the server's collation.

But one of the things you might need to check is which databases have collations that are different to the server's collation. In our free SDU Tools for developers and DBAs, we added a procedure ListMismatchedDatabaseCollations to do just that.

You can see how to execute it in the main image above. The procedure takes these parameters:

@ExcludeSystemDatabases bit – Do you want to omit any system databses from this list?
@ExcludeReportingServicesDatabases bit – Do you want to omit any SQL Server Reporting Services databases from this list?

One row is returned for each database.

The columns returned are DatabaseName, DatabaseCollation, and ServerCollation.

You can see it in action here:

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

http://sdutools.sqldownunder.com

Opinion: Corporate Compliance Isn't Training

I spend a lot of time mentoring on client sites, and many of the clients are large organizations. Often these organizations require me to attend "training" on a regular basis, to satisfy their corporate compliance goals.

I don't mind doing this at all, even though the course on conflicts of interest, or handling private or sensitive data, at company A is invariably almost word for word the equivalent course that I do at company B, and company C.

The ones that I really don't like though, are the ones where the corporate IT security is spelled out like it's obvious, and yet I know that what they're pushing doesn't meet any of the current guidelines that have been created from serious research into the topics. For example, the NIST guidelines on passwords would be a good start.

Training should involve learning something.

The vast majority of staff at the organizations wouldn't learn anything from these "courses" and invariably, the questions that they need to get say 80% correct on, are so mind-numbingly obvious, that I see many staff not even paying attention when the videos are playing, and just quickly answering the questions at the end, to keep their managers happy.

But my biggest issue is that for many companies, almost all the corporate training budget is now going to these "courses". My take on this is that the cost of delivering this material should be in a "corporate compliance" budget, not in anything that pretends to be a "training" budget.

SQL: When Performance Tuning SQL Server, Fix the Problem, not the Symptom

I spend a lot of time working with teams of DBAs and developers who need to fix performance problems. One of the real issues that I see time and again though, is people fixing the symptom, not the problem.

Many DBAs spend their time tracing query activity, working out which queries the system is spending a lot of time on, and then working out how to make those important queries run faster.

Automated tuning systems like those being put into SQL Server currently achieve much the same outcome.

But I'd always contend that if that's all you can consider, you are often working on the wrong problem.

I was at a site some years back, where I found a single query that was loading a list of countries, but was being executed 12,000 times per minute and the system seemed constantly busy executing the query.

Some DBAs that I have worked with, would have just ignored the query. Each individual execution of the query took very few resources, and it wouldn't have made it through their filter of queries that need looking at, because it ran fast.

Other (better) DBAs might identify it as an important query because they aggregated the impacts of the queries. Even better if they aggregated the normalized version of the query. (See our free course on doing this if you aren't familiar with it). They would then spend their time working out if they could improve the performance of the query by indexing, looking at wait stats, reducing blocking, and other techniques.

This is all good stuff, but they're all still missing the real problem which is why is the application asking the same question 12,000 times a minute?

That's the actual problem that needs solving.

To really solve performance problems, you need to be prepared to step back, look at the bigger picture, and be prepared to go back into the application code with the developers. It's worth noting that many 3rd party application vendors won't be keen on you doing that, but it's the only way to actually solve the problems.

Now, in this case, it's pretty clear the list of countries isn't changing. (It's a few years since we got a new one in South Sudan).

So why on earth (pun intended) is the application constantly asking for this? Surely it should be caching that data.

And of course, when I checked with the development team, they had been caching the data. What went wrong though, is they had a typo in the caching code. Every time they checked the cache, they thought the values weren't there, so they reloaded them.

That was the real problem that needed to be solved. And no amount of fiddling with indexes, wait stats, locks, etc. was going to have a noticeable impact.

Learning Mandarin: Heating and Cooling Foods

A few days ago, my wife and I visited a local restaurant that we quite like. It's called 秋月亮 (Qiū yuèliàng) or Autumn Moon. The lady who runs it has been very friendly to us. Before our meal, she asked if we wanted anything to drink. My wife asked for water but I asked for water and some tea (green tea). I wanted some water first, then I was going to change to drinking tea.

She looked at me strangely and said "No".

Now that wouldn't happen in most other types of restaurant. "I won't sell you what you asked for off the menu" wouldn't go down well with most managers.

But she immediately looked across at my wife (who is of Chinese appearance) and she said "You know". She was implying that my wife would understand that it was obvious that I shouldn't have a cool drink and a hot drink together. She was worried how this would affect me. It might upset my stomach.

This concept is pretty ingrained in Chinese culture, but generally they aren't talking about things that are actually "hot" and "cold", they are talking about foods that they consider "heating" and those that they consider "cooling".

This comes from teachings on warm and cool foods from traditional Chinese medicine. Ancient Chinese decided that a lot of chronic ailments that our bodies suffer is caused by an imbalance in our internal temperature and energy. They believe this affects our organs.

A whole range of foods is considered to be "warm". Generally they are foods that improve circulation and nourish our energy levels. This is everything from butter, chicken, and prawns, to walnuts and glutenous rice.

Cool foods are considered to "calm" the blood, clears toxins and reduce heat. This would include cheese, apples, bananas, and salt.

There are also a group of foods that are considered "neutral" like beef, milk, figs, and grapes.

Basically from birth, many Chinese are taught what increases or decreases "heat" in their bodies.

Learning Mandarin

Basic Photo Viewer in Windows 10 – Where have you been?

I teach SQL Server, BI, Azure, and AI classes on a fairly regular basis, and one thing I love to do is to show attendees images (or photos) of where the application of the technology has gone very right or very wrong. Ever since I'd installed Windows 10 though, that became much harder.

The Photos app that's installed with Windows 10 must have someone who loves it, but that's not me. There seems to be no way to just have it automatically maximize the images, so I'm always showing them, then having to resize them.

What I wanted is an app where I can double-click an image, and it would display it for me, maximized to whatever screen real estate I had available. The old Windows Photo Viewer did that to a reasonable extend, but the new Photos app in Windows 10 just seemed to have no way to do that simple task. I've seen a lot of articles by people desperate to reinstall the old Windows Photo Viewer. Microsoft has been making that harder and harder and even though you can get it working, it doesn't integrate with other things very well.

So the other day, I figured I'd just have to write an app that did what I wanted but thought I'd check the Windows Store first, and I'm glad I did. There's an awesome app called Basic Photo Viewer, and for those that hate paying for apps, even better, it's free. It has an option to upgrade to Pro but there's nothing in that for me at present.

It worked so well, I was wondering where it had been all my life. Well at least since Windows 10.

I set it as my default program for viewing images in Windows, and when I double-click an image file now, I see this:

There's a nice clean image. If I click the mouse up near the top of the screen, the menu bar appears. The top left has this:

And the top right has this:

There were a few settings that I changed. I chose to hide all pro features (or oddly it lets you set things you can't use), and I chose to have a clean screen by using this option:

The app can also do things like display a slide show by showing all files in a particular folder with a configurable delay, etc. but for me, I just wanted a simple program that displays an image as well as it can, when I double-click the image.

I can't believe I put up with the other app for so long, and didn't go looking for a better one.

Highly recommended !

Shortcut: Set query shortcuts for favorite stored procedures in SSMS

In an earlier post, I mentioned how useful the F1 key is. On its own, it provides syntax help, but when you highlight an object and hit Alt-F1, you get to see metadata about the object.

Under the covers, this just runs the sp_help stored procedure. Alt-F1 has been mapped to that.

You can see where this is configured, change it if required, and/or configure other procedures as well.

In Tools, Options, Environment, Keyboard, then Query Shortcuts, you can see this:

Here we can see how the Alt-F1 command is configured. By default, only three shortcuts are configured. It's easy to configure another one for your own use.

SDU Tools: List Indexes in a SQL Server Database

When I'm working with a SQL Server database, I often need a simple list of all the indexes that are already in place. I want it in a concise form. In our free SDU Tools for developers and DBAs, we added a procedure ListIndexes to do just that, and to provide their details in a form that's easy to consume programmatically if you need that.

You can see how to execute it in the main image above. The procedure takes these parameters:

@DatabaseName sysname – This is the database to process
@SchemasToList nvarchar(max) – a comma-delimited list of schemas to include (ie: 'Sales,Purchasing') or the word 'ALL'
@TablesToList nvarchar(max)- a comma-delimited list of tables to include (ie: 'Customers,Orders') or the word 'ALL'

One row is returned for each index.

The columns returned are SchemaName, TableName, IndexName, KeyColumnList, and IncludedColumnList as shown below: