SQL Server

MACD in T-SQL

The MACD (Moving Average Converenge Divergence) is a calculation in technical analysis used as a indicator of strength in a trend, or momentum in a stocks closing prices. The calculation uses different lengths of EMA (I covered the calculation of EMA in an earlier blog post here).

The MACD calculation uses the difference between a long EMA and a short EMA to create a oscillator (usually EMA12 – EMA26). The term Moving Average Converenge Divergence comes from the converging and divergence of these two moving averages. When the two moves towards each other there is convergence, and when they move away from each other, there is divergence. A divergence is commonly interpreted as a sign that the current trend is ending.

The MACD also moves around a zero line and when the MACD is above the zero line, that is used as a indicator of upward momentum (higher closing prices) since the short term EMA is above the long term EMA. And of course, if MACD is below the zero line this is an indicator of downward momentum.

Image by Kevin Ryde
Image by Kevin Ryde. Green line is MACD, red line is EMA9 of MACD.
White histogram is the difference between the two.

Further, a EMA9 is calculated for the MACD. This line is called the “Signal line”. The signal line is used in a trigger for buy and sell signals. More specifically, traders look for crossovers of the two lines. When the MACD moves over the MACD:EMA9, this is a buy signal since it indicates a upward momentum (a bullish market) of the closing prices. And when the MACD crosses under the MACD:EMA9, this is a sell signal since this is a indicator of downward momentum (bearish market).

This blog post will show how to calculate MACD in T-SQL. It works on all versions of SQL Server.

(more…)

Continue ReadingMACD in T-SQL

Exponential moving average in T-SQL

Exponential moving averages are similar to weighted moving averages in that they assign less weight to changes long ago, and more weight to recent changes. Weighted moving averages are linear, but exponential moving averages are exponential. That is, the weight can be expressed as a curve:

Weights in EMA
Weights in EMA

There is a great way to calculate exponential moving averages in T-SQL using an undocumented feature about variables and running totals in SQL Server. In this blog post I will show how to use that method to calculate exponential moving average in T-SQL, but I will also present a method that is using standard features in SQL Server. Unfortunately, that means using a loop.

(more…)

Continue ReadingExponential moving average in T-SQL

Weighted moving average in T-SQL

In an earlier post I demonstrated the calculation of moving averages in T-SQL. However, the there is one major drawback with simple moving averages. Price changes in the beginning of the period have the same importance as more recent price changes. Somehow you’d like to assign different weights to the price changes, so that the most recent changes gets the most weight.

For this purpose, a Weighted Moving Average (WMA) can be calculated. In this blog post I will show two different methods to calculate WMA, one that can be used on SQL Server 2005 and later, and the other version is for SQL Server versions earlier than 2005.

Weights WMA9
Weights WMA9

(more…)

Continue ReadingWeighted moving average in T-SQL

Moving average in T-SQL

A common calculation in trend analysis is the moving (or rolling) average. A moving average is the average of the, for instance, last 10 rows. The moving average shows a more smooth curve than the actual values, more so with a longer period for the moving average, making it an good tool for trend analysis. This blog post will show how to calculate moving average in T-SQL. Different methods will be used depending on the version of SQL Server.

(more…)

Continue ReadingMoving average in T-SQL

Line numbers in t-sql error messages

When you run scripts in SSMS and get an error, the error references a line number in the t-sql statement that caused the error. If you double click the error, SSMS will take you to the error. This works even if you have multiple statements in your query window. So it can be a really helpful feature. But if the error is in code that isn’t in the query window (for instance in a referenced stored procedure), nothing happens when you double click. In that case it can be good to know how SSMS calculates the line number in error messages.

(more…)

Continue ReadingLine numbers in t-sql error messages

Create or Replace

Blog series: SQL Server Connect Items

How many times have you been forced to change a “CREATE” statement to “ALTER” because “There is already an object…”:

Msg 2714, Level 16, State 3, Procedure XXX, Line 1 There is already an object named XXX in the database.
Msg 2714, Level 16, State 3, Procedure XXX, Line 1 There is already an object named XXX in the database.

And then you need to change it back to “CREATE” in the script, or else it won’t work when deploying. This will happen if you try to issue a “ALTER” statement on a object that does not exist:

(more…)

Continue ReadingCreate or Replace

Rowlock can block more than a row

To avoid the blocking of other users, rowlocks are sometimes used to create locks on the most granular locking level in a table. In theory, when user A makes changes to a row, user B can read, change or delete any other row than that. But in practice, user B can still become blocked by user A altough they are not accessing the same row. It all depends on how data is accessed.

(more…)

Continue ReadingRowlock can block more than a row

Missing comma between columns in select statement

Hopefully, when you make an typing error while writing a SQL statement, you get the red curly lines under the offending part of the query showing you where the error is. If you execute the following statement, you’ll get an error message:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'FROMTABLE'.
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘FROMTABLE’.

However, some typos doesn’t create an syntax error. Instead it alters the meaning of the t-sql statement in an perhaps unwanted way. Check out the following example.

(more…)

Continue ReadingMissing comma between columns in select statement

Zero footprint Server/Database DDL Trigger

Aaron Bertrand has written a great introduction to server/database DDL triggers in his article SQL Server DDL Triggers to Track All Database Changes.

I wanted to implement similar DDL triggers on more or less all servers. Some of the databases on those servers were from a 3:rd party, so changes in those databases wasn’t possible. Therefore I wanted to make sure these DDL triggers were “zero footprint”. That is, not causing existing database code to fail. I simply added TRY and an empty CATCH to the trigger, believing I’d be safe. Unfortunately, an error like this…

Msg 3616, Level 16, State 2, Procedure SP1, Line 5 An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
Msg 3616, Level 16, State 2, Procedure SP1, Line 5 An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

…happened to a SQL Agent job running during the evening.

(more…)

Continue ReadingZero footprint Server/Database DDL Trigger