SQL Server - List only NULL columns in a table

Sometimes when you have a database with massive tables (in the order of millions of rows) getting fed by multiple sources, it is very difficult to find out which columns are completely empty (or contain the default NULL value).

I am pretty sure that I wasn't the only one who came against this issue, so in order to save others some time, you can find below a snippet of code that I wrote where it prints out all the columns of your table that contain only NULL values for all rows.

DECLARE @NAME sysname
DECLARE CRS CURSOR

LOCAL FAST_FORWARD FOR
SELECT name FROM syscolumns
WHERE id=OBJECT_ID('<TABLE_NAME>')

OPEN CRS
FETCH NEXT FROM CRS INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN

      EXEC('SELECT ''' + @NAME + '''
            WHERE NOT EXISTS
                  (SELECT * FROM <TABLE_NAME>
                  WHERE ' + @NAME + ' IS NOT NULL)'
      )

      FETCH NEXT FROM CRS INTO @NAME

END

CLOSE CRS
DEALLOCATE CRS

Hope you found this helpful and that it will make your life a bit easier.

Till next time...

SQL Server - Get longest value in a column

The other day at work I was trying to find out what are the distinct values in one of the columns in a table within my database.

As you most probably know this can be done by using the DISTINCT keyword in SQL. So a quick example would be the following:

SELECT DISTINCT <COLUMN_NAME>
FROM <TABLE_NAME>

But what I wanted to find out was which value was the longest. Using the ORDER BY clause would sort out the resulting set alphabetically and not by length.

So a very nice and quick solution could be created using the DATALENGTH keyword. The following snippet will bring back all distinct values (along with their respective length) and will sort them in descending order by length:

SELECT DISTINCT <COLUMN_NAME>,
      DATALENGTH(<COLUMN_NAME>) AS StringLength
FROM <TABLE_NAME>
GROUP BY <COLUMN_NAME>
ORDER BY StringLength DESC

You can also modify that to bring back only the biggest string in the column by using the following code:

SELECT DISTINCT TOP 1 <COLUMN_NAME>,
      DATALENGTH(<COLUMN_NAME>) AS StringLength
FROM <TABLE_NAME>
GROUP BY <COLUMN_NAME>
ORDER BY StringLength DESC

So that was it... nice and easy and you can get the longest string in a column and the number of characters.

Hope that was useful!!!

Introduction to Financial Derivatives - Option Contracts

To quickly reiterate, there are the following four basic derivatives:

- Forward contracts
- Futures contracts
- Swap contracts
- Option contracts

In this post we will look at option contracts.

Option Contracts
An option contract grants its holder the right, but not the obligation, to buy or sell something at a specified price, on or before a specified date.

A simple example:
You are interested in a company's stock (let's say ABC company). You believe that the stock is undervalued and will increase over the next several months. You decide to enter into an option contract which grants you the right (but remember... not the obligation!!!) to buy the stock for the current price in 6 months time. In six months time, if the price of the stock has increased, you exercise the option and you buy the stock at the price you "locked" six months ago. The gain is the difference between the current price and the price you paid. If the price has decreased, then you let the option to expire without exercising it since you can buy the stock at less than the "locked" price.

* One of the main differences between the option contract and the rest of the derivatives is the premium price you pay in order to enter the option contract. For the rest of the contract no premium is involved.


This post completes the introduction to the four basic derivatives!!!
I hope you enjoyed it...

Introduction to Financial Derivatives - Swap Contracts

To quickly reiterate, there are the following four basic derivatives:

- Forward contracts
- Futures contracts
- Swap contracts
- Option contracts

In this post we will look at swap contracts.

Swap Contracts
A swap contract is an agreement to exchange future cash flows.

A simple example:
You have found a great place to buy and you want a mortgage. So you go to the bank and you get a loan for the next 35 years where for the next 5 years you will be making an interest-only payments based on a floating rate of interest according to the Libor rate index. To reduce their exposure to changing interest rates, you enter into a fixed-floating swap agreement with another party. Under the terms of the swap contract, every month you will make an interest-only payment on a fixed rate (let's say 3.75 percent). In return, you will receive from the other party an interest-payment based on Libor with which to make your payment to the bank.

Introduction to Financial Derivatives - Futures Contracts

To quickly reiterate, there are the following four basic derivatives:

- Forward contracts
- Futures contracts
- Swap contracts
- Option contracts

In this post we will look at futures contracts.

Futures Contracts
A futures contract is in essence a standardised forward contract executed in an exchange.

What this means is that again a futures contract is an agreement to buy something at a specified price on a specified future date. It is exactly the same principle as a forward contract. The only difference is that this contract is executed in an exchange and the terms of the agreement are set by the exchange.

A simple example:
A merchant knows that for the next year he will need wheat to produce flour. He predicts that wheat prices will go up during the year, so he decides to "lock" the current price. The merchant will order now bushels of wheat to be delivered at some future date for the current price. The merchant will enter into a futures contract (instead of a forward contract) where the terms of the contract (like the number of bushels of wheat, the price per bushel, the quality of the wheat, etc.) are standardised and set by the exchange.

* There are some other differences between the forward and futures contracts concerning risk which I will not mention here.

Introduction to Financial Derivatives - Forward Contracts

To quickly reiterate, there are the following four basic derivatives:

- Forward contracts
- Futures contracts
- Swap contracts
- Option contracts

In this post we will look at forward contracts.

Forward Contracts
A forward contract is an agreement to buy something at a specified price on a specified future date.

A simple example:
You are planning a trip abroad in a months time. You will need to get some foreign currency for your trip. Let's say that you are going to the States, so you will need to get some US Dollars with you. The current exchange rate is 1.56, which means that for £1 you can get $1.56. For some strange reason you feel that the US Dollar will get stronger and by the end of that month your British pound will get less than $1.56 (let's say $1.52). So, you decide to "lock" the price and order US Dollars now to be delivered to you in a months time (just before your trip) with an exchange rate of 1.56. Automatically you are entering into a forward contract whereby you agree to buy something (US Dollars) at a specified price ($1.56 for £1) on a specified future date (in a months time).

Introduction to Financial Derivatives

In this post I will try to explain in very simple everyday terms the four basic derivatives. I will not delve into the details and mathematics of these four instruments, rather than I will provide a quick and (let me repeat it...) simple overview!

(The term "financial" has been omitted since this post does not make any reference to the subject of Calculus)

So... to get started, the four basic derivatives, which all the rest (and more complex) derivatives are based upon, are:

- Forward contracts
- Futures contracts
- Swap contracts
- Option contracts

In order not to make this post too lengthy, I will dedicate a separate post for each of the aforementioned derivatives.

Since my last post...

Well, it's been a while since I posted anything on my blog.

I guess it's high time I did, so here is a snapshot of what's been happening for the past 9 months.

- I got a new job and am getting more involved with Investment Banking projects (focusing on the Risk side) while at the same time moving into gaining some C# experience. Therefore, I will now be posting interesting code snippets which might solve a specific problem or just might prove helpful in certain situations.

- I have finished the first year from the MSc in International Securities, Investment and Banking offered by ICMA Centre in cooperation with Reading University. I will be posting Finance, Investment Banking, Risk and Derivatives related posts and try to make them as simple as possible for the non-experts!

- On a more personal note, I finally got my full motorcycle license... and YES!!! I bought my first motorcycle. A beautiful black Kawasaki ER6-F, of which I will be posting some photos as soon as the weather allows it.

I will try to post at least once a week so the material in this blog becomes rich both in terms of content and variety.

Until next time...