Nulls can be evil

Nulls can produce weird results when used with comparison operators. Few days back, I was debugging a query like the one below.

Select ....,
  Case When myColumn = 'SomeValue1' Then 'True' Else 'False' End As CaseOutput1,
  Case When myColumn = 'SomeValue2' Then 'True' Else 'False' End As CaseOutput2,
  Case When myColumn != 'SomeValue1' and myColumn != 'SomeValue2' 
    Then 'True' Else 'False' End As CaseOutput3,
...

Looking at the case statements, we can infer that at least one of the case statements will always output a True, but this was not the case. After much effort, I found that myColumn resulted in null due to a left join and so all the case statements evaluated to False. Getting to the cause of problem wasn’t an easy task since the query was quite complex with multiple joins and several case statements.

Poor intellisense in SQL Server 2008 Management Studio

The new intellisense feature in SQL Server 2008 Management Studio is desperate. It does not work when connected to SQL server instances running SQL Server 2005. Even, it gives weird results with SQL instances running 2008. I added a new column in my database and the intellisense didn’t update itself. I even tried to disconnect and then reconnect to my SQL server 2008 instance but it still did not show my new columns. Recalling the great intellisense in Visual Studio, I can’t believe such poor quality!

But, still, I am a big fan of SQL Server and its optimizations.

SSIS: How to use a query containing table variables for an OLE DB Source

This one consumed a lot of my brain energies yesterday. I was working on some Sql Server Integration Services task and needed to create a complex query containing table variables. The query was working fine in SQL Management Studio and the SSIS designer was even previewing the data correctly. But when the task was actually executed, the query did not return any rows. I posted a question on TechNet forums and got an answer from Charles Talleyrand here. Actually, I needed a SET NOCOUNT ON statement. Let me explain using an example:

Say, I want to create a table variable (not a temp table), populate it and then use the result set as an input for an OLE DB Source. Here’s a sample query for this:


declare @mytable table
(
col1 int,
col2 varchar(20)
)

insert @mytable values (1, 'one')
insert @mytable values (2, 'two')
insert @mytable values (3, 'three')

select * from @mytable

The SSIS designer will correctly identify the column names from the above query and even display the data if you click on the Preview button. But when the task is executed, no rows will be returned. I think the output of insert statements: i.e. 1 row(s) affected was the source of problem and so a SET NOCOUNT ON prevented such interferences. Hooray!! So if you are ever working on some SSIS Data Transfer Task and want to use table variables inside your query, make sure you do not forget the SET NOCOUNT ON statement.