Shortcut: Finding error locations within queries in SQL Server Management Studio

This is probably one of the simplest tips that I've published, yet I'm endlessly surprised how many people do not realize that it's available.

When you have a script loaded in SQL Server Management Studio (SSMS), and you execute the script, you might run into an error like this:

To find where the error is, just double-click the error down in the Messages tab. I double-clicked it, and it took me directly to the error and highlighted it:

AI: Detecting and Avoiding Customer Churn is Critical

I've flown a lot over the years. What continues to strike me though, is how poorly airlines use machine learning and AI, even when they are in strong competitive environments. A key indicator is detecting and avoiding customer churn. Let me give you an example:

We flew with QANTAS and with their partners in One World for many years. We were both platinum and I'd been platinum for many years. At a recent peak a few years ago, we were flying once or twice a week. That's not a crazy amount, but it's enough. And it's certainly enough to be able to see a purchasing pattern.

We finally got pretty fed up some years back, and one February, we said "enough", and stopped flying with them.

But what fascinated me was how the airline reacted to that.

At the end of the year, I got an email pointing out they'd noticed that I didn't fly as much that year, but because of previous custom, they'd keep my status in place.

After another whole year of not flying, I got another email saying they'd noticed a drop in my custom, and that they'd have to drop me down to Gold. Same thing again the next year.

But at no stage did they ever seek to work out what went wrong.

When could they have actually detected the change? Probably a month or two after we stopped. At that point, there's always a chance you can recover the situation. Every business teacher will tell you how much harder it is to gain a customer than to avoid losing them in the first place, and how very much harder it is to regain a lost customer.

Look at your own businesses, and ask yourself if you have systems in place to detect changes in your customers' behaviors, particularly if they've stopped dealing with you. Don't just detect total volumes over each year. Look for changes in behavior.

 

 

Shortcut: Change the number of rows selected or edited in Object Explorer in SSMS

When you right-click a table in SQL Server Management Studio, you get options for selecting or editing but the number of rows is limited:

Those values can be changed. By default, these numbers are both 200, but I've decided to change the default number of rows selected to 1000.

In Tools, Options, SQL Server Object Explorer, then Commands, you can set the values to whatever suits you:

I don't tend to ever use the Edit option but I'd suggest not making it too large.

SDU Tools: Is XACT_ABORT on in my SQL Server session?

XACT_ABORT is one of the least well understood options that you can configure in a SQL Server session. Yet it's very important. XACT_ABORT makes statement-terminating errors become batch-terminating errors. Without it, even within a transaction, many errors only terminate the statement that they occur in, and control passes to the next statement within the transaction, not out of the transaction.

In nearly every stored procedure that I write, the template includes the following lines:

SET XACT_ABORT ON;
SET NOCOUNT ON;

In our free SDU Tools for developers and DBAs,  we added a function IsXactAbortOn to let you determine in code, if it's enabled.

You can see the outcome in the main image above.

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: Why penguins don't explode and the need for basic research

When government funding is tight, it gets harder and harder to get grants to perform fundamental research. The government always wants to see outcomes, and this means that the grants committees need to show outcomes. In turn, this often leads to research funding bodies doing one of three things:

  • Only funding research that's nearly complete
  • Only funding well-known researchers with a track record of outcomes
  • Only funding research in areas that are already showing promise

Now while at first glance, that might sound a reasonable way to proceed, it's not.

If you only fund research that's nearly complete, you are more likely to get an outcome, but what you are funding is development, not research.

If you are only funding researchers with a track record of outcomes, and in areas already showing promise, you will struggle to get great outcomes.

The best outcomes in science have always come out of left-field. As an example, you might assume that MRIs were developed by medical technologists. However, the work on those came from work by chemists and physicists, based on earlier work by other physicists and an astronomer. None of these people were working on medical technology at the time.

As for fundamental research, one of the best thesis titles I ever saw was "Why Penguins Don't Explode".

Famous image from the wonderful Monty Python's Flying Circus
Famous image from the wonderful Monty Python's Flying Circus

Now before you scoff at anyone proposing a topic like that, it was accepted knowledge for a long time that penguins must only dive down about 20 or 30 metres. But this guy tracked them and found they dived up to 500 metres. He was fascinated by how anything living can dive down 500 metres and not implode, and conversely, how does it come screaming up to the top of the water and not explode. Penguins also don't get the bends.

So how does that work?

We need people noticing these things and researching them. We might have to fund a thousand of these projects to get anything concrete back. But whole new industries can come from the handful that get an outcome.

BTW: I went looking for the final thesis but can't find it at present. My guess is that the academic fraternity made him "tone down" the title of the work.

 

SQL Server Indexing for Developers – new online on-demand training course

Do you need to understand SQL Server indexing in detail? Do you know someone else who needs to? Either way, our new SQL Server Indexing for Developers course could be just what you're looking for.

It's detailed. It has a full set of practical exercises (hands-on labs) with downloadable test data and scripts.

The best part is it's on sale until October 31st for $195 USD as an introductory special.

What Greg will teach you:

  • How SQL Server indexes really work
  • How to design effective SQL Server indexes
  • Why SQL Server seems to be ignoring the indexes that you designed
  • How data type choices affect indexes
  • If filtered indexes would work better in your application
  • How to check whether or not SQL Server "likes" your index
  • How to avoid having too many indexes (and how many is too many)
  • Which indexes you can safely remove

You'll find more info here:

https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

Learning Mandarin: So what's Golden Week?

The biggest holiday for most Chinese is Chinese New Year. It's similar in stature to how Christmas is treated in most western countries. But the second most important is Golden Week.

This post could also be entitled:

Where did everyone in China disappear to this week?

Curiously Golden Week actually happens twice per year. Once is attached to Chinese New Year. The other one is associated with the National Day and that occurs on the 1st of October 1st. Up until 2007, there was a third Golden Week but that is now gone. It was replaced by an extra three public holidays at other times.

While the workers get only three days of leave during Golden Week, things are typically rearranged so that workers usually end up with 7 full days off. If you've ever had business dealings with people in China, you'll realize that it's hard (or impossible) to get hold of anyone during that week.

Most of the ongoing controversy around Golden Week is related to the interruption that it causes to normal business activity.

Somewhat like what happens with Thanksgiving for my US buddies, during this week millions of people travel all over the country to spend time with their families. It's always a hectic period.

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: The 7 Habits of Highly Effective People

While continuing on the path of listening to many older but classic books, I had to include The 7 Habits of Highly Effective People by Stephen R Covey.

I remember first reading this book shortly after it was released. I note that the version I listened to in Audible recently was the 25th anniversary edition. I'd say it's stood the test of time pretty well. An enormous number of copies (over 25 million) have apparently been sold over those years.

This book had an almost cult following when it first appeared. I know people who believed it completely changed their lives. There were also many associated seminars at the time.

Even though it's dated, what the book is full of is moments where you'll be forced to reflect on your life and realize he's talking about you.

I do like the way he included a lot of practical advice on changes that you can start making. It is written in a pretty matter of fact, yet encouraging tone, designed to make you think about the choices you make or have made, how they are affecting you now, and what you might do to regain control.

He was also writing at a more religious time and place, and I don't share his belief of the importance of religion in his life.

This is by no means a perfect book and again some aspects of it haven't weathered the years all that well but it's still full of meaningful insights.

I was glad that I went through it again, all these years later.

Greg's rating: 7 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: XML editor in SSMS and increasing XML output size

Most people use SQL Server Management Studio (SSMS) to edit SQL queries. No big surprise there. The files will have a file type of .sql.

But what many people don't understand is that SSMS inherits many of its underlying Visual Studio's abilities to edit other document types.

For example, if you open a .txt text file, you can edit it just fine, and you can also include files like this in SSMS script projects. That can be useful for additional notes and documentation.

SSMS also knows how to open related file types like .sqlplan (for query plans) and .xdl files (for deadlocks), and more.

Most of these other file types though, are actually XML files with specific schemas constraining their contents. SSMS also contains a perfectly acceptable XML editor.

Here's an example:

If I execute the above query, the outcome is some XML. Note that SSMS recognizes that the output data type is XML and then provides a hyperlink for opening it. If I click on the link, I see this:

The important item to notice here though is the red squiggly on the second line. If we hover over that, we'll see this:

XML documents can only have a single root element. This XML is actually a fragment, not a complete document, and so it thinks that all the stock item lines are all root elements.

The important thing is that this is an XML editor, not just an XML viewer. Notice that when an XML file is open, an XML menu also appears:

Now, while it's not a bad XML editor, it has limits on the size of the data that you can work with, but you can control that too. In Tools, Options, Query Results, SQL Server, Results to Grid, you can see this:

By default, you are limited to 2MB of XML data. You can increase this to unlimited but keep in mind that SSMS is (unfortunately) still a 32 bit application and can struggle to work with gigantic files.

SDU Tools: Is a SQL Server Agent Job Running ?

I often need to write scripts that interact with SQL Server Agent jobs. One thing that I regularly need to know is if a particular job is currently running.

In our free SDU Tools for developers and DBAs,  we added a function IsJobRunning to work that out.

You can see the outcome in the main image above.

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