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

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

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

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

Sql Server CONTEXT_INFO with Numeric Values

A trigger takes no arguments but it is possible to use CONTEXT_INFO() to make 128 bytes of binary data values accessible to it. I wanted to provide an 8-byte (bigint) value from a sequence which the trigger would write to a primary key column.

Storing and retrieving a numeric value in CONTEXT_INFO is not difficult, but it’s another thing that doesn’t come up very often. For this type of problem I like to find concise explanations with source code so that I can get back to the main problem quickly. Once again my searches didn’t turn anything up so I’m trying to fill the gap with this post. Continue reading

Notes on the OVER Clause in Sql Server Ranking Functions

This article only deals with a couple of points so I’ll keep it short.

FIRST_VALUE is a very useful ranking function and works predictably. LAST_VALUE is also useful but may give you surprises. You can ignore LAST_VALUE if you want, and just re-use FIRST_VALUE: Continue reading

Complex T-Sql Column Expressions? You must try this!

There’s an outstanding video by Kendra Little called “5 T-Sql Features You’re Missing Out On” which you can reach from my Links (Topical) page under “Database > Transact-Sql”. It describes a special way of using the CROSS APPLY statement in T-SQL. This is a technique that can change the way you write Sql, or at least it can in cases where you have an expression that is used in several columns of a SELECT statement. Continue reading