Sql-Server

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

We’re really excited that our latest online on-demand course is available. If you’ve ever wanted to get comfortable with SQL Server Replication, now’s the time to do it, from the comfort of your own place, and at your own pace.

The course Working with SQL Server Replication is being released at just $195 USD, but for my readers, there’s an extra special offer: Use coupon code REPLAPR19 to get another 25% off, but just till Apr 13.

2019-03-30

Even though I’ve worked with SQL Server for what feels like forever, I still constantly trick myself up with T-SQL commands that give me output that’s different to what I was expecting. This week, it was REPLICATE.

What should the output of the following statement be?

SELECT REPLICATE(‘Hello’, 10000);

If you said the word Hello repeated 10,000 times, or didn’t know at all, read on.

The clue comes if I execute the following statement:

2019-03-29