The Bit Bucket

Ever since about version 17.3 of SQL Server Management Studio, I’ve had problems with fresh installs (as opposed to upgrades). I’ve been unable to set the fonts and colors. When I go into that dialog, I see the list as shown in the main image above.

All the normal option for windows that you can make settings for aren’t there.

On my current machine, I had just installed a v18 SSMS and it was fine. All font options, etc. were there. But I had to install v17.9.1 of SSMS side-by-side because the ssbdiagnose tool was missing on my machine. (I needed it for our new Service Broker course and v18 doesn’t install it for some reason).

2019-04-11

If there is a reasonable number of tables in a SQL Server database, and I’ve also got foreign keys linking them, it can be difficult to work out the order of the dependencies. That’s a hassle when I want to load data, and for other admin functions that I might need to perform. So we’ve fixed that.

In our free SDU Tools for developers and DBAs, there’s a stored procedure called CalculateTableLoadingOrder.

2019-04-10

In our free SDU Tools for Developers and DBAs was an AddWeekdays function. Now that was for T-SQL. Recently though, I needed to do that for Power Query. While the M language has a wonderful set of date-related functions, it didn’t have this one.

That made it time to write one. Here’s the code that’s required:

So how does it work?

Forgive the formatting to fit this window, but let’s take a quick look through it:

2019-04-09

I think T-SQL is an odd language in many ways. One aspect of this is the looseness of the language. The best example of this is the way that statement terminators (i.e. the semicolons at the end of the statements) are optional.

Back in 2005, the optionality of the statement terminators was deprecated. The SQL Server team told us to start using them, because one day they’ll be required.

2019-04-08

I like serious investigative journalism that’s released as podcasts. I enjoyed Serial with their story about Adnan Syed, and in a similar vein there’s now The Drop Out from ABC Radio.

I’m surprised that I hadn’t heard about Elizabeth Holmes or Theranos, or at least that if I had heard about them, I hadn’t taken much notice of them. After listening to The Drop Out, I’m stunned that it hadn’t been something I already knew all about.

2019-04-05

If you go to drop a database, and you’re told you can’t because it’s being used for replication, you might be a little confused. For example, if you check your publications and subscriptions, you might see this:

Given there aren’t any, how could the database be being used for replication? Well, each database has a property that indicates if it’s enabled for replication, even if there aren’t any publications. That’s what’s stopping you from dropping the database.

2019-04-04

In our free SDU Tools for developers and DBAs, we have a large number of useful functions. The topic for today is two simple ones: StartOfMonth and EndOfMonth.

SQL Server 2012 added a function EOMONTH. It returns the end of the month date for any given date. I really, really don’t like abbreviations like this. There is no need to save three characters.

I asked why it wasn’t ENDOFMONTH but was told that EOMONTH was the name of the function in Excel. It’s sad to think that’s the guiding principle for T-SQL function names. I also don’t get why sometimes the words in the names are separated by underscores, and other times they aren’t. Surely END_OF_MONTH would be more readable.

2019-04-03

Over the years, many product names have become verbs that describe what the product does. The typical example is to google for something, or to super-glue something to something else, and so on. The first and dominant product in their markets tends to become associated with the action that they perform.

But was has me puzzled in recent years, is I keep hearing company names used as nouns for something that their applications deal with.

2019-04-02

The most basic form of SELECT statement that we used to get information out of a table is the word SELECT followed by a list of columns or expressions that we want, and then details of where we need to get them from. Typically, that’s a table.

The basic syntax of a SELECT statement in SQL Server T-SQL is as follows:

Committees are curious things, and I can’t say that I always agree with their outcomes. I suspect if I’d been designing this, I would have had:

2019-04-01

Yesterday I had the pleasure of recording another new podcast on a SQL Server 2019 topic. Jakub Szymaszek is a PM on the SQL Server team, working around security.

In this podcast, Jakub and I discussed the current state of security and encryption in SQL Server, starting from cell-level encryption (CLE) and transparent database encryption (TDE) in SQL Server 2005. We then discussed the introduction of Always Encrypted and Data Masking in SQL Server 2016.

2019-03-30