Today I had the strangest thing happen. I turned on my laptop and found my SQL Server Express (2005) service came up with one of the databases saying "restoring...". Nothing I did could make it go away (restarting the computer, restarting sql server, nothing). Also, there was nothing I could do in the tasks like detach the db. It just kept erroring saying the database was in the process of restoring. Finally after some Google searching I came across this ExpertsExchange question (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22875577.html).

The answer is to execute this sql:

RESTORE DATABASE [your database name] WITH RECOVERY

I'm going to have to look into what actually was going on but right now I've got stuff to do...

This is an interesting litle tidbit to keep in mind. What do you get if you count distinct against a table with no records? Here is the example query,

SELECT COUNT(DISTINCT ID) AS UniqueRecords FROM MyTable

If MyTable does not contain any records it will return a result set with a single record with a value 0. Now, what do you get if MyTable is actually a query and you're performing a query of queries? Well, if MyTable is a query with no records then you'll have no records in the result, not a 0 value.

Sometimes you want to compare a datetime value without the time portion to a date. If you use query param and specify a timestamp for the type but only provide a date as the value you'll be given 00:00:00 for the time, which is midnight. To strip, or more specifically, set the time porition of the value in the database or midnight also use this cast:

CAST(FLOOR(CAST(DateCompleted AS FLOAT)) AS DATETIME)

Read more...