Stored procedures in Cosmos DB are JavaScript functions that run close to the data to ensure high performance. In addition to great performance, stored procedures provide multi-operation transaction support with automatic rollback when things don’t go as planned. In this episode, we’ll look at the server-side Collection and Response classes to learn how to create and update… Continue reading Cosmos DB Server-Side Primer – Episode 1
Category: Database
Shape CosmosDB Output with JMESPath
According to James Saryerwinie, his truly awesome query language for JSON is pronounced James Path. I trust him on the pronunciation of JMESPath. Besides, when I used to say “Jay-mezz Path,” it just sounded wrong to me. I fell in love with JMESPath using the Azure CLI. Many of the Azure CLI commands support a… Continue reading Shape CosmosDB Output with JMESPath
Generating Cosmos DB Auth Tokens in Postman
If you read the official article that describes how access control works in Cosmos DB, you’ll see this handy Node.js function for generating authentication tokens using a master key: var crypto = require(“crypto”); function getAuthorizationTokenUsingMasterKey(verb, resourceType, resourceId, date, masterKey) { var key = new Buffer(masterKey, “base64”); var text = (verb || “”).toLowerCase() + “\n” + (resourceType… Continue reading Generating Cosmos DB Auth Tokens in Postman
Schema Refactoring with Views
Database triggers are generally pretty awful. Anyone who has had to deal with a database heavily laden with DML triggers knows this and avoids using them. If anyone tells me that they must use a trigger, I can always prove that there’s a better way that doesn’t bury dependencies and business logic so deeply in the database.… Continue reading Schema Refactoring with Views
Generating Filenames Dynamically in SSIS
A file’s name and location are often used to express what’s inside it. Filenames are not required to be meaningful to human beings but they often follow some sort of pattern for categorizing and describing the data inside them. In this way, we can think of the name of a file as being somewhat like… Continue reading Generating Filenames Dynamically in SSIS
On Primary Key Names
If you use frameworks like Microsoft Azure Mobile Services or Ruby on Rails, then you’re accustomed to complying with a host of development conventions. Frameworks are often said to be opinionated, forcing certain design decisions on the developers who use them. Given that very few software design choices are perfect in every situation, the value… Continue reading On Primary Key Names
Invoking Stored Procedures from Azure Mobile Services
One of the questions I’m often asked is whether it’s possible to call SQL stored procedures from Azure Mobile Services. The answer is yes and it’s probably easier than you think. In case you don’t know, Azure Mobile Services is a way to very simply expose an HTTP service over data stored in an Azure… Continue reading Invoking Stored Procedures from Azure Mobile Services
Change Data Capture and Change Tracking Deep Dive
Here are the slides and scripts from my Change Data Capture and Change Tracking Deep Dive talk that I’ve been delivering at various conferences and user groups recently. PowerPoint Presentation SQL Scripts The CT script depends on database setup steps in the CDC, by the way, so run through that one first.
Earth Surface Distance in T-SQL
A few years ago, I was working on a project where I needed to calculate the distance between pairs of points on the Earth’s surface. With a bit of research, I found an implementation of the haversine formula written in Python on John D. Cook’s Standalone Numerical Code site. Given a pair of latitude and… Continue reading Earth Surface Distance in T-SQL