Sql-Server

I can’t tell you over the years how many times I’ve gone searching for details of a SQL Server build and/or related knowlegebase article. There are some sites that provide much of this, but I’d like to have it in a programmatic form. I can easily then use it in utilities and in my SQL scripts.

In our free SDU Tools for developers and DBAs, we added a view and a number of functions to help with this. The primary one is a view called SQLServerProductVersions.

2019-04-17

I’d love to be running Service Broker classes more often. Service Broker is one of the most powerful, yet least understood technologies in SQL Server. Many of our enterprise clients are using it and loving it.

What we tend to find though, is that we get requests from a number of interested people, spread over time, and locations. But not enough to run in-person classes in those locations.

So we decided to fix that, for this course and for a number of our other courses that have similar demand (i.e. Spatial, Replication, and more).

2019-04-16

If you look carefully at the following two queries in the image below:

you’ll notice there are a couple of differences. In the first query, the word Description is blue, and in the second, it isn’t blue but it has square brackets around it. The second is an example of quoting or delimiting an identifier.

In this case, the reason that the word was blue in the first query is that somewhere in SQL Server, this word is part of the syntax of the language itself. That’s much the same as SELECT or ORDER BY. So SQL Server Management Studio (SSMS) color-codes it the same way it does for the word SELECT.

2019-04-15

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

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

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

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