This is a dumping ground of software development topics that I've run across and found interesting (mainly .NET development). Many topics are covered more thoroughly in other places, and much of the information is gleaned from other places, and I'll try to credit those sources when possible.

Friday, March 18, 2011

SQL: Sorting NULL Values

I was ordering a result set by a field that is nullable, but wanted any rows whose value for this field was null to be at the end of the set, not the beginning. Probably should have figured this out on my own, but you know how quick Mr. Google can provide the answer. This site had me up and running in a matter of seconds:

ORDER BY
   CASE
      WHEN Region IS NULL THEN 1
      ELSE 0
   END,
   Region

SQL Server: Restore cannot process database because it is in use by this session.

I tried to restore a SQL Server 2005 database, but kept getting the following error:

Restore cannot process database 'WTF' because it is in use by this session. It is recommended that the master database be used when performing this operation.

I opened Activity Monitor and killed a couple processes hanging on the database, but got the same error. When I opened the Activity Monitor again, one process had come back. I closed SSMS trying to get rid of this process, to no avail. I googled a bit and found various people going round and round about it, then found this simple solution from The DW Experience.

The problem was that my SQL login had its "Default database" set to the database I was attempting to restore, which keeps opening a session. By changing the default database to something else, I could finally restore the database. Then I promptly set the default database back, because that's the way I like it. Since I'll probably run into this problem again, I figured I should write it up here.

SQL: Many-to-Many Database Design

Here is a useful article on handling many-to-many relationships in your database design. I especially liked the advice on creating additional indexes on the table, which I promptly put to use on the 10 link tables I am currently working with:

http://www.singingeels.com/Articles/Understanding_SQL_Many_to_Many_Relationships.aspx

Followers