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:

Opinion: Singular vs Plural Table Names in SQL Server

There is a near-religious debate in the development and DBA communities about singular and plural table names. It's pointless rehashing all the arguments but I want to spell out what I do, and why.

What's driving this post is that I had a developer tell me that I was doing it inconsistently because even though I generally use plural names, that he found a table where I used a singular name. He thought this was inconsistent. It's not, and this is why.

OLTP Databases 

In traditional OLTP databases, I use generally use plural table names ie: my table will be Sales.Customers, not Sales.Customer. This is to make it clear that the table is a set of data about a set of customers, not information about just one customer.

The main people who complain about this are those who think a table should directly relate to an object. Well, it doesn't. If it did, it still would be a Customers object that was a collection of Customer objects. The table would not map to one Customer.

However, all rules are meant to be broken. The one exception that I make to this is for tables that will only ever hold a single row of data ie: where more than a single rows is not logical or meaningful.

So I might have a table called Application.SourceDatabase if that table will always have one and only one row. And I won't consider that to be inconsistent.

OLAP Databases

Dimensional warehouses are perhaps treated differently.  For me, it depends upon whether there will be a view layer above the database, that's used by any analytic system that accesses the database.

I want analytic systems to see human-presentable data.

By human-presentable, I mean names that are ready to go directly into analytic or reporting models. This does mean they will have, for example, embedded spaces in names where sensible.

So I use one of two options for dimensional models:

  • (Less preferred) The database has singular table names, all names have embedded spaces where needed, except for key columns, lineage columns, etc. that are not ever part of the UI. The database can be consumed directly by those analytic applications.
  • (More preferred) The database is set up with the same naming as an OLTP database above, and has a view layer that provides the human-readable data. The analytic applications talk to the view layer, not to the database tables.

SQL: Why can't I see my files in SSMS when I want to restore?

I had a curious discussion on a mailing list the other day where the questioner was complaining that SQL Server Management Studio (SSMS) wasn't working properly. He wanted to restore a backup, and his folders did not appear in the folder list to choose from. He could see them in Windows File Explorer but couldn't see them when trying to restore a backup.

What he wasn't understanding is that when you use SSMS to perform a restore, what you are doing is sending a command to SQL Server to tell it to do a restore. SQL Server is going to open the file and get the contents out of it, not via your local copy of SSMS.

So, the issue is which files SQL Server can see, not the files that you can see.

This perhaps becomes clearer if you think about SSMS as just a client application that sends commands to a server, and that server often won't be on the same machine ie: you'll have a client system running SSMS and a server somewhere that's running SQL Server.

When you run Windows File Explorer, it's your identity that's doing the looking, either on your local machine, or on network shares on other machines.

When you restore a backup, it's SQL Server that's looking for the files, either on the server that's running SQL Server or again on some network share.

The issue then is the identity of SQL Server and the permissions granted to that identity. If you run SQL Server Configuration Manager (one of the tools that ships with SQL Server) on the server, and select the Services option in the left-hand pane as shown in the main image above, you can see who SQL Server is running as. That's the identity you need to be concerned about.