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...

JAVAWUG BOF 54 The Future of the Java SE Platform and Beyond

Thursday, 11th of March 2010 - QCon London 2010

The 54th BoF of the JAVAWUG (Java Web User Group London) is about to start. The stage has been taken by Peter Pilgrim (the founder and organised of the JAVAWUG) and three very important figures in the Java world who will discuss, debate and answer questions regarding the future of Java SE 7 and OpenJKD 7.

The panel next to Peter Pilgrim, consists of:

• Alex Buckley, who is Sun's Computational Theologist, responsible for maintaining the moral and technical integrity of the Java programming language. He collaborates widely with academics, compiler engineers, and expert developers to ensure that the Java Language Specification and Java Virtual Machine Specification are as complete and consistent as possible. He has a PhD in computer science from Imperial College London. Alex maintains a blog here.
• Stephen Colebourne, who leads JSR-310 Date and Time API, based on his previous work on Joda-Time and Joda-Money. He is a frequent contributor to debate on Java language change (FCM closures, Project Lambda, Project Coin) and the future on Java (exposing the current problems in the JCP to a wider audience). He currently works for OpenGamma, a startup developing software for the financial industry.
  He maintains a blog here. 
• Patrick Curran, who is Chair of the JCP. In this role he oversees the activities of the JCP Program Office including driving the process, managing its membership, guiding specification leads and experts through the process, leading the Executive Committee meetings, and managing the JCP.org web site. He has also participated actively in several consortia and communities including the World Wide Web Consortium (W3C) (member of the W3C's Quality Assurance Working Group, co-chair of the W3C Quality Assurance Interest Group), and the Organization for the Advancement of Structured Information Standards (OASIS) (co-chair of the OASIS Test Assertions Guidelines Technical Committee). Patrick maintains a blog here.

The BoF lasted for an hour and a half. Alex and Stephen discussed mainly the new features of the OpenJDK 7 (an open-source implementation of the next major revision of the Java SE Platform). There is no certainty which of these features will eventually end up on the final release of JAVA SE 7. There is also no definite release date.

Also a discussion was made on closures, the modularity of the JDK 7 (The most promising way to improve the key metrics of download time, startup time, and memory footprint is to attack the root problem head-on as described by Mark Reinhold in his blog), and a small note on the support for multiple native languages (reference implementation for language implementers) 

A complete list of the changes and bug fixes for the JDK 7 can be found here.

Patrick discussed the function of JCP, how the RFEs are handled how someone can contribute with new features to the Java language.

The whole discussion and especially the Q&A session during the end of the BoF were very interesting. I was also very glad that I got the chance to meet Peter in person and help him broadcast the whole BoF live using UStream.TV.

A recording of the BoF, can be found below:


Livestream 54th BoF

I am looking forward for the next BoF from the JAVAWUG on Scala Web Development, on the 24th of March 2010. More details on this event can be found here

My Motorcycle Gear - Gloves

My next purchase after the helmet, which I presented on my previous post, was a pair of gloves.


More specifically I bought the Spada Airflow Leather gloves. A picture of them can be seen below:

Details about the gloves can be found here.


Reviews about my gloves can be found in the following posts:
Spada Airflow Gloves - Review


As before, I won't be able to post a review until I have tested them, but I can say for now that they are very comfortable and a good fir for my hand.

My Motorcycle Gear - Helmet

After the MCN London Motorcycle Show, I have started buying my motorcycle gear. As I mentioned in my last post, I do not own a bike yet, but I am very serious of getting on this year.


So, I have starting by buying my first element/accessory of the gear, which is the helmet. It was on sale on the MCN show at the bargain price of £80. The helmet is a HJC FS-10 Infinity blue helmet which you can see below:

I can't post a review just yet as I haven't ridden at all with this helmet. I can only say for now that it fits great, it is very comfortable and I can't wait to use it in the future.


Having said that, I will post a review as soon as I have a few miles on the bike wearing it.

MCN London Motorcycle Show

It's been a while since I updated my blog, so I thought I should post something a bit more personal and not so much of a technical nature.


So... I went to the MCN Motorcycle Show (or "The Carole Nash MCN Motorcycle Show" as it has been advertised) at London's ExCel centre on the 7th of February 2010.


I have never been to a motorcycle show in the UK before, so it was a new experience for me. The show was fantastic and very well organized, with most of the major manufacturers present. The only exception was Suzuki and I was a bit sad about it as I wanted to check up close the new Suzuki Gladius SFV650K9.


Oh well... I didn't let that ruin my mood at all. The full list of the manufacturers present include:

• Kawasaki
• Triumph
• Ducati
• Victory
• Yamaha
• Vespa
• Piaggio
• Norton
• Aprilia
• BMW
• Derbi
• Gilera
• Harley Davidson
• Honda
• Motto Guzzi

In addition to all the new models that the aforementioned manufacturers had to present, there were a lot of exhibitors with amazing bargains for clothing and various motorcycle accessories. My mate got a brake disk for his bike at 70% discount. He also grabbed a pair of trousers, a helmet and a jacket. He saved a total of more than £250 from the retail price.

I also grabbed the opportunity of course and treated myself a HJC FS-10 Infinity blue helmet for just £80. I don't own a bike yet, but I am starting to buying my gear and when I have bought everything I need, I will get one. You can see my new helmet below:

  

There was also a FREE Live Action Arena, but it was packed and didn't get a chance to see it properly. The few stunts that I did manage to see though were great.

As you can understand most of the new models from the manufacturers were there. I have uploaded a few photos of the bikes and a few other with me on some bikes.

Me on the Kawasaki Z750

Me on the new Kawasaki Ninja ZX-6R

Me on the Ducati Monster 696

Me on the new BMW S 1000 RR

Me on the new Honda CB600F Hornet

Me on the new Honda CBF 125

This is the bike that I will be getting soon !!! 

Now a few photos of the bikes. Below there are a few photos of the new BMW S 1000 RR:

And a few other photos that I took at the MCN show:

Overall, I really enjoyed the MCN show and I will definitely go again next year. My only complaint is about the food. Too expensive and the quality was not that great. 2 burgers for me and my mate with 2 sodas a bit more than 20 quid seems overpriced to me.

Yet again, we didn't go there for the food, so all in all, a great experience. We spent 4-5 hours there with my mate, and to be honest, we could spend another 5 easily!!! I am really looking forward for next year's show!

Can't wait to start riding!!!