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 […]
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.
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 […]
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 […]
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, […]
This article only deals with a couple of points so I’ll keep it short. FIRST_VALUE, LAST_VALUE 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:
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 […]