Concatenating Strings to NULL values

We recently ran into this situation of reports containing nothing but blank spaces. When investigated the reason was that the String concatenation statement was written as a general statement. There is nothing wrong in the statement, it works fine when the variables have a value but does not work when one of the variables is NULL.

The “SELECT @FinalString” statement returns NULL since the variable @String1 is NULL. When such a variable is concatenated with other set of variables or string values it would all result in NULL and the string message(s) that is to be used will not return the desired output (resulting in blank spaces in reports in one of our case).

In order to overcome such issues, the ISNULL function can be used which checks the value and replaces with the value to be replaced if NULL is found. The same statement is rewritten and ISNULL is implemented to check for NULL values in @String1 variable, if @String1 is NULL, then ‘EMPTY STRING1’ is included in the final string and results in a meaningful string value instead of NULL.

Note that the appropriate statement would also include ISNULL(@String2,’EMPTY STRING2′), it has been avoided in the example to emphasize its importance at the @String1..

For more information on ISNULL look at Books Online link here

— Bru Medishetty

Performance Tuning in SQL Server

One of the frequently asked questions in forums, interviews and during general discussions, is performance tuning. In fact, a friend of mine, (who knows a little bit of SQL Server) when speaking about my blogs, asked me why I had not written any blog on performance tuning. I gave a pause and said, do you know that heavy usage of cursors result in poor performance, Database partitioning results in slightly better performance and Database Snapshots cause a performance overhead? Then I continued, though you see I had a blog on those topics,  it is not noticed that they are related to performance of the SQL Server.

The point is, there are many actions or the usage of certain features which directly or indirectly affect the performance of a SQL Server. I would like to post blogs on performance tuning SQL Server, it may be Query Tuning such as rewriting the query to perform better or looking at Indexes and ensure that there are proper Indexes that help the query. It can be related to locking / blocking happening on the database or improper settings of the database options.

There are certain other things to look at when looking to tune a SQL Server apart from those mentioned above. An improper placement of the database files can lead a performance degradation or excessive usage of cursors. There can be system bottlenecks such as Memory, Processor, Disk or Network that is the prime cause for a system slowness. 

Many of these shall be addressed individually in the forth-coming blogs.

— Bru Medishetty

Things to look forward in 2010

Yet another year has passed ever so quickly and another new year, in fact a new decade has begun.

I had a satisfying and great year in 2009 as I had this site started and also blogged multiple times over the last few months of 2009. Now that I am looking forward to 2010, I wanted to come up with a list of things that I am looking forward to achieve in this Year. On top of the priorities is increase my commitment towards forum/community, that might be answering increased number of questions or writing more number of blogs. Few others that I am looking forward to are participating in some of the local SQL Server user groups and complete reading the SQL Server books I had already started reading and also read regularly (at least 3 books for a quarter).  And finally pursue the MCITP : Database Developer 2008.

Wishing all readers a Happy and Prosperous New Year 2010.

–Bru Medishetty