SQL Server - List only NULL columns in a table - Ver 2

Following my last post on how to list all NULL columns within a table in SQL Server, below you can find a second version of the script where you can list the NULL columns of a table in an easier-to-copy format.

Here it goes:


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('IF EXISTS( SELECT ''' + @NAME + '''
            WHERE NOT EXISTS
                  (SELECT * FROM <TABLE_NAME>
                  WHERE ' + @NAME + ' IS NOT NULL))
            BEGIN
                  PRINT ''' + @NAME + '''
            END'
      )

      FETCH NEXT FROM CRS INTO @NAME

END

CLOSE CRS
DEALLOCATE CRS

Again, I hope you found this useful.

See ya laterz....

Happy New Year everyone

Hi all,

I just wanted to wish everyone Happy New Year  and I hope this year is a lot better from the previous one.

It's been a bit more than a month since my last post, but I have been extremely busy with work, a bit of studying and having a great time with my girlfriend and her parents :)

Anyways, all the best for 2011!!!

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.