Tricks in T-SQL and SSAS

This past weekend saw the first SQL Saturday in Melbourne. Numbers were good – there were about 300 people registered, and the attendance rate seemed high (though I didn’t find out the actual numbers). Looking around during the keynote, I didn’t see many empty seats in the room, and I knew there were 300 seats, plus people continued to arrive as the day went on.

My own session was fun. I’d been remarkably nervous (as I often am) beforehand, particularly as this was a talk I hadn’t given in about 3.5 years. There were elements of it that I teach often enough, but it was more about the structure of the talk, which ends up being so critical to how things end up working. I may give the impression of talking completely off-the-cuff, but I do have most of it thoroughly planned – the lack of slides and firm agenda is primarily there to allow me to have flexibility to match the audience.

Anyway, as my demos were coming together, I found myself putting ‘GO’ between various lines, so that my CREATE statements didn’t get the red squiggly underlines by the SSMS window. I find it kinda frustrating when I’m just going to be running individual statements, but nevertheless, it’s good to avoid the squiggles. But of course, GO isn’t part of T-SQL, and I thought it was worth mentioning it. I think only one person in the room (a former student) had heard me explain this before, so it worked out okay. And it fits in nicely with this month’s T-SQL Tuesday, which is on the topic of “Dirty Little Tricks”, and hosted by Matt Velic (@mvelic).

So I’m going to show you two tricks, which are essentially harmless, but also help demonstrate potentially useful features of SQL Server – one in T-SQL, and one in Analysis Services.

The T-SQL one, as I’ve already mentioned, is about the GO keyword, which isn’t actually part of T-SQL.

You see, it’s a feature of SQL Server Management Studio, and of sqlcmd, but it’s not really a database engine thing. It’s the batch separator, and defines the point at which a bunch of T-SQL commands should be separated from the bunch that follow. It’s particularly useful for those times when the command you’re issuing needs to be the first command in the batch (such as a CREATE command), or even issued completely by itself (such as SET SHOWPLAN_XML ON).

…and it’s configurable.

This is the SSMS Options dialog, and you’ll see an option where you can change GO to be something else.

I had thought at some point that you could change the Batch Separator to just about anything else, and then create a stored procedure called ‘go’, but of course, if you have more than one statement in your batch, then you must use EXEC to run a stored procedure. So hoping that ‘go’ might run a stored procedure by appearing at the end of your batch doesn’t work. Besides that would be a BadThingToDo.

Proper mischief involves changing it to a keyword, such as CREATE, DELETE or SELECT. If you make it SELECT, then all kinds of things will stop working in SSMS, and every SELECT query will come back with “A fatal scripting error occurred. Incorrect syntax was encountered while parsing SELECT.” Well, for new windows at least.

The point at which it becomes really annoying for your unsuspecting colleague is that restarting SSMS only makes it worse. The setting is stored in C:\Users\YourName\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin (the 12.0 means 2014 – it’s 11.0 for SQL2012), so even if you think installing a new copy of SSMS will fix it, it won’t.

Sadly for you, reader, if they do an internet search, they’ll find posts like this one, and they will quickly realise who inflicted this pain on them.

The other trick that I thought I’d mention is with SSAS translations, and again, demonstrates a nice feature of SQL Server.

One rarely-used feature of Analysis Services (Multidimensional) is Translations.

I say it’s rarely used, but if you have an environment that needs to cater for multiple languages, then you could well use them. They allow someone who has different language settings on their client machine to see localised data, dimension names, and so on. It’s really quite clever, and can be very influential in gaining acceptance of a system that must be used throughout all the worldwide branches of your organisation.

But where you can have fun (so long as it doesn’t go into production) is when you have someone on your dev or test team who is originally from a different country (but with the same language), and likes to have their computer set to their home language. Like someone in Australia who likes to use English (New Zealand), or who likes to have English (Canada), despite the fact that they’ve been living in the United States for some years.

The trick here is to introduce a translation in the language that they choose to use. They’ll be the only person who will notice it, and you can go as subtle or as blatant as you like.

In the editor for the Cube file, you will see a Translations tab over on the right. It lets you enter the words in that language for the various concepts. So you could throw in the odd “eh” for Canadians, or mix up the vowels for Kiwis.

Once you get into Dimension translations, you have so many more options! You can tell the data within attributes to come from a different column, even one that you’ve only made up for the DSV. That means that the reports they see can throw in the odd reference to hockey, or hobbits, or whatever else you might decide is appropriate to mess with their heads. Of course, when they see the report having the wrong names for things, they’ll tell someone else to fix it, but there won’t be anything to fix. It’s almost the ultimate “Doesn’t work on my machine” scenario, just to mess with that one person who doesn’t have their language settings the same as everyone else.

…but please don’t let either of these go on in production. The last thing you need is to have someone think SQL is broken in production, or to have someone think you’re racist, when you’re just picking on New Zealanders.

@rob_farley