Simplify Reconciliation Code with Filtered Indexes

Some hand-matching of internal/external records may be needed even in an automated reconciliation. The matches will be stored in a database. Rules on matching can be enforced in the application, but there are often good reasons to place them in the database. In Sql Server, a filtered index (with a WHERE clause, in this case excluding Nulls) on the table can make this easier and more correct. Continue reading Simplify Reconciliation Code with Filtered Indexes

Sql Server Filtered Index as a Unique Constraint on a Specific Value

Indexes often double as constraints. Each combination of values must only appear once among the columns of an index defined as UNIQUE. The “filtered” index allows some refinement. Suppose that in one column you won’t allow a specific value to appear more than once, but you don’t mind other values being duplicated.

A good example would be the IS_LATEST_VERSION column in a data warehouse table. Continue reading Sql Server Filtered Index as a Unique Constraint on a Specific Value

Forgotten Features of Sql Server

Well, not literally – these are features I tend to overlook because I don’t often need them. If you’re the same, this may be helpful. It isn’t a complete list (I’ve forgotten the others) but I’ll try to add to it as things come to mind. Continue reading Forgotten Features of Sql Server

Manager’s List of Potential Cloud Computing Risks

In Manager’s List of Potential Cloud Computing Benefits I promised to write up a list of risks, so here it is. The question probably isn’t “should I use cloud services or not?”, but “which activities and data would be safe to migrate to the cloud?”. You’ll have thought of most of these; if you see something new, writing this post has been worthwhile. Continue reading Manager’s List of Potential Cloud Computing Risks

Sql JOIN Cheat Sheet

Someone accidentally found my Sql Collation Cheat Sheet while searching for a “Sql JOIN Cheat Sheet” so I thought I’d knock out a post on the topic. Towards the end, you’ll find a script that demonstrates them. The script was run in Azure Sql. Some joins may be Microsoft extensions to the ANSI standard. Look at the documentation for fuller information.

When I was a beginner I had thought there was a deep meaning to the words “left” and “right”, as in LEFT OUTER JOIN. It was a mild let-down to find out that it only refers to placement of the table name in relation to the “JOIN” keyword; switching a LEFT OUTER to a RIGHT OUTER join defines the table named after the join keyword as the outer table. Continue reading Sql JOIN Cheat Sheet

Manager’s List of Potential Cloud Computing Benefits

This is a list of potential cloud computing benefits and where they might touch on your organisation(As I’ve mentioned previously, I only work with the Microsoft cloud, but the principles should apply generally).

Of course, you should have another list – the risks, and consider what that list tells you about the trade-offs you would have to make. That’s for another blog post. Continue reading Manager’s List of Potential Cloud Computing Benefits

“Ready, Fire, Aim” at Cloud Computing

A thirty-year-old management consulting slogan plus the arch-buzzword of tech? What’s not to like?

I do think the phrase is apt though, not just because doing something practical is the best way to start cutting through the hype and confusion around cloud computing. It’s also because “Ready, Fire, Aim” suggests that another shot is on the way; in other words a cycle which will continue until something is achieved. But what? Continue reading “Ready, Fire, Aim” at Cloud Computing

Manager’s First Look at Cloud Services

First a disclaimer – I don’t have an good general view of the market since I only use Microsoft Azure (see my reasons at the bottom of the blog). The points I make here should be relevant to any service you are considering though.

The best way to get started is to get started. “What, commit the organisation to a technology I don’t understand?” No, before you make any commitment, do the reconnaissance, in a pincer movement. Continue reading Manager’s First Look at Cloud Services

Raising Errors in Transact-Sql

There seems to be quite a bit of confusion about the way errors should be raised in newer versions of Transact-Sql. The documentation for RAISERROR says: “New applications should use THROW instead.” It doesn’t say that RAISERROR is deprecated; when you look at the list of deprecated features what you find is that invoking RAISERROR without an error number is now deprecated, but it’s unclear whether the command itself will go. Continue reading Raising Errors in Transact-Sql

%d bloggers like this: