Sep 242012
 

Hit-highlighting is a feature that many people wish SQL Server's Full-Text Search would support natively. This is where you can return the entire document (or an excerpt) and point out the words or phrases that helped match that document to the search. Doing so in an efficient and accurate manner is no easy task, as I found out first hand.

Continue Reading »
Sep 202012
 

I've written several times about using cursors and how, in most cases, it is more efficient to re-write your cursors using set-based logic.

I'm realistic, though.

I know that there are cases where cursors are "required" – you need to call another stored procedure or send an e-mail for every row, you are doing maintenance tasks against each database, or you are running a one-off task that simply isn't worth investing the time to convert to set-based.

Continue Reading »
Sep 132012
 

I've long been a proponent of choosing the correct data type. I've talked about some examples in a previous "Bad Habits" blog post, but this weekend at SQL Saturday #162 (Cambridge, UK), the topic of using DATETIME by default came up. In a conversation after my T-SQL : Bad Habits and Best Practices presentation, a user stated that they just use DATETIME even if they only need granularity to the minute or day, this way the date/time columns across their enterprise are always the same data type. I suggested that this might be wasteful, and that the consistency might not be worth it, but today I decided to set out to prove my theory.

Continue Reading »
Sep 052012
 

The most common need for stripping time from a datetime value is to get all the rows that represent orders (or visits, or accidents) that occurred on a given day. However, not all of the techniques that are used to do so are efficient or even safe. If you want a safe range query that performs well, use an open-ended range or, for single-day queries on SQL Server 2008 and above, use CONVERT(DATE). Read on to discover why…

Continue Reading »