SQL: What's in a (default) name?

I often see people creating databases in SQL Server and not specifying the name of defaults they are applying to columns. They define a column like this:

And there are general reasons why this makes sense. For example, a column can only have one default, so what does the name matter anyway?

There are two reasons:

Dropping columns

In SQL Server, you'll find that if you go to drop either of those columns, you'll see something like this:

SQL Server requires you to drop the default constraint on the column before you can drop the column, and unfortunately it requires you to do that by name. Notice the name that it chose for the default: DF__Customers__First__3A81B327. Life is far, far easier if you have a pattern that means you already know the name that will have been applied.

DevOps and Database Comparisons

As part of DevOps or other techniques, you'll end up wanting to compare two databases to find what's different. While some tools help with this, having the same table definition create defaults with different names isn't going to be helpful in this.


It doesn't matter too much what pattern you use for the names. I'd use this one:

I use the schema name, the table name, and the column name. That can't go wrong or end in duplicates. And I already know the names of my defaults, and they're easy to generate programmatically.


Keep in mind that this is a SQL Server specific thing. In other languages like PostgreSQL, you can't apply a name to a default constraint. But you don't need to because column defaults are automatically dropped when the column is. (No idea why SQL Server doesn't just do this). And you can drop a default with the DROP DEFAULT clause to ALTER TABLE without needing a name for the default.

These are things that SQL Server should copy but until they do, name your default constraints.


Book Review: Blockchain – by Samuel Rees

Another book I've read recently while sitting on a few planes is Blockchain – by Samuel Rees.

I've seen some big claims in the titles of books but this one had me intrigued:

The Ultimate Beginner Through Advanced Guide on Everything You Need to Know About Investing in Blockchain, Cryptocurrencies, Bitcoin, Ethereum and the Future of Finance

That's quite a claim. I was really hoping this book would provide a great amount of detail given it's 'beginner through advanced' guide claim.

That's not what I found though. While it might be a useful book if you'd never learned anything at all about Blockchain, I thought the overall discussion was pretty shallow and there was a whole lot of "gee whiz how amazing is this" types of messaging that I really didn't enjoy.

I did persevere to the end though, as I was hoping there was more coming. What I did find was a bunch of info on the author's views on investing in cryptocurrencies. While he's careful to avoid straight-out telling you to invest, the tone is certainly that you should do so.

While Blockchain as a technology is still a strong option, when I read the book, I'd have to say I wasn't in love with the idea of investing in cryptocurrencies. Given how the majority have now tumbled and/or become extinct, and the way that even Bitcoin and Ethereum have plummeted lately, I'm glad I thought that way.

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

Shortcut: Code outlining in SQL Server Management Studio

For some years now, SQL Server Management Studio (SSMS) has had the ability to use code outlining, the same way that other Visual Studio applications can.

This can be very useful when you are trying to navigate around a large script file.

The simplest usage is to collapse or expand a region of code. Note that in the following script, code regions have been automatically added by SSMS:

This allows us to click on the outline handles, and collapse the code:

Note that when the region of code is collapsed, the name of the region is shown as the first line of the code within the region, truncated.

If you hover over the ellipsis (the dot dot dot) at the beginning of the code region, you'll be shown what's contained within the region:

Now, what's missing?

I'd love to be able to just drag regions around.

I'd also love to be able to name the regions better. It's not too bad if the regions are procedures or functions but for other chunks of code, there's really no good option. Note that if I add a comment immediately above the code, it's not part of the same region. It might be better if it was like that, or if a specific comment could be treated as a region heading:

In the Edit menu, the Outlining submenu doesn't show anything else useful at this point, apart from bulk operations:

SDU Tools: DatesBetween – all dates between two dates in T-SQL

In our free SDU Tools for developers and DBAs, we have added many tools that help to manipulate dates.

When creating a date dimension (as part of dimensional modeling), you need to be able to get a list of all the dates between a start date and an end date. There are many other reasons why you might need to do this as well.

So we've added a table-valued function called DatesBetween to do just this. It takes a start date and an end date as parameters and returns all dates between. As well as the date values, it also numbers each of the dates.

In the main image above, you can see an example of it in use.

You can also see them in action here:

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


Opinion: DIY security is not security

I spend a lot of time working in software houses. One of the nastiest things that I see again and again and again, is developers attempting to roll their own security and authentication mechanisms.

Spend a moment and think about how many security incidents the big companies (Google, Apple, Microsoft, etc.) have had over the years. Now think about how much effort they've put into doing it right, yet they still have issues at times.

The scary part about trying to do this yourself is that you often don't even know how scary what you are doing is.

Apart from the ones who do a reasonable job of password hashing, etc. I also see a surprising number who still store plain text passwords, or think that applying some "special algorithm that they wrote" to "encrypt" passwords or other private information is acceptable.

It's not.

I cringe every time I see someone who's written a algorithm that does obfuscation on a value before storing it. Worse is when they refer to it as "encryption" within the organization.

So my post today is just a simple plea:

Please don't do this.

The minute you find yourself writing "encryption algorithms" or authentication code, just stop. Just because you think you've got away with it for years, don't tell yourself that you don't have an issue.

I've seen the outcome at sites where this all goes wrong, and it's not pretty. You do not want to be anywhere near it when the finger-pointing starts. It all ends in tears.

Image by Tom Pumford
Image by Tom Pumford



SQL: More on finding rows that have changed using HASHBYTES and FOR JSON PATH

In a previous post, I wrote about how to determine if a set of incoming values for a row are different to all the existing values in the row, using T-SQL in SQL Server.

I later remembered that I'd seen a message by Adam Machanic a while back, talking about how FOR JSON PATH might be useful for this, so I did a little more playing around with it.

If you are using SQL Server 2016 or later, I suspect this is a really good option.

Here's an example of using it to hash all the rows from the Purchasing.PurchaseOrders table in WideWorldImporters:

If you're not familiar with CROSS APPLY, it's basically used to call a table valued function (TVF) for every row of a source table. In this case, the source table is Purchasing.PurchaseOrders and for every row in that table, we're calling the code below. It's not really a TVF but acts as one. It can return a table of data for every incoming row from Purchasing.PurchaseOrders.

In this case, it's being used to get all the data in the row, and then push it all into JSON. The INCLUDE_NULL_VALUES option is a key trick, as it ensures that NULL values aren't ignored. Here's how it would look without the hashing:

Now, note that I've used po.* here to get all the columns in the table. Because the hash itself would also be stored in the table, and we don't have any syntax to say "all columns except one", we'd likely have to list all the columns here, to avoid including the hashed value in the hash calculations.

After using the hashing though, you can see the output in the main image above.

If you are working with SQL Server 2016 or later, this could be a good option. Thanks to Adam for suggesting it.



Learning Chinese: Does learning to read and write matter?

I have a lot of Asian friends, and one thing that's always surprised me is the number who can speak, but who cannot read or write. They are perfectly fluent speakers of their language, as they learned to speak from their parents and families, but they never learned to read and write.

So when I decided to learn to speak Mandarin Chinese, I was wondering if I should spend the extra effort to learn to read and write.

I'm really glad that I decided to learn.

Before I started to learn, I hadn't realized that even though there are many dialects of Chinese (over 900 and I'll talk about them more another day), that they pretty much all use the same writing system. They have different words in the different dialects but they typically have the same character with the same meaning. (Note: There are some exceptions to this like Simplified vs Traditional characters which I'll also talk more about another day).

This means that learning to read Chinese characters (Hanzi) is even more valuable than you might have realized.

Let's see an example:

This is a character that means "fire". In Mandarin, it's pretty much pronounced somewhat like "haw". In Pin Yin (which is basically used to write the Chinese characters in our alphabet), it's written as Huǒ. The symbol over the "o" is indicating that the character is pronounced with the 3rd tone (a falling then rising tone).

You'll also notice that this pictograph does somewhat show fire. Not all characters are literal drawings like this though. Importantly, if you were speaking another dialect, you would still recognize it as "fire".

A Cantonese speaker would call this fo and pronounce it somewhat like "fore". Rather than using strokes to indicate the tone, it might be written as fo2 with the 2 indicating the second tone. (Tones are different in the different dialects too).

Bonus – Japanese

At high school (many, many years ago), I spent 5 years learning Japanese. Later in life I spent some periods working in Japan. While I could cope with basic greetings, and getting around town, I always was hampered by my inability to read anything complicated. I knew my Hiragana, Katakana, and could use Romaji, but I didn't know enough Kanji characters to get by. Kanji characters are derived from Chinese characters, and are often the same.

A big surprise for me when I've been learning to read Chinese was that I can now look at a large number of Japanese signs and know what they mean. I haven't got the slightest idea what the word is in Japanese, but I can often understand the signs.

As an example, for the word fire above, Japanese basically uses the same character. They call it ka though.

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: Now I Know – Dan Lewis

One book that a number of my friends suggested that I read is:

Now I Know: The Revealing Stories Behind the World's Most Interesting Facts – by Dan Lewis

I wasn't sure what to make of this book as it just seemed to be a large collection of facts that Dan thought were interesting. Given I love trivia, I thought I'd try it.

Dan started an email list called "Now I know" back in 2010 with a handful of subscribers and grew that to over a hundred thousand. These are basically the topics that ended up being some of the most interesting.

I actually quite enjoyed the book. Some of the early topics captured my attention immediately. For example:

  • Where did the ampersand get its name from?
  • What actually happened to the flags on the moon?
  • Did Germany actually take over a Canadian city during the second world war?
  • Why did the UK intentionally plant a body to wash up on the shores of Spain in the second world war?

Anyone who loves a bit of trivia will get a kick out of something in this book. After each fact is discussed, Dan then has a bonus fact that's associated with the main fact. Some of those were quite fascinating.

The only downside I found with this book, as with many US books, is that there is a heavy US balance to the history and facts included. I'm not sure that the rest of the world has quite the fascination with the US Civil War, etc. that those in the US do.

I'd recommend this book but my rating depends upon where you live.

Greg's rating (if you live in the USA): 7 out of 10

Greg's rating (if you live outside the USA): 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.

Shortcut: Manually prompting for and refreshing Intellisense in SSMS

Intellisense is one of the best things that's ever been added to Visual Studio or to SQL Server Management Studio (SSMS). It's hard to remember back to before it was added, or how we worked then.

I had a young friend from the United Kingdom who had just completed a Computer Science degree and one of the things that he was most proud of, is that he knew so many HTML tags and which attributes went with which tags. When I showed him HTML Intellisense in Visual Studio, I think he was about to cry.

While Intellisense in SSMS pretty much works as expected, there are a few things that can go wrong to confuse it.

The first problem is that it caches the list of objects to make sure it can perform quite well. But the cache can get out of sync with reality. A common cause of that is if I execute T-SQL commands in one query window, and I'm using those same objects in another window.

I've seen people quite puzzled about this but it's easy to fix. On the Edit menu, you can see that the shortcut key to refresh the local cache is Ctrl-Shift-R:

So if you see a bunch of unexpected "red squigglies", first thing to do is to hit Ctrl-Shift-R.

Another thing that can happen is that you get into a situation where the prompted values won't appear. If I type the following code:

note that the Intellisense has appeared. But if I hit the Escape key, it will disappear again. So users wonder how to get it back. Now one option is to backspace over the period, then type the period again. The standard option though, is to hit Alt-RightArrow.

An alternative to this is to hit Ctrl-Space, and that's easier to hit anyway.

SDU Tools: Analyze SQL Server Table Columns

I spend a lot of time reviewing other people's databases. Apart from looking at table designs, data types, keys, etc. I'm often interested in what the typical data in the table looks like.

In our free SDU Tools for developers and DBAs, we have added a special tool called AnalyzeTableColumns that makes this type of review easy. You point it at database, schema, and table, and it tells you about the table.

In the main image, you can see what it outputs for the Warehouse.StockItems table in the WideWorldImporters database. That image is truncated at the right hand side. Here are the other columns:

Now that data is somewhat like what you get from sp_help. But I often want more. I want to see what the typical data in the table looks like. So optionally, it'll show you what they look like too.


You can also see it in action here:

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