Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




September 2007 Blog Posts

A handy but little-known SQL function: NULLIF()

A web application I have inherited uses data from a stored procedure that returns two columns:  Description and Override.  Basically, for each item returned, the web page should display the Description unless an Override is provided, in which case the Override is displayed.  This appears to be a simple case of using ISNULL() or COALESCE(): select coalesce(Override, Description) as Display from ... Unfortunately, it turns out that there is a mixture of NULL values and empty strings ('') in the Override column, so this doesn't work -- that expression will return '' instead of using the override because the coalesce() check for NULL...

posted @ Thursday, September 27, 2007 9:50 AM | Feedback (22) | Filed Under [ T-SQL ]

Optimizing Conditional WHERE Clauses: Avoiding ORs and CASE Expressions

Here's some quick tips on optimizing your SELECT statements when implementing conditions in your WHERE clause based on optional parameter values. read more...

posted @ Tuesday, September 18, 2007 4:25 PM | Feedback (27) | Filed Under [ Efficiency ]

Filter by month (plus other time periods)

Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month. What is the best way to declare parameters that will be used to indicate which month you are looking for, and how can we efficiently and easily make use of those parameters to get back the data we need? read more...

posted @ Friday, September 14, 2007 12:21 PM | Feedback (5) | Filed Under [ Techniques Efficiency Report Writing DateTime Data ]

Group by Month (and other time periods)

When you need to summarize transactional data by Month, there are several ways to do it, some better than others. What to ultimately choose depends on your needs, but remember: Keep it short and simple in T-SQL, and always do all of your formatting at your presentation layer where it belongs. read more...

posted @ Monday, September 10, 2007 11:28 AM | Feedback (58) | Filed Under [ T-SQL GROUP BY DateTime Data ]

Powered by:
Powered By Subtext Powered By ASP.NET