SDU Tools: Retrust Foreign Keys and Check Constraints in SQL Server

While doing my consulting work, I often come across Foreign Keys and Check Constraints that are not trusted. What this means is that either:

  • They were created without checking existing data
  • They were disabled for a period and re-enabled without re-checking the existing data

I like to see this fixed whenever possible. To make that easy, in our free SDU Tools for developers and DBAs, we added two stored procedures to help: RetrustForeignKeys and RetrustCheckConstraints.

Both procedures take three parameters:

  • @DatabaseName – the database to work in
  • @SchemasToList – a comma-delimited list of the schemas to include or the word ALL (which is the default)
  • @TablesToList – a comma-delimited list of the tables to include or the word ALL (which is the default)

You can see these two procedures in action in this video:

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

http://sdutools.sqldownunder.com

 

Book: The Happy Mind

Over the years, I've been really interested in what makes people happy in life. I'm always fascinated by people who think that wealth, products, properties, the latest handbag or car, new partner, etc. will make them happy. I'm sure the media has tried to tell them that, but it's never been true.

I've had friends all colleagues all across the wealth spectrum, and I can say without any doubt in my mind, that some of the richest people I know are also some of the most unhappy. Worse, I've seen money destroy families so many times.

Now I'm not trying to say that poverty makes you happy either. It can lead to serious consequences. Debt can be worse. I've always advised family and friends against debt wherever possible.

If you are in debt, you have no control over your life.

Apart from money, what else?

I know though that money isn't the only issue around happiness. I've often spent a lot of time thinking about this issue, and why people keep chasing things that won't make them happy anyway, be it new material possessions, a new partner, or whatever. They spend their lives thinking that if they just get that next thing, life will be better. Here's a hint: it probably won't be.

I have always admired my friends that live simple happy lives.

I was intrigued to see a new book called The Happy Mind: A Simple Guide to Living a Happier Life Starting Today by Kevin Horsley and Louis Fourie. It's published by Tck Publishing.

Kevin Horsley (@KevinHorsley on Twitter) is an interesting guy. He writes quite a bit on brain techniques and memory. I'm not as interested in his topics on how to remember long lists of things (like the list of US presidents), although I'm sure some people would love that. But I was fascinated to see a book dedicated to thoughts on just how to be happy.

Louis seems to write on a wide variety of topics, from this to paleo to economics in South Africa.

The book is easy to read. It has a discussion on happiness right up front and defines what that looks like. Then for comparison, it dives into what unhappiness really looks like in practice. There are many, many good lessons in those sections.

I don't share his religious views (although they were only lightly mentioned) but I particularly liked the discussion on how pleasure isn't happiness, yet it's often mistaken for it.

And then the book breaks into a significant number of short discussions on a whole range of happiness-related topics.

Bottom line?

I enjoyed reading this book, and many times, it made me stop, pause, and think. That's all I can ask for in a book like this. I enjoyed it and I think it contains many great life lessons.

Greg's rating: 8 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).

T-SQL 101: #15 Using column and table aliases in SQL Server queries

Look carefully at the following queries:

I've shown several examples here of what are called aliases. There are two basic types of aliases.

Column Alias

In the first query, I've used a column alias. I wanted to query the Description column in the dbo.Products table but I wanted the column called ProductName instead. So I follow the column with the word AS and then the name I want to use for the alias.

The word AS is actually optional but I really like using it to make it clear what's going on. I've seen many instances where someone just leaves a comma out in a list of columns, and one column name becomes an alias for the previous column name.

The second query is an example of where you really do need to use aliases. I have selected an expression 2 + 3 and without the alias, there would be no name for the column that was returned. If you're just reading it on the screen, that might not matter, but generally, I'm writing queries that are sent by programs and I need a column name on the data that comes back. It's also useful even if all you're doing is copying the results into a program like Excel.

Now there are two ways that these aliases can be written. Instead of writing:

2 + 3 AS CalculatedValue

I could have written:

CalculatedValue = 2  + 3

I don't love using aliases that way but I have some pretty clever friends who wouldn't write queries any other way. They write the column list with column names one under the other, and they like the way that this form would put all the aliases directly under each other.

It's also possible for you to write this:

'CalculatedValue' = 2 + 3

But don't do that. Same for using double quotes around the name. If you have to quote the name for some reason such as it's a reserved word, or it contains spaces, write it this way:

2 + 3 AS [Calculated Value]

or

[Calculated Value] = 2 + 3

Table Alias

In the third query, I've shown an example of a table alias. Tables can be aliased just like columns can. I tend to do this now, pretty much all the time.

The basic argument for doing this is that if you have more than one table in the query, it can be pretty confusing to work out which column in a SELECT list is from which table. You could always put the table name in front of each column but that's long and painful. Instead, just alias the table, and use the table's alias in front of the column names.

OK, so multiple tables makes sense, but why would I do that for a query with just one table? Well, the first bonus is that Intellisense works great when you do that. If I type p then hit a period, I get a list of columns for just that table. And chances are that sometime soon, someone will change my query to add another table anyway. So I'd rather just have aliases right from the start.

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: Will not Will ??

One aspect of Chinese that I love is how direct much of the language is. There is a particular pattern when this is really apparent.

We might say "Will you go or not?".  The Chinese pattern is:

你会不会去?(Nǐ huì bù huì qù?) (bù) is basically "not".

This is literally "you will not will go ?"

It's a pattern that I should be using far more often than I do, but it doesn't come as naturally to me.

有没有?(Yǒu méiyǒu?)

This means "do you have it?" but literally translated is "have not have?". (méi) is also pretty much "not" in this case.

"Is it or isn't it?" or perhaps even just "is it?" becomes:

是不是?(Shì bùshì?)

Again, this literally translates comes to "is not is?"

And sentences that are formed like this don't then need to end with a questioning word. This is where I still go wrong often. I sometimes write this like:

它是我只狗吗?(Tā shì wǒ zhǐ gǒu ma?)

Literally, this is "it is my dog" followed by the particle (ma) which tends to make a sentence into a question.

Instead, I should be writing the cleaner:

是不是我只狗?(Shì bùshì wǒ zhǐ gǒu?)

As a note, in that sentence (zhǐ ) is a measure word for dogs.

Lastly, Chinese has other question particles but (ma) is the most common. It's similar in importance and usage to (ka) in Japanese.

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: Why we get fat and what to do about it by Gary Taubes

Weight has been an area of interest for me for a very long time, given my struggles with it. All throughout the 1980s, I wish I'd known what I do know now. Gary Taube's book Why we get fat and what to do about it was a seminal work in this area, helping to remove the nonsense that's been peddled as "science" and "medicine" for decades.

I was already pretty much across most of Gary's work before I listened to this as an audiobook, but it helped me to see where much of his thinking had come from.

During the 1970s and particularly in the 1980s and 1990s, anyone with a weight issue was told to do three things:

  • Eat a bit less
  • Exercise more
  • Avoid fatty foods

They also had seemingly simple messages like it's all about "calories in vs calories out".

If you still think that's the answer to weight issues, you are part of the problem.

That's never been a solution, and it's led to an amazing variety of nonsense ever since it was discussed. Yet every time someone "failed" at doing this list of things, the answer wasn't "oh the list of things must be wrong", the response was that "that person just didn't have enough willpower", and to tell the person "it's your fault".

Weight is the only illness that's not treated properly because it's so easy to blame the victim. And usually, they're being blamed for not sticking with the advice that was never going to work. Lack of willpower is blamed but it makes no sense at all. Many overweight people have enormous willpower; I know many with far more willpower than the average person. So saying that is convenient, but simply not true.

It's also one of the last remaining legal forms of discrimination. Need to get on a plane with a wheelchair? No problems, we have lots of ways to help. Need a bigger seat? Oh, can't you just eat a bit less and exercise more?

But what about fat?

Fat has never been the problem, yet it was demonized by an entire industry, determined to keep you eating low-fat, sugar-laden, non-foods. When scientists saw clogged arteries present with heart attacks, etc. they presumed that the clogged arteries were what led to the heart attacks in the first place. But they needed to look deeper.

I've heard this described as going to house fires, always seeing firemen and firewomen there, and concluding that these firemen and firewomen must be what causes the fires in the first place.

It's nonsense. Always has been.

Even when people were pointing at the French and saying "how come they eat lots of fatty things, yet they don't get fat?", the answer kept coming back "oh but they just eat so much less of it". It never dawned on them that the fat was what stopped them eating too much in the first place.

And they'd argue that the Inuit people who were eating predominantly whale blubber, yet had amongst the lowest heart attack rates in the world, had to just be an anomaly.

The problem is everywhere though. Why is a latte with low-fat milk still often referred to as a skinny latte when it's more likely to cause you a weight problem than to fix one?

It was a similar deal with cholesterol. My father's generation was told to stop eating foods containing cholesterol because people had high cholesterol readings. The logic was that if you ate it, you'd increase your level. Seemed logical but again, that wasn't true either. Dietary cholesterol (i.e. the stuff you eat) pretty much goes right through you and isn't absorbed.

And exercise?

Exercise makes you feel great. No question. It's great for your heart and for your mood.

But if you are doing exercise to lose weight, you're pretty much wasting your time.

A bit of simple mathematics will show you how much exercise you'd have to do to remove how little excess food.

Check out gyms nowadays. They are all making very sure they don't claim that you'll lose weight if you attend. Because they know it's not true. They see the evidence every day.

Gary's Book?

Gary's book was one of the earlier works to really get stuck into the nonsense that has been peddled for so long. Too long, don't want to read?

Simple message: get rid of the carbs in your life

I can't tell you how much I wish I'd known that thirty years ago. I've seen so many people with so many issues, and so let down by the advice they received over those decades. Worse, many governments and doctors are still pushing those non-answers. I can't decide if it's because that's what they learned back in medical school (best case), or if it's because they are now ashamed to admit they were so wrong.

Bottom line?

I have so many friends whose lives I've seen completely transformed by doing this, not for some short-term fix that quickly gets undone, but real transformation.

This is one of the books that can make the core issues sink in.

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).

Fixing locking and blocking issues in SQL Server – Part 2 – RCSI

This is part 2 in a series of posts:

  • Part 1 covered being sure there is a locking and blocking issue

In part 1, I talked about pseudo locking and blocking issues that come from having queries running too long. I said that before you start fixing any locking and blocking issues, that you first need to make sure your queries are running fast. Today I want to discuss RCSI.

Lock escalation issues

Some queries, however, just need to run for a long time.

Awesome image by JC Gellidon
Awesome image by JC Gellidon

Most of the time, I find this with reporting queries. They're just doing a lot of work.

The problem is that even in the default isolation mode (read committed), SQL Server takes shared locks. When it gets to 5000 of these locks, it tries to escalate to a table lock. (Strictly, it might be a partition lock but that's a topic for another day)

And that's where the fun begins. Many people have had the experience of working with large transaction tables; someone's running a report down the end of the table and someone else is trying to modify the beginning of the table and gets blocked. Suddenly, you can't even insert current data.

No the reaction that people had to this in SQL Server 2000 days, was to just put NOLOCK on the queries. But that's not a great solution. You are basically giving up consistency for performance. It can lead to scenarios that are incorrect, and hard to explain.

Ever since SQL Server 2005, so many of these issues would be better fixed by using snapshot isolation level. Changing that in a session means changing the application, so for many customers, that's not helpful.

One possible solution

Instead of changing the application to use snapshot isolation level, SQL Server provides a database option called read-committed snapshot isolation (RSCI) that changes any read-committed queries into read-committed-snapshot queries i.e. it automagically applies snapshot isolation to read-committed queries, but just for the duration of the queries.

I've lost count of how many sites I've been to where just applying that database option fixed an enormous number of blocking issues.

What it means is that when you go to read data, and someone else is modifying it in a transaction, you see the data as it was before the transaction started, instead of waiting. Generally, that's awesome.

So why isn't it already on?

If it's such a great option, you might be wondering why it's not already turned on. Basically, that's for backwards compatibility. Some applications (I've seen very few of these) depend upon the old behavior. I'd suggest that these would be pretty badly written applications.

When we start new database applications today, we turn on RCSI for the database before doing anything else.

The other reason that it's not a default value is that it is going to slightly increase the impact on tempdb. Generally, we've not found that to be an issue.

In Part 3 of this series of posts, we'll move on to trickier situations.

 

SDU Tools: Using T-SQL to find operating system versions, locales, SKUs, and configuration

When I'm writing utility scripts for SQL Server, I often need to make decisions or report on, details of the operating system that I'm running on. Unfortunately, SQL Server doesn't have views that return this sort of information.

In our free SDU Tools for developers and DBAs, we added a series of views to help.

OperatingSystemConfiguration has details of OperatingSystemRelease, OperatingSystemName, ServicePackLevel, OperatingSystemSKU, OperatingSystemSKUName, and LanguageName.

OperatingSystemVersions has details of OS_Family, OS_Version, and OS_Name.

Ever wondered what locale 1033 means? OperatingSystemLocales has details of OS_Family, LocaleID, and LanguageName.

OperatingSystemSKUs has details of OS_Family, SKU, and SKU_Name.

We periodically check the list of these within Windows and keep it up to date.

You can see two of the views in the image above and also see the views in this video:

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

http://sdutools.sqldownunder.com

 

Another new online course: Protecting SQL Server Data with Encryption

We all see news articles talking about how company after company exposed private details of their clients, customers, and sometimes, even staff. In so many cases, a reasonable approach to encryption would have avoided these issues. Time and again though, I see people doing dodgy work on protecting their data; some even trying to roll their own protection.

Don't do this!

Encryption is one of the most important yet least understood technologies in SQL Server. It's improved so much over the years and 2019 makes it even better.

If you are a DBA or a database developer and you don't know your way around the encryption options in SQL Server, it's time to learn.

We've now made it easy. Take our new online and on-demand course: Protecting SQL Server Data with Encryption.

In no time, you'll be discussing and implementing encryption like a pro. You'll get a refresher on encryption terminology and concepts, then drill in keys, certificates, cell-level encryption, Transparent Database Encryption (TDE), extensible key management (EKM), and Always Encrypted (in 2016) and with secure enclaves (in SQL Server 2019).

And if you get into it by May 17th, you'll be able to use coupon code ENCRYPTNOW to get another 25% off the brand new course.

And like all our courses, we have video instruction, hands-on-labs with downloadable resources, quizzes to check your learning, and a certificate at on completion.

You'll find it, and our other courses here:

https://training.sqldownunder.com

 

 

 

T-SQL 101: #14 Using two part names for SQL Server tables and other objects

If you look carefully at the following simple query:

you'll notice that I didn't just say FROM Cinemas, I said FROM dbo.Cinemas. The "dbo." part is the name of the schema. I talked about schemas in an earlier post. And all through these T-SQL 101 blog posts, I keep mentioning that you should always use the schema name as well as the object name.

By why?

Image by Ken Treloar

The first reason is that whenever you write T-SQL scripts of any type, you need to be precise. When you say just Cinemas, SQL Server has to work out which Cinemas table you mean.

Now you might think that you only have one. But SQL Server doesn't know that when it starts to run your query, and it has to go to look to see if there are others.

Every user has a Default Schema associated with them. When you just say Cinemas, SQL Server first looks to see if there is a Cinemas table (or view) in your default schema. If it doesn't find one, it then looks in the dbo schema. That's why it seems to find it just fine when you simply say Cinemas.

Small as it is, why have it doing this extra work?

A worse situation is if you're writing a query to create the table. If you just say Cinemas, the table would be created in the default schema of the person running the script, not necessarily in dbo. That's not reliable query writing.

Stored Procedures and other objects too

Exactly the same issues relate to stored procedures. If you say:

EXEC SomeStoredProcedure;

How does SQL Server know if that's dbo.SomeStoredProcedure or Sales.SomeStoredProcedure ? Sales might be your default schema.

And the same issues arise when you create stored procedures. You want to write scripts that reliably create them where you want them.

But wait, there's more

A further complication is around query plan caching. If SQL Server caches a query plan for accessing the dbo.Cinemas table (once it's found it), and you run a query that's for just the Cinemas table, it doesn't already know if it can use the same query plan, until it works out which object you're talking about.

In many places in the T-SQL documentation, you'll find you're advised to use two-part object names. They're not joking. For these and other reasons, please just get used to doing this.

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: Counting Aunties

My wife is of Chinese descent. She actually speaks a Chinese dialect (not Mandarin). Locally, that's often called Teochew but it's better known to Chinese as 潮州话 (Cháozhōu huà which means tidal region language), or perhaps 潮汕话 (Cháoshàn huà).

I'm (very) slowly learning some Teochew. Even though it's typically written as Teochew, when I hear speakers of it pronounce it, the name sounds more like "der jill".

Counting aunties

When we were getting married, I noticed the long list of aunties and uncles that were coming to our wedding. I remember asking her if they were all closely related. What fascinated me was that my wife said yes, but when I asked what their names were, she referred to them by number i.e. Auntie Two, Aunty Three, and so on.

That had me quite puzzled and fascinated.

It's because the way we refer to aunties and uncles with their given names (i.e. Auntie Jane, Uncle Tom), is considered very rude in Chinese culture. Aunties are ranked in order and one of the big concerns my wife had was making sure to refer to the correct auntie with the correct number. Getting that wrong would also be rude.

The Chinese word for Auntie is: 阿姨 (ā yí)

But not all aunties are really aunties

It's important to understand though that when you refer to Auntie in Chinese, it doesn't necessarily mean that they are related to you. It's a general term used for women older than you, and that you respect.

Many English speakers do the same. I grew up with a woman I called Auntie who lived next door to us but was quite unrelated. So I guess that's pretty much the same.

I also grew up using the spelling Aunty rather than Auntie but I did an online check and apparently, Auntie is now by far the most common spelling so that's what I've used here.

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.