Business-Intelligence

Everyone who’s worked with SQL Server for any length of time, has had the experience of executing a T-SQL script, and then noticing, with horror, that they’ve just executed the script against the wrong server. You know the feeling. It even happens at Christmas time, just when you were hoping to get away from work for a few days, or when you are the unlucky one who’s doing on call work.

2017-12-28

When you first start working with SQL Server, you’re often working with smaller databases and every backup seems to happen quickly. SQL Server is fast at creating backups. When you get to larger databases, backups start to take a while. Fortunately, backup is one of the commands that sets a percentage complete value that can be seen in the sys.dm_exec_requests system view. And when the databases get even larger, the question quickly becomes:

2017-12-27

I work with a lot of SQL Server databases that are poorly normalized. One of my pet dislikes is the column to rule them all. Here are simple tests: If I ask you what’s stored in a column and you can’t tell me a single answer , then you’ve got a problem. If you need to refer to another column to work out what’s in the first column, then you’ve got a problem.

2017-12-26

Over the years, I’ve had a surprising number of questions on how to right-align a set of numbers using T-SQL. The first concept that people seem to miss is that numbers aren’t strings. So there’s no concept of actually aligning a number, it’s only a string representation of a number that can be right-aligned or left-aligned. Or how it’s displayed in a client application. But if you really want to create a string that has right-aligned numbers, then left padding of the number is what you want.

2017-12-22

Intrusions into computer systems are happening all the time now. We need to address this issue as an industry, but it’s important to understand that the way we design databases plays a big role in the impacts that occur during intrusions. If you don’t accept that you could have an intrusion, you are living in La La Land. (See https://en.wikipedia.org/wiki/Fantasy _prone_personality) A bug in any one of the frameworks that you use, the code that you write, the protocols that you use, the operating system or hosting services that you use can potentially expose you to an intrusion.

2017-12-19

There were a number of new T-SQL functions introduced in SQL Server 2012. As I’ve mentioned before, I get excited when there are new T-SQL functions. Some, I’m not so excited about. EOMONTH was in that category, not because of the functionality, but because of the name (wish it was ENDOFMONTH), and lack of symmetry (lack of a STARTOFMONTH or BEGINNINGOFMONTH). One that I thought was curious was CONCAT. I thought “why on earth do we need a function to concatenate strings.

2017-12-18

SQL Server databases have a reputation for being hard to test, or at least hard to test appropriately. For good testing, and particularly for unit tests, you really want the following: Database in a known state before each test Database containing large amounts of (preferably masked) data (production-sized) Quick restore after each test before the next test For most databases, this is hard to achieve. The restore after each test means that a normal database restore can’t be used.

2017-12-14

I’ve mentioned that I work on a lot of financial systems. Masking and trimming the production databases so they can be used in development is important, yet I see many issues with masking. A few months back, I was working on a SQL Server database from a large financial client, and the database was supposed to have been masked. However, what they missed was that the XML metadata associated with images stored in the database still had the actual client’s details.

2017-12-13

I’ve mentioned that I love it when SQL Server gets new T-SQL functionality. A useful function that was added in SQL Server 2016 was STRING_SPLIT. You can see it in action in the main image for this post. It’s another function that is great as far as it goes, but when I was a kid, this would be described as “nice try but no cigar”. It works, and it’s fast, so what’s missing.

2017-12-06

I’ve pointed out in previous blog posts that I’m not a fan of ORMs. What I’m even less of a fan of is code-first design, particularly in combination with an ORM like the Entity Framework. It might seem cool and shiny and if you are just whipping up a small proof of concept or test app, please feel free to do it, but the minute you think of doing it for enterprise systems, just don’t.

2017-12-05