Exponential Moving Average Calculations in SQL Server

Financial systems use a large variety of different analysis functions to identify trends and provide other derivations from periodic price or volume data.  One of these is the Exponential Moving Average (EMA), which provides a price average where more recent periods are weighted more significantly than older periods.  I'm going to demonstrate a way this can be calculated in SQL Server's T-SQL in a set-based way.

The EMA has only one parameter, which is a number of periods from which the weighting value is calculated.

The calculation is:

weight = 2 / ( number of periods + 1 )  [which will yield a value between 0 and 1]

EMA = CurrentPrice * weight + PreviousPeriodEMA * (1 - weight)

    = p₀ * w + EMA₁ * (1 - w)

where p₀ is the price for the current period, EMA₁ is the EMA value for the previous period, and w is the weighting factor.

As each EMA is calculated from the previous EMA, most solutions calculate this iteratively, but it can be calculated in a set-based manner.

Row Generation in SQL Server

While writing complex queries for SQL Server, I have often needed to generate a specific number of records in order to solve a problem in a set-based manner.  At times like this I reach for a trusty Row Generator functions (also referred to as a "numbers table").

Here is a simple implementation of a row generator as a table-valued function:

Certificate Deep Dive - Part 2 - What's in the box?

Continuing our deep dive into certificates, in this part we'll look at the actual certificate files themselves and explore their structure in depth.

Contents

• What's in a Certificate File?
• What About Base64 Encoding?
• How Big is a Certificate?
• How Do I Identify a Certificate File?
• Why Do My Certificates All Start with "MII"?
• What Are OIDs?

Certificate Deep Dive - Part 1 - What's a Certificate?

[Nothing to do with the Operating System at the moment I'm afraid, but this is an article I have wanted to write for a while.]

A few years ago, if you had asked me what technical subjects felt I should know more about, I would have said Time Zones, Character Encoding, and most definitely Certificates, which we'll look at today.

Despite their widespread use and amazing utility, the lack of good information and the obscurity of the tools for working with them lead many software developers to never grasp the benefits of them.  Over the next few posts, we'll do a deep dive into Certificates and attempt to cover off as much information as possible to give a good grounding and reference of what certificates are, why you might want to use them, and much more besides.

So buckle up, everybody, this is going to be a long few articles just because there is a lot to cover.  This first one is broken down into a "Question & Answer" form where the questions should logically follow each other and each adds progressively more detail.  I have tried to ensure that everything in here is correct and isn't misleading, but I can't be held liable for anything you do with this and you're encouraged to do your own research as well.

[Speaking of character encoding, if you want to weep for the species about how little software developers understand the topic, this StackOverflow Question is a disturbing read.]